deadlock update conflicts with concurrent update


2005-09-21 07:56:19 AM
delphi278
Hi all,
I have a problem which has been vexing for quite some time. I'm
using Firebird 1.5 (I know this is interbase group, but still would
apreciate if you could help me) with IBX 7.08. I have multiple threads
running which update the same table and sometimes same record in the
table. To make it simpler and to finally figure out where the deadlock
was occuring I have created the following simple program
Unit1:
-------------------------------
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls,
Forms,
Dialogs, StdCtrls;
type
TForm1 = class(TForm)
ListBox1: TListBox;
Edit1: TEdit;
Label1: TLabel;
Button1: TButton;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
uses Unit2;
{$R *.dfm}
procedure TForm1.Button1Click(Sender: TObject);
var
I, count: Integer;
begin
count := StrToInt(Edit1.Text);
for I := 0 to Count - 1 do
begin
Test.Create(false);
end;
end;
end.
Unit2
---------------------
unit Unit2;
interface
uses
Classes, IBDatabase, IBQuery, sysutils, windows, syncobjs;
type
Test = class(TThread)
private
protected
procedure Execute; override;
end;
implementation
procedure Test.Execute;
var
IBDatabase: TIBDatabase;
IBQuery: TIBQuery;
IBTransaction: TIBTransaction;
i: integer;
begin
IBDatabase:= TIBDatabase.Create(nil);
try
IBDatabase.SQLDialect:= 3;
IBDatabase.Params.Add('user_name=sysdba');
IBDatabase.Params.Add('password=masterkey');
IBDatabase.DatabaseName:= '127.0.0.1:C:\Test\Data.GDB';
IBDatabase.LoginPrompt:= false;
IBDatabase.Connected:= True;
try
IBTransaction:= TIBTransaction.Create(nil);
try
IBTransaction.DefaultDatabase:= IBDatabase;
IBQuery:= TIBQuery.Create(nil);
try
IBQuery.Database:= IBDatabase;
for i:= 1 to 5 do
begin
IBTransaction.StartTransaction;
try
IBQuery.SQL.Text:= 'UPDATE TESTTABLE set "IntData" =
"IntData" + 1 ' +
'where "pid" = 10';
IBQuery.ExecSQL;
IBTransaction.Commit;
except
IBTransaction.Rollback;
raise;
end;
end;
end;
finally
FreeAndNil(IBQuery);
end;
finally
FreeAndNil(IBTransaction);
end;
finally
IBDatabase.Connected:= false;
end;
finally
FreeAndNil(IBDatabase);
end;
end;
end.
Fairly simple program, just updates the same field in multiple threads,
which is similar to what my main program does. My main program does not
always update the same record at the same time, but there is still a
chance, and that is what I want to eliminate Now even though the update
query is executed and commited in a very short interval, it still
generates the deadlock. I have tried putting the this update code into
stored proc also so that it can be run much faster and to reduce the
chance of deadlock but the deadlock still occurs. How can I get the
transaction to wait for the deadlock to be removed and try again. Any
help would be greatly appreciated.
Vairi