SQLite

Changes On Branch shared-cache-fixes
Login

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

Changes In Branch shared-cache-fixes Excluding Merge-Ins

This is equivalent to a diff from 6dae62c4 to 65ff754e

2013-05-15
17:08
Make sure an sqlite3_close() or a rollback on one shared-cache connection does not disrupt the operation of other connections using the same shared cache. Fix for ticket [e636a050b709]. (check-in: 5cc1cc55 user: drh tags: trunk)
16:16
Add main.c to the list of files compiled with SQLITE_TEST defined when building testfixture. (check-in: a58af814 user: dan tags: trunk)
16:08
Fix the sharedA.test module so that it does not attempt to run TCL callbacks on a different thread from where the interpreter was originally created. (Closed-Leaf check-in: 65ff754e user: drh tags: shared-cache-fixes)
15:53
Do not run sharedA.test if the system is not threadsafe. (check-in: d484eaf8 user: dan tags: shared-cache-fixes)
15:42
Merge latest trunk changes with this branch. (check-in: 47dd65a8 user: dan tags: shared-cache-fixes)
15:16
When loading a database schema that contains an index definition that includes a COLLATE clause for which the collation sequence is unavailable, do not assume that that index uses BINARY instead. Fix for [0fc59f908b]. (check-in: 6dae62c4 user: dan tags: trunk)
13:05
Add the rot13.c loadable extension. (check-in: 8f9bd8e7 user: drh tags: trunk)

Changes to src/btree.c.

2513
2514
2515
2516
2517
2518
2519























2520
2521
2522
2523
2524
2525
2526
2527
2528
2529
2530
2531
2532
2533
2534
2535
2536
2537

page1_init_failed:
  releasePage(pPage1);
  pBt->pPage1 = 0;
  return rc;
}
























/*
** If there are no outstanding cursors and we are not in the middle
** of a transaction but there is a read lock on the database, then
** this routine unrefs the first page of the database file which 
** has the effect of releasing the read lock.
**
** If there is a transaction in progress, this routine is a no-op.
*/
static void unlockBtreeIfUnused(BtShared *pBt){
  assert( sqlite3_mutex_held(pBt->mutex) );
  assert( pBt->pCursor==0 || pBt->inTransaction>TRANS_NONE );
  if( pBt->inTransaction==TRANS_NONE && pBt->pPage1!=0 ){
    assert( pBt->pPage1->aData );
    assert( sqlite3PagerRefcount(pBt->pPager)==1 );
    assert( pBt->pPage1->aData );
    releasePage(pBt->pPage1);
    pBt->pPage1 = 0;
  }







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










|







2513
2514
2515
2516
2517
2518
2519
2520
2521
2522
2523
2524
2525
2526
2527
2528
2529
2530
2531
2532
2533
2534
2535
2536
2537
2538
2539
2540
2541
2542
2543
2544
2545
2546
2547
2548
2549
2550
2551
2552
2553
2554
2555
2556
2557
2558
2559
2560

page1_init_failed:
  releasePage(pPage1);
  pBt->pPage1 = 0;
  return rc;
}

#ifndef NDEBUG
/*
** Return the number of cursors open on pBt. This is for use
** in assert() expressions, so it is only compiled if NDEBUG is not
** defined.
**
** Only write cursors are counted if wrOnly is true.  If wrOnly is
** false then all cursors are counted.
**
** For the purposes of this routine, a cursor is any cursor that
** is capable of reading or writing to the databse.  Cursors that
** have been tripped into the CURSOR_FAULT state are not counted.
*/
static int countValidCursors(BtShared *pBt, int wrOnly){
  BtCursor *pCur;
  int r = 0;
  for(pCur=pBt->pCursor; pCur; pCur=pCur->pNext){
    if( (wrOnly==0 || pCur->wrFlag) && pCur->eState!=CURSOR_FAULT ) r++; 
  }
  return r;
}
#endif

