SQL Server:如何获取排他锁以防止出现竞争状况?

I have the following T-SQL code:SET TRANSACTION ISOLATION LEVEL SERIALIZABLEBEGIN TRANSACTION T1_Test /*This is a dummy table used for "locking" and it doesn't contain any meaningful data.*/ UPDATE lockTable SET ID = 1 WHERE ID = 1 DECLARE @Count TINYINT SELECT @Count = COUNT(*) FROM debugSP WAITFOR DELAY '00:00:5'; INSERT INTO debugSP (DateCreated, ClientId, Result) SELECT GETDATE(), @@SPID, @CountCOMMIT TRANSACTION T1_TestI am using "locking" hack marked with comment to acquire the exclusive lock.NOTE: using TABLOCKX or UPDLOCK hints will not work because I have broken ATOMIC-ity by splitting statements and adding WAITFOR command in the middle for testing purposes.I don't want something like that:INSERT INTO debugSP (DateCreated, ClientId, Result)SELECT GETDATE(), @@SPID, COUNT(*)FROM debugSPThis is the correct result after running two simultaneous sessions (with lock table)Id DateCreated ClientId Result-- ----------------------- -------- ------ 1 2011-03-17 15:52:12.287 66 0 2 2011-03-17 15:52:24.534 68 1and that is the incorrect result of running the code with lock commented outId DateCreated ClientId Result-- ----------------------- -------- ------ 1 2011-03-17 15:52:43.128 66 0 2 2011-03-17 15:52:46.341 68 0Is there a better way of acquiring transaction-wide exclusive lock without such hacks? 解决方案 I'm not exactly sure what you are trying to do from the posted code. I presume you are just trying to serialize access to that piece of code? If so sp_getapplock should do what you need instead of creating a new dummy table that you just use to take locks on.Details here 这篇关于SQL Server:如何获取排他锁以防止出现竞争状况?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！