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.