sql server - Confused about UPDLOCK, HOLDLOCK -

while researching use of table hints, came across these 2 questions:

• which lock hints should use (t-sql)?

• what effect holdlock have on updlock?

answers both questions when using (updlock, holdlock), other processes not able read data on table, didn't see this. test, created table , started 2 ssms windows. first window, ran transaction selected table using various table hints. while transaction running, second window ran various statements see 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 * dbo.test (updlock, holdlock) waitfor delay '00:00:10'  commit transaction 

from ssms window 2 (ran 1 of following):

select * dbo.test insert dbo.test(value) values ('bar') update dbo.test set value = 'baz' value = 'bar' delete dbo.test 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 misunderstand answers given in questions, or make mistake in testing? if not, why use (updlock, holdlock) vs. (holdlock) alone?

---

further explanation of trying accomplish:

i select rows table , prevent data in table being modified while processing it. not modifying data, , allow reads occur.

this answer says (updlock, holdlock) block reads (not want). comments on this answer imply holdlock prevents reads. try , better understand effects of table hints , see if updlock alone wanted, did above experiment , got results contradict answers.

currently, believe (holdlock) should use, concerned may have made mistake or overlooked come bite me in future, hence question.

why updlock block selects? lock compatibility matrix shows n s/u , u/s contention, in no conflict.

as holdlock hint documentation states:

holdlock: equivalent serializable. more information, see serializable later in topic.

...

serializable: ... scan performed same semantics transaction running @ serializable isolation level...

and transaction isolation level topic explains serializable means:

no other transactions can modify data has been read current transaction until current transaction completes.

other transactions cannot insert new rows key values fall in range of keys read statements in current transaction until current transaction completes.

therefore behavior see explained product documentation:

• updlock not block concurrent select nor insert, blocks update or delete of rows selected t1
• holdlock means seralizable , therefore allows selects, blocks update , deletes of rows selected t1, as well insert in range selected t1 (which entire table, therefore any insert).
• (updlock, holdlock): experiment not show block in addition case above, namely another transaction updlock in t2:
  select * dbo.test (updlock) ...
• tablockx no need explanations

the real question what trying achieve? playing lock hints w/o absolute complete 110% understanding of locking semantics begging trouble...

after op edit:

i select rows table , prevent data in table being modified while processing it.

the should use 1 of higher transaction isolation levels. repeatable read prevent data read being modified. serializable prevent data read being modified and new data being inserted. using transaction isolation levels right approach, opposed using query hints. kendra little has a nice poster exlaining isolation levels.