数据库

MySQL锁机制

云博小周宇4 Mins Read

MySQL锁机制

MySQL都有什么锁

MySQL有三种锁的级别：页级、表级、行级。

  • 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
  • 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
  • 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

一. get_lock 锁机制的分析

这个锁是应用程序级别的，在不同的mysql会话之间使用。它只是个名字锁

  • 语句

开启一个以key为名的锁

select GET_LOCK(‘key‘, timeOut) from tableName; # key:自定义字符串 timeout:超时时间

关闭该Key的锁

select RELEASE_LOCK(‘key‘) from tableName;
  • 概述
  1. get_lock会按照key来加锁，别的客户端再以同样的key加锁时就加不了了，处于等待状态。
  2. 当调用release_lock来释放上面加的锁或客户端断线了，上面的锁才会释放，其它的客户端才能进来。
  • 例子
  1. # 加锁
  2. select GET_LOCK(‘key‘, 100) from user;
  3. # 解锁
  4. select RELEASE_LOCK(‘key‘) from user;

二.行级锁

由于InnoDB预设是Row-Level Lock(行级锁)，所以只有「明确」的指定主键

共享锁:

SELECT * FROM tableName LOCK IN SHARE MODE;

排它锁:

SELECT * FROM tableName FOR UPDATE;

三.死锁

所谓死锁<DeadLock>: 是指两个或两个以上的进程在执行过程中,

因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.

此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等竺的进程称为死锁进程.

表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB.

死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。

那么对应的解决死锁问题的关键就是：让不同的session加锁有次序

在information_schema下面有三张表:INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS，通过这三张表，可以更简单地监控当前的事务并分析可能存在的问题。

  1. trx_id:InnoDB存储引擎内部唯一的事物ID
  2. trx_status:当前事务的状态
  3. trx_status:事务的开始时间
  4. trx_requested_lock_id：等待事务的锁ID
  5. trx_wait_started：事务等待的开始时间
  6. trx_weight：事务的权重，反应一个事务修改和锁定的行数，当发现死锁需要回滚时，权重越小的值被回滚
  7. trx_mysql_thread_id：MySQL中的进程ID，与 show processlist中的ID值相对应
  8. trx_query：事务运行的 SQL语句

没有提交事务的解决办法

  1.  
  2. 查看当前连接Id（线程Id）
  3. select connection_id();
  4.  
  5. 查询 正在执行(未提交)的事务：
  6. SELECT trx_started,trx_mysql_thread_id FROM information_schema.INNODB_TRX;–这里只查了线程ID和开始时间
  7. SELECT * FROM information_schema.INNODB_TRX\G
  8.  
  9. 通过查看events_statements_current表可看到每一个 session正在执行的sql，哪怕它依旧执行完成了，只是没有提交。
  10. 这里可以看到执行的 SQL语句
  11. select* from performance_schema.events_statements_current\G
  12.  
  13. 查看正在锁的事务
  14. SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
  15.  
  16. 查看等待锁的事务
  17. SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
  18.  
  19. 获取所有处理线程
  20. show processlist;
  21. show full processlist;
  22.  
  23. 杀死指定线程的Id
  24. kill Id
  25.  

经过我好几天反复的测试发现

在PHP(测试版本PHP 7.2.9)的pdo中连接MySQL时发生死锁时后面执行的进程返回Null,事务自动回滚, 不会造成事务僵死.

但是在MySQL-Client(MySQL测试版本5.6.41)中执行,死锁会造成事务僵死.

比如在PHP中我用PDO操作

  1. 我发送一个请求A首先开启事务,查询锁定到了UID1用户的资产,然后sleep(5),去所动UID2的资产
  2. 我发送一个请求B开启事务,查询锁定了UID2用户的资产,然后sleep(5),去所动UID1的资产
  3. 这时候A正确返回,而B的第二个查询查询则返回了一个null
  4. 然后我继续请求,开启一个事务无查询锁定UID1和UID2的资产,没有任何问题.

但是在MySQL-Client中,像上面操作会提示Deadlock found when trying to get lock; try restarting transaction

并且在随后的操作中,在其他窗口包括PHP中取尝试查询锁定该用户,都会一直卡住很久然后提示Deadlock found when trying to get lock; try restarting transaction

不论隔了多久,再次尝试开启事务锁定UID1和UID2都会这个样子.

当然以上测试我都没有commit或rollback过.当然这是因为我一直没有关闭事务,一直占用着这两个用户.实际我们用的时候不管成功还是失败我们都要提交一下事务.

如果一个连接中开启事务切未显示提交或回滚，在不考虑其他因素的前提下，确实应该是只有在连接断开的时候才会回滚。

如果出现锁等待，则是根据innodb_rollback_on_timeout参数进行下一步动作，如果此参数是off,则会回滚上一条语句，不会提交或回滚，直到显示提交或回滚（事务一直存在，直到连接断开）。

如果此参数是on,在超时后会回滚整个事务。

例如:

