mysql死锁问题

背景知识：
MySQL有三种锁的级别：页级、表级、行级。
MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

MySQL这3种锁的特性可大致归纳如下：

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

行级锁并不是直接锁记录，而是锁索引，如果一条SQL语句用到了主键索引，mysql会锁住主键索引；如果一条语句操作了非主键索引，mysql会先锁住非主键索引，再锁定主键索引。行级锁：分为 共享锁 和排他锁。

共享锁兼容请求的共享锁，不兼容排他锁。

排他锁不兼容共享锁和排他锁。 

select 语句不会去获取锁，如果想要给查询的行加锁，使用for update来加排他锁， lock in share mode来加共享锁。

注意:1 .insert、delete、update 都会获取排他锁。

         2. 如果语句中没有用到索引，而试图加上行级锁，就会获取表锁

select * from user_item where name  = 'z' for update。

          2. 行级锁是给索引加锁，如果一条语句用到了非主键索引，会先给非主键索引加锁，在给主键索引加锁

示例：

错误中提示下面这条sql发生了死锁

UPDATE coupon SET coup_num_usr = coup_num_usr + 1 WHERE coup_usr = ? AND spec_id = ? AND coup_num_usr < ?

因为一条语句获取了普通索引的锁，等待主键锁，另外一条语句获取了主键锁，等待非主键索引，这样就出现了死锁.

如何来解决update ... where ...语句的死锁问题呢？我们可以对其进行分离，首先利用where条件找到主键，然后再利用这些主键去更新数据。

因为select * where ...语句是没有锁的，所以不存在会锁上where条件里面的字段，也就不会发生死锁的情况，只有在update的时候回锁上主键。

所以改成下面两条语句

SELECT id WHERE coup_usr = ? AND spec_id = ?
 
UPDATE coupon SET coup_num_usr = coup_num_usr + 1 WHERE id = ? AND coup_num_usr < ?

第一条语句找出所有需要更新行的主键id，然后再一条一条更新。

可以通过SHOW ENGINE INNODB STATUS;来查看死锁日志 

根据日志来排查