/ Check-in [051ac015]
Login

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

Overview
Comment:Allow sqlite3_snapshot_open() to be called to change the snapshot after a read transaction is already open on database.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | exp-snapshot-open
Files: files | file ages | folders
SHA3-256:051ac0152048ef52723196c26ca5f2629dafb782aec1c66fc30531bf54335043
User & Date: dan 2018-08-06 17:12:36
Context
2018-08-15
14:03
Allow sqlite3_snapshot_open() to be called to change the snapshot after a read transaction is already open on database. check-in: 41399169 user: dan tags: trunk
2018-08-06
17:12
Allow sqlite3_snapshot_open() to be called to change the snapshot after a read transaction is already open on database. Closed-Leaf check-in: 051ac015 user: dan tags: exp-snapshot-open
02:08
Enhance the edit() function so that it converts text from \r\n back into \n only if the original unedited copy contained no \r\n values. check-in: 20c995d3 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/main.c.

4205
4206
4207
4208
4209
4210
4211



4212











4213

4214
4215
4216



4217
4218
4219
4220
4221
4222
4223
#endif
  sqlite3_mutex_enter(db->mutex);
  if( db->autoCommit==0 ){
    int iDb;
    iDb = sqlite3FindDbName(db, zDb);
    if( iDb==0 || iDb>1 ){
      Btree *pBt = db->aDb[iDb].pBt;



      if( 0==sqlite3BtreeIsInReadTrans(pBt) ){











        rc = sqlite3PagerSnapshotOpen(sqlite3BtreePager(pBt), pSnapshot);

        if( rc==SQLITE_OK ){
          rc = sqlite3BtreeBeginTrans(pBt, 0, 0);
          sqlite3PagerSnapshotOpen(sqlite3BtreePager(pBt), 0);



        }
      }
    }
  }

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







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


|
>
>
>







4205
4206
4207
4208
4209
4210
4211
4212
4213
4214
4215
4216
4217
4218
4219
4220
4221
4222
4223
4224
4225
4226
4227
4228
4229
4230
4231
4232
4233
4234
4235
4236
4237
4238
4239
4240
4241
#endif
  sqlite3_mutex_enter(db->mutex);
  if( db->autoCommit==0 ){
    int iDb;
    iDb = sqlite3FindDbName(db, zDb);
    if( iDb==0 || iDb>1 ){
      Btree *pBt = db->aDb[iDb].pBt;
      if( sqlite3BtreeIsInTrans(pBt)==0 ){
        Pager *pPager = sqlite3BtreePager(pBt);
        int bUnlock = 0;
        if( sqlite3BtreeIsInReadTrans(pBt) ){
          if( db->nVdbeActive==0 ){
            rc = sqlite3PagerSnapshotCheck(pPager, pSnapshot);
            if( rc==SQLITE_OK ){
              bUnlock = 1;
              rc = sqlite3BtreeCommit(pBt);
            }
          }
        }else{
          rc = SQLITE_OK;
        }
        if( rc==SQLITE_OK ){
          rc = sqlite3PagerSnapshotOpen(pPager, pSnapshot);
        }
        if( rc==SQLITE_OK ){
          rc = sqlite3BtreeBeginTrans(pBt, 0, 0);
          sqlite3PagerSnapshotOpen(pPager, 0);
        }
        if( bUnlock ){
          sqlite3PagerSnapshotUnlock(pPager);
        }
      }
    }
  }

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

Changes to src/pager.c.

7649
7650
7651
7652
7653
7654
7655
































7656
7657
7658
7659
7660
7661
7662
  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







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







7649
7650
7651
7652
7653
7654
7655
7656
7657
7658
7659
7660
7661
7662
7663
7664
7665
7666
7667
7668
7669
7670
7671
7672
7673
7674
7675
7676
7677
7678
7679
7680
7681
7682
7683
7684
7685
7686
7687
7688
7689
7690
7691
7692
7693
7694
  if( pPager->pWal ){
    rc = sqlite3WalSnapshotRecover(pPager->pWal);
  }else{
    rc = SQLITE_ERROR;
  }
  return rc;
}

