Geekly Articles each Day
📜 ⬆️ ⬇️

Automate the removal of forgotten transactions

Foreword


Quite often there are situations when a transaction in MS SQL Server is forgotten by those who launched it. The most frequent example of this is running a script in SSMS, where a transaction is opened with a begin begin instruction, then an error occurs, but a commit or rollback tran does not occur, and the initiator of the launch has successfully left the query for a long time. As a result, over time, more and more fluctuations occur in terms of blocking requests that request access to locked resources (tables, server resources (RAM, CPU, I / O system).

This article will discuss an example of automating the removal of forgotten transactions.

Decision


A forgotten transaction will be understood as an active (running) transaction in which for some sufficiently large period of time T there are no active (executed) requests.

First, we give a general algorithm for deleting such transactions:
')
  1. We create a table for storing and analyzing information on current forgotten transactions, as well as a table for archiving the deletion of selected transactions from the first table for subsequent parsing.
  2. We collect information (transactions and their sessions that have no requests, that is, transactions started and forgotten within a certain time T)
  3. Update the table of current forgotten transactions from item 1 (if a forgotten transaction has an active query, then such a transaction becomes unforgettable and is deleted from the first table of item 1)
  4. We collect those sessions that need to be killed (the session has at least one transaction that hit a certain number of times K as forgotten in the table of claim 1 and the same time the session itself has no active requests)
  5. We archive what we are going to delete (detailed information about the sessions to be deleted, connections and transactions)
  6. Delete selected sessions
  7. Delete the processed records, as well as those that can not be deleted and they are too long in table 1

Next, we give an example of the implementation of the above algorithm.
Create a table for storing and analyzing information on current forgotten transactions as follows:

Table of hit transactions as forgotten with their sessions
USE [_] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [srv].[SessionTran]( [SessionID] [int] NOT NULL, [TransactionID] [bigint] NOT NULL, [CountTranNotRequest] [tinyint] NOT NULL, [CountSessionNotRequest] [tinyint] NOT NULL, [TransactionBeginTime] [datetime] NOT NULL, [InsertUTCDate] [datetime] NOT NULL, [UpdateUTCDate] [datetime] NOT NULL, CONSTRAINT [PK_SessionTran] PRIMARY KEY CLUSTERED ( [SessionID] ASC, [TransactionID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [srv].[SessionTran] ADD CONSTRAINT [DF_SessionTran_Count] DEFAULT ((0)) FOR [CountTranNotRequest] GO ALTER TABLE [srv].[SessionTran] ADD CONSTRAINT [DF_SessionTran_CountSessionNotRequest] DEFAULT ((0)) FOR [CountSessionNotRequest] GO ALTER TABLE [srv].[SessionTran] ADD CONSTRAINT [DF_SessionTran_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate] GO ALTER TABLE [srv].[SessionTran] ADD CONSTRAINT [DF_SessionTran_UpdateUTCDate] DEFAULT (getutcdate()) FOR [UpdateUTCDate] GO

Here:

1) SessionID - session ID
2) TransactionID - identifier of the forgotten transaction
3) CountTranNotRequest - the number of times the recorded fact that the transaction was forgotten
4) CountSessionNotRequest - the number of times it was recorded that the session was without active requests and contained a forgotten transaction
5) TransactionBeginTime - the date and time of the beginning of the forgotten transaction
6) InsertUTCDate - the date and time the record was created in UTC
7) UpdateUTCDate - the date and time of the change in the UTC record

Let's create a table for archiving by the actions of deleting the transactions selected from the first table for subsequent parsing:

