SQLite

Check-in [9bc9b68473]
Login

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

Overview
Comment:In synchronous=normal mode, do not sync the log after every transaction. In synchronous=full mode, sync the log and add any extra frames required to avoid blast-radius related problems after each transaction.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | wal
Files: files | file ages | folders
SHA1: 9bc9b6847303d0324543a9ded8dd0473490122d8
User & Date: dan 2010-04-17 15:42:43.000
Context
2010-04-17
15:45
Merge with trunk commit [3e646e3f4c]. (check-in: 43463970f5 user: dan tags: wal)
15:42
In synchronous=normal mode, do not sync the log after every transaction. In synchronous=full mode, sync the log and add any extra frames required to avoid blast-radius related problems after each transaction. (check-in: 9bc9b68473 user: dan tags: wal)
12:31
Enhancements to wal-mode locking scheme. (check-in: 8549c28649 user: dan tags: wal)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/log.c.
︙︙
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891

  pRet->pFd = (sqlite3_file *)&pRet[1];
  pRet->sync_flags = SQLITE_SYNC_NORMAL;

  /* Normalize the path name. */
  zWal = sqlite3_mprintf("%s-wal", zDb);
  if( !zWal ) goto out;
  logNormalizePath(zWal);
  flags = (SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE|SQLITE_OPEN_MAIN_DB);
  nWal = sqlite3Strlen30(zWal);

  /* Enter the mutex that protects the linked-list of LogSummary structures */
  if( sqlite3GlobalConfig.bCoreMutex ){
    mutex = sqlite3_mutex_alloc(LOG_SUMMARY_MUTEX);
  }
  sqlite3_mutex_enter(mutex);








|








877
878
879
880
881
882
883
884
885
886
887
888
889
890
891

  pRet->pFd = (sqlite3_file *)&pRet[1];
  pRet->sync_flags = SQLITE_SYNC_NORMAL;

  /* Normalize the path name. */
  zWal = sqlite3_mprintf("%s-wal", zDb);
  if( !zWal ) goto out;
  logNormalizePath(zWal);
  flags = (SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE|SQLITE_OPEN_MAIN_JOURNAL);
  nWal = sqlite3Strlen30(zWal);

  /* Enter the mutex that protects the linked-list of LogSummary structures */
  if( sqlite3GlobalConfig.bCoreMutex ){
    mutex = sqlite3_mutex_alloc(LOG_SUMMARY_MUTEX);
  }
  sqlite3_mutex_enter(mutex);
︙︙
1541
1542
1543
1544
1545
1546
1547
1548
1549

1550

1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578

      return rc;
    }
    pLast = p;
  }

  /* Sync the log file if the 'isSync' flag was specified. */
  if( isSync ){
#if 0
    i64 iSegment = sqlite3OsSectorSize(pLog->pFd);

    i64 iOffset = iFrame * (nPgsz+sizeof(aFrame));


    if( iSegment<SQLITE_DEFAULT_SECTOR_SIZE ){
      iSegment = SQLITE_DEFAULT_SECTOR_SIZE;
    }
    iSegment = (((iOffset+iSegment-1)/iSegment) * iSegment);
    while( iOffset<iSegment ){
      logEncodeFrame(aCksum,pLast->pgno,nTruncate,nPgsz,pLast->pData,aFrame);
      rc = sqlite3OsWrite(pLog->pFd, aFrame, sizeof(aFrame), iOffset);
      if( rc!=SQLITE_OK ){
        return rc;
      }

      iOffset += LOG_FRAME_HDRSIZE;
      rc = sqlite3OsWrite(pLog->pFd, pLast->pData, nPgsz, iOffset); 
      if( rc!=SQLITE_OK ){
        return rc;
      }
      nLast++;
      iOffset += nPgsz;
    }
#endif

    rc = sqlite3OsSync(pLog->pFd, pLog->sync_flags);
    if( rc!=SQLITE_OK ){
      return rc;
    }
  }









<

>
|
>




















<








1541
1542
1543
1544
1545
1546
1547

1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571

