mysql InnoDB行锁的一点体会_MySQL
程序员文章站
2023-12-23 08:53:28
...
bitsCN.com
mysql InnoDB行锁的一点体会 表结构如下 Sql代码 CREATE TABLE `20121015_t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) NOT NULL, `b` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `20121015_t_a` (`a`) ) ENGINE=InnoDB 现在只在列a上有索引 在两个客户端分别执行 Sql代码 update 20121015_t set b=4 where a=1 and b=3; Sql代码 update 20121015_t set b=2 where a=1 and b=1; 第二个会等待超时 [Err] 1205 - Lock wait timeout exceeded; try restarting transaction 原来InnoDB不是只在最终要更新的行上加锁,而是在被扫描过的所有行上加锁.也就是说, 如果执行update 20121015_t set b=2 where b=1;其实表里所有的行都被加锁了.
他们官方文档说明如下 A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned. bitsCN.com
mysql InnoDB行锁的一点体会 表结构如下 Sql代码 CREATE TABLE `20121015_t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) NOT NULL, `b` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `20121015_t_a` (`a`) ) ENGINE=InnoDB 现在只在列a上有索引 在两个客户端分别执行 Sql代码 update 20121015_t set b=4 where a=1 and b=3; Sql代码 update 20121015_t set b=2 where a=1 and b=1; 第二个会等待超时 [Err] 1205 - Lock wait timeout exceeded; try restarting transaction 原来InnoDB不是只在最终要更新的行上加锁,而是在被扫描过的所有行上加锁.也就是说, 如果执行update 20121015_t set b=2 where b=1;其实表里所有的行都被加锁了.
他们官方文档说明如下 A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned. bitsCN.com