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
