InnoDB行锁实现方式问题

Question

看唐汉明等人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会自动给涉及数据集加排他锁”，也就意味着第二种方式效果应该跟第一种方式是一样的，为什么结果出现差异呢？请教各位大神。

Answer 1

我在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 TRANSACTIONmysql> 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>

Answer 2

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

Answer 3

贴出 show index from tab_no_index

Answer 4

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

Answer 5

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

Answer 6

不同的版本上，实现有差异吧
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

Answer 7

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

Answer 8

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

Answer 9

-- 一样锁的， 在查锁的查询中，第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

Answer 10

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