mysql中更新锁与排它锁的示例分析

发布时间:2021-09-16 18:08:23 作者:小新
来源:亿速云 阅读:209

这篇文章将为大家详细讲解有关mysql中更新锁与排它锁的示例分析,小编觉得挺实用的,因此分享给大家做个参考,希望大家阅读完这篇文章后可以有所收获。

一直没有认真了解UPDATE操作的锁,最近在MSDN论坛上看到一个问题,询问堆表更新的死锁问题,问题很简单,有类似这样的表及数据:

CREATE TABLE dbo.tb(
     c1 int,
     c2 char(10),
     c3 varchar(10)
);
GO
DECLARE @id int;
SET @id = 0;
WHILE @id <5
BEGIN;
     SET @id = @id + 1;
     INSERT dbo.tb VALUES( @id, 'b' + RIGHT(10000 + @id, 4), 'c' + RIGHT(100000 + @id, 4) );
END;

在查询一中执行更新操作:

BEGIN TRAN
UPDATE dbo.tb SET c2 = 'xx' WHERE c1 = 2;
WAITFOR DELAY '00:00:30';
UPDATE dbo.tb SET c2 = 'xx' WHERE c1 = 5;
ROLLBACK;

在查询一执行开始后,马上在查询二中执行下面的操作

BEGIN TRAN
UPDATE dbo.tb SET c2 = 'xx' WHERE c1 = 1;
ROLLBACK;

为什么会出现死锁,如果条件改为 c1 = 4 则不会死锁。

开始的时候想得比较简单,死锁的表现是形成循环等待(对于两个查询而言,可以简单地认为就是在相互等待对方锁定资源的释放)。

对于这个例子而言,第一个查询更新两次,会先更新并锁定一条记录,然后等待第二个更新;但第二个查询只会更新一条记录,它要么与第一个查询冲突,无法获得锁,需要等待查询一完成,这个时候它并没有锁定什么;要么能够获得锁,完成更新。似乎不应该会出现死锁,死锁会不会是其他原因导致。

在自己的电脑上简单测试了一下,似乎也确实没有死锁。

但后面通过Profile跟踪更新操作的下锁情况才发现,自己的分析大错特错了。主要原因在于没有正确理解更新操作是如何用锁的。

在联机帮助上“锁模式”中有关于更新的U(更新锁)和X(排它锁)的说明

http://msdn.microsoft.com/zh-cn/library/ms175519(v=sql.105).aspx

不过说得确实挺模糊的,里面还提到了S锁,我一直以为是查询数据过程中用的S锁(也 SELECT 一样),找到满足条件的记录后用U锁,再转换为X锁做更新。

   Profile(事件探查器)跟踪的结果让我知道了这是一个错误的理解,在Profile中新建一个跟踪,选择Locks中的Lock:Acquired(加锁),Lock:Acquired(释放锁)解两个事件,在筛选中设置只跟踪测试用的查询窗口对应的spid(可以执行 PRINT @@SPID获得),然后执行一个更新语句,比如 UPDATE dbo.tb SET c2 = 'xx' WHERE c1 = 3

在Profile中可以看到,对于每条记录都有加 U 锁的操作,对于不满足条件的记录,会马上释放U锁;对于满足条件的记录,最终转换为X锁。如下图所示。

mysql中更新锁与排它锁的示例分析

注意一下,在这个跟踪结果里面,并没有出现S锁。

另外学做了一些测试:

  1. 通过加大记录量做更新测试,会发现数据扫描涉及的记录都有U锁,并不限于更新记录所在的页。这从另一个角度说明了大表中Scan 可怕。

  2. 当使用索引Scan的时候,也会通过跟踪发现所Scan的索引资源有U锁,如果更新不涉及索引变化,那以只会对应的记录有U转X锁,索引的U锁会释放;如果影响索引,那么索引的U锁会转X锁。

  3. 删除操作与更新操作类似

  4. 使用 UPDATE aSET c2 = 'xx' FROM dbo.tb AS a WITH(NOLOCK) WHERE c1 = 3  的加锁情况是一样的, 并不会因为NOLOCK的提示而不加 U 或者 X 锁

最后回头研究一下示例中的死锁问题:

更新时,记录读取的顺序,可以通过Profile跟踪的Lock:Acquired (加锁)事件来看,涉及大量数据时,如果服务器支持,还会有并发读取。这也是分析死锁时要考虑的因素

关于“mysql中更新锁与排它锁的示例分析”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,使各位可以学到更多知识,如果觉得文章不错,请把它分享出去让更多的人看到。

推荐阅读:
  1. Mysql-InnoDB锁的示例分析
  2. SQL Server中事务与锁的示例分析

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

mysql

上一篇:CentOS系统单用户模式介绍

下一篇:AJAX中文乱码怎么解决

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》