Queries from different tables locking each other (LCK_M_X) 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.
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