SQLite

Check-in [174a6076a8]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add experimental sqlite3_snapshot_recover() API.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | serializable-snapshot
Files: files | file ages | folders
SHA1: 174a6076a8d7bebe5efebf55f3fdc5d87c589cc7
User & Date: dan 2016-11-18 20:49:43.736
Context
2016-11-19
14:53
Fix a bug in sqlite3_snapshot_recover() that could cause subsequent read transactions to use out-of-data cache entries. (check-in: 9abeb7980a user: dan tags: serializable-snapshot)
2016-11-18
20:49
Add experimental sqlite3_snapshot_recover() API. (check-in: 174a6076a8 user: dan tags: serializable-snapshot)
18:43
Require that the database handle be in autocommit mode for sqlite3_snapshot_get() to succeed. This is because it may open a read transaction on the database file. (check-in: 83b658dad0 user: dan tags: serializable-snapshot)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/main.c.
4035
4036
4037
4038
4039
4040
4041




























4042
4043
4044
4045
4046
4047
4048
          rc = sqlite3BtreeBeginTrans(pBt, 0);
          sqlite3PagerSnapshotOpen(sqlite3BtreePager(pBt), 0);
        }
      }
    }
  }





























  sqlite3_mutex_leave(db->mutex);
#endif   /* SQLITE_OMIT_WAL */
  return rc;
}

/*
** Free a snapshot handle obtained from sqlite3_snapshot_get().







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







4035
4036
4037
4038
4039
4040
4041
4042
4043
4044
4045
4046
4047
4048
4049
4050
4051
4052
4053
4054
4055
4056
4057
4058
4059
4060
4061
4062
4063
4064
4065
4066
4067
4068
4069
4070
4071
4072
4073
4074
4075
4076
          rc = sqlite3BtreeBeginTrans(pBt, 0);
          sqlite3PagerSnapshotOpen(sqlite3BtreePager(pBt), 0);
        }
      }
    }
  }

  sqlite3_mutex_leave(db->mutex);
#endif   /* SQLITE_OMIT_WAL */
  return rc;
}

/*
** Recover as many snapshots as possible from the wal file associated with
** schema zDb of database db.
*/
int sqlite3_snapshot_recover(sqlite3 *db, const char *zDb){
  int rc = SQLITE_ERROR;
  int iDb;
#ifndef SQLITE_OMIT_WAL

#ifdef SQLITE_ENABLE_API_ARMOR
  if( !sqlite3SafetyCheckOk(db) ){
    return SQLITE_MISUSE_BKPT;
  }
#endif

  sqlite3_mutex_enter(db->mutex);
  iDb = sqlite3FindDbName(db, zDb);
  if( iDb==0 || iDb>1 ){
    Btree *pBt = db->aDb[iDb].pBt;
    if( 0==sqlite3BtreeIsInReadTrans(pBt) ){
      rc = sqlite3PagerSnapshotRecover(sqlite3BtreePager(pBt));
    }
  }
  sqlite3_mutex_leave(db->mutex);
#endif   /* SQLITE_OMIT_WAL */
  return rc;
}

/*
** Free a snapshot handle obtained from sqlite3_snapshot_get().
Changes to src/pager.c.
7401
7402
7403
7404
7405
7406
7407














7408
7409
7410
7411
7412
7413
7414
  if( pPager->pWal ){
    sqlite3WalSnapshotOpen(pPager->pWal, pSnapshot);
  }else{
    rc = SQLITE_ERROR;
  }
  return rc;
}














#endif /* SQLITE_ENABLE_SNAPSHOT */
#endif /* !SQLITE_OMIT_WAL */

#ifdef SQLITE_ENABLE_ZIPVFS
/*
** A read-lock must be held on the pager when this function is called. If
** the pager is in WAL mode and the WAL file currently contains one or more







>
>
>
>
>
>
>
>
>
>
>
>
>
>







7401
7402
7403
7404
7405
7406
7407
7408
7409
7410
7411
7412
7413
7414
7415
7416
7417
7418
7419
7420
7421
7422
7423
7424
7425
7426
7427
7428
  if( pPager->pWal ){
    sqlite3WalSnapshotOpen(pPager->pWal, pSnapshot);
  }else{
    rc = SQLITE_ERROR;
  }
  return rc;
}

/*
** If this is a WAL database, call sqlite3WalSnapshotRecover(). If this 
** is not a WAL database, return an error.
*/
int sqlite3PagerSnapshotRecover(Pager *pPager){
  int rc;
  if( pPager->pWal ){
    rc = sqlite3WalSnapshotRecover(pPager->pWal);
  }else{
    rc = SQLITE_ERROR;
  }
  return rc;
}
#endif /* SQLITE_ENABLE_SNAPSHOT */
#endif /* !SQLITE_OMIT_WAL */

