MySQL的意向共享锁、意向排它锁和死锁

一、innodb的表级锁

在绝大多数情况下应该使用行锁,因为事务和行锁往往是选择innodb的理由,但个别情况下也使用表级锁。

事务需要更新大部分或全部数据,表又比较大,如果使用默认的行锁,不仅这个事务执行效率低,而且可能造成其他事务长时间等待和锁冲突事务涉及多个表,比较复杂,很可能引起死锁,造成大量事务回滚

我们希望获取表锁时,执行以下命令:

在使用表锁的时候,涉及的效率问题:
要获取一张表的共享锁s或者排它锁x,首先要确定这张表没有被其它事务获取过x锁,这张表里面的数据没有被其它事务获取过行锁x锁。

假如我们这张表有一千万个数据,怎么判断这一千万个数据哪些行有x锁?
如果想获得表的s锁,就需要判断表里面哪些行有过x锁,如果有一些行有过x锁,那么就不能获取这张表的s锁或者x锁。除了挨个检查,没有更好的办法,这就导致效率低下的问题

由于需要加表锁而去挨个遍历数据,确定是否有某些数据被加了行锁,而导致的效率低下问题。我们这里学习的意向共享锁和意向排他锁就可以解决,当要获取表的x锁是,不需要再检查表中的哪些行锁被(x或者s)占用,只需要快速检查ix和is锁即可

二、意向共享锁和意向排它锁

  • 意向共享锁(is锁):事务计划给记录加行共享锁,事务在给一行记录加共享锁前,必须先取得该表的is锁
  • 意向排他锁(ix锁):事务计划给记录加行排他锁,事务在给一行记录加排他锁前,必须先取得该表的ix锁

  • 在加行锁之前,由innodb存储引擎加上表的is或ix锁
  • 意向锁之间都是兼容的,不会产生冲突,主要是为了辅助其他的在获取表锁的时候加快效率
  • 意向锁存在的意义是为了更高效的获取表锁(表格中的x和s指的是表锁,不是行锁!)
  • 意向锁是表级锁协调表锁和行锁的共存关系。主要目的是显示事务正在锁定某行或者试图锁定某行。

分析事务1获取行x锁和事务2获取表s锁:

首先事务1需要给表的第10行数据加x锁,于是innodb存储引擎自动给整张表加上了ix锁。当事务2再想获取整张表的s锁时,看到这张表已经有别的事务获取了ix锁了,就说明这张表肯定有某些数据被加上了x锁,这就导致事务2不能给整张表加s锁了。此时事务2只能等待,无法成功获取表s锁

三、死锁

1. 数据库中的死锁

myisam表锁时deadlock free的,这是因为myisam 不支持事务,只支持表锁,总是一次获得所需的全部锁,要么全部满足,要么等待,因此不会出现死锁。但在innodb中,除单个sql组成的事务外,锁是逐步获得的,即锁的粒度比较小,这就决定了在innodb中发生死锁是可能的。当然如果处理多张表,还是有可能出现死锁的。

死锁问题一般都是我们自己造成的,和多线程编程的死锁情况相似,大部分都是由于我们多个线程在获取多个锁资源的时候,获取的顺序不同而导致的死锁问题。因此我们应用在对数据库的多个表做更新的时候,不同的代码段,应对这些表按相同的顺序进行更新操作,以防止锁冲突导致死锁问题。

2. 死锁场景以及解决方法

死锁出现的场景如下:

事务1成功获取行锁1
事务2成功获取行锁2

事务1无法获取行锁2,被阻塞的同时没有办法执行commit/rollback,无法释放行锁1
事务2无法获取行锁1,被阻塞的同时没有办法执行commit/rollback,无法释放行锁2

所有的事务都阻塞了,相当于进程内的所有线程都阻塞了,造成了死锁问题。

解决死锁的方法:多个事务/线程获取多个相同资源锁的时候,应该按照同样的顺序,获取资源的锁。

事务被阻塞或者死锁了,mysqld(mysql server守护进程)设置有事务阻塞的超时时间,事务不会阻塞很长时间,超时后事务处理失败,自动释放当前占有的锁。

3. 操作

设置手动提交和可重复读隔离级别并开启事务

查询一下表数据,在可重复读隔离级别使用的是mvcc提供的快照读,并没有加锁

事务1获取id=7的排他锁,事务2获取id=8的排他锁

事务1再获取id=8的排它锁,发生阻塞

事务2再获取id=7的排它锁,发生阻塞

此时由于mysql server检测到发生了死锁,于是解除事务1的阻塞,进行事务1的rollback,释放其占有的行锁,于是事务2成功获取id=7的排它锁

三、锁的优化建议

  • 在能正确完成业务的前提下,为确保效率,尽量使用较低的隔离级别(必须避免脏读)
  • 设计合理的索引并尽量使用索引访问数据,使加锁更准确,减少锁冲突的机会,提高并发能力
  • 选择合理的事务大小,小事务发生锁冲突的概率小(事务越大,包含的sql越多,可能包含更多的表资源和行资源的锁,增大了锁冲突的概率)不同的程序访问一组表时,应尽量约定以相同的顺序访问各表,对一个表而言,尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会
  • 尽量用相等条件访问数据,这样可以避免间隙锁对并发插入的影响(其实等值查询也会加间隙锁)不要申请超过实际需要的锁级别
  • 除非必须,查询时不要显示加锁(在已提交读和可重复读隔离级别,mvcc提供了读取机制,不需要手动加锁)

到此这篇关于mysql的意向共享锁、意向排它锁和死锁的文章就介绍到这了,更多相关mysql锁内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com!

“张承辉博客” MySQL的意向共享锁、意向排它锁和死锁 https://www.zhangchenghui.com/267406

(0)
上一篇 2022年7月20日 下午10:03
下一篇 2022年7月20日 下午10:04

相关阅读

发表回复

登录后才能评论