Subject Re: [firebird-support] how to resolve this deadlock
Author Svein Erling Tysvær
Post date 2018-09-10T14:48:50Z
Hi Hamish!

What exactly do you mean by "The queries kind of don't actually conflict"? If they're trying to MERGE into the same row simultaneously, then they do actually conflict, even if they contain the same values. Strictly speaking, I think you have a 'lock conflict' and not a 'deadlock' (a proper deadlock would be more if transaction A has updated record a and waits to update record b, when transaction B has updated record b and waits to update record a).

One way to solve lock conflicts can sometimes be to insert into a separate table (never update) and then have a separate batch process that goes through this table and updates the real tables monthly, daily or every hour. But I've no idea whether this is of any help in your situation.

Set

Den man. 10. sep. 2018 kl. 16:33 skrev Hugo Eyng hugoeyng@... [firebird-support] <firebird-support@yahoogroups.com>:


Hi Hamish.

I am not sure if I understood clearly your problem. If I did, you probably should use "WITH LOCK" clause.


Atenciosamente,

+ + Hugo Eyng + +
De: firebird-support@yahoogroups.com <firebird-support@yahoogroups.com> em nome de 'Hamish Moffatt' hamish@... [firebird-support] <firebird-support@yahoogroups.com>
Enviado: segunda-feira, 10 de setembro de 2018 08:46
Para: firebird-support@yahoogroups.com
Assunto: [firebird-support] how to resolve this deadlock
 
 

I have a bunch of users logging in to my system at the same time, which
causes a simple query like this to run:

UPDATE OR INSERT INTO G_CLASS (CLASS_NUM, CLASS_NAME, USER_NUM,
SYLLABUS_NUM)
VALUES (1, 'Foo', 10000001, 43)

The field values are identical for each - it's exactly the same query.
There's multiple threads running.

I'm getting a deadlock. The transaction mode is read committed with
wait; when one transaction finishes the next one unblocks, but fails
with a deadlock error.


The real query during login is actually a MERGE INTO which achieves the
same thing except it doesn't update all the fields. This might be
another level of complexity again.


Where do I start on resolving this? The queries kind of don't actually
conflict... Is there a different isolation mode that makes it work, or
do I need a different approach, or to retry?


Hamish