细说MySQL死锁与日志二三事

最近线上 MySQL 接连发生了几起数据异常,都是在凌晨爆发,由于业务场景属于典型的数据仓库型应用,白天压力较小无法复现。甚至有些异常还比较诡异,最后 root cause 分析颇费周折。那实际业务当中咱们如何能快速的定位线上 MySQL 问题,修复异常呢?下文我会根据两个实际 case,分享下相关的经验与方法。

Case1:部分数据更新失败

某天渠道同学反馈某报表极个别渠道数据为 0,大部分渠道数据正常。这个数据是由一个统计程序每天凌晨例行更新的,按理来说,要么全部正常,要么全部失败,那会是什么原因导致极个别数据异常呢?

首先我们能想到的自然是根据统计任务日志来看了,但是看了统计程序打印的日志没有发现诸如 SQL update 失败的异常描述,那当时的数据库究竟发生了什么呢?在查看 MySQL-server 日志之前,习惯性的看了下数据库状态:

恰好看到了凌晨这个 update 发生了死锁:

篇幅所限,上下文我这里省略了很多,从这段日志里可以看到,TRANSACTION 1 和 TRANSACTION 2 分别持有一定数量的行锁,然后又等待对方的锁,最后 MySQL 检测到 deadlock ,然后选择回滚了 TRANSACTION 1:Innodb目前处理死锁的方法是将持有最少行级排他锁的事务进行回滚。

那这里就有 3 个问题了:

1、innodb 行锁不是只锁一行?

因为这张表是 innodb 引擎的,InnoDB 支持行锁和表锁。而InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQL与Oracle不同,后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁,会把所有扫描过的行都锁定!在实际应用中,要特别注意InnoDB行锁的这一特性,不然的话,可能导致大量的锁冲突,从而影响并发性能。由于MySQL的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行的记录,但是如果是使用相同的索引键,是会出现锁冲突的。当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;另外间隙锁也会锁多行,InnoDB除了通过范围条件加锁时使用间隙锁外,如果使用相等条件请求给一个不存在的记录加锁,InnoDB也会使用间隙锁!

话都说到这了,那就看下咱们业务表的索引情况:

可以看到这张表的索引极不合理:有3个索引,但是 update 却没有完全的用上索引,导致 update 没有精确的用上索引,需要锁定多行范围数据,从而引发死锁。

知道原理后,咱们再精心构建一个四字段的组合索引即可让 update 精准的走 innodb 索引,实际上,我们更新索引后,这个死锁问题即得到了解决。

注:innodb不仅会打印出事务和事务持有和等待的锁,而且还有记录本身,不幸的是,它可能超过innodb为输出结果预留的长度(只能打印1M的内容且只能保留最近一次的死锁信息),如果你无法看到完整的输出,此时可以在任意库下创建innodb_monitor或innodb_lock_monitor表,这样innodb status信息会完整且每15s一次被记录到错误日志中。如:create table innodb_monitor(a int)engine=innodb;,不需要记录到错误日志中时就删掉这个表即可。

2、回滚为什么只有部分 update 语句失败

回滚的话,为什么只有部分 update 语句失败,而不是整个事务里的所有 update 都失败?

这是因为咱们的 innodb 默认是自动提交的:

在多个 update 或 insert 语句情况下,每执行完一条 SQL,innodb 就立即 commit 一次以持久化变更,同时释放锁,这也正是本例中死锁回滚事务后只有极个别语句失败的原因。

需要注意的是,通常还有另外一种情况也可能导致部分语句回滚,需要格外留意。在 innodb 里有个参数叫:innodb_rollback_on_timeout

官方手册里这样描述:

In MySQL 5.1, InnoDB rolls back only the last statement on a transaction timeout by default. If –innodb_rollback_on_timeout is specified, a transaction timeout causes InnoDB to abort and roll back the entire transaction (the same behavior as in MySQL 4.1). This variable was added in MySQL 5.1.15.

解释:这个参数关闭或不存在的话遇到超时只回滚事务最后一个Query,打开的话事务遇到超时就回滚整个事务。

3、怎样降低 innodb 死锁几率?

死锁在行锁及事务场景下很难完全消除,但可以通过表设计和SQL调整等措施减少锁冲突和死锁,包括:

尽量使用较低的隔离级别,比如如果发生了间隙锁,你可以把会话或者事务的事务隔离级别更改为 RC(read committed)级别来避免,但此时需要把 binlog_format 设置成 row 或者 mixed 格式

