SQL 存储过程中的 SQLServer 锁表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4143558/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLServer lock table during stored procedure
提问by asc99c
I've got a table where I need to auto-assign an ID 99% of the time (the other 1% rules out using an identity column it seems). So I've got a stored procedure to get next ID along the following lines:
我有一个表，我需要在 99% 的情况下自动分配一个 ID（其他 1% 似乎排除了使用标识列）。所以我有一个存储过程可以按照以下几行获取下一个 ID：
select @nextid = lastid+1 from last_auto_id
check next available id in the table...
update last_auto_id set lastid = @nextid
Where the check has to check if users have manually used the IDs and find the next unused ID.
检查必须检查用户是否手动使用了 ID 并找到下一个未使用的 ID。
It works fine when I call it serially, returning 1, 2, 3 ... What I need to do is provide some locking where multiple processes call this at the same time. Ideally, I just need it to exclusively lock the last_auto_id table around this code so that a second call must wait for the first to update the table before it can run it's select.
当我连续调用它时它工作正常，返回 1, 2, 3 ...我需要做的是提供一些锁定，其中多个进程同时调用它。理想情况下，我只需要它专门锁定此代码周围的 last_auto_id 表，以便第二次调用必须等待第一个调用更新表，然后才能运行它的选择。
In Postgres, I can do something like 'LOCK TABLE last_auto_id;' to explicitly lock the table. Any ideas how to accomplish it in SQL Server?
在 Postgres 中，我可以执行诸如“LOCK TABLE last_auto_id;”之类的操作 显式锁定表。任何想法如何在 SQL Server 中完成它？
Thanks in advance!
提前致谢！
采纳答案by asc99c
You guys have between you answered my question. I'm putting in my own reply to collate the working solution I've got into one post. The key seems to have been the transaction approach, with locking hints on the last_auto_id table. Setting the transaction isolation to serializable seemed to create deadlock problems.
你们之间已经回答了我的问题。我将自己的回复整理成一篇文章。关键似乎是事务方法，在 last_auto_id 表上有锁定提示。将事务隔离设置为可序列化似乎会产生死锁问题。
Here's what I've got (edited to show the full code so hopefully I can get some further answers...):
这是我得到的（编辑以显示完整代码，所以希望我能得到一些进一步的答案......）：
DECLARE @Pointer AS INT
BEGIN TRANSACTION
-- Check what the next ID to use should be
SELECT @NextId = LastId + 1 FROM Last_Auto_Id WITH (TABLOCKX) WHERE Name = 'CustomerNo'
-- Now check if this next ID already exists in the database
IF EXISTS (SELECT CustomerNo FROM Customer
WHERE ISNUMERIC(CustomerNo) = 1 AND CustomerNo = @NextId)
BEGIN
-- The next ID already exists - we need to find the next lowest free ID
CREATE TABLE #idtbl ( IdNo int )
-- Into temp table, grab all numeric IDs higher than the current next ID
INSERT INTO #idtbl
SELECT CAST(CustomerNo AS INT) FROM Customer
WHERE ISNUMERIC(CustomerNo) = 1 AND CustomerNo >= @NextId
ORDER BY CAST(CustomerNo AS INT)
-- Join the table with itself, based on the right hand side of the join
-- being equal to the ID on the left hand side + 1. We're looking for
-- the lowest record where the right hand side is NULL (i.e. the ID is
-- unused)
SELECT @Pointer = MIN( t1.IdNo ) + 1 FROM #idtbl t1
LEFT OUTER JOIN #idtbl t2 ON t1.IdNo + 1 = t2.IdNo
WHERE t2.IdNo IS NULL
END
UPDATE Last_Auto_Id SET LastId = @NextId WHERE Name = 'CustomerNo'
COMMIT TRANSACTION
SELECT @NextId
This takes out an exclusive table lock at the start of the transaction, which then successfully queues up any further requests until after this request has updated the table and committed it's transaction.
这会在事务开始时取出一个排他表锁，然后成功地将任何进一步的请求排队，直到该请求更新了表并提交了它的事务。
I've written a bit of C code to hammer it with concurrent requests from half a dozen sessions and it's working perfectly.
我已经编写了一些 C 代码来处理来自六个会话的并发请求，并且它运行良好。
However, I do have one worry which is the term locking 'hints' - does anyone know if SQLServer treats this as a definite instruction or just a hint (i.e. maybe it won't always obey it??)
但是，我确实有一个担心，那就是锁定“提示”这个术语 - 有谁知道 SQLServer 将其视为明确的指令还是只是一个提示（即它可能不会总是遵守它？？）
回答by Lieven Keersmaekers
Following update increments your lastid by one and assigns this value to your local variable in a single transaction.
以下更新将您的 lastid 增加 1，并在单个事务中将此值分配给您的局部变量。
Edit
编辑
thanks to Dave and Mitch for pointing out isolation level problems with the original solution.
感谢 Dave 和 Mitch 指出原始解决方案的隔离级别问题。
UPDATE last_auto_id WITH (READCOMMITTEDLOCK)
SET @nextid = lastid = lastid + 1
回答by Yaqub Ahmad
How is this solution? No TABLE LOCK is required and works perfectly!!!
这个解决方案如何？不需要 TABLE LOCK 并且完美运行！！！
DECLARE @NextId INT
UPDATE Last_Auto_Id
SET @NextId = LastId = LastId + 1
WHERE Name = 'CustomerNo'
SELECT @NextId
回答by Donnie
I prefer doing this using an identityfield in a second table. If you make lastididentitythen all you have to do is insert a row in that table and select @scope_identityto get your new value and you still have the concurrency safety of identityeven though the id field in your main table is not identity.
我更喜欢使用identity第二个表中的字段来执行此操作。如果您创建，lastididentity那么您所要做的就是在该表中插入一行并选择@scope_identity获取新值，identity即使主表中的 id 字段不是，您仍然具有并发安全性identity。
回答by Carls Jr.
You might wanna consider deadlocks. This usually happens when multiple users use the stored procedure simultaneously. In order to avoid deadlock and make sure every query from the user will succeed you will need to do some handling during update failures and to do this you will need a try catch. This works on Sql Server 2005,2008 only.
你可能想考虑死锁。这通常发生在多个用户同时使用存储过程时。为了避免死锁并确保用户的每个查询都会成功，您需要在更新失败期间进行一些处理，为此您需要尝试捕获。这仅适用于 Sql Server 2005,2008。
DECLARE @Tries tinyint
SET @Tries = 1
WHILE @Tries <= 3
BEGIN
BEGIN TRANSACTION
BEGIN TRY
-- this line updates the last_auto_id
update last_auto_id set lastid = lastid+1
COMMIT
BREAK
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() as ErrorMessage
ROLLBACK
SET @Tries = @Tries + 1
CONTINUE
END CATCH
END
