Error message "Lock conflict on no wait transaction deadlock."

[Patrick Beckers]

I'm struggling with a problem for some time now, and I don't know how to
solve it. Perhaps you experts can help me out.

I have a function (listed below) which generates a new number, depending on
the type of document (Doc).
We use Interbase 7.5 as the database-server and mixed clients: Delphi7/Win32
(local and via Citrix) and Delphi7/Intraweb (local and via Internet)
The problem is that in a multi-user environement with 50 users or so, this
functions sometimes generates a "Lock conflict on no wait transaction
deadlock." on the client machine. Not always, but enough the become really
annoying.

We used this function for several years, and it always ran smoothly (on
Interbase 4, 6, 7, 7.5 and Delphi 3, 5, 7).
I'm not sure it started right after we upgraded to Interbase 7 or 7.5,
because users don't always report such errors (except when it occurs several
times a day).

Basically, DOCUMENTNR is a small table containing only 13 records. Here's
the layout:
-------------------
SHOW TABLE documentnr
DOC SMALLINT Nullable
NUMBER INTEGER Nullable
-------------------
SHOW INDEX documentnr
DOCUMENTNR_DOC INDEX ON DOCUMENTNR(DOC)
-------------------

In IBconfig75, I've tried changing some parameters - without succes.
These are the current settings:
CPU_AFFINITY 3
ENABLE_HYPERTHREADING 1
MAX_THREADS 10000
V4_LOCK_MEM_SIZE 524250
DATABASE_CACHE_PAGES 20000
Over time, I've tried changing values for some of these parameters. Also,
default settings don't solve it.

Btw, the database pagesize is 8192



All Win32 clients connect with the IB server through a single IBDatabase1
and IBTransaction1, located on the datamodule DM.
The params of IBTransaction1 are read_committed, rec_version, nowait


This is the function. It reads the current number according to Doc (type of
document) from the table and increments it with 1. Then, it updates the
table with the new number.

function NewDocumentNumber(Doc:Integer):Integer;
var Q : TIBQuery;
Num : integer;
begin
Result := 0;
Q := TIBQuery.create( nil);
try
Q.Database := DM.IBDatabase1;
Q.Transaction := DM.IBTransaction1;
Q.UniDirectional := true;

Q.SQL.Clear;
Q.SQL.Add( 'select number from DOCUMENTNR where doc = '+IntToStr(Doc));
Q.Open;
if not Q.EOF then
Num := Q.FieldByName('number').AsInteger + 1
else
Num := 1;
Q.close;

Q.SQL.Clear;
Q.SQL.Add( 'update DOCUMENTNR set number = '+IntToStr(Num)+
' where doc = '+IntToStr(Doc));
Q.ExecSQL;
Q.close;

Result := Num;
finally
Q.Free;
end;
end;

[Bill Todd]

The error message that you are getting occurs when two users try to
update the same row at the same time. Specifically the error occurs
when user A starts a transaction and updates the row and user B starts
a transaction and tries to update the row before user A's transaction
has committed. This is normal behavior and, as you can see, is more
likely to occur as the number of user increases.

The best solution is to use one or more generators to get the numbers.
If you want to continue using the function you posted you need to trap
the exception, wait a short period of time to give the transaction that
has updated the row a chance to commit, then try the update again.

[Far]

Just a question... If I were to use a stored procedure to read an index
number, add one to it and return the origional number would that be doing
basically the same as a gernerator. Does a generator use and internal
transaction so there can never be a conflict?

Post by Bill Todd
The error message that you are getting occurs when two users try to
update the same row at the same time. Specifically the error occurs
when user A starts a transaction and updates the row and user B starts
a transaction and tries to update the row before user A's transaction
has committed. This is normal behavior and, as you can see, is more
likely to occur as the number of user increases.
The best solution is to use one or more generators to get the numbers.
If you want to continue using the function you posted you need to trap
the exception, wait a short period of time to give the transaction that
has updated the row a chance to commit, then try the update again.
--
Bill Todd (TeamB)

[Bill Todd]

Post by Far
Just a question... If I were to use a stored procedure to read an
index number, add one to it and return the origional number would
that be doing basically the same as a gernerator. Does a generator
use and internal transaction so there can never be a conflict?

A generator operates outside the context of a transaction. If you call
the gen_id function to get the next value from a generator then
rollback your transaction the generator value does not rollback because
between the time you got your value and the time your transaction
committed ten other users could have retrived values from the
generator. Because generators are outside the transaction context there
is no need to lock the generator until the transaction commits and that
eliminates the problem that you are having.

So, to answer your first question, if your stored procedure is going to
get the value from a table, increment it, then update the table just as
your current function does then that would not mimic a generator
because you would be operating within the context of a transaction and
have the same problem you are having now. Hope this helps.

[Patrick Beckers]

Just out of curiosity, is it possible that a user starting this function
(part 1 reads a record, part 2 updates the same record) actually blocks
himself? Sometimes the error occurs with few people working, or one has to
try 5 times before it actually succeeds (this is especially true for the
Citrix users).

Post by Bill Todd
The error message that you are getting occurs when two users try to
update the same row at the same time. Specifically the error occurs
when user A starts a transaction and updates the row and user B starts
a transaction and tries to update the row before user A's transaction
has committed. This is normal behavior and, as you can see, is more
likely to occur as the number of user increases.
The best solution is to use one or more generators to get the numbers.
If you want to continue using the function you posted you need to trap
the exception, wait a short period of time to give the transaction that
has updated the row a chance to commit, then try the update again.
--
Bill Todd (TeamB)

[Jeff Overcash (TeamB)]

Post by Patrick Beckers
Just out of curiosity, is it possible that a user starting this function
(part 1 reads a record, part 2 updates the same record) actually blocks
himself? Sometimes the error occurs with few people working, or one has to
try 5 times before it actually succeeds (this is especially true for the
Citrix users).

You can not block yourself in the same transaction. You would have to setup 2
separate transactions and do half in one and try the other half in the other.

[Bill Todd]

As Jeff said, locks you place only affect other transactions. A more
likely cause is that the transaction remains active for some period of
time after the table is updated. The code you posted does not show the
transaction control statements so there is no way to guess when the
transaction that updates the table ends. You might consider using a
second transaction for the function that gets the unique number. That
way you can start the transaction, update then commit immediately.

[ssamayoa]

Post by Patrick Beckers
try 5 times before it actually succeeds (this is especially true for the
Citrix users).

When are you commiting transactions?



--- posted by geoForum on http://delphi.newswhat.com