mysql死锁日志在哪 mysql死锁时间

转载

lazihuman 2023-11-08 21:07:56

文章标签 mysql死锁日志在哪 mysql SQL 数据 文章分类 MySQL 数据库

MySQL批量更新死锁案例分析：

直接上图

mysql提示 Lock wait timeout exceeded

mysql死锁日志在哪 mysql死锁时间_mysql死锁日志在哪

原因：原因是你使用的InnoDB 表类型的时候,
默认参数:innodb_lock_wait_timeout设置锁等待的时间是50s,
因为有的锁等待超过了这个时间,所以抱错.

你可以把这个时间加长,或者优化存储过程,事务避免过长时间的等待.

解决的办法有两个：

第一：innodb_lock_wait_timeout 锁定等待时间改大

my.ini文件：

代码如下	复制代码
#innodb_lock_wait_timeout = 50 修改为 innodb_lock_wait_timeout = 500

show processlist; kill掉出现问题的进程
select * from
C.我的方法：设置MySQL锁等待超时 innodb_lock_wait_timeout=50 ，autocommit=on

别是当一个SQL执行完了，但未COMMIT，后面的SQL想要执行就是被锁，超时结束；

DBA光从

数据库

无法着手找出源头是哪个SQL锁住了；

有时候看看show

engine

innodb status

, 并结合 show full processlist; 能暂时解决问题；但一直不能精确定位；‘

mysql死锁日志在哪 mysql死锁时间_mysql_02

下面是转载：

在5.5中，information_schema 库中增加了三个关于锁的表（MEMORY引擎）；
SELECT * FROM information_schema.INNODB_LOCKS//当前出现的锁
SELECT * FROM information_schema.INNODB_LOCK_WAITS//锁等待的对应关系
SELECT * FROM information_schema.INNODB_TRX//当前运行的所有事务
看到这个就非常激动；这可是解决了一个大麻烦，先来看一下表结构

• root@127.0.0.1   : information_schema 13:28:38> desc innodb_locks;
+-------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| lock_id | varchar(81) | NO | | | |#锁ID
| lock_trx_id | varchar(18) | NO | | | |#拥有锁的事务ID
| lock_mode | varchar(32) | NO | | | |#锁模式
| lock_type | varchar(32) | NO | | | |#锁类型
| lock_table | varchar(1024) | NO | | | |#被锁的表
| lock_index | varchar(1024) | YES | | NULL | |#被锁的索引
| lock_space | bigint(21) unsigned | YES | | NULL | |#被锁的表空间号
| lock_page | bigint(21) unsigned | YES | | NULL | |#被锁的页号
| lock_rec | bigint(21) unsigned | YES | | NULL | |#被锁的记录号
| lock_data | varchar(8192) | YES | | NULL | |#被锁的数据
+-------------+---------------------+------+-----+---------+-------+
10 rows in set (0.00 sec)

root@127.0.0.1   : information_schema 13:28:56> desc innodb_lock_waits;+-------------------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| requesting_trx_id | varchar(18) | NO | | | |#请求锁的事务ID
| requested_lock_id | varchar(81) | NO | | | |#请求锁的锁ID
| blocking_trx_id | varchar(18) | NO | | | |#当前拥有锁的事务ID
| blocking_lock_id | varchar(81) | NO | | | |#当前拥有锁的锁ID
+-------------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

• root@127.0.0.1   : information_schema 13:29:05> desc innodb_trx ;
+----------------------------+---------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+---------------------+------+-----+---------------------+-------+
| trx_id | varchar(18) | NO | | | |#事务ID
| trx_state | varchar(13) | NO | | | |#事务状态：
| trx_started | datetime | NO | | 0000-00-00 00:00:00 | |#事务开始时间；
| trx_requested_lock_id | varchar(81) | YES | | NULL | |#innodb_locks.lock_id
| trx_wait_started | datetime | YES | | NULL | |#事务开始等待的时间
| trx_weight | bigint(21) unsigned | NO | | 0 | |#
| trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | |#事务线程ID
| trx_query | varchar(1024) | YES | | NULL | |#具体SQL语句
| trx_operation_state | varchar(64) | YES | | NULL | |#事务当前操作状态
| trx_tables_in_use | bigint(21) unsigned | NO | | 0 | |#事务中有多少个表被使用
| trx_tables_locked | bigint(21) unsigned | NO | | 0 | |#事务拥有多少个锁
| trx_lock_structs | bigint(21) unsigned | NO | | 0 | |#
| trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 | |#事务锁住的内存大小（B）
| trx_rows_locked | bigint(21) unsigned | NO | | 0 | |#事务锁住的行数
| trx_rows_modified | bigint(21) unsigned | NO | | 0 | |#事务更改的行数
| trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 | |#事务并发票数
| trx_isolation_level | varchar(16) | NO | | | |#事务隔离级别
| trx_unique_checks | int(1) | NO | | 0 | |#是否唯一性检查
| trx_foreign_key_checks | int(1) | NO | | 0 | |#是否外键检查
| trx_last_foreign_key_error | varchar(256) | YES | | NULL | |#最后的外键错误
| trx_adaptive_hash_latched | int(1) | NO | | 0 | |#
| trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 | |#
+----------------------------+---------------------+------+-----+---------------------+-------+
22 rows in set (0.01 sec)