精心设计索引,并尽量使用索引访问数据,使加锁更精确,从而减少锁冲突的机会;

选择合理的事务大小,小事务发生锁冲突的几率也更小;

给记录集显示加锁时,最好一次性请求足够级别的锁。比如要修改数据的话,最好直接申请排他锁,而不是先申请共享锁,修改时再请求排他锁,这样容易产生死锁;

不同的程序访问一组表时,应尽量约定以相同的顺序访问各表,对一个表而言,尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会;

尽量用相等条件访问数据,这样可以避免间隙锁对并发插入的影响;

不要申请超过实际需要的锁级别;除非必须,查询时不要显示加锁;

对于一些特定的事务,可以使用表锁来提高处理速度或减少死锁的可能。

Case2:诡异的 Lock wait timeout

连续几天凌晨6点和早上8点 都分别有一个任务失败,load data local infile 的时候报 Lock wait timeout exceeded try restarting transaction innodb 的 Java SQL 异常,和平台的同学沟通得知,这是我们自己的业务数据库的 Lock 时间太短或者锁冲突的问题。但是回头一想不应该啊?这不一直好好的吗?而且基本都是单表单任务,不存在多人冲突。

甭管谁的问题,那咱们还是先看自己的数据库有没有问题:

默认 lock 超时时间 50s,这个时间真心不短了,估计调了也没用,事实上确实死马当活马医的试了下没用。。。

而且这次 SHOW ENGINE INNODB STATUS\G 也没出现任何死锁信息,然后又将目光转向 MySQL-server 日志,希望能从日志里看一看那个时刻前后数据究竟在做什么操作。这里先简单的介绍下MySQL日志文件系统的组成:

(a) error 日志:记录启动、运行或停止 mysqld 时出现的问题,默认开启。
   (b) general 日志:通用查询日志,记录所有语句和指令,开启数据库会有 5% 左右性能损失。
   (c) binlog 日志:二进制格式,记录所有更改数据的语句,主要用于 slave 复制和数据恢复。
   (d) slow 日志:记录所有执行时间超过 long_query_time 秒的查询或不使用索引的查询,默认关闭。
   (e) Innodb日志:innodb redo log、undo log,用于恢复数据和撤销操作。

从上面的介绍可以看到,目前这个问题的日志可能在 d 和 b 中,看了下 d 中没有,那就只能开启 b 了,但 b 对数据库的性能有一定损耗,由于是全量日志,量非常巨大,所以开启一定要谨慎:

我这里只是每天在出问题的前后半小时开启下全量日志,结果没有发现任何 MySQL-client 请求到我们的业务数据库!该日志格式如下,记录了所有的连接与命令:

那问题基本确定了,客户端请求都没到我们这边就抛出了上述的异常,和平台方再三沟通确认下,最后平台方查证是因为在执行插入前他们需要先从 SQL task 表取出 SQL 和更新 task 状态,结果这张表由于在整点存在大量 insert 和 update 并发,导致部分 SQL 等待 lock 超时了。。。

MySQL 日志分析脚本

由于凌晨是数据仓库的业务高峰,很多问题都是在这个时候爆发,一些诡异的问题往往是过了这个村就没这个店了,白天无法复现。如何能捕获我们关心的日志,便于快速的定位问题,这个是重中之重,这里我写了个小脚本,crontab 部署,可以选择时间范围开启,每分钟采样一次日志,需要说明的是 general log 没事别轻易开启,否则对数据库性能损耗较大。

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持我们。

(0)

