V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
huntcool001
V2EX  ›  数据库

Mysql 数据库锁的一个问题

  •  
  •   huntcool001 · 2020-09-08 17:00:45 +08:00 · 1963 次点击
    这是一个创建于 1297 天前的主题,其中的信息可能已经有所发展或是发生改变。
    默认隔离级别 可重复读, 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,没有出现死锁. 为什么呢?
    16 条回复    2020-09-09 17:01:38 +08:00
    DonaldY
        1
    DonaldY  
       2020-09-08 17:04:57 +08:00
    不会出现死锁吧。

    session2 中,id=1, 值没有改变,影响行数为 0, 即锁的行数为 0 行
    huntcool001
        2
    huntcool001  
    OP
       2020-09-08 17:09:53 +08:00
    @DonaldY update ... where... 的过程中肯定要锁行的,只是判断发现不需要改变或者不符合 where 条件的约束,再释放掉. 从常理上来说也是, 不能我一边判断 where 的时候你一遍改数据影响我的 where 判断吧? (update 的查询只会查最近一条数据,不会用 MVCC)
    louettagfh
        3
    louettagfh  
       2020-09-08 17:16:56 +08:00
    RRjibie. InnoDB 的 record 锁只有在 trx commi t 之后才释放.

    当一个二级索引列被更新的时候,旧的二级索引记录被标记为删除,同时插入一个新的二级索引记录
    momocraft
        4
    momocraft  
       2020-09-08 17:17:31 +08:00
    两边都在 transaction 内吗
    huntcool001
        5
    huntcool001  
    OP
       2020-09-08 17:22:26 +08:00
    @louettagfh 感谢. 但是你说的是释放和修改. 问题是获取锁的时机, 这两条 update 不同的获取锁的顺序是否会引起死锁?
    louettagfh
        6
    louettagfh  
       2020-09-08 17:28:11 +08:00
    @huntcool001

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

    你纠结的问题是先锁 二级索引 还是 主键索引.
    对于查询来说你的逻辑是对的,但是修改还是先改聚簇索引,再改二级索引 ( InnoDB 里所有非聚簇索引均为二级索引).
    xsm1890
        7
    xsm1890  
       2020-09-08 17:33:04 +08:00
    这样永远不会发生死锁,个人观点如下:

    1.锁粒度为行级锁,跟是先在主键或者二级索引加锁没关系,此处完全构不成互相等待对方资源释放的情况。
    2.这样更新持有锁的时间非常非常非常短,想用这种方法产生互相等待基本不可能,基本是个玄学(记得某本 mysql 相关的书劝过读者,别指望用这种方式跑出相互等待)。
    huntcool001
        8
    huntcool001  
    OP
       2020-09-08 17:37:32 +08:00
    @xsm1890

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

    但是这种主键和二级索引加锁,可能是某种源码层面的锁? 还是 Innodb 对这种情况已经有了某种特殊处理?
    louettagfh
        9
    louettagfh  
       2020-09-08 17:41:11 +08:00
    针对 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
        10
    maigebaoer  
       2020-09-08 17:47:41 +08:00 via Android
    2pl,你描述的情况怎么都不会死锁。
    xsm1890
        11
    xsm1890  
       2020-09-08 17:54:41 +08:00
    @huntcool001 没看过源码,但是个人觉得再小粒度上的加锁,其实本质上都是一样的;锁粒度越小,分配管理需要消耗的资源越高,现在的锁应该是兼顾性能,资源且够用的结果
    DonaldY
        12
    DonaldY  
       2020-09-08 19:06:04 +08:00
    突然想当然,把修改行和影响行搞混了。

    试了下。不会死锁。

    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
        13
    specita  
       2020-09-09 11:40:24 +08:00
    我觉得会死锁啊,会竞争 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
        14
    specita  
       2020-09-09 11:44:12 +08:00
    lz 你那样测试肯定测试不出来啊,这语句执行那么快,等不到锁超时的
    huntcool001
        15
    huntcool001  
    OP
       2020-09-09 12:24:40 +08:00   ❤️ 1
    @specita

    你说的是普通的锁的竞争, 我问的会不会死锁(循环等对方释放锁)
    BugsBunny
        16
    BugsBunny  
       2020-09-09 17:01:38 +08:00
    二级索引的叶子节点存储的是主键的值,而不是物理行指针,如果要 update 是不是需要知道物理行指针,所以这里是不是应该会有一次回表查询,这样的话加锁是不是直接加到主键就可以了。
    我只是说下我的理解,求指正
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   3311 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 13:47 · PVG 21:47 · LAX 06:47 · JFK 09:47
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.