/ Check-in [6da0e962]
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:Add some test cases and fix some small problems with BEGIN UNLOCKED transactions.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | begin-concurrent
Files: files | file ages | folders
SHA1: 6da0e962ad2aa5e52c1f1b5c3dbf77a2cb16ac2d
User & Date: dan 2015-07-28 16:46:49
Wiki:begin-concurrent
Context
2015-07-29
12:14
Only allow UNLOCKED transactions to commit if none of the pages read by the transaction have been modified since it was opened. check-in: 0b971842 user: dan tags: begin-concurrent
2015-07-28
16:46
Add some test cases and fix some small problems with BEGIN UNLOCKED transactions. check-in: 6da0e962 user: dan tags: begin-concurrent
2015-07-27
19:31
Add an experimental "BEGIN UNLOCKED" command. check-in: 80794216 user: dan tags: begin-concurrent
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/wal.c.

2504
2505
2506
2507
2508
2509
2510




























2511
2512
2513
2514
2515
2516
2517
....
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
2561
2562
2563
2564
2565
2566
2567
2568
2569
2570
2571
2572
2573
2574
2575
2576
2577
2578
2579
2580
2581
2582
2583
2584
2585
2586
2587
2588
2589
2590
2591
2592
2593
2594
2595


2596
2597


2598
2599
2600
2601
2602
2603
2604
2605
2606
2607
2608
2609
2610
2611

2612
2613







2614
2615
2616
2617
2618
2619
2620
2621
2622
2623
2624
2625
2626
2627
2628
2629

2630
2631
2632
2633
2634
2635
2636
2637
2638
2639
2640
2641









2642
2643
2644
2645
2646
2647
2648
....
2707
2708
2709
2710
2711
2712
2713
2714
2715
2716
2717
2718
2719
2720
2721
Pgno sqlite3WalDbsize(Wal *pWal){
  if( pWal && ALWAYS(pWal->readLock>=0) ){
    return pWal->hdr.nPage;
  }
  return 0;
}






























/* 
** This function starts a write transaction on the WAL.
**
** A read transaction must have already been started by a prior call
** to sqlite3WalBeginReadTransaction().
**
................................................................................
** the read transaction was started, then it is not possible for this
** thread to write as doing so would cause a fork.  So this routine
** returns SQLITE_BUSY in that case and no write transaction is started.
**
** There can only be a single writer active at a time.
*/
int sqlite3WalBeginWriteTransaction(Wal *pWal){
  int rc;

  /* Cannot start a write transaction without first holding a read
  ** transaction. */
  assert( pWal->readLock>=0 );

  if( pWal->readOnly ){
    return SQLITE_READONLY;
  }

  /* Only one writer allowed at a time.  Get the write lock.  Return
  ** SQLITE_BUSY if unable.
  */
  rc = walLockExclusive(pWal, WAL_WRITE_LOCK, 1, 0);
  if( rc ){
    return rc;
  }
  pWal->writeLock = 1;

  /* If another connection has written to the database file since the
  ** time the read transaction on this connection was started, then
  ** the write is disallowed.
  */

  if( memcmp(&pWal->hdr, (void *)walIndexHdr(pWal), sizeof(WalIndexHdr))!=0 ){
    walUnlockExclusive(pWal, WAL_WRITE_LOCK, 1);
    pWal->writeLock = 0;
    rc = SQLITE_BUSY_SNAPSHOT;
  }

  return rc;
}

