This is not a full/correct MySQL query only pseudo-code:
Select *
from Notifications as n
where n.date > (CurrentDate-10 days)
limit by 1
FOR UPDATE
http://dev.mysql.com/doc/refman/5.0/en/select.html states:
If you use FOR UPDATE with a storage engine that uses page or row locks, rows examined by the query are write-locked until the end of the current transaction
Is here only the one record returned locked by MySQL or all records it has to scan to find the single record?
Best Answer
Why don't we just try it?
Set up the database
Now, start two database connections
Connection 1
Connection 2
If MySQL locks all rows, the following statement would block. If it only locks the rows it returns, it shouldn't block.
And indeed it does block.
Interestingly, we also cannot add records that would be read, i.e.
blocks as well!
I can't be sure at this point whether MySQL just goes ahead and locks the entire table when a certain percentage of rows are locked, or where it's actually really intelligent in making sure the result of the
SELECT ... FOR UPDATE
query can never be changed by another transaction (with anINSERT
,UPDATE
, orDELETE
) while the lock is being held.