Hi SQLite development team.
I think that I found a bug in SQLite or in System.Data.SQLite and isolated the
case when it happens. In short, the newly added row can be missed if it is
requested from another connection.
The bug is quite stable and can be reproduced on my demo application each time,
but it is very fragile: if I change or remove any query or some preference in
connection string, it will disappear.
Short steps (full demo application with comments are referenced below):
0. Create two password-protected connections. Let's call them #0 and #1.
data source=DatabaseFileName;password=000000000000000000
1. Use connection #1 to select something.
SELECT id FROM [MainTable]
2. Use connection #0 to insert new row to the same table.
INSERT INTO MainTable (id) VALUES (@id)
3. Use connection #0 to insert something absolutely not related to
another table.
INSERT INTO DummyTable (Id) VALUES (@id)
4. Use connection #1 to read the row that was inserted first.
SELECT id FROM [MainTable]
Actual: The query doesn't return newly inserted row.
Each of these steps is obligatory. For me the step #3 looks the most confusing:
issue happens if application inserts something to another table. In reality, if
application inserts something to the same table, the issue will happen too. I
left example with another table because it shows strangeness of the issue. But
if application doesn't insert anything on this step, then you won't see the
issue: on the step #4 the newly inserted row will be read.
Also, the issue can't be reproduced if:
- Cache size is set to 0; or
- Journal mode is WAL; or
- Password for database is too short (please see examples in the demo code).
Link to the demo application:
https://github.com/apodogov/SQLiteCachingIssue/blob/master/IsolatedCacheIssue/Program.cs
Regards,
Alexey Podogov,
Akvelon - Russia (UTC+3),
Email: alexey.podo...@akvelon.com
Skype: alexey.podogov
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
