mysql的死锁情况

mysql锁机制分为表级锁和行级锁还有页级锁，其中还有乐观锁和悲观锁、共享锁和排他锁。

myisam 和 memory 存储引擎采用的是 表级锁； innodb 存储引擎既支持行级锁，也支持表级锁，但默认情况下采用行级锁。

共享锁又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

排他锁又称为写锁，简称X锁，顾名思义，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

注意：排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁。

mysql InnoDB引擎默认的update,delete,insert都会自动给涉及到的数据加上排他锁，而select语句默认不会加任何锁类型。如果要加排他锁可以使用 select ... for update 语句，加共享锁可以使用 select ... lock in share mode 语句。

这里要注意一点：普通查询没有任何锁机制（即 select...from...where...），不与排他锁互斥，不管是加了共享锁还是排他锁，普通查询都可以查到数据，只不过查到的数据有可能是旧数据。

 

死锁

数据库和操作系统一样，是一个多用户使用的共享资源，当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。当两个事务需要一组有冲突的锁，而不能将事务继续下去的话，就会出现死锁。

死锁的第一种情况

步骤	用户A	用户B
1	begin;	begin;
2	select * from tb_A where id=1 for update;
3		select * from tb_B where id=1 for update;
4	select * from tb_B where id=1 for update; 或 select * from tb_B where id=1 lock in share mode;
5		select * from tb_A where id=1 for update; 或 select * from tb_B where id=1 lock in share mode;

分析：

用户A访问表A并锁住表A的数据（排他锁），然后用户B访问表B并锁住表B的数据（排他锁）；这时用户A想要访问表B并加锁，用户B想要访问表A并加锁。这时由于用户B已经锁住表B，用户A必须等待用户B释放表B的锁才能继续，同样用户A已经锁住表A，用户B要等用户A释放表A才能继续，这就死锁就产生了。

解决方法：

仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理， 必须同时锁定两个资源，要保证在任何时刻都应该按照相同的顺序来锁定资源。

 

死锁的第二种情况

步骤	用户A	用户B
1	begin;	begin;
2	select * from tb_A where id=1 for update;
3		update tb_A set user_id=2 where id=2;
4	update tb_A set user_id=1 where id=2;
5		update tb_A set user_id=2 where id=1;

步骤	用户A	用户B
1	begin;	begin;
2	select * from tb_A where id=1 lock in share mode;
3		select * from tb_A where id=1 lock in share mode;
4	update tb_A set user_id=1 where id=1;
5		update tb_A set user_id=1 where id=1;

分析：

用户A查询表A中id为1的数据并加锁（共享锁或排他锁），用户B更改表A中id为2的数据（排他锁）；然后用户A想要更改表A中id为2的数据，同时用户B想要更改表A中id为1的数据。这时用户A由于用户B对id为2的数据加锁而无法获得锁，用户B由于用户A对id为1的数据加锁而无法获得锁，于是出现了死锁。

用户A查询表A中id为1的数据并加锁（共享锁），用户B查询表A中id为1的数据并加锁（共享锁）；然后用户A想要更改表A中id为1的数据，同时用户B也想更改表A中id为1的数据。用户A的事务里锁的性质由查询的共享锁企图上升到独占锁，而用户B也是由查询的共享锁企图上升为独占锁，由于A 有共享锁存在所以B必须等A释放掉共享锁，同样B有共享锁存在所以A必须等B释放掉共享锁，因此A和B互相等待对方释放锁，于是出现了死锁。

如在某项目中，页面上的按钮点击后，没有使按钮立刻失效，使得用户会多次快速点击同一按钮，这样同一段代码对数据库同一条记录进行多次操 作，很容易就出现这种死锁的情况。

解决方法：

1、对于按钮等控件，点击后使其立刻失效，不让用户重复点击，避免对同时对同一条记录操作。

2、使用乐观锁进行控制。乐观锁是基于数据版本（Version）记录机制实现。通过为数据库表增加一个“version”字段，读取出数据时，将此版本号一同读出，之后更新时，对此版本号+1，将提交数据的版本号与数据库表中对应记录的当前版本号进行比对，如果提交的版本号大于数据库表当前的版本号，则予以更新，否则认为是过期数据。乐观锁机制避免了长事务中数据库加锁的开销（因为用户A和用户B操作过程中，都没有必要对数据库数据加锁了，只需比对版本号），大大提升了大并发量下的系统整体性能表现。

3、使用悲观锁进行控制。悲观锁大多数情况下依靠数据库的锁机制实现，如 select ... for update 语句，保证操作最大程度的独占性。但对长事务而言，数据库性能的开销会增大，如果面对成百上千个并发，这样的情况是不被允许的。所以，采用悲观锁进行控制时一定要考虑清楚。

 

死锁的第三种情况

如果在事务中执行了一条不满足条件的update语句，则执行全表扫描，如 update tb_A set user_id=1; ，这条update语句会将行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞。当表中的数据量非常庞大并且索引过少或不合适的时候，数据库经常发生全表扫描，最终应用系统会越来越慢，最终发生阻塞或死锁。

解决方法：

SQL语句中不要使用太复杂的关联多表的查询；使用“explain”对SQL语句进行分析，对于有全表扫描的SQL语句，建立相应的索引进行优化。

 

解除死锁的方法

# 第一种：  
# 1.查询是否有表死锁  
show open tables where in_use > 0;  
# 2.查询进程（如果您有super权限，您可以看到所有线程。否则，您只能看到您自己的线程）  
show processlist;
# 3.杀死线程id  
kill id;

# 第二种：  
# 1.查看下在锁的事务   
select * from information_schema.innodb_trx;  
# 2.杀死进程id
kill 进程id;

# 例子：  
# 查出死锁进程: show processlist;
# 杀掉进程:     kill 420821;

# 其它关于查看死锁的命令：  
# 1：查看当前的事务  
select * from information_schema.innodb_trx;  
# 2：查看当前锁定的事务  
select * from information_schema.innodb_locks;  
# 3：查看当前等锁的事务  
select * from information_schema.innodb_lock_waits;