mysql-refman-5.7, 14.7.2.1 Transaction Isolation Levels, says:
Under READ COMMITTED,
- "For UPDATE or DELETE statements, InnoDB holds locks only for rows that it updates or deletes. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition."
- "However, if the WHERE condition includes an indexed column, and InnoDB uses the index, only the indexed column is considered when taking and retaining record locks. "
So, does 1st description conflicts with 2nd?
Here is the example:
mysql> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`a` int(11) NOT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> select * from t order by b,c;
+---+------+------+
| a | b | c |
+---+------+------+
| 2 | 2 | 4 |
| 1 | 3 | 3 |
| 3 | 3 | 4 |
| 4 | 5 | 6 |
+---+------+------+
# Session A:
mysql> set TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update t set c = 5 where b=3 and c =3;
Query OK, 1 row affected (0.00 sec)
Session B:
mysql> set TRANSACTION ISOLATION LEVEL READ COMMITTED;
mysql> update t set c = 2 where b=3 and c =4; # waiting for lock
So, Session A locks all the records with b=3, why not it just locks the record with b=3 and c=3?