在 SQL Server 2005 中使用 TABLOCK 和 HOLDLOCK 提示是否会完全阻止插入直到事务结束?
我需要检索 SQL Server 2005 生成的标识字段。通常我只会使用 SCOPE_IDENTITY 或向插入添加 OUTPUT CLAUSE,但是这些方法在这种情况下都没有帮助:因为表附加了 INSTEAD OF 触发器。接下来我考虑了@@IDENTITY,但由于表上还附加了另一个触发器,这也不好。所以我想我必须使用IDENT_CURRENT。然而,由于这不是会话安全的,我需要某种方法来确保在我检索到新 ID 之前没有其他会话可以插入表中。我尝试过使用 TABLOCK 和 HOLDLOCK 提示,这似乎有效,但我不是 DBA。所以我的问题是,在 SQL Server 2005 中使用 TABLOCK 和 HOLDLOCK 提示是否会完全阻止插入直到事务结束?
希望这个人为的例子能够解释这种情况。 (我意识到,从功能上讲,存储过程可以取代这种情况下的两个触发器。)
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;
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;
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
最好的选择是 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.