1572
1573
1574
1575
1576
1577
1578

      return rc;
    }
    pLast = p;
  }

  /* Sync the log file if the 'isSync' flag was specified. */
  if( isSync ){

    i64 iSegment = sqlite3OsSectorSize(pLog->pFd);
    i64 iOffset = logFrameOffset(iFrame+1, nPgsz);

    assert( isCommit );

    if( iSegment<SQLITE_DEFAULT_SECTOR_SIZE ){
      iSegment = SQLITE_DEFAULT_SECTOR_SIZE;
    }
    iSegment = (((iOffset+iSegment-1)/iSegment) * iSegment);
    while( iOffset<iSegment ){
      logEncodeFrame(aCksum,pLast->pgno,nTruncate,nPgsz,pLast->pData,aFrame);
      rc = sqlite3OsWrite(pLog->pFd, aFrame, sizeof(aFrame), iOffset);
      if( rc!=SQLITE_OK ){
        return rc;
      }

      iOffset += LOG_FRAME_HDRSIZE;
      rc = sqlite3OsWrite(pLog->pFd, pLast->pData, nPgsz, iOffset); 
      if( rc!=SQLITE_OK ){
        return rc;
      }
      nLast++;
      iOffset += nPgsz;
    }


    rc = sqlite3OsSync(pLog->pFd, pLog->sync_flags);
    if( rc!=SQLITE_OK ){
      return rc;
    }
  }
︙︙
Changes to src/pager.c.
︙︙
3760
3761
3762
3763
3764
3765
3766

3767
3768
3769
3770
3771
3772
3773

    ** the required SHARED lock on the database file. 
    */
#ifdef SQLITE_DEBUG
    int locktype;
    sqlite3OsFileControl(pPager->fd, SQLITE_FCNTL_LOCKSTATE, &locktype);
    assert( locktype==SQLITE_LOCK_SHARED );
#endif

  }

  return SQLITE_OK;
}


/*








>








3760
3761
3762
3763
3764
3765
3766
3767
3768
3769
3770
3771
3772
3773
3774

    ** the required SHARED lock on the database file. 
    */
#ifdef SQLITE_DEBUG
    int locktype;
    sqlite3OsFileControl(pPager->fd, SQLITE_FCNTL_LOCKSTATE, &locktype);
    assert( locktype==SQLITE_LOCK_SHARED );
#endif
    pPager->state = PAGER_SHARED;
  }

  return SQLITE_OK;
}


