catch and understand transaction rollback



  • Because of the data consistency issue of the application (sporadic data loss) I have to perform a deep dive into transaction handling. I've created an extended event session:

    CREATE EVENT SESSION [system_rollback_error] ON SERVER 
    ADD EVENT sqlserver.error_reported(
        ACTION(
            package0.event_sequence,
            sqlserver.client_app_name,
            sqlserver.client_hostname,
            sqlserver.database_id,
            sqlserver.database_name,
            sqlserver.server_principal_name,
            sqlserver.session_id,
            sqlserver.session_nt_username,
            sqlserver.sql_text,
            sqlserver.transaction_id,
            sqlserver.transaction_sequence
        )
        WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4))
            AND [severity]>(10))),
    ADD EVENT sqlserver.sql_transaction(
        ACTION(
            package0.event_sequence,
            sqlserver.client_app_name,
            sqlserver.client_hostname,
            sqlserver.database_id,
            sqlserver.database_name,
            sqlserver.server_principal_name,
            sqlserver.session_id,
            sqlserver.session_nt_username,
            sqlserver.sql_text,
            sqlserver.transaction_id,
            sqlserver.transaction_sequence
        )
        WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4))
            AND [transaction_state]='Rollback'
            AND [transaction_type]='System'))
    

    ADD TARGET package0.event_file(
    SET filename=N'system_rollback_error',
    max_file_size=(100)
    )
    WITH (
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=OFF
    )
    GO

    Now I get a lot of DUI statements shown in this ext-event session, all of them are in autocommit mode but there is no one error message. No key violation, no wrong syntax or something giving me an idea why transaction was rolled back. I've checked transaction log using

    select * from sys.fn_dblog(NULL,NULL) where operation in ('LOP_ABORT_XACT') 
    

    but there is also nothing.

    Do you have an idea, why an autocommit transaction has a transaction_state "Rollback" with no errors? (it's not a ROLLBACK at the end of the statement and that is also not a broken session in implicit_transaction mode, because this is a transaction_type "user" and not "system").



  • Do you have an idea, why an autocommit transaction has a transaction_state "Rollback" with no errors?

    The client can abort a running batch by sending an " https://docs.microsoft.com/en-us/openspecs/windows_protocols/ms-tds/dc28579f-49b1-4a78-9c5f-63fbda002d2e#:%7E:text=The%20client%20can%20interrupt%20and%20cancel%20the%20current,MUST%20read%20until%20it%20receives%20an%20Attention%20acknowledgment. ". This is how you cancel a running query in SSMS, and this is how timeouts work.

    You can add the sqlserver.attention event to your trace, eg

    CREATE EVENT SESSION [attentions] ON SERVER 
    ADD EVENT sqlserver.attention(
        ACTION(sqlserver.sql_text))
    

Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2