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.

Answer 2

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.

Answer 3

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.

Answer 4

Following links from the documentation page you posted gives more information about locking. In this page

A SELECT ... FOR UPDATE reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL UPDATE would set on the rows.

This seems pretty clear that it is all rows that it has to scan.

Answer 5

From mysql official doc:

A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row.

For the case discussed in Frans' answer, all rows are locked because there's a table scan during sql processing:

If you have no indexes suitable for your statement and MySQL must scan the entire table to process the statement, every row of the table becomes locked, which in turn blocks all inserts by other users to the table. It is important to create good indexes so that your queries do not unnecessarily scan many rows.

Check the latest doc here: https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html

Answer 6

As others have mentioned, SELECT... FOR UPDATE locks all rows encountered in the default isolation level. Try setting the isolation for the session which runs this query to READ COMMITTED, for example precede the query with: set session transaction isolation level read committed;

Answer 7

It locks all the rows selected by query.