How to implement pessimistic locking in a php/mysql web application?

Question

How to implement pessimistic locking in a php/mysql web application?

1. web-user opens a page to edit one dataset (row)
2. web-user clicks on the button "lock", so other users are able to read but not to write this dataset
3. web-user makes some modifications (takes maybe 1 to 30 minutes)
4. web-user clicks "save" or "cancel" and the "lock" is removed

Are there standard methods in php/mysql for this scenario? What happens if the web-user never clicks on "save"/"cancel" but closes the internet-exploror?

Answer 1

Traditionally this is done with a boolean locked column on the record in the database that is flagged appropriately.

It is a function of this sort of locking that the lock has to be released, and circumstances may prevent this happening naturally (system crashes, user stupidity, dropped network packets, etc etc etc). This is why you would need to provide some manual unlock method and/or impose a time limit (maybe with a cron job?) on how long a record can be locked for. You could implement some kind of AJAX poll to keep the record locked if the browser is still open? At any rate, you would probably be best to verify the data in the record is the same as it was when the lock was aquired before you modify it.

This limitation of this type of behaviour is particularly prevalent in web applications, but is true of anything that uses this approach - Sage Line 50, for one, is a bugger for it, I regularly have to delete lock files after machine/application crashes.