/*
︙︙
4876
4877
4878
4879
4880
4881
4882
4883
4884
4885
4886
4887
4888
4889
4890

    */
    sqlite3BackupRestart(pPager->pBackup);
  }else if( pPager->state!=PAGER_SYNCED && pPager->dbModified ){
    if( pagerUseLog(pPager) ){
      PgHdr *pList = sqlite3PcacheDirtyList(pPager->pPCache);
      if( pList ){
        rc = sqlite3LogFrames(pPager->pLog, pPager->pageSize, pList,
            pPager->dbSize, 1, 1
        );
      }
      sqlite3PcacheCleanAll(pPager->pPCache);
    }else{
      /* The following block updates the change-counter. Exactly how it
      ** does this depends on whether or not the atomic-update optimization
      ** was enabled at compile time, and if this transaction meets the 








|








4877
4878
4879
4880
4881
4882
4883
4884
4885
4886
4887
4888
4889
4890
4891

    */
    sqlite3BackupRestart(pPager->pBackup);
  }else if( pPager->state!=PAGER_SYNCED && pPager->dbModified ){
    if( pagerUseLog(pPager) ){
      PgHdr *pList = sqlite3PcacheDirtyList(pPager->pPCache);
      if( pList ){
        rc = sqlite3LogFrames(pPager->pLog, pPager->pageSize, pList,
            pPager->dbSize, 1, pPager->fullSync
        );
      }
      sqlite3PcacheCleanAll(pPager->pPCache);
    }else{
      /* The following block updates the change-counter. Exactly how it
      ** does this depends on whether or not the atomic-update optimization
      ** was enabled at compile time, and if this transaction meets the
︙︙
Changes to test/wal.test.
︙︙
18
19
20
21
22
23
24






25
26
27
28


29
30
31
32
33
34
35

source $testdir/lock_common.tcl

proc reopen_db {} {
  catch { db close }
  file delete -force test.db test.db-wal
  sqlite3_wal db test.db
}







proc sqlite3_wal {args} {
  eval sqlite3 $args
  [lindex $args 0] eval { PRAGMA journal_mode = wal }


}

proc log_file_size {nFrame pgsz} {
  expr {12 + ($pgsz+16)*$nFrame}
}

proc log_deleted {logfile} {








>
>
>
>
>
>




>
>








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

source $testdir/lock_common.tcl

proc reopen_db {} {
  catch { db close }
  file delete -force test.db test.db-wal
  sqlite3_wal db test.db
}

set ::blobcnt 0
proc blob {nByte} {
  incr ::blobcnt
  return [string range [string repeat "${::blobcnt}x" $nByte] 1 $nByte]
}

proc sqlite3_wal {args} {
  eval sqlite3 $args
  [lindex $args 0] eval { PRAGMA journal_mode = wal }
  [lindex $args 0] eval { PRAGMA synchronous = normal }
  [lindex $args 0] function blob blob
}

proc log_file_size {nFrame pgsz} {
  expr {12 + ($pgsz+16)*$nFrame}
}

proc log_deleted {logfile} {
︙︙
45
46
47
48
49
50
51

52
53
54
55
56
57
58

# wal-3.*: Test transaction rollback.
# wal-4.*: Test savepoint/statement rollback.
# wal-5.*: Test the temp database.
# wal-6.*: Test creating databases with different page sizes.
#

do_test wal-0.1 {

  execsql { PRAGMA journal_mode = wal }
} {wal}

do_test wal-1.0 {
  execsql { 
    BEGIN;
    CREATE TABLE t1(a, b); 








>








53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

# wal-3.*: Test transaction rollback.
# wal-4.*: Test savepoint/statement rollback.
# wal-5.*: Test the temp database.
# wal-6.*: Test creating databases with different page sizes.
#

do_test wal-0.1 {
  execsql { PRAGMA synchronous = normal }
  execsql { PRAGMA journal_mode = wal }
} {wal}

do_test wal-1.0 {
  execsql { 
    BEGIN;
    CREATE TABLE t1(a, b);
︙︙
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280

    PRAGMA auto_vacuum;
  }
} {1}
do_test wal-8.2 {
  execsql {
    PRAGMA page_size = 1024;
    CREATE TABLE t1(x);
    INSERT INTO t1 VALUES(randomblob(900));
    INSERT INTO t1 VALUES(randomblob(900));
    INSERT INTO t1 SELECT randomblob(900) FROM t1;       /*  4 */
    INSERT INTO t1 SELECT randomblob(900) FROM t1;       /*  8 */
    INSERT INTO t1 SELECT randomblob(900) FROM t1;       /* 16 */
    INSERT INTO t1 SELECT randomblob(900) FROM t1;       /* 32 */
    INSERT INTO t1 SELECT randomblob(900) FROM t1;       /* 64 */
    PRAGMA checkpoint;
  }
  file size test.db
} [expr 68*1024]
do_test wal-8.3 {
  execsql { 
    DELETE FROM t1 WHERE rowid<54;
    PRAGMA checkpoint;
  }
  file size test.db
} [expr 14*1024]

# Run some "warm-body" tests to ensure that log-summary files with more
# than 256 entries (log summaries that contain index blocks) work Ok.
#
do_test wal-9.1 {
  reopen_db
  execsql {
    PRAGMA page_size = 1024;
    CREATE TABLE t1(x PRIMARY KEY);
    INSERT INTO t1 VALUES(randomblob(900));
    INSERT INTO t1 VALUES(randomblob(900));
    INSERT INTO t1 SELECT randomblob(900) FROM t1;       /*  4 */
    INSERT INTO t1 SELECT randomblob(900) FROM t1;       /*  8 */
    INSERT INTO t1 SELECT randomblob(900) FROM t1;       /* 16 */
    INSERT INTO t1 SELECT randomblob(900) FROM t1;       /* 32 */
    INSERT INTO t1 SELECT randomblob(900) FROM t1;       /* 64 */
    INSERT INTO t1 SELECT randomblob(900) FROM t1;       /* 128 */
    INSERT INTO t1 SELECT randomblob(900) FROM t1;       /* 256 */
  }
  file size test.db
} 0
do_test wal-9.2 {
  sqlite3_wal db2 test.db
  execsql {PRAGMA integrity_check } db2
} {ok}








|
|
|
|
|
|
|




















|
|
|
|
|
|
|
|
|








240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289

    PRAGMA auto_vacuum;
  }
} {1}
do_test wal-8.2 {
  execsql {
    PRAGMA page_size = 1024;
    CREATE TABLE t1(x);
    INSERT INTO t1 VALUES(blob(900));
    INSERT INTO t1 VALUES(blob(900));
    INSERT INTO t1 SELECT blob(900) FROM t1;       /*  4 */
    INSERT INTO t1 SELECT blob(900) FROM t1;       /*  8 */
    INSERT INTO t1 SELECT blob(900) FROM t1;       /* 16 */
    INSERT INTO t1 SELECT blob(900) FROM t1;       /* 32 */
    INSERT INTO t1 SELECT blob(900) FROM t1;       /* 64 */
    PRAGMA checkpoint;
  }
  file size test.db
} [expr 68*1024]
do_test wal-8.3 {
  execsql { 
    DELETE FROM t1 WHERE rowid<54;
    PRAGMA checkpoint;
  }
  file size test.db
} [expr 14*1024]

