Mysql锁和死锁分析

tab_test 结构如下：

id：主键;

state：状态;

time：时间;

索引：idx_1(state，time)

出现死锁的2条sql语句

update tab_test set state=1064,time=now() where state=1061 and time < date_sub(now(), INTERVAL 30 minute)
update tab_test set state=1067,time=now () where id in (9921180)

原因分析：

　　当“update tab_test set state=1064,time=now() where state=1061 and time < date_sub(now(), INTERVAL 30 minute)”执行时，MySQL会使用idx_1索引，因此首先锁定相关的索引记录，因为idx_1是非主键索引，为执行该语句，MySQL还会锁定主键索引。

　　假设“update tab_test set state=1067,time=now () where id in (9921180)”几乎同时执行时，本语句首先锁定主键索引，由于需要更新state的值，所以还需要锁定idx_1的某些索引记录。

　　这样第一条语句锁定了idx_1的记录，等待主键索引，而第二条语句则锁定了主键索引记录，而等待idx_1的记录，这样死锁就产生了。

在第一条语句给主键加锁前，第二条语句已经给主键加了锁，所以在高并发的数据操作时，死锁的情况就容易产生

InnoDB 会自动检测一个事务的死锁并回滚一个或多个事务来防止死锁。Innodb会选择代价比较小的事务回滚，此次事务（1）解锁并回滚，语句（2）继续运行直至事务结束。

解决办法

拆分第一条sql，先查出符合条件的主键值，再按照主键更新记录：

select id from tab_test where state=1061 and time < date_sub(now(), INTERVAL 30 minute);
update tab_test state=1064,time=now() where id in(......);

案例分析2

teamUser表的表结构如下：
PRIMARY KEY (`uid`,`Id`),
KEY `k_id_titleWeight_score` (`Id`,`titleWeight`,`score`),
ENGINE=InnoDB

出现死锁的2条sql语句

insert into teamUser_20110121 select * from teamUser
DELETE FROM teamUser WHERE teamId=$teamId AND titleWeight<32768 AND joinTime<‘$daysago_1week‘

两语句加锁情况
在innodb默认的事务隔离级别下，普通的SELECT是不需要加行锁的，但LOCK IN SHARE MODE、FOR UPDATE及高串行化级别中的SELECT都要加锁。有一个例外，此案例中，语句（1）insert into teamUser_20110121 select * from teamUser会对表teamUser_20110121（ENGINE= MyISAM）加表锁，并对teamUser表所有行的主键索引（即聚簇索引）加共享锁。默认对其使用主键索引。
而语句（2）DELETE FROM teamUser WHERE teamId=$teamId AND titleWeight<32768 AND joinTime<‘$daysago_1week‘为删除操作，会对选中行的主键索引加排他锁。由于此语句还使用了非聚簇索引KEY `k_teamid_titleWeight_score` (`teamId`,`titleWeight`,`score`)的前缀索引，于是，还会对相关行的此非聚簇索引加排他锁。
锁冲突的产生
由于共享锁与排他锁是互斥的，当一方拥有了某行记录的排他锁后，另一方就不能其拥有共享锁，同样，一方拥有了其共享锁后，另一方也无法得到其排他锁。所 以，当语句（1）、（2）同时运行时，相当于两个事务会同时申请某相同记录行的锁资源，于是会产生锁冲突。由于两个事务都会申请主键索引，锁冲突只会发生 在主键索引上。

避免死锁的方法
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.应用程序中增加对死锁的判断，如果事务意外结束，重新运行该事务，减少对功能的影响。

查看死锁记录

show InnoDB STATUS \G语句在mysql客户端中查看最近一次的死锁记录。

Mysql锁和死锁分析

标签：