Table for archiving remote sessions by forgotten transactions
 USE [_] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [srv].[KillSession]( [ID] [int] IDENTITY(1,1) NOT NULL, [session_id] [smallint] NOT NULL, [transaction_id] [bigint] NOT NULL, [login_time] [datetime] NOT NULL, [host_name] [nvarchar](128) NULL, [program_name] [nvarchar](128) NULL, [host_process_id] [int] NULL, [client_version] [int] NULL, [client_interface_name] [nvarchar](32) NULL, [security_id] [varbinary](85) NOT NULL, [login_name] [nvarchar](128) NOT NULL, [nt_domain] [nvarchar](128) NULL, [nt_user_name] [nvarchar](128) NULL, [status] [nvarchar](30) NOT NULL, [context_info] [varbinary](128) NULL, [cpu_time] [int] NOT NULL, [memory_usage] [int] NOT NULL, [total_scheduled_time] [int] NOT NULL, [total_elapsed_time] [int] NOT NULL, [endpoint_id] [int] NOT NULL, [last_request_start_time] [datetime] NOT NULL, [last_request_end_time] [datetime] NULL, [reads] [bigint] NOT NULL, [writes] [bigint] NOT NULL, [logical_reads] [bigint] NOT NULL, [is_user_process] [bit] NOT NULL, [text_size] [int] NOT NULL, [language] [nvarchar](128) NULL, [date_format] [nvarchar](3) NULL, [date_first] [smallint] NOT NULL, [quoted_identifier] [bit] NOT NULL, [arithabort] [bit] NOT NULL, [ansi_null_dflt_on] [bit] NOT NULL, [ansi_defaults] [bit] NOT NULL, [ansi_warnings] [bit] NOT NULL, [ansi_padding] [bit] NOT NULL, [ansi_nulls] [bit] NOT NULL, [concat_null_yields_null] [bit] NOT NULL, [transaction_isolation_level] [smallint] NOT NULL, [lock_timeout] [int] NOT NULL, [deadlock_priority] [int] NOT NULL, [row_count] [bigint] NOT NULL, [prev_error] [int] NOT NULL, [original_security_id] [varbinary](85) NOT NULL, [original_login_name] [nvarchar](128) NOT NULL, [last_successful_logon] [datetime] NULL, [last_unsuccessful_logon] [datetime] NULL, [unsuccessful_logons] [bigint] NULL, [group_id] [int] NOT NULL, [database_id] [smallint] NOT NULL, [authenticating_database_id] [int] NULL, [open_transaction_count] [int] NOT NULL, [most_recent_session_id] [int] NULL, [connect_time] [datetime] NULL, [net_transport] [nvarchar](40) NULL, [protocol_type] [nvarchar](40) NULL, [protocol_version] [int] NULL, [encrypt_option] [nvarchar](40) NULL, [auth_scheme] [nvarchar](40) NULL, [node_affinity] [smallint] NULL, [num_reads] [int] NULL, [num_writes] [int] NULL, [last_read] [datetime] NULL, [last_write] [datetime] NULL, [net_packet_size] [int] NULL, [client_net_address] [nvarchar](48) NULL, [client_tcp_port] [int] NULL, [local_net_address] [nvarchar](48) NULL, [local_tcp_port] [int] NULL, [connection_id] [uniqueidentifier] NULL, [parent_connection_id] [uniqueidentifier] NULL, [most_recent_sql_handle] [varbinary](64) NULL, [LastTSQL] [nvarchar](max) NULL, [transaction_begin_time] [datetime] NOT NULL, [CountTranNotRequest] [tinyint] NOT NULL, [CountSessionNotRequest] [tinyint] NOT NULL, [InsertUTCDate] [datetime] NOT NULL, CONSTRAINT [PK_KillSession] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [srv].[KillSession] ADD CONSTRAINT [DF_KillSession_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate] GO

Here, all the fields from the system views are sys.dm_exec_sessions and sys.dm_exec_connections , and InsertUTCDate is the date and time the entry was created in UTC.

Next, to implement the remaining items, we implement the [srv]. [AutoKillSessionTranBegin] stored procedure as follows:

Implementing the [srv]. [AutoKillSessionTranBegin] Stored Procedure
 USE [_] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [srv].[AutoKillSessionTranBegin] @minuteOld int, --   (T ) @countIsNotRequests int ---    (K) AS BEGIN SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; declare @tbl table ( SessionID int, TransactionID bigint, IsSessionNotRequest bit, TransactionBeginTime datetime ); --  (   ,    ,      ) insert into @tbl ( SessionID, TransactionID, IsSessionNotRequest, TransactionBeginTime ) select t.[session_id] as SessionID , t.[transaction_id] as TransactionID , case when exists(select top(1) 1 from sys.dm_exec_requests as r where r.[session_id]=t.[session_id]) then 0 else 1 end as IsSessionNotRequest , (select top(1) ta.[transaction_begin_time] from sys.dm_tran_active_transactions as ta where ta.[transaction_id]=t.[transaction_id]) as TransactionBeginTime from sys.dm_tran_session_transactions as t where t.[is_user_transaction]=1 and not exists(select top(1) 1 from sys.dm_exec_requests as r where r.[transaction_id]=t.[transaction_id]); --   ,      ;merge srv.SessionTran as st using @tbl as t on st.[SessionID]=t.[SessionID] and st.[TransactionID]=t.[TransactionID] when matched then update set [UpdateUTCDate] = getUTCDate() , [CountTranNotRequest] = st.[CountTranNotRequest]+1 , [CountSessionNotRequest] = case when (t.[IsSessionNotRequest]=1) then (st.[CountSessionNotRequest]+1) else 0 end , [TransactionBeginTime] = t.[TransactionBeginTime] when not matched by target then insert ( [SessionID] ,[TransactionID] ,[TransactionBeginTime] ) values ( t.[SessionID] ,t.[TransactionID] ,t.[TransactionBeginTime] ) when not matched by source then delete; --    (  ) declare @kills table ( SessionID int ); --    declare @kills_copy table ( SessionID int, TransactionID bigint, CountTranNotRequest tinyint, CountSessionNotRequest tinyint, TransactionBeginTime datetime ) --  ,    --      ,   @countIsNotRequests                insert into @kills_copy ( SessionID, TransactionID, CountTranNotRequest, CountSessionNotRequest, TransactionBeginTime ) select SessionID, TransactionID, CountTranNotRequest, CountSessionNotRequest, TransactionBeginTime from srv.SessionTran where [CountTranNotRequest]>=@countIsNotRequests and [CountSessionNotRequest]>=@countIsNotRequests and [TransactionBeginTime]<=DateAdd(minute,-@minuteOld,GetDate()); --    (    ,   ) INSERT INTO [srv].[KillSession] ([session_id] ,[transaction_id] ,[login_time] ,[host_name] ,[program_name] ,[host_process_id] ,[client_version] ,[client_interface_name] ,[security_id] ,[login_name] ,[nt_domain] ,[nt_user_name] ,[status] ,[context_info] ,[cpu_time] ,[memory_usage] ,[total_scheduled_time] ,[total_elapsed_time] ,[endpoint_id] ,[last_request_start_time] ,[last_request_end_time] ,[reads] ,[writes] ,[logical_reads] ,[is_user_process] ,[text_size] ,[language] ,[date_format] ,[date_first] ,[quoted_identifier] ,[arithabort] ,[ansi_null_dflt_on] ,[ansi_defaults] ,[ansi_warnings] ,[ansi_padding] ,[ansi_nulls] ,[concat_null_yields_null] ,[transaction_isolation_level] ,[lock_timeout] ,[deadlock_priority] ,[row_count] ,[prev_error] ,[original_security_id] ,[original_login_name] ,[last_successful_logon] ,[last_unsuccessful_logon] ,[unsuccessful_logons] ,[group_id] ,[database_id] ,[authenticating_database_id] ,[open_transaction_count] ,[most_recent_session_id] ,[connect_time] ,[net_transport] ,[protocol_type] ,[protocol_version] ,[encrypt_option] ,[auth_scheme] ,[node_affinity] ,[num_reads] ,[num_writes] ,[last_read] ,[last_write] ,[net_packet_size] ,[client_net_address] ,[client_tcp_port] ,[local_net_address] ,[local_tcp_port] ,[connection_id] ,[parent_connection_id] ,[most_recent_sql_handle] ,[LastTSQL] ,[transaction_begin_time] ,[CountTranNotRequest] ,[CountSessionNotRequest]) select ES.[session_id] ,kc.[TransactionID] ,ES.[login_time] ,ES.[host_name] ,ES.[program_name] ,ES.[host_process_id] ,ES.[client_version] ,ES.[client_interface_name] ,ES.[security_id] ,ES.[login_name] ,ES.[nt_domain] ,ES.[nt_user_name] ,ES.[status] ,ES.[context_info] ,ES.[cpu_time] ,ES.[memory_usage] ,ES.[total_scheduled_time] ,ES.[total_elapsed_time] ,ES.[endpoint_id] ,ES.[last_request_start_time] ,ES.[last_request_end_time] ,ES.[reads] ,ES.[writes] ,ES.[logical_reads] ,ES.[is_user_process] ,ES.[text_size] ,ES.[language] ,ES.[date_format] ,ES.[date_first] ,ES.[quoted_identifier] ,ES.[arithabort] ,ES.[ansi_null_dflt_on] ,ES.[ansi_defaults] ,ES.[ansi_warnings] ,ES.[ansi_padding] ,ES.[ansi_nulls] ,ES.[concat_null_yields_null] ,ES.[transaction_isolation_level] ,ES.[lock_timeout] ,ES.[deadlock_priority] ,ES.[row_count] ,ES.[prev_error] ,ES.[original_security_id] ,ES.[original_login_name] ,ES.[last_successful_logon] ,ES.[last_unsuccessful_logon] ,ES.[unsuccessful_logons] ,ES.[group_id] ,ES.[database_id] ,ES.[authenticating_database_id] ,ES.[open_transaction_count] ,EC.[most_recent_session_id] ,EC.[connect_time] ,EC.[net_transport] ,EC.[protocol_type] ,EC.[protocol_version] ,EC.[encrypt_option] ,EC.[auth_scheme] ,EC.[node_affinity] ,EC.[num_reads] ,EC.[num_writes] ,EC.[last_read] ,EC.[last_write] ,EC.[net_packet_size] ,EC.[client_net_address] ,EC.[client_tcp_port] ,EC.[local_net_address] ,EC.[local_tcp_port] ,EC.[connection_id] ,EC.[parent_connection_id] ,EC.[most_recent_sql_handle] ,(select top(1) text from sys.dm_exec_sql_text(EC.[most_recent_sql_handle])) as [LastTSQL] ,kc.[TransactionBeginTime] ,kc.[CountTranNotRequest] ,kc.[CountSessionNotRequest] from @kills_copy as kc inner join sys.dm_exec_sessions ES with(readuncommitted) on kc.[SessionID]=ES.[session_id] inner join sys.dm_exec_connections EC with(readuncommitted) on EC.session_id = ES.session_id; --  insert into @kills ( SessionID ) select [SessionID] from @kills_copy group by [SessionID]; declare @SessionID int; --    while(exists(select top(1) 1 from @kills)) begin select top(1) @SessionID=[SessionID] from @kills; BEGIN TRY EXEC sp_executesql N'kill @SessionID', N'@SessionID INT', @SessionID; END TRY BEGIN CATCH END CATCH delete from @kills where [SessionID]=@SessionID; end select st.[SessionID] ,st.[TransactionID] into #tbl from srv.SessionTran as st where st.[CountTranNotRequest]>=250 or st.[CountSessionNotRequest]>=250 or exists(select top(1) 1 from @kills_copy kc where kc.[SessionID]=st.[SessionID]); --  ,   ,             delete from st from #tbl as t inner join srv.SessionTran as st on t.[SessionID] =st.[SessionID] and t.[TransactionID]=st.[TransactionID]; drop table #tbl; END GO

Here, step 7 of the algorithm is implemented when one of the CountTranNotRequest or CountSessionNotRequest counters reaches 250.

Result


This article was considered an example of the implementation of automated removal of forgotten transactions.

This method allows you to automate the process of deleting forgotten transactions, the result of which is the suppression of attempts to increase fluctuations in terms of locks emanating from such transactions. As a result, DBMS performance protection is achieved against such initiators' actions to launch transactions that may become forgotten in the future.

Sources:


» Sys.dm_exec_requests
» Sys.dm_tran_active_transactions
» Sys.dm_tran_session_transactions
» Sys.dm_exec_sql_text
» Sys.dm_exec_sessions
» Sys.dm_exec_connections
KILL

Source: https://habr.com/ru/post/349798/

More articles:


All Articles