2

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.

2

Uncommitted Transactions

As Dan Guzman mentioned, check for uncommitted transactions (e.g. using DBCC OPENTRAN) and look at the locks held by the blocking session. It may be a query timed out, wasn't rolled back properly, and the pooled connection with the open transaction reused. You need to remember locks are held by the session of that 'unrelated' query and it is quite likely that in that session the identifier update was done before and still locked (mentioned by eckes).

Foreign Keys

Aaron Bertrand mentioned foreign keys might be involved in your blocking issue. Since you confirmed there are foreign keys defined on these tables, I'd like to briefly expand on that.

Take the second update in your execution plan as an example:

UPDATE Entity WITH (ROWLOCK) 
SET EntityTypeCid = 2, EntityNumber = 49634989, NumberIssued = 1 WHERE Did = @p10

And this subset of the execution plan for that statement:

screenshot of FK assertion in execution plan

As expected, since you're updating the Entity table, there's a "Clustered Index Update" operator performing that update*. Notice, though, that there's also a seek into the EntityType table, followed by an Assert to make sure that the value being set in Entity.EntityTypeCid (2 in this case) has a corresponding match in EntityType.Cid).

All that to say, the update query looks like it would only acquire locks on the Entity table, when in fact it will also briefly need locks from the EntityType table as well (to validate the foreign key constraint). So other queries that acquire locks on the EntityType table could block this one.

I don't see any evidence of that happening on the update of Numeration.NumCounter, but if other tables reference NumCounter as a foreign key then the same thing could be happening there (in reverse).

Triggers

Another possibility is that the table has Triggers defined on it, which perform data access to other tables, causing blocking. You already clarified that there are no triggers involved in these specific tables, so I just mention this for completeness.

Long-Running Transactions

You shared a screenshot of profiler in the comments:

screenshot of profiler output

I've highlighted session ID 423 because it looks like the following actions all occur inside a transaction:

  1. The Numeration table is updated
  2. The NumCounter column is selected (presumably from the Numeration table)
  3. Another statement runs - based on your comments and the query plan, I imagine this is updating the Entity table
  4. Then the transaction is committed

The locks on Numeration and Entity will be held for the duration of the transaction when using the default READ COMMITTED isolation level, which could block other sessions trying to read from or update these rows.

In this specific screenshot, all of that happened very quickly. But if the 3rd statement takes longer than expected, then the lock on Numeration is also held longer, creating a blocking chain like the one shown in your other screenshot:

screenshot of sp_WhoIsActive blocking chain

Notice that loads of sessions are waiting on access to the Numeration table, all of them blocked by session 227 (which is updating the Numeration table). Session 227 is being blocked by session 222, which isn't shown in your screenshot.

It's hard to make suggestions on what to do about this without knowing what session 222 is doing. If that's a read query, you might have better luck with an optimistic isolation level like READ COMMITTED SNAPSHOT isolation (RCSI), since in that case readers wouldn't block writers.

The problem might be a higher-level, architectural issue - where this Numeration table is a bottleneck that needs to be scaled out somehow.


* this likely isn't relevant to your question, but the update is also updating 3 nonclustered indexes on that table which contain one or more of the columns being updated. This is not really obvious in SSMS, but SentryOne Plan Explorer calls it out nicely

| improve this answer | |
  • The tables don't use any triggers and i disabled the FK, still locking. I am using JMeter to create load just like multiple Users GUI inputs, its always the same action. My idea is that this is a transaction that starts with an Update Numeration unique identifier and after it updates Entity the transaction is commited, since there are many transactions trying to read the Numeration row and the update entity is not commited it is still locked.. does this make sense? i don't know how to know if the 3 queries are in one batch/transaction, here is the SQLProfiler. imgur.com/a/fXLyD8w – xicotaslb Sep 10 '19 at 10:19
  • @xicotaslb I've updated my answer with more information based on your screenshots there. I think you are on the right track, it's likely a long-running, multi-statement transaction creating a blocking chain. – Josh Darnell Sep 10 '19 at 13:07
  • I think i found the culprit.. i found the session ID causing the lock using sp_whoisactive and with "SQL Server - Transaction Report" i saw the transaction Id, i saw the exclusive locks and they are in a "DidRegistry" table, which saves all "did" unique identifiers, they are both locking the same page because the query has the same datetime in column "Creationmoment", i.e. CreationMoment='2019-09-10 11:03:07.487' . Maybe i should change this to Datetime2.. – xicotaslb Sep 10 '19 at 13:07
  • @xicotaslb Nice job tracking that down then! – Josh Darnell Sep 10 '19 at 13:32
  • Thank you for the insights, architecturally i don't know if this is a problem since this is a very specific case where i just make the same query multiple times, it definitely shows a cap in scalability imo, maybe someday i have real user load models to play with :d. – xicotaslb Sep 10 '19 at 13:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.