/ Check-in [9ad846e5]
Login

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 Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/btree.c.

 10282  10282   ** obtained for an CONCURRENT transaction due to a conflict with an already
 10283  10283   ** committed transaction, SQLITE_BUSY_SNAPSHOT is returned. Otherwise, if
 10284  10284   ** some other error (OOM, IO, etc.) occurs, the relevant SQLite error code
 10285  10285   ** is returned.
 10286  10286   */
 10287  10287   int sqlite3BtreeExclusiveLock(Btree *p){
 10288  10288     int rc;
        10289  +  Pgno pgno = 0;
 10289  10290     BtShared *pBt = p->pBt;
 10290  10291     assert( p->inTrans==TRANS_WRITE && pBt->pPage1 );
 10291  10292     sqlite3BtreeEnter(p);
 10292         -  rc = sqlite3PagerExclusiveLock(pBt->pPager, pBt->pPage1->pDbPage);
        10293  +  rc = sqlite3PagerExclusiveLock(pBt->pPager, pBt->pPage1->pDbPage, &pgno);
        10294  +  if( rc==SQLITE_BUSY_SNAPSHOT && pgno ){
        10295  +    PgHdr *pPg = 0;
        10296  +    int rc2 = sqlite3PagerGet(pBt->pPager, pgno, &pPg, 0);
        10297  +    if( rc2==SQLITE_OK ){
        10298  +      int bWrite = -1;
        10299  +      const char *zObj = 0;
        10300  +      const char *zTab = 0;
        10301  +
        10302  +      if( pPg ){
        10303  +        Pgno pgnoRoot = 0;
        10304  +        HashElem *pE;
        10305  +        Schema *pSchema;
        10306  +
        10307  +        pgnoRoot = ((MemPage*)sqlite3PagerGetExtra(pPg))->pgnoRoot;
        10308  +        bWrite = sqlite3PagerIswriteable(pPg);
        10309  +        sqlite3PagerUnref(pPg);
        10310  +
        10311  +        pSchema = sqlite3SchemaGet(p->db, p);
        10312  +        if( pSchema ){
        10313  +          for(pE=sqliteHashFirst(&pSchema->tblHash); pE; pE=sqliteHashNext(pE)){
        10314  +            Table *pTab = (Table *)sqliteHashData(pE);
        10315  +            if( pTab->tnum==(int)pgnoRoot ){
        10316  +              zObj = pTab->zName;
        10317  +              zTab = 0;
        10318  +            }else{
        10319  +              Index *pIdx;
        10320  +              for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
        10321  +                if( pIdx->tnum==(int)pgnoRoot ){
        10322  +                  zObj = pIdx->zName;
        10323  +                  zTab = pTab->zName;
        10324  +                }
        10325  +              }
        10326  +            }
        10327  +          }
        10328  +        }
        10329  +      }
        10330  +
        10331  +      sqlite3_log(SQLITE_OK,
        10332  +          "cannot commit CONCURRENT transaction "
        10333  +          "- conflict at page %d "
        10334  +          "(%s page; part of db %s %s%s%s)",
        10335  +          (int)pgno,
        10336  +          (bWrite==0?"read-only":(bWrite>0?"read/write":"unknown")),
        10337  +          (zTab ? "index" : "table"),
        10338  +          (zTab ? zTab : ""), (zTab ? "." : ""), (zObj ? zObj : "UNKNOWN")
        10339  +      );
        10340  +    }
        10341  +  }
        10342  +
 10293  10343     sqlite3BtreeLeave(p);
 10294  10344     return rc;
 10295  10345   }
 10296  10346   
 10297  10347   #if !defined(SQLITE_OMIT_SHARED_CACHE)
 10298  10348   /*
 10299  10349   ** Return true if the Btree passed as the only argument is sharable.

Changes to src/pager.c.

  4262   4262   
  4263   4263     assert( pPager->eState==PAGER_WRITER_CACHEMOD
  4264   4264          || pPager->eState==PAGER_WRITER_DBMOD
  4265   4265     );
  4266   4266     assert( assert_pager_state(pPager) );
  4267   4267     assert( !pagerUseWal(pPager) );
  4268   4268   
  4269         -  rc = sqlite3PagerExclusiveLock(pPager, 0);
         4269  +  rc = sqlite3PagerExclusiveLock(pPager, 0, 0);
  4270   4270     if( rc!=SQLITE_OK ) return rc;
  4271   4271   
  4272   4272     if( !pPager->noSync ){
  4273   4273       assert( !pPager->tempFile );
  4274   4274       if( isOpen(pPager->jfd) && pPager->journalMode!=PAGER_JOURNALMODE_MEMORY ){
  4275   4275         const int iDc = sqlite3OsDeviceCharacteristics(pPager->fd);
  4276   4276         assert( isOpen(pPager->jfd) );
................................................................................
  6343   6343   ** If the required locks are already held or successfully obtained and
  6344   6344   ** the transaction can be committed, SQLITE_OK is returned. If a required lock
  6345   6345   ** cannot be obtained, SQLITE_BUSY is returned. Or, if the current transaction
  6346   6346   ** is CONCURRENT and cannot be committed due to a conflict, SQLITE_BUSY_SNAPSHOT
  6347   6347   ** is returned. Otherwise, if some other error occurs (IO error, OOM etc.),
  6348   6348   ** and SQLite error code is returned.
  6349   6349   */
  6350         -int sqlite3PagerExclusiveLock(Pager *pPager, PgHdr *pPage1){
         6350  +int sqlite3PagerExclusiveLock(Pager *pPager, PgHdr *pPage1, Pgno *piConflict){
  6351   6351     int rc = pPager->errCode;
  6352   6352     assert( assert_pager_state(pPager) );
  6353   6353     if( rc==SQLITE_OK ){
  6354   6354       assert( pPager->eState==PAGER_WRITER_CACHEMOD 
  6355   6355            || pPager->eState==PAGER_WRITER_DBMOD 
  6356   6356            || pPager->eState==PAGER_WRITER_LOCKED 
  6357   6357       );
................................................................................
  6363   6363       else{
  6364   6364         if( pPager->pAllRead ){
  6365   6365           /* This is an CONCURRENT transaction. Attempt to lock the wal database
  6366   6366           ** here. If SQLITE_BUSY (but not SQLITE_BUSY_SNAPSHOT) is returned,
  6367   6367           ** invoke the busy-handler and try again for as long as it returns
  6368   6368           ** non-zero.  */
  6369   6369           do {
  6370         -          rc = sqlite3WalLockForCommit(pPager->pWal, pPage1, pPager->pAllRead);
         6370  +          rc = sqlite3WalLockForCommit(
         6371  +              pPager->pWal, pPage1, pPager->pAllRead, piConflict
         6372  +          );
  6371   6373           }while( rc==SQLITE_BUSY 
  6372   6374                && pPager->xBusyHandler(pPager->pBusyHandlerArg) 
  6373   6375           );
  6374   6376         }
  6375   6377       }
  6376   6378   #endif /* SQLITE_OMIT_CONCURRENT */
  6377   6379     }

Changes to src/pager.h.

   160    160   void *sqlite3PagerGetData(DbPage *); 
   161    161   void *sqlite3PagerGetExtra(DbPage *); 
   162    162   
   163    163   /* Functions used to manage pager transactions and savepoints. */
   164    164   void sqlite3PagerPagecount(Pager*, int*);
   165    165   int sqlite3PagerBegin(Pager*, int exFlag, int);
   166    166   int sqlite3PagerCommitPhaseOne(Pager*,const char *zMaster, int);
   167         -int sqlite3PagerExclusiveLock(Pager*, DbPage *pPage1);
          167  +int sqlite3PagerExclusiveLock(Pager*, DbPage *pPage1, Pgno*);
   168    168   int sqlite3PagerSync(Pager *pPager, const char *zMaster);
   169    169   int sqlite3PagerCommitPhaseTwo(Pager*);
   170    170   int sqlite3PagerRollback(Pager*);
   171    171   int sqlite3PagerOpenSavepoint(Pager *pPager, int n);
   172    172   int sqlite3PagerSavepoint(Pager *pPager, int op, int iSavepoint);
   173    173   int sqlite3PagerSharedLock(Pager *pPager);
   174    174   

Changes to src/wal.c.

   239    239   ** When a rollback occurs, the value of K is decreased. Hash table entries
   240    240   ** that correspond to frames greater than the new K value are removed
   241    241   ** from the hash table at this point.
   242    242   */
   243    243   #ifndef SQLITE_OMIT_WAL
   244    244   
   245    245   #include "wal.h"
   246         -#include "btreeInt.h"
   247    246   
   248    247   /*
   249    248   ** Trace output macros
   250    249   */
   251    250   #if defined(SQLITE_TEST) && defined(SQLITE_DEBUG)
   252    251   int sqlite3WalTrace = 0;
   253    252   # define WALTRACE(X)  if(sqlite3WalTrace) sqlite3DebugPrintf X
................................................................................
  2853   2852   ** If no error occurs and the caller may proceed with committing the 
  2854   2853   ** transaction, SQLITE_OK is returned. SQLITE_BUSY is returned if the WRITER
  2855   2854   ** lock cannot be obtained. Or, if the WRITER lock can be obtained but there
  2856   2855   ** are conflicts with a committed transaction, SQLITE_BUSY_SNAPSHOT. Finally,
  2857   2856   ** if an error (i.e. an OOM condition or IO error), an SQLite error code
  2858   2857   ** is returned.
  2859   2858   */
  2860         -int sqlite3WalLockForCommit(Wal *pWal, PgHdr *pPage1, Bitvec *pAllRead){
         2859  +int sqlite3WalLockForCommit(
         2860  +  Wal *pWal, 
         2861  +  PgHdr *pPage1, 
         2862  +  Bitvec *pAllRead, 
         2863  +  Pgno *piConflict
         2864  +){
  2861   2865     Pager *pPager = pPage1->pPager;
  2862   2866     int rc = walWriteLock(pWal);
  2863   2867   
  2864   2868     /* If the database has been modified since this transaction was started,
  2865   2869     ** check if it is still possible to commit. The transaction can be 
  2866   2870     ** committed if:
  2867   2871     **
................................................................................
  2913   2917                 sz = (sz&0xfe00) + ((sz&0x0001)<<16);
  2914   2918                 iOffset = walFrameOffset(i+iZero, sz) + WAL_FRAME_HDRSIZE + 40;
  2915   2919                 rc = sqlite3OsRead(pWal->pWalFd, aNew, sizeof(aNew), iOffset);
  2916   2920                 if( rc==SQLITE_OK && memcmp(aOld, aNew, sizeof(aNew)) ){
  2917   2921                   rc = SQLITE_BUSY_SNAPSHOT;
  2918   2922                 }
  2919   2923               }else if( sqlite3BitvecTestNotNull(pAllRead, aPgno[i]) ){
  2920         -              PgHdr *pPg = 0;
  2921         -              rc = sqlite3PagerGet(pPage1->pPager, aPgno[i], &pPg, 0);
  2922         -              if( rc==SQLITE_OK ){
  2923         -                Pgno pgnoRoot = 0;
  2924         -                int bWrite = -1;
  2925         -                if( pPg ){
  2926         -                  pgnoRoot = ((MemPage*)sqlite3PagerGetExtra(pPg))->pgnoRoot;
  2927         -                  bWrite = sqlite3PagerIswriteable(pPg);
  2928         -                  sqlite3PagerUnref(pPg);
  2929         -                }
  2930         -                sqlite3_log(SQLITE_OK,
  2931         -                  "cannot commit CONCURRENT transaction "
  2932         -                  "- conflict at page %d "
  2933         -                  "(%s page; part of b-tree with root page %d)",
  2934         -                  (int)aPgno[i], 
  2935         -                  (bWrite==0?"read-only":(bWrite>0?"read/write":"unknown")),
  2936         -                  (int)pgnoRoot
  2937         -                );
  2938         -                rc = SQLITE_BUSY_SNAPSHOT;
  2939         -              }
         2924  +              *piConflict = aPgno[i];
         2925  +              rc = SQLITE_BUSY_SNAPSHOT;
  2940   2926               }else if( (pPg = sqlite3PagerLookup(pPager, aPgno[i])) ){
  2941   2927                 /* Page aPgno[i], which is present in the pager cache, has been
  2942   2928                 ** modified since the current CONCURRENT transaction was started.
  2943   2929                 ** However it was not read by the current transaction, so is not
  2944   2930                 ** a conflict. There are two possibilities: (a) the page was
  2945   2931                 ** allocated at the of the file by the current transaction or 
  2946   2932                 ** (b) was present in the cache at the start of the transaction.

Changes to src/wal.h.

   132    132   int sqlite3WalSnapshotGet(Wal *pWal, sqlite3_snapshot **ppSnapshot);
   133    133   void sqlite3WalSnapshotOpen(Wal *pWal, sqlite3_snapshot *pSnapshot);
   134    134   int sqlite3WalSnapshotRecover(Wal *pWal);
   135    135   #endif
   136    136   
   137    137   #ifndef SQLITE_OMIT_CONCURRENT
   138    138   /* Tell the wal layer that we want to commit a concurrent transaction */
   139         -int sqlite3WalLockForCommit(Wal *pWal, PgHdr *pPg, Bitvec *pRead);
          139  +int sqlite3WalLockForCommit(Wal *pWal, PgHdr *pPg, Bitvec *pRead, Pgno*);
   140    140   
   141    141   /* Upgrade the state of the client to take into account changes written
   142    142   ** by other connections */
   143    143   int sqlite3WalUpgradeSnapshot(Wal *pWal);
   144    144   #endif /* SQLITE_OMIT_CONCURRENT */
   145    145   
   146    146   #ifdef SQLITE_ENABLE_ZIPVFS

Changes to test/concurrent5.test.

    55     55     db eval {
    56     56       INSERT INTO t1 VALUES(randomblob(200), randomblob(200));
    57     57     }
    58     58   
    59     59     catchsql COMMIT db2
    60     60   } {1 {database is locked}}
    61     61   do_test_conflict_msg 1.1.2 {
    62         -  conflict at page 2 (read-only page; part of b-tree with root page 2)
           62  +  conflict at page 2 (read-only page; part of db table t1)
    63     63   }
    64     64   
    65     65   do_test 1.2.1 {
    66     66     set ::log [list]
    67     67     db2 eval {
    68     68       ROLLBACK;
    69     69       BEGIN CONCURRENT;
................................................................................
    74     74       INSERT INTO t1 VALUES(12, 11);
    75     75     }
    76     76   
    77     77     catchsql COMMIT db2
    78     78   } {1 {database is locked}}
    79     79   
    80     80   do_test_conflict_msg 1.2.2 {
    81         -  conflict at page 105 (read/write page; part of b-tree with root page 2)
           81  +  conflict at page 105 (read/write page; part of db table t1)
    82     82   }
    83     83   
    84     84   do_test 1.3.1 {
    85     85     set ::log [list]
    86     86     db2 eval {
    87     87       ROLLBACK;
    88     88       BEGIN CONCURRENT;
................................................................................
    93     93       INSERT INTO t2 VALUES('y');
    94     94     }
    95     95   
    96     96     catchsql COMMIT db2
    97     97   } {1 {database is locked}}
    98     98   
    99     99   do_test_conflict_msg 1.3.2 {
   100         -  conflict at page 3 (read/write page; part of b-tree with root page 3)
          100  +  conflict at page 3 (read/write page; part of db table t2)
          101  +}
          102  +
          103  +do_test 1.4.1 {
          104  +  set ::log [list]
          105  +
          106  +  execsql {
          107  +    ROLLBACK;
          108  +    CREATE TABLE t3(a INTEGER PRIMARY KEY, b INTEGER);
          109  +    CREATE INDEX i3 ON t3(b);
          110  +
          111  +    WITH s(i) AS (
          112  +      SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<5000
          113  +    ) INSERT INTO t3 SELECT i, i FROM s;
          114  +
          115  +    BEGIN CONCURRENT;
          116  +      INSERT INTO t3 VALUES(0, 5001);
          117  +  } db2
          118  +
          119  +  execsql { INSERT INTO t3 VALUES(NULL, 5002) } db
          120  +  catchsql COMMIT db2
          121  +} {1 {database is locked}}
          122  +
          123  +do_test_conflict_msg 1.3.2 {
          124  +  conflict at page 211 (read/write page; part of db index t3.i3)
   101    125   }
   102    126   
   103    127   db close
   104    128   db2 close
   105    129   sqlite3_shutdown
   106    130   test_sqlite3_log 
   107    131   sqlite3_initialize
   108    132   finish_test
   109    133