#ifdef SQLITE_ENABLE_ZIPVFS
/*
** A read-lock must be held on the pager when this function is called. If
** the pager is in WAL mode and the WAL file currently contains one or more
Changes to src/pager.h.
178
179
180
181
182
183
184

185
186
187
188
189
190
191
  int sqlite3PagerWalCallback(Pager *pPager);
  int sqlite3PagerOpenWal(Pager *pPager, int *pisOpen);
  int sqlite3PagerCloseWal(Pager *pPager, sqlite3*);
  int sqlite3PagerUseWal(Pager *pPager);
# ifdef SQLITE_ENABLE_SNAPSHOT
  int sqlite3PagerSnapshotGet(Pager *pPager, sqlite3_snapshot **ppSnapshot);
  int sqlite3PagerSnapshotOpen(Pager *pPager, sqlite3_snapshot *pSnapshot);

# endif
#else
# define sqlite3PagerUseWal(x) 0
#endif

#ifdef SQLITE_ENABLE_ZIPVFS
  int sqlite3PagerWalFramesize(Pager *pPager);







>







178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
  int sqlite3PagerWalCallback(Pager *pPager);
  int sqlite3PagerOpenWal(Pager *pPager, int *pisOpen);
  int sqlite3PagerCloseWal(Pager *pPager, sqlite3*);
  int sqlite3PagerUseWal(Pager *pPager);
# ifdef SQLITE_ENABLE_SNAPSHOT
  int sqlite3PagerSnapshotGet(Pager *pPager, sqlite3_snapshot **ppSnapshot);
  int sqlite3PagerSnapshotOpen(Pager *pPager, sqlite3_snapshot *pSnapshot);
  int sqlite3PagerSnapshotRecover(Pager *pPager);
# endif
#else
# define sqlite3PagerUseWal(x) 0
#endif

#ifdef SQLITE_ENABLE_ZIPVFS
  int sqlite3PagerWalFramesize(Pager *pPager);
Changes to src/sqlite.h.in.
8410
8411
8412
8413
8414
8415
8416






8417
8418
8419
8420
8421
8422
8423
** snapshot, and a positive value if P1 is a newer snapshot than P2.
*/
SQLITE_EXPERIMENTAL int sqlite3_snapshot_cmp(
  sqlite3_snapshot *p1,
  sqlite3_snapshot *p2
);







/*
** Undo the hack that converts floating point types to integer for
** builds on processors without floating point support.
*/
#ifdef SQLITE_OMIT_FLOATING_POINT
# undef double
#endif







>
>
>
>
>
>







8410
8411
8412
8413
8414
8415
8416
8417
8418
8419
8420
8421
8422
8423
8424
8425
8426
8427
8428
8429
** snapshot, and a positive value if P1 is a newer snapshot than P2.
*/
SQLITE_EXPERIMENTAL int sqlite3_snapshot_cmp(
  sqlite3_snapshot *p1,
  sqlite3_snapshot *p2
);

/*
** CAPI3REF: Recover snapshots from a wal file
** EXPERIMENTAL
*/
SQLITE_EXPERIMENTAL int sqlite3_snapshot_recover(sqlite3 *db, const char *zDb);

/*
** Undo the hack that converts floating point types to integer for
** builds on processors without floating point support.
*/
#ifdef SQLITE_OMIT_FLOATING_POINT
# undef double
#endif
Changes to src/test1.c.
2306
2307
2308
2309
2310
2311
2312
































2313
2314
2315
2316
2317
2318
2319
    if( sqlite3TestMakePointerStr(interp, zBuf, pSnapshot) ) return TCL_ERROR;
    Tcl_SetObjResult(interp, Tcl_NewStringObj(zBuf, -1));
  }
  return TCL_OK;
}
#endif /* SQLITE_ENABLE_SNAPSHOT */

































#ifdef SQLITE_ENABLE_SNAPSHOT
/*
** Usage: sqlite3_snapshot_open DB DBNAME SNAPSHOT
*/
static int SQLITE_TCLAPI test_snapshot_open(
  void * clientData,
  Tcl_Interp *interp,







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







2306
2307
2308
2309
2310
2311
2312
2313
2314
2315
2316
2317
2318
2319
2320
2321
2322
2323
2324
2325
2326
2327
2328
2329
2330
2331
2332
2333
2334
2335
2336
2337
2338
2339
2340
2341
2342
2343
2344
2345
2346
2347
2348
2349
2350
2351
    if( sqlite3TestMakePointerStr(interp, zBuf, pSnapshot) ) return TCL_ERROR;
    Tcl_SetObjResult(interp, Tcl_NewStringObj(zBuf, -1));
  }
  return TCL_OK;
}
#endif /* SQLITE_ENABLE_SNAPSHOT */

