When using MySQL's FOR UPDATE locking, what is exactly locked?

Question

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?

Answer 1

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.