简单理解mysql/InnoDB的死锁问题

MySQL三种锁的级别：

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

 

数据库存储引擎：

MyISAM和MEMORY存储引擎，采用的是表级锁（table-level locking）；

BDB存储引擎，采用的是页面锁（page-level locking），但也支持表级锁；

InnoDB存储引擎，既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

注意：InnoDB行级锁是基于索引的，如果一条SQL的条件字段并没有添加索引，则使用表级锁

 

行级锁加锁顺序：

如果一条SQL语句操作了主键索引（PRIMARY KEY），mysql会锁住主键索引；

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

 

项目中死锁关键报错信息：

### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

 

InnoDB可以通过如下命令来观察锁的情况：

select * from information_schema.INNODB_TRX; #查看事务情况

select * from information_schema.INNODB_LOCKS; #查看锁情况

select * from information_schema.INNODB_LOCK_WAITS; #查看锁等待情况

 

mysql/InnoDB死锁产生示例：

新建一个test表，结构如下，其中id为主键索引，aa为普通索引：

CREATE TABLE `test` (                    
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `aa` varchar(50) DEFAULT NULL,         
  `bb` varchar(50) DEFAULT NULL,         
  `cc` varchar(50) DEFAULT NULL,         
  PRIMARY KEY (`id`),                    
  KEY `aa` (`aa`)                        
) ENGINE=InnoDB DEFAULT CHARSET=utf8    

--sql1:

update test set bb = '222' where aa = '11'

--sql2:

update test set aa = '111' where id = 1

 

根据死锁示例进行分析：

首先sql1会先锁住非主键索引aa，还需要锁定主键索引id，与此同时

sql2直接锁定主键索引id，而其中update语句中set还使用了aa，同时还需要锁定非主键索引aa

因此两条sql就出现了对索引资源的竞争，造成死锁。

 

死锁示例解决方案：

对sql1进行拆分，先根据条件查询出数据，再根据数据id进行update

select * from test where aa = '11'

update test set bb = '222' where id in()