Func walRestartLog reset readlock to greater than 0 after walRestartHdr. Is this an indispensable logic, must the readlock be reset?

Because the write lock is also exclusive in wal mode, updating the readlock seems to avoid repeated execution of walRestartLog. However, the conn that acquires the write lock can definitely detect whether restart needs to be executed. So this logic seems to acquire a non-zero readlock for the subsequent write transaction before acquiring the write lock. Is it just a performance optimization. In fact, this code can also be deleted?

Can anyone identify this or explain more?

If the client goes on to commit the write transaction but does not also immediately close the read transaction (i.e. because it has an unfinalized SELECT holding the read transaction open), it needs to be holding a read-lock other than 0. Otherwise, if a second writer writes a transaction to the wal file, a checkpointer could then run before the read transaction finishes - breaking snapshot isolation.

Thank you for your quick reply, but I have some doubts.

According to my observation, when sqlite is in wal mode, it acquire and release lock like this. Read lock and write lock have always been locked and unlocked in this order.

// when transaction begin(IMMEDIATE, EXCLUSIVE )，or first access database (DEFERRED )
sqlite3WalBeginReadTransaction

// when transaction begin(IMMEDIATE, EXCLUSIVE ) or first update database (DEFERRED)
sqlite3WalBeginWriteTransaction

// after transaction commit
sqlite3WalEndWriteTransaction
sqlite3WalEndReadTransaction

If my statement above is correct. If there is a write transaction that is not committed, there will be no second write transaction that can be committed, and there will be no checkpoint that can be committed. Because checkpoint also needs to acquire write lock first, whether the checkpoint is triggered manually or by transaction commit.

If there is still a SELECT running when COMMIT is executed, then that last call to sqlite3WalEndReadTransaction() is not made. The read transaction remains open until the number of running SELECT statements drops to zero. If the app executes a write statement (or "BEGIN IMMEDIATE" etc.), then a second write transaction is opened without ever dropping and reacquiring the read lock.

I don't think I 100% follow the bit in bold text. Checkpoints don't always need the WRITER lock - the default PASSIVE checkpoints proceed without it.

Dan.

Does it mean that a select statement does not call reset but the transaction is committed? If it is convenient, can you express it in pseudocode?

Say you do this, where the sqlite3_prepare() prepares a statement that fetches many rows:

     // start with empty wal file.
     sqlite3_prepare();  // prepare a SELECT
     sqlite3_step();     // fetch first row of SELECT results
     sqlite3_exec("BEGIN; dml statments ; COMMIT;");
     sqlite3_step();     // fetch second row of results

Then the COMMIT drops the write lock but keeps the read transaction open. The branch at 3281 in the code you linked above is taken.

But in this case readlock 0 is released, readlock1 is kept but readmark is 0. During checkpoint, the calculated mxSafeFrame is still 0, and checkpoint does not actually occur.

I'm not sure I follow, but that sounds correct to me. Following the pseudo-code above, the client may hold read-lock 1 with the corresponding read-mark set to 0.

In theory, it would also be safe to hold read-lock 1 with the read-mark set to however many frames were written by the COMMIT so that a checkpoint could copy all that has been written into the db file. But that would make the code more complicated.

Dan.

In theory, it would also be safe to hold read-lock 1 with the read-mark set to however many frames were written by the COMMIT so that a checkpoint could copy all that has been written into the db file.

I don't quite understand this. When we are calling walRestartLog, pInfo->nBackfill==pWal->hdr.mxFrame and write-lock is held, so no other connection can write to wal-log. If reset the read-lock, read-mark should always be 0.