ITPub博客

首页 > 数据库 > MySQL > MySQL 锁(Lock)问题处理

MySQL 锁(Lock)问题处理

原创 MySQL 作者:龙山游龙 时间:2021-08-25 17:35:30 0 删除 编辑

第一部分 锁问题

1.1 丢失更新

丢失更新是一个经典的数据库问题,实际上,所有多用户计算机系统环境下都有可能产生这个问题,简单来说,出现下面的情况时,就会发生丢失更新:

1. 事务T1查询一行数据,放入本地内存,并显示给一个终端用户user1;

2. 事务T2也查询该行数据,并将取得的数据显示给终端user2;

3. User1修改这行记录,更新数据并提交;

4. User2修改这行记录,更新数据并提交。

  显然,这个时候user1的更新操作丢失了,这在生产环境是很糟糕的,设想是银行的账户金额操作,影响甚大。

要避免这种丢失更新,我们就要将并发的操作变成串行操作,即在上述第1种情况下,对用户读取的记录加上一个排他锁,同样,发生第2种情况的操作时,用户也需要加一个排他锁,这样,第2步就必须等到1、3完成,最后完成第4步的操作。如下图所示:

 1.2 脏读

脏页:脏页指的是在缓冲池中已经被修改的页,但是还没有刷新到磁盘,即数据库实例内存中的页和磁盘的页中的数据是不一致的,当然,在刷新到磁盘之前,日志都已经被写入了重做日志文件中。

脏数据:脏数据是指在缓冲池被修改的数据,并且还没有被提交。

所以,以上的脏读指的是读取未提交的脏数据,至于脏页的读取,是非常正确的,脏页是由于数据库实例内存和磁盘的异步写入造成的,但是并不影响数据的一致性,同时还会提高性能。而读取了脏数据,就违反了数据库的隔离性。

脏读指的是在不同的事务下,可以读到其他事务未提交的数据,简单来说,就是可以读到脏数据,如下如所示:

 以上例子,我们的隔离级别设置为READ UNCOMMITTED,因此在会话A中事务没有提交的前提下,会话B中两次select操作取到了不同的结果,并且这来给你个记录是在会话A中未提交,即产生了脏读,违反了事务的隔离性。

脏读现象在生产环境中不常发生,而且脏读的条件是隔离界别设置未READ

UNCOMMITTED,而目前大部分的数据库都至少设置为READ COMMITTED,InnoDB存储引擎默认为READ REPEATABLE,SQL server和Oracle使用的都是READ COMMITTED。

1.3 不可重复读

不可重读读是指在一个事务内多次读取到同一数据。在这个事务还没有结束时,另外一个事务也访问该数据。那么,在第一个事务两次访问数据之间,由于第二个事务的修改,第一个事务两次读取到的结果不一样,因此称为不可重复读。

不可重复读和脏读的区别是:脏读是读到未提交的数据,而不可重复读是读已提交的数据,但是违反了事务一致性的原则,如下图所示:

 

 以上例子,我们的隔离级别设置为READ COMMITTED,一般情况下,很多厂商也会选择READ COMMITTED,所以不可重复读是允许的。因为事务已经提交。

其实,不可重复读也是可以避免的,在MySQL官方文档中,利用Next-key Lock的算法,对于索引的扫描,不仅仅是锁住扫描到的索引,而且还会锁住这些索引覆盖的范围(gap)。因此在这个范围内的插入都是不允许的,也就避免了不可重复读的问题。而Innodb默认引擎READ REPEATABLE就是采用了Next-key Lock算法,避免了不可重复读现象。

第二部分 阻塞 

因为不同锁之间的兼容性关系,所以在有些时刻,一个事务中的锁需要等待另一个事务中的锁释放它所占的资源。

在innodb存储引擎中,参数innodb_lock_wait_timeout用来控制等待时间,默认是50秒,innodb_rollback_on_timeout用来设定时头在等待超时时对进行中的事务进行回滚操作,默认off,代表不回滚。innodb_lock_wait_timeout是动态的,可以在MySQL数据库运行时进行调整,而innodb_rollback_on_timeout是静态的,不可以在MySQL运行时调整。

mysql> show global variables like '%innodb_lock_wait_timeout%';
mysql> show global variables like 'innodb_rollback_on_timeout';

 

第三部分 死锁

如果程序是串行的,那么不可能发生死锁。死锁只发生在并发的情况下,数据库就像一个并发进行着的程序,因此可能会发生死锁。在前面的文章里,我们已经知道,INNODB存储引擎有一个后台的锁监控线程,该线程负责查看可能的死锁问题,并自动告知用户。如下图:

 

通过上图我们捕获到了1213这个错误,也就是发生了死锁。为什么发生死锁,相比大家都很清楚,是因为会话A和会话B的资源在互相等待。但是在1213异常抛出后,会话A为什么会立刻获取记录为2的结果呢?难道是因为会话B回滚了?可是前一章节刚刚讲了锁超时会话不回滚的啊……

其实,大家猜的没错,会话B 的确回滚了,锁超时会话不回滚也是对的,但是,死锁例外。

也就是说,当抛出1213异常后,我们不需要人为的对事务进行回滚。在Oracle中,产生死锁的原因大多是因为没有对外键建立索引,而在innodb存储引擎会自动为其添加,如果人为删除外键上的索引就会抛出异常。

 


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

请登录后发表评论 登录
全部评论
拥有超过9年以上的MySQL/Oracle数据库领域从业经验,TB级高并发数据库的管理经验。对于MySQL/Oracle内部原理、体系结构,数据页/块结构原理有着深刻认识;对于MySQL/ORACLE新特性、分布式高可用架构和性能调优有着丰富的的实战经验。擅长故障诊断以及数据库异常打开及数据灾难挽救。

注册时间:2015-06-28

  • 博文量
    78
  • 访问量
    109786