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
www.2cto.com
現在只在列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;
www.2cto.com
第二個會等待超時
[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.