/* 
** TODO: Combine some code with BeginWriteTransaction()
**
** This function is only ever called when committing a "BEGIN UNLOCKED"
** transaction. It may be assumed that no frames have been written to
** the wal file.
*/
int sqlite3WalLockForCommit(Wal *pWal, PgHdr *pList, PgHdr *pPage1){
  volatile WalIndexHdr *pHead;    /* Head of the wal file */
  int rc;

  /* Cannot start a write transaction without first holding a read
  ** transaction. */
  assert( pWal->readLock>=0 );

  if( pWal->readOnly ){
    return SQLITE_READONLY;
  }

  /* Only one writer allowed at a time.  Get the write lock.  Return
  ** SQLITE_BUSY if unable.
  */
  rc = walLockExclusive(pWal, WAL_WRITE_LOCK, 1, 0);
  if( rc ){
    return rc;
  }
  pWal->writeLock = 1;

  /* 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:
  **
  **   a) None of the pages in pList have been modified since the 
  **      transaction opened, and
  **
  **   b) The database schema cookie has not been modified since the
  **      transaction was started.
  */


  pHead = walIndexHdr(pWal);
  if( memcmp(&pWal->hdr, (void*)pHead, sizeof(WalIndexHdr))!=0 ){


    /* TODO: Is this safe? Because it holds the WRITER lock this thread
    ** has exclusive access to the live header, but might it be corrupt? */
    PgHdr *pPg;
    u32 iLast = pHead->mxFrame;
    for(pPg=pList; rc==SQLITE_OK && pPg; pPg=pPg->pDirty){
      u32 iSlot = 0;
      rc = walFindFrame(pWal, pPg->pgno, iLast, &iSlot);
      if( iSlot>pWal->hdr.mxFrame ){
        sqlite3_log(SQLITE_OK,
            "cannot commit UNLOCKED transaction (conflict at page %d)",
            (int)pPg->pgno
        );
        rc = SQLITE_BUSY_SNAPSHOT;
      }

    }








    if( rc==SQLITE_OK ){
      /* Read the newest schema cookie from the wal file. */
      u32 iSlot = 0;
      rc = walFindFrame(pWal, 1, iLast, &iSlot);
      if( rc==SQLITE_OK && iSlot>pWal->hdr.mxFrame ){
        u8 aNew[4];
        u8 *aOld = &((u8*)pPage1->pData)[40];
        int sz;
        i64 iOffset;
        sz = pWal->hdr.szPage;
        sz = (sz&0xfe00) + ((sz&0x0001)<<16);
        iOffset = walFrameOffset(iSlot, sz) + WAL_FRAME_HDRSIZE + 40;
        rc = sqlite3OsRead(pWal->pWalFd, aNew, sizeof(aNew), iOffset);
        if( rc==SQLITE_OK && memcmp(aOld, aNew, sizeof(aNew)) ){
          /* TODO: New error code? SQLITE_BUSY_SCHEMA. */
          rc = SQLITE_BUSY_SNAPSHOT;

        }
      }
    }
  }

  return rc;
}

/*
** The caller holds the WRITER lock. This function returns true if a snapshot
** upgrade is required before the transaction can be committed, or false
** otherwise.









*/
int sqlite3WalCommitRequiresUpgrade(Wal *pWal){
  assert( pWal->writeLock );
  return memcmp(&pWal->hdr, (void*)walIndexHdr(pWal), sizeof(WalIndexHdr))!=0;
}

/*
................................................................................
/* 
** Argument aWalData must point to an array of WAL_SAVEPOINT_NDATA u32 
** values. This function populates the array with values required to 
** "rollback" the write position of the WAL handle back to the current 
** point in the event of a savepoint rollback (via WalSavepointUndo()).
*/
void sqlite3WalSavepoint(Wal *pWal, u32 *aWalData){
  /* assert( pWal->writeLock ); */
  aWalData[0] = pWal->hdr.mxFrame;
  aWalData[1] = pWal->hdr.aFrameCksum[0];
  aWalData[2] = pWal->hdr.aFrameCksum[1];
  aWalData[3] = pWal->nCkpt;
}