/*
** The caller currently has a read transaction open on the database.
** If this is not a WAL database, SQLITE_ERROR is returned. Otherwise,
** this function takes a SHARED lock on the CHECKPOINTER slot and then
** checks if the snapshot passed as the second argument is still 
** available. If so, SQLITE_OK is returned.
**
** If the snapshot is not available, SQLITE_ERROR is returned. Or, if
** the CHECKPOINTER lock cannot be obtained, SQLITE_BUSY. If any error
** occurs (any value other than SQLITE_OK is returned), the CHECKPOINTER
** lock is released before returning.
*/
int sqlite3PagerSnapshotCheck(Pager *pPager, sqlite3_snapshot *pSnapshot){
  int rc;
  if( pPager->pWal ){
    rc = sqlite3WalSnapshotCheck(pPager->pWal, pSnapshot);
  }else{
    rc = SQLITE_ERROR;
  }
  return rc;
}

/*
** Release a lock obtained by an earlier successful call to
** sqlite3PagerSnapshotCheck().
*/
void sqlite3PagerSnapshotUnlock(Pager *pPager){
  assert( pPager->pWal );
  return sqlite3WalSnapshotUnlock(pPager->pWal);
}

#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.

182
183
184
185
186
187
188


189
190
191
192
193
194
195
# ifdef SQLITE_DIRECT_OVERFLOW_READ
  int sqlite3PagerUseWal(Pager *pPager, Pgno);
# endif
# 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,y) 0
#endif

#ifdef SQLITE_ENABLE_ZIPVFS
  int sqlite3PagerWalFramesize(Pager *pPager);







>
>







182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
# ifdef SQLITE_DIRECT_OVERFLOW_READ
  int sqlite3PagerUseWal(Pager *pPager, Pgno);
# endif
# ifdef SQLITE_ENABLE_SNAPSHOT
  int sqlite3PagerSnapshotGet(Pager *pPager, sqlite3_snapshot **ppSnapshot);
  int sqlite3PagerSnapshotOpen(Pager *pPager, sqlite3_snapshot *pSnapshot);
  int sqlite3PagerSnapshotRecover(Pager *pPager);
  int sqlite3PagerSnapshotCheck(Pager *pPager, sqlite3_snapshot *pSnapshot);
  void sqlite3PagerSnapshotUnlock(Pager *pPager);
# endif
#else
# define sqlite3PagerUseWal(x,y) 0
#endif

#ifdef SQLITE_ENABLE_ZIPVFS
  int sqlite3PagerWalFramesize(Pager *pPager);

Changes to src/sqlite.h.in.

9031
9032
9033
9034
9035
9036
9037
9038
9039
9040
9041
9042
9043
9044
9045
9046
9047
9048
9049
9050




9051


9052
9053










9054
9055
9056
9057
9058
9059
9060
  sqlite3_snapshot **ppSnapshot
);

