MySQL死锁使用详解及检测和避免方法

目录
  • 前言
  • 锁的释放与阻塞
  • 死锁的发生和检测
  • 查看锁信息（日志)
  • 死锁的避免

前言

上一篇博客我们知道的Mysql事务的隔离机制和实现，以及锁的详细解析

链接: MySQL脏读幻读不可重复读及事务的隔离级别和MVCC、LBCC实现

在我们使用锁的时候，有一个问题是需要注意和避免的，我们知道，排它锁有互斥的特性。一个事务或者说一个线程持有锁的时候，会阻止其他的线程获取锁，这个时候会造成阻塞等待，如果循环等待，会有可能造成死锁。

这个问题我们需要从几个方面来分析，一个是锁为什么不释放，第二个是被阻塞了怎么办，第三个死锁是怎么发生的，怎么避免。

锁的释放与阻塞

回顾:锁什么时候释放?
事务结束(commit,rollback)﹔
客户端连接断开。

如果一个事务一直未释放锁，其他事务会被阻塞多久?会不会永远等待下去?
如果是，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题,甚至拖跨数据库。

线上怕不怕这个错？

[Err] 1205 - Lock wait timeout exceeded; try restarting transaction

MySQL有一个参数来控制获取锁的等待时间，默认是50秒。

show VARIABLES like "innodb_lock_wait_timeout";

对于死锁，是无论等多久都不能获取到锁的，这种情况，也需要等待50秒钟吗?那不是白白浪费了50秒钟的时间吗?

死锁的发生和检测

演示一下，开两个会话：

方便对时间线的提现，这里用图片，有兴趣的可以跟着模仿一下

栗子一：

栗子二：

在第一个事务中，检测到了死锁，马上退出了，第二个事务获得了锁，不需要等待50秒:

[Err] 1213 - Deadlock found when trying to get lock; try restarting transaction

为什么可以直接检测到呢?是因为死锁的发生需要满足一定的条件，对于我们程序员来说，有明确的条件，意味着能判定，所以在发生死锁时，InnoDB一般都能通过算法(wait-for graph)自动检测到。

那么死锁需要满足什么条件?死锁的产生条件，因为锁本身是互斥的:

  • (1)同一时刻只能有一个事务持有这把锁;
  • (2）其他的事务需要在这个事务释放锁之后才能获取锁，而不可以强行剥夺;
  • (3）当多个事务形成等待环路的时候，即发生死锁。

理发店有两个总监。一个负责剪头的Tony老师，一个负责洗头的Kelvin老师。Tony老师不能同时给两个人剪头，这个就叫互斥。

Tony在给别人在剪头的时候，你不能让他停下来帮你剪头，这个叫不能强行剥夺。
如果Tony的客户对Kelvin说:你不帮我洗头我怎么剪头? Kelvin 的客户对Tony说:你不帮我剪头我怎么洗头?这个就叫形成等待环路。
实际上，发生死锁的情况非常多，但是都满足以上3个条件。
这个也是表锁是不会发生死锁的原因，因为表锁的资源都是一次性获取的。

如果锁一直没有释放，就有可能造成大量阻塞或者发生死锁，造成系统吞吐量下降,这时候就要查看是哪些事务持有了锁。

查看锁信息（日志)

首先,SHow STATUS命令中，包括了一些行锁的信息:

show status like 'innodb_row_lock_%';

lnnodb_row_lock_current_waits:当前正在等待锁定的数量;
lnnodb_row_lock_time :从系统启动到现在锁定的总时间长度，单位ms;
Innodb_row_lock_time_avg :每次等待所花平均时间;
Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间;
lnnodb_row_lock_waits :从系统启动到现在总共等待的次数。

SHOW命令是一个概要信息。InnoDB还提供了三张表来分析事务与锁的情况:

select * from information_schema.INNODB_TRX; --当前运行的所有事务﹐还有具体的语句

select* from information_schema.INNODB_LOCKS; --当前出现的锁

select * from information_schema.INNODB_LOCK_WAITS; --锁等待的对应关系

更加详细的锁信息，开启标准监控和锁监控:

额外的监控肯定会消耗额外的性能

set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;

通过分析锁日志，找出持有锁的事务之后呢?
如果一个事务长时间持有锁不释放，可以kill事务对应的线程ID，也就是INNODB_TRX表中的trx_mysql_thread_id，例如执行kill 4，kill 7， kill 8。
当然，死锁的问题不能每次都靠kill线程来解决，这是治标不治本的行为。我们应该尽量在应用端，也就是在编码的过程中避免。
有哪些可以避免死锁的方法呢?

死锁的避免

  • 1、在程序中，操作多张表时，尽量以相同的顺序来访问（避免形成等待环路)
  • 2、批量操作单张表数据的时候，先对数据进行排序(避免形成等待环路);
  • 3、申请足够级别的锁，如果要操作数据，就申请排它锁;
  • 4、尽量使用索引访问数据，避免没有where条件的操作，避免锁表;
  • 5、如果可以，大事务化成小事务;
  • 6、使用等值查询而不是范围查询查询数据，命中记录，避免间隙锁对并发的影响。

