Index: main.mk ================================================================== --- main.mk +++ main.mk @@ -529,12 +529,12 @@ # The next two rules are used to support the "threadtest" target. Building # threadtest runs a few thread-safety tests that are implemented in C. This # target is invoked by the releasetest.tcl script. # -threadtest3$(EXE): sqlite3.c $(TOP)/test/threadtest3.c - $(TCCX) -O2 sqlite3.c $(TOP)/test/threadtest3.c \ +threadtest3$(EXE): sqlite3.o $(TOP)/test/threadtest3.c $(TOP)/test/tt3_checkpoint.c + $(TCCX) -O2 sqlite3.o $(TOP)/test/threadtest3.c \ -o threadtest3$(EXE) $(THREADLIB) threadtest: threadtest3$(EXE) ./threadtest3$(EXE) Index: src/btree.c ================================================================== --- src/btree.c +++ src/btree.c @@ -7939,20 +7939,22 @@ /* ** Run a checkpoint on the Btree passed as the first argument. ** ** Return SQLITE_LOCKED if this or any other connection has an open ** transaction on the shared-cache the argument Btree is connected to. +** +** Parameter eMode is one of SQLITE_CHECKPOINT_PASSIVE, FULL or RESTART. */ -int sqlite3BtreeCheckpoint(Btree *p){ +int sqlite3BtreeCheckpoint(Btree *p, int eMode, int *pnLog, int *pnCkpt){ int rc = SQLITE_OK; if( p ){ BtShared *pBt = p->pBt; sqlite3BtreeEnter(p); if( pBt->inTransaction!=TRANS_NONE ){ rc = SQLITE_LOCKED; }else{ - rc = sqlite3PagerCheckpoint(pBt->pPager); + rc = sqlite3PagerCheckpoint(pBt->pPager, eMode, pnLog, pnCkpt); } sqlite3BtreeLeave(p); } return rc; } Index: src/btree.h ================================================================== --- src/btree.h +++ src/btree.h @@ -205,11 +205,11 @@ int sqlite3BtreeCursorInfo(BtCursor*, int*, int); void sqlite3BtreeCursorList(Btree*); #endif #ifndef SQLITE_OMIT_WAL - int sqlite3BtreeCheckpoint(Btree*); + int sqlite3BtreeCheckpoint(Btree*, int, int *, int *); #endif /* ** If we are not using shared cache, then there is no need to ** use mutexes to access the BtShared structures. So make the Index: src/main.c ================================================================== --- src/main.c +++ src/main.c @@ -1338,40 +1338,60 @@ #else return 0; #endif } - /* -** Checkpoint database zDb. If zDb is NULL, or if the buffer zDb points -** to contains a zero-length string, all attached databases are -** checkpointed. +** Checkpoint database zDb. */ -int sqlite3_wal_checkpoint(sqlite3 *db, const char *zDb){ +int sqlite3_wal_checkpoint_v2( + sqlite3 *db, /* Database handle */ + const char *zDb, /* Name of attached database (or NULL) */ + int eMode, /* SQLITE_CHECKPOINT_* value */ + int *pnLog, /* OUT: Size of WAL log in frames */ + int *pnCkpt /* OUT: Total number of frames checkpointed */ +){ #ifdef SQLITE_OMIT_WAL return SQLITE_OK; #else int rc; /* Return code */ int iDb = SQLITE_MAX_ATTACHED; /* sqlite3.aDb[] index of db to checkpoint */ + + if( eMode!=SQLITE_CHECKPOINT_PASSIVE + && eMode!=SQLITE_CHECKPOINT_FULL + && eMode!=SQLITE_CHECKPOINT_RESTART + ){ + return SQLITE_MISUSE; + } sqlite3_mutex_enter(db->mutex); if( zDb && zDb[0] ){ iDb = sqlite3FindDbName(db, zDb); } if( iDb<0 ){ rc = SQLITE_ERROR; sqlite3Error(db, SQLITE_ERROR, "unknown database: %s", zDb); }else{ - rc = sqlite3Checkpoint(db, iDb); + rc = sqlite3Checkpoint(db, iDb, eMode, pnLog, pnCkpt); sqlite3Error(db, rc, 0); } rc = sqlite3ApiExit(db, rc); sqlite3_mutex_leave(db->mutex); return rc; #endif } + +/* +** Checkpoint database zDb. If zDb is NULL, or if the buffer zDb points +** to contains a zero-length string, all attached databases are +** checkpointed. +*/ +int sqlite3_wal_checkpoint(sqlite3 *db, const char *zDb){ + return sqlite3_wal_checkpoint_v2(db, zDb, SQLITE_CHECKPOINT_PASSIVE, 0, 0); +} + #ifndef SQLITE_OMIT_WAL /* ** Run a checkpoint on database iDb. This is a no-op if database iDb is ** not currently open in WAL mode. ** @@ -1385,24 +1405,33 @@ ** this function while the checkpoint is running. ** ** If iDb is passed SQLITE_MAX_ATTACHED, then all attached databases are ** checkpointed. If an error is encountered it is returned immediately - ** no attempt is made to checkpoint any remaining databases. +** +** Parameter eMode is one of SQLITE_CHECKPOINT_PASSIVE, FULL or RESTART. */ -int sqlite3Checkpoint(sqlite3 *db, int iDb){ +int sqlite3Checkpoint(sqlite3 *db, int iDb, int eMode, int *pnLog, int *pnCkpt){ int rc = SQLITE_OK; /* Return code */ int i; /* Used to iterate through attached dbs */ + int bBusy = 0; /* True if SQLITE_BUSY has been encountered */ assert( sqlite3_mutex_held(db->mutex) ); for(i=0; inDb && rc==SQLITE_OK; i++){ if( i==iDb || iDb==SQLITE_MAX_ATTACHED ){ - rc = sqlite3BtreeCheckpoint(db->aDb[i].pBt); + rc = sqlite3BtreeCheckpoint(db->aDb[i].pBt, eMode, pnLog, pnCkpt); + pnLog = 0; + pnCkpt = 0; + if( rc==SQLITE_BUSY ){ + bBusy = 1; + rc = SQLITE_OK; + } } } - return rc; + return (rc==SQLITE_OK && bBusy) ? SQLITE_BUSY : rc; } #endif /* SQLITE_OMIT_WAL */ /* ** This function returns true if main-memory should be used instead of Index: src/pager.c ================================================================== --- src/pager.c +++ src/pager.c @@ -6548,18 +6548,24 @@ return &pPager->pBackup; } #ifndef SQLITE_OMIT_WAL /* -** This function is called when the user invokes "PRAGMA checkpoint". +** This function is called when the user invokes "PRAGMA wal_checkpoint", +** "PRAGMA wal_blocking_checkpoint" or calls the sqlite3_wal_checkpoint() +** or wal_blocking_checkpoint() API functions. +** +** Parameter eMode is one of SQLITE_CHECKPOINT_PASSIVE, FULL or RESTART. */ -int sqlite3PagerCheckpoint(Pager *pPager){ +int sqlite3PagerCheckpoint(Pager *pPager, int eMode, int *pnLog, int *pnCkpt){ int rc = SQLITE_OK; if( pPager->pWal ){ - u8 *zBuf = (u8 *)pPager->pTmpSpace; - rc = sqlite3WalCheckpoint(pPager->pWal, pPager->ckptSyncFlags, - pPager->pageSize, zBuf); + rc = sqlite3WalCheckpoint(pPager->pWal, eMode, + pPager->xBusyHandler, pPager->pBusyHandlerArg, + pPager->ckptSyncFlags, pPager->pageSize, (u8 *)pPager->pTmpSpace, + pnLog, pnCkpt + ); } return rc; } int sqlite3PagerWalCallback(Pager *pPager){ Index: src/pager.h ================================================================== --- src/pager.h +++ src/pager.h @@ -136,11 +136,11 @@ int sqlite3PagerRollback(Pager*); int sqlite3PagerOpenSavepoint(Pager *pPager, int n); int sqlite3PagerSavepoint(Pager *pPager, int op, int iSavepoint); int sqlite3PagerSharedLock(Pager *pPager); -int sqlite3PagerCheckpoint(Pager *pPager); +int sqlite3PagerCheckpoint(Pager *pPager, int, int*, int*); int sqlite3PagerWalSupported(Pager *pPager); int sqlite3PagerWalCallback(Pager *pPager); int sqlite3PagerOpenWal(Pager *pPager, int *pisOpen); int sqlite3PagerCloseWal(Pager *pPager); Index: src/pragma.c ================================================================== --- src/pragma.c +++ src/pragma.c @@ -1392,17 +1392,33 @@ }else #endif /* SQLITE_OMIT_COMPILEOPTION_DIAGS */ #ifndef SQLITE_OMIT_WAL /* - ** PRAGMA [database.]wal_checkpoint + ** PRAGMA [database.]wal_checkpoint = passive|full|restart ** ** Checkpoint the database. */ if( sqlite3StrICmp(zLeft, "wal_checkpoint")==0 ){ + int iBt = (pId2->z?iDb:SQLITE_MAX_ATTACHED); + int eMode = SQLITE_CHECKPOINT_PASSIVE; + if( zRight ){ + if( sqlite3StrICmp(zRight, "full")==0 ){ + eMode = SQLITE_CHECKPOINT_FULL; + }else if( sqlite3StrICmp(zRight, "restart")==0 ){ + eMode = SQLITE_CHECKPOINT_RESTART; + } + } if( sqlite3ReadSchema(pParse) ) goto pragma_out; - sqlite3VdbeAddOp3(v, OP_Checkpoint, pId2->z?iDb:SQLITE_MAX_ATTACHED, 0, 0); + sqlite3VdbeSetNumCols(v, 3); + pParse->nMem = 3; + sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "busy", SQLITE_STATIC); + sqlite3VdbeSetColName(v, 1, COLNAME_NAME, "log", SQLITE_STATIC); + sqlite3VdbeSetColName(v, 2, COLNAME_NAME, "checkpointed", SQLITE_STATIC); + + sqlite3VdbeAddOp2(v, OP_Checkpoint, iBt, eMode); + sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 3); }else /* ** PRAGMA wal_autocheckpoint ** PRAGMA wal_autocheckpoint = N Index: src/sqlite.h.in ================================================================== --- src/sqlite.h.in +++ src/sqlite.h.in @@ -6176,10 +6176,93 @@ ** from SQL. ^The [sqlite3_wal_autocheckpoint()] interface and the ** [wal_autocheckpoint pragma] can be used to cause this interface to be ** run whenever the WAL reaches a certain size threshold. */ int sqlite3_wal_checkpoint(sqlite3 *db, const char *zDb); + +/* +** +** CAPI3REF: Checkpoint a database +** +** Run a checkpoint operation on WAL database zDb attached to database +** handle db. The specific operation is determined by the value of the +** eMode parameter: +** +**
+**
SQLITE_CHECKPOINT_PASSIVE
+** Checkpoint as many frames as possible without waiting for any database +** readers or writers to finish. Sync the db file if all frames in the log +** are checkpointed. This mode is the same as calling +** sqlite3_wal_checkpoint(). The busy-handler callback is never invoked. +** +**
SQLITE_CHECKPOINT_FULL
+** This mode blocks (calls the busy-handler callback) until there is no +** database writer and all readers are reading from the most recent database +** snapshot. It then checkpoints all frames in the log file and syncs the +** database file. This call blocks database writers while it is running, +** but not database readers. +** +**
SQLITE_CHECKPOINT_RESTART
+** This mode works the same way as SQLITE_CHECKPOINT_FULL, except after +** checkpointing the log file it blocks (calls the busy-handler callback) +** until all readers are reading from the database file only. This ensures +** that the next client to write to the database file restarts the log file +** from the beginning. This call blocks database writers while it is running, +** but not database readers. +**
+** +** If pnLog is not NULL, then *pnLog is set to the total number of frames in +** the log file before returning. If pnCkpt is not NULL, then *pnCkpt is set to +** the total number of checkpointed frames (including any that were already +** checkpointed when this function is called). *pnLog and *pnCkpt may be +** populated even if sqlite3_wal_checkpoint_v2() returns other than SQLITE_OK. +** If no values are available because of an error, they are both set to -1 +** before returning to communicate this to the caller. +** +** All calls obtain an exclusive "checkpoint" lock on the database file. If +** any other process is running a checkpoint operation at the same time, the +** lock cannot be obtained and SQLITE_BUSY is returned. Even if there is a +** busy-handler configured, it will not be invoked in this case. +** +** The SQLITE_CHECKPOINT_FULL and RESTART modes also obtain the exclusive +** "writer" lock on the database file. If the writer lock cannot be obtained +** immediately, and a busy-handler is configured, it is invoked and the writer +** lock retried until either the busy-handler returns 0 or the lock is +** successfully obtained. The busy-handler is also invoked while waiting for +** database readers as described above. If the busy-handler returns 0 before +** the writer lock is obtained or while waiting for database readers, the +** checkpoint operation proceeds from that point in the same way as +** SQLITE_CHECKPOINT_PASSIVE - checkpointing as many frames as possible +** without blocking any further. SQLITE_BUSY is returned in this case. +** +** If parameter zDb is NULL or points to a zero length string, then the +** specified operation is attempted on all WAL databases. In this case the +** values written to output parameters *pnLog and *pnCkpt are undefined. If +** an SQLITE_BUSY error is encountered when processing one or more of the +** attached WAL databases, the operation is still attempted on any remaining +** attached databases and SQLITE_BUSY is returned to the caller. If any other +** error occurs while processing an attached database, processing is abandoned +** and the error code returned to the caller immediately. If no error +** (SQLITE_BUSY or otherwise) is encountered while processing the attached +** databases, SQLITE_OK is returned. +** +** If database zDb is the name of an attached database that is not in WAL +** mode, SQLITE_OK is returned and both *pnLog and *pnCkpt set to -1. If +** zDb is not NULL (or a zero length string) and is not the name of any +** attached database, SQLITE_ERROR is returned to the caller. +*/ +int sqlite3_wal_checkpoint_v2( + sqlite3 *db, /* Database handle */ + const char *zDb, /* Name of attached database (or NULL) */ + int eMode, /* SQLITE_CHECKPOINT_* value */ + int *pnLog, /* OUT: Size of WAL log in frames */ + int *pnCkpt /* OUT: Total number of frames checkpointed */ +); +#define SQLITE_CHECKPOINT_PASSIVE 0 +#define SQLITE_CHECKPOINT_FULL 1 +#define SQLITE_CHECKPOINT_RESTART 2 + /* ** Undo the hack that converts floating point types to integer for ** builds on processors without floating point support. */ Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -3035,11 +3035,11 @@ void sqlite3ExprListCheckLength(Parse*, ExprList*, const char*); CollSeq *sqlite3BinaryCompareCollSeq(Parse *, Expr *, Expr *); int sqlite3TempInMemory(const sqlite3*); VTable *sqlite3GetVTable(sqlite3*, Table*); const char *sqlite3JournalModename(int); -int sqlite3Checkpoint(sqlite3*, int); +int sqlite3Checkpoint(sqlite3*, int, int, int*, int*); int sqlite3WalDefaultHook(void*,sqlite3*,const char*,int); /* Declarations for functions in fkey.c. All of these are replaced by ** no-op macros if OMIT_FOREIGN_KEY is defined. In this case no foreign ** key functionality is available. If OMIT_TRIGGER is defined but Index: src/vdbe.c ================================================================== --- src/vdbe.c +++ src/vdbe.c @@ -5214,17 +5214,37 @@ } break; } #ifndef SQLITE_OMIT_WAL -/* Opcode: Checkpoint P1 * * * * +/* Opcode: Checkpoint P1 P2 P3 * * ** ** Checkpoint database P1. This is a no-op if P1 is not currently in -** WAL mode. +** WAL mode. Parameter P2 is one of SQLITE_CHECKPOINT_PASSIVE, FULL +** or RESTART. */ case OP_Checkpoint: { - rc = sqlite3Checkpoint(db, pOp->p1); + int nLog = -1; /* Number of pages in WAL log */ + int nCkpt = -1; /* Number of checkpointed pages */ + int bBusy = 0; + assert( pOp->p2==SQLITE_CHECKPOINT_PASSIVE + || pOp->p2==SQLITE_CHECKPOINT_FULL + || pOp->p2==SQLITE_CHECKPOINT_RESTART + ); + rc = sqlite3Checkpoint(db, pOp->p1, pOp->p2, &nLog, &nCkpt); + if( rc==SQLITE_BUSY ){ + rc = SQLITE_OK; + bBusy = 1; + } + + aMem[1].u.i = bBusy; + aMem[2].u.i = nLog; + aMem[3].u.i = nCkpt; + MemSetTypeFlag(&aMem[1], MEM_Int); + MemSetTypeFlag(&aMem[2], MEM_Int); + MemSetTypeFlag(&aMem[3], MEM_Int); + break; }; #endif #ifndef SQLITE_OMIT_PRAGMA Index: src/wal.c ================================================================== --- src/wal.c +++ src/wal.c @@ -1520,10 +1520,38 @@ walIteratorFree(p); } *pp = p; return rc; } + +/* +** Attempt to obtain the exclusive WAL lock defined by parameters lockIdx and +** n. If the attempt fails and parameter xBusy is not NULL, then it is a +** busy-handler function. Invoke it and retry the lock until either the +** lock is successfully obtained or the busy-handler returns 0. +*/ +static int walBusyLock( + Wal *pWal, /* WAL connection */ + int (*xBusy)(void*), /* Function to call when busy */ + void *pBusyArg, /* Context argument for xBusyHandler */ + int lockIdx, /* Offset of first byte to lock */ + int n /* Number of bytes to lock */ +){ + int rc; + do { + rc = walLockExclusive(pWal, lockIdx, n); + }while( xBusy && rc==SQLITE_BUSY && xBusy(pBusyArg) ); + return rc; +} + +/* +** The cache of the wal-index header must be valid to call this function. +** Return the page-size in bytes used by the database. +*/ +static int walPagesize(Wal *pWal){ + return (pWal->hdr.szPage&0xfe00) + ((pWal->hdr.szPage&0x0001)<<16); +} /* ** Copy as much content as we can from the WAL back into the database file ** in response to an sqlite3_wal_checkpoint() request or the equivalent. ** @@ -1554,13 +1582,16 @@ ** checkpoint is running (in any other thread or process) at the same ** time. */ static int walCheckpoint( Wal *pWal, /* Wal connection */ + int eMode, /* One of PASSIVE, FULL or RESTART */ + int (*xBusyCall)(void*), /* Function to call when busy */ + void *pBusyArg, /* Context argument for xBusyHandler */ int sync_flags, /* Flags for OsSync() (or 0) */ - int nBuf, /* Size of zBuf in bytes */ - u8 *zBuf /* Temporary buffer to use */ + u8 *zBuf, /* Temporary buffer to use */ + int *pnCkpt /* Total frames checkpointed */ ){ int rc; /* Return code */ int szPage; /* Database page-size */ WalIterator *pIter = 0; /* Wal iterator context */ u32 iDbpage = 0; /* Next database page to write */ @@ -1567,55 +1598,53 @@ u32 iFrame = 0; /* Wal frame containing data for iDbpage */ u32 mxSafeFrame; /* Max frame that can be backfilled */ u32 mxPage; /* Max database page to write */ int i; /* Loop counter */ volatile WalCkptInfo *pInfo; /* The checkpoint status information */ + int (*xBusy)(void*) = 0; /* Function to call when waiting for locks */ - szPage = (pWal->hdr.szPage&0xfe00) + ((pWal->hdr.szPage&0x0001)<<16); + szPage = walPagesize(pWal); testcase( szPage<=32768 ); testcase( szPage>=65536 ); - if( pWal->hdr.mxFrame==0 ) return SQLITE_OK; /* Allocate the iterator */ rc = walIteratorInit(pWal, &pIter); if( rc!=SQLITE_OK ){ return rc; } assert( pIter ); - /*** TODO: Move this test out to the caller. Make it an assert() here ***/ - if( szPage!=nBuf ){ - rc = SQLITE_CORRUPT_BKPT; - goto walcheckpoint_out; - } + pInfo = walCkptInfo(pWal); + mxPage = pWal->hdr.nPage; + if( pnCkpt ) *pnCkpt = pInfo->nBackfill; + if( eMode!=SQLITE_CHECKPOINT_PASSIVE ) xBusy = xBusyCall; /* Compute in mxSafeFrame the index of the last frame of the WAL that is ** safe to write into the database. Frames beyond mxSafeFrame might ** overwrite database pages that are in use by active readers and thus ** cannot be backfilled from the WAL. */ mxSafeFrame = pWal->hdr.mxFrame; - mxPage = pWal->hdr.nPage; - pInfo = walCkptInfo(pWal); for(i=1; iaReadMark[i]; - if( mxSafeFrame>=y ){ + if( mxSafeFrame>y ){ assert( y<=pWal->hdr.mxFrame ); - rc = walLockExclusive(pWal, WAL_READ_LOCK(i), 1); + rc = walBusyLock(pWal, xBusy, pBusyArg, WAL_READ_LOCK(i), 1); if( rc==SQLITE_OK ){ pInfo->aReadMark[i] = READMARK_NOT_USED; walUnlockExclusive(pWal, WAL_READ_LOCK(i), 1); }else if( rc==SQLITE_BUSY ){ mxSafeFrame = y; + xBusy = 0; }else{ goto walcheckpoint_out; } } } if( pInfo->nBackfillnBackfill; /* Sync the WAL to disk */ @@ -1659,21 +1688,41 @@ rc = sqlite3OsSync(pWal->pDbFd, sync_flags); } } if( rc==SQLITE_OK ){ pInfo->nBackfill = mxSafeFrame; + if( pnCkpt ) *pnCkpt = mxSafeFrame; } } /* Release the reader lock held while backfilling */ walUnlockExclusive(pWal, WAL_READ_LOCK(0), 1); - }else if( rc==SQLITE_BUSY ){ + } + + if( rc==SQLITE_BUSY ){ /* Reset the return code so as not to report a checkpoint failure - ** just because active readers prevent any backfill. - */ + ** just because there are active readers. */ rc = SQLITE_OK; } + + /* If this is an SQLITE_CHECKPOINT_RESTART operation, and the entire wal + ** file has been copied into the database file, then block until all + ** readers have finished using the wal file. This ensures that the next + ** process to write to the database restarts the wal file. + */ + if( rc==SQLITE_OK && eMode!=SQLITE_CHECKPOINT_PASSIVE ){ + assert( pWal->writeLock ); + if( pInfo->nBackfillhdr.mxFrame ){ + rc = SQLITE_BUSY; + }else if( eMode==SQLITE_CHECKPOINT_RESTART ){ + assert( mxSafeFrame==pWal->hdr.mxFrame ); + rc = walBusyLock(pWal, xBusy, pBusyArg, WAL_READ_LOCK(1), WAL_NREADER-1); + if( rc==SQLITE_OK ){ + walUnlockExclusive(pWal, WAL_READ_LOCK(1), WAL_NREADER-1); + } + } + } walcheckpoint_out: walIteratorFree(pIter); return rc; } @@ -1702,11 +1751,13 @@ rc = sqlite3OsLock(pWal->pDbFd, SQLITE_LOCK_EXCLUSIVE); if( rc==SQLITE_OK ){ if( pWal->exclusiveMode==WAL_NORMAL_MODE ){ pWal->exclusiveMode = WAL_EXCLUSIVE_MODE; } - rc = sqlite3WalCheckpoint(pWal, sync_flags, nBuf, zBuf); + rc = sqlite3WalCheckpoint( + pWal, SQLITE_CHECKPOINT_PASSIVE, 0, 0, sync_flags, nBuf, zBuf, 0, 0 + ); if( rc==SQLITE_OK ){ isDelete = 1; } } @@ -2617,21 +2668,31 @@ ** This routine is called to implement sqlite3_wal_checkpoint() and ** related interfaces. ** ** Obtain a CHECKPOINT lock and then backfill as much information as ** we can from WAL into the database. +** +** If parameter xBusy is not NULL, it is a pointer to a busy-handler +** callback. In this case this function runs a blocking checkpoint. */ int sqlite3WalCheckpoint( Wal *pWal, /* Wal connection */ + int eMode, /* PASSIVE, FULL or RESTART */ + int (*xBusy)(void*), /* Function to call when busy */ + void *pBusyArg, /* Context argument for xBusyHandler */ int sync_flags, /* Flags to sync db file with (or 0) */ int nBuf, /* Size of temporary buffer */ - u8 *zBuf /* Temporary buffer to use */ + u8 *zBuf, /* Temporary buffer to use */ + int *pnLog, /* OUT: Number of frames in WAL */ + int *pnCkpt /* OUT: Number of backfilled frames in WAL */ ){ int rc; /* Return code */ int isChanged = 0; /* True if a new wal-index header is loaded */ + int eMode2 = eMode; /* Mode to pass to walCheckpoint() */ assert( pWal->ckptLock==0 ); + assert( pWal->writeLock==0 ); WALTRACE(("WAL%p: checkpoint begins\n", pWal)); rc = walLockExclusive(pWal, WAL_CKPT_LOCK, 1); if( rc ){ /* Usually this is SQLITE_BUSY meaning that another thread or process @@ -2639,15 +2700,44 @@ ** also be SQLITE_IOERR. */ return rc; } pWal->ckptLock = 1; - /* Copy data from the log to the database file. */ - rc = walIndexReadHdr(pWal, &isChanged); + /* If this is a blocking-checkpoint, then obtain the write-lock as well + ** to prevent any writers from running while the checkpoint is underway. + ** This has to be done before the call to walIndexReadHdr() below. + ** + ** If the writer lock cannot be obtained, then a passive checkpoint is + ** run instead. Since the checkpointer is not holding the writer lock, + ** there is no point in blocking waiting for any readers. Assuming no + ** other error occurs, this function will return SQLITE_BUSY to the caller. + */ + if( eMode!=SQLITE_CHECKPOINT_PASSIVE ){ + rc = walBusyLock(pWal, xBusy, pBusyArg, WAL_WRITE_LOCK, 1); + if( rc==SQLITE_OK ){ + pWal->writeLock = 1; + }else if( rc==SQLITE_BUSY ){ + eMode2 = SQLITE_CHECKPOINT_PASSIVE; + rc = SQLITE_OK; + } + } + + /* Read the wal-index header. */ if( rc==SQLITE_OK ){ - rc = walCheckpoint(pWal, sync_flags, nBuf, zBuf); + rc = walIndexReadHdr(pWal, &isChanged); + } + + /* Copy data from the log to the database file. */ + if( rc==SQLITE_OK && pWal->hdr.mxFrame ){ + if( walPagesize(pWal)!=nBuf ){ + rc = SQLITE_CORRUPT_BKPT; + }else{ + if( pnLog ) *pnLog = (int)pWal->hdr.mxFrame; + rc = walCheckpoint(pWal, eMode2, xBusy, pBusyArg, sync_flags,zBuf,pnCkpt); + } } + if( isChanged ){ /* If a new wal-index header was loaded before the checkpoint was ** performed, then the pager-cache associated with pWal is now ** out of date. So zero the cached wal-index header to ensure that ** next time the pager opens a snapshot on this database it knows that @@ -2655,14 +2745,15 @@ */ memset(&pWal->hdr, 0, sizeof(WalIndexHdr)); } /* Release the locks. */ + sqlite3WalEndWriteTransaction(pWal); walUnlockExclusive(pWal, WAL_CKPT_LOCK, 1); pWal->ckptLock = 0; WALTRACE(("WAL%p: checkpoint %s\n", pWal, rc ? "failed" : "ok")); - return rc; + return (rc==SQLITE_OK && eMode!=eMode2 ? SQLITE_BUSY : rc); } /* Return the value to pass to a sqlite3_wal_hook callback, the ** number of frames in the WAL at the point of the last commit since ** sqlite3WalCallback() was called. If no commits have occurred since Index: src/wal.h ================================================================== --- src/wal.h +++ src/wal.h @@ -18,26 +18,26 @@ #define _WAL_H_ #include "sqliteInt.h" #ifdef SQLITE_OMIT_WAL -# define sqlite3WalOpen(x,y,z) 0 -# define sqlite3WalClose(w,x,y,z) 0 -# define sqlite3WalBeginReadTransaction(y,z) 0 +# define sqlite3WalOpen(x,y,z) 0 +# define sqlite3WalClose(w,x,y,z) 0 +# define sqlite3WalBeginReadTransaction(y,z) 0 # define sqlite3WalEndReadTransaction(z) -# define sqlite3WalRead(v,w,x,y,z) 0 -# define sqlite3WalDbsize(y) 0 -# define sqlite3WalBeginWriteTransaction(y) 0 -# define sqlite3WalEndWriteTransaction(x) 0 -# define sqlite3WalUndo(x,y,z) 0 +# define sqlite3WalRead(v,w,x,y,z) 0 +# define sqlite3WalDbsize(y) 0 +# define sqlite3WalBeginWriteTransaction(y) 0 +# define sqlite3WalEndWriteTransaction(x) 0 +# define sqlite3WalUndo(x,y,z) 0 # define sqlite3WalSavepoint(y,z) -# define sqlite3WalSavepointUndo(y,z) 0 -# define sqlite3WalFrames(u,v,w,x,y,z) 0 -# define sqlite3WalCheckpoint(u,v,w,x) 0 -# define sqlite3WalCallback(z) 0 -# define sqlite3WalExclusiveMode(y,z) 0 -# define sqlite3WalHeapMemory(z) 0 +# define sqlite3WalSavepointUndo(y,z) 0 +# define sqlite3WalFrames(u,v,w,x,y,z) 0 +# define sqlite3WalCheckpoint(r,s,t,u,v,w,x,y,z) 0 +# define sqlite3WalCallback(z) 0 +# define sqlite3WalExclusiveMode(y,z) 0 +# define sqlite3WalHeapMemory(z) 0 #else #define WAL_SAVEPOINT_NDATA 4 /* Connection to a write-ahead log (WAL) file. @@ -84,13 +84,18 @@ int sqlite3WalFrames(Wal *pWal, int, PgHdr *, Pgno, int, int); /* Copy pages from the log to the database file */ int sqlite3WalCheckpoint( Wal *pWal, /* Write-ahead log connection */ + int eMode, /* One of PASSIVE, FULL and RESTART */ + int (*xBusy)(void*), /* Function to call when busy */ + void *pBusyArg, /* Context argument for xBusyHandler */ int sync_flags, /* Flags to sync db file with (or 0) */ int nBuf, /* Size of buffer nBuf */ - u8 *zBuf /* Temporary buffer to use */ + u8 *zBuf, /* Temporary buffer to use */ + int *pnLog, /* OUT: Number of frames in WAL */ + int *pnCkpt /* OUT: Number of backfilled frames in WAL */ ); /* Return the value to pass to a sqlite3_wal_hook callback, the ** number of frames in the WAL at the point of the last commit since ** sqlite3WalCallback() was called. If no commits have occurred since Index: test/attach.test ================================================================== --- test/attach.test +++ test/attach.test @@ -833,6 +833,7 @@ do_test attach-10.2 { lrange [execsql { PRAGMA database_list; }] 9 end } {4 noname {} 5 inmem {}} + finish_test Index: test/pager1.test ================================================================== --- test/pager1.test +++ test/pager1.test @@ -1990,11 +1990,11 @@ execsql { CREATE TABLE ko(c DEFAULT 'abc', b DEFAULT 'def'); INSERT INTO ko DEFAULT VALUES; } execsql { PRAGMA wal_checkpoint } -} {} +} {0 -1 -1} do_test pager1-22.2.1 { testvfs tv -default 1 tv filter xSync tv script xSyncCb proc xSyncCb {args} {incr ::synccount} Index: test/threadtest3.c ================================================================== --- test/threadtest3.c +++ test/threadtest3.c @@ -1392,10 +1392,11 @@ join_all_threads(&err, &threads); print_and_free_err(&err); } +#include "tt3_checkpoint.c" int main(int argc, char **argv){ struct ThreadTest { void (*xTest)(int); const char *zTest; @@ -1406,12 +1407,15 @@ { walthread3, "walthread3", 20000 }, { walthread4, "walthread4", 20000 }, { walthread5, "walthread5", 1000 }, { walthread5, "walthread5", 1000 }, - { cgt_pager_1, "cgt_pager_1", 0 }, + { cgt_pager_1, "cgt_pager_1", 0 }, { dynamic_triggers, "dynamic_triggers", 20000 }, + + { checkpoint_starvation_1, "checkpoint_starvation_1", 10000 }, + { checkpoint_starvation_2, "checkpoint_starvation_2", 10000 }, }; int i; char *zTest = 0; int nTest = 0; ADDED test/tt3_checkpoint.c Index: test/tt3_checkpoint.c ================================================================== --- /dev/null +++ test/tt3_checkpoint.c @@ -0,0 +1,150 @@ +/* +** 2001 September 15 +** +** The author disclaims copyright to this source code. In place of +** a legal notice, here is a blessing: +** +** May you do good and not evil. +** May you find forgiveness for yourself and forgive others. +** May you share freely, never taking more than you give. +** +************************************************************************* +** This file is part of the test program "threadtest3". Despite being a C +** file it is not compiled separately, but included by threadtest3.c using +** the #include directive normally used with header files. +** +** This file contains the implementation of test cases: +** +** checkpoint_starvation_1 +** checkpoint_starvation_2 +*/ + +/* +** Both test cases involve 1 writer/checkpointer thread and N reader threads. +** +** Each reader thread performs a series of read transactions, one after +** another. Each read transaction lasts for 100 ms. +** +** The writer writes transactions as fast as possible. It uses a callback +** registered with sqlite3_wal_hook() to try to keep the WAL-size limited to +** around 50 pages. +** +** In test case checkpoint_starvation_1, the auto-checkpoint uses +** SQLITE_CHECKPOINT_PASSIVE. In checkpoint_starvation_2, it uses RESTART. +** The expectation is that in the first case the WAL file will grow very +** large, and in the second will be limited to the 50 pages or thereabouts. +** However, the overall transaction throughput will be lower for +** checkpoint_starvation_2, as every checkpoint will block for up to 200 ms +** waiting for readers to clear. +*/ + +/* Frame limit used by the WAL hook for these tests. */ +#define CHECKPOINT_STARVATION_FRAMELIMIT 50 + +/* Duration in ms of each read transaction */ +#define CHECKPOINT_STARVATION_READMS 100 + +struct CheckpointStarvationCtx { + int eMode; + int nMaxFrame; +}; +typedef struct CheckpointStarvationCtx CheckpointStarvationCtx; + +static int checkpoint_starvation_walhook( + void *pCtx, + sqlite3 *db, + const char *zDb, + int nFrame +){ + CheckpointStarvationCtx *p = (CheckpointStarvationCtx *)pCtx; + if( nFrame>p->nMaxFrame ){ + p->nMaxFrame = nFrame; + } + if( nFrame>=CHECKPOINT_STARVATION_FRAMELIMIT ){ + sqlite3_wal_checkpoint_v2(db, zDb, p->eMode, 0, 0); + } + return SQLITE_OK; +} + +static char *checkpoint_starvation_reader(int iTid, int iArg){ + Error err = {0}; + Sqlite db = {0}; + + opendb(&err, &db, "test.db", 0); + while( !timetostop(&err) ){ + i64 iCount1, iCount2; + sql_script(&err, &db, "BEGIN"); + iCount1 = execsql_i64(&err, &db, "SELECT count(x) FROM t1"); + usleep(CHECKPOINT_STARVATION_READMS*1000); + iCount2 = execsql_i64(&err, &db, "SELECT count(x) FROM t1"); + sql_script(&err, &db, "COMMIT"); + + if( iCount1!=iCount2 ){ + test_error(&err, "Isolation failure - %lld %lld", iCount1, iCount2); + } + } + closedb(&err, &db); + + print_and_free_err(&err); + return 0; +} + +static void checkpoint_starvation_main(int nMs, CheckpointStarvationCtx *p){ + Error err = {0}; + Sqlite db = {0}; + Threadset threads = {0}; + int nInsert = 0; + int i; + + opendb(&err, &db, "test.db", 1); + sql_script(&err, &db, + "PRAGMA page_size = 1024;" + "PRAGMA journal_mode = WAL;" + "CREATE TABLE t1(x);" + ); + + setstoptime(&err, nMs); + + for(i=0; i<4; i++){ + launch_thread(&err, &threads, checkpoint_starvation_reader, 0); + usleep(CHECKPOINT_STARVATION_READMS*1000/4); + } + + sqlite3_wal_hook(db.db, checkpoint_starvation_walhook, (void *)p); + while( !timetostop(&err) ){ + sql_script(&err, &db, "INSERT INTO t1 VALUES(randomblob(1200))"); + nInsert++; + } + + printf(" Checkpoint mode : %s\n", + p->eMode==SQLITE_CHECKPOINT_PASSIVE ? "PASSIVE" : "RESTART" + ); + printf(" Peak WAL : %d frames\n", p->nMaxFrame); + printf(" Transaction count: %d transactions\n", nInsert); + + join_all_threads(&err, &threads); + closedb(&err, &db); + print_and_free_err(&err); +} + +static void checkpoint_starvation_1(int nMs){ + Error err = {0}; + CheckpointStarvationCtx ctx = { SQLITE_CHECKPOINT_PASSIVE, 0 }; + checkpoint_starvation_main(nMs, &ctx); + if( ctx.nMaxFrame<(CHECKPOINT_STARVATION_FRAMELIMIT*10) ){ + test_error(&err, "WAL failed to grow - %d frames", ctx.nMaxFrame); + } + print_and_free_err(&err); +} + +static void checkpoint_starvation_2(int nMs){ + Error err = {0}; + CheckpointStarvationCtx ctx = { SQLITE_CHECKPOINT_RESTART, 0 }; + checkpoint_starvation_main(nMs, &ctx); + if( ctx.nMaxFrame>CHECKPOINT_STARVATION_FRAMELIMIT+10 ){ + test_error(&err, "WAL grew too large - %d frames", ctx.nMaxFrame); + } + print_and_free_err(&err); +} + + Index: test/wal.test ================================================================== --- test/wal.test +++ test/wal.test @@ -285,12 +285,12 @@ INSERT INTO t2 VALUES('w', 'x'); SAVEPOINT save; INSERT INTO t2 VALUES('y', 'z'); ROLLBACK TO save; COMMIT; - SELECT * FROM t2; } + execsql { SELECT * FROM t2 } } {w x} reopen_db do_test wal-5.1 { @@ -540,34 +540,34 @@ do_test wal-10.$tn.11 { sql2 { BEGIN; SELECT * FROM t1 } } {1 2 3 4 5 6 7 8 9 10} do_test wal-10.$tn.12 { catchsql { PRAGMA wal_checkpoint } - } {0 {}} ;# Reader no longer block checkpoints + } {0 {0 13 13}} ;# Reader no longer block checkpoints do_test wal-10.$tn.13 { execsql { INSERT INTO t1 VALUES(11, 12) } sql2 {SELECT * FROM t1} } {1 2 3 4 5 6 7 8 9 10} # Writers do not block checkpoints any more either. # do_test wal-10.$tn.14 { catchsql { PRAGMA wal_checkpoint } - } {0 {}} + } {0 {0 15 13}} # The following series of test cases used to verify another blocking # case in WAL - a case which no longer blocks. # do_test wal-10.$tn.15 { sql2 { COMMIT; BEGIN; SELECT * FROM t1; } } {1 2 3 4 5 6 7 8 9 10 11 12} do_test wal-10.$tn.16 { catchsql { PRAGMA wal_checkpoint } - } {0 {}} + } {0 {0 15 15}} do_test wal-10.$tn.17 { execsql { PRAGMA wal_checkpoint } - } {} + } {0 15 15} do_test wal-10.$tn.18 { sql3 { BEGIN; SELECT * FROM t1 } } {1 2 3 4 5 6 7 8 9 10 11 12} do_test wal-10.$tn.19 { catchsql { INSERT INTO t1 VALUES(13, 14) } @@ -586,17 +586,17 @@ # Another series of tests that used to demonstrate blocking behavior # but which now work. # do_test wal-10.$tn.23 { execsql { PRAGMA wal_checkpoint } - } {} + } {0 17 17} do_test wal-10.$tn.24 { sql2 { BEGIN; SELECT * FROM t1; } } {1 2 3 4 5 6 7 8 9 10 11 12 13 14} do_test wal-10.$tn.25 { execsql { PRAGMA wal_checkpoint } - } {} + } {0 17 17} do_test wal-10.$tn.26 { catchsql { INSERT INTO t1 VALUES(15, 16) } } {0 {}} do_test wal-10.$tn.27 { sql3 { INSERT INTO t1 VALUES(17, 18) } @@ -609,15 +609,15 @@ execsql { SELECT * FROM t1 } } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18} do_test wal-10.$tn.29 { execsql { INSERT INTO t1 VALUES(19, 20) } catchsql { PRAGMA wal_checkpoint } - } {0 {}} + } {0 {0 6 0}} do_test wal-10.$tn.30 { code3 { sqlite3_finalize $::STMT } execsql { PRAGMA wal_checkpoint } - } {} + } {0 6 0} # At one point, if a reader failed to upgrade to a writer because it # was reading an old snapshot, the write-locks were not being released. # Test that this bug has been fixed. # @@ -652,19 +652,19 @@ SELECT * FROM t1; } } {a b c d} do_test wal-10.$tn.36 { catchsql { PRAGMA wal_checkpoint } - } {0 {}} + } {0 {0 16 16}} do_test wal-10.$tn.36 { sql3 { INSERT INTO t1 VALUES('e', 'f') } sql2 { SELECT * FROM t1 } } {a b c d} do_test wal-10.$tn.37 { sql2 COMMIT execsql { PRAGMA wal_checkpoint } - } {} + } {0 18 18} } #------------------------------------------------------------------------- # This block of tests, wal-11.*, test that nothing goes terribly wrong # if frames must be written to the log file before a transaction is @@ -795,12 +795,12 @@ UPDATE t2 SET y = 2 WHERE x = 'B'; PRAGMA wal_checkpoint; UPDATE t1 SET y = 1 WHERE x = 'A'; PRAGMA wal_checkpoint; UPDATE t1 SET y = 0 WHERE x = 'A'; - SELECT * FROM t2; } + execsql { SELECT * FROM t2 } } {B 2} do_test wal-12.6 { file copy -force test.db test2.db file copy -force test.db-wal test2.db-wal sqlite3_wal db2 test2.db @@ -843,10 +843,11 @@ puts $fd "" close $fd sqlite3 db test.db execsql { SELECT * FROM t2 } } {B 2} +breakpoint do_test wal-13.1.3 { db close file exists test.db-wal } {0} @@ -1027,18 +1028,18 @@ # API, an attempt is made to checkpoint all attached databases. # foreach {tn ckpt_cmd ckpt_res ckpt_main ckpt_aux} { 1 {sqlite3_wal_checkpoint db} SQLITE_OK 1 1 2 {sqlite3_wal_checkpoint db ""} SQLITE_OK 1 1 - 3 {db eval "PRAGMA wal_checkpoint"} {} 1 1 + 3 {db eval "PRAGMA wal_checkpoint"} {0 10 10} 1 1 4 {sqlite3_wal_checkpoint db main} SQLITE_OK 1 0 5 {sqlite3_wal_checkpoint db aux} SQLITE_OK 0 1 6 {sqlite3_wal_checkpoint db temp} SQLITE_OK 0 0 - 7 {db eval "PRAGMA main.wal_checkpoint"} {} 1 0 - 8 {db eval "PRAGMA aux.wal_checkpoint"} {} 0 1 - 9 {db eval "PRAGMA temp.wal_checkpoint"} {} 0 0 + 7 {db eval "PRAGMA main.wal_checkpoint"} {0 10 10} 1 0 + 8 {db eval "PRAGMA aux.wal_checkpoint"} {0 16 16} 0 1 + 9 {db eval "PRAGMA temp.wal_checkpoint"} {0 -1 -1} 0 0 } { do_test wal-16.$tn.1 { file delete -force test2.db test2.db-wal test2.db-journal file delete -force test.db test.db-wal test.db-journal @@ -1398,14 +1399,12 @@ } } } } {0} do_test wal-20.3 { close $::buddy - execsql { - PRAGMA wal_checkpoint; - SELECT count(*) FROM t1; - } + execsql { PRAGMA wal_checkpoint } + execsql { SELECT count(*) FROM t1 } } {16384} do_test wal-20.4 { db close sqlite3 db test.db execsql { SELECT count(*) FROM t1 } @@ -1435,12 +1434,12 @@ BEGIN; SAVEPOINT s; INSERT INTO t1 SELECT randomblob(900), randomblob(900) FROM t1; ROLLBACK TO s; COMMIT; - SELECT * FROM t1; } + execsql { SELECT * FROM t1 } } {1 2 3 4 5 6 7 8 9 10 11 12} do_test wal-21.3 { execsql { PRAGMA integrity_check } } {ok} Index: test/wal2.test ================================================================== --- test/wal2.test +++ test/wal2.test @@ -344,11 +344,11 @@ PRAGMA journal_mode = WAL; CREATE TABLE data(x); INSERT INTO data VALUES('need xShmOpen to see this'); PRAGMA wal_checkpoint; } -} {wal} +} {wal 0 5 5} do_test wal2-4.2 { db close testvfs tvfs -noshm 1 sqlite3 db test.db -vfs tvfs catchsql { SELECT * FROM data } @@ -710,11 +710,11 @@ CREATE TABLE t2(a, b); PRAGMA wal_checkpoint; INSERT INTO t2 VALUES('I', 'II'); PRAGMA journal_mode; } -} {wal exclusive wal} +} {wal exclusive 0 3 3 wal} do_test wal2-6.5.2 { execsql { PRAGMA locking_mode = normal; INSERT INTO t2 VALUES('III', 'IV'); PRAGMA locking_mode = exclusive; @@ -721,11 +721,11 @@ SELECT * FROM t2; } } {normal exclusive I II III IV} do_test wal2-6.5.3 { execsql { PRAGMA wal_checkpoint } -} {} +} {0 4 4} db close proc lock_control {method filename handle spec} { foreach {start n op type} $spec break if {$op == "lock"} { return SQLITE_IOERR } @@ -805,13 +805,13 @@ PRAGMA page_size = 1024; PRAGMA journal_mode = WAL; CREATE TABLE t1(x); INSERT INTO t1 VALUES(zeroblob(8188*1020)); CREATE TABLE t2(y); - } - execsql { PRAGMA wal_checkpoint; + } + execsql { SELECT rootpage>=8192 FROM sqlite_master WHERE tbl_name = 't2'; } } {1} do_test wal2-8.1.3 { execsql { Index: test/wal3.test ================================================================== --- test/wal3.test +++ test/wal3.test @@ -425,11 +425,11 @@ sqlite3 db3 test.db execsql { BEGIN ; SELECT * FROM t1 } db3 } {o t t f} do_test wal3-6.1.3 { execsql { PRAGMA wal_checkpoint } db2 -} {} +} {0 7 7} # At this point the log file has been fully checkpointed. However, # connection [db3] holds a lock that prevents the log from being wrapped. # Test case 3.6.1.4 has [db] attempt a read-lock on aReadMark[0]. But # as it is obtaining the lock, [db2] appends to the log file. @@ -513,11 +513,11 @@ }] } } do_test wal3-6.2.2 { execsql { PRAGMA wal_checkpoint } -} {} +} {0 7 7} do_test wal3-6.2.3 { set ::R } {h h l b} do_test wal3-6.2.4 { set sz1 [file size test.db-wal] @@ -622,11 +622,11 @@ INSERT INTO b VALUES('Tehran'); INSERT INTO b VALUES('Qom'); INSERT INTO b VALUES('Markazi'); PRAGMA wal_checkpoint; } -} {wal} +} {wal 0 9 9} do_test wal3-8.2 { execsql { SELECT * FROM b } } {Tehran Qom Markazi} do_test wal3-8.3 { db eval { SELECT * FROM b } { ADDED test/wal5.test Index: test/wal5.test ================================================================== --- /dev/null +++ test/wal5.test @@ -0,0 +1,262 @@ +# 2010 April 13 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. The +# focus of this file is testing the operation of "blocking-checkpoint" +# operations. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +source $testdir/lock_common.tcl +source $testdir/wal_common.tcl +ifcapable !wal {finish_test ; return } + +set testprefix wal5 + +proc db_page_count {{file test.db}} { expr [file size $file] / 1024 } +proc wal_page_count {{file test.db}} { wal_frame_count ${file}-wal 1024 } + + +do_multiclient_test tn { + + + set ::nBusyHandler 0 + set ::busy_handler_script "" + proc busyhandler {n} { + incr ::nBusyHandler + eval $::busy_handler_script + return 0 + } + + proc reopen_all {} { + code1 {db close} + code2 {db2 close} + code3 {db3 close} + code1 {sqlite3 db test.db} + code2 {sqlite3 db2 test.db} + code3 {sqlite3 db3 test.db} + sql1 { PRAGMA synchronous = NORMAL } + code1 { db busy busyhandler } + } + + do_test 1.$tn.1 { + reopen_all + sql1 { + PRAGMA page_size = 1024; + PRAGMA auto_vacuum = 0; + CREATE TABLE t1(x, y); + PRAGMA journal_mode = WAL; + INSERT INTO t1 VALUES(1, zeroblob(1200)); + INSERT INTO t1 VALUES(2, zeroblob(1200)); + INSERT INTO t1 VALUES(3, zeroblob(1200)); + } + expr [file size test.db] / 1024 + } {2} + + # Have connection 2 grab a read-lock on the current snapshot. + do_test 1.$tn.2 { sql2 { BEGIN; SELECT x FROM t1 } } {1 2 3} + + # Attempt a checkpoint. + do_test 1.$tn.3 { + sql1 { PRAGMA wal_checkpoint } + list [db_page_count] [wal_page_count] + } {5 9} + + # Write to the db again. The log cannot wrap because of the lock still + # held by connection 2. The busy-handler has not yet been invoked. + do_test 1.$tn.4 { + sql1 { INSERT INTO t1 VALUES(4, zeroblob(1200)) } + list [db_page_count] [wal_page_count] $::nBusyHandler + } {5 12 0} + + # Now do a blocking-checkpoint. Set the busy-handler up so that connection + # 2 releases its lock on the 6th invocation. The checkpointer should then + # proceed to checkpoint the entire log file. Next write should go to the + # start of the log file. + # + set ::busy_handler_script { if {$n==5} { sql2 COMMIT } } + do_test 1.$tn.5 { + sql1 { PRAGMA wal_checkpoint = RESTART } + list [db_page_count] [wal_page_count] $::nBusyHandler + } {6 12 6} + do_test 1.$tn.6 { + set ::nBusyHandler 0 + sql1 { INSERT INTO t1 VALUES(5, zeroblob(1200)) } + list [db_page_count] [wal_page_count] $::nBusyHandler + } {6 12 0} + + do_test 1.$tn.7 { + reopen_all + list [db_page_count] [wal_page_count] $::nBusyHandler + } {7 0 0} + + do_test 1.$tn.8 { sql2 { BEGIN ; SELECT x FROM t1 } } {1 2 3 4 5} + do_test 1.$tn.9 { + sql1 { INSERT INTO t1 VALUES(6, zeroblob(1200)) } + list [db_page_count] [wal_page_count] $::nBusyHandler + } {7 5 0} + do_test 1.$tn.10 { sql3 { BEGIN ; SELECT x FROM t1 } } {1 2 3 4 5 6} + + set ::busy_handler_script { + if {$n==5} { sql2 COMMIT } + if {$n==6} { set ::db_file_size [db_page_count] } + if {$n==7} { sql3 COMMIT } + } + do_test 1.$tn.11 { + sql1 { PRAGMA wal_checkpoint = RESTART } + list [db_page_count] [wal_page_count] $::nBusyHandler + } {10 5 8} + do_test 1.$tn.12 { set ::db_file_size } 10 +} + + +#------------------------------------------------------------------------- +# This block of tests explores checkpoint operations on more than one +# database file. +# +proc setup_and_attach_aux {} { + sql1 { ATTACH 'test.db2' AS aux } + sql2 { ATTACH 'test.db2' AS aux } + sql3 { ATTACH 'test.db2' AS aux } + sql1 { + PRAGMA main.page_size=1024; PRAGMA main.journal_mode=WAL; + PRAGMA aux.page_size=1024; PRAGMA aux.journal_mode=WAL; + } +} + +proc file_page_counts {} { + list [db_page_count test.db ] \ + [wal_page_count test.db ] \ + [db_page_count test.db2] \ + [wal_page_count test.db2] +} + +# Test that executing "PRAGMA wal_checkpoint" checkpoints all attached +# databases, not just the main db. +# +do_multiclient_test tn { + setup_and_attach_aux + do_test 2.1.$tn.1 { + sql1 { + CREATE TABLE t1(a, b); + INSERT INTO t1 VALUES(1, 2); + CREATE TABLE aux.t2(a, b); + INSERT INTO t2 VALUES(1, 2); + } + } {} + do_test 2.2.$tn.2 { file_page_counts } {1 5 1 5} + do_test 2.1.$tn.3 { sql1 { PRAGMA wal_checkpoint } } {0 5 5} + do_test 2.1.$tn.4 { file_page_counts } {2 5 2 5} +} + +do_multiclient_test tn { + setup_and_attach_aux + do_test 2.2.$tn.1 { + execsql { + CREATE TABLE t1(a, b); + INSERT INTO t1 VALUES(1, 2); + CREATE TABLE aux.t2(a, b); + INSERT INTO t2 VALUES(1, 2); + INSERT INTO t2 VALUES(3, 4); + } + } {} + do_test 2.2.$tn.2 { file_page_counts } {1 5 1 7} + do_test 2.2.$tn.3 { sql2 { BEGIN; SELECT * FROM t1 } } {1 2} + do_test 2.2.$tn.4 { sql1 { PRAGMA wal_checkpoint = RESTART } } {1 5 5} + do_test 2.2.$tn.5 { file_page_counts } {2 5 2 7} +} + +do_multiclient_test tn { + setup_and_attach_aux + do_test 2.3.$tn.1 { + execsql { + CREATE TABLE t1(a, b); + INSERT INTO t1 VALUES(1, 2); + CREATE TABLE aux.t2(a, b); + INSERT INTO t2 VALUES(1, 2); + } + } {} + do_test 2.3.$tn.2 { file_page_counts } {1 5 1 5} + do_test 2.3.$tn.3 { sql2 { BEGIN; SELECT * FROM t1 } } {1 2} + do_test 2.3.$tn.4 { sql1 { INSERT INTO t1 VALUES(3, 4) } } {} + do_test 2.3.$tn.5 { sql1 { INSERT INTO t2 VALUES(3, 4) } } {} + do_test 2.3.$tn.6 { file_page_counts } {1 7 1 7} + do_test 2.3.$tn.7 { sql1 { PRAGMA wal_checkpoint = FULL } } {1 7 5} + do_test 2.3.$tn.8 { file_page_counts } {1 7 2 7} +} + +# Check that checkpoints block on the correct locks. And respond correctly +# if they cannot obtain those locks. There are three locks that a checkpoint +# may block on (in the following order): +# +# 1. The writer lock: FULL and RESTART checkpoints block until any writer +# process releases its lock. +# +# 2. Readers using part of the log file. FULL and RESTART checkpoints block +# until readers using part (but not all) of the log file have finished. +# +# 3. Readers using any of the log file. After copying data into the +# database file, RESTART checkpoints block until readers using any part +# of the log file have finished. +# +# This test case involves running a checkpoint while there exist other +# processes holding all three types of locks. +# +foreach {tn1 checkpoint busy_on ckpt_expected expected} { + 1 PASSIVE - {0 5 5} - + 2 TYPO - {0 5 5} - + + 3 FULL - {0 7 7} 2 + 4 FULL 1 {1 5 5} 1 + 5 FULL 2 {1 7 5} 2 + 6 FULL 3 {0 7 7} 2 + + 7 RESTART - {0 7 7} 3 + 8 RESTART 1 {1 5 5} 1 + 9 RESTART 2 {1 7 5} 2 + 10 RESTART 3 {1 7 7} 3 + +} { + do_multiclient_test tn { + setup_and_attach_aux + + proc busyhandler {x} { + set ::max_busyhandler $x + if {$::busy_on!="-" && $x==$::busy_on} { return 1 } + switch -- $x { + 1 { sql2 "COMMIT ; BEGIN ; SELECT * FROM t1" } + 2 { sql3 "COMMIT" } + 3 { sql2 "COMMIT" } + } + return 0 + } + set ::max_busyhandler - + + do_test 2.4.$tn1.$tn.1 { + sql1 { + CREATE TABLE t1(a, b); + INSERT INTO t1 VALUES(1, 2); + } + sql2 { BEGIN; INSERT INTO t1 VALUES(3, 4) } + sql3 { BEGIN; SELECT * FROM t1 } + } {1 2} + + do_test 2.4.$tn1.$tn.2 { + code1 { db busy busyhandler } + sql1 "PRAGMA wal_checkpoint = $checkpoint" + } $ckpt_expected + do_test 2.4.$tn1.$tn.3 { set ::max_busyhandler } $expected + } +} + + +finish_test + Index: test/wal_common.tcl ================================================================== --- test/wal_common.tcl +++ test/wal_common.tcl @@ -16,11 +16,13 @@ proc wal_file_size {nFrame pgsz} { expr {32 + ($pgsz+24)*$nFrame} } proc wal_frame_count {zFile pgsz} { + if {[file exists $zFile]==0} { return 0 } set f [file size $zFile] + if {$f < 32} { return 0 } expr {($f - 32) / ($pgsz+24)} } proc wal_cksum_intlist {ckv1 ckv2 intlist} { upvar $ckv1 c1