Re: [firebird-support] how to resolve this deadlock
12.09.2018 2:45, 'Hamish Moffatt' ham...@risingsoftware.com [firebird-support] wrote: > Unfortunately your journal / batch update solution doesn't help in my case. What's wrong with it in your case? -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
RE: [firebird-support] how to resolve this deadlock
Hi Hamish. I don't intend to be obvious, but would it be possible to check record existence before inserting? Atenciosamente, + + Hugo Eyng + + De: firebird-support@yahoogroups.com em nome de 'Hamish Moffatt' ham...@risingsoftware.com [firebird-support] Enviado: quarta-feira, 12 de setembro de 2018 00:50 Para: firebird-support@yahoogroups.com Assunto: Re: [firebird-support] how to resolve this deadlock On 10/09/18 22:52, Hugo Eyng hugoe...@msn.com<mailto:hugoe...@msn.com> [firebird-support] wrote: Hi Hamish. I am not sure if I understood clearly your problem. If I did, you probably should use "WITH LOCK" clause. Thanks Hugo, that might work. But as the row might not exist yet, I would have to lock the whole table, else the two inserts could still run simultaneously and conflict. Hamish
Re: [firebird-support] how to resolve this deadlock
On 10/09/18 22:52, Hugo Eyng hugoe...@msn.com [firebird-support] wrote: > > Hi Hamish. > > I am not sure if I understood clearly your problem. If I did, you > probably should use "WITH LOCK" clause. Thanks Hugo, that might work. But as the row might not exist yet, I would have to lock the whole table, else the two inserts could still run simultaneously and conflict. Hamish
Re: [firebird-support] how to resolve this deadlock
On 11/09/18 00:48, Svein Erling Tysvær setys...@gmail.com [firebird-support] wrote: > 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. Hi Set, I mean that at high level they don't conflict because they write the same values. From Firebird's point of view obviously they do conflict. I say a deadlock because that is what is being reported by Firebird in the error message; SQLCODE: -913 - deadlock - update conflicts with concurrent update - concurrent transaction number is 22464 Unfortunately your journal / batch update solution doesn't help in my case. Thanks for the suggestion though. Hamish
Re: [firebird-support] how to resolve this deadlock
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] : > > > 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 > 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', 1001, 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 > > > > >
RE: [firebird-support] how to resolve this deadlock
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 em nome de 'Hamish Moffatt' ham...@risingsoftware.com [firebird-support] 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', 1001, 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
[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', 1001, 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
