On Sunday, 13 October, 2019 15:23, Adam Levy <theadaml...@gmail.com> wrote:

>My application requires a way to build a "pending state" in the database
>while allowing users to query data from both the "official state" and the
>"pending state". I am achieving this using sessions and snapshots.

SQLite3 does not do database snapshots.  What you are calling a "snapshot" is 
merely "remembering" a commit mark location in the WAL journal.

>When pending data first comes into the application, I take a snapshot of
>the current "official" state of the database and keep a read transaction
>open on one read only connection for the life of the snapshot to prevent
>it being checkpointed out of the WAL, and then start a session that tracks
>all changes on my one write connection so that the pending data may later be
>"rolled back" by applying the inverse changeset. Then I proceed to commit
>incoming pending data to the write connection.

Define "open a read transaction".  

"BEGIN" does not commence a transaction, it merely turns off autocommit.  You 
have to actually READ something in order to obtain a read lock (transaction) on 
the database.

>Any user querying the application for the pending state, is reading from
>a read only connection which will reflect the current latest state of the
>database, which includes the pending data, if any exists.

Yes, it will use the latest commit point recorded in the WAL file at the time 
it actually reads something (not at the time the BEGIN is executed), or if you 
have used sqlite3_checkpoint_open to "go to" a memorized commit mark, then all 
data committed before that commit mark.

>Any user querying for the official state, is reading from a read only
>connection that has a read transaction started from the official state
>snapshot captured above, if any pending data exists.

Do you mean it is using the connection that you started a read transaction on, 
or it turning off autocommit (BEGIN) and then going to this memorized commit 
marker location (sqlite3_snapshot_open)?

>When the next official state update occurs, the snapshot is freed, its
>associated read transaction that I set aside is closed (and the read
>connection is returned to my read conn pool), and the inverse changeset
>is used to roll back the pending state on a write connection before applying
>the official state update. Any reader currently reading from the snapshot
>should be able to proceed until they end their read transaction.

First of all, you cannot "free" a snapshot, since there is no such thing.  What 
you are doing is basically nothing more than freeing the notation of the commit 
point location that you made (sort of like crumpling up the piece of paper that 
it was written on and setting it on fire).  This has no effect whatsoever on 
the commit marker itself or on the WAL file -- merely on the on the piece of 
paper on which it was written.

Secondly, readers do not read from a snapshot since snapshots do not actually 
exist.  What they are reading is based on the commit marker in the WAL file at 
the time they actually started to read (not BEGIN, which does not actually do 
anything other than turn off autocommit).

Thirdly, why do you "roll back" the pending state and then re-apply it again?  
Or is the actual update that you are going to commit different from the updates 
already written?

>I assumed that keeping a read transaction open on the snapshot would be
>enough to prevent the WAL from being checkpointed past the snapshot,
>making it unavailable for use by other read connections. However, despite
>keeping one read transaction open, I am still getting SQLITE_ERROR_SNAPSHOT
>from sqlite3_snapshot_open when the pending data grow large enough to 
>force an auto checkpoint. I am fairly confident that the snapshot getting 
>checkpointed out of the WAL is the cause of this error since it went 
>away when I disabled auto checkpoints.

Did you actually read something or did you just turn off autocommit?  BEGIN 
just turns off autocommit, it does not actually commence a transaction.  That 
transaction and the commit marker location is set when you actually read 
something.  If you do not actually read something, then there is not actually a 
transaction in process.

sqlite3_snapshot_get and sqlite3_snapshot_open say that they "open a read 
transaction" on a connection that has autocommit turned off.  From my testing 
it appears that they do.

>Is my assumption incorrect? If so, is there any way to ensure a snapshot
>remains in the WAL without going so far as to manually manage checkpoints
>myself?

You mean a commit marker?  Yes.  Do not checkpoint the transaction associated 
with that commit marker.  There are two ways to do this:  control your 
checkpointing or open the snapshot (commit marker) and keep it open.

>Finally if I must manually manage checkpoints, is it possible for a
>PASSIVE checkpoint to leave the WAL in a state that will prevent taking a
>snapshot in the first place?  Specifically, can a PASSIVE checkpoint cause
>the following requirement for sqlite3_snapshot_get to no longer hold:
>"One or more transactions must have been written to the current wal file
>since it was created on disk (by any connection). This means that a
>snapshot cannot be taken on a wal mode database with no wal file
>immediately after it is first opened. At least one transaction must be
>written to it first."

No.  sqlite3_snapshot_get will work after a checkpoint even it it checkpoints 
and truncates the log.

#include <sqlite3.h>
#include <stdio.h>
#include <string.h>


