SQL隔离级别或大型过程中的锁定
我有处理用户操作的大型存储过程。
它们由多个选择语句组成。 这些被过滤,大部分时间只有一行。 选择被复制到临时表中或以其他方式评估。 最后,合并声明会在数据库中进行所需的更改。 所有都封装在一个事务中。
我有来自用户的并发输入,并且选择语句的选定行应被锁定以保持数据完整性。
如何锁定所有选择语句的选定行,以便在当前事务处理过程中不会通过其他事务更新它们?
ROWLOCK和HOLDLOCK的表提示组合的工作方式是只有选定的行被锁定,还是整个表由于HOLDLOCK而被锁定?
SELECT *
FROM dbo.Test
WITH (ROWLOCK HOLDLOCK )
WHERE id = @testId
我可以改用吗?
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
交易开始后? 或者这是否锁定整个表? 我正在使用SQL2008 R2,但如果SQL2012中的工作方式不同,也会感兴趣。
PS:我刚刚阅读了关于表格提示UPDLOCK和SERIALIZE的内容。 UPDLOCK似乎是锁定一行的解决方案,看起来好像UPDLOCK总是锁定而不是ROWLOCK,它只指定锁是基于行的IF锁应用。 我仍然对解决这个问题的最佳方式感到困惑......
更改隔离级别可解决问题(并锁定在行级别上):
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
这是我如何测试它。 我在SQL Management Studio的空白页面中创建了一个语句:
begin tran
select
*
into #message
from dbo.MessageBody
where MessageBody.headerId = 28
WAITFOR DELAY '0:00:05'
update dbo.MessageBody set [message] = 'message1'
where headerId = (select headerId from #message)
select * from dbo.MessageBody where headerId = (select headerId from #message)
drop table #message
commit tran
在执行此语句时(由于延迟最后需要5秒),我在另一个窗口中调用了第二个查询:
begin tran
select
*
into #message
from dbo.MessageBody
where MessageBody.headerId = 28
update dbo.MessageBody set [message] = 'message2'
where headerId = (select headerId from #message)
select * from dbo.MessageBody where headerId = (select headerId from #message)
drop table #message
commit tran
我很惊讶它瞬间执行。 这是由于默认的SQL Server事务级别“Read Commited”http://technet.microsoft.com/en-us/library/ms173763.aspx。 由于第一个脚本的更新是在延迟后完成的,因此在第二个脚本期间还没有未完成的更改,因此将读取和更新第28行。
将隔离级别更改为序列化阻止了这一点,但它也阻止了并发 - 两个scipt都是连续执行的。
这很好,因为这两个脚本都读取并更改了同一行(通过headerId = 28)。 将headerId更改为第二个脚本中的另一个值,这些语句被并行执行。 所以SERIALIZATION的锁定似乎在行级别上。
添加表格提示
WITH ( SERIALIZABLE)
在第一条语句的第一个选择中,也会阻止进一步读取所选行。
链接地址: http://www.djcxy.com/p/32833.html