为读写操作锁定SQL server表

Question

我有一个表，其中主键是一个增量int'ID'，我必须手动设置它。我知道自动递增int(IDENTITY(应该是最好的选择，但我不能改变现有的表设计。

所以我需要将读写操作原子化，在某种程度上：

• 锁定表
• 读取现有ID的最大值
• 添加主键为ID+1的新记录
• 发布表格

在多用户环境中锁定表的正确方法是什么？我想这是事务和TABLOCX使用的混合。我需要确保：

• 没有死锁
• 如果某个操作失败，则表不应保持锁定状态(例如，程序在尝试写入时失败并退出，并且不调用COMMIT/ROLLBACK(。我甚至不知道这是否可能

注意：数据库也被其他应用程序使用，我想这些应用程序自己也会处理这个问题。

编辑：这可以被认为是一个足够的原子解决方案吗？：

INSERT INTO MYTABLE(ID，OtherFields…(VALUES((从MYTABLE中选择Max(ID(+1(，'VALUES'…(

Answer 1

尝试使用表锁滚动您自己的自动递增机制几乎注定会失败-然而，由于您写了无法更改现有表，我建议使用序列来获取下一个数字，而不是锁定表。

CREATE SEQUENCE dbo.MySequence -- Don't use this name, please!
AS int -- note: default is bigInt
START WITH 1  
INCREMENT BY 1 
NO CYCLE; 

这使all一些¹具有标识列的好处，而无需在表中添加标识列。

您也可以使用该序列为列生成默认值(当然，假设添加默认约束不算作"更改现有表结构"(。参见官方文件中的示例D

ALTER TABLE dbo.YourTableName
ADD CONSTRAINT YourTableName_id_default  
DEFAULT NEXT VALUE FOR MySequence 
FOR Id;  

¹好处是您不需要添加锁或自己计算下一个数字
但是，您应该知道，与标识列不同，这不会保护您不受id列更新的影响，也不会保护您插入显式向该列插入值的语句(不使用next value for(。

第一个问题可以很容易地通过表上的替代更新触发器来解决，该触发器只更新不是id列的列，但我不知道如何解决另一个问题。

Answer 2

因此，如果其他进程正在正确处理锁定，您可以通过执行类似于以下的操作来执行您提到的操作(锁定、获取最后一个ID、插入和释放(：

DECLARE @MaxID INT
BEGIN TRY
BEGIN TRANSACTION
SELECT
@MaxID = MAX(I.ID)
FROM 
MyTable AS I WITH (TABLOCKX, HOLDLOCK) -- TABLOCKX: no operations can be done, HOLDLOCK: until the end of the transaction
INSERT INTO MyTable (
ID,
OtherColumn)
SELECT
ID = ISNULL(@MaxID + 1, 1)
OtherColumn = 'Other values'
COMMIT
END TRY
BEGIN CATCH

-- Handle your error logging and rollback the transaction so the table locks are released, a basic example:
DECLARE @ErrorMessage VARCHAR(MAX) = ERROR_MESSAGE()
IF @@TRANCOUNT > 0
ROLLBACK
RAISERROR(@ErrorMessage, 16, 1)
END CATCH

但是，如果需要插入的ID来加载其他相关的表，您仍然需要为批插入做额外的工作。

此外，TABLOCKX的限制性很强，还有其他限制性较小的锁，但我相信它们可能会让您面临并发问题。您可以在文档中查看其他锁定提示。