1

Problem:

A .NET application during business transaction executes a query like 

UPDATE Order 
SET Description = 'some new description` 
WHERE OrderId = @p1 AND RowVersion = @p2

This query hangs until timeout (several minutes) and then I get an exception:

SqlException: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

It is reproduced, when database is under heavy load (several times per day).
I need to detect the cause of the lock of the query.

What I've tried:

  1. Exploring activity monitor - it shows that the query is hanging by lock. Filtering by headblocker does not give much, it is frequently changing.

  2. Analyze SQL script, that gives similar to activity monitor data - almost same result as looking to activity monitor. Chasing blocking_session_id results in some session, that awaits for command or executing some SQL, I can't reason a relation to Order table. Executing the same script in a second gives other session. I also tried a some other queries/stored procedures from this atritcle with no result.

  3. Building standard SQL Server report for locked/problem transactions results in errors like Max recursion exhausted or Local OutOfMemory Exception (I have 16 Gb RAM).

Database Details

  • Version: SQL Server 2016
  • Approximate number of concurrent queries per second by apps to database: 400
  • Database size: 1.5 Tb
  • Transaction isolation level: ReadUncommited for readonly transactions, Serializable for transactions with modifications

I'm absolutely new to this kind of problems, so I have missed a lot for sure.
Any help or direction would be great!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
FireAlkazar
  • 1,795
  • 1
  • 14
  • 27
  • What is it actually blocked on? What else is that transaction doing? Using Serializable for every transaction that modifies data seems potentially problematic, is there a reason you've done that? – TZHX Apr 16 '17 at 06:33
  • @TZHX The transaction reads from ~30 tables and updates 3 tables. About Serializable - a whole huge app uses this pattern, I'm a newcomer, don't know the reason yet. What do you mean about actually blocked on? Kind of lock or something else? – FireAlkazar Apr 16 '17 at 06:40
  • 1
    So Serializable means "every lock I take out, keep as mine until I'm done. Even reads." And it does this by taking range locks on tables, not just row locks. If you have two transactions trying to, for example, insert an order after the one you're updating, that's a no go. Serializable has its place, but on every transaction sounds like overkill and certainly contributing to your problems. – TZHX Apr 16 '17 at 06:44
  • 1
    As for what is the problem, write a query looking at dm_exec_requests (or google for one) and pay attention to the waittype and waitresource columns, as well as querying to get the text that is running at that time (using the statement offsets, again you should be able to find examples). Once you have the query that's waiting, know where it's up to, and what exactly it's waiting for you have more information to work with. – TZHX Apr 16 '17 at 06:48
  • @TZHX I talked about Serializable with colleagues, they admit this not very clear to them desicion too. The exact query text that is waiting is this `UPDATE Order SET Description='some new description` WHERE OrderId=@p1 AND RowVersion=@p2` (shows in activity monitor and activity monitor like script). Will try to find waittype and waitresource info. – FireAlkazar Apr 16 '17 at 06:52
  • Try `sys.dm_exec_requests view`, and filter by columns `blocking_session_id, wait_time` – Mikhail Lobanov Apr 17 '17 at 09:05
  • @MikhailLobanov @TZHX I'm quering now a combination of `dm_exec_requests` and `dm_tran_locks` and it looks really promising. If you post your advice I'll accept/upvote it – FireAlkazar Apr 17 '17 at 12:34

2 Answers2

2

In case anyone interested, I have found this particular query espesially usefull:

SELECT tl.resource_type
 ,OBJECT_NAME(p.object_id) AS object_name
 ,tl.request_status
 ,tl.request_mode
 ,tl.request_session_id
 ,tl.resource_description
 ,(select text from sys.dm_exec_sql_text(r.sql_handle))
FROM sys.dm_tran_locks tl
    INNER JOIN sys.dm_exec_requests r ON tl.request_session_id=r.session_id
    LEFT JOIN sys.partitions p ON p.hobt_id = tl.resource_associated_entity_id
WHERE tl.resource_database_id = DB_ID()
    AND OBJECT_NAME(p.object_id) = '<YourTableName>'
ORDER BY tl.request_session_id

It shows transactions, that have acquired locks on <YourTableName> and what query they are executing now.

FireAlkazar
  • 1,795
  • 1
  • 14
  • 27
1

Try to use sys.dm_exec_requests view, and filter by columns blocking_session_id, wait_time

Mikhail Lobanov
  • 2,976
  • 9
  • 24