问题描述
我需要检索SQL Server 2005生成的标识字段。通常，我只会使用SCOPE_IDENTITY或在插入内容中添加OUTPUT CLAUSE，但是在这种情况下，这两种方法都无济于事：因为附加了INSTEAD OF触发器桌子。接下来，我考虑了@@ IDENTITY，但是由于该表还附加了另一个触发器，因此这也不是一件好事。因此，我认为必须使用IDENT_CURRENT。但是，由于这不是会话安全的，因此我需要某种方法来确保在检索到新ID之前，其他会话都不能插入表中。我已经尝试过使用TABLOCK和HOLDLOCK提示，这似乎可行，但是我不是DBA。所以我的问题是，在SQL Server 2005 COMPLETELY中使用TABLOCK和HOLDLOCK提示会阻止插入直到事务结束吗？
I need to retrieve the identity field generated by SQL Server 2005. Usually I would just use SCOPE_IDENTITY or add an OUTPUT CLAUSE to the insert, however neither of these method help in this situation: as there is an INSTEAD OF trigger attached to the table. Next I considered @@IDENTITY, but as there is also another trigger attached to the table, this is no good either. So I figured I'd have to use IDENT_CURRENT. However as this is not session safe I need some way to ensure no other session can insert into the table until I have retrieved the new ID. I've experimented with using the TABLOCK and HOLDLOCK hints, this seems to work, but I'm not a DBA. So my question is, will using the TABLOCK and HOLDLOCK hints in SQL Server 2005 COMPLETELY prevent inserts until the end of the transaction?
希望这个人为的例子可以解释这种情况。 （我意识到，从功能上讲，在这种情况下，存储过程可以替换两个触发器。）
Hopefully this contrived example will explain the situation. (I realise that, functionally, a stored procedure could replace both the triggers in this case.)
CREATE TABLE Person
(
PersonID INT IDENTITY PRIMARY KEY,
FirstName VARCHAR(50)
);
GO
CREATE TABLE PersonHistory
(
PersonHistoryID INT IDENTITY(5,1) PRIMARY KEY,
PersonId INT,
FirstName VARCHAR(50)
);
GO
CREATE TRIGGER PersonAudit ON Person
AFTER Insert
AS
INSERT INTO PersonHistory (PersonID, FirstName)
SELECT Inserted.PersonID, Inserted.FirstName
FROM Inserted;
GO
CREATE TRIGGER PersonCleaner ON Person
INSTEAD OF Insert
AS
INSERT INTO Person (FirstName)
SELECT UPPER(Inserted.FirstName)
FROM Inserted;
GO
BEGIN TRAN;
INSERT INTO Person WITH (TABLOCK, HOLDLOCK) (FirstName)
VALUES ('George');
--SELECT @@IDENTITY AS '@@IDENTITY'; -- 5
--SELECT IDENT_CURRENT('Person') AS 'IDENT_CURRENT'; -- 1
--SELECT SCOPE_IDENTITY() AS 'SCOPE_IDENITIY'; -- NULL
DECLARE @PersonID int;
SELECT @PersonID = IDENT_CURRENT('Person');
SELECT @PersonID; -- 1
COMMIT TRAN;
--SELECT * FROM Person;
--SELECT * FROM PersonHistory;
DROP TABLE Person;
DROP TABLE PersonHistory;
推荐答案
您最好的选择是TABLOCKX提示，该提示指定事务将在表上获得排他锁，直到事务完成。排他（X）锁可防止并发事务访问资源。没有其他事务可以读取或修改使用排他（X）锁锁定的数据。
Your best bet would be the TABLOCKX hint which specifies that an exclusive lock is taken on the table until the transaction completes. Exclusive (X) locks prevent access to a resource by concurrent transactions. No other transactions can read or modify data locked with an exclusive (X) lock.
这篇关于在SQL Server 2005 COMPLETELY中使用TABLOCK和HOLDLOCK提示会阻止插入直到事务结束吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！