mysql deadlock

最新推荐文章于 2021-01-27 13:31:34 发布

congzhou7822

最新推荐文章于 2021-01-27 13:31:34 发布

阅读量91

点赞数

4种事务隔离级别：

读数据一致性及允许的并发副作用隔离级别	读数据一致性	脏读	不可重复读	幻读
未提交读（Read uncommitted）	最低级别，只能保证不读取物理上损坏的数据	是	是	是
已提交度（Read committed）	语句级	否	是	是
可重复读（Repeatable read）	事务级	否	否	是？
可序列化（Serializable）	最高级别，事务级	否	否	否

Mysql 的repeatable read 是不存在幻读的，这是由它的复制和恢复机制决定的。

Serializable 普通的select 语句也会加锁，串行的，并发性能低

请求锁模式是否兼容当前锁模式	X	IX	S	IS
X	冲突	冲突	冲突	冲突
IX	冲突	兼容	冲突	兼容
S	冲突	冲突	兼容	兼容
IS	冲突	兼容	兼容	兼容

SELECT ... IN SHARE MODE 显示加共享锁

SELECT... FOR UPDATE 显示加排他锁

Innodb 行锁实现方式：

（1）在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。（这个也是因为所有的主键被锁定）

（2）由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点

（3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁

我觉得这个理解是有误的，因为mysql的锁是加在索引上的，怎么又会加在数据上？正确的理解是，对某条记录加锁的时候，会对这条记录的主键索引或者唯一索引加锁，所以会看到相同的记录相互锁定，虽然用的索引不同。没有指定主键的时候，系统会分配默认的主键（GEN_CLUST_INDEX）。

（4）即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

Be careful:

例子中tab_with_index表的name字段有索引，但是name字段是varchar类型的，如果where条件中不是和varchar类型进行比较，则会对name进行类型转换，而执行的全表扫描

间隙锁（Next-Key锁）即范围锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！对不存在的键值的范围锁，譬如要新加数据的时候insert。

(5) Update 语句中的set字段也会成为锁对象, a and b all have index

Session a

update test_innodb_lock set b='4' where a =1;

session b:

mysql> update test_innodb_lock set a = 7 where b='4';

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set a = 7 where b='5';

Query OK, 1 row affected (0.05 sec)

Rows matched: 1 Changed: 1 Warnings: 0

两次insert 语句除非是主键相同，就算普通索引键重复，也不会相互锁。

但是update和 delete 有范围锁，很容易和insert 冲突。

譬如insert into test (a,b) (1,1) (4,4) (7,7) (10,10)

Delete from test where a >=7. 会锁4-7，大于7的， 4之前的不锁

Delete from test where a>=7 and a<=10会锁4-7，大于7的。因为10为最后一个数，所以10以后的都会锁

Delete from test where a<=10, 全部都锁，不是受只有10之前的会锁。因为10时最后一个

如果多了一个值（100,100）. 则100以后的就不会锁， 100之前的还是会锁

这就是间隙锁的实际，注意。

恢复和复制的需要，对InnoDB锁机制的影响

一是MySQL的恢复是SQL语句级的，也就是重新执行BINLOG中的SQL语句。这与Oracle数据库不同，Oracle是基于数据库文件块的。

·二是MySQL的Binlog是按照事务提交的先后顺序记录的，恢复也是按这个顺序进行的。这点也与Oralce不同，Oracle是按照系统更新号（System Change Number，SCN）来恢复数据的，每个事务开始时，Oracle都会分配一个全局唯一的SCN，SCN的顺序与事务开始的时间顺序是一致的。

从上面两点可知，MySQL的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读，这已经超过了ISO/ANSI SQL92“可重复读”隔离级别的要求，实际上是要求事务要串行化。这也是许多情况下，InnoDB要用到间隙锁的原因，比如在用范围条件更新记录时，无论在Read Commited或是Repeatable Read隔离级别下，InnoDB都要使用间隙锁，但这并不是隔离级别要求的

INSERT...SELECT...和CREATE TABLE...SELECT...语句会对元表加共享锁。

因此，INSERT...SELECT...和CREATE TABLE...SELECT...语句，可能会阻止对源表的并发更新，造成对源表锁的等待。如果查询比较复杂的话，会造成严重的性能问题，我们在应用中应尽量避免使用。实际上，MySQL将这种SQL叫作不确定（non-deterministic）的SQL，不推荐使用

InnoDB在不同隔离级别下的一致性读及锁的差异

死锁：

发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回退（事物比较小的， insert/update/delete 数据量相对较小的），另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生

通常来说，死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小，以及访问数据库的SQL语句，绝大部分死锁都可以避免。下面就通过实例来介绍几种避免死锁的常用方法：也是减少锁发生几率

1．在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。

2．在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能

3．在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。（这种情况实际情况中基本不可能出现）。

4．降低数据库隔离级别

5．减少事务大小

6．在程序设计中总是捕获并处理死锁异常是一个很好的编程习惯。

7．使用表锁，缺点是降低了并发

在了解InnoDB锁特性后，用户可以通过设计和SQL调整等措施减少锁冲突和死锁，包括：

·尽量使用较低的隔离级别；

·精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会；

·选择合理的事务大小，小事务发生锁冲突的几率也更小；