update vs insert deadlock...update seems to be taking X lock and not a U lock

Question

Post reply

update vs insert deadlock...update seems to be taking X lock and not a U lock

sambartick 90616

SSC Eights!

Points: 947
More actions
March 14, 2018 at 6:34 am

#327812

hello,
a client is getting the attached deadlock and i am struggling to see why a parameterised update for a single row is causing a deadlock against an insert statement.
can someone have a look and let me know...from what i can read both statements have an IX lock and are looking to take an X lock.
many thanks
samuel

Viewing 8 posts - 1 through 7 (of 7 total)

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

sambartick 90616 SSC Eights! Points: 947 More actions · Answer 1

just to note....these two queries deadlock frequently and either can be the victim...depends on timing

was assuming it was an index lock maybe but all locks seem to be object related

thinking about using rowlock or updlock hints....also maybe changing the isolation level as they have a lot of issues with reads

ideally the database would be redesigned but in the meantime i am here trying to help

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

I haven't looked at the deadlock graph as I have a meeting in a couple minutes, but updates absolutely do take X locks.

The update will initially take U locks (and IU) as its finding the rows that need to be updated. Once it has a U lock on all rows that need to be updated (or on the pages containing them), it will then convert the IU locks to IX and the U locks to X and then will perform the update.

If the X lock is taken on the row, then there will be IX at the page and table level (intention to lock one or more sub-resources X). If the X lock is on the page, then there will be an IX on the table.

Don't know if that helps you at all.

Edit: A updlock hint only sets the minimum lock level to U. If an X lock is needed, an X lock will still be used. Isolation levels don't affect data modifications much, they affect how long shared locks are held (for the most part)

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

sambartick 90616 SSC Eights! Points: 947 More actions · Answer 3

many thanks gail

when you have a second to confirm your theory by checking the deadlock graph.

i missed the page/table lock above the rowlock bit....that makes much more sense.

yeah the isolation level would solve a lot of read issues they get but assuming the insert/update conflict could use different row versions if they had to?

much appreciated....i will go back to the developers with this info 🙂

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

sambartick 90616 - Wednesday, March 14, 2018 7:44 AM
when you have a second to confirm your theory by checking the deadlock graph.

Nothing I said was a theory. That's a description of how SQL's locking works.

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

sambartick 90616 SSC Eights! Points: 947 More actions · Answer 5

wrong choice of words....the locking behavious is obviously correct...you would know above all others!!

whether that is happening here may be in question depending on the exact deadlock information

i am not the best at interpreting them 🙁

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

Just noticed the deadlock graph is Excel. I don't like downloading unknown spreadsheets, and Excel mangles the data as it is.
Can you attach either the deadlock graph (.xdl) or the details in a text file please?

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

sambartick 90616 SSC Eights! Points: 947 More actions · Answer 7

sure...added as txt file....no graph im afraid

let me know if this is ok...if not i should be able to screenshot but txt may be a little small

many thanks