文档章节

MySQL 锁之死锁

learn_more
 learn_more
发布于 2017/09/22 12:39
字数 1847
阅读 64
收藏 0

 

1、事务

事务的四大特性ACID,其中 I 指隔离性,这个隔离性要重点说明,因为隔离性指的事务与事务之间互不影响,可是为了高并发,高性能,如何设置不影响才最合适呢?

所以,有了隔离级别:@@tx_isolation 、 @@global.tx_isolation

select @@tx_isolation;

| READ-COMMITTED | // 推荐使用

 

 

2、事务的隔离级别

事务的隔离级别强弱,直接影响事务的并发性能

1)Read Uncommitted(读取未提交内容)

在该隔离级别,所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用,因为它的性能也不比其他级别好多少。读取未提交的数据,也被称之为脏读(Dirty Read)。

2)Read Committed(读取提交内容)

这是大多数数据库系统的默认隔离级别(但不是MySQL默认的)。它满足了隔离的简单定义:一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读(Nonrepeatable Read),因为同一事务的其他实例在该实例处理其间可能会有新的commit,所以同一select可能返回不同结果。

3)Repeatable Read(可重读)

这是MySQL的默认事务隔离级别,它确保同一事务的多个实例在并发读取数据时,会看到同样的数据行。不过理论上,这会导致另一个棘手的问题:幻读 (Phantom Read)。简单的说,幻读指当用户读取某一范围的数据行时,另一个事务又在该范围内插入了新行,当用户再读取该范围的数据行时,会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制(MVCC,Multiversion Concurrency Control)机制解决了该问题。

4)Serializable(可串行化)

这是最高的隔离级别,它通过强制事务排序,使之不可能相互冲突,从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。在这个级别,可能导致大量的超时现象和锁竞争。

这四种隔离级别采取不同的锁类型来实现,若读取的是同一个数据的话,就容易发生问题。例如:

脏读(Drity Read):某个事务已更新一份数据,另一个事务在此时读取了同一份数据,由于某些原因,前一个RollBack了操作,则后一个事务所读取的数据就会是不正确的。

不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致,这可能是两次查询过程中间插入了一个事务更新的原有的数据。

幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致,例如有一个事务查询了几列(Row)数据,而另一个事务却在此时插入了新的几列数据,先前的事务在接下来的查询中,就会发现有几列数据是它先前所没有的。

注意:不可重复读与幻读的最大区别在于前者是更新操作,后者是插入操作。

 

 

 

3、死锁

事务是数据库执行的最小单位,然后为了数据安全性,我们会以事务为载体,给资源适当加锁,锁又分很多种,表锁、行锁、页锁等。因为我们 MySQL 默认引擎使用的是 innodb,所以行锁居多。行锁并发性能好,但是容易发生死锁。

死锁产生的原因无非两个

1)两个事务互相等待锁,形成一个互等的闭环

2)多个事务同时占有同一个资源(没有去验证)

 

 

4、死锁排查

1)SHOW FULL PROCESSLIST;

或者 SELECT * FROM information_schema.`PROCESSLIST` WHERE db = 'dongni_test';

通过他可以看到当前正在执行的SQL语句,如果你的SQL语句耗时很长或一直被阻塞,那么肯定会在这里找到,不过他只是临时的数据,时刻会变化。

 

2)Navicat F6 控制台下执行: show engine innodb status;

他的作用有两个,其一是他会记录最近一次死锁的两个事务,但是只是记录相互等待的两条SQL语句,并不能说明是因为他们两个造成死锁,只能说他们是构成死锁这个闭环的一部分。

其二是他会记录当前正在执行的事务,但是这里要注意,事务正在执行可能是被锁了也可能是应用一直拿着锁没有释放,所以你看到的事务可能没有正在执行的SQL语句。

 

 

3)查看当前事务

SELECT * FROM information_schema.INNODB_LOCKS;

SELECT * FROM information_schema.INNODB_TRX;

SELECT * FROM information_schema.INNODB_LOCK_WAITS;

作用就是记录当前正在执行的事务,以及事务持有的类型锁,通过他我们很容易判断是谁在阻塞谁,但是缺点很明显,那就是你能看到被阻塞的SQL,但是你却看不到拥有锁的SQL,也就是说正拥有的锁的事务他是不会把那个SQL记录下来的,所以当你看到正在运行的SQL时千万不要误以为是他导致的。

 

 

5、死锁解决

核心思想就是让事务可能的小,即占用时间小,占用锁的粒度小。

1)更新语句经量使用主键更新,这样的好处是只锁这一行记录

2)尽量不要使用关联更新,关联删除,因为关联操作会同时被锁,分开操作会更好

3)不同应用尽量保证加锁的顺序,互等就是因为顺序不一致导致

