记mysql gone away和死锁Deadlock解决方案

小熊 MySQL23,226字数 7474阅读24分54秒阅读模式

最近运行了两年的程序,在多个项目上出现了gone away和 Deadlock,下面记录下解决方案,以便以后查询。

mysql gone away 可能原因

参考 http://ronaldbradford.com/blog/sqlstatehy000-general-error-2006-mysql-server-has-gone-away-2013-01-02/

MySQL 服务宕了

判断是否属于这个原因的方法很简单,执行以下命令,查看mysql的运行时长

$ mysql -uroot -p -e "show global status like 'uptime';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 68928 |
+---------------+-------+
1 row in set (0.04 sec)

或者查看MySQL的报错日志,看看有没有重启的信息

$ tail /var/log/mysql/error.log
130101 22:22:30 InnoDB: Initializing buffer pool, size = 256.0M
130101 22:22:30 InnoDB: Completed initialization of buffer pool
130101 22:22:30 InnoDB: highest supported file format is Barracuda.
130101 22:22:30 InnoDB: 1.1.8 started; log sequence number 63444325509
130101 22:22:30 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
130101 22:22:30 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
130101 22:22:30 [Note] Server socket created on IP: '127.0.0.1'.
130101 22:22:30 [Note] Event Scheduler: Loaded 0 events
130101 22:22:30 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.28-cll'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)

如果uptime数值很大,表明mysql服务运行了很久了。说明最近服务没有重启过。

如果日志没有相关信息,也表名mysql服务最近没有重启过,可以继续检查下面几项内容。

连接超时

如果程序使用的是长连接,则这种情况的可能性会比较大。

即,某个长连接很久没有新的请求发起,达到了server端的timeout,被server强行关闭。

此后再通过这个connection发起查询的时候,就会报错server has gone away

$ mysql -uroot -p -e "show global variables like '%timeout';"
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| connect_timeout            | 30       |
| delayed_insert_timeout     | 300      |
| innodb_lock_wait_timeout   | 50       |
| innodb_rollback_on_timeout | OFF      |
| interactive_timeout        | 28800    |
| lock_wait_timeout          | 31536000 |
| net_read_timeout           | 30       |
| net_write_timeout          | 60       |
| slave_net_timeout          | 3600     |
| wait_timeout               | 28800    |
+----------------------------+----------+

mysql> SET SESSION wait_timeout=5;
# Wait 10 seconds
mysql> SELECT NOW();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    132361
Current database: *** NONE ***

+---------------------+
| NOW()               |
+---------------------+
| 2013-01-02 11:31:15 |
+---------------------+
1 row in set (0.00 sec)

所以这种情况要把wait_timeout改大,再一个就是优化代码让sql跑快一点。

进程在server端被主动kill

这种情况和情况2相似,只是发起者是DBA或者其他job。发现有长时间的慢查询执行kill xxx导致。

$ mysql -uroot -p -e "show global status like 'com_kill'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_kill      | 0     |
+---------------+-------+

Your SQL statement was too large.

当查询的结果集超过 max_allowed_packet 也会出现这样的报错。定位方法是打出相关报错的语句。

用select * into outfile 的方式导出到文件,查看文件大小是否超过 max_allowed_packet ,如果超过则需要调整参数,或者优化语句。

mysql> show global variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.00 sec)

修改参数:

mysql> set global max_allowed_packet=1024*1024*16;
mysql> show global variables like 'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 16777216 |
+--------------------+----------+
1 row in set (0.00 sec)

长期生效还是要改mysql的配置文件

max_allowed_packet = 128M

Deadlock

排查

死锁可以快速排查,用以下命令, 查看mysql记录的信息(可以看到最后一次死锁)

show engine innodb status\G;

下面是死锁日志,我把ip改成localhost,脱敏

