次の方法で共有

Attention events can cause open transactions and blocking in SQL Server

  • [アーティクル]

Wondering what causes Attention events in a SQL server Profiler trace and how these events can result into severe blocking problems? I will attempt to provide some answers in this post.

First of all let’s talk about what Attentions events are and what may cause them. According to BOL –

"The Attention event class indicates that an attention event, such as cancel, client-interrupt requests, or broken client connections, has occurred. Cancel operations can also be seen as part of implementing data access driver time-outs"

There are three most common reasons of Attention events that I have seen in my experience so far. In each case, Attention events were causing open transactions and resulting in massive blocking issues. Here are those three scenarios -

1. Query Cancellation

User may cancel the currently executing query/batch request at any time - may be the user got tired of waiting on the screen! If the query or the batch was in an explicit user transaction (BEGIN TRAN …. END TRAN), these Attention events could result in open transactions and severe blocking problems. We can reproduce this scenario from within SQL Server Management Studio. Try the following -

  • Run a SQL Server Profiler trace with the following events selected - Attention, SQL:BatchCompleted, SQL:BatchStarting, SQL:StmtCompleted, SQL:StmtStarting
  • Open Management Studio and run the following batch –
  USE TEMPDB 
 GO 
 
 IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID (N'[DBO].[TEST]') AND TYPE in (N'U')) 
 DROP TABLE [DBO].[TEST] 
 GO 
 
 --Create a Test table and Insert few records in it 
 
 CREATE TABLE TEST (C1 INT, C2 VARCHAR (100)) 
 GO 
 
 INSERT INTO TEST VALUES (1, 'AAA') 
 GO 
 INSERT INTO TEST VALUES (2, 'BBB') 
 GO 
 
 -- Simulate a user transaction that will take a several seconds to execute 
 -- we will use the WAITFOR command to simulate such a transaction 
 
 BEGIN TRAN 
 UPDATE TEST SET C2 = 'MODIFY1' 
 WHERE C1 = 1 
 WAITFOR DELAY '00:00:45' 
 COMMIT TRAN
  • While the transaction is in progress, click on the Cancel option (red square button on the toolbar) -
  • You will notice an Attention event in Profiler trace.

2_Profiler_Showing_Attention

  • In the trace, you will also notice that the COMMIT TRAN statement never executed and so the transaction is still open. You can confirm that by running the following statement from the same query window in which you had cancelled the query
 SELECT @@TRANCOUNT 
GO
  • Now let’s see what kind of locks are being held by this open transaction. Run a select query on the sys.dm_tran_locks DMV (you may want to filter the result on your session to exclude extra noise). You should notice an exclusive (X) lock on the row (RID), and Intent exclusive (IX) locks on the Page and the table. So with those locks in place, if another user starts a query that needs a conflicting lock on any of these resources, you will see blocking. For example, if another user/transaction/query tries to SELECT/UPDATE/DELETE the same row, it will get blocked by the open transaction. Try running this SELECT query in a another query window
 SELECT * FROM TEST WHERE C1=1 
GO
  • Open the “Activity - All Blocking Transactions” server report to analyze the blocking chain (right click on your SQL instance in Object Explorer -> Reports -> Standard Reports -> Activity – All Blocking Transactions)

6_Blocking_Transaction_report

So the question is how long will this blocking go on for? Actually this blocking chain will go indefinitely until one of the following events occurs –

  • The open transaction is committed or rolled back manually, since we just saw that SQL server will not automatically commit or rollback the transaction on Attention events.
  • The connection (or SQL Server session) that started the transaction is terminated. This will happen when the user disconnects from the application.
  • The connection is recycled in a connection the pool, if the application is using connection pooling.

2. Query Timeouts:

This is the second most common reason I have seen in my experience, which causes Attention events, open transactions and blocking issues. The default query timeout in SQL Server is 0, which means that a query will run indefinitely until completion. However, application developers can change the default query timeout value in SQL Server connection string. Also, OLEDB and ODBC providers of SQL Server have non-zero query timeout values as discussed in this article – DBA’s Quick Guide to Timeouts. Query timeouts, if not handled properly, can cause blocking issues in SQL Server. Let’s see how that can happen -

  • Open a new query editor window in Management Studio that will prompt you to specify the authentication details. You can do that by clicking on the File menu –> New –> Database Engine Query. On the Connect to Database Engine screen, click on the Option >> button and change the Execution time-out setting from default of 0 to 15. The default value of 0 means the query will run indefinitely. Here is a snapshot-

9_SSMS_Change_Execution_Timeout

  • Restart the Profiler trace and run the following batch from the query window that you just opened –
  USE TEMPDB 
 GO 
 
 -- Run a TSQL batch request that would take a few seconds to execute 
 -- We will use the WAITFOR command to simulate such a query 
 
 IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[TEST2]') AND TYPE IN (N'U')) 
 DROP TABLE [DBO].[TEST2] 
 GO 
 
 --Create a Test table and Insert few records in it 
 
 CREATE TABLE TEST2 (C1 INT, C2 VARCHAR (100)) 
 GO 
 
 INSERT INTO TEST2 VALUES (1, 'AAA') 
 GO 
 INSERT INTO TEST2 VALUES (2, 'BBB') 
 GO 
 
 --Start a user transaction that would take about 10 seconds to complete
 
 BEGIN TRAN 
 UPDATE TEST2 SET C2 = 'MODIFY1' 
 WHERE C1 = 1 
 WAITFOR DELAY '00:00:45' 
 COMMIT TRAN

After 15 seconds, which is the query timeout value that we had set while connecting to the server, the batch will terminate with the following timeout error –

