对 UPDLOCK 感到困惑，HOLDLOCK

在研究 餐桌提示的使用时，我遇到了以下两个问题:

这两个问题的答案都表明，当使用 (UPDLOCK, HOLDLOCK)时，其他进程将无法读取该表上的数据，但我没有看到这一点。为了进行测试，我创建了一个表并启动了两个 SSMS 窗口。在第一个窗口中，我运行了一个事务，该事务使用各种表提示从表中选择。在事务运行时，我从第二个窗口运行各种语句，以查看哪些语句将被阻塞。

测试表:

CREATE TABLE [dbo].[Test](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Value] [nvarchar](50) NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

从 SSMS 视窗1:

BEGIN TRANSACTION


SELECT * FROM dbo.Test WITH (UPDLOCK, HOLDLOCK)
WAITFOR DELAY '00:00:10'


COMMIT TRANSACTION

从 SSMS Window2(运行下列操作之一) :

SELECT * FROM dbo.Test
INSERT dbo.Test(Value) VALUES ('bar')
UPDATE dbo.Test SET Value = 'baz' WHERE Value = 'bar'
DELETE dbo.Test WHERE Value= 'baz'

不同表提示对在 Windows2中运行的语句的影响:

           (UPDLOCK)       (HOLDLOCK)    (UPDLOCK, HOLDLOCK)    (TABLOCKX)
---------------------------------------------------------------------------
SELECT    not blocked      not blocked       not blocked         blocked
INSERT    not blocked        blocked           blocked           blocked
UPDATE      blocked          blocked           blocked           blocked
DELETE      blocked          blocked           blocked           blocked

我是否误解了这些问题的答案，或者在测试中犯了错误？如果不是这样，为什么要单独使用 (UPDLOCK, HOLDLOCK)和 (HOLDLOCK)？

进一步解释我想要完成的事情:

我希望从表中选择行，并防止在处理该表时修改该表中的数据。我没有修改这些数据，希望允许读操作发生。

这个答案 清楚地说明 (UPDLOCK, HOLDLOCK)将阻止读(不是我想要的)。关于 这个答案的注释意味着阻止读操作的是 HOLDLOCK。为了更好地理解表提示的效果，并且看看单独使用 UPDLOCK是否可以达到我想要的效果，我做了上面的实验，得到了与这些答案相矛盾的结果。

目前，我相信 (HOLDLOCK)是我应该使用的，但我担心我可能犯了一个错误或忽略了一些东西，将来会反咬我一口，因此这个问题。

134911 次浏览
小开

UPDLOCK is used when you want to lock a row or rows during a select statement for a future update statement. The future update might be the very next statement in the transaction.

Other sessions can still see the data. They just cannot obtain locks that are incompatiable with the UPDLOCK and/or HOLDLOCK.

You use UPDLOCK when you wan to keep other sessions from changing the rows you have locked. It restricts their ability to update or delete locked rows.

You use HOLDLOCK when you want to keep other sessions from changing any of the data you are looking at. It restricts their ability to insert, update, or delete the rows you have locked. This allows you to run the query again and see the same results.

小开
最佳答案

Why would UPDLOCK block selects? The Lock Compatibility Matrix clearly shows N for the S/U and U/S contention, as in No Conflict.

As for the HOLDLOCK hint the documentation states:

HOLDLOCK: Is equivalent to SERIALIZABLE. For more information, see SERIALIZABLE later in this topic.

...

SERIALIZABLE: ... The scan is performed with the same semantics as a transaction running at the SERIALIZABLE isolation level...

and the Transaction Isolation Level topic explains what SERIALIZABLE means:

No other transactions can modify data that has been read by the current transaction until the current transaction completes.

Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

Therefore the behavior you see is perfectly explained by the product documentation:

  • UPDLOCK does not block concurrent SELECT nor INSERT, but blocks any UPDATE or DELETE of the rows selected by T1
  • HOLDLOCK means SERALIZABLE and therefore allows SELECTS, but blocks UPDATE and DELETES of the rows selected by T1, as well as any INSERT in the range selected by T1 (which is the entire table, therefore any insert).
  • (UPDLOCK, HOLDLOCK): your experiment does not show what would block in addition to the case above, namely another transaction with UPDLOCK in T2:
    SELECT * FROM dbo.Test WITH (UPDLOCK) WHERE ...
  • TABLOCKX no need for explanations

The real question is what are you trying to achieve? Playing with lock hints w/o an absolute complete 110% understanding of the locking semantics is begging for trouble...

After OP edit:

I would like to select rows from a table and prevent the data in that table from being modified while I am processing it.

The you should use one of the higher transaction isolation levels. REPEATABLE READ will prevent the data you read from being modified. SERIALIZABLE will prevent the data you read from being modified and new data from being inserted. Using transaction isolation levels is the right approach, as opposed to using query hints. Kendra Little has a nice poster exlaining the isolation levels.


提交答案