V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
hhhhhh123
V2EX  ›  程序员

mysql 悲观锁 机制 请教

  •  
  •   hhhhhh123 · 2022-11-28 14:55:42 +08:00 · 1947 次点击
    这是一个创建于 486 天前的主题,其中的信息可能已经有所发展或是发生改变。

    问题 1.:悲观锁 运行机制 目前无法理解的是 假设 pk_money= 1 , 我每次运行都会+ 1 但是我没提交啊
    不知道为什么他会每次 + 1 ,
    不知道是我 sql 语法问题 还是啥 ?
    难道每次运行的时候 会把没有提及的 sql 自动提交? 但是我已经关闭了自动提交 。

    问题 2: 我想把 set pk_money=pk_money+1 这个 pk_money 等于上面的查询结果 然后在+ 1 这个应该怎么写啊? 同时是悲观锁。 怎么把 下面的 select 和 update 合并。

    问题 3: 我不提交的话,我重新开个窗口 单独去运行

    update user_basic_info set pk_money=pk_money+1 where uid=123456803;
    

    是会被阻塞到超时。 说明这个悲观锁是起作用了。 但是不知道怎么解释第一个问题

    set autocommit=0;
    start transaction;
    select `pk_money` from user_basic_info where uid=123456803 for update ;
    update user_basic_info set pk_money=pk_money+1 where uid=123456803;
    --  COMMIT;
    

    不会放图片。。。。

    set autocommit=0
    > OK
    > Time: 0.074s
    
    
    start transaction
    > OK
    > Time: 0.077s
    
    
    select `pk_money` from user_basic_info where uid=123456803 for update
    > Affected rows: 0
    > Time: 0.071s
    
    
    update user_basic_info set pk_money=pk_money+1 where uid=123456803
    > Affected rows: 1
    > Time: 0.074s
    
    
    --  COMMIT;
    > OK
    > Time: 0.071s
    
    
    44 条回复    2022-12-01 10:35:10 +08:00
    rqxiao
        1
    rqxiao  
       2022-11-28 15:02:44 +08:00
    mysql update 语句不是自带行锁吗(如果 where 的条件有索引的话)
    hhhhhh123
        2
    hhhhhh123  
    OP
       2022-11-28 15:04:08 +08:00
    @rqxiao 主键就是 uid
    hhhhhh123
        3
    hhhhhh123  
    OP
       2022-11-28 15:05:09 +08:00
    @rqxiao 自带这个问题我不是很清楚,sql 目前只会写 curd 。目前在接触更高级的
    rqxiao
        4
    rqxiao  
       2022-11-28 15:05:50 +08:00
    set autocommit=0;
    start transaction;
    update user_basic_info set pk_money=pk_money+1 where uid=123456803;
    COMMIT;

    这样和你加了一句 for update 是一样的把
    hhhhhh123
        5
    hhhhhh123  
    OP
       2022-11-28 15:08:20 +08:00
    @rqxiao 结果是一样的, 但是把 commit 注释 , 在窗口一直运行重复的代码, 结果会自动+=1 。 这个问题是为什么?
    hhhhhh123
        6
    hhhhhh123  
    OP
       2022-11-28 15:10:09 +08:00
    @rqxiao 顺便请教一下。 为啥网上几乎所有的教程都是要写一个 select 语法 + for update 。
    rqxiao
        7
    rqxiao  
       2022-11-28 15:15:16 +08:00
    @hhhhhh123 只是在当前事物看到是加了 1 吧(虽然当前事物还没提交)。新开个会话,查看的话还是原来的值。
    hhhhhh123
        8
    hhhhhh123  
    OP
       2022-11-28 15:16:35 +08:00
    百度了一下 个人感觉很像是 脏读。
    事务并发调度的问题
    1. 脏读( dirty read ):A 事务读取 B 事务尚未提交的更改数据,并在这个数据基础上操作。如果 B 事务回滚,那么 A 事务读到的数据根本不是合法的,称为脏读。在 oracle 中,由于有 version 控制,不会出现脏读。
    2. 不可重复读( unrepeatable read ):A 事务读取了 B 事务已经提交的更改(或删除)数据。比如 A 事务第一次读取数据,然后 B 事务更改该数据并提交,A 事务再次读取数据,两次读取的数据不一样。
    3. 幻读( phantom read ):A 事务读取了 B 事务已经提交的新增数据。注意和不可重复读的区别,这里是新增,不可重复读是更改(或删除)。这两种情况对策是不一样的,对于不可重复读,只需要采取行级锁防止该记录数据被更改或删除,然而对于幻读必须加表级锁,防止在这个表中新增一条数据。
    4.第一类丢失更新:A 事务撤销时,把已提交的 B 事务的数据覆盖掉。
    5.第二类丢失更新:A 事务提交时,把已提交的 B 事务的数据覆盖掉。
    xhinliang
        9
    xhinliang  
       2022-11-28 15:19:39 +08:00
    问题 1.:悲观锁 运行机制 目前无法理解的是 假设 pk_money= 1 , 我每次运行都会+ 1 但是我没提交啊
    不知道为什么他会每次 + 1 ,
    ----
    你虽然没提交,但是在你的 transaction 内已经可见了,没提交只是别的 transaction 内不可见而已。
    不知道这样能理解么?
    hhhhhh123
        10
    hhhhhh123  
    OP
       2022-11-28 15:19:56 +08:00
    @rqxiao 新会话就是 + 1
    hhhhhh123
        11
    hhhhhh123  
    OP
       2022-11-28 15:21:46 +08:00
    @xhinliang 数据在数据库中确确实实的变化的 += 1
    rqxiao
        12
    rqxiao  
       2022-11-28 15:24:13 +08:00
    @hhhhhh123 那应该就是你发的 mysql 隔离级别为 read uncommitted 读未提交 (脏读)
    xhinliang
        13
    xhinliang  
       2022-11-28 15:24:44 +08:00
    为啥网上几乎所有的教程都是要写一个 select 语法 + for update 。
    -----
    这个不是必须的。在业务上先「写一个 select 语法 + for update 」一般是先锁定一下这些行的数据,同时可以读到这些数据当前值,你上来就 update 也没问题的。
    xhinliang
        14
    xhinliang  
       2022-11-28 15:25:43 +08:00
    那你看看当前的隔离级别呢?不会是 read uncommitted 吧?
    hhhhhh123
        15
    hhhhhh123  
    OP
       2022-11-28 15:26:41 +08:00
    @rqxiao 还有个疑问, 就算是脏读 也不应该更新数据到数据库吧? 更新到数据库那不就持久化了?
    xhinliang
        16
    xhinliang  
       2022-11-28 15:28:42 +08:00
    还有个疑问, 就算是脏读 也不应该更新数据到数据库吧? 更新到数据库那不就持久化了?
    ------------
    什么叫更新到数据库,所有的写入都是更新到数据库的呀,只是别的 session 能不能看到而已,这不就是隔离级别的基本概念么?
    hhhhhh123
        17
    hhhhhh123  
    OP
       2022-11-28 15:29:28 +08:00
    @xhinliang ``` show variables like '%tx_isolation%';``` 返回结果是空 啥也没有 ,这种是什么
    rqxiao
        18
    rqxiao  
       2022-11-28 15:29:52 +08:00
    @hhhhhh123 一般有事物功能的话没提交确实不会更新到数据库。
    rqxiao
        19
    rqxiao  
       2022-11-28 15:31:29 +08:00
    你是 MyISAM 引擎吗,MyISAM 引擎不支持数事物,不过还没用过 MyISAM
    hhhhhh123
        20
    hhhhhh123  
    OP
       2022-11-28 15:32:16 +08:00
    @rqxiao
    @xhinliang
    ```
    select @@transaction_isolation;
    select @@global.transaction_isolation;
    ```
      结果是 REPEATABLE-READ
    hhhhhh123
        21
    hhhhhh123  
    OP
       2022-11-28 15:35:12 +08:00
    是 InnoDB
    zhanglintc
        22
    zhanglintc  
       2022-11-28 15:57:11 +08:00
    @hhhhhh123 #20 你这个现象倒是很神奇。我`Distrib 5.5.60-MariaDB`的版本,反正没能再现出来你的现象。如果是`REPEATABLE-READ`状态,没有 commit 之前,其他窗口是看不到这个变更的。

    -----

    > 问题 2: 我想把 set pk_money=pk_money+1 这个 pk_money 等于上面的查询结果 然后在+ 1 这个应该怎么写啊? 同时是悲观锁。 怎么把 下面的 select 和 update 合并。

    这个问题没看懂。
    感觉你这个需求直接用 update 命令就行了:`set pk_money=pk_money+1`。不需要用到`select`命令,没有合并一说。
    hhhhhh123
        23
    hhhhhh123  
    OP
       2022-11-28 17:59:35 +08:00
    @zhanglintc 因为网上的教程都是先 select 在 update 我以为这是必要流程
    la2la
        24
    la2la  
       2022-11-28 21:44:14 +08:00
    1. MySQL 的存储引擎
    la2la
        25
    la2la  
       2022-11-28 21:46:24 +08:00
    2. 设置的隔离级别

    ps: 不要根据现象去推测原理,这种现象或许仅仅是原理的一种表现而已。现象是用来验证原理的。网上有关于 Innodb 引擎的源码级别的教程,看看锁的具体实现呗
    zhanglintc
        26
    zhanglintc  
       2022-11-28 22:02:33 +08:00
    @hhhhhh123 #10 就这一点来说,我表示怀疑。感觉没有可能新窗口中会看到未 commit 的提交。
    zhanglintc
        27
    zhanglintc  
       2022-11-28 22:03:30 +08:00
    对 10 楼的回复“新会话就是 + 1”表示怀疑
    hhhhhh123
        28
    hhhhhh123  
    OP
       2022-11-29 09:10:50 +08:00
    @zhanglintc 主要是不会贴图片, 不然我就吧图片贴上来。 确确实实+1 。 不过代码就不会。 我也很好奇。 理论上确实不应该拿到未提交数据且更新到数据中了
    Dlin
        29
    Dlin  
       2022-11-29 09:17:51 +08:00   ❤️ 1
    如果真有这种问题,肯定就不是你的问题了。
    zhanglintc
        30
    zhanglintc  
       2022-11-29 11:17:32 +08:00
    @hhhhhh123 #28 图片要传到某个图床,然后在这边贴链接才行。
    raysonlu
        31
    raysonlu  
       2022-11-29 15:55:21 +08:00
    关于“新会话就是 + 1”也表示怀疑,建议再认真测试一次,在 InnoDB 引擎的表,执行“不会放图片。。。。”后面的代码块的“COMMIT”之前,再确保开了新的会话,直接执行 select (不要 for update ),能把图贴上来,或者语句+结果文字贴上来就行。这种试验,已经给多位新入职同事科普过了。
    hhhhhh123
        32
    hhhhhh123  
    OP
       2022-11-29 17:39:59 +08:00
    @raysonlu
    session1 : SELECT pk_money FROM user_basic_info WHERE uid = 123456803; # pk_money= 496

    session2:
    ```
    BEGIN;
    set autocommit=0;
    select `pk_money` from user_basic_info where uid=123456803 for update ;
    update user_basic_info set pk_money=pk_money+1 where uid=123456803;
    # 这里没有 commit
    ```
    结果
    ```
    BEGIN
    > OK
    > Time: 0.047s


    set autocommit=0
    > OK
    > Time: 0.045s


    select `pk_money` from user_basic_info where uid=123456803 for update
    > Affected rows: 0
    > Time: 0.042s


    update user_basic_info set pk_money=pk_money+1 where uid=123456803
    > Affected rows: 1
    > Time: 0.044s

    ```
    再次运行 session1
    session1 : SELECT pk_money FROM user_basic_info WHERE uid = 123456803; # pk_money= 497
    bruce0
        33
    bruce0  
       2022-11-29 18:38:55 +08:00
    不知道能不能对你起到帮助 https://www.jianshu.com/p/fe708aad6113
    zhanglintc
        34
    zhanglintc  
       2022-11-29 21:49:59 +08:00
    @hhhhhh123 #32 你这俩 session ,是同一个 GUI 工具里的两个不同的 tab 吗?
    zhanglintc
        35
    zhanglintc  
       2022-11-29 22:05:08 +08:00
    这俩“session”里分别执行`select CONNECTION_ID()`感觉会是同一个值?
    Dlin
        36
    Dlin  
       2022-11-29 23:07:52 +08:00
    建议用命令行测试
    hhhhhh123
        37
    hhhhhh123  
    OP
       2022-11-30 09:22:56 +08:00
    @zhanglintc 是啊,navicat 工具 不同的窗口
    hhhhhh123
        38
    hhhhhh123  
    OP
       2022-11-30 09:26:45 +08:00
    @zhanglintc 不一样 一个 2744 一个 2741
    raysonlu
        39
    raysonlu  
       2022-11-30 09:35:20 +08:00
    @hhhhhh123 set autocommit=0 在 begin 前
    hhhhhh123
        40
    hhhhhh123  
    OP
       2022-11-30 09:39:11 +08:00
    @Dlin
    你看看 我这语法有问题吗? 应该是这样写的吧?

    mysql> begin; set autocommit=0; SELECT pk_money FROM user_basic_info WHERE uid = 123456803 for update ; UPDATE user_basic_info set pk_money=pk_money+1 where uid =123456803;

    Query OK, 0 rows affected (0.00 sec)

    Query OK, 0 rows affected (0.00 sec)

    +----------+
    | pk_money |
    +----------+
    | 498 |
    +----------+
    1 row in set (0.00 sec)

    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1 Changed: 1 Warnings: 0

    mysql> select pk_money from user_basic_info where uid= 123456803;
    +----------+
    | pk_money |
    +----------+
    | 499 |
    +----------+
    1 row in set (0.00 sec)

    mysql>
    hhhhhh123
        41
    hhhhhh123  
    OP
       2022-11-30 09:39:55 +08:00
    @Dlin mysql> select pk_money from user_basic_info where uid= 123456803;
    +----------+
    | pk_money |
    +----------+
    | 499 |
    +----------+
    1 row in set (0.00 sec)

    mysql> begin; set autocommit=0; SELECT pk_money FROM user_basic_info WHERE uid = 123456803 for update ; UPDATE user_basic_info set pk_money=pk_money+1 where uid =123456803;
    Query OK, 0 rows affected (0.00 sec)

    Query OK, 0 rows affected (0.00 sec)

    +----------+
    | pk_money |
    +----------+
    | 499 |
    +----------+
    1 row in set (0.00 sec)

    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1 Changed: 1 Warnings: 0

    mysql> select pk_money from user_basic_info where uid= 123456803;
    +----------+
    | pk_money |
    +----------+
    | 500 |
    +----------+
    1 row in set (0.00 sec)

    mysql>
    hhhhhh123
        42
    hhhhhh123  
    OP
       2022-11-30 09:41:35 +08:00
    @raysonlu 这个是一样的
    mysql> select pk_money from user_basic_info where uid= 123456803;
    +----------+
    | pk_money |
    +----------+
    | 500 |
    +----------+
    1 row in set (0.00 sec)

    mysql> set autocommit=0; begin; SELECT pk_money FROM user_basic_info WHERE uid = 123456803 for update ; UPDATE user_basic_info set pk_money=pk_money+1 where uid =123456803;
    Query OK, 0 rows affected (0.00 sec)

    Query OK, 0 rows affected (0.00 sec)

    +----------+
    | pk_money |
    +----------+
    | 500 |
    +----------+
    1 row in set (0.00 sec)

    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1 Changed: 1 Warnings: 0

    mysql> select pk_money from user_basic_info where uid= 123456803;
    +----------+
    | pk_money |
    +----------+
    | 501 |
    +----------+
    1 row in set (0.00 sec)

    mysql>
    hhhhhh123
        43
    hhhhhh123  
    OP
       2022-11-30 09:48:20 +08:00
    @raysonlu @Dlin @raysonlu @all 兄弟们,算了 不搞了。 谢谢各位的解惑。 反正 代码中测试 commit 是没有问题的 。
    raysonlu
        44
    raysonlu  
       2022-12-01 10:35:10 +08:00
    @hhhhhh123 技术在于折腾,不要放弃。有点怀疑是不是数据库工具没有帮你真正开到第二个会话,可以在 update 之后,在另一个会话界面执行一下“SELECT pk_money FROM user_basic_info WHERE uid = 123456803 for update ;” 正常某个会话锁了某行,另一个会话再尝试加锁,就会出现阻塞等待,可以用这一点判断一下是否在新的会话中。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   1044 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 27ms · UTC 19:17 · PVG 03:17 · LAX 12:17 · JFK 15:17
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.