問題描述

在學習MySQL事務隔離級別時，本來想重現《高性能MySQL》中的死鎖現象（Page 9）。結果發現innodb更新單行時，造成全表被鎖住，不符合innodb行鎖的設置。

使用的版本：

mysql> status--------------mysql Ver 14.14 Distrib 5.6.26, for Linux (x86_64) using EditLine wrapperConnection id: 2Current database: testCurrent user: root@localhostSSL: Not in useCurrent pager: stdoutUsing outfile: ’’Using delimiter:;Server version: 5.6.26 MySQL Community Server (GPL)Protocol version: 10Connection: Localhost via UNIX socketServer characterset: latin1Db characterset: latin1Client characterset: utf8Conn. characterset: utf8UNIX socket: /var/lib/mysql/mysql.sockUptime: 4 hours 52 min 1 secThreads: 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 tG;*************************** 1. row *************************** Table: tCreate Table: CREATE TABLE `t` ( `a1` int(11) DEFAULT NULL, `b` varchar(10) DEFAULT NULL, `c` varchar(10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)ERROR: No query specifiedmysql> 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 沒有索引，照樣是表鎖定