How does InnoDB take and retain record locks on READ COMMITTED LEVEL

149 Views Asked by At

mysql-refman-5.7, 14.7.2.1 Transaction Isolation Levels, says:

Under READ COMMITTED,

  1. "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."
  2. "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?

0

There are 0 best solutions below