sqlite-3.40.0/test/wal3.test

# 2010 April 13
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the operation of the library in
# "PRAGMA journal_mode=WAL" mode.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
source $testdir/lock_common.tcl
source $testdir/wal_common.tcl
source $testdir/malloc_common.tcl
ifcapable !wal {finish_test ; return }

set a_string_counter 1
proc a_string {n} {
  global a_string_counter
  incr a_string_counter
  string range [string repeat "${a_string_counter}." $n] 1 $n
}
db func a_string a_string

#-------------------------------------------------------------------------
# When a rollback or savepoint rollback occurs, the client may remove
# elements from one of the hash tables in the wal-index. This block
# of test cases tests that nothing appears to go wrong when this is
# done.
#
do_test wal3-1.0 {
  execsql {
    PRAGMA cache_size = 2000;
    PRAGMA page_size = 1024;
    PRAGMA auto_vacuum = off;
    PRAGMA synchronous = normal;
    PRAGMA journal_mode = WAL;
    PRAGMA wal_autocheckpoint = 0;
    BEGIN;
      CREATE TABLE t1(x);
      INSERT INTO t1 VALUES( a_string(800) );                  /*    1 */
      INSERT INTO t1 SELECT a_string(800) FROM t1;             /*    2 */
      INSERT INTO t1 SELECT a_string(800) FROM t1;             /*    4 */
      INSERT INTO t1 SELECT a_string(800) FROM t1;             /*    8 */
      INSERT INTO t1 SELECT a_string(800) FROM t1;             /*   16 */
      INSERT INTO t1 SELECT a_string(800) FROM t1;             /*   32 */
      INSERT INTO t1 SELECT a_string(800) FROM t1;             /*   64 */
      INSERT INTO t1 SELECT a_string(800) FROM t1;             /*  128*/
      INSERT INTO t1 SELECT a_string(800) FROM t1;             /*  256 */
      INSERT INTO t1 SELECT a_string(800) FROM t1;             /*  512 */
      INSERT INTO t1 SELECT a_string(800) FROM t1;             /* 1024 */
      INSERT INTO t1 SELECT a_string(800) FROM t1;             /* 2048 */
      INSERT INTO t1 SELECT a_string(800) FROM t1 LIMIT 2000;  /* 4048 */
    COMMIT;
    PRAGMA cache_size = 10;
  }
  wal_frame_count test.db-wal 1024
} 4086

for {set i 1} {$i < 20} {incr i} {

  do_test wal3-1.$i.1 {
    set str [a_string 800]
    execsql { UPDATE t1 SET x = $str WHERE rowid = $i }
    lappend L [wal_frame_count test.db-wal 1024]
    execsql {
      BEGIN;
        INSERT INTO t1 SELECT a_string(800) FROM t1 LIMIT 100;
      ROLLBACK;
      PRAGMA integrity_check;
    }
  } {ok}

  # Check that everything looks OK from the point of view of an
  # external connection.
  #
  sqlite3 db2 test.db
  do_test wal3-1.$i.2 {
    execsql { SELECT count(*) FROM t1 } db2
  } 4048
  do_test wal3-1.$i.3 {
    execsql { SELECT x FROM t1 WHERE rowid = $i }
  } $str
  do_test wal3-1.$i.4 {
    execsql { PRAGMA integrity_check } db2
  } {ok}
  db2 close

  # Check that the file-system in its current state can be recovered.
  #
  file copy -force test.db test2.db
  file copy -force test.db-wal test2.db-wal
  file delete -force test2.db-journal
  sqlite3 db2 test2.db
  do_test wal3-1.$i.5 {
    execsql { SELECT count(*) FROM t1 } db2
  } 4048
  do_test wal3-1.$i.6 {
    execsql { SELECT x FROM t1 WHERE rowid = $i }
  } $str
  do_test wal3-1.$i.7 {
    execsql { PRAGMA integrity_check } db2
  } {ok}
  db2 close
}

