sql-server – T-SQL是更新限制Atomic的子查询吗?
我在MS Sql Server 2008 R2中有一个简单的队列实现.这是队列的本质: CREATE TABLE ToBeProcessed ( Id BIGINT IDENTITY(1,1) PRIMARY KEY NOT NULL,[Priority] INT DEFAULT(100) NOT NULL,IsBeingProcessed BIT default (0) NOT NULL,SomeData nvarchar(MAX) NOT null ) 我想原子地选择按优先级排序的前n行和IsBeingProcessed为false的id,并更新这些行以表示它们正在被处理.我以为我会使用Update,Top,Output和Order By的组合,但不幸的是你不能在Update语句中使用top和order by. 所以我创建了一个in子句来限制更新,并且子查询按顺序执行(见下文).我的问题是,这整个语句是原子的,还是我需要将它包装在一个事务中? DECLARE @numberToProcess INT = 2 CREATE TABLE #IdsToProcess ( Id BIGINT NOT null ) UPDATE ToBeProcessed SET ToBeProcessed.IsBeingProcessed = 1 OUTPUT INSERTED.Id INTO #IdsToProcess WHERE ToBeProcessed.Id IN ( SELECT TOP(@numberToProcess) ToBeProcessed.Id FROM ToBeProcessed WHERE ToBeProcessed.IsBeingProcessed = 0 ORDER BY ToBeProcessed.Id,ToBeProcessed.Priority DESC) SELECT * FROM #IdsToProcess DROP TABLE #IdsToProcess 这是一些插入一些虚拟行的SQL: INSERT INTO ToBeProcessed (SomeData) VALUES (N''); INSERT INTO ToBeProcessed (SomeData) VALUES (N''); INSERT INTO ToBeProcessed (SomeData) VALUES (N''); INSERT INTO ToBeProcessed (SomeData) VALUES (N''); INSERT INTO ToBeProcessed (SomeData) VALUES (N''); 解决方法如果我理解问题的动机,你想避免两个并发事务都可以执行子查询以获得前N行进行处理然后继续更新相同行的可能性?在那种情况下,我会使用这种方法. ;WITH cte As ( SELECT TOP(@numberToProcess) * FROM ToBeProcessed WITH(UPDLOCK,ROWLOCK,READPAST) WHERE ToBeProcessed.IsBeingProcessed = 0 ORDER BY ToBeProcessed.Id,ToBeProcessed.Priority DESC ) UPDATE cte SET IsBeingProcessed = 1 OUTPUT INSERTED.Id INTO #IdsToProcess 我之前有点不确定SQL Server是否会在使用子查询处理您的版本时使用U锁,从而阻止两个并发事务读取相同的TOP N行.这似乎不是这种情况. 测试表 CREATE TABLE JobsToProcess ( priority INT IDENTITY(1,1),isprocessed BIT,number INT ) INSERT INTO JobsToProcess SELECT TOP (1000000) 0,0 FROM master..spt_values v1,master..spt_values v2 测试脚本(在2个并发SSMS会话中运行) BEGIN TRY DECLARE @FinishedMessage VARBINARY (128) = CAST('TestFinished' AS VARBINARY (128)) DECLARE @SynchMessage VARBINARY (128) = CAST('TestSynchronising' AS VARBINARY (128)) SET CONTEXT_INFO @SynchMessage DECLARE @OtherSpid int WHILE(@OtherSpid IS NULL) SELECT @OtherSpid=spid FROM sys.sysprocesses WHERE context_info=@SynchMessage and spid<>@@SPID SELECT @OtherSpid DECLARE @increment INT = @@spid DECLARE @number INT = @increment WHILE (@number = @increment AND NOT EXISTS(SELECT * FROM sys.sysprocesses WHERE context_info=@FinishedMessage)) UPDATE JobsToProcess SET @number=number +=@increment,isprocessed=1 WHERE priority = (SELECT TOP 1 priority FROM JobsToProcess WHERE isprocessed=0 ORDER BY priority DESC) SELECT * FROM JobsToProcess WHERE number not in (0,@OtherSpid,@@spid) SET CONTEXT_INFO @FinishedMessage END TRY BEGIN CATCH SET CONTEXT_INFO @FinishedMessage SELECT ERROR_MESSAGE(),ERROR_NUMBER() END CATCH 几乎立即执行停止,因为两个并发事务都更新同一行,因此在识别TOP 1优先级时采用的S锁必须在获取U锁之前释放,然后2个事务继续按顺序获得行U和X锁. 如果添加了CI ALTER TABLE JobsToProcess ADD PRIMARY KEY CLUSTERED(优先级),那么死锁几乎立即发生,因为在这种情况下,行S锁没有被释放,一个事务获取行上的U锁并等待将其转换为一个X锁,另一个事务仍在等待将其S锁转换为U锁. 如果上面的查询更改为使用MIN而不是TOP WHERE priority = (SELECT MIN(priority) FROM JobsToProcess WHERE isprocessed=0 ) 然后SQL Server设法完全消除计划中的子查询并一直采用U锁. (编辑:开发网_郴州站长网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |
- sql-server – 如何将Postgres数据转储导入MS SQL?
- 在SQL Server 2012中开发Analysis Services Multidimension
- MVC使用Controller代替Filter完成登录验证(Session校验)学
- 数据库设计 – 如何将IS-A关系映射到数据库?
- 虚拟化 – 虚拟化和SQL服务器
- 台湾捷波引爆板卡类股高股息第一炮
- sql-server – 使用LEFT JOIN或NOT EXISTS之间的最佳实践
- 花费 0.052asp站长网 www.aspzz.cn 秒(0.150 MB/秒)
- 数据库设计 – 在逻辑/布尔数据库字段中使用[0,1]与[“Y”,
- sql-server – 存档旧数据