将MySQL-client开两个窗口

执行顺序

  1. 执行下面两个两个部分的begin
  2. 执行下面两个第一部分和第二部分的第一个SQL和第二个SQL(按顺序)即两者的第一个for udpate 和update操作.
  3. 执行SELECT trx_started,trx_mysql_thread_id FROM information_schema.INNODB_TRX;可以看到有两个事务
  4. 执行下面两个第一部分和第二部分的第二个SQL和第二个SQL(按顺序)即两者的第二个for update
  5. 执行SELECT trx_started,trx_mysql_thread_id FROM information_schema.INNODB_TRX;可以看到只有一个事务存活了.
  6. 执行最后一句,commit.
  7. 执行SELECT trx_started,trx_mysql_thread_id FROM information_schema.INNODB_TRX;发现没有任何事务了.

这时候会发现,MySQL返回错误信息

Deadlock found when trying to get lock; try restarting transaction

执行第一部分SQL

  1. begin
  2.  
  3. select `bac_balance` from user_finance where uid=1 for update
  4. update `user_finance` set `bac_balance`=`bac_balance`+100 where uid=1;
  5.  
  6. select `bac_balance` from user_finance where uid=2 for update
  7.  
  8. commit
  9.  

第二部分SQL

  1. begin
  2.  
  3. select `bac_balance` from user_finance where uid=2 for update
  4. update `user_finance` set `bac_balance`=`bac_balance`+100 where uid=2;
  5.  
  6.  
  7. select `bac_balance` from user_finance where uid=1 for update
  8.  
  9. commit

有人说, 并不会导致死锁等待, 而是innodb会将执行自动回滚.

InnoDB会在死锁的时候自动回滚影响小的事务(我的测试结果是回滚后面执行的事务).

但是我们在开发的时候肯定要避免死锁这种情况,

上面的运行结果 第一部分运行成功.而第二部分提示死锁且事务回滚.

这是因为,在第一部分的最后一句执行锁UID2的时候会一直等待,等到innodb_lock_wait_timeout设置的时间超时,

而这时候立刻执行第二部分的最后一句,去锁UID2,会马上返回死锁提示,并且回滚事务.所以第一部分成功了.

如果成功的这个事务不提交的话,连接关闭的时候会回滚事务.

工作中遇到的案例

实际上有上百个撮合引擎, 这里假设有两个A&B错误引擎

假设平台对UID 1,2,3 用户进行进行撮合.两个撮合引擎同时运行.

  • A撮合引擎: 从UID为1的用户扣除100USDT,分别为UID2和UID3的用户加20USDT.
  • B撮合引擎: 从UID为3的用户扣除10BTC,分别为UID1和UID2的用户各加5BTC.

理论上,瞬间就会形成死锁(同时开始资产操作是有可能的);

  1. A撮合首先对UID1用户的资产进行select *from finance where uid=1 for update加锁
  2. 同时B撮合也对UID3用户的资产进行select *from finance where uid=3 for update加锁
  3. 这时候A撮合继续开始加锁UID2和UID3,B撮合继续加锁UID1和UID2
  4. 这时候A撮合发现UID3已被锁定,所以会等待B执行完;B撮合发现UID1被锁定,等待A运行完.
  5. 这时候撮合A和撮合B就会进入锁等待状态, 相互等待对方的锁执行完毕.形成死锁

上面这个情况是比较常见的,当时我们想到了两种方案.

    1. 在指定for update 加锁时,对加锁用户进行排序.

从大到小或从小到大都可以.但是要所有撮合都保持顺序一致.这样在加锁时就不会出现反方向加锁导致冲突的情况.

比如,我从1-10加锁,你从10-1加锁,必然会死锁.而我们都从1-10 加锁的话.必然不会有这种情况

    1. 在指定for update 加锁时使用in()将所有要加锁的用户,一次性加锁.

Select * from finance where id in (1,2,3) for update

在in里面的列表值mysql是会自动从小到大排序，加锁也是一条条从小到大加的锁

百度来的案例

泽锦

https://www.cnblogs.com/zejin2008/p/5262751.html

