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