------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-11-08 11:00:01 0x7f1d3d4fc700
*** (1) TRANSACTION: 事务1在此
TRANSACTION 13058816, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1 #mysql有一个表在使用,一个表被锁住
LOCK WAIT 7 lock struct(s), heap size 1136, 7 row lock(s), undo log entries 6##有7个锁链表,内存中堆的大小1136, 7行记录被锁定
MySQL thread id 49581, OS thread handle 139762045613824, query id 9659334 localhost root updating ##mysql线程id 49581, query id 9659334, localhost root用户执行update操作
UPDATE server_info_ex SET#这就是锁表sql,我省略了部分,脱敏
......
*** (1) HOLDS THE LOCK(S): 锁情况
RECORD LOCKS space id 710 page no 14 n bits 96 index PRIMARY of table `cloud`.`server_info_ex` trx id 13058816 lock_mode X locks rec but not gap #类型:行锁,事物 id 13058816,在cloud库的server_info_ex表的主键上加一个X锁(not gap 没有间隙锁),锁住96 bits
...

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 710 page no 11 n bits 104 index PRIMARY of table `cloud`.`server_info_ex` trx id 13058816 lock_mode X locks rec but not gap waiting

.....

*** (2) TRANSACTION: 事务2
TRANSACTION 13058786, ACTIVE 1 sec fetching rows
mysql tables in use 4, locked 4 #mysql有4表在使用,4个表被锁住
LOCK WAIT 925 lock struct(s), heap size 106704, 22490 row lock(s), undo log entries 249 #有925个锁链表,内存中堆的大小106704, 22490行记录被锁定,真够多个,因为这个update语句同时使用了4个表,更新了很多的记录,本质上还是和事务1同一个表的锁定出现的问题
MySQL thread id 49577, OS thread handle 139762044397312, query id 9659236 localhost root executing#mysql线程id 49577, query id 9659236, localhost root用户执行update操作
UPDATE server_info_ex
                    SET server_info_ex.cpu = (
                        SELECT #这就是锁表sql,我省略了部分,脱敏
......
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 710 page no 11 n bits 104 index PRIMARY of table `cloud`.`server_info_ex` trx id 13058786 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0  #类型:行锁,事物 id 13058786,在cloud库的server_info_ex表的主键上加一个X锁(not gap 没有间隙锁),锁住 104 bits
 0: len 8; hex 73757072656d756d; asc supremum;;
....
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 710 page no 14 n bits 88 index PRIMARY of table `cloud`.`server_info_ex` trx id 13058786 lock_mode X waiting
....

*** WE ROLL BACK TRANSACTION (1)

可以看到是 X 锁,X锁是互斥锁。

原理

排它锁(X锁)和共享锁(S锁):

  • 所谓X锁,是事务T对数据A加上X锁时,只允许事务T读取和修改数据A,别的事务就没办法读取和修改,所以也叫排它锁,是互斥的
  • 所谓S锁,是事务T对数据A加上S锁时,其他事务只能再对数据A加S锁,而不能加X锁,直到T释放A上的S锁,别的事务也用加S锁,所以也叫共享锁,是不互斥的

若事务T对数据对象A加了S锁,则T就可以对A进行读取,但不能进行更新(S锁因此又称为读锁),在T释放A上的S锁以前,其他事务可以再对A加S锁也就是都能读取,但是不能写也就是不能加X锁, 从而可以读取A,但不能更新A.

记mysql gone away和死锁Deadlock解决方案

S X
S 不冲突 冲突
X 冲突 冲突

因为X和S锁是互斥的,session2想要X锁,必须等待session3的S锁释放, session3想要获得X锁也要session2释放S锁,这个时候构成了环路等待,引起了死锁。

我的死锁原因

我的事务1中update table1 会多很多个update,这里有7行记录被锁定,事务的更新大量数据时间比较长,更新会加x锁,而此时事务2是UPDATE table1 set xx=(SELECT table1 xxx),先select加s锁,然后想要x锁。

事务1的x锁正准备加上还是还没加上,实际是存在x锁,但是事务2加了s锁,事务1会等待事务2的s锁
事务2的完整事务加了s锁立即就要加x锁,但是事务1的x锁没有释放。造成了环路等待。

在最后我会做mysql锁全部情况分析。

MYSQL自身回滚了只加x锁的事务1,这个要读源码来看是为什么

解决办法

我的程序是监控系统的程序,这几个事务对数据实时性要求不高,我也可以决定他的执行时间,于是把他错峰执行就解决了。如果对实时性高的程序参考

  1. 分布式锁,可以利用Redis,或者ZK等等,把并行化修改为串行化进入,效率也不会太差,一般在互联网应用中,其实还是比较常见的方式,缺点就是引入了新的组件,这个地方还有一个缺 点就是,如果这是一个对外的接口,这个地方还需要考虑接口的幂等以及可用率,这里超出了本文的讨论范围。
  2. 重试机制,出现了事务1这种死锁,等待一会重试
  3. 将RR隔离级别,降低成RC隔离级别。这里RC隔离级别会用快照读,从而不会加S锁。
  4. 再插入的时候使用select * for update,加X锁,从而不会加S锁。

数据库隔离级别

隔离性

其中 隔离性 分为了四种:

  • READ UNCOMMITTED:可以读取未提交的数据,未提交的数据称为脏数据,所以又称脏读。此时:幻读,不可重复读和脏读均允许;
  • READ COMMITTED:只能读取已经提交的数据;此时:允许幻读和不可重复读,但不允许脏读,所以RC隔离级别要求解决脏读;
  • REPEATABLE READ:同一个事务中多次执行同一个select,读取到的数据没有发生改变;此时:允许幻读,但不允许不可重复读和脏读,所以RR隔离级别要求解决不可重复读;
  • SERIALIZABLE: 幻读,不可重复读和脏读都不允许,所以serializable要求解决幻读;

几个概念

  • 脏读:可以读取未提交的数据。RC 要求解决脏读;
  • 不可重复读:同一个事务中多次执行同一个select, 读取到的数据发生了改变(被其它事务update并且提交);
  • 可重复读:同一个事务中多次执行同一个select, 读取到的数据没有发生改变(一般使用MVCC实现);RR各级级别要求达到可重复读的标准;
  • 幻读:同一个事务中多次执行同一个select, 读取到的数据行发生改变。也就是行数减少或者增加了(被其它事务delete/insert并且提交)。SERIALIZABLE要求解决幻读问题;

这里一定要区分 不可重复读 和 幻读:

不可重复读的重点是修改:

同样的条件的select, 你读取过的数据, 再次读取出来发现值不一样了

幻读的重点在于新增或者删除:

同样的条件的select, 第1次和第2次读出来的记录数不一样

从结果上来看, 两者都是为多次读取的结果不一致。但如果你从实现的角度来看, 它们的区别就比较大:

对于前者, 在RC下只需要锁住满足条件的记录,就可以避免被其它事务修改,也就是 select for update, select in share mode; RR隔离下使用MVCC实现可重复读;

对于后者, 要锁住满足条件的记录及所有这些记录之间的gap,也就是需要 gap lock。

除了MySQL默认采用RR隔离级别之外,其它几大数据库都是采用RC隔离级别。

修改隔离级别方法

永久生效

transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE}

