optimistic and pessimistic locks

[eluser]billmce[/eluser]
Working on my first php/Codeigniter project and I've scoured the 'net for information on locking access to editing data and haven't found very much information.

I expect it to be a fairly regular occurrence for 2 users to attempt to edit the same form simultaneously.

My experience (in the stateful world of BBx, filePro, and other RAD apps) is that the data being edited is locked using a pessimistic lock -- one user has access to the edit form at the time. The second user basically has to wait for the first to finish. I understand this can be done using Ajax sending XMLHttpRequests to maintain a 'lock' database.

The php world, lacking state, seems to prefer optimistic locking. If I understand it correctly it works like this: both users get to access the data and they each record a 'before changes' version of the data. Before saving their changes, the data is once again retrieved and compared the 'before changes' version. If the two versions are identical then the users changes are written. If they are different; the user is shown what has changed since he/she started editing and some mechanism is added to resolve the differences -- or the user is shown a 'Sorry, try again' message.

I'm interested in any experience people here have had with implementing both pessimistic and optimistic locking. If there are any libraries, tools, or 'how-to's available I'm appreciate a link.

Thanks

[eluser]WanWizard[/eluser]
You usually deal with this at database level, not at application level, as there could be more than one process that wants to modify the same dataset.

To do that, you have to start working transaction based (choose a RDMBS that supports transactions), enclose your queries in a transaction, and do a "SELECT ... FOR UPDATE" to lock the rows you want to update. Other users can still do a select to view the data, but if they also do a "SELECT ... FOR UPDATE" for the same rows. A commit or rollback of the transaction will release the locks.

Note that for most RDBMS's, other lock requests wait until the lock is released. Which means if you do this with persistent connections, set a lock before displaying the form, and then go for coffee, you could lock out other users for a significant period of time. If you don't use persistent connections, locks are automatically released when your script end, triggering a rollback if you hadn't done a commit.

In all, not very practical for your application.

You probably need to go for some kind of semaphore system, either in a database table or using file I/O. Make sure you implement lock expiry, and a cleanup routine, to prevent permanent locks with someone opens a form, sets a lock, then closes his computer for a trip to the Bahamas...

[eluser]billmce[/eluser]
Is my understanding correct:

. $this->db->trans_start();
. Use SELECT ... FOR UPDATE to pull data out of the database
. Bring up a VIEW where the user can make their changes
. when the user submits their changes the data is saved and
. $this->db->trans_complete(); completes the transaction unlocking everything that was locked

right?

Thanks for taking the time to write such a thorough and detailed explanation. It's very helpful

[eluser]WanWizard[/eluser]
Nope.

Within a single request that is what you should do, but in your case there are two requests. The first one fetches the form, the second one posts the form. When a request finishes, all locks are automatically released and any open transactions rollbacked. You could work around that by using a persistent connection to the database (not all engines support that), but then a lock is never released if a user requests the form but never posts it. Untill you kill the database connection.

So you'll need a semaphone system, that works not unlike sessions, to span multiple requests in a stateless environment. To do that, create a library that implements methods to set a lock (using identifying key fields of your choice), releases a lock (using the same keyfields), and a method to expire stale locks (using a timeout value on the lock, call it a garbage collection method).

You can use files or a database table as storage. You have to devise a way to prevent two processes setting the same lock. For files there's a double file lock system, google for it, for tables you could lock the table, check if the key exists, check if it's not expired, add a lock for the key if not, unlock the table, return the result. Don't know how well that scales though.

It's on my todo list to create such a library, but the list is rather long...