Does a ROWLOCK lock the whole index

Question

Does a ROWLOCK lock the whole index

arrjay

Hall of Fame

Points: 3455
More actions
February 19, 2014 at 5:16 am

#304606

Hi there - if I applied the ROWLOCK hint would it lock the whole index?
The situation I am experiencing is detailed in this post; http://www.sqlservercentral.com/Forums/Topic1542043-391-1.aspx
I need to reindex a table but this results in a deadlock becuase a SELECT statement has a TABLOCK on the table.
Thanks!
--------------------------------------------
Laughing in the face of contention...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

RowLock takes locks at the row level. It won't prevent lock escalation, but it will start the locks at the row level.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

arrjay Hall of Fame Points: 3455 More actions · Answer 2

Thanks for your response Gail.

I guess my question is if I have a ROWLOCK on a table would I be able to run a reindex, or in other words does the re-index require an exclusive lock

--------------------------------------------

Laughing in the face of contention...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

From Books Online (under ALTER INDEX)

Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered, spatial, or XML index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

arrjay Hall of Fame Points: 3455 More actions · Answer 4

Thats great. Thanks!

--------------------------------------------

Laughing in the face of contention...