mysql连接池最大时间_连接池中的连接超过数据库端的最大空闲时间

一般来将，连接在DB端有最大空闲时间的限制。

如：mysql默认连接在8个小时无人使用(即有线程用Connection发送了SQL)，就会自动断开。

而此时，数据库连接池，却还不知道该连接已经被数据库断开了。这样问题就产生了，虽然从连接池中拿出了连接却在使用时，抛出连接已经被关闭的异常。

unit untDBPool;

{$I def.inc}

interface

uses

Classes, SyncObjs, SysUtils,

DateUtils, untDB, Windows, untGlobal;

type

TLoopThread=class(TThread)

private

CS: TCriticalSection;

protected

procedure Execute; override;

public

constructor Create; overload;

destructor Destroy; override;

end;

TDBPool = class

private

FCS: TCriticalSection;

FFreelist, FUsedList: TList;

FCount: integer;

FDatabaseParams: TDBParams;

public

constructor Create; overload;

destructor Destroy; override;

procedure Init;

function Lock: TfrmDB;

procedure Unlock(Value: TfrmDB);

function NewObj: TfrmDB;

property Count: integer read FCount default 0;

property DatabaseParams: TDBParams read FDatabaseParams

write FDatabaseParams;

end;

var

DBPool: TDBPool;

loopThread: TLoopThread;

implementation

uses untLog;

constructor TDBPool.Create;

begin

FFreelist := TList.Create;

FUsedList := TList.Create;

FCS := TCriticalSection.Create;

loopThread := TLoopThread.Create;

end;

destructor TDBPool.Destroy;

begin

while FFreelist.Count > 0 do

begin

TfrmDB(FFreelist[0]).con.Close;

TfrmDB(FFreelist[0]).Free;

FFreelist.Delete(0);

end;

while FUsedList.Count > 0 do

begin

TfrmDB(FFreelist[0]).con.Close;

TfrmDB(FUsedList[0]).Free;

FUsedList.Delete(0);

end;

FreeAndNil(FFreelist);

FreeAndNil(FUsedList);

FreeAndNil(FCS);

loopThread.Terminate;

loopThread.WaitFor;

FreeAndNil(loopThread);

inherited Destroy;

end;

procedure TDBPool.Init;

var

f: TfrmDB;

begin

while FFreelist.Count < poolParams.poolSize do

begin

f := NewObj;

if f <> nil then

begin

f.TimeStamp := GetTickCount;

f.ConnectDB;

FFreelist.Add(f);

end;

end;

end;

function TDBPool.Lock: TfrmDB;

begin

FCS.Enter;

try

if FFreelist.Count > 0 then

begin

Result := TfrmDB(FFreelist[0]);

if not Result.Connected then

Result.ConnectDB;

FFreelist.Delete(0);

FUsedList.Add(Result);

end

else

Result := nil;

if Result = nil then

begin

Result := NewObj;

if Result <> nil then

begin

Result.ConnectDB;

Result.Tag := 5;

end;

end;

finally

FCS.Leave;

end;

end;

function TDBPool.NewObj: TfrmDB;

begin

Result := nil;

if poolParams.maxValue = 0 then

begin

Result := TfrmDB.Create(nil);

Result.DatabaseParams := Self.DatabaseParams;

inc(FCount);

end

else if (poolParams.maxValue <> 0) and (FCount < poolParams.maxValue)

then

begin

Result := TfrmDB.Create(nil);

Result.DatabaseParams := Self.DatabaseParams;

inc(FCount);

end;

end;

procedure TDBPool.Unlock(Value: TfrmDB);

procedure _Free;

begin

Value.DisConnectDB;

FreeAndNil(Value);

Dec(FCount);

end;

begin

if Value = nil then

exit;

FCS.Enter;

try

if Value.Tag = 5 then

begin

_Free;

end

else

begin

if FFreelist.Count < poolParams.poolSize then

begin

FUsedList.Delete(FUsedList.IndexOf(Value));

FFreelist.Add(Value);

Value.TimeStamp:=GetTickCount;

end

else

_Free;

end;

finally

FCS.Leave;

end;

end;

{ TLoopThread }

constructor TLoopThread.Create;

begin

inherited Create(False);

Self.FreeOnTerminate := false;

CS := TCriticalSection.Create;

end;

destructor TLoopThread.Destroy;

begin

CS.Free;

inherited;

end;

procedure TLoopThread.Execute;

var

i: Integer;

begin

inherited;

while not Self.Terminated do

begin

Sleep(5*60*1000);

CS.Enter;

try

if not Assigned(DBPool) then Continue;

if DBPool.FFreelist.Count <=0 then Continue;

for i := DBPool.FFreelist.Count -1 downto 0 do

begin

if ((GetTickCount-TfrmDB(DBPool.FFreelist[i]).TimeStamp) div (60*1000))

>=Cardinal(poolParams.timeout) then

begin

TfrmDB(DBPool.FFreelist[i]).con.Close;

TfrmDB(DBPool.FFreelist[i]).Free;

DBPool.FFreelist.Delete(i);

end else Continue;

end;

finally

CS.Leave;

end;

end;

end;

end.