解决MySQL事务未提交导致死锁报错 避免死锁的方法
时间:2019-01-03 14:06 来源:未知 作者:IT
解决mysql 事务未提交导致死锁报错：
当 sessionA 尝试修改 B 表数据，因为 sessionB 当前为锁定状态，而且 sessionB 对 B 表中数据具有锁定状态中，则出现死锁。sessionB 会自动终止尝试修改 A 表数据事务， 两个事务操作都被终止，并返回下面错误信息。
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
查看当前运行的所有事务
mysql> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
trx_id: 45900
trx_state: ROLLING BACK
trx_started: 2018-04-09 10:24:38
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 30687
trx_mysql_thread_id: 0
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 1
trx_lock_memory_bytes: 320
trx_rows_locked: 1
trx_rows_modified: 30686
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
1 row in set (0.00 sec)
关注事务所在线程ID:
trx_mysql_thread_id: 0
线程0的事务没有commit导致死锁，执行：
kill 0; //强制关闭线程和事务
执行结果：ERROR 1094 (HY000): Unknown thread id: 0
发现并没有解决问题，继续。
MYSQL中如何强制终止一条语句的执行
KILL命令的语法格式如下：KILL [CONNECTION | QUERY] thread_id
步骤如下：
1、KILL允许自选的CONNECTION或QUERY修改符：KILL CONNECTION与不含修改符的KILL一样：它会终止与给定的thread_id有关的连接。
2、KILL QUERY会终止连接当前正在执行的语句，但是会保持连接的原状。
3、如果您拥有PROCESS权限，则您可以查看所有线程。
4、如果您拥有超级管理员权限，您可以终止所有线程和语句。否则，您只能查看和终止您自己的线程和语句。
5、您也可以使用mysqladmin processlist和mysqladmin kill命令来检查和终止线程。
首先登录mysql，然后使用： show processlist; 查看当前mysql中各个线程状态。
以上显示出当前正在执行的sql语句列表，找到消耗资源最大的那条语句对应的id.
然后运行kill命令，命令格式如下：
[sql] view plain copy
kill id;
- 示例:
kill 8358
避免死锁的方法
InnoDB给MySQL提供了具有提交，回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句提供非锁定读。这些特色增加了多用户部署和性能。
但其行锁的机制也带来了产生死锁的风险，这就需要在应用程序设计时避免死锁的发生。以单个SQL语句组成的隐式事务来说，建议的避免死锁的方法如下：
1.如果使用insert…select语句备份表格且数据量较大，在单独的时间点操作，避免与其他sql语句争夺资源，或使用select into outfile加上load data infile代替 insert…select，这样不仅快，而且不会要求锁定
2. 一个锁定记录集的事务，其操作结果集应尽量简短，以免一次占用太多资源，与其他事务处理的记录冲突。
3.更新或者删除表格数据，sql语句的where条件都是主键或都是索引，避免两种情况交叉，造成死锁。对于where子句较复杂的情况，将其单独通过sql得到后，再在更新语句中使用。
4. sql语句的嵌套表格不要太多，能拆分就拆分，避免占有资源同时等待资源，导致与其他事务冲突。
5. 对定点运行脚本的情况，避免在同一时间点运行多个对同一表进行读写的脚本，特别注意加锁且操作数据量比较大的语句。
6.应用程序中增加对死锁的判断，如果事务意外结束，重新运行该事务，减少对功能的影响。
（六）事务的提交与回滚极死锁检测、处理和预防
事务的提交与回滚极死锁检测、处理和预防
（一）MySQL InnoDB事务模型
（二）MySQL InnoDB锁模型
（三）MySQL InnoDB非锁定一致性读与锁定读
（四）MySQL InnoDB锁类型及幻象读问题
（五）MySQL InnoDB中各类语句加锁方式
（六）事务的提交与回滚极死锁检测、处理和预防
事务的提交与回滚
默认情况下，MySQL开启自动提交，每条语句执行完成且运行无误的情况下会被自动提交。若语句发生了错误，提交或回滚与具体的SQL有关。另外，还有一些语句会隐式的结束一个事务，就好比在执行这些SQL前执行了COMMIT语句。
若发生了Table is full错误 InnoDB会回滚语句。
死锁导致InnoDB回滚整个事务。若单是发生了 lock wait timeout则InnoDB仅会回滚事务中等待锁并发生超时的SQL语句。若想在此种情况下回滚整个事务，可通过同时开启 --innodb_rollback_on_timeout选项。死锁和锁等待在繁忙的服务器中很常见。应用需妥善处理这些情况，尽可能在较少的记录上持有锁，并且锁定的时间尽可能的短。若是通过START TRANSACTION或BEGIN明确开启事务，则死锁或者锁等待超时导致的回滚并不会关闭当前事务，后续的SQL语句仍会成为当前事务的一部分，除非使用COMMIT，ROLLBACK或者其他隐式提交事务的语句。
若没有指定IGNORE则 duplicate-key error会导致SQL语句回滚。row too long error也会导致SQL语句回滚。其他的错误大多由MySQL Server层而非InnoDB引擎层检测，会回滚SQL语句。单挑SQL语句回滚不会释放事务持有的锁。
一些会隐式的结束事务的SQL，分为几大类：定义或修改数据库对象的DDL语句；隐式的使用或者修改mysql库中的表的语句；事务控制与锁定语句；数据导入语句；数据库管理语句；复制控制语句等。具体可参考官方手册：http://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html
InnoDB中死锁自动被检测出，并选择代价较小的事务进行回滚以打破死锁。事务完全回滚后其保持的锁被全部释放，若是仅有单条SQL由于错误发生了回滚则语句保持的锁可能不会被释放，因为InnoDB中不保存哪条语句持有哪些锁的信息。若事务中的select调用了存储函数，函数中的SQL执行失败，则该语句被回滚。
死锁是事务型应用中的典型问题，不可消除只能尽可能避免。死锁并不危险但频繁出现就有问题了。应用中应做好出现死锁导致事务回滚后的后续处理逻辑。
如何预防和处理死锁？
对于DBA，可以通过SHOW ENGINE INNODB STATUS 查看最近的死锁信息，以辅助调整应用。另外，若想看到更加详细的信息可开启innodb_print_all_deadlocks 配置，这样可以在error log中看到所有的死锁信息而非最近的一个（调试结束后记得关闭）。
应用中需要有死锁发生后导致事务回滚的处理逻辑。
尽量保持事务短小精悍以避免冲突。
尽早提交事务，不要保持一个有长期未关闭事务的交互式mysql session。
若非必要则不使用锁定读，若要选择使用锁定读则可选择较低的事务隔离级别如 READ COMMITTED。
若在同一事务中修改多个表或者修改同一表中的不同行，则每次尽量按一致的顺序进行操作。
为表添加合理的索引，并用explain确认SQL能否使用到合适的索引。
特殊情况下可以使用表锁。
通过设置辅助表来序列化事务，表中只包含一行，事务在访问其他表前必须更新该表中的行，这样可保证事务的串行执行，避免死锁。
不好的事务使用习惯
· 在循环中提交事务
· 使用自动提交
· 自动回滚
· 长事务
(责任编辑：IT)
解决mysql 事务未提交导致死锁报错：
当 sessionA 尝试修改 B 表数据，因为 sessionB 当前为锁定状态，而且 sessionB 对 B 表中数据具有锁定状态中，则出现死锁。sessionB 会自动终止尝试修改 A 表数据事务， 两个事务操作都被终止，并返回下面错误信息。
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
查看当前运行的所有事务
mysql> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
trx_id: 45900
trx_state: ROLLING BACK
trx_started: 2018-04-09 10:24:38
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 30687
trx_mysql_thread_id: 0
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 1
trx_lock_memory_bytes: 320
trx_rows_locked: 1
trx_rows_modified: 30686
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
1 row in set (0.00 sec)
关注事务所在线程ID:
trx_mysql_thread_id: 0
线程0的事务没有commit导致死锁，执行：
kill 0; //强制关闭线程和事务
执行结果：ERROR 1094 (HY000): Unknown thread id: 0
发现并没有解决问题，继续。
MYSQL中如何强制终止一条语句的执行
KILL命令的语法格式如下：KILL [CONNECTION | QUERY] thread_id
步骤如下：
1、KILL允许自选的CONNECTION或QUERY修改符：KILL CONNECTION与不含修改符的KILL一样：它会终止与给定的thread_id有关的连接。
2、KILL QUERY会终止连接当前正在执行的语句，但是会保持连接的原状。
3、如果您拥有PROCESS权限，则您可以查看所有线程。
4、如果您拥有超级管理员权限，您可以终止所有线程和语句。否则，您只能查看和终止您自己的线程和语句。
5、您也可以使用mysqladmin processlist和mysqladmin kill命令来检查和终止线程。
首先登录mysql，然后使用： show processlist; 查看当前mysql中各个线程状态。
以上显示出当前正在执行的sql语句列表，找到消耗资源最大的那条语句对应的id.
然后运行kill命令，命令格式如下：
[sql] view plain copy
kill id;
- 示例:
kill 8358
避免死锁的方法
InnoDB给MySQL提供了具有提交，回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句提供非锁定读。这些特色增加了多用户部署和性能。
但其行锁的机制也带来了产生死锁的风险，这就需要在应用程序设计时避免死锁的发生。以单个SQL语句组成的隐式事务来说，建议的避免死锁的方法如下：
1.如果使用insert…select语句备份表格且数据量较大，在单独的时间点操作，避免与其他sql语句争夺资源，或使用select into outfile加上load data infile代替 insert…select，这样不仅快，而且不会要求锁定
2. 一个锁定记录集的事务，其操作结果集应尽量简短，以免一次占用太多资源，与其他事务处理的记录冲突。
3.更新或者删除表格数据，sql语句的where条件都是主键或都是索引，避免两种情况交叉，造成死锁。对于where子句较复杂的情况，将其单独通过sql得到后，再在更新语句中使用。
4. sql语句的嵌套表格不要太多，能拆分就拆分，避免占有资源同时等待资源，导致与其他事务冲突。
5. 对定点运行脚本的情况，避免在同一时间点运行多个对同一表进行读写的脚本，特别注意加锁且操作数据量比较大的语句。
6.应用程序中增加对死锁的判断，如果事务意外结束，重新运行该事务，减少对功能的影响。
（六）事务的提交与回滚极死锁检测、处理和预防
事务的提交与回滚极死锁检测、处理和预防
（一）MySQL InnoDB事务模型
（二）MySQL InnoDB锁模型
（三）MySQL InnoDB非锁定一致性读与锁定读
（四）MySQL InnoDB锁类型及幻象读问题
（五）MySQL InnoDB中各类语句加锁方式
（六）事务的提交与回滚极死锁检测、处理和预防
事务的提交与回滚
默认情况下，MySQL开启自动提交，每条语句执行完成且运行无误的情况下会被自动提交。若语句发生了错误，提交或回滚与具体的SQL有关。另外，还有一些语句会隐式的结束一个事务，就好比在执行这些SQL前执行了COMMIT语句。
若发生了Table is full错误 InnoDB会回滚语句。
死锁导致InnoDB回滚整个事务。若单是发生了 lock wait timeout则InnoDB仅会回滚事务中等待锁并发生超时的SQL语句。若想在此种情况下回滚整个事务，可通过同时开启 --innodb_rollback_on_timeout选项。死锁和锁等待在繁忙的服务器中很常见。应用需妥善处理这些情况，尽可能在较少的记录上持有锁，并且锁定的时间尽可能的短。若是通过START TRANSACTION或BEGIN明确开启事务，则死锁或者锁等待超时导致的回滚并不会关闭当前事务，后续的SQL语句仍会成为当前事务的一部分，除非使用COMMIT，ROLLBACK或者其他隐式提交事务的语句。
若没有指定IGNORE则 duplicate-key error会导致SQL语句回滚。row too long error也会导致SQL语句回滚。其他的错误大多由MySQL Server层而非InnoDB引擎层检测，会回滚SQL语句。单挑SQL语句回滚不会释放事务持有的锁。
一些会隐式的结束事务的SQL，分为几大类：定义或修改数据库对象的DDL语句；隐式的使用或者修改mysql库中的表的语句；事务控制与锁定语句；数据导入语句；数据库管理语句；复制控制语句等。具体可参考官方手册：http://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html
InnoDB中死锁自动被检测出，并选择代价较小的事务进行回滚以打破死锁。事务完全回滚后其保持的锁被全部释放，若是仅有单条SQL由于错误发生了回滚则语句保持的锁可能不会被释放，因为InnoDB中不保存哪条语句持有哪些锁的信息。若事务中的select调用了存储函数，函数中的SQL执行失败，则该语句被回滚。
死锁是事务型应用中的典型问题，不可消除只能尽可能避免。死锁并不危险但频繁出现就有问题了。应用中应做好出现死锁导致事务回滚后的后续处理逻辑。
如何预防和处理死锁？
对于DBA，可以通过SHOW ENGINE INNODB STATUS 查看最近的死锁信息，以辅助调整应用。另外，若想看到更加详细的信息可开启innodb_print_all_deadlocks 配置，这样可以在error log中看到所有的死锁信息而非最近的一个（调试结束后记得关闭）。
应用中需要有死锁发生后导致事务回滚的处理逻辑。
尽量保持事务短小精悍以避免冲突。
尽早提交事务，不要保持一个有长期未关闭事务的交互式mysql session。
若非必要则不使用锁定读，若要选择使用锁定读则可选择较低的事务隔离级别如 READ COMMITTED。
若在同一事务中修改多个表或者修改同一表中的不同行，则每次尽量按一致的顺序进行操作。
为表添加合理的索引，并用explain确认SQL能否使用到合适的索引。
特殊情况下可以使用表锁。
通过设置辅助表来序列化事务，表中只包含一行，事务在访问其他表前必须更新该表中的行，这样可保证事务的串行执行，避免死锁。
不好的事务使用习惯
· 在循环中提交事务
· 使用自动提交
· 自动回滚
· 长事务
|