/*
** If there are no outstanding cursors and we are not in the middle
** of a transaction but there is a read lock on the database, then
** this routine unrefs the first page of the database file which 
** has the effect of releasing the read lock.
**
** If there is a transaction in progress, this routine is a no-op.
*/
static void unlockBtreeIfUnused(BtShared *pBt){
  assert( sqlite3_mutex_held(pBt->mutex) );
  assert( countValidCursors(pBt,0)==0 || pBt->inTransaction>TRANS_NONE );
  if( pBt->inTransaction==TRANS_NONE && pBt->pPage1!=0 ){
    assert( pBt->pPage1->aData );
    assert( sqlite3PagerRefcount(pBt->pPager)==1 );
    assert( pBt->pPage1->aData );
    releasePage(pBt->pPage1);
    pBt->pPage1 = 0;
  }
3251
3252
3253
3254
3255
3256
3257
3258
3259
3260
3261
3262
3263
3264
3265
static void btreeEndTransaction(Btree *p){
  BtShared *pBt = p->pBt;
  assert( sqlite3BtreeHoldsMutex(p) );

#ifndef SQLITE_OMIT_AUTOVACUUM
  pBt->bDoTruncate = 0;
#endif
  btreeClearHasContent(pBt);
  if( p->inTrans>TRANS_NONE && p->db->activeVdbeCnt>1 ){
    /* If there are other active statements that belong to this database
    ** handle, downgrade to a read-only transaction. The other statements
    ** may still be reading from the database.  */
    downgradeAllSharedCacheTableLocks(p);
    p->inTrans = TRANS_READ;
  }else{







<







3274
3275
3276
3277
3278
3279
3280

3281
3282
3283
3284
3285
3286
3287
static void btreeEndTransaction(Btree *p){
  BtShared *pBt = p->pBt;
  assert( sqlite3BtreeHoldsMutex(p) );

#ifndef SQLITE_OMIT_AUTOVACUUM
  pBt->bDoTruncate = 0;
#endif

  if( p->inTrans>TRANS_NONE && p->db->activeVdbeCnt>1 ){
    /* If there are other active statements that belong to this database
    ** handle, downgrade to a read-only transaction. The other statements
    ** may still be reading from the database.  */
    downgradeAllSharedCacheTableLocks(p);
    p->inTrans = TRANS_READ;
  }else{
3326
3327
3328
3329
3330
3331
3332

3333
3334
3335
3336
3337
3338
3339
    assert( pBt->nTransaction>0 );
    rc = sqlite3PagerCommitPhaseTwo(pBt->pPager);
    if( rc!=SQLITE_OK && bCleanup==0 ){
      sqlite3BtreeLeave(p);
      return rc;
    }
    pBt->inTransaction = TRANS_READ;

  }

  btreeEndTransaction(p);
  sqlite3BtreeLeave(p);
  return SQLITE_OK;
}








>







3348
3349
3350
3351
3352
3353
3354
3355
3356
3357
3358
3359
3360
3361
3362
    assert( pBt->nTransaction>0 );
    rc = sqlite3PagerCommitPhaseTwo(pBt->pPager);
    if( rc!=SQLITE_OK && bCleanup==0 ){
      sqlite3BtreeLeave(p);
      return rc;
    }
    pBt->inTransaction = TRANS_READ;
    btreeClearHasContent(pBt);
  }

  btreeEndTransaction(p);
  sqlite3BtreeLeave(p);
  return SQLITE_OK;
}

3347
3348
3349
3350
3351
3352
3353
3354
3355
3356
3357
3358
3359
3360
3361
3362
3363
3364
3365
3366
3367
3368
3369
3370
3371
3372
3373
3374
3375
3376
3377
3378
3379
3380
3381
  if( rc==SQLITE_OK ){
    rc = sqlite3BtreeCommitPhaseTwo(p, 0);
  }
  sqlite3BtreeLeave(p);
  return rc;
}

#ifndef NDEBUG
/*
** Return the number of write-cursors open on this handle. This is for use
** in assert() expressions, so it is only compiled if NDEBUG is not
** defined.
**
** For the purposes of this routine, a write-cursor is any cursor that
** is capable of writing to the databse.  That means the cursor was
** originally opened for writing and the cursor has not be disabled
** by having its state changed to CURSOR_FAULT.
*/
static int countWriteCursors(BtShared *pBt){
  BtCursor *pCur;
  int r = 0;
  for(pCur=pBt->pCursor; pCur; pCur=pCur->pNext){
    if( pCur->wrFlag && pCur->eState!=CURSOR_FAULT ) r++; 
  }
  return r;
}
#endif

