1

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:

<blocked-process-report monitorLoop="914797">
 <blocked-process>
  <process id="process2e077f95088" taskpriority="0" logused="0" waitresource="KEY: 51:72057594213498880 (8bb5457f059a)" waittime="28217" ownerId="8906221681" transactionguid="0xeaa174df0998654799709e48bef6ff3b" transactionname="DTCXact" lasttranstarted="2021-12-13T17:28:22.620" XDES="0x2d960cd4040" lockMode="U" schedulerid="1" kpid="8748" status="suspended" spid="290" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2021-12-13T17:28:22.623" lastbatchcompleted="2021-12-13T17:28:22.623" lastattention="1900-01-01T00:00:00.623" clientapp=".Net SqlClient Data Provider" hostname="AAAAAA" hostpid="13524" loginname="XXX\User1" isolationlevel="read committed (2)" xactid="8906221681" currentdb="51" currentdbname="DatabaseB" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame line="1" stmtstart="44" stmtend="884" sqlhandle="0x0200000092d95e215a495660d80f9ae81a21bb7563b968e60000000000000000000000000000000000000000" />
    <frame line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000" />
   </executionStack>
   <inputbuf>
SELECT stuff from whatever1.table1 WHERE bunch_of_things with (updlock, rowlock)    </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="sleeping" spid="58" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2021-12-13T17:28:50.140" lastbatchcompleted="2021-12-13T17:28:50.140" lastattention="1900-01-01T00:00:00.140" clientapp=".Net SqlClient Data Provider" hostname="AAAAAA" hostpid="13524" loginname="XXX\User1" isolationlevel="read committed (2)" xactid="8906151325" currentdb="35" currentdbname="DatabaseA" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack />
   <inputbuf>
DELETE from  whatever2.table2 WHERE bunch_of_things </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>

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...

Improve this question
3
  • Is there any correlation between the two tables in question, whatever1.table1 and whatever2.table2, e.g. triggers? A distributed transaction is one that occurs across multiple servers (not just databases) typically.
    – J.D.
    Dec 17, 2021 at 1:44
  • no correlation on the SQL Server level, no triggers either. There is only one server, albeit other cross-database queries do happen there, too. Maybe some correlation is enforced by NHibernate, but I don't know how to check it. @ErikDarling I noticed that sleeping status too. In fact, based on your blog post I checked (XEvents: existing_connections) if implicit transactions were involved, but I they weren't. But it does look like the transaction isn't committing in time - hence my thoughts of NHibernate/connection pooling misconfiguration. I have no proof, though.
    – Timbalero
    Dec 17, 2021 at 8:35
  • One more thing - my XEvent session captured other events, too. It looks like overall some rpc calls begin with exec sp_reset_connection sequence, while some don't. Those queries involved in the blocking don't. Can it mean connection reuse problems?
    – Timbalero
    Dec 17, 2021 at 8:49

1 Answer 1

Reset to default
4

the blocked process report sucks

I've mentioned it in my post here:

But you can see similar effects when queries executed using the normal transaction mechanism don't commit or rollback at the end of their commands, because the report isn't guaranteed to gather exactly-related statements.

The blocked process report is done on a best effort basis. There is no guarantee of any real-time or even close to real-time reporting.

You can get part of the way to figuring this out by looking at a couple clues:

  • The delete query holding locks has a sleeping status

  • The select query is not the only one in the batch that's running

    stmtstart="44" stmtend="884"...

Good luck with that 840 line select, though 😉

I'd look at two things:

  • The larger code block that the select runs in, for references to the table that the delete is hitting
  • Whatever calls the delete query to make sure it's committing correctly when the query finishes

This is probably the wrong forum to ask specifically about NHibernate issues (lo siento on that choice) -- StackOverflow would be better -- but perhaps you'll get lucky and someone will come along who knows more about it.

Improve this answer
0

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.