Sample Header Ad - 728x90

SQL Server and NHibernate - blocking occurs between two unrelated queries (distributed transactions)

1 vote
1 answer
463 views
there's a problem that I'm seeking your help with... There are two simple queries: 1. DELETE a row from a table in a databaseA 2. SELECT a row from a table in a databaseB using hints with (updlock, rowlock) Query 1 blocks query 2 The Blocked Process Report shows the following: SELECT stuff from whatever1.table1 WHERE bunch_of_things with (updlock, rowlock) DELETE from whatever2.table2 WHERE bunch_of_things Looks like the blocked query is a distributed transaction (DTCXact), right? A query to sys.dm_tran_database_transactions shows that: - the blocked query (the SELECT) is associated with just databaseB - the blocking query (the DELETE) is associated with 3 databases: databaseA, **databaseB** and databaseC - wouldn't that mean a distributed transaction as well? The queries come from NHibernate. Would you please help me find the root cause of this blocking and how to solve it? Can it be related to misconfiguration in NHibernate? Connection pooling issue? I'm out of ideas and I don't know much about NHibernate. I'm thinking of dropping the updlock hint and maybe turning on Snapshot Isolation, but if it's the NHibernate's fault, then I'd rather have that corrected. Thanks in advance...
Asked by Timbalero (111 rep)
Dec 17, 2021, 12:59 AM
Last activity: Aug 10, 2022, 09:04 AM