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

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

Answer 2

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.