# Run some "warm-body" tests to ensure that log-summary files with more
# than 256 entries (log summaries that contain index blocks) work Ok.
#
do_test wal-9.1 {
  reopen_db
  execsql {
    PRAGMA page_size = 1024;
    CREATE TABLE t1(x PRIMARY KEY);
    INSERT INTO t1 VALUES(blob(900));
    INSERT INTO t1 VALUES(blob(900));
    INSERT INTO t1 SELECT blob(900) FROM t1;       /*  4 */
    INSERT INTO t1 SELECT blob(900) FROM t1;       /*  8 */
    INSERT INTO t1 SELECT blob(900) FROM t1;       /* 16 */
    INSERT INTO t1 SELECT blob(900) FROM t1;       /* 32 */
    INSERT INTO t1 SELECT blob(900) FROM t1;       /* 64 */
    INSERT INTO t1 SELECT blob(900) FROM t1;       /* 128 */
    INSERT INTO t1 SELECT blob(900) FROM t1;       /* 256 */
  }
  file size test.db
} 0
do_test wal-9.2 {
  sqlite3_wal db2 test.db
  execsql {PRAGMA integrity_check } db2
} {ok}
︙︙
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635

  list [expr [file size test.db]/1024] [expr [file size test.db-wal]/1044]
} {0 3}
do_test wal-11.2 {
  execsql { PRAGMA checkpoint }
  list [expr [file size test.db]/1024] [file size test.db-wal]
} [list 3 [log_file_size 3 1024]]
do_test wal-11.3 {
  execsql { INSERT INTO t1 VALUES( randomblob(900) ) }
  list [expr [file size test.db]/1024] [file size test.db-wal]
} [list 3 [log_file_size 4 1024]]

do_test wal-11.4 {
  execsql { 
    BEGIN;
      INSERT INTO t1 SELECT randomblob(900) FROM t1;   -- 2
      INSERT INTO t1 SELECT randomblob(900) FROM t1;   -- 4
      INSERT INTO t1 SELECT randomblob(900) FROM t1;   -- 8
      INSERT INTO t1 SELECT randomblob(900) FROM t1;   -- 16
  }
  list [expr [file size test.db]/1024] [file size test.db-wal]
} [list 3 [log_file_size 33 1024]]
do_test wal-11.5 {
  execsql { 
    SELECT count(*) FROM t1;
    PRAGMA integrity_check;
  }
} {16 ok}
do_test wal-11.6 {
  execsql COMMIT
  list [expr [file size test.db]/1024] [file size test.db-wal]
} [list 3 [log_file_size 42 1024]]
do_test wal-11.7 {
  execsql { 
    SELECT count(*) FROM t1;
    PRAGMA integrity_check;
  }
} {16 ok}
do_test wal-11.8 {
  execsql { PRAGMA checkpoint }
  list [expr [file size test.db]/1024] [file size test.db-wal]
} [list 37 [log_file_size 42 1024]]
do_test wal-11.9 {
  db close
  sqlite3_wal db test.db
  list [expr [file size test.db]/1024] [log_deleted test.db-wal]
} {37 1}