下面我们来动手看看数据吧： ##建立测试数据：

•  use test;
 create table tx1
 (id int primary key ,
 c1 varchar(20),
 c2 varchar(30))
engine=innodb default charset = utf8 ;•  insert into tx1 values
 (1,'aaaa','aaaaa2'),
 (2,'bbbb','bbbbb2'),
 (3,'cccc','ccccc2');•  commit;
•  ###产生事务；
 ### Session1
 start transaction;
 update tx1 set c1='heyf',c2='heyf' where id =3 ;•  ## 产生事务，在innodb_trx就有数据 ；
root@127.0.0.1   : information_schema 13:38:21> select * from innodb_trx G
 *************************** 1. row ***************************
 trx_id: 3669D82
 trx_state: RUNNING
 trx_started: 2010-12-24 13:38:06
 trx_requested_lock_id: NULL
 trx_wait_started: NULL
 trx_weight: 3
 trx_mysql_thread_id: 2344
 trx_query: NULL
 trx_operation_state: NULL
 trx_tables_in_use: 0
 trx_tables_locked: 0
 trx_lock_structs: 2
 trx_lock_memory_bytes: 376
 trx_rows_locked: 1
 trx_rows_modified: 1
 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)•  ### 由于没有产生锁等待，下面两个表没有数据 ；
 information_schema 13:38:31> select * from innodb_lock_waits G
 Empty set (0.00 sec)•  information_schema 13:38:57> select * from innodb_locks G
 Empty set (0.00 sec)•  #### 产生锁等待
 #### session 2
 start transaction;
 update tx1 set c1='heyfffff',c2='heyffffff' where id =3 ;•  
root@127.0.0.1   : information_schema 13:39:01> select * from innodb_trx G
 *************************** 1. row ***************************
 trx_id: 3669D83 ##第2个事务
 trx_state: LOCK WAIT ## 处于等待状态
 trx_started: 2010-12-24 13:40:07
 trx_requested_lock_id: 3669D83:49:3:4 ##请求的锁ID
 trx_wait_started: 2010-12-24 13:40:07
 trx_weight: 2
 trx_mysql_thread_id: 2346 ##线程 ID
 trx_query: update tx1 set c1='heyfffff',c2='heyffffff' where id =3
 trx_operation_state: starting index read
 trx_tables_in_use: 1 ##需要用到1个表
 trx_tables_locked: 1 ##有1个表被锁
 trx_lock_structs: 2
 trx_lock_memory_bytes: 376
 trx_rows_locked: 1
 trx_rows_modified: 0
 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
 *************************** 2. row ***************************
 trx_id: 3669D82 ##第1个事务
 trx_state: RUNNING
 trx_started: 2010-12-24 13:38:06
 trx_requested_lock_id: NULL
 trx_wait_started: NULL
 trx_weight: 3
 trx_mysql_thread_id: 2344
 trx_query: NULL
 trx_operation_state: NULL
 trx_tables_in_use: 0
 trx_tables_locked: 0
 trx_lock_structs: 2
 trx_lock_memory_bytes: 376
 trx_rows_locked: 1
 trx_rows_modified: 1
 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
 2 rows in set (0.00 sec)•  root@127.0.0.1   : information_schema 13:40:12> select * from innodb_locks G
 *************************** 1. row ***************************
 lock_id: 3669D83:49:3:4 ## 第2个事务需要的锁
 lock_trx_id: 3669D83
 lock_mode: X
 lock_type: RECORD
 lock_table: `test`.`tx1`
 lock_index: `PRIMARY`
 lock_space: 49
 lock_page: 3
 lock_rec: 4
 lock_data: 3
 *************************** 2. row ***************************
 lock_id: 3669D82:49:3:4 ## 第1个事务需要的锁
 lock_trx_id: 3669D82
 lock_mode: X
 lock_type: RECORD
 lock_table: `test`.`tx1`
 lock_index: `PRIMARY`
 lock_space: 49
 lock_page: 3
 lock_rec: 4
 lock_data: 3
 2 rows in set (0.00 sec)•  root@127.0.0.1   : information_schema 13:40:15> select * from innodb_lock_waits G
 *************************** 1. row ***************************