db close
foreach code [list {
  proc code2 {tcl} { uplevel #0 $tcl }
  proc code3 {tcl} { uplevel #0 $tcl }
  set tn singleproc
} {
  set ::code2_chan [launch_testfixture]
  set ::code3_chan [launch_testfixture]
  proc code2 {tcl} { testfixture $::code2_chan $tcl }
  proc code3 {tcl} { testfixture $::code3_chan $tcl }
  set tn multiproc
}] {
  file delete -force test.db test.db-wal test.db-journal
  sqlite3 db test.db
  eval $code

  # Open connections [db2] and [db3]. Depending on which iteration this
  # is, the connections may be created in this interpreter, or in
  # interpreters running in other OS processes. As such, the [db2] and [db3]
  # commands should only be accessed within [code2] and [code3] blocks,
  # respectively.
  #
  code2 { sqlite3 db2 test.db ; db2 eval { PRAGMA journal_mode = WAL } }
  code3 { sqlite3 db3 test.db ; db3 eval { PRAGMA journal_mode = WAL } }

  # Shorthand commands. Execute SQL using database connection [db], [db2]
  # or [db3]. Return the results.
  #
  proc sql  {sql} { db eval $sql }
  proc sql2 {sql} { code2 [list db2 eval $sql] }
  proc sql3 {sql} { code3 [list db3 eval $sql] }

  do_test wal3-2.$tn.1 {
    sql {
      PRAGMA page_size = 1024;
      PRAGMA auto_vacuum = OFF;
      PRAGMA journal_mode = WAL;
    }
    sql {
      CREATE TABLE t1(a, b);
      INSERT INTO t1 VALUES(1, 'one');
      BEGIN;
        SELECT * FROM t1;
    }
  } {1 one}
  do_test wal3-2.$tn.2 {
    sql2 {
      CREATE TABLE t2(a, b);
      INSERT INTO t2 VALUES(2, 'two');
      BEGIN;
        SELECT * FROM t2;
    }
  } {2 two}
  do_test wal3-2.$tn.3 {
    sql3 {
      CREATE TABLE t3(a, b);
      INSERT INTO t3 VALUES(3, 'three');
      BEGIN;
        SELECT * FROM t3;
    }
  } {3 three}

  # Try to checkpoint the database using [db]. It should be possible to
  # checkpoint everything except the table added by [db3] (checkpointing
  # these frames would clobber the snapshot currently being used by [db2]).
  #
  # After [db2] has committed, a checkpoint can copy the entire log to the
  # database file. Checkpointing after [db3] has committed is therefore a
  # no-op, as the entire log has already been backfilled.
  #
  do_test wal3-2.$tn.4 {
    sql {
      COMMIT;
      PRAGMA wal_checkpoint;
    }
    file size test.db
  } [expr $AUTOVACUUM ? 4*1024 : 3*1024]
  do_test wal3-2.$tn.5 {
    sql2 {
      COMMIT;
      PRAGMA wal_checkpoint;
    }
    file size test.db
  } [expr $AUTOVACUUM ? 5*1024 : 4*1024]
  do_test wal3-2.$tn.6 {
    sql3 {
      COMMIT;
      PRAGMA wal_checkpoint;
    }
    file size test.db
  } [expr $AUTOVACUUM ? 5*1024 : 4*1024]

  catch { db close }
  catch { code2 { db2 close } }
  catch { code3 { db3 close } }
  catch { close $::code2_chan }
  catch { close $::code3_chan }
}
catch {db close}

#-------------------------------------------------------------------------
# Test that that for the simple test:
#
#   CREATE TABLE x(y);
#   INSERT INTO x VALUES('z');
#   PRAGMA wal_checkpoint;
#
# in WAL mode the xSync method is invoked as expected for each of
# synchronous=off, synchronous=normal and synchronous=full.
#
foreach {tn syncmode synccount} {
  1 off
    {}
  2 normal
    {test.db-wal normal test.db normal}
  3 full
    {test.db-wal normal test.db-wal normal test.db-wal normal test.db normal}
} {

  proc sync_counter {args} {
    foreach {method filename id flags} $args break
    lappend ::syncs [file tail $filename] $flags
  }
  do_test wal3-3.$tn {
    file delete -force test.db test.db-wal test.db-journal

    testvfs T
    T filter {}
    T script sync_counter
    sqlite3 db test.db -vfs T

    execsql "PRAGMA synchronous = $syncmode"
    execsql { PRAGMA journal_mode = WAL }

    set ::syncs [list]
    T filter xSync
    execsql {
      CREATE TABLE x(y);
      INSERT INTO x VALUES('z');
      PRAGMA wal_checkpoint;
    }
    T filter {}
    set ::syncs
  } $synccount

  db close
  T delete
}

#-------------------------------------------------------------------------
# When recovering the contents of a WAL file, a process obtains the WRITER
# lock, then locks all other bytes before commencing recovery. If it fails
# to lock all other bytes (because some other process is holding a read
# lock) it should retry up to 100 times. Then return SQLITE_PROTOCOL to the
# caller. Test this (test case wal3-4.3).
#
# Also test the effect of hitting an SQLITE_BUSY while attempting to obtain
# the WRITER lock (should be the same). Test case wal3-4.4.
#
proc lock_callback {method filename handle lock} {
  lappend ::locks $lock
}
do_test wal3-4.1 {
  testvfs T
  T filter xShmLock
  T script lock_callback
  set ::locks [list]
  sqlite3 db test.db -vfs T
  execsql { SELECT * FROM x }
  lrange $::locks 0 3
} [list {0 1 lock exclusive} {1 7 lock exclusive}      \
        {1 7 unlock exclusive} {0 1 unlock exclusive}  \
]
do_test wal3-4.2 {
  db close
  set ::locks [list]
  sqlite3 db test.db -vfs T
  execsql { SELECT * FROM x }
  lrange $::locks 0 3
} [list {0 1 lock exclusive} {1 7 lock exclusive}      \
        {1 7 unlock exclusive} {0 1 unlock exclusive}  \
]
proc lock_callback {method filename handle lock} {
  if {$lock == "1 7 lock exclusive"} { return SQLITE_BUSY }
  return SQLITE_OK
}
puts "  Warning: This next test case causes SQLite to call xSleep(1) 100 times."
puts "  Normally this equates to a 100ms delay, but if SQLite is built on unix"
puts "  without HAVE_USLEEP defined, it may be 100 seconds."
do_test wal3-4.3 {
  db close
  set ::locks [list]
  sqlite3 db test.db -vfs T
  catchsql { SELECT * FROM x }
} {1 {locking protocol}}

puts "  Warning: Same again!"
proc lock_callback {method filename handle lock} {
  if {$lock == "0 1 lock exclusive"} { return SQLITE_BUSY }
  return SQLITE_OK
}
do_test wal3-4.4 {
  db close
  set ::locks [list]
  sqlite3 db test.db -vfs T
  catchsql { SELECT * FROM x }
} {1 {locking protocol}}
db close
T delete


#-------------------------------------------------------------------------
# Only one client may run recovery at a time. Test this mechanism.
#
# When client-2 tries to open a read transaction while client-1 is
# running recovery, it fails to obtain a lock on an aReadMark[] slot
# (because they are all locked by recovery). It then tries to obtain
# a shared lock on the RECOVER lock to see if there really is a
# recovery running or not.
#
# This block of tests checks the effect of an SQLITE_BUSY or SQLITE_IOERR
# being returned when client-2 attempts a shared lock on the RECOVER byte.
#
# An SQLITE_BUSY should be converted to an SQLITE_BUSY_RECOVERY. An
# SQLITE_IOERR should be returned to the caller.
#
do_test wal3-5.1 {
  faultsim_delete_and_reopen
  execsql {
    PRAGMA journal_mode = WAL;
    CREATE TABLE t1(a, b);
    INSERT INTO t1 VALUES(1, 2);
    INSERT INTO t1 VALUES(3, 4);
  }
  faultsim_save_and_close
} {}

testvfs T -default 1
T script method_callback

proc method_callback {method args} {
  if {$method == "xShmBarrier"} {
    incr ::barrier_count
    if {$::barrier_count == 1} {
      # This code is executed within the xShmBarrier() callback invoked
      # by the client running recovery as part of writing the recovered
      # wal-index header. If a second client attempts to access the
      # database now, it reads a corrupt (partially written) wal-index
      # header. But it cannot even get that far, as the first client
      # is still holding all the locks (recovery takes an exclusive lock
      # on *all* db locks, preventing access by any other client).
      #
      # If global variable ::wal3_do_lockfailure is non-zero, then set
      # things up so that an IO error occurs within an xShmLock() callback
      # made by the second client (aka [db2]).
      #
      sqlite3 db2 test.db
      if { $::wal3_do_lockfailure } { T filter xShmLock }
      set ::testrc [ catch { db2 eval "SELECT * FROM t1" } ::testmsg ]
      T filter {}
      db2 close
    }
  }

  if {$method == "xShmLock"} {
    foreach {file handle spec} $args break
    if { $spec == "2 1 lock shared" } {
      return SQLITE_IOERR
    }
  }

  return SQLITE_OK
}

# Test a normal SQLITE_BUSY return.
#
T filter xShmBarrier
set testrc ""
set testmsg ""
set barrier_count 0
set wal3_do_lockfailure 0
do_test wal3-5.2 {
  faultsim_restore_and_reopen
  execsql { SELECT * FROM t1 }
} {1 2 3 4}
do_test wal3-5.3 {
  list $::testrc $::testmsg
} {1 {database is locked}}
db close

# Test an SQLITE_IOERR return.
#
T filter xShmBarrier
set barrier_count 0
set wal3_do_lockfailure 1
set testrc ""
set testmsg ""
do_test wal3-5.4 {
  faultsim_restore_and_reopen
  execsql { SELECT * FROM t1 }
} {1 2 3 4}
do_test wal3-5.5 {
  list $::testrc $::testmsg
} {1 {disk I/O error}}

db close
T delete

#-------------------------------------------------------------------------
# When opening a read-transaction on a database, if the entire log has
# already been copied to the database file, the reader grabs a special
# kind of read lock (on aReadMark[0]). This set of test cases tests the
# outcome of the following:
#
#   + The reader discovering that between the time when it determined
#     that the log had been completely backfilled and the lock is obtained
#     that a writer has written to the log. In this case the reader should
#     acquire a different read-lock (not aReadMark[0]) and read the new
#     snapshot.
#
#   + The attempt to obtain the lock on aReadMark[0] fails with SQLITE_BUSY.
#     This can happen if a checkpoint is ongoing. In this case also simply
#     obtain a different read-lock.
#
catch {db close}
testvfs T -default 1
do_test wal3-6.1.1 {
  file delete -force test.db test.db-journal test.db wal
  sqlite3 db test.db
  execsql { PRAGMA journal_mode = WAL }
  execsql {
    CREATE TABLE t1(a, b);
    INSERT INTO t1 VALUES('o', 't');
    INSERT INTO t1 VALUES('t', 'f');
  }
} {}
do_test wal3-6.1.2 {
  sqlite3 db2 test.db
  sqlite3 db3 test.db
  execsql { BEGIN ; SELECT * FROM t1 } db3
} {o t t f}
do_test wal3-6.1.3 {
  execsql { PRAGMA wal_checkpoint } db2
} {}

# At this point the log file has been fully checkpointed. However,
# connection [db3] holds a lock that prevents the log from being wrapped.
# Test case 3.6.1.4 has [db] attempt a read-lock on aReadMark[0]. But
# as it is obtaining the lock, [db2] appends to the log file.
#
T filter xShmLock
T script lock_callback
proc lock_callback {method file handle spec} {
  if {$spec == "3 1 lock shared"} {
    # This is the callback for [db] to obtain the read lock on aReadMark[0].
    # Disable future callbacks using [T filter {}] and write to the log
    # file using [db2]. [db3] is preventing [db2] from wrapping the log
    # here, so this is an append.
    T filter {}
    db2 eval { INSERT INTO t1 VALUES('f', 's') }
  }
  return SQLITE_OK
}
do_test wal3-6.1.4 {
  execsql {
    BEGIN;
    SELECT * FROM t1;
  }
} {o t t f f s}

# [db] should be left holding a read-lock on some slot other than
# aReadMark[0]. Test this by demonstrating that the read-lock is preventing
# the log from being wrapped.
#
do_test wal3-6.1.5 {
  db3 eval COMMIT
  db2 eval { PRAGMA wal_checkpoint }
  set sz1 [file size test.db-wal]
  db2 eval { INSERT INTO t1 VALUES('s', 'e') }
  set sz2 [file size test.db-wal]
  expr {$sz2>$sz1}
} {1}

# Test that if [db2] had not interfered when [db] was trying to grab
# aReadMark[0], it would have been possible to wrap the log in 3.6.1.5.
#
do_test wal3-6.1.6 {
  execsql { COMMIT }
  execsql { PRAGMA wal_checkpoint } db2
  execsql {
    BEGIN;
    SELECT * FROM t1;
  }
} {o t t f f s s e}
do_test wal3-6.1.7 {
  db2 eval { PRAGMA wal_checkpoint }
  set sz1 [file size test.db-wal]
  db2 eval { INSERT INTO t1 VALUES('n', 't') }
  set sz2 [file size test.db-wal]
  expr {$sz2==$sz1}
} {1}

db3 close
db2 close
db close

do_test wal3-6.2.1 {
  file delete -force test.db test.db-journal test.db wal
  sqlite3 db test.db
  sqlite3 db2 test.db
  execsql { PRAGMA journal_mode = WAL }
  execsql {
    CREATE TABLE t1(a, b);
    INSERT INTO t1 VALUES('h', 'h');
    INSERT INTO t1 VALUES('l', 'b');
  }
} {}

T filter xShmLock
T script lock_callback
proc lock_callback {method file handle spec} {
  if {$spec == "3 1 unlock exclusive"} {
    T filter {}
    set ::R [db2 eval {
      BEGIN;
      SELECT * FROM t1;
    }]
  }
}
do_test wal3-6.2.2 {
  execsql { PRAGMA wal_checkpoint }
} {}
do_test wal3-6.2.3 {
  set ::R
} {h h l b}
do_test wal3-6.2.4 {
  set sz1 [file size test.db-wal]
  execsql { INSERT INTO t1 VALUES('b', 'c'); }
  set sz2 [file size test.db-wal]
  expr {$sz2 > $sz1}
} {1}
do_test wal3-6.2.5 {
  db2 eval { COMMIT }
  execsql { PRAGMA wal_checkpoint }
  set sz1 [file size test.db-wal]
  execsql { INSERT INTO t1 VALUES('n', 'o'); }
  set sz2 [file size test.db-wal]
  expr {$sz2 == $sz1}
} {1}

db2 close
db close
T delete

#-------------------------------------------------------------------------
# When opening a read-transaction on a database, if the entire log has
# not yet been copied to the database file, the reader grabs a read
# lock on aReadMark[x], where x>0. The following test cases experiment
# with the outcome of the following:
#
#   + The reader discovering that between the time when it read the
#     wal-index header and the lock was obtained that a writer has
#     written to the log. In this case the reader should re-read the
#     wal-index header and lock a snapshot corresponding to the new
#     header.
#
#   + The value in the aReadMark[x] slot has been modified since it was
#     read.
#
catch {db close}
testvfs T -default 1
do_test wal3-7.1.1 {
  file delete -force test.db test.db-journal test.db wal
  sqlite3 db test.db
  execsql {
    PRAGMA journal_mode = WAL;
    CREATE TABLE blue(red PRIMARY KEY, green);
  }
} {wal}

T script method_callback
T filter xOpen
proc method_callback {method args} {
  if {$method == "xOpen"} { return "reader" }
}
do_test wal3-7.1.2 {
  sqlite3 db2 test.db
  execsql { SELECT * FROM blue } db2
} {}

T filter xShmLock
set ::locks [list]
proc method_callback {method file handle spec} {
  if {$handle != "reader" } { return }
  if {$method == "xShmLock"} {
    catch { execsql { INSERT INTO blue VALUES(1, 2) } }
    catch { execsql { INSERT INTO blue VALUES(3, 4) } }
  }
  lappend ::locks $spec
}
do_test wal3-7.1.3 {
  execsql { SELECT * FROM blue } db2
} {1 2 3 4}
do_test wal3-7.1.4 {
  set ::locks
} {{4 1 lock shared} {4 1 unlock shared} {5 1 lock shared} {5 1 unlock shared}}

set ::locks [list]
proc method_callback {method file handle spec} {
  if {$handle != "reader" } { return }
  if {$method == "xShmLock"} {
    catch { execsql { INSERT INTO blue VALUES(5, 6) } }
  }
  lappend ::locks $spec
}
do_test wal3-7.2.1 {
  execsql { SELECT * FROM blue } db2
} {1 2 3 4 5 6}
do_test wal3-7.2.2 {
  set ::locks
} {{5 1 lock shared} {5 1 unlock shared} {4 1 lock shared} {4 1 unlock shared}}

db close
db2 close
T delete

#-------------------------------------------------------------------------
#
do_test wal3-8.1 {
  file delete -force test.db test.db-journal test.db wal
  sqlite3 db test.db
  sqlite3 db2 test.db
  execsql {
    PRAGMA journal_mode = WAL;
    CREATE TABLE b(c);
    INSERT INTO b VALUES('Tehran');
    INSERT INTO b VALUES('Qom');
    INSERT INTO b VALUES('Markazi');
    PRAGMA wal_checkpoint;
  }
} {wal}
do_test wal3-8.2 {
  execsql { SELECT * FROM b }
} {Tehran Qom Markazi}
do_test wal3-8.3 {
  db eval { SELECT * FROM b } {
    db eval { INSERT INTO b VALUES('Qazvin') }
    set r [db2 eval { SELECT * FROM b }]
    break
  }
  set r
} {Tehran Qom Markazi Qazvin}
do_test wal3-8.4 {
  execsql {
    INSERT INTO b VALUES('Gilan');
    INSERT INTO b VALUES('Ardabil');
  }
} {}
db2 close

faultsim_save_and_close
testvfs T -default 1
faultsim_restore_and_reopen
T filter xShmLock
T script lock_callback

proc lock_callback {method file handle spec} {
  if {$spec == "4 1 unlock exclusive"} {
    T filter {}
    set ::r [catchsql { SELECT * FROM b } db2]
  }
}
sqlite3 db test.db
sqlite3 db2 test.db
do_test wal3-8.5 {
  execsql { SELECT * FROM b }
} {Tehran Qom Markazi Qazvin Gilan Ardabil}
do_test wal3-8.6 {
  set ::r
} {1 {locking protocol}}

db close
db2 close

faultsim_restore_and_reopen
sqlite3 db2 test.db
T filter xShmLock
T script lock_callback
proc lock_callback {method file handle spec} {
  if {$spec == "1 7 unlock exclusive"} {
    T filter {}
    set ::r [catchsql { SELECT * FROM b } db2]
  }
}
unset ::r
do_test wal3-8.5 {
  execsql { SELECT * FROM b }
} {Tehran Qom Markazi Qazvin Gilan Ardabil}
do_test wal3-8.6 {
  set ::r
} {1 {locking protocol}}

db close
db2 close
T delete

#-------------------------------------------------------------------------
# When a connection opens a read-lock on the database, it searches for
# an aReadMark[] slot that is already set to the mxFrame value for the
# new transaction. If it cannot find one, it attempts to obtain an
# exclusive lock on an aReadMark[] slot for the purposes of modifying
# the value, then drops back to a shared-lock for the duration of the
# transaction.
#
# This test case verifies that if an exclusive lock cannot be obtained
# on any aReadMark[] slot (because there are already several readers),
# the client takes a shared-lock on a slot without modifying the value
# and continues.
#
do_test wal3-9.0 {
  file delete -force test.db test.db-journal test.db wal
  sqlite3 db test.db
  execsql {
    PRAGMA journal_mode = WAL;
    CREATE TABLE whoami(x);
    INSERT INTO whoami VALUES('nobody');
  }
} {wal}
for {set i 0} {$i < 50} {incr i} {
  set c db$i
  do_test wal3-9.1.$i {
    sqlite3 $c test.db
    execsql { UPDATE whoami SET x = $c }
    execsql {
      BEGIN;
      SELECT * FROM whoami
    } $c
  } $c
}
for {set i 0} {$i < 50} {incr i} {
  set c db$i
  do_test wal3-9.2.$i {
    execsql { SELECT * FROM whoami } $c
  } $c
}
do_test wal3-9.3 {
  for {set i 0} {$i < 49} {incr i} { db$i close }
  execsql { PRAGMA wal_checkpoint }
  set sz1 [file size test.db]
  db49 close
  execsql { PRAGMA wal_checkpoint }
  set sz2 [file size test.db]
  expr {$sz2 > $sz1}
} {1}

db close

finish_test