4)把数据库操作的地方尽量统一放一处,因为从操作开始会一直持有锁直到事务提交

 

 

6、真实案例

事务1:采用关联更新,导致两张表都被锁,然而实际上表 t_exam_plan 是无辜的,因为他只是统一一下数据而已,就因为写在了子查询里面,所以他被加了读锁。

事务2:去更新是发现需要等待X锁,因为状态为2的记录以及被事务1加了读锁,所以不能加写锁。

解决办法:事务1更新时,先单独查询 t_exam_plan ,然后再写一条更新 t_test 的SQL,这样事务2就可以顺利执行。

 

事务1:

BEGIN

UPDATE t_test t1 // 拥有写锁

LEFT JOIN (

SELECT t2.school_id, COUNT(1) total

FROM t_exam_plan t2

WHERE t2.exam_plan_status = 2

GROUP BY t2.school_id // 子查询中的表会拥有读锁

) t3 ON t1.id = t3.school_id

SET t1.status = t3.total;

ROLLBACK;

 

事务2:

BEGIN

UPDATE t_exam_plan SET exam_plan_status = 3 WHERE exam_plan_id in (173,174,175);

ROLLBACK;

 

 

7、死锁的另一种可能

从该图可以看出,索引很重要,因为使用不同的索引,加锁顺序会不一致,因此如果我们先把ID查询出来,再用ID去更新,就不会出现这种情况了。

 

参考:

http://hedengcheng.com/?p=771

《MySQL 高性能》

 

 

© 著作权归作者所有

learn_more
粉丝 93
博文 240
码字总数 210196
作品 0
深圳
程序员
私信 提问
Mysql并发时经典常见的死锁原因及解决方法

Mysql并发时经典常见的死锁原因及解决方法 1. mysql都有什么锁 MySQL有三种锁的级别:页级、表级、行级。 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最...

rshare
2017/12/06
0
0
MySQL并发时经典常见的死锁原因及解决方法

MySQL并发时经典常见的死锁原因及解决方法 1. mysql都有什么锁 MySQL有三种锁的级别:页级、表级、行级。 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最...

推荐码发放
2018/08/20
0
0
Mysql并发时经典常见的死锁原因及解决方法

mysql都有什么锁 MySQL有三种锁的级别:页级、表级、行级。 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。 行级锁:开销大,加锁慢;会出现死锁;锁...

满风
2017/10/19
64
0
mysql 锁机制 详解二 (死锁)

1 死锁问题背景 1 1.1 一个不可思议的死锁 1 1.1.1 初步分析 3 1.2 如何阅读死锁日志 3 2 死锁原因深入剖析 4 2.1 Delete操作的加锁逻辑 4 2.2 死锁预防策略 5 2.3 剖析死锁的成因 6 3 总结 ...

LYQ1990
2016/05/19
177
0
MySQL中的行级锁,表级锁,页级锁

在计算机科学中,锁是在执行多线程时用于强行限制资源访问的同步机制,即用于在并发控制中保证对互斥要求的满足。 在数据库的锁机制中介绍过,在DBMS中,可以按照锁的粒度把数据库锁分为行级...

Hosee
2016/06/01
725
0

没有更多内容

加载失败,请刷新页面

加载更多

MBTI助你成功,让你更了解你自己

MBTI助你成功,让你更了解你自己 生活总是一个七日接着又一个七日,相信看过第七日的小伙伴,很熟悉这段开场白,人生是一个测试接着又一个测试,上学的时候测试,是为了证明你的智力,可谓从...

蛤蟆丸子
今天
55
0
Android实现App版本自动更新

现在很多的App中都会有一个检查版本的功能。例如斗鱼TV App的设置界面下: 当我们点击检查更新的时候,就会向服务器发起版本检测的请求。一般的处理方式是:服务器返回的App版本与当前手机安...

shzwork
昨天
63
0
npm 发布webpack插件 webpack-html-cdn-plugin

初始化一个项目 npm init 切换到npm源 淘宝 npm config set registry https://registry.npm.taobao.org npm npm config set registry http://registry.npmjs.org 登录 npm login 登录状态......

阿豪boy
昨天
87
0
java基础(16)递归

一.说明 递归:方法内调用自己 public static void run1(){ //递归 run1(); } 二.入门: 三.执行流程: 四.无限循环:经常用 无限递归不要轻易使用,无限递归的终点是:栈内存溢出错误 五.递...

煌sir
昨天
63
0
REST接口设计规范总结

URI格式规范 URI中尽量使用连字符”-“代替下划线”_”的使用 URI中统一使用小写字母 URI中不要包含文件(脚本)的扩展名 URI命名规范 文档(Document)类型的资源用名词(短语)单数命名 集合(Co...

Treize
昨天
69
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部