ITPub博客

首页 > 数据库 > MySQL > mysql deadlock

mysql deadlock

MySQL 作者:pooldb 时间:2015-09-17 20:01:41 0 删除 编辑

 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调整等措施减少锁冲突和死锁,包括:

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

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

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

·给记录集显示加锁时,最好一次性请求足够级别的锁。比如要修改数据的话,最好直接申请排他锁,而不是先申请共享锁,修改时再请求排他锁,这样容易产生死锁;

·不同的程序访问一组表时,应尽量约定以相同的顺序访问各表,对一个表而言,尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会;

·尽量用相等条件访问数据,这样可以避免间隙锁对并发插入的影响;

·不要申请超过实际需要的锁级别;除非必须,查询时不要显示加锁;

·对于一些特定的事务,可以使用表锁来提高处理速度或减少死锁的可能。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30074459/viewspace-1802524/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-12-22

  • 博文量
    404
  • 访问量
    274269