/* 







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







 







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











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











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









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







 







<







2504
2505
2506
2507
2508
2509
2510
2511
2512
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
....
2547
2548
2549
2550
2551
2552
2553
2554













2555




2556
2557
2558

2559
2560
2561
2562
2563
2564
2565
2566
2567
2568
2569
2570
2571
2572
2573
2574
2575
2576
2577


















2578
2579
2580
2581
2582
2583
2584
2585
2586
2587
2588
2589
2590
2591
2592
2593
2594
2595
2596
2597
2598
2599
2600
2601
2602
2603
2604
2605
2606
2607
2608
2609
2610
2611
2612
2613
2614
2615
2616
2617
2618
2619

2620
2621
2622
2623
2624
2625
2626
2627
2628
2629
2630
2631
2632
2633
2634
2635
2636
2637
2638
2639
2640
2641
2642
2643
2644
2645
2646
2647
2648
2649
2650
2651
2652
2653
2654
2655
2656
2657
2658
2659
2660
2661
2662
....
2721
2722
2723
2724
2725
2726
2727

2728
2729
2730
2731
2732
2733
2734
Pgno sqlite3WalDbsize(Wal *pWal){
  if( pWal && ALWAYS(pWal->readLock>=0) ){
    return pWal->hdr.nPage;
  }
  return 0;
}

/*
** Take the WRITER lock on the WAL file. Return SQLITE_OK if successful,
** or an SQLite error code otherwise. This routine does not invoke any
** busy-handler callbacks, that is done at a higher level.
*/
static int walWriteLock(Wal *pWal){
  int rc;

  /* Cannot start a write transaction without first holding a read lock */
  assert( pWal->readLock>=0 );
  assert( pWal->writeLock==0 );

  /* If this is a read-only connection, obtaining a write-lock is not
  ** possible. In this case return SQLITE_READONLY. Otherwise, attempt
  ** to grab the WRITER lock. Set Wal.writeLock to true and return
  ** SQLITE_OK if successful, or leave Wal.writeLock clear and return 
  ** an SQLite error code (possibly SQLITE_BUSY) otherwise. */
  if( pWal->readOnly ){
    rc = SQLITE_READONLY;
  }else{
    rc = walLockExclusive(pWal, WAL_WRITE_LOCK, 1, 0);
    if( rc==SQLITE_OK ){
      pWal->writeLock = 1;
    }
  }

  return rc;
}

/* 
** This function starts a write transaction on the WAL.
**
** A read transaction must have already been started by a prior call
** to sqlite3WalBeginReadTransaction().
**
................................................................................
** the read transaction was started, then it is not possible for this
** thread to write as doing so would cause a fork.  So this routine
** returns SQLITE_BUSY in that case and no write transaction is started.
**
** There can only be a single writer active at a time.
*/
int sqlite3WalBeginWriteTransaction(Wal *pWal){
  int rc = walWriteLock(pWal);













  if( rc==SQLITE_OK ){




    /* If another connection has written to the database file since the
    ** time the read transaction on this connection was started, then
    ** the write is disallowed. Release the WRITER lock and return

    ** SQLITE_BUSY_SNAPSHOT in this case.  */
    if( memcmp(&pWal->hdr, (void *)walIndexHdr(pWal), sizeof(WalIndexHdr))!=0 ){
      walUnlockExclusive(pWal, WAL_WRITE_LOCK, 1);
      pWal->writeLock = 0;
      rc = SQLITE_BUSY_SNAPSHOT;
    }
  }
  return rc;
}