Msg -2, Level 11, State 0, Line 0 Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

  • You will also notice an Attention event in the trace. If you check the open transaction count by running the SELECT @@TRANCOUNT query from the same query window in which you were running the batch, it will return 1, meaning that there is one open transaction. If you run a SELECT query on the sys.dm_tran_locks DMV, you will notice the same set of locks being held by the open transaction as you saw in the first scenario. If the application is not handling the query timeout errors and rolling back open transactions, it will result in blocking.

What about Lock timeout errors? Will they also result in Attention events?

The answer is NO! Lock Timeout errors do not cause Attention events and execution will proceed to the next statements in the transaction. See it for yourself!

  • Run the following batch from a new query window (if you are using an existing query window that is already open, make sure to roll back all open transactions in that window) -

     

      USE TEMPDB 
 GO 

 -- Create a Test table and Insert some rows in it 
 IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[TEST3]') AND TYPE IN (N'U')) 
 DROP TABLE [DBO].[TEST3] 
 GO 

 CREATE TABLE TEST3 (C1 INT, C2 VARCHAR (100)) 
 GO 

 INSERT INTO TEST3 VALUES (1, 'AAA') 
 GO 

 INSERT INTO TEST3 VALUES (2, 'BBB') 
 GO 

 -- Run a TSQL batch request that will start a 
 -- transaction but do not COMMIT or ROLLBACK the transaction 
 -- I am doing this to create a classic blocking 
 -- scenario to test lock timeout errors 

 BEGIN TRAN 
 UPDATE TEST3 SET C2 = 'MODIFY1' 
 WHERE C1 = 1 
 GO

     

  • Restart the Profiler trace and run the following batch in a second query editor window –
 USE Tempdb
 GO
 
 SET LOCK_TIMEOUT 5000 
 GO 
 
 BEGIN TRAN 
 UPDATE TEST3 SET C2 = 'MODIFYAGAIN' 
 WHERE C1 = 1 
 COMMIT TRAN
  • Expectedly, after 5 seconds, the batch will terminate with a lock timeout error (Error 1222) –

Msg 1222, Level 16, State 45, Line 3 Lock request time out period exceeded. The statement has been terminated.

  • However, you will not see an Attention event in the Profiler trace this time. You will also notice that after the lock timeout error occurred, SQL server continued with the execution of the batch and finally committed the transaction even though the UPDATE statement had failed. If you run a SELECT @@TRANCOUNT in the same query window, it will return 0, meaning there is no open transaction

12_Trace_Lock_timeout

3. Applications not processing the result set completely

I have seen this problem while working with a customer who had an ODBC application fetching data from SQL Server. The application was coded to ask for a result set in a cursor, fetch the first few records (and not all of the requested rows) and discard the rest. This problem has been described in detail in this blog. Unfortunately, I don’t have an application to demonstrate this behavior. But if you are noticing the following characteristics with your application and seeing a large number of Attention events and blocking chains in SQL Server, this could be the case.

  • You notice a large number of Attention events in the a SQL Server profiler trace and you have confirmed that they are not caused by user cancellation or query timeouts
  • You are seeing a pattern in the trace showing that the Attention event arrived right after the batch started, generally milliseconds afterwards and not something like a 30 second query timeout. Here is a snapshot of this pattern that I was seeing in SQL traces -

Attention_Events

  • You are seeing ASYNC_NETWORK_IO waits in SQL Server, which is an indication that the application is not processing the result set completely.

Solutions:

So now that we know how Attention events can result in open transactions and blocking problems, what can we do to resolve this problem? Here are two solutions that I have successfully tried while working with customers -

  • Check after each transaction (or before starting a new transaction in the same session) to see if the transaction is complete by using the following statement:

IF @@TRANCOUNT > 0 ROLLBACK

  • Use SET XACT_ABORT ON for the connection, or in any stored procedures which begin transactions and are not cleaning up following an error. In the event of a run-time error, this setting will abort any open transactions and return control to the client. Note that T-SQL statements following the statement which caused the error will not be executed. It’s a good practice to set XACT_ABORT to on before starting any user transactions so that any batch terminating error (such as Attention events) would roll back the entire transaction. If you can’t modify the application easily to specify XACT_ABORT, you can try the USER_OPTIONS configuration setting in SQL Server to turn on XACT_ABORT on an entire instance of SQL Server. The below snapshot shows how to turn on XACT_ABORT on an instance of SQL Server –

12_XACT_ABORT

Important: This (or setting it at the connection level) does not guarantee desired behavior in case of an Attention event though, since application code can override the setting. The only completely reliable way would be to set XACT_ABORT to ON before every BEGIN TRAN

Comments

  • Anonymous
    May 21, 2012
    It’s good to come across a post every once in a while that isn’t the same outdated information.I’ve bookmarked your site and I’m including your RSS feeds to my RSS Reader. Perhaps readers of this post could find interesting the following post: www.agile-code.com/.../an-easy-way-to-avoid-sql-server-nested-transactions which explains how to avoid nested transactions and how to manage them.

  • Anonymous
    January 16, 2015
    There is one more. It appears that SqlBulkCopy.WriteToServer() method was fine on .NET 2 but somewhere between that and .NET 4 it now raises an Attention alert directly after all of the INSERT BULK statements have completed (whether you dispose or not, it happens immediately). What a pain. I haven't been able to find this documented anywhere else though one person had mentioned seeing similar on MSDN.

  • Anonymous
    March 25, 2015
    we have a sp that does update and insert but there isn't any Begin Transaction... commit/rollback used in the sp.  If  a user issues a cancel or connection is broken while the sp is running, would that cause open transaction and therefore blocking and the attention alert?   This SP could have up to few hundred executions in a 1~2 mins and constantly encountered blocking (idle blocker) during this heavy usage.

  • Anonymous
    June 02, 2015
    Thanks for the great article. this helped in my analysis of blocking events