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?
Advertisement
Answer
Why don’t we just try it?
Set up the database
CREATE DATABASE so1; USE so1; CREATE TABLE notification (`id` BIGINT(20), `date` DATE, `text` TEXT) ENGINE=InnoDB; INSERT INTO notification(id, `date`, `text`) values (1, '2011-05-01', 'Notification 1'); INSERT INTO notification(id, `date`, `text`) values (2, '2011-05-02', 'Notification 2'); INSERT INTO notification(id, `date`, `text`) values (3, '2011-05-03', 'Notification 3'); INSERT INTO notification(id, `date`, `text`) values (4, '2011-05-04', 'Notification 4'); INSERT INTO notification(id, `date`, `text`) values (5, '2011-05-05', 'Notification 5');
Now, start two database connections
Connection 1
BEGIN; SELECT * FROM notification WHERE `date` >= '2011-05-03' FOR UPDATE;
Connection 2
BEGIN;
If MySQL locks all rows, the following statement would block. If it only locks the rows it returns, it shouldn’t block.
SELECT * FROM notification WHERE `date` = '2011-05-02' FOR UPDATE;
And indeed it does block.
Interestingly, we also cannot add records that would be read, i.e.
INSERT INTO notification(id, `date`, `text`) values (6, '2011-05-06', 'Notification 6');
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 an INSERT
, UPDATE
, or DELETE
) while the lock is being held.