最近运行了两年的程序，在多个项目上出现了gone away和 Deadlock，下面记录下解决方案，以便以后查询。
mysql gone away 可能原因
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.
| 锁 | 锁 | 锁 |
|---|---|---|
| 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，这个要读源码来看是为什么
解决办法
我的程序是监控系统的程序，这几个事务对数据实时性要求不高，我也可以决定他的执行时间，于是把他错峰执行就解决了。如果对实时性高的程序参考
- 分布式锁，可以利用Redis,或者ZK等等，把并行化修改为串行化进入，效率也不会太差，一般在互联网应用中，其实还是比较常见的方式，缺点就是引入了新的组件，这个地方还有一个缺 点就是，如果这是一个对外的接口，这个地方还需要考虑接口的幂等以及可用率，这里超出了本文的讨论范围。
- 重试机制，出现了事务1这种死锁，等待一会重试
- 将RR隔离级别，降低成RC隔离级别。这里RC隔离级别会用快照读，从而不会加S锁。
- 再插入的时候使用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}
改变单个会话或者所有新进连接的隔离级别（临时生效，重启失效）
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
哪些情况可能会造成死锁
以下情况都是两个事务，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
如上图，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 事务隔离级别解析和实战
评论