/*
** This routine sets the state to CURSOR_FAULT and the error
** code to errCode for every cursor on BtShared that pBtree
** references.
**
** Every cursor is tripped, including cursors that belong
** to other database connections that happen to be sharing







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







3370
3371
3372
3373
3374
3375
3376





















3377
3378
3379
3380
3381
3382
3383
  if( rc==SQLITE_OK ){
    rc = sqlite3BtreeCommitPhaseTwo(p, 0);
  }
  sqlite3BtreeLeave(p);
  return rc;
}






















/*
** This routine sets the state to CURSOR_FAULT and the error
** code to errCode for every cursor on BtShared that pBtree
** references.
**
** Every cursor is tripped, including cursors that belong
** to other database connections that happen to be sharing
3447
3448
3449
3450
3451
3452
3453
3454
3455

3456
3457
3458
3459
3460
3461
3462
      int nPage = get4byte(28+(u8*)pPage1->aData);
      testcase( nPage==0 );
      if( nPage==0 ) sqlite3PagerPagecount(pBt->pPager, &nPage);
      testcase( pBt->nPage!=nPage );
      pBt->nPage = nPage;
      releasePage(pPage1);
    }
    assert( countWriteCursors(pBt)==0 );
    pBt->inTransaction = TRANS_READ;

  }

  btreeEndTransaction(p);
  sqlite3BtreeLeave(p);
  return rc;
}








|

>







3449
3450
3451
3452
3453
3454
3455
3456
3457
3458
3459
3460
3461
3462
3463
3464
3465
      int nPage = get4byte(28+(u8*)pPage1->aData);
      testcase( nPage==0 );
      if( nPage==0 ) sqlite3PagerPagecount(pBt->pPager, &nPage);
      testcase( pBt->nPage!=nPage );
      pBt->nPage = nPage;
      releasePage(pPage1);
    }
    assert( countValidCursors(pBt, 1)==0 );
    pBt->inTransaction = TRANS_READ;
    btreeClearHasContent(pBt);
  }

  btreeEndTransaction(p);
  sqlite3BtreeLeave(p);
  return rc;
}

Changes to src/main.c.

844
845
846
847
848
849
850






851
852
853
854
855
856
857
  if( !forceZombie && connectionIsBusy(db) ){
    sqlite3Error(db, SQLITE_BUSY, "unable to close due to unfinalized "
       "statements or unfinished backups");
    sqlite3_mutex_leave(db->mutex);
    return SQLITE_BUSY;
  }







#ifdef SQLITE_ENABLE_SQLLOG
  if( sqlite3GlobalConfig.xSqllog ){
    /* Closing the handle. Fourth parameter is passed the value 2. */
    sqlite3GlobalConfig.xSqllog(sqlite3GlobalConfig.pSqllogArg, db, 0, 2);
  }
#endif








>
>
>
>
>
>







844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
  if( !forceZombie && connectionIsBusy(db) ){
    sqlite3Error(db, SQLITE_BUSY, "unable to close due to unfinalized "
       "statements or unfinished backups");
    sqlite3_mutex_leave(db->mutex);
    return SQLITE_BUSY;
  }

  /* If a transaction is open, roll it back. This also ensures that if
  ** any database schemas have been modified by the current transaction
  ** they are reset. And that the required b-tree mutex is held to make
  ** the the pager rollback and schema reset an atomic operation. */
  sqlite3RollbackAll(db, SQLITE_OK);

#ifdef SQLITE_ENABLE_SQLLOG
  if( sqlite3GlobalConfig.xSqllog ){
    /* Closing the handle. Fourth parameter is passed the value 2. */
    sqlite3GlobalConfig.xSqllog(sqlite3GlobalConfig.pSqllogArg, db, 0, 2);
  }
#endif

