有关mysql死锁

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

直接上图

mysql提示 Lock wait timeout exceeded

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

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

解决的办法有两个：

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

my.ini文件：

 代码如下复制代码

#innodb_lock_wait_timeout = 50

修改为

innodb_lock_wait_timeout = 500

• A.应急方法：show processlist; kill掉出现问题的进程

• B.根治方法：select * from innodb_trx 查看有是哪些事务占据了表资源

• C.我的方法：设置MySQL锁等待超时 innodb_lock_wait_timeout=50 ，autocommit=on

• 
  

别是当一个SQL执行完了，但未COMMIT，后面的SQL想要执行就是被锁，超时结束；
DBA光从数据库无法着手找出源头是哪个SQL锁住了；
有时候看看show engine innodb status , 并结合 show full processlist; 能暂时解决问题；但一直不能精确定位；‘

下面是转载：

• 在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] view plaincopyprint?

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.         at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)  
4.         at java.lang.reflect.Constructor.newInstance(Constructor.java:513)  
5.         at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)  
6.         at com.mysql.jdbc.Util.getInstance(Util.java:381)  
7.         at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1045)  
8.         at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)  
9.         at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)  
10.         at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)  
11.         at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)  
12.         at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)  
13.         at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)  
14.         at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)  
15.         at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2019)  
16.         at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1937)  
17.         at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1922)  

表结构如下：

[sql] view plaincopyprint?

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

SQL语句如下：

[sql] view plaincopyprint?

1. 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] view plaincopyprint?

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

2、逐条更新

[java] view plaincopyprint?

1. for (Long id : idList) {  
2.     userItemDAO.updateStatus(id, userId, 1);  
3. }  

[sql] view plaincopyprint?

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

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

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

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

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

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

[java] view plaincopyprint?

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的执行计划，看看是否用到了索引，用到了哪个索引，

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

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

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

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