mysql innodb 表锁 - 当更新一行时阻止更新另一行

Question

当在mysql(innodb)中创建死锁时，如“高性能MySQL”第10页的示例。但是如果我在一个事务中更新测试的一行，则更新另一行将被阻塞并最终超时。这就像innodb在使用where条件进行更新时使用表级锁而不是行级锁。这种情况不符合innodb的行级锁。

Mysql版本:

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)

然后打开两个独立的 session 来创建两个事务

第 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

在 session 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

阻塞期间，使用InnoDB插件获取锁定关系

mysql> SELECT r.trx_id waiting_trx_id,  r.trx_mysql_thread_id waiting_thread,
    ->       r.trx_query waiting_query,
    ->       b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread,
    ->       b.trx_query blocking_query
    -> FROM       information_schema.innodb_lock_waits w
    -> INNER JOIN information_schema.innodb_trx b  ON  b.trx_id = w.blocking_trx_id
    -> INNER JOIN information_schema.innodb_trx r  ON  r.trx_id = w.requesting_trx_id;
+----------------+----------------+--------------------------------+-----------------+-----------------+----------------+
| waiting_trx_id | waiting_thread | waiting_query                  | blocking_trx_id | blocking_thread | blocking_query |
+----------------+----------------+--------------------------------+-----------------+-----------------+----------------+
| 5933           |              6 | update t set c='yy' where a1=1 | 5932            |               5 | NULL           |
+----------------+----------------+--------------------------------+-----------------+-----------------+----------------+
1 row in set (0.00 sec)

原则上， session 1 中的行锁不会阻止 session 2 中的更新。

如果您遇到这样的问题，请您帮忙解释一下 session 2被阻止的原因。

Answer 1

不要考虑在没有主键的情况下在InnoDB中使用事务。

并认真考虑在执行 UPDATE ... WHERE ai = Constant 时至少使用 INDEX(a1)。

否则，InnoDB 在“行级锁定”方面会变得相当草率——可能是因为它在没有索引的情况下难以控制每个“行”。