|
Hello guys, I have a question regarding the "CHECK ON COMMIT" foreign key option during a table creation. When should I set this on/off? I ask that because we are facing some weird bug in our system. In a specific screen we execute a "UPDATE ... WHERE id = ?", where id has a foreign key to another table. However, this statement fails in some situations with a foreign key violation over "id" column (no ids are updated by this statement). The weird part is that all the FK violations happens when this statement is executed along with a synchronism. I was thinking that the "update" fails because the statement is executed after the referenced id is removed from database, by the synchronism. Could you enlighten me with some insights about what could be happening? |
Follow this question
By Email:Once you sign in you will be able to subscribe for any updates here
By RSS:Markdown Basics
- *italic* or _italic_
- **bold** or __bold__
- link:[text](http://url.com/ "title")
- image?
- numbered list: 1. Foo 2. Bar
- to add a line break simply add two spaces to where you would like the new line to be.
- basic HTML tags are also supported
Question tags:
question asked: 11 Dec '17, 08:13
question was seen: 3,581 times
last updated: 17 Oct '22, 04:41
What exactly is your question: How the CHECK_ON_COMMIT clause (resp. the "wait_for_commit" database option) works or why your update statement may fail? As to synchronization: Is this at a MobiLink consolidated or remote database?
I think I might mixed up some subjects, but I basically want to know if this option could help me with the situation I reported. The error happens in a remote database (Ultralite) while syncing with a consolidated database.
I don't use Ultralite, but AFAIK all MobiLink clients do use Wait_for_commit during the download, so
So I would conclude that the CHECK_ON_COMMIT clause has no further effect during the download.
We do a commit after some lines of code, so I doubt it has some impact over this scenario. Perhaps this could be a bug, then. Thanks
Okay, so an update statement fails during a (concurrent) synchronization, and the sync could be modifying related rows, correct?
Are you modifying the primary (referenced) row, or the foreign (referencing) row? Are you updating unrelated columns, or something related to the foreign key?
I update a row which contains reference (FK) to another table. My guess is that, during the sync, the reference register (FK) could have been deleted, causing the FK violation. However, I'm not updating the FK itself, only minor columns in the same table.
I will have to get back to you with a more thorough answer for this case specifically, but it's certainly true that concurrent operations on related rows that the download has modified are well protected by UL: it doesn't let you do things that would be inconsistent as soon as it knows what the download is doing. A basic example is that inserting foreign rows referencing a primary row, where the download is deleting the primary row, will fail.