releasethread.pas

多线程管理发现处理数据库死锁问题

unit ReleaseThread;

interface

uses
  Classes,ComCtrls,ADODB,SysUtils,ExtCtrls,comobj,ActiveX,SyncObjs,Windows,Valedit,Helper;
type
  TBlkInfo=class
    SPID:integer;
    Blk:integer;
    BeginFindTime:TDateTime;
    public
    constructor Create();
  end;

type
  ReleaseThreads = class(TThread)
  private
    FPaint:TRichEdit;
    FExit:^TBRun;
    FSqlConString:string;
    FList:TList;
    BlkInfo:TBlkInfo;
    con:TADOConnection;

    com:TADOCommand;
    ds:TADODataSet;
    { Private declarations }
  private
    procedure ReleaseDeadLock();
    function FindNode(const i:integer):integer;
  public
    procedure Execute; override;

    constructor Create(Paint: TRichEdit;var bExit:TBRun;SqlConString: string);
  end;

implementation
{ TBlkInfo }

constructor TBlkInfo.Create;
begin
   inherited Create();
end;
{ Important: Methods and properties of objects in VCL or CLX can only be used
  in a method called using Synchronize, for example,

      Synchronize(UpdateCaption);

  and UpdateCaption could look like,

    procedure ReleaseThread.UpdateCaption;
    begin
      Form1.Caption := 'Updated in a thread';
    end; }

{ ReleaseThread }

constructor ReleaseThreads.Create(Paint: TRichEdit;var bExit:TBRun;SqlConString: string);
begin
  FPaint:=Paint;
  FExit:=@bExit;
  FList:=TList.Create;

  FSqlConString:=SqlConString;

  CoInitialize(nil);
  con:=TADOConnection.Create(nil);
  con.ConnectionString:= FSqlConString;
  con.Open;

  com:=TADOCommand.Create(nil);
  com.Connection:=con;

  BlkInfo:=TBlkInfo.Create();
  ds:=TADODataSet.Create(nil);
  inherited Create(false);
end;

procedure ReleaseThreads.Execute;
begin
  { Place thread code here }
  FPaint.Lines.Add('系统开始工作');
  while FExit.FRun do
  begin
    Synchronize(ReleaseDeadLock);
    sleep(60000*3);
  end;
  FPaint.Lines.Add('系统停止对数据库死锁问题的侦察工作');
end;

function ReleaseThreads.FindNode(const i:integer): integer;
var
  ii:integer;
begin
  for ii:=0 to FList.Count -1  do
  begin
    if TBlkInfo(FList.Items[ii]).SPID= i then
    begin
      result :=ii;
      exit;
    end;
  end;
  result:=-1;
end;

procedure ReleaseThreads.ReleaseDeadLock;
var
  i,ii:integer;
begin
  //释放被长期阻塞的线程
  try
    com.CommandText:='select convert(char(5),blocked) blk, last_batch,spid from  master.dbo.sysprocesses where blocked <> 0 order by Last_Batch';
    ds.Recordset:=com.Execute;
    while not ds.Eof do
    begin
      if ds['BLK']<> 0 then
      begin
        BlkInfo:=TBlkInfo.Create;
        BlkInfo.SPID := ds['spid'];
        BLKInfo.Blk :=ds['BLK'];
        BLKInfo.BeginFindTime := ds['Last_batch'];
        i:= FindNode(BlkInfo.SPID);

        if i>-1 then
        begin
          FPaint.Lines.Add(('SPID为'+ inttostr(ds['BLK'])+'数据库进程被停止'));
          //'上次执行远程存储过程调用或 EXECUTE 语句的时间为'+DateTimeTOStr(ds['Last_batch'])+
          com.CommandText:='Kill ' + inttostr(ds['BLK']) ;
          com.Execute;
        end
        else
          Flist.Add(BLKInfo);

        i:=FindNode(ds['BLK']);
        if i>-1 then
        begin
          FList.Delete(i) ;
        end;
        //FPaint.Lines.Add((DateTimeTOStr(ds['Last_batch']) + '   ' + inttostr(ds['spid']) + '   BLK   ' + inttostr(ds['BLK'])))
      end;
      ds.Next;
    end;
  except

  end;
end;
end.