Optimistic and pessimistic locks

Question

Work on my first project php / Codeigniter and Ive checked the "network" for information about blocking access to data editing, and havent found a lot of information.

I expect this to be a fairly common occurrence for 2 users to try to edit the same form at the same time.

My experience (in the world of preserving the state of BBx, filePro, and other RAD applications) is that editable data is blocked using pessimistic locking - one user has access to the editing form at that time. The second user basically has to wait for the completion of the first. I understand that this can be done using Ajax sending XMLHttpRequests to support the lock database.

The stateless php world seems to prefer an optimistic lock. If I understand correctly, it works as follows: both users gain access to the data, and each of them writes "before changing the version of the data." Before saving the changes, the data is retrieved again and compared with the version before changes. If the two versions are identical, then user changes are recorded. If they are different; the user is shown what has changed since he started editing, and some mechanism has been added to eliminate the differences, or the message "Sorry, try again" is displayed to the user.

I wonder what experience we had with people with both pessimistic and optimistic blockages. If there are any libraries, tools, or “accessible access methods,” I appreciate the link.

thanks

Answer 1

As far as I know, CodeIgniter does not support row locking. If you want to implement optimistic locking, you must add a version column or a timestamp column, which you must change each time you update / insert. Put the version column in a hidden field in your forms. Then, before each update, add a where clause, for example:

$this->db->where('version',$editedVersion); 

or

 $this->db->where('timestamp',$editedTimestamp); 

And then you should check that the update correctly updated 1 line ..

 $this->db->where('id',$editedId); $this->db->update('tablename',$data); $rowsAffected = $this->db->affected_rows(); if ($rowsAffected == 0) { /* Data changed by other user, update failed */ } else { /* updated successfully */ } 

Answer 2

If you use the InnoDB storage engine, you can get exclusive row-level locks using SELECT FOR UPDATE statements.

SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE Read lock

InnoDB Lock Modes