Mysql 数据库锁的一个问题

[huntcool001]

默认隔离级别 可重复读, autocommit=1, 建表:
CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`age` tinyint(3) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB ;


INSERT into test (id,age) values (1,1);



session 1:
update test set age=1 where id=1;


session 2:
update test set id=1 where age =1;





那么 session 1 和 2 是否有可能产生死锁?


session 1 是先锁主键,再锁二级索引. session 2 是先锁二级索引再锁主键.

我在本地上各开了二十个线程来不停 update,没有出现死锁. 为什么呢?

[DonaldY]

不会出现死锁吧。

session2 中，id=1, 值没有改变，影响行数为 0, 即锁的行数为 0 行

[huntcool001]

@DonaldY update ... where... 的过程中肯定要锁行的,只是判断发现不需要改变或者不符合 where 条件的约束,再释放掉. 从常理上来说也是, 不能我一边判断 where 的时候你一遍改数据影响我的 where 判断吧? (update 的查询只会查最近一条数据,不会用 MVCC)

[louettagfh]

RRjibie. InnoDB 的 record 锁只有在 trx commi t 之后才释放.

当一个二级索引列被更新的时候，旧的二级索引记录被标记为删除，同时插入一个新的二级索引记录

[momocraft]

两边都在 transaction 内吗

[huntcool001]

@louettagfh 感谢. 但是你说的是释放和修改. 问题是获取锁的时机, 这两条 update 不同的获取锁的顺序是否会引起死锁?

[louettagfh]

@huntcool001

更新 table 是有 table lock 的, 只是表锁后面会降级为意向锁.

你纠结的问题是先锁 二级索引 还是 主键索引.
对于查询来说你的逻辑是对的，但是修改还是先改聚簇索引，再改二级索引 ( InnoDB 里所有非聚簇索引均为二级索引).

[xsm1890]

这样永远不会发生死锁，个人观点如下：

1.锁粒度为行级锁，跟是先在主键或者二级索引加锁没关系，此处完全构不成互相等待对方资源释放的情况。
2.这样更新持有锁的时间非常非常非常短，想用这种方法产生互相等待基本不可能，基本是个玄学（记得某本 mysql 相关的书劝过读者，别指望用这种方式跑出相互等待）。

[huntcool001]

@xsm1890

我明白 InnoDB 锁的粒度最小就是行了.

但是这种主键和二级索引加锁,可能是某种源码层面的锁? 还是 Innodb 对这种情况已经有了某种特殊处理?

[louettagfh]

针对 RR 级别 非常容易出现死锁. 不过 InnoDB 的死锁检测会回滚其中的一个事务, 我给你举个例子, 依然以你的数据表:

session 1：
create table xxx...;

INSERT into test (id,age) values (1,1);

INSERT into test (id,age) values (2,2);

begin;

update test set age=1 where id=1;


这时候切换到 session 2, 记住 session 1 不要 commit

session 2:
update test set age=2 where id=2;

session 1:
update test set age=2 where id=2;

session 2:
update test set age=1 where id=1;

这时候你就在 session 2 可以看到你想看到的死锁提示.

[maigebaoer]

2pl，你描述的情况怎么都不会死锁。

[xsm1890]

@huntcool001 没看过源码，但是个人觉得再小粒度上的加锁，其实本质上都是一样的；锁粒度越小，分配管理需要消耗的资源越高，现在的锁应该是兼顾性能，资源且够用的结果

[DonaldY]

突然想当然，把修改行和影响行搞混了。

试了下。不会死锁。

session1: (关闭 autocommit)
mysql> update test set age=1 where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

session2:（开着 autocommit ）
mysql> update test set id=1 where age =1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update test set id=1 where age =1;
Query OK, 0 rows affected (4.18 sec)
Rows matched: 1 Changed: 0 Warnings: 0

session1 先执行，没有 commit，再执行 session2, session2 等待锁;
session1 commit 后，session2 也提交成功。

[specita]

我觉得会死锁啊，会竞争 X 锁啊。你开两个事务，start transaction 手动测试就知道了啊。
看之前的回答我还怀疑自己，测试了下：
Trx id counter 101931
Purge done for trx's n:o < 101930 undo n:o < 0 state: running but idle
History list length 11
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 101930, ACTIVE 16 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 13, OS thread handle 19224, query id 835 localhost ::1 root starting
show engine innodb status
---TRANSACTION 101928, ACTIVE 107 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 14, OS thread handle 1736, query id 834 localhost ::1 root Searching rows for update
update user set id = 1 where name = 'aa'
------- TRX HAS BEEN WAITING 10 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 489 page no 3 n bits 72 index PRIMARY of table `orange`.`user` trx id 101928 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

[specita]

lz 你那样测试肯定测试不出来啊，这语句执行那么快，等不到锁超时的

[huntcool001]

@specita

你说的是普通的锁的竞争, 我问的会不会死锁(循环等对方释放锁)

[BugsBunny]

二级索引的叶子节点存储的是主键的值，而不是物理行指针，如果要 update 是不是需要知道物理行指针，所以这里是不是应该会有一次回表查询，这样的话加锁是不是直接加到主键就可以了。
我只是说下我的理解，求指正