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 hugoe...@msn.com [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' ham...@risingsoftware.com [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 > > > > >