int sqlite3_snapshot_ensure(sqlite3* db, char* zDbName)
{
    sqlite3_snapshot *sn = NULL;
    sqlite3_stmt* stmt;
    int rc = 0;
    int wal = 0;
    int persist = 1;
    char buf[128];

    // Prerequisite 1:  Make sure database is in journal_mode=wal
    //                  Disable AutoCheckpointing
    //                  Persist the WAL files

    if ((zDbName == NULL) || (strlen(zDbName) < 1) || (strlen(zDbName) > 64))
        return SQLITE_ERROR;
    snprintf(buf, sizeof(buf), "pragma %s.journal_mode", zDbName);
    if (sqlite3_prepare_v2(db, buf, -1, &stmt, NULL) != SQLITE_OK)
        return SQLITE_ERROR;
    sqlite3_step(stmt);
    wal = !(strnicmp("wal", sqlite3_column_text(stmt, 0), 3));
    sqlite3_finalize(stmt);
    if (!wal)
    {
        snprintf(buf, sizeof(buf), "pragma %s.journal_mode=wal", zDbName);
        if (sqlite3_exec(db, buf, NULL, NULL, NULL) != SQLITE_OK)
            return SQLITE_ERROR;
    }
    snprintf(buf, sizeof(buf), "pragma %s.wal_autocheckpoint=0", zDbName);
    if (sqlite3_exec(db, buf, NULL, NULL, NULL) != SQLITE_OK)
        return SQLITE_ERROR;
    snprintf(buf, sizeof(buf), "pragma %s.synchronous=full", zDbName);
    if (sqlite3_exec(db, buf, NULL, NULL, NULL) != SQLITE_OK)
        return SQLITE_ERROR;
//    sqlite3_file_control(db, zDbName, SQLITE_FCNTL_PERSIST_WAL, &persist);

    // Test retrieving a snapshot structure

    sqlite3_exec(db, "BEGIN", NULL, NULL, NULL);
    rc = sqlite3_snapshot_get(db, zDbName, &sn);
    sqlite3_exec(db, "ROLLBACK", NULL, NULL, NULL);

    // Prerequisite 2:  Must have a transaction in the wal file

    if (!sn)
    {

        sqlite3_exec(db, "BEGIN IMMEDIATE", NULL, NULL, NULL);
        snprintf(buf, sizeof(buf), "pragma %s.user_version", zDbName);
        sqlite3_prepare_v2(db, buf, -1, &stmt, NULL);
        sqlite3_step(stmt);
        snprintf(buf, sizeof(buf), "pragma %s.user_version=%d", zDbName, 
sqlite3_column_int(stmt, 1));
        sqlite3_finalize(stmt);
        sqlite3_exec(db, buf, NULL, NULL, NULL);
        sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);

        // Test retrieving a snapshot structure

        sqlite3_exec(db, "BEGIN", NULL, NULL, NULL);
        rc = sqlite3_snapshot_get(db, zDbName, &sn);
        sqlite3_exec(db, "ROLLBACK", NULL, NULL, NULL);
    }

    // Free the snapshop structure if we have one
    // and return the result code from the attempt

    if (sn)
        sqlite3_snapshot_free(sn);

    return rc;
}


void main(void)
{
    sqlite3* db = NULL;
    sqlite3* db2 = NULL;
    sqlite3_snapshot* sn = NULL;
    int frames = 0;
    int written = 0;
    int rc = 1;

    if (SQLITE_OK ==  sqlite3_open("test.db", &db))
    {
        printf("Database Opened\n");
        printf("Snapshot ensured=%d\n", sqlite3_snapshot_ensure(db, "main"));
        sqlite3_open("test.db", &db2);
        sqlite3_exec(db2, "BEGIN", NULL, NULL, NULL);
        sqlite3_snapshot_get(db2, "main", &sn);
        sqlite3_exec(db, "drop table if exists x; create table if not exists 
x(x); insert into x values (1);", NULL, NULL, NULL);
        sqlite3_exec(db, "BEGIN", NULL, NULL, NULL);
        sqlite3_snapshot_open(db, "main", sn);
        sqlite3_exec(db2, "COMMIT", NULL, NULL, NULL);
        rc = sqlite3_wal_checkpoint_v2(db2, "main", SQLITE_CHECKPOINT_PASSIVE, 
&frames, &written);
        printf("wal passive checkpoint returned %d %d %d\n", rc, frames, 
written);
        sqlite3_exec(db2, "BEGIN", NULL, NULL, NULL);
        printf("snapshot open returned %d\n", sqlite3_snapshot_open(db2, 
"main", sn));
        sqlite3_exec(db, "ROLLBACK", NULL, NULL, NULL);
        sqlite3_exec(db2, "ROLLBACK", NULL, NULL, NULL);
        rc = sqlite3_wal_checkpoint_v2(db2, "main", SQLITE_CHECKPOINT_PASSIVE, 
&frames, &written);
        printf("wal passive checkpoint returned %d %d %d\n", rc, frames, 
written);
        sqlite3_exec(db, "BEGIN", NULL, NULL, NULL);
        printf("snapshot open rc=%d\n", sqlite3_snapshot_open(db, "main", sn));
        printf("snapshot get rc=%d\n", sqlite3_snapshot_get(db, "main", &sn));
        sqlite3_exec(db, "ROLLBACK", NULL, NULL, NULL);
        rc = sqlite3_wal_checkpoint_v2(db2, "main", SQLITE_CHECKPOINT_TRUNCATE, 
&frames, &written);
        printf("wal truncate checkpoint returned %d %d %d\n", rc, frames, 
written);
        sqlite3_exec(db, "BEGIN", NULL, NULL, NULL);
        printf("snapshot open rc=%d\n", sqlite3_snapshot_open(db, "main", sn));
        printf("snapshot get rc=%d\n", sqlite3_snapshot_get(db, "main", &sn));
        sqlite3_exec(db, "ROLLBACK", NULL, NULL, NULL);
    }
    else
        printf("Database Open Failed\n");
    if (db)
        sqlite3_close(db);
    if (db2)
        sqlite3_close(db2);
}

>test
Database Opened
Snapshot ensured=0
wal passive checkpoint returned 0 6 1
snapshot open returned 0
wal passive checkpoint returned 0 6 6
snapshot open rc=769
snapshot get rc=0
wal truncate checkpoint returned 0 0 0
snapshot open rc=769
snapshot get rc=0

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume. 



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

Reply via email to