MySQL 锁(Lock)问题处理

发布时间:2021-08-26 11:17 来源:ITPUB博客 阅读:0 作者: 栏目: Mysql

第一部分 锁问题

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存储引擎会自动为其添加,如果人为删除外键上的索引就会抛出异常。

 


免责声明:本站发布的内容(图片、视频和文字)以原创、来自本网站内容采集于网络互联网转载等其它媒体和分享为主,内容观点不代表本网站立场,如侵犯了原作者的版权,请告知一经查实,将立刻删除涉嫌侵权内容,联系QQ:712375056。