相关推荐

  • MySQL死锁问题分析及解决方法实例详解

    MySQL死锁问题是很多程序员在项目开发中常遇到的问题,现就MySQL死锁及解决方法详解如下: 1.MySQL常用存储引擎的锁机制 MyISAM和MEMORY采用表级锁(table-level locking) BDB采用页面锁(page-level locking)或表级锁,默认为页面锁 InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁 2.各种锁特点 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低 行级锁:开销大,加锁慢;

  • 一次Mysql死锁排查过程的全纪录

    前言 之前接触到的数据库死锁,都是批量更新时加锁顺序不一致而导致的死锁,但是上周却遇到了一个很难理解的死锁.借着这个机会又重新学习了一下mysql的死锁知识以及常见的死锁场景.在多方调研以及和同事们的讨论下终于发现了这个死锁问题的成因,收获颇多.虽然是后端程序员,我们不需要像DBA一样深入地去分析与锁相关的源码,但是如果我们能够掌握基本的死锁排查方法,对我们的日常开发还是大有裨益的. PS:本文不会介绍死锁的基本知识,mysql的加锁原理可以参考本文的参考资料提供的链接. 死锁起因 先介绍一下数

  • MySQL Innodb表导致死锁日志情况分析与归纳

    案例描述在定时脚本运行过程中,发现当备份表格的sql语句与删除该表部分数据的sql语句同时运行时,mysql会检测出死锁,并打印出日志.两个sql语句如下:(1)insert into backup_table select * from source_table(2)DELETE FROM source_table WHERE Id>5 AND titleWeight<32768 AND joinTime<'$daysago_1week'teamUser表的表结构如下:PRIMARY

  • mysql通过查看跟踪日志跟踪执行的sql语句

    在SQL SERVER下跟踪sql采用事件探查器,而在mysql下如何跟踪sql呢? 其实方法很简单,开启mysql的日志log功能,通过查看跟踪日志即可. 开启mysql的日志log方法: windows环境下的配置方法: 我使用的版本:Version: 5.0.37-community-nt-log (MySQL Community Edition (GPL)) 找到my.ini,我的是在"G:\Program Files (x86)\MySQL\MySQL Server 5.0"

  • mysql 数据库死锁原因及解决办法

    死锁(Deadlock) 所谓死锁:是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待的进程称为死锁进程.由于资源占用是互斥的,当某个进程提出申请资源后,使得有关进程在无外力协助下,永远分配不到必需的资源而无法继续运行,这就产生了一种特殊现象死锁. 一种情形,此时执行程序中两个或多个线程发生永久堵塞(等待),每个线程都在等待被其他线程占用并堵塞了的资源.例如,如果线程A锁住了记

  • 查看MySQL的错误日志的方法

    MySQL的错误信息是在data目录下的,且文件名为<hostname>.err(<hostname>指的是主机名),但由于每个人安装的环境不一样,或你忘记了data目录的所在位置,你可以通过下面方法查找. #hostname //获得主机名 <hostname> #find / -name <hostname>.err ..... #cd ... #vi <hostname>.err

  • 详解MySQL中的死锁情况以及对死锁的处理方法

    当多个事务同时持有和请求同一资源上的锁而产生循环依赖的时候就产生了死锁.死锁发生在事务试图以不同的顺序锁定资源.以StockPrice表上的两个事务为例: 事务1 START TRANSACTION; UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 and date = '2002-05-01'; UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 and date = '20

  • Mysql 数据库死锁过程分析(select for update)

    近期有一个业务需求,多台机器需要同时从Mysql一个表里查询数据并做后续业务逻辑,为了防止多台机器同时拿到一样的数据,每台机器需要在获取时锁住获取数据的数据段,保证多台机器不拿到相同的数据. 我们Mysql的存储引擎是innodb,支持行锁.解决同时拿数据的方法有很多,为了更加简单,不增加其他表和服务的情况下,我们考虑采用select... for update的方式,这样X锁锁住查询的数据段,表里其他数据没有锁,其他业务逻辑还是可以操作. 这样一台服务器比如select .. for upda

  • 查找MySQL线程中死锁的ID的方法

    如果遇到死锁了,怎么解决呢?找到原始的锁ID,然后KILL掉一直持有的那个线程就可以了, 但是众多线程,可怎么找到引起死锁的线程ID呢? MySQL 发展到现在,已经非常强大了,这个问题很好解决. 直接从数据字典连查找. 我们来演示下. 线程A,我们用来锁定某些记录,假设这个线程一直没提交,或者忘掉提交了. 那么就一直存在,但是数据里面显示的只是SLEEP状态. mysql> set @@autocommit=0; Query OK, 0 rows affected (0.00 sec) mys

  • mysql日志文件在哪 如何修改MySQL日志文件位置

    MySQL日志文件相信大家都有很多的了解,MySQL日志文件一般在:/var/log/mysqld.log,下面就教您修改MySQL日志文件位置的方法,供您参考. 今天需要改MySQL日志文件的位置,发现在/etc/my.cnf中怎么也改不了. 后来发现MySQL日志位是指定的: [root@localhost etc]# ps aux|grep mysqld root 11830 0.5 0.0 4524 1204 pts/0 S 03:03 0:00 /bin/sh /usr/bin/mys

随机推荐