Page 1 of 1
Lock conflict on no wait transaction
Posted: Wed 16 Mar 2016 03:50
by kneighbour
I have been using UniDac and IBDac for years (first in D7 and now in XE7). Usually there are no problems, but for one project that I am working on there is a possibility that two different programs can update the same table at the same time. If this happens, I get the "Lock conflict on no wait transaction", just as you should.
I am wondering if there is some standard way of getting around this problem?
My projects are all based on Firebird 2.5. I do not use transactions explicitly.
My initial idea is that I should use transactions, and it it fails, I wait for a set time (ie 2 seconds or something), then try again. is this the right approach?
Re: Lock conflict on no wait transaction
Posted: Wed 16 Mar 2016 08:41
by FCS
Hello,
There is the UQ.Lock method.
You can use it to check if record is locked by another query.
Code: Select all
try
UQ.SQL.Add('Select * from aTable WHERE id=.....'); //condition to select a record
UQ.Open;
try
UQ.Connection.StartTransaction;
UQ.Lock;
UQ.Edit;
UQ.FieldByName('aField').As....:= aValue;
try
UQ.Post;
UQ.Connection.Commit;
except
UQ.Cancel;
UQ,Connection.RollBack;
MsgBox('Problem with update !');
end;
except
UQ.RollBack;
MsgBox('Record is edited by the other user !');
end;
You must read about transactions in FB. As I had read on this forum, the connection to FB makes automatically a transaction.
It may by necessary to set the lock type to the pessimistic mode.
You must do this in all your programs.
Maybe this will be helpful for you:
http://forums.devart.com/viewtopic.php?f=28&t=27371
Regards
Michal
Re: Lock conflict on no wait transaction
Posted: Thu 17 Mar 2016 21:20
by kneighbour
Well, I already get an error message, so not sure if getting a more 'controlled' error message is that much of a benefit.
In this particular case we are adding journal entries into a totals table - so I don't mind if the other user is updating the data. I just need to wait and retry in a few seconds - hopefully when the other user has finished and moved on. I guess your approach (ie using a transaction) would serve as the trigger to start the wait loop and try again.
Will give this method a try
Re: Lock conflict on no wait transaction
Posted: Fri 18 Mar 2016 08:20
by FCS
Hello,
You may use the simple repeat loop:
Code: Select all
function Update_a_Table:boolean;
var
ii:byte;
begin
Result:=false;
ii:=0;
repeat
inc(ii);
try
<Update table>
Result:=true;
BREAK; //or set ii:=20;
except
sleep(50);
end;
until ii=20;
end;
Regards
Michal
Re: Lock conflict on no wait transaction
Posted: Fri 18 Mar 2016 12:40
by ViktorV
Please let us know the value of the TUniTransaction.IsolationLevel property, in order to give you a clear answer.
Re: Lock conflict on no wait transaction
Posted: Tue 22 Mar 2016 22:43
by kneighbour
ViktorV wrote:Please let us know the value of the TUniTransaction.IsolationLevel property, in order to give you a clear answer.
I don't use explicit transactions - just the default ones that are built into TUniQuery/TUniConnection. So whatever the default is.
Re: Lock conflict on no wait transaction
Posted: Mon 12 Sep 2016 09:00
by ViktorV
Please compose a small sample reproducing the described behavior and send it to viktorv*devart*com, including scripts for creating database objects, in order for us to be able to give you a more detailed answer.