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

Question

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

• the web user opens a page for editing one data set (row)
• the web user clicks the lock button, so other users can read but not write this data set.
• the web user makes some changes (takes 1 to 30 minutes).
• the web user clicks “save” or “cancel” and the “lock” is deleted.

Are there standard methods in php / mysql for this script? What happens if the web user never clicks “save” / “cancel” but closes the internet search engine?

Answer 1

Traditionally, this is done with a boolean column locked in a database record that is marked accordingly.

This is a blocking function, which the lock should be released, and circumstances can prevent this in a natural way (system crashes, stupid user, dropped network packets, etc., etc., etc., etc.). That's why you will need to provide a manual unlock method and / or set a time limit (perhaps with a cron job?) On how long the recording can be locked. Could you do some kind of AJAX poll to keep the record locked if the browser is still open? In any case, it is probably best to verify that the data in the record is the same as when the lock was acquired before you changed it.

This limitation of this type of behavior is especially common in web applications, but true for anything that uses this approach. Sage Line 50, for one, is a mistake for him, I regularly delete lock files after the machine / application crashes.

Answer 2

You need to enter LOCKDATE and LOCKWHO in the table. Ive done this in many applications outside of PHP / Mysql, and it is always the same.

The lock ends when the TTL has passed, so you can infer from dates using NOW and LOCKDATE to find out if the object has been locked for more than 30 minutes or 1 hour, as you wish.

Another factor is considering whether the current user is locking the object. That is why you also need LOCKWHO. It could be user_id from your database, session_id from PHP. But keep it in something that identifies the user, ipaddress is not the best way to do this.

Finally, always think of a mass unlock function that simply resets all LOCKDATE and LOCKWHOs ...

Greetings

Answer 3

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

Example table structure:

tblLocks

• TableName (name of the locked table)
• RowID (primary row key of a locked table)
• LockDateTime (when the row is locked)
• LockUser (Who locked the line)

With this approach, you can find all the locks created by the user without scanning all tables. You can kill all locks when the user logs out, for example.