案例一：

  1. 需求：将投资的钱拆成几份随机分配给借款人。
  2.  
  3. 起初业务程序思路是这样的：
  4.  
  5. 投资人投资后，将金额随机分为几份，然后随机从借款人表里面选几个，然后通过一条条 select for update 去更新借款人表里面的余额等。
  6.  
  7.  
  8.  
  9. 抽象出来就是一个session通过 for循环会有几条如下的语句：
  10.  
  11. Select * from xxx where id=‘随机id‘ for update
  12.  
  13.  
  14.  
  15. 基本来说，程序开启后不一会就死锁。
  16.  
  17. 这可以是说最经典的死锁情形了。
  18.  
  19.  
  20.  
  21. 例如两个用户同时投资，A用户金额随机分为 2份，分给借款人1，2
  22.  
  23. B用户金额随机分为 2份，分给借款人2，1
  24.  
  25. 由于加锁的顺序不一样，死锁当然很快就出现了。
  26.  
  27.  
  28.  
  29. 对于这个问题的改进很简单，直接把所有分配到的借款人直接一次锁住就行了。
  30.  
  31. Select * from xxx where id in (xx,xx,xx) for update
  32.  
  33. 在 in里面的列表值mysql是会自动从小到大排序，加锁也是一条条从小到大加的锁
  34.  
  35.  
  36. 例如（以下会话id为主键）：
  37.  
  38. Session1:
  39.  
  40. mysql> select * from t3 where id in (8,9) for update;
  41.  
  42. +—-+——–+——+———————+
  43.  
  44. | id | course | name | ctime |
  45.  
  46. +—-+——–+——+———————+
  47.  
  48. | 8 | WA | f | 2016-03-02 11:36:30 |
  49.  
  50. | 9 | JX | f | 2016-03-01 11:36:30 |
  51.  
  52. +—-+——–+——+———————+
  53. rows in set (0.04 sec)
  54.  
  55.  
  56.  
  57.  
  58.  
  59. Session2:
  60.  
  61. select * from t3 where id in (10,8,5) for update;
  62.  
  63. 锁等待中……
  64.  
  65. 其实这个时候id= 10这条记录没有被锁住的，但id=5的记录已经被锁住了，锁的等待在id=8的这里。
  66.  
  67.  
  68.  
  69. 不信请看
  70.  
  71. Session3:
  72.  
  73. mysql> select * from t3 where id=5 for update;
  74.  
  75. 锁等待中
  76.  
  77.  
  78.  
  79. Session4:
  80.  
  81. mysql> select * from t3 where id=10 for update;
  82.  
  83. +—-+——–+——+———————+
  84.  
  85. | id | course | name | ctime |
  86.  
  87. +—-+——–+——+———————+
  88.  
  89. | 10 | JB | g | 2016-03-10 11:45:05 |
  90.  
  91. +—-+——–+——+———————+
  92. row in set (0.00 sec)
  93.  
  94.  
  95.  
  96. 在其它session中id= 5是加不了锁的，但是id=10是可以加上锁的。

案例2：

在开发中，经常会做这类的判断需求：根据字段值查询（有索引），如果不存在，则插入；否则更新。

  1.  
  2. 以id为主键为例，目前还没有id=22的行
  3.  
  4. Session1:
  5.  
  6. select * from t3 where id=22 for update;
  7.  
  8. Empty set (0.00 sec)
  9.  
  10.  
  11.  
  12. session2:
  13.  
  14. select * from t3 where id=23 for update;
  15.  
  16. Empty set (0.00 sec)
  17.  
  18.  
  19.  
  20. Session1:
  21.  
  22. insert into t3 values(22,‘ac‘,‘a‘,now());
  23.  
  24. 锁等待中……
  25.  
  26.  
  27.  
  28. Session2:
  29.  
  30. insert into t3 values(23,‘bc‘,‘b‘,now());
  31.  
  32. ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
  33.  
  34.  
  35. 当对存在的行进行锁的时候(主键)，mysql就只有行锁。
  36.  
  37. 当对未存在的行进行锁的时候(即使条件为主键)，mysql是会锁住一段范围（有gap锁）
  38.  
  39.  
  40.  
  41.  
  42.  
  43. 锁住的范围为：
  44.  
  45. (无穷小或小于表中锁住id的最大值，无穷大或大于表中锁住id的最小值)
  46.  
  47.  
  48.  
  49. 如：如果表中目前有已有的id为（11 ， 12）
  50.  
  51. 那么就锁住（12，无穷大）
  52.  
  53. 如果表中目前已有的id为（11 ， 30）
  54.  
  55. 那么就锁住（11，30）
  56.  
  57.  
  58.  
  59. 对于这种死锁的解决办法是：
  60.  
  61. insert into t3(xx,xx) on duplicate key update `xx`=‘XX‘;
  62.  
  63.  
  64.  
  65. 用mysql特有的语法来解决此问题。因为 insert语句对于主键来说，插入的行不管有没有存在，都会只有行锁。
  66.  
  67.  
  68.  
  69.  

案例3：

直接上情景：

  1. mysql> select * from t3 where id=9 for update;
  2.  
  3. +—-+——–+——+———————+
  4.  
  5. | id | course | name | ctime |
  6.  
  7. +—-+——–+——+———————+
  8.  
  9. | 9 | JX | f | 2016-03-01 11:36:30 |
  10.  
  11. +—-+——–+——+———————+
  12. row in set (0.00 sec)
  13.  
  14.  
  15.  
  16. Session2:
  17.  
  18. mysql> select * from t3 where id<20 for update;
  19.  
  20. 锁等待中
  21.  
  22.  
  23.  
  24. Session1:
  25.  
  26. mysql> insert into t3 values(7,‘ae‘,‘a‘,now());
  27.  
  28. ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

转载于:https://my.oschina.net/chinaliuhan/blog/3065290