Strange behavior of CURRENTTRANSACTIONTYPE operator

Alterant · 2008-04-15

Hello!

I faced with a strange behavior of CURRENTTRANSACTIONTYPE operator.

I have two sessions of NAV - the first is writing to the table from time to time and the second is reading. For the second session it is needed to read only commited data.
I have the "lock timeout" option on and timeout duration of 300 seconds.

When I use CURRENTTRANSACTIONTYPE(TRANSACTIONTYPE::Update) before reading the data in the second session and the table is locked by the first session after approx. 5 seconds I receive an error:

Your activity was deadlocked with another user modifying the xxx table.

Start again.

But if I run the same code again while the table is still blocked everything works fine - the second session waits until the lock is released or timeout is expired.

If I use
LOCKABLE
or
CURRENTTRANSACTIONTYPE(TRANSACTIONTYPE::Update)
LOCKTIMEOUT(FALSE);
everithing works fine - the system waits until lock is released without any error messages.

Does any one know is it a bug or it is by disign?

kine · 2008-04-15

The problem is, that it is not just Lock, but deadlock - it means that it depends on other tables you are reading during the transaction in the second session and first session. It seems that you are reading same tables but in different order...

Alterant · 2008-04-15

But why when I run the same code again the deadlock does not happen?

For the testing purposes I've created two forms with a button in each.
The first form has the following code in "on push" trigger:

myRecord.LOCKTABLE;
myRecord.FINDLAST;
EntryNo := myRecord."Entry No.";

Dlg.OPEN('#1####', i);

FOR i := 1 TO 30 DO BEGIN
  EntryNo := EntryNo + 1;
  myRecord.INIT;
  myRecord."Entry No." := EntryNo;
  myRecord."Document No." := 'TEST-' + FORMAT(i);
  myRecord."Posting Date" := TODAY;
  myRecord.INSERT;
  
  SLEEP(1000);

  Dlg.UPDATE;
END;

Dlg.CLOSE;

ERROR('');

The second form's code:

CURRENTTRANSACTIONTYPE(TRANSACTIONTYPE::Update);

IF myRecord.FIND('+') THEN
  MESSAGE('ok');

And it raises an error during the first execution. But if I push the button again (while the transaction in the first form is still running) it works OK - waits until transaction finishes.

The following code works fine (without error during first execution):

myRecord.LOCKTABLE;

IF myRecord.FIND('+') THEN
  MESSAGE('ok');

and this one works fine too:

CURRENTTRANSACTIONTYPE(TRANSACTIONTYPE::Update);
LOCKTIMEOUT(FALSE);

IF myRecord.FIND('+') THEN
  MESSAGE('ok');

kine · 2008-04-15

Best will be to catch the SQL communication in both cases and compare it...

Howdy, Stranger!

Quick Links

Categories

Strange behavior of CURRENTTRANSACTIONTYPE operator

Comments