/ Check-in [9ad846e5]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Instead of a root page number, log the object (table or index) name if a page level locking conflict is detected.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | begin-concurrent
Files: files | file ages | folders
SHA3-256: 9ad846e57bd427adc7c29768cabca18905f7f978168e0642a5917d894fda8bfd
User & Date: dan 2017-05-29 19:23:56
Wiki:begin-concurrent
Context
2017-05-31
17:06
Generate extra log messages in response to irregularites in the pointer-map used by "BEGIN CONCURRENT" transactions. check-in: f7e3e2bc user: dan tags: begin-concurrent
2017-05-29
19:23
Instead of a root page number, log the object (table or index) name if a page level locking conflict is detected. check-in: 9ad846e5 user: dan tags: begin-concurrent
14:27
Enhance the log messages emitted when a page conflict is detected. check-in: 92618492 user: dan tags: begin-concurrent
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/btree.c.

10282
10283
10284
10285
10286
10287
10288

10289
10290
10291
10292

















































10293
10294
10295
10296
10297
10298
10299
** obtained for an CONCURRENT transaction due to a conflict with an already
** committed transaction, SQLITE_BUSY_SNAPSHOT is returned. Otherwise, if
** some other error (OOM, IO, etc.) occurs, the relevant SQLite error code
** is returned.
*/
int sqlite3BtreeExclusiveLock(Btree *p){
  int rc;

  BtShared *pBt = p->pBt;
  assert( p->inTrans==TRANS_WRITE && pBt->pPage1 );
  sqlite3BtreeEnter(p);
  rc = sqlite3PagerExclusiveLock(pBt->pPager, pBt->pPage1->pDbPage);

















































  sqlite3BtreeLeave(p);
  return rc;
}

#if !defined(SQLITE_OMIT_SHARED_CACHE)
/*
** Return true if the Btree passed as the only argument is sharable.







>



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







10282
10283
10284
10285
10286
10287
10288
10289
10290
10291
10292
10293
10294
10295
10296
10297
10298
10299
10300
10301
10302
10303
10304
10305
10306
10307
10308
10309
10310
10311
10312
10313
10314
10315
10316
10317
10318
10319
10320
10321
10322
10323
10324
10325
10326
10327
10328
10329
10330
10331
10332
10333
10334
10335
10336
10337
10338
10339
10340
10341
10342
10343
10344
10345
10346
10347
10348
10349
** obtained for an CONCURRENT transaction due to a conflict with an already
** committed transaction, SQLITE_BUSY_SNAPSHOT is returned. Otherwise, if
** some other error (OOM, IO, etc.) occurs, the relevant SQLite error code
** is returned.
*/
int sqlite3BtreeExclusiveLock(Btree *p){
  int rc;
  Pgno pgno = 0;
  BtShared *pBt = p->pBt;
  assert( p->inTrans==TRANS_WRITE && pBt->pPage1 );
  sqlite3BtreeEnter(p);
  rc = sqlite3PagerExclusiveLock(pBt->pPager, pBt->pPage1->pDbPage, &pgno);
  if( rc==SQLITE_BUSY_SNAPSHOT && pgno ){
    PgHdr *pPg = 0;
    int rc2 = sqlite3PagerGet(pBt->pPager, pgno, &pPg, 0);
    if( rc2==SQLITE_OK ){
      int bWrite = -1;
      const char *zObj = 0;
      const char *zTab = 0;

      if( pPg ){
        Pgno pgnoRoot = 0;
        HashElem *pE;
        Schema *pSchema;

        pgnoRoot = ((MemPage*)sqlite3PagerGetExtra(pPg))->pgnoRoot;
        bWrite = sqlite3PagerIswriteable(pPg);
        sqlite3PagerUnref(pPg);

        pSchema = sqlite3SchemaGet(p->db, p);
        if( pSchema ){
          for(pE=sqliteHashFirst(&pSchema->tblHash); pE; pE=sqliteHashNext(pE)){
            Table *pTab = (Table *)sqliteHashData(pE);
            if( pTab->tnum==(int)pgnoRoot ){
              zObj = pTab->zName;
              zTab = 0;
            }else{
              Index *pIdx;
              for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
                if( pIdx->tnum==(int)pgnoRoot ){
                  zObj = pIdx->zName;
                  zTab = pTab->zName;
                }
              }
            }
          }
        }
      }

      sqlite3_log(SQLITE_OK,
          "cannot commit CONCURRENT transaction "
          "- conflict at page %d "
          "(%s page; part of db %s %s%s%s)",
          (int)pgno,
          (bWrite==0?"read-only":(bWrite>0?"read/write":"unknown")),
          (zTab ? "index" : "table"),
          (zTab ? zTab : ""), (zTab ? "." : ""), (zObj ? zObj : "UNKNOWN")
      );
    }
  }

  sqlite3BtreeLeave(p);
  return rc;
}

#if !defined(SQLITE_OMIT_SHARED_CACHE)
/*
** Return true if the Btree passed as the only argument is sharable.

Changes to src/pager.c.

4262
4263
4264
4265
4266
4267
4268
4269
4270
4271
4272
4273
4274
4275
4276
....
6343
6344
6345
6346
6347
6348
6349
6350
6351
6352
6353
6354
6355
6356
6357
....
6363
6364
6365
6366
6367
6368
6369

6370

6371
6372
6373
6374
6375
6376
6377

  assert( pPager->eState==PAGER_WRITER_CACHEMOD
       || pPager->eState==PAGER_WRITER_DBMOD
  );
  assert( assert_pager_state(pPager) );
  assert( !pagerUseWal(pPager) );

  rc = sqlite3PagerExclusiveLock(pPager, 0);
  if( rc!=SQLITE_OK ) return rc;

  if( !pPager->noSync ){
    assert( !pPager->tempFile );
    if( isOpen(pPager->jfd) && pPager->journalMode!=PAGER_JOURNALMODE_MEMORY ){
      const int iDc = sqlite3OsDeviceCharacteristics(pPager->fd);
      assert( isOpen(pPager->jfd) );
................................................................................
** If the required locks are already held or successfully obtained and
** the transaction can be committed, SQLITE_OK is returned. If a required lock
** cannot be obtained, SQLITE_BUSY is returned. Or, if the current transaction
** is CONCURRENT and cannot be committed due to a conflict, SQLITE_BUSY_SNAPSHOT
** is returned. Otherwise, if some other error occurs (IO error, OOM etc.),
** and SQLite error code is returned.
*/
int sqlite3PagerExclusiveLock(Pager *pPager, PgHdr *pPage1){
  int rc = pPager->errCode;
  assert( assert_pager_state(pPager) );
  if( rc==SQLITE_OK ){
    assert( pPager->eState==PAGER_WRITER_CACHEMOD 
         || pPager->eState==PAGER_WRITER_DBMOD 
         || pPager->eState==PAGER_WRITER_LOCKED 
    );
................................................................................
    else{
      if( pPager->pAllRead ){
        /* This is an CONCURRENT transaction. Attempt to lock the wal database
        ** here. If SQLITE_BUSY (but not SQLITE_BUSY_SNAPSHOT) is returned,
        ** invoke the busy-handler and try again for as long as it returns
        ** non-zero.  */
        do {

          rc = sqlite3WalLockForCommit(pPager->pWal, pPage1, pPager->pAllRead);

        }while( rc==SQLITE_BUSY 
             && pPager->xBusyHandler(pPager->pBusyHandlerArg) 
        );
      }
    }
#endif /* SQLITE_OMIT_CONCURRENT */
  }







|







 







|







 







>
|
>







4262
4263
4264
4265
4266
4267
4268
4269
4270
4271
4272
4273
4274
4275
4276
....
6343
6344
6345
6346
6347
6348
6349
6350
6351
6352
6353
6354
6355
6356
6357
....
6363
6364
6365
6366
6367
6368
6369
6370
6371
6372
6373
6374
6375
6376
6377
6378
6379

  assert( pPager->eState==PAGER_WRITER_CACHEMOD
       || pPager->eState==PAGER_WRITER_DBMOD
  );
  assert( assert_pager_state(pPager) );
  assert( !pagerUseWal(pPager) );

  rc = sqlite3PagerExclusiveLock(pPager, 0, 0);
  if( rc!=SQLITE_OK ) return rc;

  if( !pPager->noSync ){
    assert( !pPager->tempFile );
    if( isOpen(pPager->jfd) && pPager->journalMode!=PAGER_JOURNALMODE_MEMORY ){
      const int iDc = sqlite3OsDeviceCharacteristics(pPager->fd);
      assert( isOpen(pPager->jfd) );
................................................................................
** If the required locks are already held or successfully obtained and
** the transaction can be committed, SQLITE_OK is returned. If a required lock
** cannot be obtained, SQLITE_BUSY is returned. Or, if the current transaction
** is CONCURRENT and cannot be committed due to a conflict, SQLITE_BUSY_SNAPSHOT
** is returned. Otherwise, if some other error occurs (IO error, OOM etc.),
** and SQLite error code is returned.
*/
int sqlite3PagerExclusiveLock(Pager *pPager, PgHdr *pPage1, Pgno *piConflict){
  int rc = pPager->errCode;
  assert( assert_pager_state(pPager) );
  if( rc==SQLITE_OK ){
    assert( pPager->eState==PAGER_WRITER_CACHEMOD 
         || pPager->eState==PAGER_WRITER_DBMOD 
         || pPager->eState==PAGER_WRITER_LOCKED 
    );
................................................................................
    else{
      if( pPager->pAllRead ){
        /* This is an CONCURRENT transaction. Attempt to lock the wal database
        ** here. If SQLITE_BUSY (but not SQLITE_BUSY_SNAPSHOT) is returned,
        ** invoke the busy-handler and try again for as long as it returns
        ** non-zero.  */
        do {
          rc = sqlite3WalLockForCommit(
              pPager->pWal, pPage1, pPager->pAllRead, piConflict
          );
        }while( rc==SQLITE_BUSY 
             && pPager->xBusyHandler(pPager->pBusyHandlerArg) 
        );
      }
    }
#endif /* SQLITE_OMIT_CONCURRENT */
  }

Changes to src/pager.h.

160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
void *sqlite3PagerGetData(DbPage *); 
void *sqlite3PagerGetExtra(DbPage *); 

/* Functions used to manage pager transactions and savepoints. */
void sqlite3PagerPagecount(Pager*, int*);
int sqlite3PagerBegin(Pager*, int exFlag, int);
int sqlite3PagerCommitPhaseOne(Pager*,const char *zMaster, int);
int sqlite3PagerExclusiveLock(Pager*, DbPage *pPage1);
int sqlite3PagerSync(Pager *pPager, const char *zMaster);
int sqlite3PagerCommitPhaseTwo(Pager*);
int sqlite3PagerRollback(Pager*);
int sqlite3PagerOpenSavepoint(Pager *pPager, int n);
int sqlite3PagerSavepoint(Pager *pPager, int op, int iSavepoint);
int sqlite3PagerSharedLock(Pager *pPager);








|







160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
void *sqlite3PagerGetData(DbPage *); 
void *sqlite3PagerGetExtra(DbPage *); 

/* Functions used to manage pager transactions and savepoints. */
void sqlite3PagerPagecount(Pager*, int*);
int sqlite3PagerBegin(Pager*, int exFlag, int);
int sqlite3PagerCommitPhaseOne(Pager*,const char *zMaster, int);
int sqlite3PagerExclusiveLock(Pager*, DbPage *pPage1, Pgno*);
int sqlite3PagerSync(Pager *pPager, const char *zMaster);
int sqlite3PagerCommitPhaseTwo(Pager*);
int sqlite3PagerRollback(Pager*);
int sqlite3PagerOpenSavepoint(Pager *pPager, int n);
int sqlite3PagerSavepoint(Pager *pPager, int op, int iSavepoint);
int sqlite3PagerSharedLock(Pager *pPager);

Changes to src/wal.c.

239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
....
2853
2854
2855
2856
2857
2858
2859
2860





2861
2862
2863
2864
2865
2866
2867
....
2913
2914
2915
2916
2917
2918
2919
2920
2921
2922
2923
2924
2925
2926
2927
2928
2929
2930
2931
2932
2933
2934
2935
2936
2937
2938
2939
2940
2941
2942
2943
2944
2945
2946
** When a rollback occurs, the value of K is decreased. Hash table entries
** that correspond to frames greater than the new K value are removed
** from the hash table at this point.
*/
#ifndef SQLITE_OMIT_WAL

#include "wal.h"
#include "btreeInt.h"

/*
** Trace output macros
*/
#if defined(SQLITE_TEST) && defined(SQLITE_DEBUG)
int sqlite3WalTrace = 0;
# define WALTRACE(X)  if(sqlite3WalTrace) sqlite3DebugPrintf X
................................................................................
** If no error occurs and the caller may proceed with committing the 
** transaction, SQLITE_OK is returned. SQLITE_BUSY is returned if the WRITER
** lock cannot be obtained. Or, if the WRITER lock can be obtained but there
** are conflicts with a committed transaction, SQLITE_BUSY_SNAPSHOT. Finally,
** if an error (i.e. an OOM condition or IO error), an SQLite error code
** is returned.
*/
int sqlite3WalLockForCommit(Wal *pWal, PgHdr *pPage1, Bitvec *pAllRead){





  Pager *pPager = pPage1->pPager;
  int rc = walWriteLock(pWal);

  /* If the database has been modified since this transaction was started,
  ** check if it is still possible to commit. The transaction can be 
  ** committed if:
  **
................................................................................
              sz = (sz&0xfe00) + ((sz&0x0001)<<16);
              iOffset = walFrameOffset(i+iZero, sz) + WAL_FRAME_HDRSIZE + 40;
              rc = sqlite3OsRead(pWal->pWalFd, aNew, sizeof(aNew), iOffset);
              if( rc==SQLITE_OK && memcmp(aOld, aNew, sizeof(aNew)) ){
                rc = SQLITE_BUSY_SNAPSHOT;
              }
            }else if( sqlite3BitvecTestNotNull(pAllRead, aPgno[i]) ){
              PgHdr *pPg = 0;
              rc = sqlite3PagerGet(pPage1->pPager, aPgno[i], &pPg, 0);
              if( rc==SQLITE_OK ){
                Pgno pgnoRoot = 0;
                int bWrite = -1;
                if( pPg ){
                  pgnoRoot = ((MemPage*)sqlite3PagerGetExtra(pPg))->pgnoRoot;
                  bWrite = sqlite3PagerIswriteable(pPg);
                  sqlite3PagerUnref(pPg);
                }
                sqlite3_log(SQLITE_OK,
                  "cannot commit CONCURRENT transaction "
                  "- conflict at page %d "
                  "(%s page; part of b-tree with root page %d)",
                  (int)aPgno[i], 
                  (bWrite==0?"read-only":(bWrite>0?"read/write":"unknown")),
                  (int)pgnoRoot
                );
                rc = SQLITE_BUSY_SNAPSHOT;
              }
            }else if( (pPg = sqlite3PagerLookup(pPager, aPgno[i])) ){
              /* Page aPgno[i], which is present in the pager cache, has been
              ** modified since the current CONCURRENT transaction was started.
              ** However it was not read by the current transaction, so is not
              ** a conflict. There are two possibilities: (a) the page was
              ** allocated at the of the file by the current transaction or 
              ** (b) was present in the cache at the start of the transaction.







<







 







|
>
>
>
>
>







 







<
<
<
<
<
<
<
<
<
<
<
<
<
<
|
<
<
<
|
<







239
240
241
242
243
244
245

246
247
248
249
250
251
252
....
2852
2853
2854
2855
2856
2857
2858
2859
2860
2861
2862
2863
2864
2865
2866
2867
2868
2869
2870
2871
....
2917
2918
2919
2920
2921
2922
2923














2924



2925

2926
2927
2928
2929
2930
2931
2932
** When a rollback occurs, the value of K is decreased. Hash table entries
** that correspond to frames greater than the new K value are removed
** from the hash table at this point.
*/
#ifndef SQLITE_OMIT_WAL

#include "wal.h"


/*
** Trace output macros
*/
#if defined(SQLITE_TEST) && defined(SQLITE_DEBUG)
int sqlite3WalTrace = 0;
# define WALTRACE(X)  if(sqlite3WalTrace) sqlite3DebugPrintf X
................................................................................
** If no error occurs and the caller may proceed with committing the 
** transaction, SQLITE_OK is returned. SQLITE_BUSY is returned if the WRITER
** lock cannot be obtained. Or, if the WRITER lock can be obtained but there
** are conflicts with a committed transaction, SQLITE_BUSY_SNAPSHOT. Finally,
** if an error (i.e. an OOM condition or IO error), an SQLite error code
** is returned.
*/
int sqlite3WalLockForCommit(
  Wal *pWal, 
  PgHdr *pPage1, 
  Bitvec *pAllRead, 
  Pgno *piConflict
){
  Pager *pPager = pPage1->pPager;
  int rc = walWriteLock(pWal);

  /* If the database has been modified since this transaction was started,
  ** check if it is still possible to commit. The transaction can be 
  ** committed if:
  **
................................................................................
              sz = (sz&0xfe00) + ((sz&0x0001)<<16);
              iOffset = walFrameOffset(i+iZero, sz) + WAL_FRAME_HDRSIZE + 40;
              rc = sqlite3OsRead(pWal->pWalFd, aNew, sizeof(aNew), iOffset);
              if( rc==SQLITE_OK && memcmp(aOld, aNew, sizeof(aNew)) ){
                rc = SQLITE_BUSY_SNAPSHOT;
              }
            }else if( sqlite3BitvecTestNotNull(pAllRead, aPgno[i]) ){














              *piConflict = aPgno[i];



              rc = SQLITE_BUSY_SNAPSHOT;

            }else if( (pPg = sqlite3PagerLookup(pPager, aPgno[i])) ){
              /* Page aPgno[i], which is present in the pager cache, has been
              ** modified since the current CONCURRENT transaction was started.
              ** However it was not read by the current transaction, so is not
              ** a conflict. There are two possibilities: (a) the page was
              ** allocated at the of the file by the current transaction or 
              ** (b) was present in the cache at the start of the transaction.

Changes to src/wal.h.

132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
int sqlite3WalSnapshotGet(Wal *pWal, sqlite3_snapshot **ppSnapshot);
void sqlite3WalSnapshotOpen(Wal *pWal, sqlite3_snapshot *pSnapshot);
int sqlite3WalSnapshotRecover(Wal *pWal);
#endif

#ifndef SQLITE_OMIT_CONCURRENT
/* Tell the wal layer that we want to commit a concurrent transaction */
int sqlite3WalLockForCommit(Wal *pWal, PgHdr *pPg, Bitvec *pRead);

/* Upgrade the state of the client to take into account changes written
** by other connections */
int sqlite3WalUpgradeSnapshot(Wal *pWal);
#endif /* SQLITE_OMIT_CONCURRENT */

#ifdef SQLITE_ENABLE_ZIPVFS







|







132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
int sqlite3WalSnapshotGet(Wal *pWal, sqlite3_snapshot **ppSnapshot);
void sqlite3WalSnapshotOpen(Wal *pWal, sqlite3_snapshot *pSnapshot);
int sqlite3WalSnapshotRecover(Wal *pWal);
#endif

#ifndef SQLITE_OMIT_CONCURRENT
/* Tell the wal layer that we want to commit a concurrent transaction */
int sqlite3WalLockForCommit(Wal *pWal, PgHdr *pPg, Bitvec *pRead, Pgno*);

/* Upgrade the state of the client to take into account changes written
** by other connections */
int sqlite3WalUpgradeSnapshot(Wal *pWal);
#endif /* SQLITE_OMIT_CONCURRENT */

#ifdef SQLITE_ENABLE_ZIPVFS

Changes to test/concurrent5.test.

55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
..
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
..
93
94
95
96
97
98
99
100
























101
102
103
104
105
106
107
108
109
  db eval {
    INSERT INTO t1 VALUES(randomblob(200), randomblob(200));
  }

  catchsql COMMIT db2
} {1 {database is locked}}
do_test_conflict_msg 1.1.2 {
  conflict at page 2 (read-only page; part of b-tree with root page 2)
}

do_test 1.2.1 {
  set ::log [list]
  db2 eval {
    ROLLBACK;
    BEGIN CONCURRENT;
................................................................................
    INSERT INTO t1 VALUES(12, 11);
  }

  catchsql COMMIT db2
} {1 {database is locked}}

do_test_conflict_msg 1.2.2 {
  conflict at page 105 (read/write page; part of b-tree with root page 2)
}

do_test 1.3.1 {
  set ::log [list]
  db2 eval {
    ROLLBACK;
    BEGIN CONCURRENT;
................................................................................
    INSERT INTO t2 VALUES('y');
  }

  catchsql COMMIT db2
} {1 {database is locked}}

do_test_conflict_msg 1.3.2 {
  conflict at page 3 (read/write page; part of b-tree with root page 3)
























}

db close
db2 close
sqlite3_shutdown
test_sqlite3_log 
sqlite3_initialize
finish_test








|







 







|







 







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









55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
..
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
..
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
  db eval {
    INSERT INTO t1 VALUES(randomblob(200), randomblob(200));
  }

  catchsql COMMIT db2
} {1 {database is locked}}
do_test_conflict_msg 1.1.2 {
  conflict at page 2 (read-only page; part of db table t1)
}

do_test 1.2.1 {
  set ::log [list]
  db2 eval {
    ROLLBACK;
    BEGIN CONCURRENT;
................................................................................
    INSERT INTO t1 VALUES(12, 11);
  }

  catchsql COMMIT db2
} {1 {database is locked}}

do_test_conflict_msg 1.2.2 {
  conflict at page 105 (read/write page; part of db table t1)
}

do_test 1.3.1 {
  set ::log [list]
  db2 eval {
    ROLLBACK;
    BEGIN CONCURRENT;
................................................................................
    INSERT INTO t2 VALUES('y');
  }

  catchsql COMMIT db2
} {1 {database is locked}}

do_test_conflict_msg 1.3.2 {
  conflict at page 3 (read/write page; part of db table t2)
}

do_test 1.4.1 {
  set ::log [list]

  execsql {
    ROLLBACK;
    CREATE TABLE t3(a INTEGER PRIMARY KEY, b INTEGER);
    CREATE INDEX i3 ON t3(b);

    WITH s(i) AS (
      SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<5000
    ) INSERT INTO t3 SELECT i, i FROM s;

    BEGIN CONCURRENT;
      INSERT INTO t3 VALUES(0, 5001);
  } db2

  execsql { INSERT INTO t3 VALUES(NULL, 5002) } db
  catchsql COMMIT db2
} {1 {database is locked}}

do_test_conflict_msg 1.3.2 {
  conflict at page 211 (read/write page; part of db index t3.i3)
}

db close
db2 close
sqlite3_shutdown
test_sqlite3_log 
sqlite3_initialize
finish_test