/* 
** TODO: Combine some code with BeginWriteTransaction()
**
** This function is only ever called when committing a "BEGIN UNLOCKED"
** transaction. It may be assumed that no frames have been written to
** the wal file.
*/
int sqlite3WalLockForCommit(Wal *pWal, PgHdr *pList, PgHdr *pPage1){
  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:
  **
  **   a) None of the pages in pList have been modified since the 
  **      transaction opened, and
  **
  **   b) The database schema cookie has not been modified since the
  **      transaction was started.
  */
  if( rc==SQLITE_OK ){
    volatile WalIndexHdr *pHead;    /* Head of the wal file */
    pHead = walIndexHdr(pWal);
    if( memcmp(&pWal->hdr, (void*)pHead, sizeof(WalIndexHdr))!=0 ){
      int bSeenPage1 = 0;           /* True if page 1 is in list pList */
  
      /* TODO: Is this safe? Because it holds the WRITER lock this thread
      ** has exclusive access to the live header, but might it be corrupt? */
      PgHdr *pPg;
      u32 iLast = pHead->mxFrame;
      for(pPg=pList; rc==SQLITE_OK && pPg; pPg=pPg->pDirty){
        u32 iSlot = 0;
        rc = walFindFrame(pWal, pPg->pgno, iLast, &iSlot);
        if( iSlot>pWal->hdr.mxFrame ){
          sqlite3_log(SQLITE_OK,
              "cannot commit UNLOCKED transaction (conflict at page %d)",
              (int)pPg->pgno
          );
          rc = SQLITE_BUSY_SNAPSHOT;
        }
        if( pPg->pgno==1 ) bSeenPage1 = 1;
      }
  
      /* If the current transaction does not modify page 1 of the database,
      ** check if page 1 has been modified since the transaction was started.
      ** If it has, check if the schema cookie value (the 4 bytes beginning at
      ** byte offset 40) is the same as it is on pPage1. If not, this indicates
      ** that the current head of the wal file uses a different schema than 
      ** the snapshot against which the current transaction was prepared. Return
      ** SQLITE_BUSY_SNAPSHOT in this case.  */
      if( rc==SQLITE_OK && bSeenPage1==0 ){

        u32 iSlot = 0;
        rc = walFindFrame(pWal, 1, iLast, &iSlot);
        if( rc==SQLITE_OK && iSlot>pWal->hdr.mxFrame ){
          u8 aNew[4];
          u8 *aOld = &((u8*)pPage1->pData)[40];
          int sz;
          i64 iOffset;
          sz = pWal->hdr.szPage;
          sz = (sz&0xfe00) + ((sz&0x0001)<<16);
          iOffset = walFrameOffset(iSlot, sz) + WAL_FRAME_HDRSIZE + 40;
          rc = sqlite3OsRead(pWal->pWalFd, aNew, sizeof(aNew), iOffset);
          if( rc==SQLITE_OK && memcmp(aOld, aNew, sizeof(aNew)) ){
            /* TODO: New error code? SQLITE_BUSY_SCHEMA. */
            rc = SQLITE_BUSY_SNAPSHOT;
          }
        }
      }
    }
  }

  return rc;
}

/*
** This function is only ever called while committing an UNLOCKED 
** transaction, after the caller has already obtained the WRITER lock
** (by calling the sqlite3WalLockForCommit() routine). This function 
** returns true if the transaction was prepared against a database 
** snapshot older than the current head of the wal file.
**
** Note that this will only work as described if the database is 
** currently executing an UNLOCKED transaction, as it assumes that 
** pWal->hdr has not been modified since the beginning of the 
** transaction. This may not be true for a non-UNLOCKED transaction,
** as pWal->hdr is updated if any pages are spilled to the wal file
** while the transaction is executing.
*/
int sqlite3WalCommitRequiresUpgrade(Wal *pWal){
  assert( pWal->writeLock );
  return memcmp(&pWal->hdr, (void*)walIndexHdr(pWal), sizeof(WalIndexHdr))!=0;
}

/*
................................................................................
/* 
** Argument aWalData must point to an array of WAL_SAVEPOINT_NDATA u32 
** values. This function populates the array with values required to 
** "rollback" the write position of the WAL handle back to the current 
** point in the event of a savepoint rollback (via WalSavepointUndo()).
*/
void sqlite3WalSavepoint(Wal *pWal, u32 *aWalData){

  aWalData[0] = pWal->hdr.mxFrame;
  aWalData[1] = pWal->hdr.aFrameCksum[0];
  aWalData[2] = pWal->hdr.aFrameCksum[1];
  aWalData[3] = pWal->nCkpt;
}

