When using MySQL's FOR UPDATE locking, what is exactly locked?
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.
I know this question is pretty old, but I've wanted to share the results of some relevant testing I've done with indexed columns which has yielded some pretty strange results.
Table structure:
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`notid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
12 rows inserted with INSERT INTO t1 (notid) VALUES (1), (2),..., (12). On connection 1:
BEGIN;
SELECT * FROM t1 WHERE id=5 FOR UPDATE;
On connection 2, the following statements are blocked:
SELECT * FROM t1 WHERE id!=5 FOR UPDATE;
SELECT * FROM t1 WHERE id<5 FOR UPDATE;
SELECT * FROM t1 WHERE notid!=5 FOR UPDATE;
SELECT * FROM t1 WHERE notid<5 FOR UPDATE;
SELECT * FROM t1 WHERE id<=4 FOR UPDATE;
The strangest part is that SELECT * FROM t1 WHERE id>5 FOR UPDATE; is not blocked, nor are any of
...
SELECT * FROM t1 WHERE id=3 FOR UPDATE;
SELECT * FROM t1 WHERE id=4 FOR UPDATE;
SELECT * FROM t1 WHERE id=6 FOR UPDATE;
SELECT * FROM t1 WHERE id=7 FOR UPDATE;
...
I'd also like to point out that it seems the entire table is locked when the WHERE condition in the query from connection 1 matches a non-indexed row. For example, when connection 1 executes SELECT * FROM t1 WHERE notid=5 FOR UPDATE, all select queries with FOR UPDATE and UPDATE queries from connection 2 are blocked.
-EDIT-
This is a rather specific situation, but it was the only I could find that exhibits this behaviour:
Connection 1:
BEGIN;
SELECT *, @x:=@x+id AS counter FROM t1 CROSS JOIN (SELECT @x:=0) b HAVING counter>5 LIMIT 1 FOR UPDATE;
+----+-------+-------+---------+
| id | notid | @x:=0 | counter |
+----+-------+-------+---------+
| 3 | 3 | 0 | 9 |
+----+-------+-------+---------+
1 row in set (0.00 sec)
From connection 2:
SELECT * FROM t1 WHERE id=2 FOR UPDATE; is blocked;
SELECT * FROM t1 WHERE id=4 FOR UPDATE; is not blocked.
The thread is pretty old, just to share my two cents regarding the tests above performed by @Frans
Connection 1
BEGIN;
SELECT * FROM notification WHERE `date` >= '2011-05-03' FOR UPDATE;
Connection 2
BEGIN;
SELECT * FROM notification WHERE `date` = '2011-05-02' FOR UPDATE;
The concurrent transaction 2 will be blocked for sure, but the reason is NOT that the transaction 1 is holding the lock on the whole table. The following explains what has happened behind the scene:
First of all, the default isolation level of the InnoDB storage engine is Repeatable Read. In this case,
1- When the column used in where condition is not indexed (as the case above):
The engine is obliged to perform a full table scan to filter out the records not matching the criteria. EVERY ROW that have been scanned are locked in the first place. MySQL may release the locks on those records not matching the where clause later on. It is an optimization for the performance, however, such behavior violates the 2PL constraint.
When transaction 2 starts, as explained, it needs to acquire the X lock for each row retrieved although there exists only a single record (id = 2) matching the where clause. Eventually the transaction 2 will be waiting for the X lock of the first row (id = 1) until the transaction 1 commits or rollbacks.
2- When the column used in where condition is a primary index
Only the index entry satisfying the criteria is locked. That's why in the comments someone says that some tests are not blocked.
3 - When the column used in where condition is an index but not unique
This case is more complicated. 1) The index entry is locked. 2) One X lock is attached to the corresponding primary index. 3) Two gap locks are attached to the non-existing entries right before and after the record matching the search criteria.