存储过程中的SQLServer锁定表 [英] SQLServer lock table during stored procedure

问题描述

我有一张表，我需要在该表中99％的时间自动分配ID(其他1％似乎使用身份列来排除).因此，我有一个存储过程来获取以下行中的下一个ID:

必须检查用户是否手动使用了ID并找到下一个未使用的ID.

当我依次调用它并返回1、2、3时，它可以正常工作.我需要做的是在多个进程同时调用此方法的情况下提供一些锁定.理想情况下，我只需要它专用于围绕此代码锁定last_auto_id表，以便第二个调用必须等待第一个调用更新表才能运行它的选择.

在Postgres中，我可以执行类似"LOCK TABLE last_auto_id;"的操作显式锁定表.有什么想法可以在SQL Server中完成吗?

提前谢谢！

解决方案

你们之间，你们回答了我的问题.我要发表自己的答复，以整理我发表在一篇文章中的工作解决方案.关键似乎是事务处理方法，在last_auto_id表上具有锁定提示.将事务隔离设置为可序列化似乎会导致死锁问题.

这就是我所拥有的(编辑以显示完整的代码，因此希望我可以得到更多的答案...):

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

这将在事务开始时取出排他表锁，然后该锁将所有其他请求成功排队，直到此请求更新了表并提交了它的事务.

我已经编写了一些C代码，以应对来自六个会话的并发请求，并且运行良好.

但是，我确实有个担心，那就是锁定提示"一词-有人知道SQLServer是将其视为确定的指令还是仅作为提示(即也许不会总是服从它吗?)

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:

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.

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.

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?

Thanks in advance!

解决方案

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.

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.

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锁定表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！