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?

asked 11 Dec '17, 08:13

Alex's gravatar image

Alex
1.1k274756
accept rate: 25%

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?

(11 Dec '17, 08:41) Volker Barth
Replies hidden

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.

(11 Dec '17, 08:48) Alex

I don't use Ultralite, but AFAIK all MobiLink clients do use Wait_for_commit during the download, so

  1. Checking of orphaned rows is delayed until the single download transaction is committed.
  2. Orphaned rows are then automatically deleted (raising a warning within Ultralite).

So I would conclude that the CHECK_ON_COMMIT clause has no further effect during the download.

(11 Dec '17, 09:43) Volker Barth

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

(11 Dec '17, 10:23) Alex

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?

(11 Dec '17, 17:23) Tim McClements
Replies hidden

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.

(12 Dec '17, 05:02) Alex

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.

(14 Dec '17, 11:23) Tim McClements
showing 2 of 7 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • 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:

×161
×94
×84
×38
×31

question asked: 11 Dec '17, 08:13

question was seen: 3,762 times

last updated: 17 Oct '22, 04:41