blocking issue

Sam 1,371 Reputation points
2024-03-19T11:57:52.3866667+00:00

Hi All,

Today came across a blocking issue from the application. A very simple INSERT ran and the spid is in "sleeping" "AWAITING_COMMAND" with an open transaction and lock on the table. After 15 hours, then comes a spid which "truncate that table" and it is waiting on Sch-M lock. There is no implicit_Tran on. but don't know why it has one open transaction. I seeing this behavior quite often and eventually had to kill that insert spid. isolation is Read- committed and RCSI is on for that particular db.

1

 

<additional_info>

  <text_size>-1</text_size>

  <language>us_english</language>

  <date_format>mdy</date_format>

  <date_first>7</date_first>

  <quoted_identifier>ON</quoted_identifier>

  <arithabort>OFF</arithabort>

  <ansi_null_dflt_on>ON</ansi_null_dflt_on>

  <ansi_defaults>OFF</ansi_defaults>

  <ansi_warnings>ON</ansi_warnings>

  <ansi_padding>ON</ansi_padding>

  <ansi_nulls>ON</ansi_nulls>

  <concat_null_yields_null>ON</concat_null_yields_null>

  <transaction_isolation_level>ReadCommitted</transaction_isolation_level>

  <lock_timeout>-1</lock_timeout>

  <deadlock_priority>0</deadlock_priority>

  <row_count>1</row_count>

  <command_type>AWAITING COMMAND</command_type>

  <sql_handle>0x01000b002c203603d0be7be26602000000000000</sql_handle>

  <statement_start_offset>-1</statement_start_offset>

  <statement_end_offset>-1</statement_end_offset>

  <host_process_id>25578</host_process_id>

  <group_id>2</group_id>

  <original_login_name>app_ors</original_login_name>

</additional_info>

My questions, by default any connection to sql server will be auto-commit mode , (correct me if I am wrong). I have tested this with a small insert on dept table and as and when the insert is done, its auto-commited by default and there is no open_tran. however, it is "sleeping" and "awaiting_command" as expected. I opened a new ssms window and tried to SELECT * FROM DEPT; I was able to see the new record which means the insert has been auto committed.

2

In case, of the application WHY it is keeping the transaction open in that spid for a simple insert.

Am I missing anything? what else can we collect or check to know the root cause of that open txn? is there a possibility of another spid has open that txn( i dont think so) !! . please give some ideas.

Regards,

Sam

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,601 questions
0 comments No comments
{count} votes

Accepted answer
  1. Olaf Helper 40,576 Reputation points
    2024-03-19T12:00:54.42+00:00

    In case, of the application WHY it is keeping the transaction open in that spid for a simple insert.

    I guess the application starts an explicit transaction, and as long as it don't commit, it's quite normal to see the process in SLEEPING state with an open transaction.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Zahid Butt 556 Reputation points
    2024-03-19T15:55:31.4933333+00:00

    Hi,

    Found related stuff somewhere, may help:

    In SQL Server, a session with the status of “sleeping” or “awaiting command” represents a client connection that is currently idle and not actively executing any queries against the database. Let’s delve into the details:

    Sleeping / Awaiting Command State:

    When a session is in this state, it means that the client connection exists, but there is no active query being processed by the SQL Server.
    
       The transition from running to sleeping states occurs as follows:
    
      
             **Connect Running**: The session is connected and actively executing a query.
    
            
                   **Connect Completed**: The query execution completes, and the session transitions to a waiting state.
    
                  
                         **Sleeping / Awaiting Command**: The session is idle, waiting for the next command from the client.
    
                        
                            Common scenarios for this state include:
    
                           
                                  A client connection that has not submitted any queries yet.
    
                                 
                                        A session holding locks due to an open transaction where the client did not issue a commit or rollback command.
    
                                       
                                              Situations where a procedure times out, leaving an open transaction.
    
                                             
                                                 To automatically roll back transactions in such cases, ensure that transaction abort is enabled.
    
                                                
                                                **Application Design Considerations**:
    
                                                
                                                    The “sleeping / awaiting command” state is typically an application design issue.
    
                                                   
                                                       Outside of physical connection problems, it arises due to how the application interacts with the database.
    
                                                      
                                                          For example, if a client cancels execution without explicitly committing or rolling back a transaction, the session remains in this state.
    
                                                         
                                                             Always review your application code and ensure proper handling of transactions and query timeouts.
    ```Remember, while “awaiting command” sessions may seem puzzling, they are part of the normal behavior of SQL Server and often reflect application design choices.
    
    0 comments No comments