采取独占锁的正确方法 [英] Correct way to take a exclusive lock
问题描述
我正在编写一个程序,该程序将协调实时数据库中的最终交易.我正在做的工作无法作为设置操作完成,因此我使用了两个嵌套游标.
I am writing a procedure that will be reconciling finical transactions on a live database. The work I am doing can not be done as a set operation so I am using two nested cursors.
我在对每个客户端进行协调时需要在事务表上设置排他锁,但是我想释放该锁,并让其他人在我处理的每个客户端之间运行查询.
I need to take a exclusive lock on the transaction table while I am reconciling per client, but I would like to release the lock and let other people run their queries in between each client I process.
我想在行级别而不是表级别上执行排他锁,但是到目前为止,我已经读过什么说如果其他事务以READCOMMITED
隔离级别运行(对我来说),我将无法执行with (XLOCK, ROWLOCK, HOLDLOCK)
.
I would love to do a exclusive lock on a row level instead of a table level, but what I have read so far says I can not do with (XLOCK, ROWLOCK, HOLDLOCK)
if the other transactions are running at READCOMMITED
isolation level (which it is for me).
我是否正确地获取了表级互斥锁,并且Server 2008 R2中有什么方法可以使行级互斥锁按我想要的方式工作而无需修改数据库上运行的其他查询?
Am I taking a table level exclusive lock correctly, and is there any way in Server 2008 R2 to make row level exclusive locks work the way I want to without modifying the other queries running on the database?
declare client_cursor cursor local forward_only for
select distinct CLIENT_GUID from trnHistory
open client_cursor
declare @ClientGuid uniqueidentifier
declare @TransGuid uniqueidentifier
fetch next from client_cursor into @ClientGuid
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
begin tran
declare @temp int
--The following row will not work if the other connections are running READCOMMITED isolation level
--select @temp = 1
--from trnHistory with (XLOCK, ROWLOCK, HOLDLOCK)
--left join trnCB with (XLOCK, ROWLOCK, HOLDLOCK) on trnHistory.TRANS_GUID = trnCB.TRANS_GUID
--left join trnClients with (XLOCK, ROWLOCK, HOLDLOCK) on trnHistory.TRANS_GUID = trnClients.TRANS_GUID
--(Snip) --Other tables that will be "touched" during the reconcile
--where trnHistory.CLIENT_GUID = @ClientGuid
--Works allways but locks whole table.
select top 1 @temp = 1 from trnHistory with (XLOCK, TABLOCK)
select top 1 @temp = 1 from trnCB with (XLOCK, TABLOCK)
select top 1 @temp = 1 from trnClients with (XLOCK, TABLOCK)
--(Snip) --Other tables that will be "touched" during the reconcile
declare trans_cursor cursor local forward_only for
select TRANS_GUID from trnHistory where CLIENT_GUID = @ClientGuid order by TRANS_NUMBER
open trans_cursor
fetch next from trans_cursor into @TransGuid
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
--Do Work here
END
fetch next from trans_cursor into @TransGuid
END
close trans_cursor
deallocate trans_cursor
--commit the transaction and release the lock, this allows other
-- connections to get a few queries in while it is safe to read.
commit tran
END
fetch next from client_cursor into @ClientGuid
END
close client_cursor
deallocate client_cursor
推荐答案
如果只担心其他读者,那么就不需要排他锁,即模式
If you are only worried about other readers, then you shouldn't need exclusive locks, the pattern
Begin Transaction
Make Data Inconsistent
Make Data Consistent
Commit Transaction
应该没事.唯一会看到不一致数据的会话是那些使用nolock
或Read Uncommitted
的会话,或者那些期望在不使用Repeatable Rows
或Serializable
的情况下进行多次一致读取的会话.
Should be fine. The only sessions who will see inconsistent data are those that use nolock
or Read Uncommitted
, or those that expect to make multiple consistent reads without using Repeatable Rows
or Serializable
.
在回答这个问题时,我认为获得排他锁的正确方法是安排事情,以便引擎为您完成.
In answer to the question, the correct way to take an exclusive lock, in my opinion, is to arrange things so the engine does it for you.
这篇关于采取独占锁的正确方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!