/*
** CAPI3REF: Start a read transaction on an historical snapshot
** METHOD: sqlite3_snapshot
**
** ^The [sqlite3_snapshot_open(D,S,P)] interface starts a
** read transaction for schema S of
** [database connection] D such that the read transaction
** refers to historical [snapshot] P, rather than the most
** recent change to the database.
** ^The [sqlite3_snapshot_open()] interface returns SQLITE_OK on success
** or an appropriate [error code] if it fails.
**
** ^In order to succeed, a call to [sqlite3_snapshot_open(D,S,P)] must be
** the first operation following the [BEGIN] that takes the schema S
** out of [autocommit mode].
** ^In other words, schema S must not currently be in
** a transaction for [sqlite3_snapshot_open(D,S,P)] to work, but the




** database connection D must be out of [autocommit mode].


** ^A [snapshot] will fail to open if it has been overwritten by a
** [checkpoint].










** ^(A call to [sqlite3_snapshot_open(D,S,P)] will fail if the
** database connection D does not know that the database file for
** schema S is in [WAL mode].  A database connection might not know
** that the database file is in [WAL mode] if there has been no prior
** I/O on that database connection, or if the database entered [WAL mode] 
** after the most recent I/O on the database connection.)^
** (Hint: Run "[PRAGMA application_id]" against a newly opened







|
|
|
|
<
|
|

|
<
<
<
|
>
>
>
>
|
>
>
|
<
>
>
>
>
>
>
>
>
>
>







9031
9032
9033
9034
9035
9036
9037
9038
9039
9040
9041

9042
9043
9044
9045



9046
9047
9048
9049
9050
9051
9052
9053
9054

9055
9056
9057
9058
9059
9060
9061
9062
9063
9064
9065
9066
9067
9068
9069
9070
9071
  sqlite3_snapshot **ppSnapshot
);

/*
** CAPI3REF: Start a read transaction on an historical snapshot
** METHOD: sqlite3_snapshot
**
** ^The [sqlite3_snapshot_open(D,S,P)] interface either starts a new read 
** transaction or upgrades an existing one for schema S of 
** [database connection] D such that the read transaction refers to 
** historical [snapshot] P, rather than the most recent change to the 

** database. ^The [sqlite3_snapshot_open()] interface returns SQLITE_OK 
** on success or an appropriate [error code] if it fails.
**
** ^In order to succeed, the database connection must not be in 



** [autocommit mode] when [sqlite3_snapshot_open(D,S,P)] is called. If there
** is already a read transaction open on schema S, then the database handle
** must have no active statements (SELECT statements that have been passed
** to sqlite3_step() but not sqlite3_reset() or sqlite3_finalize()). 
** SQLITE_ERROR is returned if either of these conditions is violated, or
** if schema S does not exist, or if the snapshot object is invalid.
**
** ^A call to sqlite3_snapshot_open() will fail to open if the specified
** snapshot has been overwritten by a [checkpoint]. In this case 

** SQLITE_BUSY_SNAPSHOT is returned.
**
** If there is already a read transaction open when this function is 
** invoked, then the same read transaction remains open (on the same
** database snapshot) if SQLITE_ERROR, SQLITE_BUSY or SQLITE_BUSY_SNAPSHOT
** is returned. If another error code - for example SQLITE_PROTOCOL or an
** SQLITE_IOERR error code - is returned, then the final state of the
** read transaction is undefined. If SQLITE_OK is returned, then the 
** read transaction is now open on database snapshot P.
**
** ^(A call to [sqlite3_snapshot_open(D,S,P)] will fail if the
** database connection D does not know that the database file for
** schema S is in [WAL mode].  A database connection might not know
** that the database file is in [WAL mode] if there has been no prior
** I/O on that database connection, or if the database entered [WAL mode] 
** after the most recent I/O on the database connection.)^
** (Hint: Run "[PRAGMA application_id]" against a newly opened

Changes to src/test1.c.

2389
2390
2391
2392
2393
2394
2395


2396
2397
2398
2399
2400
2401
2402
  zName = Tcl_GetString(objv[2]);
  pSnapshot = (sqlite3_snapshot*)sqlite3TestTextToPtr(Tcl_GetString(objv[3]));

  rc = sqlite3_snapshot_open(db, zName, pSnapshot);
  if( rc!=SQLITE_OK ){
    Tcl_SetObjResult(interp, Tcl_NewStringObj(sqlite3ErrName(rc), -1));
    return TCL_ERROR;


  }
  return TCL_OK;
}
#endif /* SQLITE_ENABLE_SNAPSHOT */

#ifdef SQLITE_ENABLE_SNAPSHOT
/*







>
>







2389
2390
2391
2392
2393
2394
2395
2396
2397
2398
2399
2400
2401
2402
2403
2404
  zName = Tcl_GetString(objv[2]);
  pSnapshot = (sqlite3_snapshot*)sqlite3TestTextToPtr(Tcl_GetString(objv[3]));

  rc = sqlite3_snapshot_open(db, zName, pSnapshot);
  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
/*

Changes to src/wal.c.

3765
3766
3767
3768
3769
3770
3771





































3772
3773
3774
3775
3776
3777
3778
  ** is incremented each time the wal file is restarted.  */
  if( pHdr1->aSalt[0]<pHdr2->aSalt[0] ) return -1;
  if( pHdr1->aSalt[0]>pHdr2->aSalt[0] ) return +1;
  if( pHdr1->mxFrame<pHdr2->mxFrame ) return -1;
  if( pHdr1->mxFrame>pHdr2->mxFrame ) return +1;
  return 0;
}





