#ifdef SQLITE_ENABLE_SNAPSHOT
/*
** Usage: sqlite3_snapshot_recover DB DBNAME
*/
static int SQLITE_TCLAPI test_snapshot_recover(
  void * clientData,
  Tcl_Interp *interp,
  int objc,
  Tcl_Obj *CONST objv[]
){
  int rc;
  sqlite3 *db;
  char *zName;

  if( objc!=3 ){
    Tcl_WrongNumArgs(interp, 1, objv, "DB DBNAME");
    return TCL_ERROR;
  }
  if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;
  zName = Tcl_GetString(objv[2]);

  rc = sqlite3_snapshot_recover(db, zName);
  if( rc!=SQLITE_OK ){
    Tcl_SetObjResult(interp, Tcl_NewStringObj(sqlite3ErrName(rc), -1));
    return TCL_ERROR;
  }else{
    Tcl_ResetResult(interp);
  }
  return TCL_OK;
}
#endif /* SQLITE_ENABLE_SNAPSHOT */

#ifdef SQLITE_ENABLE_SNAPSHOT
/*
** Usage: sqlite3_snapshot_open DB DBNAME SNAPSHOT
*/
static int SQLITE_TCLAPI test_snapshot_open(
  void * clientData,
  Tcl_Interp *interp,
7642
7643
7644
7645
7646
7647
7648

7649
7650
7651
7652
7653
7654
7655
#endif
     { "vfs_current_time_int64",           vfsCurrentTimeInt64,   0 },
#ifdef SQLITE_ENABLE_SNAPSHOT
     { "sqlite3_snapshot_get", test_snapshot_get, 0 },
     { "sqlite3_snapshot_open", test_snapshot_open, 0 },
     { "sqlite3_snapshot_free", test_snapshot_free, 0 },
     { "sqlite3_snapshot_cmp", test_snapshot_cmp, 0 },

     { "sqlite3_snapshot_get_blob", test_snapshot_get_blob, 0 },
     { "sqlite3_snapshot_open_blob", test_snapshot_open_blob, 0 },
     { "sqlite3_snapshot_cmp_blob", test_snapshot_cmp_blob, 0 },
#endif
     { "sqlite3_delete_database", test_delete_database, 0 },
  };
  static int bitmask_size = sizeof(Bitmask)*8;







>







7674
7675
7676
7677
7678
7679
7680
7681
7682
7683
7684
7685
7686
7687
7688
#endif
     { "vfs_current_time_int64",           vfsCurrentTimeInt64,   0 },
#ifdef SQLITE_ENABLE_SNAPSHOT
     { "sqlite3_snapshot_get", test_snapshot_get, 0 },
     { "sqlite3_snapshot_open", test_snapshot_open, 0 },
     { "sqlite3_snapshot_free", test_snapshot_free, 0 },
     { "sqlite3_snapshot_cmp", test_snapshot_cmp, 0 },
     { "sqlite3_snapshot_recover", test_snapshot_recover, 0 },
     { "sqlite3_snapshot_get_blob", test_snapshot_get_blob, 0 },
     { "sqlite3_snapshot_open_blob", test_snapshot_open_blob, 0 },
     { "sqlite3_snapshot_cmp_blob", test_snapshot_cmp_blob, 0 },
#endif
     { "sqlite3_delete_database", test_delete_database, 0 },
  };
  static int bitmask_size = sizeof(Bitmask)*8;
Changes to src/wal.c.
2374
2375
2376
2377
2378
2379
2380



























































2381
2382
2383
2384
2385
2386
2387
    return WAL_RETRY;
  }else{
    assert( mxReadMark<=pWal->hdr.mxFrame );
    pWal->readLock = (i16)mxI;
  }
  return rc;
}




























































