PHP - MySQL Row level locking example

Question

I've seen many posts explaining the usage of Select FOR UPDATE and how to lock a row, however I haven't been able to find any that explain what occurs when the code tries to read a row that's locked.

For instance. Say I use the following:

$con->autocommit(FALSE);
$ps = $con->prepare( "SELECT 1 FROM event WHERE row_id = 100 FOR UPDATE");
$ps->execute();
...
//do something if lock successful
...
$mysqli->commit();

In this case, how do I determine if my lock was successful? What is the best way to handle a scenario when the row is locked already?

Sorry if this is described somewhere, but all I seem to find are the 'happy path' explanations out there.

Answer 1

In this case, how do I determine if my lock was successful? What is the best way to handle a scenario when the row is locked already?

If the row you are trying to lock is already locked - the mysql server will not return any response for this row. It will wait², until the locking transaction is either commited or rolled back.

(Obviously: if the row has been deleted already, your SELECT will return an empty result set and not lock anything)

After that, it will return the latest value, commited by the transaction that was holding the lock.

Regular Select Statements will not care about the lock and return the current value, ignoring that there is a uncommited change.

So, in other words: your code will only be executed WHEN the lock is successfull. (Otherwhise waiting² until the prior lock is released)

Note, that using FOR UPDATE will also block any transactional SELECTS for the time beeing locked - If you do not want this, you should use LOCK IN SHARE MODE instead. This would allow transactional selects to proceed with the current value, while just blocking any update or delete statement.

² the query will return an error, after the time defined with innodb_lock_wait_timeout http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout It then will return ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

In other words: That's the point where your attempt to acquire a lock fails.

---

Sidenode: This kind of locking is just suitable to ensure data-integrity. (I.e. that no referenced row is deleted while you are inserting something that references this row).

Once the lock is released any blocked (or better call it delayed) delete statement will be executed, maybe deleting the row you just inserted due to Cascading on the row on which you just held the lock to ensure integrity.

If you want to create a system to avoid 2 users modifying the same data at the same time, you should do this at an application level and look at pessimistic vs optimistic locking approches, because it is no good idea to keep transactions running for a long period of time. (I think in PHP your database connections are automatically closed after each request anyway, causing an implicit commit on any running transaction)