Automated Deletion of Forgotten Transactions

  • Tutorial

Foreword


Quite often there are situations when a transaction in MS SQL Server is forgotten by those who launched it. The most common example of this is running a script in SSMS, where the transaction is explicitly opened with the begin tran instruction, then an error occurs, but commit or rollback tran does not occur, and the launch initiator safely departed for a long time from this request. As a result, over time, more and more fluctuations arise in terms of blocking for requests that request access to blocked resources (tables, server resources (RAM, CPU, input-output system).

This article will examine an example of automation of deleting forgotten transactions.

Decision


By a forgotten transaction, we mean that 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 by deletion actions of transactions selected from the first table for subsequent analysis
  2. We collect information (transactions and their sessions that have no requests, i.e., transactions that are started and forgotten for a certain time T)
  3. We update the table of current forgotten transactions from item 1 (if a forgotten transaction has an active request, then such a transaction becomes unforgettable and is deleted from the first table of item 1)
  4. We collect the sessions that need to be killed (a session has at least one transaction that has hit a certain number of times K as forgotten in the table from step 1 and the same number of times the session itself has no active queries)
  5. We archive what we are going to delete (detailed information about deleted sessions, connections and transactions)
  6. Delete selected sessions
  7. We delete the processed records, as well as those that cannot be deleted and they are too long in table 1

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

Transaction hit table 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 identifier
2) TransactionID - identifier of a forgotten transaction
3) CountTranNotRequest - the number of times the fact that the transaction was forgotten
4) CountSessionNotRequest - the number of times the fact that the session was without active of requests and contained a forgotten transaction
5) TransactionBeginTime - date and time of the beginning of the forgotten transaction
6) InsertUTCDate - date and time of creation of the record in UTC
7) UpdateUTCDate - date and time of the change in the record in UTC

Create a table for archiving by actions of deleting the transactions selected from the first table for afterwards of analysis:

Table for archiving remote sessions for 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 sys.dm_exec_sessions and sys.dm_exec_connections , and InsertUTCDate are the date and time the record was created in UTC.

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

Implementing the stored procedure [srv]. [AutoKillSessionTranBegin]
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, item 7 of the algorithm is implemented upon reaching one of the counters CountTranNotRequest or CountSessionNotRequest of 250.

Result


This article examined an example of implementing automated deletion 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, the DBMS performance is protected from such actions of initiators 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

Also popular now: