/ Check-in [f308c485]
Login

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

Overview
Comment:Allow read transactions to be freely opened and closed by SQL statements run from within the implementation of user-functions if the user-function is called by a SELECT statement that does not access any database tables (e.g. "SELECT user_function();").
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:f308c4851726b4b75636f714466f2314f56e3ec0
User & Date: dan 2013-06-28 19:41:43
Context
2013-06-28
21:12
Add the SQLITE_DEFAULT_AUTOMATIC_INDEX compile-time option, which if set to zero turns automatic indices off by default. Increase the estimated cost of an automatic index. Additional minor refactoring of the automatic index code. check-in: 459b3179 user: drh tags: trunk
19:41
Allow read transactions to be freely opened and closed by SQL statements run from within the implementation of user-functions if the user-function is called by a SELECT statement that does not access any database tables (e.g. "SELECT user_function();"). check-in: f308c485 user: dan tags: trunk
17:29
Add a bit to the SQLITE_TESTCTRL_OPTIMIZATIONS option for sqlite3_file_control() that will disable the use of SQLITE_STAT3 information in the query planner. check-in: 60c19b86 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/btree.c.

3275
3276
3277
3278
3279
3280
3281
3282
3283
3284
3285
3286
3287
3288
3289
  BtShared *pBt = p->pBt;
  sqlite3 *db = p->db;
  assert( sqlite3BtreeHoldsMutex(p) );

#ifndef SQLITE_OMIT_AUTOVACUUM
  pBt->bDoTruncate = 0;
