mysql - 为什么innodb下更新A行时B行也被锁住？

Question

在学习MySQL事务隔离级别时，本来想重现《高性能MySQL》中的死锁现象（Page 9）。结果发现innodb更新单行时，造成全表被锁住，不符合innodb行锁的设置。

使用的版本：

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.6.26, for Linux (x86_64) using  EditLine wrapper

Connection id:          2
Current database:       test
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.6.26 MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 4 hours 52 min 1 sec

Threads: 3  Questions: 107  Slow queries: 0  Opens: 69  Flush tables: 1  Open tables: 62  Queries per second avg: 0.006
--------------

mysql> show variables like '%isolation%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

• 测试表

mysql> show create table t\G;
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a1` int(11) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> select * from t;
+------+------+------+
| a1   | b    | c    |
+------+------+------+
|    1 | a    | b    |
|    2 | aa   | bb   |
+------+------+------+
2 rows in set (0.00 sec)

• 在两个独立的会话中创建两个事务

• 会话1

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update t set b='x' where a1=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

• 会话2，在被阻塞一段时间后会出现超时错误。

mysql> start transaction
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> update t set c='yy' where a1=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

原则上来说，会话1中的行锁不会阻塞会话2中的更新才对。

不知道大家是否遇到过这样的问题，感谢提供相应的解答。

Answer 1

在a1上添加索引，才是行锁定。innodb 没有索引，照样是表锁定