Possible caching issue between connections with specific inserts

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Possible caching issue between connections with specific inserts

Alexey Podogov
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: [hidden email]
Skype: alexey.podogov

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Possible caching issue between connections with specific inserts

Joe Mistachkin

Alexey Podogov wrote:
>
> 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).
>

Thanks for the report.  I'm looking into it now.

--
Joe Mistachkin

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users