🏺 🖐️ 👯 忘れられたトランザクションの自動削除 🏳️ 💃🏻 🌊

まえがき

MS SQL Serverのトランザクションが、それを起動した人によって忘れられる場合がよくあります。これの最も一般的な例は、SSMSでスクリプトを実行することです。この場合、トランザクションはbegin tran命令で明示的に開かれ、エラーが発生しますが、コミットまたはロールバックtranは発生しません。その結果、時間の経過とともに、ブロックされたリソース（テーブル、サーバーリソース（RAM、CPU、I / Oシステム））へのアクセスを要求するリクエストのブロックに関して、ますます多くの変動が生じます。

この記事では、忘れられたトランザクションの削除を自動化する例を示します。

解決策

忘れられたトランザクションとは、十分に長い期間Tの間、アクティブな（実行された）要求がないアクティブな（実行中の）トランザクションを意味します。

まず、このようなトランザクションを削除するための一般的なアルゴリズムを示します。

現在の忘れられたトランザクションに関する情報を保存および分析するためのテーブル、および後続の分析のために最初のテーブルから選択されたトランザクションの削除アクションに従ってアーカイブするためのテーブルを作成します
情報（特定の時間Tの間に開始および忘れられたトランザクションなど、リクエストのないトランザクションとそのセッション）を収集します
現在の忘れられたトランザクションのテーブルをアイテム1から更新します（忘れられたトランザクションにアクティブなリクエストがある場合、そのようなトランザクションは忘れられなくなり、アイテム1の最初のテーブルから削除されます）
強制終了する必要のあるセッションを収集します（セッションには、アイテム1からテーブル内で特定の回数Kが忘れられた回数と、セッション自体にアクティブなクエリがない同じ回数のトランザクションが少なくとも1つあります）
削除するものをアーカイブします（削除されたセッション、接続、およびトランザクションに関する詳細情報）
選択したセッションを削除
処理済みのレコードと、削除できないレコードがテーブル1で長すぎます。

以下は、上記のアルゴリズムの実装例です。

次のように、現在忘れられているトランザクションに関する情報を保存および分析するためのテーブルを作成しましょう。

セッションで忘れられたトランザクションヒットテーブル

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

ここに：

1）SessionID-セッション識別子

2）TransactionID-忘れられたトランザクションの識別子

3）CountTranNotRequest-トランザクションが忘れられたという事実が記録された回数

4）CountSessionNotRequest-セッションにアクティブなリクエストがなく、忘れられたトランザクションが含まれていたという事実が記録された回数

5）TransactionBeginTime-忘れられたトランザクションの開始日時

6）InsertUTCDate-レコードがUTCで作成された日付と時刻

7）UpdateUTCDate-レコードがUTCで変更された日付と時刻

さらに分析するために、最初のテーブルから選択されたトランザクションを削除するアクションによって、アーカイブ用のテーブルを作成しましょう。

忘れられたトランザクションのリモートセッションをアーカイブするための表

 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

ここで、システムビューsys.dm_exec_sessionsおよびsys.dm_exec_connectionsのすべてのフィールド、およびInsertUTCDateは、レコードがUTCで作成された日時です。

さらに、残りの項目を実装するには、次のようにストアドプロシージャ[srv]。[AutoKillSessionTranBegin]を実装します。

ストアドプロシージャ[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

ここで、アルゴリズムのアイテム7は、CountTranNotRequestまたはCountSessionNotRequestの250のカウンターの1つに到達すると実装されます。

結果

この記事では、忘れられたトランザクションの自動削除を実装する例を検討しました。

この方法を使用すると、忘れられたトランザクションを削除するプロセスを自動化できます。その結果、そのようなトランザクションから発生するロックの変動を増やす試みが抑制されます。その結果、DBMSのパフォーマンスは、将来忘れられる可能性のあるトランザクションを起動するイニシエーターのアクションから保護されます。

ソース：

» 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

» 殺す

忘れられたトランザクションの自動削除

まえがき

解決策

結果

ソース：

More articles: