ROWLOCK UPDLOCK - Locks whole table

Question

ROWLOCK UPDLOCK - Locks whole table

SQLSACT

SSC-Insane

Points: 21838
More actions
October 23, 2020 at 8:44 am

#3800372
Hi All
I came across the following query on one of my systems.
```
SELECT
Col1,
Col2,
Col3,
Col4,
Col5,
Col6,
Col7
FROM TABLE_1 WITH (ROWLOCK,UPDLOCK)
WHERE Col3 = 'value' and Col1 = 'value' and Col2 = 'value'
```
There is a Clustered Unique Primary Key on the table with the following columns in this order: (Col1, Col2, Col3)
The query plan indicates a Clustered Index Seek.
At some point, this query was causing excessive blocking on the system. I saw that the query had a lock against the whole table.
My question is - Is this normal? Why would this query lock the entire table?

Thanks

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

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

ScottPletcher SSC Guru Points: 101122 More actions · Answer 1

I'd expect maybe a Shared lock and even a Schema lock on the table, but I don't think SQL would need to exclusively lock the table.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

Found this article:

https://dba.stackexchange.com/questions/114572/why-does-updlock-cause-selects-to-hang-lock

From that link, the solution is to have a better index. Now, that being said, I would expect that since you are doing a SEEK, your index is good enough.

My next thought is that the issues isn't with your query, but with the second query in the blocking chain. If your query runs for a while and the next query in the chain is looking to change the data on a row you have a lock on, it will need to wait. So lets say someone wants to delete from the table where Col1='value', the second query is going to need an exclusive lock on multiple pages and may escalate to table level lock. Since it needs an exclusive type lock, it will need to wait for your SELECT to finish before it can start.

I would look at the lock type against the table. Is it a shared lock when your query runs or an exclusive lock? My guess is that it isn't an exclusive lock and therefore should not block other SELECT queries. But anything that is requesting to change data may be waiting for your query to complete.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 3

Why do you think you need the locking hints at all?

Like Paul White said in the thread referenced by Brian...

As documented in Books Online, UPDLOCK takes update locks and holds them to the end of the transaction.
Without an index to locate the row(s) to be locked, all tested rows are locked, and locks on qualifying rows are held until the transaction completes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)