requesting_trx_id: 3669D83 ## 请求锁的事务
requested_lock_id: 3669D83:49:3:4 ## 请求锁的锁ID
blocking_trx_id: 3669D82 ## 拥有锁的事务
blocking_lock_id: 3669D82:49:3:4 ## 拥有锁的锁ID
1 row in set (0.00 sec)

其实解决的步骤如下：

1，查看数据库的隔离级别：

mysql> select @@tx_isolation;
 +-----------------+
 | @@tx_isolation |
 +-----------------+
 | REPEATABLE-READ |
 +-----------------+
 1 row in set (0.00 sec)

 mysql>

2，去查看先当前库的线程情况：

mysql> show full processlist;
+----------+-----------------+-------------------+-----------------+-------------+---------+-------------------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----------+-----------------+-------------------+-----------------+-------------+---------+-------------------------+-----------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 9635385 | Waiting on empty queue | NULL |
| 9930577 | business_web | 192.168.1.21:45503 | business_db | Sleep | 153 | | NULL |
| 9945825 | business_web | 192.168.1.25:49518 | business_db | Sleep | 43 | | NULL |
| 9946322 | business_web | 192.168.1.23:44721 | business_db | Sleep | 153 | | NULL |
| 9960167 | business_web | 192.168.3.28:2409 | business_db | Sleep | 93 | | NULL |
| 9964484 | business_web | 192.168.1.21:24280 | business_db | Sleep | 7 | | NULL |
| 9972499 | business_web | 192.168.3.28:35752 | business_db | Sleep | 13 | | NULL |
| 10000117 | business_web | 192.168.3.28:9149 | business_db | Sleep | 6 | | NULL |
| 10002523 | business_web | 192.168.3.29:42872 | business_db | Sleep | 6 | | NULL |
| 10007545 | business_web | 192.168.1.21:51379 | business_db | Sleep | 155 | | NULL |
 ......+----------+-----------------+-------------------+-----------------+-------------+---------+-------------------------+-----------------------+

没有看到正在执行的慢SQL记录线程，再去查看innodb的事务表INNODB_TRX，看下里面是否有正在锁定的事务线程，看看ID是否在show full processlist里面的sleep线程中，如果是，就证明这个sleep的线程事务一直没有commit或者rollback而是卡住了，我们需要手动kill掉。

mysql> SELECT * FROM information_schema.INNODB_TRX  G;
*************************** 1. row ***************************
trx_id: 20866
trx_state: LOCK WAIT
trx_started: 2014-07-31 10:42:35
trx_requested_lock_id: 20866:617:3:3
trx_wait_started: 2014-07-30 10:42:35
trx_weight: 2
trx_mysql_thread_id: 9930577
trx_query: delete from dltask where id=1
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 376
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: READ COMMITTED
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
trx_is_read_only: 0
trx_autocommit_non_locking: 0

3，看到有这条9930577的sql，kill掉，执行kill 9930577;

mysql> kill 9930577;
Query OK, 0 rows affected (0.00 sec)
 
mysql>

然后再去查询INNODB_TRX表，就没有阻塞的事务sleep线程存在了，如下所示：

mysql> SELECT * FROM INNODB_TRX  G;
Empty set (0.00 sec)
 
ERROR:
No query specified
 
mysql>

再去执行update语句，就能正常执行了，如下所示：

mysql> update order_info set province_id=15 ,city_id= 1667 where order_from=10 and order_out_sn='1407261241xxxx';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
 
mysql>

