在学习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中的更新才对。
不知道大家是否遇到过这样的问题,感谢提供相应的解答。
在a1上添加索引,才是行锁定。innodb 没有索引,照样是表锁定