到此这篇关于MySQL死锁使用详解及检测和避免方法的文章就介绍到这了,更多相关MySQL死锁 内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • Mysql使用kill命令解决死锁问题(杀死某条正在执行的sql语句)

    在使用mysql运行某些语句时,会因数据量太大而导致死锁,没有反映.这个时候,就需要kill掉某个正在消耗资源的query语句即可, KILL命令的语法格式如下: KILL [CONNECTION | QUERY] thread_id 每个与mysqld的连接都在一个独立的线程里运行,您可以使用SHOW PROCESSLIST语句查看哪些线程正在运行,并使用KILL thread_id语句终止一个线程. KILL允许自选的CONNECTION或QUERY修改符:KILL CONNECTION与不

  • Mysql超详细讲解死锁问题的理解

    Mysql超详细讲解死锁问题的理解

    目录 1.什么是死锁? 2.Mysql出现死锁的必要条件 资源独占条件 请求和保持条件 不剥夺条件 相互获取锁条件 3. Mysql经典死锁案例 3.1 建表语句 3.2 初始化相关数据 3.3 正常转账过程 3.4 死锁转账过程 3.5 死锁导致的问题 4.如何解决死锁问题? 4.1 打破请求和保持条件 4.2 打破相互获取锁条件(推荐) 5.总结 1.什么是死锁? 死锁指的是在两个或两个以上不同的进程或线程中,由于存在共同资源的竞争或进程(或线程)间的通讯而导致各个线程间相互挂起等待,如果没

  • mysql死锁和分库分表问题详解

    记录生产mysql的问题点. 业务场景与问题描述 请求一个外部接口时,每天的请求量在900万左右. 分为请求项目和回执这两个项目.请求是用来调用外部接口,回执是接收发送的接口. 在发送请求前会先插入数据库. 在请求后,如果接口返回调用失败,会更新数据库状态为失败. 如果发送成功,则会等待上游给出回执消息后,然后更新数据库状态. 而在生产运行过程中,半年出现过两次mysql导致的mq消费者堆积的问题. 问题分析 记录两次不同的原因导致的生产问题及原因分析. mysql死锁问题 查看mq聚合平台TP

  • 浅谈Mysql insert on duplicate key 死锁问题定位与解决

    目录 前言 死锁定位 insert on duplicate key的锁 问题解决 前言 最近在监测线上日志时发现我们一个Mysql业务db时常出现 dead lock,频次不高但却一直出现,定位后发现是在并发场景下的 insert on duplicate key update sql 出现的死锁.经过分析发现这种sql确实比较容易造成死锁,不太适用于我们目前的业务场景,于是更换后解决问题. 这篇文章就从分析死锁展开,到最终如何解决这样的问题 分享相应的思路. 死锁定位 我们目前生产环境使用M

  • Mysql 行级锁的使用及死锁的预防方案

    一.前言 mysql的InnoDB,支持事务和行级锁,可以使用行锁来处理用户提现等业务.使用mysql锁的时候有时候会出现死锁,要做好死锁的预防. 二.MySQL行级锁 行级锁又分共享锁和排他锁. 共享锁: 名词解释:共享锁又叫做读锁,所有的事务只能对其进行读操作不能写操作,加上共享锁后其他事务不能再加排他锁了只能加行级锁. 用法: SELECT `id` FROM table WHERE id in(1,2) LOCK IN SHARE MODE 结果集的数据都会加共享锁 排他锁: 名词解释:

  • Mysql锁机制之行锁、表锁、死锁的实现

    Mysql锁机制之行锁、表锁、死锁的实现

    目录 一.Mysql锁是什么?锁有哪些类别? 二.行锁和表锁的区别 三.InnoDB死锁概念和死锁案例 死锁场景一之selectforupdate: 死锁场景二之两个update 四.程序开发过程中应该如何注意避免死锁 一.Mysql锁是什么?锁有哪些类别? 锁定义:    同一时间同一资源只能被一个线程访问    在数据库中,除传统的计算资源(如CPU.I/O等)的争用以外,数据也是一种供许多用户共享的资源.如何保证数据并发访问的一致性.有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据

  • MySQL打印死锁日志的方法步骤

    目录 前言: 1.手动打印死锁日志 2.自动保存死锁日志 总结: 前言: 在 MySQL 运维过程中,难免会遇到 MySQL 死锁的情况,一旦线上业务日渐复杂,各种业务操作之间往往会产生锁冲突,有些会导致死锁异常.这种死锁异常一般要在特定时间特定数据和特定业务操作才会复现,有时候处理起来毫无头绪,一般只能从死锁日志下手.本篇文章我们一起来看下 MySQL 的死锁日志. 1.手动打印死锁日志 当业务发生死锁时,首先是线上错误日志报警发现死锁异常,也会提示一些堆栈信息,然后会反馈到数据库层面进行排查

  • MySQL死锁使用详解及检测和避免方法

    MySQL死锁使用详解及检测和避免方法

    目录 前言 锁的释放与阻塞 死锁的发生和检测 查看锁信息(日志) 死锁的避免 前言 上一篇博客我们知道的Mysql事务的隔离机制和实现,以及锁的详细解析 链接: MySQL脏读幻读不可重复读及事务的隔离级别和MVCC.LBCC实现 在我们使用锁的时候,有一个问题是需要注意和避免的,我们知道,排它锁有互斥的特性.一个事务或者说一个线程持有锁的时候,会阻止其他的线程获取锁,这个时候会造成阻塞等待,如果循环等待,会有可能造成死锁. 这个问题我们需要从几个方面来分析,一个是锁为什么不释放,第二个是被阻塞

  • mysql幻读详解实例以及解决办法

    mysql幻读详解实例以及解决办法

    目录 事务隔离级别(tx_isolation) 幻读 RR级别下防止幻读 SERIALIZABLE级别杜绝幻读 总结 脏读/不可重复读的概念都比较容易理解和掌握,这里不在讨论 事务隔离级别(tx_isolation) mysql 有四级事务隔离级别 每个级别都有字符或数字编号 级别 symbol 值 描述 读未提交 READ-UNCOMMITTED 0 存在脏读.不可重复读.幻读的问题 读已提交 READ-COMMITTED 1 解决脏读的问题,存在不可重复读.幻读的问题 可重复读 REPEAT

  • Oracle10个分区和Mysql分区区别详解

    Oracle10g分区常用的是:range(范围分区).list(列表分区).hash(哈希分区).range-hash(范围-哈希分区).range-list(列表-复合分区). Range分区:Range分区是应用范围比较广的表分区方式,它是以列的值的范围来做为分区的划分条件,将记录存放到列值所在的range分区中. 如按照时间划分,2010年1月的数据放到a分区,2月的数据放到b分区,在创建的时候,需要指定基于的列,以及分区的范围值. 在按时间分区时,如果某些记录暂无法预测范围,可以创建m

  • MySQL 序列 AUTO_INCREMENT详解及实例代码

    MySQL 序列 AUTO_INCREMENT详解及实例代码 MySQL序列是一组整数:1, 2, 3, ...,由于一张数据表只能有一个字段自增主键, 如果你想实现其他字段也实现自动增加,就可以使用MySQL序列来实现. 本章我们将介绍如何使用MySQL的序列. 使用AUTO_INCREMENT MySQL中最简单使用序列的方法就是使用 MySQL AUTO_INCREMENT 来定义列. 实例 以下实例中创建了数据表insect, insect中id无需指定值可实现自动增长. mysql>

  • MySQL prepare原理详解

    Prepare的好处  Prepare SQL产生的原因.首先从mysql服务器执行sql的过程开始讲起,SQL执行过程包括以下阶段 词法分析->语法分析->语义分析->执行计划优化->执行.词法分析->语法分析这两个阶段我们称之为硬解析.词法分析识别sql中每个词,语法分析解析SQL语句是否符合sql语法,并得到一棵语法树(Lex).对于只是参数不同,其他均相同的sql,它们执行时间不同但硬解析的时间是相同的.而同一SQL随着查询数据的变化,多次查询执行时间可能不同,但硬解

  • 在Linux系统安装MySql步骤截图详解

    在Linux系统安装MySql步骤截图详解

    如下是我工作中的记录,介绍的是linux系统下使用官方编译好的二进制文件进行安装MySql的安装过程和安装截屏,这种安装方式速度快,安装步骤简单! 需要的朋友可以按照如下步骤进行安装,可以快速安装MySql,希望可以帮助大家:)! 1.下载mysql的linux版本的二进制安装包: 地址:http://dev.mysql.com/downloads/mysql/ 这里我将安装包重命名为:tingyun-mysql-5.6.22.tar.gz 说明:根据自己需要可以不进行重命名操作 2.解压安装包

  • mysql分区功能详解,以及实例分析

    一,什么是数据库分区 前段时间写过一篇关于mysql分表的 的文章,下面来说一下什么是数据库分区,以mysql为例.mysql数据库中的数据是以文件的形势存在磁盘上的,默认放在/mysql/data下面 (可以通过my.cnf中的datadir来查看),一张表主要对应着三个文件,一个是frm存放表结构的,一个是myd存放表数据的,一个是myi存表 索引的.如果一张表的数据量太大的话,那么myd,myi就会变的很大,查找数据就会变的很慢,这个时候我们可以利用mysql的分区功能,在物理上将这 一张

  • PHP与MySQL交互使用详解

    PHP与MySQL交互使用详解 1.创建自动连接数据库的代码,并生成一些必要的代码.我们仔细研究一下数据库的连接函数,会发现是这样的一行代码. $link_id=@mysql_connect($hostname,$username,$password); 所以我们在include文件connect.inc中添加以下代码就可以了.connect.inc<?php$hostname='localhost'; $username='phpstar';$password='phpstar';$dbnam

  • LINUX启动/重启/停上MYSQL的命令(详解)

    如何启动/停止/重启MySQL 一.启动方式 1.使用 service 启动:service mysqld start 2.使用 mysqld 脚本启动:/etc/inint.d/mysqld start 3.使用 safe_mysqld 启动:safe_mysqld& 二.停止 1.使用 service 启动:service mysqld stop 2.使用 mysqld 脚本启动:/etc/inint.d/mysqld stop 3.mysqladmin shutdown 三.重启 1.使用

  • LINUX重启MYSQL的命令详解

    如何启动/停止/重启MySQL 一.启动方式 1.使用 service 启动:service mysqld start 2.使用 mysqld 脚本启动:/etc/inint.d/mysqld start 3.使用 safe_mysqld 启动:safe_mysqld& 二.停止 1.使用 service 启动:service mysqld stop 2.使用 mysqld 脚本启动:/etc/inint.d/mysqld stop 3. mysqladmin shutdown 三.重启 1.

随机推荐