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).
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:
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
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).
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.
You shared a screenshot of profiler in the comments:
I've highlighted session ID 423 because it looks like the following actions all occur inside a transaction:
Numeration table is updated
NumCounter column is selected (presumably from the
- Another statement runs - based on your comments and the query plan, I imagine this is updating the
- Then the transaction is committed
The locks on
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:
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