I have an open database connection, and am opening a second connection to the same database. I need those connections to see the same data, so I'm trying to use the "sqlite3_snapshot_get" functionality.

However, it's not working for me. Could someone help point out what I'm doing wrong? My process:

• The database is in WAL mode, and there are no open connections to it (so no "-shm" and "-wal" files)
• Open database connection 1 read-only
• Start a transaction on connection 1, and read some things
• (In another process) write some new data to the database
• As expected, the "-shm" and "-wal" files exist, and connection 1 cannot see the changes made (if I actually do some queries, which I do not do in this reproduction)
• Open database connection 2 read-only
• Call sqlite3_snapshot_get(connection1, "main", &snapshot)
• This returns SQLITE_ERROR

Based on the documentation however, I expected this to return SQLITE_OK and a snapshot:

• The database is not in autocommit mode (I have an open transaction)
• Schema "main" is in WAL mode
• There is no write transaction open on connection 1 (there is no open write transaction at all)
• The WAL file has a written transaction

Thus, I am unable to apply such a snapshot to connection 2.

The error is because the WAL file does not contain a transaction and you cannot get a snapshot marker when there are no transactions in the WAL file. So for your process to work, you need to actually commit that transaction.

sqlite3_snapshot_get records the location of the "commit marker" in the WAL file, so in order for it to work, there must be at least one "commit marker" which means you must have committed at least one transaction.

sqlite3_snapshot_open sets the "location" to the "commit marker" previously recorded by a call to sqlite3_snapshot_get. If you "checkpoint" that commit marker out of the WAL, you can no longer open that recorded "location".

In other words, think of the WAL log as a queue of dinner plates waiting to get loaded into the dishwasher, each one with a number (1, 2, 3, 4, 5 ... and so on). As you write (COMMIT) each transaction, a new "plate" (transaction) is added to the queue (this is a FIFO queue) with ever increasing plate numbers.

sqlite3_snapshot_get returns the number of the last added plate in the queue at the particular moment it is called. If there are no plates in the queue (no transactions) then you will get an error that there is no plate number to remember.

When a WAL Checkpoint occurs, plates are removed from the other end of the stack and loaded into the dishwasher (the main database file) up until an "in use" plate (with an open transaction referring to it) -- it is a FIFO queue.

sqlite3_snapshot_open sets the current read location to the "plate" number previously saved with sqlite3_snapshot_get. If that plate has been removed (put in the dishwasher) you can no longer go to that plate number because it is no longer in the queue, so you get an error.

This is how it worked last time someone asked and I looked at it. Perhaps it has been updated to permit sqlite3_snapshot_get to work when the WAL is empty, but I don't know the answer to that. Based on what you are reporting, it would look like there must be a transaction in the WAL file.

The following C code will ensure that you can snapshot against a database connection (you may want to fix/change some of the error handling):

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;
    if (sqlite3_step(stmt) != SQLITE_ROW)
    {
        sqlite3+finalize(stmt);
        return SQLITE_ERROR;
    }
    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;
}

Thanks for your information. Your example should indeed work, but is not preferred since this currently is a read-only transaction.

Based on your description, however, I think my process should have worked. Maybe I didn't get it correctly?

 time  |  Program A           |  Program B   |  State of WAL file
  |    |  Open read-only      |              |  (empty)
  |    |  connection c1       |              |  c1
  v    |                      |              |
       |  start read          |              |  (empty)
       |  transaction on c1   |              |  c1
       |                      |              |
       |                      |  Modify the  |  |--commit1--|
       |                      |  database    |  c1
       |                      |              |
       |  Open read-only      |              |  |--commit1--|
       |  connection c2       |              |  c1          c2

Desired:
       |                      |              |  |--commit1--|
       |                      |              |  c1
       |                      |              |  c2

Now, I'd like to move c2 to the same position as c1 ("no plates/plate 0"). The content/state/plate should still be there, since c1 has an open read transaction.

sqlite3_snapshot_open sets the current read location to the "plate" number previously saved with sqlite3_snapshot_get. If that plate has been removed (put in the dishwasher) you can no longer go to that plate number because it is no longer in the queue, so you get an error.

This is how it worked last time someone asked and I looked at it. Perhaps it has been updated to permit sqlite3_snapshot_get to work when the WAL is empty, but I don't know the answer to that. Based on what you are reporting, it would look like there must be a transaction in the WAL file.

The WAL file isn't empty, so from the documentation and your reply, I interpret that this should work. Maybe because the WAL was empty when I started connection/transaction c1?

If it doesn't and shouldn't work, is there another way to ensure c1 and c2 point at the same position? (Besides creating a "dummy commit")

If it doesn't and shouldn't work, is there another way to ensure c1 and c2 point at the same position? (Besides creating a "dummy commit")

My experience and testing indicates that there has to be a transaction in the WAL file that has been committed and not yet checkpointed out before you can get a snapshot location with sqlite3_snapshot_get. It does not appear that there is a way to get or create a marker that says "from the main database only without the WAL file".

I would suspect this is because there would then be no way to know if that snapshot was checkpointed out of the WAL (ie, if the snapshot recorded "at the beginning of the WAL file" there would be no way to invalidate that snapshot if all the transactions in the WAL were checkpointed, and you would have a snapshot that is not a valid snapshot but you have no way to tell).

So I think the "dummy commit" is required to provide a starting point. Note that I used the "pragma user_version" because updating this to the same value it already has is in fact a transaction, where updating some data in some table somewhere to a value it already had is not (SQLite optimizes away the change that isn't and when you commit optimizes away the transaction that isn't). This means that this "dummy transaction" serves as an actual transaction and writes a commit record to the WAL without actually changing anything.

there would then be no way to know if that snapshot was checkpointed out of the WAL

Well, there's the File Change Counter, isn't there?

In WAL mode, when there's a WAL file, the counter comes from the WAL,
so the DB file counter stays unchanged until checkpointed, no?

So if the snapshot data indicated whether its DB-only or DB+WAL, and
included the DB's counter in the former case, then one can detect
a snapshot too old case (to use the Oracle terminology),
since I assume checkpointing updates the DB counter.

Well, I looked at the code.

The sqlite3_snapshot is nothing more than a copy of the WAL-header as defined here: https://sqlite.org/walformat.html

So, when the WAL is created, there is no valid WAL-Header until the first transaction is committed and the WAL structures are initialized. Thereafter sqlite3_snapshot_get will work to get a checkpoint location even if the WAL file is checkpointed (unless you do a TRUNCATE checkpoint which deletes the WAL header). However, the WAL file must have been initialized by writing at least one transaction to it sometime between when it is created and when sqlite3_checkpoint_get is called.

This means that if you "suddenly" decide that you want to use snapshots, you have to ensure that the WAL is properly initialized. If you know that you will be using snapshots then you can simply set WAL persistence so that it isn't deleted when the connection closes (you need to set persistence for EVERY connection to the database).

Would it not be more intuitive if the sqlite snapshot code created the needed wal structure if it's missing?

Thanks for all the replies! I can use this workaround for now (my usecase is the one ddevienne mentioned: read parallellism)

It would be great if SQLite supports the "empty-WAL" usecase in the future though. Also, IMHO the documentation can use some improvement: it mentions the "empty-WAL" error-case, but it wasn't clear to me this is about the start time of the transaction, not the time you call sqlite3_snapshot_get.