InnoDB行锁实现方式问题

[leopardaa521]

看唐汉明等人2008年出版的的《深入浅出MySQL》一书，第277页“InnoDB行锁实现方式”中讲到，行锁是通过给索引上的索引项加锁来实现的，并且创建了一个没有索引的表做示例；

# 建表语句
CREATE TABLE `tab_no_index` (
	`id` INT(11) NULL DEFAULT NULL,
	`name` VARCHAR(10) NULL DEFAULT NULL
)
ENGINE=InnoDB;

# 插如（1, '1'）、（2, '2'）、（3, '3'）、（4, '4'）四条数据的SQL省略

但是我在验证时发现，SQL语句必须显式地带for update才能出现书中所说的效果：

# 会话1
set autocommit=0;

select * from tab_no_index a where id = 1 for update;

# 不提交

# 会话2
set autocommit=0;
# 下面的语句无法执行，需等待会话1提交。
select * from tab_no_index a where id = 2 for update;

如果直接按下面这种写法就没有效果：

# 会话1
set autocommit=0;

update tab_no_index a 
set a.name = 'abc'
where a.id = 1;

# 不提交

# 会话2
set autocommit=0;

# 下面这个update语句可以执行
update tab_no_index a 
set a.name = 'abc'
where a.id = 2;

而在书中第274也第二段指出：“对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁”，也就意味着第二种方式效果应该跟第一种方式是一样的，为什么结果出现差异呢？请教各位大神。

[leopardaa521]

可能我没表达清楚，书上的意思是说没有索引，行锁就失效了，就变成了表锁。 这样虽然是更新id=1的列，但是会影响更新id=2的操作，对其造成阻塞。

[ACMAIN_CHM]

贴出 show index from tab_no_index

[LongRui888]

我在5.6版本上做了一个实验，不管是for update，还是直接update都会阻塞住：

#会话1
mysql> SET autocommit=0;
QUERY OK, 0 ROWS affected (0.03 sec)

#1
mysql> SELECT * FROM tab_no_index a WHERE id = 1 FOR UPDATE;
+------+------+
| id   | NAME |
+------+------+
|    1 | 1    |
+------+------+
1 ROW IN SET (0.03 sec)

mysql> COMMIT;
QUERY OK, 0 ROWS affected (0.03 sec)

#2
mysql> UPDATE tab_no_index a
    -> SET a.name = 'abc'
    -> WHERE a.id = 1;
QUERY OK, 1 ROW affected (6.13 sec)
ROWS matched: 1  CHANGED: 1  WARNINGS: 0


#会话2
mysql> SET autocommit=0;
QUERY OK, 0 ROWS affected (0.03 sec)

#1
mysql> SELECT * FROM tab_no_index a WHERE id = 2 FOR UPDATE;
ERROR 1205 (HY000): LOCK WAIT timeout exceeded; try restarting TRANSACTION

mysql> COMMIT ;
QUERY OK, 0 ROWS affected (0.03 sec)

#2
mysql> UPDATE tab_no_index a
    -> SET a.name = 'abc'
    -> WHERE a.id = 2;
ERROR 1205 (HY000): LOCK WAIT timeout exceeded; try restarting TRANSACTION
mysql>

[LongRui888]

引用 2 楼 leopardaa521 的回复:

可能我没表达清楚，书上的意思是说没有索引，行锁就失效了，就变成了表锁。 这样虽然是更新id=1的列，但是会影响更新id=2的操作，对其造成阻塞。

那肯定的，不仅是mysql，sql server也是如此。 要修改数据，首先要找到数据，而如果没有索引，就会直接锁住表的数据，就会锁住其他的会话。 所以，如果你看innodb的最佳实践里，一定会要你创建主键索引，如果你没有主键，可以用一个代理键，也就是自增列实现。

[mgwilliam]

楼主的疑问解决了吗？我也遇到了同样的问题。有点想不明白。为什么for update会锁表，而单独执行update则没有问题呢？

[zjcxc]

sql server 里面不一定是表锁，表级别通常只有意向锁，真正锁定的通常只是更新的数据，除非数据量大导致锁升级， 可以用 profiler 跟踪 Lock:Acquired / Lock:Released 两个事件验证加锁和释放锁

[mgwilliam]

引用 6 楼 zjcxc 的回复:

不同的版本上，实现有差异吧 mysql> select @@version; +------------+ | @@version | +------------+ | 5.7.18-log | +------------+ 1 row in set (0.00 sec) mysql> show create table tab_no_index\G *************************** 1. row *************************** Table: tab_no_index Create Table: CREATE TABLE `tab_no_index` ( `id` int(11) DEFAULT NULL, `name` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> select * from tab_no_index; +------+------+ | id | name | +------+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | +------+------+ 4 rows in set (0.00 sec) ----------------------------------------------------------------------------------- 测试 UPDATE 发现确实锁了，查询锁的信息 SELECT TRX.trx_isolation_level as transaction_level, LX.lock_mode, LX.lock_type, LX.lock_table, LX.lock_index FROM information_schema.innodb_trx TRX INNER JOIN information_schema.innodb_locks LX ON LX.lock_trx_id = TRX.trx_id 结果，行锁（SELECT FOR UPDATE一样）： ---------------------------------------------------------------------------------------------------------------------- REPEATABLE READ X RECORD `tempdb`.`tab_no_index` GEN_CLUST_INDEX REPEATABLE READ X RECORD `tempdb`.`tab_no_index` GEN_CLUST_INDEX

你的mysql隔离级别应该是repeatable-read，所以才会锁。当你把隔离级别调成READ-COMMITTED的时候，两个会话都是for update会锁掉。但是两个会话是update语句的时候就不会锁 。

[zjcxc]

-- 一样锁的， 在查锁的查询中，第1列就是当前的事务隔离级别（ mysql 默认是 repeatable-read，所以通常不特别说明都是基于这个隔离级别） READ COMMITTED X RECORD `tempdb`.`tab_no_index` GEN_CLUST_INDEX READ COMMITTED X RECORD `tempdb`.`tab_no_index` GEN_CLUST_INDEX

[zjcxc]

对于满足条件的记录，更新 或者 FOR UPDATE 跟事务隔离级别没什么关系，满足条件的记录始终会加 X 锁，不同的是加锁范围的在不同的隔离级别下可能会有差异

[zjcxc]

不同的版本上，实现有差异吧 mysql> select @@version; +------------+ | @@version | +------------+ | 5.7.18-log | +------------+ 1 row in set (0.00 sec) mysql> show create table tab_no_index\G *************************** 1. row *************************** Table: tab_no_index Create Table: CREATE TABLE `tab_no_index` ( `id` int(11) DEFAULT NULL, `name` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> select * from tab_no_index; +------+------+ | id | name | +------+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | +------+------+ 4 rows in set (0.00 sec) ----------------------------------------------------------------------------------- 测试 UPDATE 发现确实锁了，查询锁的信息 SELECT TRX.trx_isolation_level as transaction_level, LX.lock_mode, LX.lock_type, LX.lock_table, LX.lock_index FROM information_schema.innodb_trx TRX INNER JOIN information_schema.innodb_locks LX ON LX.lock_trx_id = TRX.trx_id 结果，行锁（SELECT FOR UPDATE一样）： ---------------------------------------------------------------------------------------------------------------------- REPEATABLE READ X RECORD `tempdb`.`tab_no_index` GEN_CLUST_INDEX REPEATABLE READ X RECORD `tempdb`.`tab_no_index` GEN_CLUST_INDEX