#endif
  if( p->inTrans>TRANS_NONE && db->nVdbeActive>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{
    /* If the handle had any kind of transaction open, decrement the 







|







3275
3276
3277
3278
3279
3280
3281
3282
3283
3284
3285
3286
3287
3288
3289
  BtShared *pBt = p->pBt;
  sqlite3 *db = p->db;
  assert( sqlite3BtreeHoldsMutex(p) );

#ifndef SQLITE_OMIT_AUTOVACUUM
  pBt->bDoTruncate = 0;
#endif
  if( p->inTrans>TRANS_NONE && db->nVdbeRead>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{
    /* If the handle had any kind of transaction open, decrement the 

Changes to src/vdbe.c.

2682
2683
2684
2685
2686
2687
2688

2689
2690
2691
2692
2693
2694
2695
....
2843
2844
2845
2846
2847
2848
2849

2850
2851
2852
2853
2854
2855
2856
....
2949
2950
2951
2952
2953
2954
2955
2956
2957
2958
2959
2960
2961
2962
2963
....
4740
4741
4742
4743
4744
4745
4746

4747

4748
4749
4750
4751
4752
4753
4754
4755
4756
4757
4758
4759
....
5568
5569
5570
5571
5572
5573
5574
5575
5576
5577
5578
5579
5580
5581
5582
  /* Assert that the p1 parameter is valid. Also that if there is no open
  ** transaction, then there cannot be any savepoints. 
  */
  assert( db->pSavepoint==0 || db->autoCommit==0 );
  assert( p1==SAVEPOINT_BEGIN||p1==SAVEPOINT_RELEASE||p1==SAVEPOINT_ROLLBACK );
  assert( db->pSavepoint || db->isTransactionSavepoint==0 );
  assert( checkSavepointCount(db) );


  if( p1==SAVEPOINT_BEGIN ){
    if( db->nVdbeWrite>0 ){
      /* A new savepoint cannot be created if there are active write 
      ** statements (i.e. open read/write incremental blob handles).
      */
      sqlite3SetString(&p->zErrMsg, db, "cannot open savepoint - "
................................................................................

  desiredAutoCommit = pOp->p1;
  iRollback = pOp->p2;
  turnOnAC = desiredAutoCommit && !db->autoCommit;
  assert( desiredAutoCommit==1 || desiredAutoCommit==0 );
  assert( desiredAutoCommit==1 || iRollback==0 );
  assert( db->nVdbeActive>0 );  /* At least this one VM is active */


#if 0
  if( turnOnAC && iRollback && db->nVdbeActive>1 ){
    /* If this instruction implements a ROLLBACK and other VMs are
    ** still running, and a transaction is active, return an error indicating
    ** that the other VMs must complete first. 
    */
................................................................................
      goto vdbe_return;
    }
    if( rc!=SQLITE_OK ){
      goto abort_due_to_error;
    }

    if( pOp->p2 && p->usesStmtJournal 
     && (db->autoCommit==0 || db->nVdbeActive>1) 
    ){
      assert( sqlite3BtreeIsInTrans(pBt) );
      if( p->iStatement==0 ){
        assert( db->nStatement>=0 && db->nSavepoint>=0 );
        db->nStatement++; 
        p->iStatement = db->nSavepoint + db->nStatement;
      }
................................................................................
  Vdbe *pVdbe;
  int iDb;

  assert( p->readOnly==0 );
#ifndef SQLITE_OMIT_VIRTUALTABLE
  iCnt = 0;
  for(pVdbe=db->pVdbe; pVdbe; pVdbe = pVdbe->pNext){

    if( pVdbe->magic==VDBE_MAGIC_RUN && pVdbe->inVtabMethod<2 && pVdbe->pc>=0 ){

      iCnt++;
    }
  }
#else
  iCnt = db->nVdbeActive;
#endif
  pOut->flags = MEM_Null;
  if( iCnt>1 ){
    rc = SQLITE_LOCKED;
    p->errorAction = OE_Abort;
  }else{
    iDb = pOp->p3;
................................................................................
  ){
    eNew = eOld;
  }

  if( (eNew!=eOld)
   && (eOld==PAGER_JOURNALMODE_WAL || eNew==PAGER_JOURNALMODE_WAL)
  ){
    if( !db->autoCommit || db->nVdbeActive>1 ){
      rc = SQLITE_ERROR;
      sqlite3SetString(&p->zErrMsg, db, 
          "cannot change %s wal mode from within a transaction",
          (eNew==PAGER_JOURNALMODE_WAL ? "into" : "out of")
      );
      break;
    }else{







>







 







>







 







|







 







>
|
>




|







 







|







2682
2683
2684
2685
2686
2687
2688
2689
2690
2691
2692
2693
2694
2695
2696
....
2844
2845
2846
2847
2848
2849
2850
2851
2852
2853
2854
2855
2856
2857
2858
....
2951
2952
2953
2954
2955
2956
2957
2958
2959
2960
2961
2962
2963
2964
2965
....
4742
4743
4744
4745
4746
4747
4748
4749
4750
4751
4752
4753
4754
4755
4756
4757
4758
4759
4760
4761
4762
4763
....
5572
5573
5574
5575
5576
5577
5578
5579
5580
5581
5582
5583
5584
5585
5586
  /* Assert that the p1 parameter is valid. Also that if there is no open
  ** transaction, then there cannot be any savepoints. 
  */
  assert( db->pSavepoint==0 || db->autoCommit==0 );
  assert( p1==SAVEPOINT_BEGIN||p1==SAVEPOINT_RELEASE||p1==SAVEPOINT_ROLLBACK );
  assert( db->pSavepoint || db->isTransactionSavepoint==0 );
  assert( checkSavepointCount(db) );
  assert( p->bIsReader );

  if( p1==SAVEPOINT_BEGIN ){
    if( db->nVdbeWrite>0 ){
      /* A new savepoint cannot be created if there are active write 
      ** statements (i.e. open read/write incremental blob handles).
      */
      sqlite3SetString(&p->zErrMsg, db, "cannot open savepoint - "
................................................................................

  desiredAutoCommit = pOp->p1;
  iRollback = pOp->p2;
  turnOnAC = desiredAutoCommit && !db->autoCommit;
  assert( desiredAutoCommit==1 || desiredAutoCommit==0 );
  assert( desiredAutoCommit==1 || iRollback==0 );
  assert( db->nVdbeActive>0 );  /* At least this one VM is active */
  assert( p->bIsReader );

#if 0
  if( turnOnAC && iRollback && db->nVdbeActive>1 ){
    /* If this instruction implements a ROLLBACK and other VMs are
    ** still running, and a transaction is active, return an error indicating
    ** that the other VMs must complete first. 
    */
................................................................................
      goto vdbe_return;
    }
    if( rc!=SQLITE_OK ){
      goto abort_due_to_error;
    }

    if( pOp->p2 && p->usesStmtJournal 
     && (db->autoCommit==0 || db->nVdbeRead>1) 
    ){
      assert( sqlite3BtreeIsInTrans(pBt) );
      if( p->iStatement==0 ){
        assert( db->nStatement>=0 && db->nSavepoint>=0 );
        db->nStatement++; 
        p->iStatement = db->nSavepoint + db->nStatement;
      }
................................................................................
  Vdbe *pVdbe;
  int iDb;

  assert( p->readOnly==0 );
#ifndef SQLITE_OMIT_VIRTUALTABLE
  iCnt = 0;
  for(pVdbe=db->pVdbe; pVdbe; pVdbe = pVdbe->pNext){
    if( pVdbe->magic==VDBE_MAGIC_RUN && pVdbe->bIsReader 
     && pVdbe->inVtabMethod<2 && pVdbe->pc>=0 
    ){
      iCnt++;
    }
  }
#else
  iCnt = db->nVdbeRead;
#endif
  pOut->flags = MEM_Null;
  if( iCnt>1 ){
    rc = SQLITE_LOCKED;
    p->errorAction = OE_Abort;
  }else{
    iDb = pOp->p3;
................................................................................
  ){
    eNew = eOld;
  }

  if( (eNew!=eOld)
   && (eOld==PAGER_JOURNALMODE_WAL || eNew==PAGER_JOURNALMODE_WAL)
  ){
    if( !db->autoCommit || db->nVdbeRead>1 ){
      rc = SQLITE_ERROR;
      sqlite3SetString(&p->zErrMsg, db, 
          "cannot change %s wal mode from within a transaction",
          (eNew==PAGER_JOURNALMODE_WAL ? "into" : "out of")
      );
      break;
    }else{

Changes to src/vdbeaux.c.

409
410
411
412
413
414
415


416
417
418
419
420
421
422
...
443
444
445
446
447
448
449

450
451
452
453
454
455
456
....
2129
2130
2131
2132
2133
2134
2135
2136

2137
2138
2139
2140
2141
2142
2143
2144

    pOp->opflags = sqlite3OpcodeProperty[opcode];
    if( opcode==OP_Function || opcode==OP_AggStep ){
      if( pOp->p5>nMaxArgs ) nMaxArgs = pOp->p5;
    }else if( opcode==OP_Transaction ){
      if( pOp->p2!=0 ) p->readOnly = 0;
      p->bIsReader = 1;


    }else if( opcode==OP_Vacuum
           || opcode==OP_JournalMode
#ifndef SQLITE_OMIT_VIRTUALTABLE
           || opcode==OP_Checkpoint
#endif
    ){
      p->readOnly = 0;
................................................................................
      assert( -1-pOp->p2<p->nLabel );
      pOp->p2 = aLabel[-1-pOp->p2];
    }
  }
  sqlite3DbFree(p->db, p->aLabel);
  p->aLabel = 0;
  *pMaxFuncArgs = nMaxArgs;

}

/*
** Return the address of the next instruction to be inserted.
*/
int sqlite3VdbeCurrentAddr(Vdbe *p){
  assert( p->magic==VDBE_MAGIC_INIT );
................................................................................
  if( p->aOnceFlag ) memset(p->aOnceFlag, 0, p->nOnceFlag);
  closeAllCursors(p);
  if( p->magic!=VDBE_MAGIC_RUN ){
    return SQLITE_OK;
  }
  checkActiveVdbeCnt(db);

  /* No commit or rollback needed if the program never started */

  if( p->pc>=0 ){
    int mrc;   /* Primary error code from p->rc */
    int eStatementOp = 0;
    int isSpecialError;            /* Set to true if a 'special' error */

    /* Lock all btrees used by the statement */
    sqlite3VdbeEnter(p);








>
>







 







>







 







|
>
|







409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
...
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
....
2132
2133
2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
2148

    pOp->opflags = sqlite3OpcodeProperty[opcode];
    if( opcode==OP_Function || opcode==OP_AggStep ){
      if( pOp->p5>nMaxArgs ) nMaxArgs = pOp->p5;
    }else if( opcode==OP_Transaction ){
      if( pOp->p2!=0 ) p->readOnly = 0;
      p->bIsReader = 1;
    }else if( opcode==OP_AutoCommit || opcode==OP_Savepoint ){
      p->bIsReader = 1;
    }else if( opcode==OP_Vacuum
           || opcode==OP_JournalMode
#ifndef SQLITE_OMIT_VIRTUALTABLE
           || opcode==OP_Checkpoint
#endif
    ){
      p->readOnly = 0;
................................................................................
      assert( -1-pOp->p2<p->nLabel );
      pOp->p2 = aLabel[-1-pOp->p2];
    }
  }
  sqlite3DbFree(p->db, p->aLabel);
  p->aLabel = 0;
  *pMaxFuncArgs = nMaxArgs;
  assert( p->bIsReader!=0 || p->btreeMask==0 );
}

/*
** Return the address of the next instruction to be inserted.
*/
int sqlite3VdbeCurrentAddr(Vdbe *p){
  assert( p->magic==VDBE_MAGIC_INIT );
................................................................................
  if( p->aOnceFlag ) memset(p->aOnceFlag, 0, p->nOnceFlag);
  closeAllCursors(p);
  if( p->magic!=VDBE_MAGIC_RUN ){
    return SQLITE_OK;
  }
  checkActiveVdbeCnt(db);

  /* No commit or rollback needed if the program never started or if the
  ** SQL statement does not read or write a database file.  */
  if( p->pc>=0 && p->bIsReader ){
    int mrc;   /* Primary error code from p->rc */
    int eStatementOp = 0;
    int isSpecialError;            /* Set to true if a 'special' error */

    /* Lock all btrees used by the statement */
    sqlite3VdbeEnter(p);

Changes to test/wal6.test.

111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
...
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147

do_execsql_test 2.5 {
  SELECT * FROM t1;
  COMMIT;
  INSERT INTO t1 VALUES('x', 'x') 
} {1 one 2 two}

if 0 {
proc test3 {prefix} {
  do_test $prefix.1 {
    execsql { SELECT count(*) FROM t1 } 
  } {0}
  do_test $prefix.2 {
    execsql { INSERT INTO t1 VALUES('x', 'x') } db2
  } {}
................................................................................

do_execsql_test 2.6.1 { DELETE FROM t1 }
test3 2.6.2

db func test3 test3
do_execsql_test 2.6.3 { DELETE FROM t1 }
db eval {SELECT test3('2.6.4')}
}

do_test 2.x {
  db2 close
} {}

#-------------------------------------------------------------------------
# Check that if BEGIN IMMEDIATE fails, it does not leave the user with







<







 







<







111
112
113
114
115
116
117

118
119
120
121
122
123
124
...
132
133
134
135
136
137
138

139
140
141
142
143
144
145

do_execsql_test 2.5 {
  SELECT * FROM t1;
  COMMIT;
  INSERT INTO t1 VALUES('x', 'x') 
} {1 one 2 two}


proc test3 {prefix} {
  do_test $prefix.1 {
    execsql { SELECT count(*) FROM t1 } 
  } {0}
  do_test $prefix.2 {
    execsql { INSERT INTO t1 VALUES('x', 'x') } db2
  } {}
................................................................................

do_execsql_test 2.6.1 { DELETE FROM t1 }
test3 2.6.2

db func test3 test3
do_execsql_test 2.6.3 { DELETE FROM t1 }
db eval {SELECT test3('2.6.4')}


do_test 2.x {
  db2 close
} {}

#-------------------------------------------------------------------------
# Check that if BEGIN IMMEDIATE fails, it does not leave the user with