/* 

Changes to test/unlocked.test.

275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
...
295
296
297
298
299
300
301
302













303

304





305

306













































307
  # 1. Begin and UNLOCKED write to "t1" using [db]
  #
  # 2. Create an index on t1 using [db2].
  #
  # 3. Attempt to commit the UNLOCKED write. This is an SQLITE_BUSY_SNAPSHOT,
  #    even though there is no page collision.
  #
  
  do_test 2.$tn.5.1 {
    sql1 {
      BEGIN UNLOCKED;
        INSERT INTO t1 VALUES(6, 'six');
    }
  } {}

................................................................................
    list [catch { sql1 { COMMIT } } msg] $msg [sqlite3_errcode db]
  } {1 {database is locked} SQLITE_BUSY_SNAPSHOT}

  do_test 2.$tn.5.4 {
    sql2 { PRAGMA integrity_check }
  } {ok}
  catch { sql1 ROLLBACK }














}























































finish_test







<







 








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

>
>
>
>
>
|
>

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

275
276
277
278
279
280
281

282
283
284
285
286
287
288
...
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
  # 1. Begin and UNLOCKED write to "t1" using [db]
  #
  # 2. Create an index on t1 using [db2].
  #
  # 3. Attempt to commit the UNLOCKED write. This is an SQLITE_BUSY_SNAPSHOT,
  #    even though there is no page collision.
  #

  do_test 2.$tn.5.1 {
    sql1 {
      BEGIN UNLOCKED;
        INSERT INTO t1 VALUES(6, 'six');
    }
  } {}

................................................................................
    list [catch { sql1 { COMMIT } } msg] $msg [sqlite3_errcode db]
  } {1 {database is locked} SQLITE_BUSY_SNAPSHOT}

  do_test 2.$tn.5.4 {
    sql2 { PRAGMA integrity_check }
  } {ok}
  catch { sql1 ROLLBACK }

  #-----------------------------------------------------------------------
  # The "schema cookie" issue.
  #
  # 1. Begin an UNLOCKED write to "t1" using [db]
  #
  # 2. Lots of inserts into t2. Enough to grow the db file.
  #
  # 3. Check that the UNLOCKED transaction can still be committed.
  #
  do_test 2.$tn.6.1 {
    sql1 {
      BEGIN UNLOCKED;
        INSERT INTO t1 VALUES(6, 'six');
    }
  } {}

  do_test 2.$tn.6.2 {
    sql2 { 
      WITH src(a,b) AS (
        VALUES(1,1) UNION ALL SELECT a+1,b+1 FROM src WHERE a<10000
      ) INSERT INTO t2 SELECT * FROM src;
    }
  } {}

  do_test 2.$tn.6.3 {
    sql1 {
      SELECT count(*) FROM t2;
      COMMIT;
      SELECT count(*) FROM t2;
    }
  } {1 10001}

  #-----------------------------------------------------------------------
  # 
  # 1. Begin an big UNLOCKED write to "t1" using [db] - large enough to
  #    grow the db file.
  #
  # 2. Lots of inserts into t2. Also enough to grow the db file.
  #
  # 3. Check that the UNLOCKED transaction cannot be committed (due to a clash
  #    on page 1 - the db size field).
  #
  do_test 2.$tn.7.1 {
    sql1 {
      BEGIN UNLOCKED;
        WITH src(a,b) AS (
          VALUES(10000,10000) UNION ALL SELECT a+1,b+1 FROM src WHERE a<20000
        ) INSERT INTO t1 SELECT * FROM src;
    }
  } {}

  do_test 2.$tn.7.2 {
    sql2 { 
      WITH src(a,b) AS (
        VALUES(1,1) UNION ALL SELECT a+1,b+1 FROM src WHERE a<10000
      ) INSERT INTO t2 SELECT * FROM src;
    }
  } {}

  do_test 2.$tn.7.3 {
    list [catch { sql1 { COMMIT } } msg] $msg [sqlite3_errcode db]
  } {1 {database is locked} SQLITE_BUSY_SNAPSHOT}
  sql1 ROLLBACK


}



finish_test