#endif /* SQLITE_ENABLE_SNAPSHOT */

#ifdef SQLITE_ENABLE_ZIPVFS
/*
** If the argument is not NULL, it points to a Wal object that holds a
** read-lock. This function returns the database page-size if it is known,
** or zero if it is not (or if pWal is NULL).







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







3765
3766
3767
3768
3769
3770
3771
3772
3773
3774
3775
3776
3777
3778
3779
3780
3781
3782
3783
3784
3785
3786
3787
3788
3789
3790
3791
3792
3793
3794
3795
3796
3797
3798
3799
3800
3801
3802
3803
3804
3805
3806
3807
3808
3809
3810
3811
3812
3813
3814
3815
  ** is incremented each time the wal file is restarted.  */
  if( pHdr1->aSalt[0]<pHdr2->aSalt[0] ) return -1;
  if( pHdr1->aSalt[0]>pHdr2->aSalt[0] ) return +1;
  if( pHdr1->mxFrame<pHdr2->mxFrame ) return -1;
  if( pHdr1->mxFrame>pHdr2->mxFrame ) return +1;
  return 0;
}

/*
** The caller currently has a read transaction open on the database.
** This function takes a SHARED lock on the CHECKPOINTER slot and then
** checks if the snapshot passed as the second argument is still 
** available. If so, SQLITE_OK is returned.
**
** If the snapshot is not available, SQLITE_ERROR is returned. Or, if
** the CHECKPOINTER lock cannot be obtained, SQLITE_BUSY. If any error
** occurs (any value other than SQLITE_OK is returned), the CHECKPOINTER
** lock is released before returning.
*/
int sqlite3WalSnapshotCheck(Wal *pWal, sqlite3_snapshot *pSnapshot){
  int rc;
  rc = walLockShared(pWal, WAL_CKPT_LOCK);
  if( rc==SQLITE_OK ){
    WalIndexHdr *pNew = (WalIndexHdr*)pSnapshot;
    if( memcmp(pNew->aSalt, pWal->hdr.aSalt, sizeof(pWal->hdr.aSalt))
     || pNew->mxFrame<walCkptInfo(pWal)->nBackfillAttempted
    ){
      rc = SQLITE_BUSY_SNAPSHOT;
      walUnlockShared(pWal, WAL_CKPT_LOCK);
    }
  }
  return rc;
}

/*
** Release a lock obtained by an earlier successful call to
** sqlite3WalSnapshotCheck().
*/
void sqlite3WalSnapshotUnlock(Wal *pWal){
  assert( pWal );
  walUnlockShared(pWal, WAL_CKPT_LOCK);
}


#endif /* SQLITE_ENABLE_SNAPSHOT */

#ifdef SQLITE_ENABLE_ZIPVFS
/*
** If the argument is not NULL, it points to a Wal object that holds a
** read-lock. This function returns the database page-size if it is known,
** or zero if it is not (or if pWal is NULL).

Changes to src/wal.h.

128
129
130
131
132
133
134


135
136
137
138
139
140
141
*/
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);







>
>