4，总结分析
表数据量也不大，按照普通的情况来说，简单的update应该不会造成阻塞的，mysql都是autocommit，不会出现update卡住的情况，去查看下autocommit的值。

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)


mysql>

看到亮闪闪的0，这个设置导致原来的update语句如果没有commit的话，你再重新执行update语句，就会等待锁定，当等待时间过长的时候，就会报ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction的错误。
所以赶紧commit刚才执行的update语句，之后 set global autocommit=1;

MySQL批量更新死锁案例分析

问题描述

在做项目的过程中，由于写SQL太过随意，一不小心就抛了一个死锁异常，如下：

[java]

1. com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction  
2.         at sun.reflect.GeneratedConstructorAccessor247.newInstance(Unknown Source)  
3. 27)  
4. 513)  
5. 406)  
6. 381)  
7. 1045)  
8. 956)  
9. 3491)  
10. 3423)  
11. 1936)  
12. 2060)  
13. 2542)  
14. 1734)  
15. 2019)  
16. 1937)  
17. 1922)

表结构如下：

[sql]

1. CREATE TABLE `user_item` (  
2. BIGINT(20) NOT NULL,  
3. BIGINT(20) NOT NULL,  
4. BIGINT(20) NOT NULL,  
5. NOT NULL,  
6. PRIMARY KEY (`id`),  
7. KEY `idx_1` (`user_id`,`item_id`,`status`)  
8. ) ENGINE=INNODB DEFAULT CHARSET=utf-8

SQL语句如下：

[sql]

update user_item set status=1 where user_id=? and item_id=? 【这里用到的上面的索引idx_1,所以肯定会加上行级锁】

原因分析

mysql的事务支持与存储引擎有关，---

1.MyISAM:不支持事务，

2.INNODB支持事务，更新时采用的是行级锁【行级锁必须建立在索引的基础】。

这里采用的是INNODB做存储引擎，意味着会将update语句做为一个事务来处理。

行级锁并不是直接锁记录，而是锁索引，

如果一条SQL语句用到了主键索引，mysql会锁住主键索引；

如果一条语句操作了非主键索引，mysql会先锁住非主键索引，再锁定主键索引。

这个update语句会执行以下步骤：

step1、由于用到了非主键索引，首先需要获取idx_1上的行级锁

step2、紧接着根据主键进行更新，所以需要获取主键上的行级锁；

step3、更新完毕后，提交，并释放所有锁。

如果在步骤1和2之间突然插入一条语句：

update user_item .....where id=? and user_id=?,这条语句会先锁住主键索引，然后锁住idx_1。

蛋疼的情况出现了，一条语句获取了idx_1上的锁，等待主键索引上的锁；另一条语句获取了主键上的锁，等待idx_1上的锁，这样就出现了死锁

解决方案

1、先获取需要更新的记录的主键

[sql]

select id from user_item where user_id=? and item_id=?

2、逐条更新

[java]

for (Long id : idList) {
1);
}

[sql]

update user_item set status=? where id=? and user_id=?

这样貌似解决了，都是对单条进行操作，都是先获取主键上的锁，再获取idx_1上的锁。

不过这个解决方案与先前的更新语句不一样，

先前的更新语句对所有记录的更新在一个事务中，

新的更新语句采用循环更新后并不在同一个事务中，

所以在for循环外面还得开一个事务。

[java]

1. Exception e = (Exception)getDbfeelTransactionTemplate().execute(new TransactionCallback() {  
2. public Object doInTransaction(TransactionStatus status) {  
3. try {  
4. for(Long id:idList) {  
5. userItemDAO.updateStatus(id,userId,1)  
6.         }  
7. return null;  
8. catch(DAOException e) {  
9.          status.setRollbackOnly();  
10. return e;  
11.       }  
12. catch (Exception e) {  
13.          status.setRollbackOnly();  
14. return e;  
15.       }  
16.    }  
17. });

小结：在采用INNODB的MySQL中，

更新操作默认会加行级锁，行级锁是基于索引的，

在分析死锁之前需要查询一下mysql的执行计划，看看是否用到了索引，用到了哪个索引，

对于没有用索引的操作会采用表级锁。

如果操作用到了主键索引会先在主键索引上加锁，然后在其他索引上加锁，否则加锁顺序相反。

在并发度高的应用中，批量更新一定要带上记录的主键，优先获取主键上的锁，

这样可以减少死锁的发生。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。