Confused about UPDLOCK, HOLDLOCK
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 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.