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.

Typically SELECT's are non-locking because they are a read operation which is not transactional. — Jay Blanchard
– Jay Blanchard, Commented Oct 29, 2014 at 16:52
If its locked for reading, it waits. And waits. (Depending on your SESSION TRANSACTION ISOLATION LEVEL). But usually you'll see 'stale' data (i.e: no data yet from yet uncommitted transactions, so 'stale' is debatable), rather then run into row locking with reads. — Wrikken
– Wrikken, Commented Oct 29, 2014 at 16:53
it also depends on the table type. I assume this is InnoDB? — Machavity
– Machavity ♦, Commented Oct 29, 2014 at 16:58
So what you're saying @Wrikken - is that my code should assume a successful lock and continue on with logic - so that a concurrent transaction would appear similar to one that was done previously. Basically, if Transcation 1 locks and makes changes as Transaction 2 tries to read, it will appear to Transaction 2 the same way as if Transaction 1 occurred non-concurrently but prior to T2's read time. — NEW2WEB
– NEW2WEB, Commented Oct 29, 2014 at 17:03

dognose · Accepted Answer · 2014-10-29 19:12:19Z

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)

excellent response. I was reading the response from Wrikkn yesterday and I was confused as he mentioned that SELECTS can still occur when FOR UPDATE is used. I get how using the WHERE clause can benefit you in some scenarios, but you'll have to always be aware of ALL of the different types of updates possible to the data you're using to ensure they are all encapsulated within your update statement. I don't want to do this as there are many ways to update the data I'm looking at. I just want to temporarily 'lock' a few rows in 2 or 3 tables so I can update them, then release.

Collectives™ on Stack Overflow

PHP - MySQL Row level locking example

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related