128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
*/
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);
int sqlite3WalSnapshotCheck(Wal *pWal, sqlite3_snapshot *pSnapshot);
void sqlite3WalSnapshotUnlock(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/snapshot.test.

213
214
215
216
217
218
219



220

221



222



223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238





239
240
241
242
243
244
245

  do_test $tn.3.2.1 {
    execsql {
      BEGIN;
        SELECT * FROM t2;
    }
  } {a b c d e f}



  do_test $tn.3.2.2 {

    list [catch {snapshot_open db main $snapshot } msg] $msg



  } {1 SQLITE_ERROR}




  do_test $tn.3.2.3 {
    execsql {
      COMMIT;
      BEGIN;
        INSERT INTO t2 VALUES('g', 'h');
    }
    list [catch {snapshot_open db main $snapshot } msg] $msg
  } {1 SQLITE_ERROR}
  do_execsql_test $tn.3.2.4 COMMIT

  do_test $tn.3.3.1 {
    execsql { PRAGMA journal_mode = DELETE }
    execsql { BEGIN }
    list [catch {snapshot_open db main $snapshot } msg] $msg
  } {1 SQLITE_ERROR}






  do_test $tn.$tn.3.3.2 {
    snapshot_free $snapshot
    execsql COMMIT
  } {}

  #-------------------------------------------------------------------------







>
>
>
|
>
|
>
>
>

>
>
>











|




>
>
>
>
>







213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260

  do_test $tn.3.2.1 {
    execsql {
      BEGIN;
        SELECT * FROM t2;
    }
  } {a b c d e f}

  # Update - it is no longer an error to have a read-transaction open, 
  # provided there are no active SELECT statements.
  do_test $tn.3.2.2a {
    db eval "SELECT * FROM t2" {
      set res [list [catch {snapshot_open db main $snapshot } msg] $msg]
      break
    }
    set res
  } {1 SQLITE_ERROR}
  do_test $tn.3.2.2b {
    snapshot_open db main $snapshot
  } {}

  do_test $tn.3.2.3 {
    execsql {
      COMMIT;
      BEGIN;
        INSERT INTO t2 VALUES('g', 'h');
    }
    list [catch {snapshot_open db main $snapshot } msg] $msg
  } {1 SQLITE_ERROR}
  do_execsql_test $tn.3.2.4 COMMIT

  do_test $tn.3.3.1a {
    execsql { PRAGMA journal_mode = DELETE }
    execsql { BEGIN }
    list [catch {snapshot_open db main $snapshot } msg] $msg
  } {1 SQLITE_ERROR}

  do_test $tn.3.3.1b {
    execsql { COMMIT ; BEGIN ; SELECT * FROM t2 }
    list [catch {snapshot_open db main $snapshot } msg] $msg
  } {1 SQLITE_ERROR}

  do_test $tn.$tn.3.3.2 {
    snapshot_free $snapshot
    execsql COMMIT
  } {}

  #-------------------------------------------------------------------------

Added test/snapshot_up.test.

















































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
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
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
# 2018 August 6
#
# 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.
#
#***********************************************************************
#
# Tests for calling sqlite3_snapshot_open() when there is already
# a read transaction open on the database.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
ifcapable !snapshot {finish_test; return}
set testprefix snapshot_up

# This test does not work with the inmemory_journal permutation. The reason
# is that each connection opened as part of this permutation executes
# "PRAGMA journal_mode=memory", which fails if the database is in wal mode
# and there are one or more existing connections.
if {[permutation]=="inmemory_journal"} {
  finish_test
  return
}

do_execsql_test 1.0 {
  CREATE TABLE t1(a, b, c);
  PRAGMA journal_mode = wal;
  INSERT INTO t1 VALUES(1, 2, 3);
  INSERT INTO t1 VALUES(4, 5, 6);
  INSERT INTO t1 VALUES(7, 8, 9);
} {wal}

do_test 1.1 {
  execsql BEGIN
  set ::snap1 [sqlite3_snapshot_get db main]
  execsql COMMIT
  execsql { INSERT INTO t1 VALUES(10, 11, 12); }
  execsql BEGIN
  set ::snap2 [sqlite3_snapshot_get db main]
  execsql COMMIT
  execsql { INSERT INTO t1 VALUES(13, 14, 15); }
  execsql BEGIN
  set ::snap3 [sqlite3_snapshot_get db main]
  execsql COMMIT
} {}

do_execsql_test 1.2 {
  BEGIN;
    SELECT * FROM t1
} {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15}

do_test 1.3 {
  sqlite3_snapshot_open db main $::snap1
  execsql { SELECT * FROM t1 }
} {1 2 3 4 5 6 7 8 9}

do_test 1.4 {
  sqlite3_snapshot_open db main $::snap2
  execsql { SELECT * FROM t1 }
} {1 2 3 4 5 6 7 8 9 10 11 12}

do_test 1.5 {
  sqlite3 db2 test.db
  execsql { PRAGMA wal_checkpoint } db2
} {0 5 4}

do_execsql_test 1.6 {
  SELECT * FROM t1
} {1 2 3 4 5 6 7 8 9 10 11 12}

do_test 1.7 {
  list [catch { sqlite3_snapshot_open db main $::snap1 } msg] $msg
} {1 SQLITE_BUSY_SNAPSHOT}

do_execsql_test 1.8 {
  SELECT * FROM t1
} {1 2 3 4 5 6 7 8 9 10 11 12}

do_test 1.9 {
  execsql { COMMIT ; BEGIN }
  list [catch { sqlite3_snapshot_open db main $::snap1 } msg] $msg
} {1 SQLITE_BUSY_SNAPSHOT}

do_test 1.10 {
  execsql { COMMIT }
  execsql {
    PRAGMA wal_checkpoint;
    DELETE FROM t1 WHERE a = 1;
  } db2
  execsql BEGIN
  set ::snap4 [sqlite3_snapshot_get db main]
  execsql COMMIT
  execsql {
    DELETE FROM t1 WHERE a = 4;
  } db2
} {}

do_test 1.11 {
  execsql { 
    BEGIN;
      SELECT * FROM t1
  }
} {7 8 9 10 11 12 13 14 15}
do_test 1.12 {
  sqlite3_snapshot_open db main $::snap4
  execsql { SELECT * FROM t1 }
} {4 5 6 7 8 9 10 11 12 13 14 15}

do_test 1.13 {
  list [catch { sqlite3_snapshot_open db main $::snap3 } msg] $msg
} {1 SQLITE_BUSY_SNAPSHOT}
do_test 1.14 {
  execsql { SELECT * FROM t1 }
} {4 5 6 7 8 9 10 11 12 13 14 15}

db close
db2 close
sqlite3 db test.db
do_execsql_test 1.15 {
  BEGIN;
    SELECT * FROM t1
} {7 8 9 10 11 12 13 14 15}
do_test 1.16 {
  list [catch { sqlite3_snapshot_open db main $::snap4 } msg] $msg
} {1 SQLITE_BUSY_SNAPSHOT}
do_execsql_test 1.17 { COMMIT }

sqlite3_snapshot_free $::snap1
sqlite3_snapshot_free $::snap2
sqlite3_snapshot_free $::snap3
sqlite3_snapshot_free $::snap4

#-------------------------------------------------------------------------
catch { db close }
sqlite3 db test.db
sqlite3 db2 test.db
sqlite3 db3 test.db

proc xBusy {args} { return 1 }
db3 busy xBusy

do_test 2.1 {
  execsql { INSERT INTO t1 VALUES(16, 17, 18) } db2
  execsql BEGIN
  set ::snap1 [sqlite3_snapshot_get db main]
  execsql COMMIT
  execsql { INSERT INTO t1 VALUES(19, 20, 21) } db2
  execsql BEGIN
  set ::snap2 [sqlite3_snapshot_get db main]
  execsql COMMIT
  set {} {}
} {}

do_execsql_test -db db2 2.2 {
  BEGIN;
    INSERT INTO t1 VALUES(19, 20, 21);
}

do_test 2.3 {
  execsql BEGIN
  sqlite3_snapshot_open db main $::snap1
  execsql { SELECT * FROM t1 }
} {7 8 9 10 11 12 13 14 15 16 17 18}

proc xBusy {args} { 
  set ::res [list [catch { sqlite3_snapshot_open db main $::snap2 } msg] $msg]
  return 1
}
db3 busy xBusy
do_test 2.4 {
  execsql {PRAGMA wal_checkpoint = restart} db3
  set ::res
} {1 SQLITE_BUSY}

sqlite3_snapshot_free $::snap1
sqlite3_snapshot_free $::snap2

finish_test