Different applications keep undoing changes made to database
(1) By anonymous on 2022-02-24 10:12:25 [link] [source]
We have an SQLite database with several tables in it. We also have a few applications that read and write to those tables. The issue is a bit difficult to explain but I'll try my best to illustrate the problem. _______________________________________ Let's say App1 writes to table T1, and App2 writes to T2 and T3. Let's also say that initially T1's last row ID is 508, T2's 559, and T3's 321. (These are the actual numbers.) Basically: [latest ID T1]/[latest ID T2]/[latest ID T3] = 508/559/321 Step App1 - App2 -------------------------------------------------------- 1 | +509/559/321 - 2 | - 509/+560/+322 3 | +510/559/321 - 4 | +511/559/321 - 5 | - 509/+560, +561/+322, +323 6 | - 509/+562/323 7 | - 509/562/+324 8 | ...+512/559/321 9 | - 509/...+563/...+325 So, in step 1, App1 has written row ID 509 to T1. In step 2, App2 writes row 560 to T2 and row 322 to T3. Note that the update from T1 is seen. In step 3, App1 writes row 510 to T1. But note the rows for T2 and T3. It seems like the changes from step 2 were undone. This behavior continues in the following steps. E.g. in step 5 the changes from steps 3 and 4 are undone. Additionally, the changes from step 2 are inserted back along with the new IDs (561 and 323). I ran a PRAGMA integrity_check on (a copy of) the DB and it return "ok". So I don't think that that DB file is corrupted. The API calls for the inserts are also pretty standard: - Connect to DB - Execute SQL statement (INSERT INTO...) - Commit - Close connection to DB _______________________________________ -----> Any ideas what may be the cause of such behavior?
(2) By Richard Hipp (drh) on 2022-02-24 11:31:28 in reply to 1 [link] [source]
(3) By anonymous on 2022-02-24 11:46:02 in reply to 2 [link] [source]
Those entries are written into the DB, so they should have been committed. And in step 2, App2 actually sees the change made by App1.
The code looks like this:
App1:
con = sqlite3.connect(self._database_path)
con_cursor = con.cursor()
con_cursor.execute(sql)
result = con_cursor.fetchone()
con.commit()
con.close()
return result
App2:
con = None
try:
con = sqlite3.connect(self._database_path)
cursor = con.cursor()
cursor.execute(command, values)
con.commit()
cursor.close()
finally:
if con:
con.close()
In case of a missing or failing commit there shouldn't be any changes visible at all, I believe?
(4) By Larry Brasfield (larrybr) on 2022-02-24 12:09:47 in reply to 3 [link] [source]
You're getting tripped up by some overly automagical commit shenanigans that Python's DB adapter is doing on your "behalf". If you put your DDL into explicit transactionsa, I believe your puzzling behavior will vanish.
a. By "explicit transaction", I mean the pattern:
BEGIN TRANSACTION
<do stuff>
COMMIT
, which is distinctly different from what you have posted.
(6) By anonymous on 2022-02-24 14:44:06 in reply to 4 [link] [source]
There indeed seem to be issues revolving around this (https://stackoverflow.com/q/15856976/4303296). Thank you, I will consider this.
(5) By Gunter Hick (gunter_hick) on 2022-02-24 12:10:22 in reply to 1 [link] [source]
This sequence is very strange indeed. Please provide the exact SQLite version you are using and the OS running the applications. Can you reproduce the problem using two SQLite shell processes to perform the updates? Are you sure that both applications are working on the same physical database file (and there is not some strange copy-on-reference mumbo jumbo going on at the OS level). One explanation would be that App2 is working on a copy of the file as left behind by App1 and from then on the two files diverge. What values are actually conserved in the file? (i.e. what does the next run of both apps see? Changing your application to the following pseudocode may shed some light on the issue: connect execute BEGIN IMMEDIATE while (keep running) execute INSERT INTO execute COMMIT disconnect If your Apps are using the same database file, App2 should block until App1 is finished, there being only one write transaction allowed at any one time.
(7) By anonymous on 2022-02-24 15:12:46 in reply to 5 [link] [source]
sqlite3.version and sqlite3.sqlite_version outputs: - App1: 2.6.0, 3.37.0 - App2: 2.6.0, 3.31.1 OS is Windows 10. Unfortunately I haven't been able to reproduce the problem so far, on my machine (the .db file currently resides on a NAS). They should be working on the same file, because they both make changes to it and their changes are visible. That being said, some suspicion in that direction was there and I cannot fully eliminate this possibility, yet. What is preserved varies (I may have misunderstood your question here). In the above example (say step 5 -> step 6) everything remains in tact except T1's rows 510 and 511 are now missing. As another example: between steps 5 and 6 there actually were two manual modifications - let's call them steps 5.1 and 5.2 - to the DB (via DBBrowser.exe). In both cases some columns of T3 were modified. In step 6, the modifications from step 5.2 were actually lost, but those from 5.1 remained.
(8) By Kees Nuyt (knu) on 2022-02-24 17:37:24 in reply to 7 [link] [source]
Unfortunately I haven't been able to reproduce the problem so far, on my machine (the .db file currently resides on a NAS).
You will most probably not be able to reproduce this behavior when the database file is on a filesystem managed by the OS the Apps run on.
All bets are off if the database file resides on a network file system.
You even risk corruption.
--
Regards,
Kees NUyt
(12) By anonymous on 2022-02-25 10:07:35 in reply to 8 [source]
Thanks to everyone for replying. This clears a lot of things up.
(9) By Gunter Hick (gunter_hick) on 2022-02-25 06:36:16 in reply to 7 [link] [source]
You are commiting two mortal sins at the same time. SQLite db files should not be placed on a neteork file system, as the locking is usually broken. The SQlite db file must reside on the same system that is running the application. Additionally, all applications need to share the same SQLite library to function properly. As a result, you have a nondeterministic mix of the database pages written by both applications.
(10) By Larry Brasfield (larrybr) on 2022-02-25 08:10:21 in reply to 9 [link] [source]
Additionally, all applications need to share the same SQLite library to function properly.
That greatly overstates the requirement for proper functioning of SQLite. As long as newer features, enabled by use of a later SQLite library version but not supported by an earlier SQLite library version, are avoided in the applications which access a (local file) databases, the stability of the database format is enough to ensure interoperability.
BTW, I agree regarding the first mortal sin. That is likely playing a role in the OP's difficulty, along with Python's too-clever auto-transaction logic.
(11) By Gunter Hick (gunter_hick) on 2022-02-25 08:59:10 in reply to 10 [link] [source]
I remembered something from the back of my head and upon rereading https://sqlite.org/howtocorrupt.html chapter 2.2.1 found it to relate to linking multiple copies of SQLite within one application.