do_test wal-11.10 {
  execsql {
    PRAGMA cache_size = 10;
    BEGIN;
      INSERT INTO t1 SELECT randomblob(900) FROM t1;   -- 32
      SELECT count(*) FROM t1;
  }
  list [expr [file size test.db]/1024] [file size test.db-wal]
} [list 37 [log_file_size 40 1024]]
do_test wal-11.11 {
  execsql {
      SELECT count(*) FROM t1;
    ROLLBACK;
    SELECT count(*) FROM t1;
  }
} {32 16}
do_test wal-11.12 {
  list [expr [file size test.db]/1024] [file size test.db-wal]
} [list 37 [log_file_size 40 1024]]
do_test wal-11.13 {
  execsql {
    INSERT INTO t1 VALUES( randomblob(900) );
    SELECT count(*) FROM t1;
    PRAGMA integrity_check;
  }
} {17 ok}
do_test wal-11.14 {
  list [expr [file size test.db]/1024] [file size test.db-wal]
} [list 37 [log_file_size 40 1024]]


#-------------------------------------------------------------------------
# This block of tests, wal-12.*, tests the fix for a problem that 
# could occur if a log that is a prefix of an older log is written 
# into a reused log file.
#








|






|
|
|
|


|









|









|


<


|




|



|









|


|






|








562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604

605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643

  list [expr [file size test.db]/1024] [expr [file size test.db-wal]/1044]
} {0 3}
do_test wal-11.2 {
  execsql { PRAGMA checkpoint }
  list [expr [file size test.db]/1024] [file size test.db-wal]
} [list 3 [log_file_size 3 1024]]
do_test wal-11.3 {
  execsql { INSERT INTO t1 VALUES( blob(900) ) }
  list [expr [file size test.db]/1024] [file size test.db-wal]
} [list 3 [log_file_size 4 1024]]

do_test wal-11.4 {
  execsql { 
    BEGIN;
      INSERT INTO t1 SELECT blob(900) FROM t1;   -- 2
      INSERT INTO t1 SELECT blob(900) FROM t1;   -- 4
      INSERT INTO t1 SELECT blob(900) FROM t1;   -- 8
      INSERT INTO t1 SELECT blob(900) FROM t1;   -- 16
  }
  list [expr [file size test.db]/1024] [file size test.db-wal]
} [list 3 [log_file_size 32 1024]]
do_test wal-11.5 {
  execsql { 
    SELECT count(*) FROM t1;
    PRAGMA integrity_check;
  }
} {16 ok}
do_test wal-11.6 {
  execsql COMMIT
  list [expr [file size test.db]/1024] [file size test.db-wal]
} [list 3 [log_file_size 41 1024]]
do_test wal-11.7 {
  execsql { 
    SELECT count(*) FROM t1;
    PRAGMA integrity_check;
  }
} {16 ok}
do_test wal-11.8 {
  execsql { PRAGMA checkpoint }
  list [expr [file size test.db]/1024] [file size test.db-wal]
} [list 37 [log_file_size 41 1024]]
do_test wal-11.9 {
  db close

  list [expr [file size test.db]/1024] [log_deleted test.db-wal]
} {37 1}
sqlite3_wal db test.db
do_test wal-11.10 {
  execsql {
    PRAGMA cache_size = 10;
    BEGIN;
      INSERT INTO t1 SELECT blob(900) FROM t1;   -- 32
      SELECT count(*) FROM t1;
  }
  list [expr [file size test.db]/1024] [file size test.db-wal]
} [list 37 [log_file_size 35 1024]]
do_test wal-11.11 {
  execsql {
      SELECT count(*) FROM t1;
    ROLLBACK;
    SELECT count(*) FROM t1;
  }
} {32 16}
do_test wal-11.12 {
  list [expr [file size test.db]/1024] [file size test.db-wal]
} [list 37 [log_file_size 35 1024]]
do_test wal-11.13 {
  execsql {
    INSERT INTO t1 VALUES( blob(900) );
    SELECT count(*) FROM t1;
    PRAGMA integrity_check;
  }
} {17 ok}
do_test wal-11.14 {
  list [expr [file size test.db]/1024] [file size test.db-wal]
} [list 37 [log_file_size 35 1024]]


#-------------------------------------------------------------------------
# This block of tests, wal-12.*, tests the fix for a problem that 
# could occur if a log that is a prefix of an older log is written 
# into a reused log file.
#
︙︙