/*
** Begin a read transaction on the database.
**
** This routine used to be called sqlite3OpenSnapshot() and with good reason:
** it takes a snapshot of the state of the WAL and wal-index for the current
** instant in time.  The current thread will continue to use this snapshot.







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







2374
2375
2376
2377
2378
2379
2380
2381
2382
2383
2384
2385
2386
2387
2388
2389
2390
2391
2392
2393
2394
2395
2396
2397
2398
2399
2400
2401
2402
2403
2404
2405
2406
2407
2408
2409
2410
2411
2412
2413
2414
2415
2416
2417
2418
2419
2420
2421
2422
2423
2424
2425
2426
2427
2428
2429
2430
2431
2432
2433
2434
2435
2436
2437
2438
2439
2440
2441
2442
2443
2444
2445
2446
    return WAL_RETRY;
  }else{
    assert( mxReadMark<=pWal->hdr.mxFrame );
    pWal->readLock = (i16)mxI;
  }
  return rc;
}

/*
** Recover as many snapshots as possible from the wal file.
*/
int sqlite3WalSnapshotRecover(Wal *pWal){
  int dummy;
  int rc;

  rc = sqlite3WalBeginReadTransaction(pWal, &dummy);
  if( rc==SQLITE_OK ){
    rc = walLockExclusive(pWal, WAL_CKPT_LOCK, 1);
    if( rc==SQLITE_OK ){
      volatile WalCkptInfo *pInfo = walCkptInfo(pWal);
      int szPage = (int)pWal->szPage;
      void *pBuf1 = sqlite3_malloc(szPage);
      void *pBuf2 = sqlite3_malloc(szPage);

      if( pBuf1==0 || pBuf2==0 ){
        rc = SQLITE_NOMEM;
      }else{
        u32 i = pInfo->nBackfillAttempted;
        for(i=pInfo->nBackfillAttempted; i>pInfo->nBackfill; i--){
          volatile ht_slot *dummy;
          volatile u32 *aPgno;      /* Array of page numbers */
          u32 iZero;                /* Frame corresponding to aPgno[0] */
          u32 pgno;                 /* Page number in db file */
          i64 iDbOff;               /* Offset of db file entry */
          i64 iWalOff;              /* Offset of wal file entry */
          rc = walHashGet(pWal, walFramePage(i), &dummy, &aPgno, &iZero);

          if( rc==SQLITE_OK ){
            pgno = aPgno[i-iZero];
            iDbOff = (i64)(pgno-1) * szPage;
            iWalOff = walFrameOffset(i, szPage) + WAL_FRAME_HDRSIZE;
            rc = sqlite3OsRead(pWal->pWalFd, pBuf1, szPage, iWalOff);
          }

          if( rc==SQLITE_OK ){
            rc = sqlite3OsRead(pWal->pDbFd, pBuf2, szPage, iDbOff);
          }

          if( rc!=SQLITE_OK || 0==memcmp(pBuf1, pBuf2, szPage) ){
            break;
          }

          pInfo->nBackfillAttempted = i-1;
        }
      }

      sqlite3_free(pBuf1);
      sqlite3_free(pBuf2);
      walUnlockExclusive(pWal, WAL_CKPT_LOCK, 1);
    }

    sqlite3WalEndReadTransaction(pWal);
  }

  return rc;
}

