6

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?

user1027167
  • 4,320
  • 6
  • 33
  • 40

3 Answers3

10

You need to implement a LOCKDATE and LOCKWHO field in your table. Ive done that in many applications outside of PHP/Mysql and it's always the same way.

The lock is terminated when the TTL has passed, so you could do a substraction of dates using NOW and LOCKDATE to see if the object has been locked for more than 30 minutes or 1h as you wish.

Another factor is to consider if the current user is the one locking the object. So thats why you also need a LOCKWHO. This can be a user_id from your database, a session_id from PHP. But keep it to something that identifies a user, an ipaddress is not a good way to do it.

Finaly, always think of a mass-unlock feature that simply resets all LOCKDATEs and LOCKWHOs...

Cheers

Mathieu Dumoulin
  • 12,126
  • 7
  • 43
  • 71
  • 1
    Also note, that optimistic is also a good way to work, you can implement optimistic locking with a simple LASTUPDATE column and compare it to the last LASTUPDATE value that you have in a HIDDEN FIELD. If the data has changed, you force the user to accept that he has to restart his editing or you can try and see if you want to do a MERGE page to show new and old content for the user to compare. – Mathieu Dumoulin Nov 17 '11 at 16:47
  • I was considering something very similar to the LOCKDATE and LOCKWHO mentioned above. Have you ever thought about using a jQuery/Ajax request to update the LOCKDATE field (from the Edit View) while the user is working on the edit? – jjwdesign Feb 21 '13 at 20:52
  • I would not implement a jquery lock updater because, if for any reason, your user end up forgetting his browser open and someone needs to edit the object, it will never time out. Giving a reasonnable TTL to your lockdate is really the method of choice. – Mathieu Dumoulin Feb 22 '13 at 19:30
  • Need self coding to implement pessimistic locking in web application, no some simple function call in existing framework? – user1169587 Jan 23 '19 at 12:41
  • I'm sure you can find a framework that will have this feature either native or through a plugin but considering this was written in 2013, you might want to review the need for locking. The general app behavior today is to used ReST patterns and they aren't really good for pessimistic locking, maybe optimisic to some degrees, but not pessimistic. – Mathieu Dumoulin Jan 25 '19 at 15:19
7

I would write the locks in one centralized table instead of adding fields to all tables.

Example table structure :

tblLocks

  • TableName (The name of tha locked table)
  • RowID (Primary key of locked table row)
  • LockDateTime (When the row was locked)
  • LockUser (Who locked the row)

With this approach you can find all locks that are made by a user without having to scan all tables. You could kill all locks when user logs out for example.

Benoit Gauthier
  • 141
  • 2
  • 8
6

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.

DaveRandom
  • 87,921
  • 11
  • 154
  • 174