Lock conflict on no wait transaction

[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?

[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

[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

[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

[ViktorV]

Please let us know the value of the TUniTransaction.IsolationLevel property, in order to give you a clear answer.

[kneighbour]

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.

[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.