MySQL锁机制

最新推荐文章于 2024-09-21 11:30:16 发布

chuanguan1820

最新推荐文章于 2024-09-21 11:30:16 发布

阅读量198

点赞数

文章标签：数据库 php

原文链接：https://my.oschina.net/chinaliuhan/blog/3065290

版权

MySQL锁机制

MySQL都有什么锁

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

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

一. get_lock 锁机制的分析

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

语句

开启一个以key为名的锁

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

关闭该Key的锁

select RELEASE_LOCK('key') from tableName;

概述

get_lock会按照key来加锁，别的客户端再以同样的key加锁时就加不了了，处于等待状态。
当调用release_lock来释放上面加的锁或客户端断线了，上面的锁才会释放，其它的客户端才能进来。

例子

# 加锁
select  GET_LOCK('key', 100) from user;
# 解锁
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，通过这三张表，可以更简单地监控当前的事务并分析可能存在的问题。

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

没有提交事务的解决办法


查看当前连接Id（线程Id）
select connection_id();

查询 正在执行(未提交)的事务：
SELECT trx_started,trx_mysql_thread_id FROM information_schema.INNODB_TRX;--这里只查了线程ID和开始时间
SELECT * FROM information_schema.INNODB_TRX\G

通过查看events_statements_current表可看到每一个session正在执行的sql，哪怕它依旧执行完成了，只是没有提交。
这里可以看到执行的SQL语句
select* from performance_schema.events_statements_current\G

查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 

查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

获取所有处理线程
show processlist;
show full processlist;

杀死指定线程的Id
kill Id

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

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

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

比如在PHP中我用PDO操作

我发送一个请求A首先开启事务,查询锁定到了UID1用户的资产,然后sleep(5),去所动UID2的资产
我发送一个请求B开启事务,查询锁定了UID2用户的资产,然后sleep(5),去所动UID1的资产
这时候A正确返回,而B的第二个查询查询则返回了一个null
然后我继续请求,开启一个事务无查询锁定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开两个窗口

执行顺序

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

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

Deadlock found when trying to get lock; try restarting transaction

执行第一部分SQL

begin

select `bac_balance` from user_finance where uid=1 for update
update `user_finance` set `bac_balance`=`bac_balance`+100 where uid=1;

select `bac_balance` from user_finance where uid=2 for update

commit

第二部分SQL

begin

select `bac_balance` from user_finance where uid=2 for update
update `user_finance` set `bac_balance`=`bac_balance`+100 where uid=2;


select `bac_balance` from user_finance where uid=1 for update

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.

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

A撮合首先对UID1用户的资产进行select *from finance where uid=1 for update加锁
同时B撮合也对UID3用户的资产进行select *from finance where uid=3 for update加锁
这时候A撮合继续开始加锁UID2和UID3,B撮合继续加锁UID1和UID2
这时候A撮合发现UID3已被锁定,所以会等待B执行完;B撮合发现UID1被锁定,等待A运行完.
这时候撮合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

案例一：

需求：将投资的钱拆成几份随机分配给借款人。

起初业务程序思路是这样的：

投资人投资后，将金额随机分为几份，然后随机从借款人表里面选几个，然后通过一条条select for update 去更新借款人表里面的余额等。

 

抽象出来就是一个session通过for循环会有几条如下的语句：

Select * from xxx where id='随机id' for update

 

基本来说，程序开启后不一会就死锁。

这可以是说最经典的死锁情形了。

 

例如两个用户同时投资，A用户金额随机分为2份，分给借款人1，2

B用户金额随机分为2份，分给借款人2，1

由于加锁的顺序不一样，死锁当然很快就出现了。

 

对于这个问题的改进很简单，直接把所有分配到的借款人直接一次锁住就行了。

Select * from xxx where id in (xx,xx,xx) for update

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


例如（以下会话id为主键）：

Session1:

mysql> select * from t3 where id in (8,9) for update;

+----+--------+------+---------------------+

| id | course | name | ctime               |

+----+--------+------+---------------------+

|  8 | WA     | f    | 2016-03-02 11:36:30 |

|  9 | JX     | f    | 2016-03-01 11:36:30 |

+----+--------+------+---------------------+
rows in set (0.04 sec)

 

 

Session2:

select * from t3 where id in (10,8,5) for update;

锁等待中……

其实这个时候id=10这条记录没有被锁住的，但id=5的记录已经被锁住了，锁的等待在id=8的这里。

 

不信请看

Session3:

mysql> select * from t3 where id=5 for update;

锁等待中

 

Session4:

mysql> select * from t3 where id=10 for update;

+----+--------+------+---------------------+

| id | course | name | ctime               |

+----+--------+------+---------------------+

| 10 | JB     | g    | 2016-03-10 11:45:05 |

+----+--------+------+---------------------+
row in set (0.00 sec)

 

在其它session中id=5是加不了锁的，但是id=10是可以加上锁的。

案例2：

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


以id为主键为例，目前还没有id=22的行

Session1:

select * from t3 where id=22 for update;

Empty set (0.00 sec)

 

session2:

select * from t3 where id=23  for update;

Empty set (0.00 sec)

 

Session1:

insert into t3 values(22,'ac','a',now());

锁等待中……

 

Session2:

insert into t3 values(23,'bc','b',now());

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction


当对存在的行进行锁的时候(主键)，mysql就只有行锁。

当对未存在的行进行锁的时候(即使条件为主键)，mysql是会锁住一段范围（有gap锁）

 

 

锁住的范围为：

(无穷小或小于表中锁住id的最大值，无穷大或大于表中锁住id的最小值)

 

如：如果表中目前有已有的id为（11 ， 12）

那么就锁住（12，无穷大）

如果表中目前已有的id为（11 ， 30）

那么就锁住（11，30）

 

对于这种死锁的解决办法是：

insert into t3(xx,xx) on duplicate key update `xx`='XX';

 

用mysql特有的语法来解决此问题。因为insert语句对于主键来说，插入的行不管有没有存在，都会只有行锁。

案例3：

直接上情景：

mysql> select * from t3 where id=9 for update;

+----+--------+------+---------------------+

| id | course | name | ctime               |

+----+--------+------+---------------------+

|  9 | JX     | f    | 2016-03-01 11:36:30 |

+----+--------+------+---------------------+
row in set (0.00 sec)

 

Session2:

mysql> select * from t3 where id<20 for update;

锁等待中

 

Session1:

mysql> insert into t3 values(7,'ae','a',now());

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

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