998
999
1000
1001
1002
1003
1004

1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021

1022
1023
1024
1025
1026
1027
1028
** attempts to use that cursor.
*/
void sqlite3RollbackAll(sqlite3 *db, int tripCode){
  int i;
  int inTrans = 0;
  assert( sqlite3_mutex_held(db->mutex) );
  sqlite3BeginBenignMalloc();

  for(i=0; i<db->nDb; i++){
    Btree *p = db->aDb[i].pBt;
    if( p ){
      if( sqlite3BtreeIsInTrans(p) ){
        inTrans = 1;
      }
      sqlite3BtreeRollback(p, tripCode);
      db->aDb[i].inTrans = 0;
    }
  }
  sqlite3VtabRollback(db);
  sqlite3EndBenignMalloc();

  if( (db->flags&SQLITE_InternChanges)!=0 && db->init.busy==0 ){
    sqlite3ExpirePreparedStatements(db);
    sqlite3ResetAllSchemasOfConnection(db);
  }


  /* Any deferred constraint violations have now been resolved. */
  db->nDeferredCons = 0;

  /* If one has been configured, invoke the rollback-hook callback */
  if( db->xRollbackCallback && (inTrans || !db->autoCommit) ){
    db->xRollbackCallback(db->pRollbackArg);







>

















>







1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
** attempts to use that cursor.
*/
void sqlite3RollbackAll(sqlite3 *db, int tripCode){
  int i;
  int inTrans = 0;
  assert( sqlite3_mutex_held(db->mutex) );
  sqlite3BeginBenignMalloc();
  sqlite3BtreeEnterAll(db);
  for(i=0; i<db->nDb; i++){
    Btree *p = db->aDb[i].pBt;
    if( p ){
      if( sqlite3BtreeIsInTrans(p) ){
        inTrans = 1;
      }
      sqlite3BtreeRollback(p, tripCode);
      db->aDb[i].inTrans = 0;
    }
  }
  sqlite3VtabRollback(db);
  sqlite3EndBenignMalloc();

  if( (db->flags&SQLITE_InternChanges)!=0 && db->init.busy==0 ){
    sqlite3ExpirePreparedStatements(db);
    sqlite3ResetAllSchemasOfConnection(db);
  }
  sqlite3BtreeLeaveAll(db);

  /* Any deferred constraint violations have now been resolved. */
  db->nDeferredCons = 0;

  /* If one has been configured, invoke the rollback-hook callback */
  if( db->xRollbackCallback && (inTrans || !db->autoCommit) ){
    db->xRollbackCallback(db->pRollbackArg);

Added test/sharedA.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
185
186
187
188
# 2013 May 14
#
# 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.
#
#***********************************************************************
#
# Test some specific circumstances to do with shared cache mode.
#


set testdir [file dirname $argv0]
source $testdir/tester.tcl
if {[run_thread_tests]==0} { finish_test ; return }
db close
set ::testprefix sharedA

set ::enable_shared_cache [sqlite3_enable_shared_cache 1]

#-------------------------------------------------------------------------
#
do_test 0.1 {
  sqlite3 db1 test.db
  sqlite3 db2 test.db

  db1 eval {
    CREATE TABLE t1(x);
    INSERT INTO t1 VALUES(randomblob(100));
    INSERT INTO t1 SELECT randomblob(100) FROM t1;
    INSERT INTO t1 SELECT randomblob(100) FROM t1;
    INSERT INTO t1 SELECT randomblob(100) FROM t1;
    INSERT INTO t1 SELECT randomblob(100) FROM t1;
    INSERT INTO t1 SELECT randomblob(100) FROM t1;
    INSERT INTO t1 SELECT randomblob(100) FROM t1;
    CREATE INDEX i1 ON t1(x);
  }

  db1 eval {
    BEGIN;
    DROP INDEX i1;
  }

  db2 close

  db1 eval {
    INSERT INTO t1 SELECT randomblob(100) FROM t1;
    ROLLBACK;
    PRAGMA integrity_check;
  }
} {ok}

db1 close
forcedelete test.db


#-------------------------------------------------------------------------
#
do_test 1.1 {
  sqlite3 db1 test.db
  sqlite3 db2 test.db
  db2 eval {
    CREATE TABLE t1(x);
    INSERT INTO t1 VALUES(123);
  }
  db1 eval { 
    SELECT * FROM t1;
    CREATE INDEX i1 ON t1(x);
  }
} {123}

do_test 1.2 {
  db2 eval { SELECT * FROM t1 ORDER BY x; }

  db1 eval {
    BEGIN; DROP INDEX i1;
  }
  db1 close

  db2 eval { SELECT * FROM t1 ORDER BY x; }
} {123}

do_test 1.3 {
  db2 close
} {}

#-------------------------------------------------------------------------
#
# sqlite3RollbackAll() loops through all attached b-trees and rolls
# back each one separately.  Then if the SQLITE_InternChanges flag is
# set, it resets the schema.  Both of the above steps must be done
# while holding a mutex, otherwise another thread might slip in and
# try to use the new schema with the old data.
#
# The following sequence of tests attempt to verify that the actions
# taken by sqlite3RollbackAll() are thread-atomic (that they cannot be
# interrupted by a separate thread.)  
#
# Note that a TCL interpreter can only be used within the thread in which
# it was originally created (because it uses thread-local-storage).  
# The tvfs callbacks must therefore only run on the main thread.  
# There is some trickery in the read_callback procedure to ensure that
# this is the case.
#
testvfs tvfs

# Set up two databases and two database connections.
#
#   db1:  main(test.db), two(test2.db)
#   db2:  main(test.db)
#
# The cache for test.db is shared between db1 and db2.
#
do_test 2.1 {
  forcedelete test.db test.db2
  sqlite3 db1 test.db -vfs tvfs
  db1 eval { ATTACH 'test.db2' AS two }

  db1 eval {
    CREATE TABLE t1(x);
    INSERT INTO t1 VALUES(1);
    INSERT INTO t1 VALUES(2);
    INSERT INTO t1 VALUES(3);
    CREATE TABLE two.t2(x);
    INSERT INTO t2 SELECT * FROM t1;
  }

  sqlite3 db2 test.db -vfs tvfs
  db2 eval { SELECT * FROM t1 }
} {1 2 3}

# Create a prepared statement on db2 that will attempt a schema change
# in test.db.  Meanwhile, start a transaction on db1 that changes
# the schema of test.db and that creates a rollback journal on test2.db
#
do_test 2.2 {
  set ::STMT [sqlite3_prepare db2 "CREATE INDEX i1 ON t1(x)" -1 tail]
  db1 eval {
    BEGIN;
      CREATE INDEX i1 ON t1(x);
      INSERT INTO t2 VALUES('value!');
  }
} {}

# Set up a callback that will cause db2 to try to execute its
# schema change when db1 accesses the journal file of test2.db.
#
# This callback will be invoked after the content of test.db has
# be rolled back but before the schema has been reset.  If the
# sqlite3RollbackAll() operation is not thread-atomic, then the
# db2 statement in the callback will see old content with the newer
# schema, which is wrong.
#
tvfs filter xRead
tvfs script read_callback
unset -nocomplain ::some_time_laster
unset -nocomplain ::thread_result
proc read_callback {call file args} { 
  if {[string match *test.db2-journal $file]} {
    tvfs filter {}   ;# Ensure that tvfs callbacks to do run on the
                      # child thread
    sqlthread spawn ::thread_result [subst -nocommands {
      sqlite3_step $::STMT
      set rc [sqlite3_finalize $::STMT]
    }]
    after 1000 { set ::some_time_later 1 }
    vwait ::some_time_later
  }
}
do_test 2.3 { db1 eval ROLLBACK } {}

# Verify that the db2 statement invoked by the callback detected the
# schema change.
#
if {[info exists ::thread_result]==0} { vwait ::thread_result }
do_test 2.4 { 
  list $::thread_result [sqlite3_errmsg db2] 
} {SQLITE_SCHEMA {database schema has changed}}

db1 close
db2 close
tvfs delete

sqlite3_enable_shared_cache $::enable_shared_cache
finish_test