/*
** Begin a read transaction on the database.
**
** This routine used to be called sqlite3OpenSnapshot() and with good reason:
** it takes a snapshot of the state of the WAL and wal-index for the current
** instant in time.  The current thread will continue to use this snapshot.
2437
2438
2439
2440
2441
2442
2443
2444




2445
2446
2447
2448
2449
2450
2451
      /* It is possible that there is a checkpointer thread running 
      ** concurrent with this code. If this is the case, it may be that the
      ** checkpointer has already determined that it will checkpoint 
      ** snapshot X, where X is later in the wal file than pSnapshot, but 
      ** has not yet set the pInfo->nBackfillAttempted variable to indicate 
      ** its intent. To avoid the race condition this leads to, ensure that
      ** there is no checkpointer process by taking a shared CKPT lock 
      ** before checking pInfo->nBackfillAttempted.  */




      rc = walLockShared(pWal, WAL_CKPT_LOCK);

      if( rc==SQLITE_OK ){
        /* Check that the wal file has not been wrapped. Assuming that it has
        ** not, also check that no checkpointer has attempted to checkpoint any
        ** frames beyond pSnapshot->mxFrame. If either of these conditions are
        ** true, return SQLITE_BUSY_SNAPSHOT. Otherwise, overwrite pWal->hdr







|
>
>
>
>







2496
2497
2498
2499
2500
2501
2502
2503
2504
2505
2506
2507
2508
2509
2510
2511
2512
2513
2514
      /* It is possible that there is a checkpointer thread running 
      ** concurrent with this code. If this is the case, it may be that the
      ** checkpointer has already determined that it will checkpoint 
      ** snapshot X, where X is later in the wal file than pSnapshot, but 
      ** has not yet set the pInfo->nBackfillAttempted variable to indicate 
      ** its intent. To avoid the race condition this leads to, ensure that
      ** there is no checkpointer process by taking a shared CKPT lock 
      ** before checking pInfo->nBackfillAttempted.  
      **
      ** TODO: Does the aReadMark[] lock prevent a checkpointer from doing
      **       this already?
      */
      rc = walLockShared(pWal, WAL_CKPT_LOCK);

      if( rc==SQLITE_OK ){
        /* Check that the wal file has not been wrapped. Assuming that it has
        ** not, also check that no checkpointer has attempted to checkpoint any
        ** frames beyond pSnapshot->mxFrame. If either of these conditions are
        ** true, return SQLITE_BUSY_SNAPSHOT. Otherwise, overwrite pWal->hdr
Changes to src/wal.h.
127
128
129
130
131
132
133

134
135
136
137
138
139
140
** WAL module is using shared-memory, return false. 
*/
int sqlite3WalHeapMemory(Wal *pWal);

#ifdef SQLITE_ENABLE_SNAPSHOT
int sqlite3WalSnapshotGet(Wal *pWal, sqlite3_snapshot **ppSnapshot);
void sqlite3WalSnapshotOpen(Wal *pWal, sqlite3_snapshot *pSnapshot);

#endif

#ifdef SQLITE_ENABLE_ZIPVFS
/* If the WAL file is not empty, return the number of bytes of content
** stored in each frame (i.e. the db page-size when the WAL was created).
*/
int sqlite3WalFramesize(Wal *pWal);







>







127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
** WAL module is using shared-memory, return false. 
*/
int sqlite3WalHeapMemory(Wal *pWal);

#ifdef SQLITE_ENABLE_SNAPSHOT
int sqlite3WalSnapshotGet(Wal *pWal, sqlite3_snapshot **ppSnapshot);
void sqlite3WalSnapshotOpen(Wal *pWal, sqlite3_snapshot *pSnapshot);
int sqlite3WalSnapshotRecover(Wal *pWal);
#endif

#ifdef SQLITE_ENABLE_ZIPVFS
/* If the WAL file is not empty, return the number of bytes of content
** stored in each frame (i.e. the db page-size when the WAL was created).
*/
int sqlite3WalFramesize(Wal *pWal);
Changes to test/snapshot2.test.
74
75
76
77
78
79
80

81

























































82


  execsql { INSERT INTO t1 VALUES(10, 11, 12) } db2
  execsql BEGIN
  string length [sqlite3_snapshot_get_blob db main]
} 48
execsql COMMIT
db2 close




























































finish_test









>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

>
>
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
  execsql { INSERT INTO t1 VALUES(10, 11, 12) } db2
  execsql BEGIN
  string length [sqlite3_snapshot_get_blob db main]
} 48
execsql COMMIT
db2 close

#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 2.0 {
  CREATE TABLE t1(x);
  PRAGMA journal_mode = wal;
  INSERT INTO t1 VALUES(1);
  INSERT INTO t1 VALUES(2);
} {wal}

do_test 2.1 {
  db trans { set snap [sqlite3_snapshot_get_blob db main] }
  sqlite3_db_config db NO_CKPT_ON_CLOSE 1
  db close
  sqlite3 db test.db

  execsql {SELECT * FROM sqlite_master}
  execsql BEGIN
  sqlite3_snapshot_open_blob db main $snap
  execsql COMMIT;
  execsql { INSERT INTO t1 VALUES(3); }
} {}

do_test 2.2 {
  sqlite3_db_config db NO_CKPT_ON_CLOSE 1
  db close
  sqlite3 db test.db

  execsql {SELECT * FROM sqlite_master}
  execsql BEGIN
  list [catch { sqlite3_snapshot_open_blob db main $snap } msg] $msg
} {1 SQLITE_BUSY_SNAPSHOT}

do_test 2.3 {
  execsql COMMIT
  sqlite3_snapshot_recover db main
  execsql BEGIN
  sqlite3_snapshot_open_blob db main $snap
  execsql { SELECT * FROM t1 }
} {1 2}

do_test 2.4 {
  execsql COMMIT
  execsql { SELECT * FROM t1 }
} {1 2 3}

do_test 2.5 {
  execsql { PRAGMA wal_checkpoint }
  sqlite3_db_config db NO_CKPT_ON_CLOSE 1
  db close
  sqlite3 db test.db

  execsql {SELECT * FROM sqlite_master}
  sqlite3_snapshot_recover db main
  execsql BEGIN
  list [catch { sqlite3_snapshot_open_blob db main $snap } msg] $msg
} {1 SQLITE_BUSY_SNAPSHOT}


finish_test