Board index » delphi » deadlock update conflicts with concurrent update
vairisingh
Delphi Developer |
deadlock update conflicts with concurrent update2005-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 |