I've been looking around now, reading the mysql site and I still can't see exactly how it works.
I want to select and row lock the result for writing, write the change and release the lock. audocommit is on.
scheme
id (int)
name (varchar50)
status (enum 'pending', 'working', 'complete')
created (datetime)
updated (datetime)
Select an item with a status of pending, and update it to working. Use an exclusive write to make sure that the same item is not picked up twice.
so;
"SELECT id FROM `items` WHERE `status`='pending' LIMIT 1 FOR WRITE"
get the id from the result
"UPDATE `items` SET `status`='working', `updated`=NOW() WHERE `id`=<selected id>
Do I need to do anything to release the lock, and does it work like I've done above?
Best Answer
What you want is SELECT ... FOR UPDATE from within the context of a transaction. SELECT FOR UPDATE puts an exclusive lock on the rows selected, just as if you were executing UPDATE. It also implicitly runs in READ COMMITTED isolation level regardless of what the isolation level is explicitly set to. Just be aware that SELECT ... FOR UPDATE is very bad for concurrency and should only be used when absolutely necessary. It also has a tendency to multiply in a codebase as people cut and paste.
Here's an example session from the Sakila database which demonstrates some of the behaviors of FOR UPDATE queries.
First, just so we're crystal clear, set the transaction isolation level to REPEATABLE READ. This is normally unnecessary, as it is the default isolation level for InnoDB:
In the other session, update this row. Linda got married and changed her name:
Back in session1, because we were in REPEATABLE READ, Linda is still LINDA WILLIAMS:
But now, we want exclusive access to this row, so we call FOR UPDATE on the row. Notice that we now get the most recent version of the row back, that was updated in session2 outside of this transaction. That's not REPEATABLE READ, that's READ COMMITTED
Let's test out the lock set in session1. Note that session2 cannot update the row.
But we can still select from it
And we can still update a child table with a foreign key relationship
Another side effect is that you greatly increase your probability of causing a deadlock.
In your specific case, you probably want:
If the "do some other stuff" piece is unnecessary and you don't actually need to keep information about the row around, then the SELECT FOR UPDATE is unnecessary and wasteful and you can instead just run an update:
Hope this makes some sense.