Queries from different tables locking each other (LCK_M_X)
2
votes
1
answer
1982
views
Background: Was hired to work with JMeter for performance testing but I'm also getting more and more into SQL performance (because of time, and because I like it). I still suck, and will for a long time. I will try to explain the best I can.
I have a table (
NUMERATION
) that is updated by many transactions. It contains most unique identifiers, so it gets a lot of updates.
This query
UPDATE Numeration WITH (ROWLOCK) SET NumCounter = NumCounter + 1 WHERE Tid = @p0
...is sometimes being locked by queries who have no relationship to this one, and I have no idea why.
There are no triggers in the tables. There is a Foreign Key that references a Unique identifer in another table, I don't know how to map all relationships but I think both queries have FKs on this Identifier
I used sp_WhoIsActive
and sp_BlitzFirst
to see the session IDs and type of lock (LCK_M_X
). The query plans are below:
https://www.brentozar.com/pastetheplan/?id=r1ebGh7Lr
Hope you can help me with not my homework.
Asked by xicotaslb
(31 rep)
Sep 9, 2019, 11:20 AM
Last activity: Sep 10, 2019, 01:06 PM
Last activity: Sep 10, 2019, 01:06 PM