Sample Header Ad - 728x90

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