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, Oct 29 '14 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, Oct 29 '14 at 16:53
it also depends on the table type. I assume this is `InnoDB`? — Machavity, Oct 29 '14 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, Oct 29 '14 at 17:03
No, the Transaction 2 will read rows as if Transaction 1 hadn't changed them (that transaction isn't committed yet), however, it it tries to _modify_ those rows, the query doing that modification will hang until Transaction 1 commits. — Wrikken, Oct 29 '14 at 17:10
However, if another transaction does a `SELECT ... whatever... FOR UPDATE`, _that_ query will hang until the the other transaction has committed its changes. However, for normal activities, doing a `FOR UPDATE` in transactions has very little use. It normally doesn't block reads, but what you see could be something else if transactions have finished committing. — Wrikken, Oct 29 '14 at 17:11
@Wrikken - I'm assuming you didn't see my update (FOR UPDATE). It is my understanding that when using these keywords it locks the row for READ as well and my statement stands. Am I correct? — NEW2WEB, Oct 29 '14 at 17:20
No, you are not. Reads continue on, without your changes, until you commit a transaction. (Just try it out with 2 different mysql cli clients, and you'll see). However, the transaction _doing_ the `FOR UPDATE` query will wait until all transactions with a lock have committed. Also: normally you don't _want_ to block reads for other processes there, do you want this, and if so: can you explain _why_ you want this. On the question: "how can I know this lock is successful": `execute()` returns true or false (or: if you've enabled exceptions, an exception is thrown on failure). — Wrikken, Oct 29 '14 at 17:24
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/63882/discussion-between-new2web-and-wrikken). — NEW2WEB, Oct 29 '14 at 17:33
Ah, regarding your last question in chat, when I was already gone again: do that in one query: `UPDATE invite i JOIN event e ON e.id = i.event_id AND e.status='P' SET e.status = 'A', i.status = 'A' WHERE i.inv_id = 1000;` — Wrikken, Oct 30 '14 at 09:50

dognose · Answer 1 · 2014-10-29T19:12:19.957

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. — NEW2WEB, Oct 30 '14 at 15:13

PHP - MySQL Row level locking example

1 Answers1