记mysql gone away和死锁Deadlock解决方案

改变单个会话或者所有新进连接的隔离级别(临时生效,重启失效)

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

记mysql gone away和死锁Deadlock解决方案

哪些情况可能会造成死锁

以下情况都是两个事务,RR隔离级别

s锁: insert/select
x锁:update

  • 情况1:同时拿到S请求X(事务 2 拿到了 S 锁,想加 X 锁,事务 1 拿到了 S 锁,也想加 X 锁,彼此都在等对方的 S 锁)。
  • 情况2:事务1拿到s请求x、事务2拿到x(有一个过程,先尝试x,标记锁等待状态,判断有没有冲突锁)发现事务1有s锁,则等待事务1释放s

记mysql gone away和死锁Deadlock解决方案

如上图,t1 的 加了 S 锁,t2 的 X 锁虽然没加成功,但是真实存在,标记为等待状态。t1 再想获取 X 锁,发现与 t2 等待状态的 X 锁冲突。再次检测,发现 t2 等待状态的 X 锁与 t1 的 S 锁冲突,死锁产生。

避免死锁的方法

有多种方法可以避免死锁,这里只介绍常见的三种:

  • 如果不同程序会并发存取多个表,尽量约定以相同的顺序访问表,可以大大降低发生死锁的可能性;
  • 在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁产生概率;
  • 对于非常容易产生死锁的业务部分,可以尝试使用升级锁定颗粒度,通过表级锁定来减少死锁产生的概率。

引用

MySQL 死锁问题分析
mysql死锁 Deadlock found when trying to get lock; try restarting transaction
大招落地:MySQL 插入更新死锁源码分析
MySQL 事务隔离级别解析和实战

weinxin
公众号
扫码订阅最新深度技术文,回复【资源】获取技术大礼包
MySQL最后更新:2020-11-9
小熊