困惑关于UPDLOCK，HOLDLOCK [英] Confused about UPDLOCK, HOLDLOCK
问题描述
在研究表提示的使用时，我遇到了以下两个问题：
这两个问题的答案说，当使用（UPDLOCK，HOLDLOCK）时，其他进程将无法读取表，但我没有看到这个。要测试，我创建了一个表并启动了两个SSMS窗口。从第一个窗口，我使用各种表提示运行从表中选择的事务。在事务正在运行时，从第二个窗口我运行了各种语句来查看哪些会被阻止。
测试表：
CREATE TABLE [dbo]。[Test]（
[ID] [int] IDENTITY（1,1）NOT NULL，
[ [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'
提交交易
$ b b
从SSMS窗口2（运行以下操作之一）：
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'
不同的表提示对窗口2中运行的语句的影响：
（UPDLOCK）（HOLDLOCK）（UPDLOCK，HOLDLOCK）（TABLOCKX）
-------------------- -------------------------------------------------- -----
SELECT未阻止未阻止未阻止阻止阻止
INSERT未阻止阻止阻止阻止阻止
更新阻止阻止阻止阻止
DELETE阻止阻止阻止阻止
我误解了这些问题中给出的答案，或者在我的测试中犯了一个错误？如果没有，为什么你会单独使用（UPDLOCK，HOLDLOCK）与（HOLDLOCK）？
进一步说明我要完成的事项：
从表中选择行，并防止在处理该表时修改该表中的数据。我不会修改该数据，并且希望允许读取。
（UPDLOCK，HOLDLOCK）将阻塞读取（不是什么）我想要）。有关此答案的评论意味着是 HOLDLOCK ，可防止读取。为了尝试和更好地理解表提示的效果，看看 UPDLOCK 单独会做我想要的，我做了上面的实验，得到的结果与这些答案矛盾。 p>
目前，我相信（HOLDLOCK）是我应该使用的，但我担心我可能已经
为什么UPDLOCK会阻止选择？ 锁定兼容性矩阵清楚地显示了 N 对于S / U和U / S争用，如无冲突。
至于 HOLDLOCK 提示文档说明：
HOLDLOCK：相当于SERIALIZABLE。有关详细信息，请参阅本主题后面的SERIALIZABLE
。
...
SERIALIZABLE： ...使用与在SERIALIZABLE隔离级别运行的事务相同的语义执行扫描...
href =http://msdn.microsoft.com/en-us/library/ms173763.aspx>事务隔离级别主题说明什么是SERIALIZABLE的意思：
< blockquote>
其他事务可以修改
当前事务读取的数据，直到当前事务完成。
其他事务不能插入新行的键值，
落在当前
事务中的任何语句读取的键范围内，直到当前事务完成。
因此，您看到的行为完全由产品文档解释：
- UPDLOCK不阻止并发SELECT或INSERT，但阻止由T1选择的行的任何UPDATE或DELETE
- HOLDLOCK表示SERALIZABLE，因此允许SELECTS，但是阻止由T1选择的行的UPDATE和DELETES，以及作为由T1选择的范围中的任何INSERT（这是整个表，因此插入）。
- （UPDLOCK，HOLDLOCK） ：除了上述情况，您的实验没有显示会阻止什么，即在T2 中使用UPDLOCK的另一个事务：
SELECT * FROM dbo.Test WITH（UPDLOCK）WHERE ... - TABLOCKX无需解释
$ b b
真正的问题是你想实现什么？使用锁提示时，锁定语义绝对完全110％的理解是乞求麻烦...
OP编辑后：
我想从表中选择行，并防止在
表中的数据在我处理时被修改。
您应该使用较高的事务隔离级别之一。 REPEATABLE READ将阻止您读取的数据被修改。 SERIALIZABLE将阻止您读取的数据被修改和新数据被插入。使用事务隔离级别是正确的方法，而不是使用查询提示。 Kendra Little有一个漂亮的海报，展现了隔离级别。
While researching the use of Table Hints, I came across these two questions:
Answers to both questions say that when using (UPDLOCK, HOLDLOCK), other processes will not be able to read data on that table, but I didn't see this. To test, I created a table and started up two SSMS windows. From the first window, I ran a transaction that selected from the table using various table hints. While the transaction was running, from the second window I ran various statements to see which would be blocked.
The test table:
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]
From SSMS Window 1:
BEGIN TRANSACTION
SELECT * FROM dbo.Test WITH (UPDLOCK, HOLDLOCK)
WAITFOR DELAY '00:00:10'
COMMIT TRANSACTION
From SSMS Window 2 (ran one of the following):
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'
Effect of different table hints on statements run in Window 2:
(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
Did I misunderstand the answers given in those questions, or make a mistake in my testing? If not, why would you use (UPDLOCK, HOLDLOCK) vs. (HOLDLOCK) alone?
Further explanation of what I am trying to accomplish:
I would like to select rows from a table and prevent the data in that table from being modified while I am processing it. I am not modifying that data, and would like to allow reads to occur.
This answer clearly says that (UPDLOCK, HOLDLOCK) will block reads (not what I want). The comments on this answer imply that it is HOLDLOCK that prevents reads. To try and better understand the effects of the table hints and see if UPDLOCK alone would do what I wanted, I did the above experiment and got results that contradict those answers.
Currently, I believe that (HOLDLOCK) is what I should use, but I am concerned that I may have made a mistake or overlooked something that will come back to bite me in the future, hence this question.
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.
这篇关于困惑关于UPDLOCK，HOLDLOCK的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
