[sqlite-dev] WAL_CHECKPOINT(TRUNCATE) strange behavior?

[Yury Efimochev]

Hi,

I faced the following issue dealing with sqlite db in WAL mode. I wonder if this is an expected behavior(and why) or a bug?

The behavior is following.

I have two sessions and execute following scenario:

session1: open
session2: open

session1: start select operation from table X(do not fetch all results)

session2: wal_checkpoint(truncate) - succeed

session1: try to update some records in table X - fails with 'database is locked' error

In this example WAL is empty since no modification operations were performed, so I would expect
'update' operation to succeed(or at least 'wal_checkpoint' operation to fail with 'database is locked' error).

This is the simple example to reproduce the issue(last query here fails with 'database is locked' error):

#include <sqlite3.h>

#include <memory>
#include <iostream>

std::string const DbPath = "./temp.db";

typedef std::unique_ptr<sqlite3_stmt, int(*)(sqlite3_stmt*)> ResultPtr;

ResultPtr ExecuteQuery(std::string const& query, sqlite3* session)
{
    sqlite3_stmt* statement = nullptr;
    sqlite3_prepare_v2(session, query.c_str(), query.length(), &statement, nullptr);
    if (sqlite3_step(statement) == SQLITE_BUSY)
    {
        std::cout << "Query '" << query << "': database is locked" << std::endl;
    }

    return ResultPtr(statement, sqlite3_finalize);
}

int main()
{
    sqlite3* session1 = nullptr;
    sqlite3_open_v2(DbPath.c_str(), &session1, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nullptr);

    ExecuteQuery("PRAGMA JOURNAL_MODE = WAL", session1);
    ExecuteQuery("CREATE TABLE Test(Id INT, Value TEXT)", session1);
    ExecuteQuery("INSERT INTO Test(Id, Value) VALUES (1, 'a')", session1);
    ExecuteQuery("PRAGMA WAL_CHECKPOINT(TRUNCATE)", session1);

    ResultPtr result = ExecuteQuery("SELECT 1 FROM Test", session1);

    sqlite3* session2 = nullptr;
    sqlite3_open_v2(DbPath.c_str(), &session2, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nullptr);

    ExecuteQuery("PRAGMA WAL_CHECKPOINT(TRUNCATE)", session2);

    ExecuteQuery("UPDATE TEST SET Value = 'b'", session1);
}

--

Best regards,

Yury Yafimachau

[Dan Kennedy]

On 12/29/2016 01:43 PM, Yury Efimochev wrote:
> Hi,
>
> I faced the following issue dealing with sqlite db in WAL mode. I
> wonder if this is an expected behavior(and why) or a bug?
>
> The behavior is following.
> I have two sessions and execute following scenario:
>
> session1: open
> session2: open
> session1: start select operation from table X(do not fetch all results)
> session2: wal_checkpoint(truncate) - succeed
> session1: try to update some records in table X - fails with 'database
> is locked' error
>
> In this example WAL is empty since no modification operations were
> performed, so I would expect
> 'update' operation to succeed(or at least 'wal_checkpoint' operation
> to fail with 'database is locked' error).

It's an interesting case. The error is occurring because "PRAGMA
wal_checkpoint=truncate" and "PRAGMA wal_checkpoint=restart" are more or
less implemented as a "PRAGMA wal_checkpoint = full" followed by a
write-transaction that writes zero pages to the database file. So
effectively:

session1: Open read-transaction.
session2: Full checkpoint (no-op as wal file empty).
session2: Write to database.
session1: Attempt to upgrade to write-transaction ->
SQLITE_BUSY_SNAPSHOT!

Which is a bit counter-intuitive.

Dan.

_______________________________________________
sqlite-dev mailing list
sqlit...@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-dev