/ Check-in [9435f313]
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 tests to check inter-process WAL locking.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | wal
Files: files | file ages | folders
SHA1: 9435f3135849e0d38fde1669201db508561a6308
User & Date: dan 2010-04-14 18:06:51
Context
2010-04-14
18:50
Add tests and fix bugs in WAL locking mechanism. check-in: c18077f2 user: dan tags: wal
18:06
Add tests to check inter-process WAL locking. check-in: 9435f313 user: dan tags: wal
15:49
Improve the logLockRegion() function in log.c. check-in: 5e9dd3bd user: dan tags: wal
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/log.c.

  1096   1096         /* 0000 */ {0, 0},    /* 0001 */ {4, 1}, 
  1097   1097         /* 0010 */ {3, 1},    /* 0011 */ {3, 2},
  1098   1098         /* 0100 */ {2, 1},    /* 0101 */ {0, 0}, 
  1099   1099         /* 0110 */ {2, 2},    /* 0111 */ {2, 3},
  1100   1100         /* 1000 */ {1, 1},    /* 1001 */ {0, 0}, 
  1101   1101         /* 1010 */ {0, 0},    /* 1011 */ {0, 0},
  1102   1102         /* 1100 */ {1, 2},    /* 1101 */ {0, 0}, 
  1103         -      /* 1110 */ {1, 3},    /* 1111 */ {0, 0}
         1103  +      /* 1110 */ {0, 0},    /* 1111 */ {0, 0}
  1104   1104       };
  1105   1105       int rc;                       /* Return code of fcntl() */
  1106   1106       struct flock f;               /* Locking operation */
  1107   1107   
  1108   1108       assert( mRegion<ArraySize(aMap) && aMap[mRegion].iStart!=0 );
  1109   1109   
  1110   1110       memset(&f, 0, sizeof(f));

Changes to test/lock2.test.

    12     12   # focus of this script is database locks between competing processes.
    13     13   #
    14     14   # $Id: lock2.test,v 1.11 2009/05/01 10:55:34 danielk1977 Exp $
    15     15   
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
           19  +source $testdir/lock_common.tcl
    19     20   
    20         -# Launch another testfixture process to be controlled by this one. A
    21         -# channel name is returned that may be passed as the first argument to proc
    22         -# 'testfixture' to execute a command. The child testfixture process is shut
    23         -# down by closing the channel.
    24         -proc launch_testfixture {} {
    25         -  set prg [info nameofexec]
    26         -  if {$prg eq ""} {
    27         -    set prg [file join . testfixture]
    28         -  }
    29         -  set chan [open "|$prg tf_main.tcl" r+]
    30         -  fconfigure $chan -buffering line
    31         -  return $chan
    32         -}
    33         -
    34         -# Execute a command in a child testfixture process, connected by two-way
    35         -# channel $chan. Return the result of the command, or an error message.
    36         -proc testfixture {chan cmd} {
    37         -  puts $chan $cmd
    38         -  puts $chan OVER
    39         -  set r ""
    40         -  while { 1 } {
    41         -    set line [gets $chan]
    42         -    if { $line == "OVER" } { 
    43         -      return $r
    44         -    }
    45         -    if {[eof $chan]} {
    46         -      return "ERROR: Child process hung up"
    47         -    }
    48         -    append r $line
    49         -  }
    50         -}
    51         -
    52         -# Write the main loop for the child testfixture processes into file
    53         -# tf_main.tcl. The parent (this script) interacts with the child processes
    54         -# via a two way pipe. The parent writes a script to the stdin of the child
    55         -# process, followed by the word "OVER" on a line of its own. The child
    56         -# process evaluates the script and writes the results to stdout, followed
    57         -# by an "OVER" of its own.
    58         -set f [open tf_main.tcl w]
    59         -puts $f {
    60         -  set l [open log w]
    61         -  set script ""
    62         -  while {![eof stdin]} {
    63         -    flush stdout
    64         -    set line [gets stdin]
    65         -    puts $l "READ $line"
    66         -    if { $line == "OVER" } {
    67         -      catch {eval $script} result
    68         -      puts $result
    69         -      puts $l "WRITE $result"
    70         -      puts OVER
    71         -      puts $l "WRITE OVER"
    72         -      flush stdout
    73         -      set script ""
    74         -    } else {
    75         -      append script $line
    76         -      append script " ; "
    77         -    }
    78         -  }
    79         -  close $l
    80         -}
    81         -close $f
    82     21   
    83     22   # Simple locking test case:
    84     23   #
    85     24   # lock2-1.1: Connect a second process to the database.
    86     25   # lock2-1.2: Establish a RESERVED lock with this process.
    87     26   # lock2-1.3: Get a SHARED lock with the second process.
    88     27   # lock2-1.4: Try for a RESERVED lock with process 2. This fails.

Added test/lock_common.tcl.

            1  +# 2010 April 14
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file contains code used by several different test scripts. The
           12  +# code in this file allows testfixture to control another process (or
           13  +# processes) to test locking.
           14  +#
           15  +
           16  +# Launch another testfixture process to be controlled by this one. A
           17  +# channel name is returned that may be passed as the first argument to proc
           18  +# 'testfixture' to execute a command. The child testfixture process is shut
           19  +# down by closing the channel.
           20  +proc launch_testfixture {} {
           21  +  set prg [info nameofexec]
           22  +  if {$prg eq ""} {
           23  +    set prg [file join . testfixture]
           24  +  }
           25  +  set chan [open "|$prg tf_main.tcl" r+]
           26  +  fconfigure $chan -buffering line
           27  +  return $chan
           28  +}
           29  +
           30  +# Execute a command in a child testfixture process, connected by two-way
           31  +# channel $chan. Return the result of the command, or an error message.
           32  +proc testfixture {chan cmd} {
           33  +  puts $chan $cmd
           34  +  puts $chan OVER
           35  +  set r ""
           36  +  while { 1 } {
           37  +    set line [gets $chan]
           38  +    if { $line == "OVER" } { 
           39  +      return $r
           40  +    }
           41  +    if {[eof $chan]} {
           42  +      return "ERROR: Child process hung up"
           43  +    }
           44  +    append r $line
           45  +  }
           46  +}
           47  +
           48  +# Write the main loop for the child testfixture processes into file
           49  +# tf_main.tcl. The parent (this script) interacts with the child processes
           50  +# via a two way pipe. The parent writes a script to the stdin of the child
           51  +# process, followed by the word "OVER" on a line of its own. The child
           52  +# process evaluates the script and writes the results to stdout, followed
           53  +# by an "OVER" of its own.
           54  +set f [open tf_main.tcl w]
           55  +puts $f {
           56  +  set l [open log w]
           57  +  set script ""
           58  +  while {![eof stdin]} {
           59  +    flush stdout
           60  +    set line [gets stdin]
           61  +    puts $l "READ $line"
           62  +    if { $line == "OVER" } {
           63  +      catch {eval $script} result
           64  +      puts $result
           65  +      puts $l "WRITE $result"
           66  +      puts OVER
           67  +      puts $l "WRITE OVER"
           68  +      flush stdout
           69  +      set script ""
           70  +    } else {
           71  +      append script $line
           72  +      append script " ; "
           73  +    }
           74  +  }
           75  +  close $l
           76  +}
           77  +close $f

Changes to test/wal.test.

    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the operation of the library in
    13     13   # "PRAGMA journal_mode=WAL" mode.
    14     14   #
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18         -
    19         -proc range {args} {
    20         -  set ret [list]
    21         -  foreach {start end} $args {
    22         -    for {set i $start} {$i <= $end} {incr i} {
    23         -      lappend ret $i
    24         -    }
    25         -  }
    26         -  set ret
    27         -}
           18  +source $testdir/lock_common.tcl
    28     19   
    29     20   proc reopen_db {} {
    30     21     catch { db close }
    31     22     file delete -force test.db test.db-wal
    32     23     sqlite3_wal db test.db
    33         -  #register_logtest
    34         -}
    35         -proc register_logtest {{db db}} {
    36         -  register_logsummary_module $db
    37         -  execsql { CREATE VIRTUAL TABLE temp.logsummary USING logsummary } $db
    38         -  execsql { CREATE VIRTUAL TABLE temp.logcontent USING logcontent } $db
    39         -  execsql { CREATE VIRTUAL TABLE temp.loglock USING loglock } $db
    40     24   }
    41     25   
    42     26   proc sqlite3_wal {args} {
    43     27     eval sqlite3 $args
    44     28     [lindex $args 0] eval { PRAGMA journal_mode = wal }
    45     29   }
    46     30   
................................................................................
   305    289   } {ok}
   306    290   
   307    291   foreach handle {db db2 db3} { catch { $handle close } }
   308    292   unset handle
   309    293   
   310    294   #-------------------------------------------------------------------------
   311    295   # The following block of tests - wal-10.* - test that the WAL locking 
   312         -# scheme works for clients in a single process.
          296  +# scheme works in simple cases. This block of tests is run twice. Once
          297  +# using multiple connections in the address space of the current process,
          298  +# and once with all connections except one running in external processes.
   313    299   #
   314         -reopen_db
   315         -sqlite3_wal db2 test.db
   316         -sqlite3_wal db3 test.db
          300  +foreach code [list {
          301  +  set ::code2_chan [launch_testfixture]
          302  +  set ::code3_chan [launch_testfixture]
          303  +  proc code2 {tcl} { testfixture $::code2_chan $tcl }
          304  +  proc code3 {tcl} { testfixture $::code3_chan $tcl }
          305  +  set tn 1
          306  +} {
          307  +  proc code2 {tcl} { uplevel #0 $tcl }
          308  +  proc code3 {tcl} { uplevel #0 $tcl }
          309  +  set tn 2
          310  +}] {
          311  +
          312  +  eval $code
          313  +  reopen_db
          314  +
          315  +  # Open connections [db2] and [db3]. Depending on which iteration this
          316  +  # is, the connections may be created in this interpreter, or in 
          317  +  # interpreters running in other OS processes. As such, the [db2] and [db3]
          318  +  # commands should only be accessed within [code2] and [code3] blocks,
          319  +  # respectively.
          320  +  #
          321  +  code2 { sqlite3 db2 test.db ; db2 eval { PRAGMA journal_mode = WAL } }
          322  +  code3 { sqlite3 db3 test.db ; db3 eval { PRAGMA journal_mode = WAL } }
   317    323   
   318         -do_test wal-10.1 {
   319         -  execsql {
   320         -    CREATE TABLE t1(a, b);
   321         -    INSERT INTO t1 VALUES(1, 2);
   322         -    BEGIN;
   323         -      INSERT INTO t1 VALUES(3, 4);
   324         -  }
   325         -  execsql "SELECT * FROM t1" db2
   326         -} {1 2}
   327         -do_test wal-10.2 {
   328         -  execsql { COMMIT }
   329         -  execsql "SELECT * FROM t1" db2
   330         -} {1 2 3 4}
   331         -do_test wal-10.3 {
   332         -  execsql { 
   333         -    BEGIN;
   334         -      SELECT * FROM t1;
   335         -  } db2
   336         -} {1 2 3 4}
   337         -do_test wal-10.4 {
   338         -  catchsql { PRAGMA checkpoint } 
   339         -} {1 {database is locked}}
   340         -do_test wal-10.5 {
   341         -  execsql { INSERT INTO t1 VALUES(5, 6) }
   342         -  execsql { SELECT * FROM t1 } db2
   343         -} {1 2 3 4}
          324  +  # Shorthand commands. Execute SQL using database connection [db2] or 
          325  +  # [db3]. Return the results.
          326  +  #
          327  +  proc sql2 {sql} { code2 [list db2 eval $sql] }
          328  +  proc sql3 {sql} { code3 [list db3 eval $sql] }
   344    329   
   345         -# Connection [db2] is holding a lock on a snapshot, preventing [db] from
   346         -# checkpointing the database. Add a busy-handler to [db]. If [db2] completes
   347         -# its transaction from within the busy-handler, [db] is able to complete
   348         -# the checkpoint operation.
   349         -#
   350         -proc busyhandler x {
   351         -  if {$x==4} {
   352         -    execsql { COMMIT } db2
   353         -  }
   354         -  if {$x<5} {return 0}
   355         -  return 1
   356         -}
   357         -db busy busyhandler
   358         -do_test wal-10.6 {
   359         -  execsql { PRAGMA checkpoint } 
   360         -} {}
   361         -
   362         -# Similar to the test above. Except this time, a new read transaction is
   363         -# started (db3) while the checkpointer is waiting for an old one to finish.
   364         -# The checkpointer can finish, but any subsequent write operations must
   365         -# wait until after db3 has closed the read transaction.
   366         -#
   367         -db busy {}
   368         -do_test wal-10.7 {
   369         -  execsql { 
   370         -    BEGIN;
          330  +  # Initialize the database schema and contents.
          331  +  #
          332  +  do_test wal-10.$tn.1 {
          333  +    execsql {
          334  +      CREATE TABLE t1(a, b);
          335  +      INSERT INTO t1 VALUES(1, 2);
   371    336         SELECT * FROM t1;
   372         -  } db2
   373         -} {1 2 3 4 5 6}
   374         -do_test wal-10.8 {
   375         -  execsql { INSERT INTO t1 VALUES(7, 8) }
   376         -  catchsql { PRAGMA checkpoint } 
   377         -} {1 {database is locked}}
   378         -proc busyhandler x {
   379         -  if {$x==3} { execsql { BEGIN; SELECT * FROM t1 } db3 }
   380         -  if {$x==4} { execsql { COMMIT } db2 }
   381         -  if {$x<5}  { return 0 }
   382         -  return 1
   383         -}
   384         -db busy busyhandler
   385         -do_test wal-10.9 {
   386         -  execsql { PRAGMA checkpoint } 
   387         -} {}
   388         -do_test wal-10.10 {
   389         -  execsql { SELECT * FROM t1 } db3
   390         -} {1 2 3 4 5 6 7 8}
   391         -do_test wal-10.11 {
   392         -  catchsql { INSERT INTO t1 VALUES(9, 10) }
   393         -} {1 {database is locked}}
   394         -do_test wal-10.12 {
   395         -  execsql { SELECT * FROM t1 }
   396         -} {1 2 3 4 5 6 7 8}
   397         -do_test wal-10.13 {
   398         -  execsql { COMMIT } db3
   399         -} {}
   400         -do_test wal-10.14 {
   401         -  execsql { INSERT INTO t1 VALUES(9, 10) }
   402         -  execsql { SELECT * FROM t1 }
   403         -} {1 2 3 4 5 6 7 8 9 10}
          337  +    }
          338  +  } {1 2}
          339  +
          340  +  # Open a transaction and write to the database using [db]. Check that [db2]
          341  +  # is still able to read the snapshot before the transaction was opened.
          342  +  #
          343  +  do_test wal-10.$tn.2 {
          344  +    execsql { BEGIN; INSERT INTO t1 VALUES(3, 4); }
          345  +    sql2 {SELECT * FROM t1}
          346  +  } {1 2}
          347  +
          348  +  # Have [db] commit the transaction. Check that [db2] is now seeing the 
          349  +  # new, updated snapshot.
          350  +  #
          351  +  do_test wal-10.$tn.3 {
          352  +    execsql { COMMIT }
          353  +    sql2 {SELECT * FROM t1}
          354  +  } {1 2 3 4}
          355  +
          356  +  # Have [db2] open a read transaction. Then write to the db via [db]. Check
          357  +  # that [db2] is still seeing the original snapshot. Then read with [db3].
          358  +  # [db3] should see the newly committed data.
          359  +  #
          360  +  do_test wal-10.$tn.4 {
          361  +    sql2 { BEGIN ; SELECT * FROM t1}
          362  +  } {1 2 3 4}
          363  +  do_test wal-10.$tn.5 {
          364  +    execsql { INSERT INTO t1 VALUES(5, 6); }
          365  +    sql2 {SELECT * FROM t1}
          366  +  } {1 2 3 4}
          367  +  do_test wal-10.$tn.6 {
          368  +    sql3 {SELECT * FROM t1}
          369  +  } {1 2 3 4 5 6}
          370  +  do_test wal-10.$tn.7 {
          371  +    sql2 COMMIT
          372  +  } {}
          373  +
          374  +  # Have [db2] open a write transaction. Then attempt to write to the 
          375  +  # database via [db]. This should fail (writer lock cannot be obtained).
          376  +  #
          377  +  # Then open a read-transaction with [db]. Commit the [db2] transaction
          378  +  # to disk. Verify that [db] still cannot write to the database (because
          379  +  # it is reading an old snapshot).
          380  +  #
          381  +  # Close the current [db] transaction. Open a new one. [db] can now write
          382  +  # to the database (as it is not locked and [db] is reading the latest
          383  +  # snapshot).
          384  +  #
          385  +  do_test wal-10.$tn.7 {
          386  +    sql2 { BEGIN; INSERT INTO t1 VALUES(7, 8) ; }
          387  +    catchsql { INSERT INTO t1 VALUES(9, 10) }
          388  +  } {1 {database is locked}}
          389  +  do_test wal-10.$tn.8 {
          390  +    execsql { BEGIN ; SELECT * FROM t1 }
          391  +  } {1 2 3 4 5 6}
          392  +  do_test wal-10.$tn.9 {
          393  +    sql2 COMMIT
          394  +    catchsql { INSERT INTO t1 VALUES(9, 10) }
          395  +  } {1 {database is locked}}
          396  +  do_test wal-10.$tn.10 {
          397  +    execsql { COMMIT; BEGIN; INSERT INTO t1 VALUES(9, 10); COMMIT; }
          398  +    execsql { SELECT * FROM t1 }
          399  +  } {1 2 3 4 5 6 7 8 9 10}
          400  +
          401  +  # Open a read transaction with [db2]. Check that this prevents [db] from
          402  +  # checkpointing the database. But not from writing to it.
          403  +  #
          404  +  do_test wal-10.$tn.11 {
          405  +    sql2 { BEGIN; SELECT * FROM t1 }
          406  +  } {1 2 3 4 5 6 7 8 9 10}
          407  +  do_test wal-10.$tn.12 {
          408  +    catchsql { PRAGMA checkpoint } 
          409  +  } {1 {database is locked}}
          410  +  do_test wal-10.$tn.13 {
          411  +    execsql { INSERT INTO t1 VALUES(11, 12) }
          412  +    sql2 {SELECT * FROM t1}
          413  +  } {1 2 3 4 5 6 7 8 9 10}
          414  +
          415  +  # Connection [db2] is holding a lock on a snapshot, preventing [db] from
          416  +  # checkpointing the database. Add a busy-handler to [db]. If [db2] completes
          417  +  # its transaction from within the busy-handler, [db] is able to complete
          418  +  # the checkpoint operation.
          419  +  #
          420  +  proc busyhandler x {
          421  +    if {$x==4} { sql2 COMMIT }
          422  +    if {$x<5} { return 0 }
          423  +    return 1
          424  +  }
          425  +  db busy busyhandler
          426  +  do_test wal-10.$tn.14 {
          427  +    execsql { PRAGMA checkpoint } 
          428  +  } {}
          429  +
          430  +  # Similar to the test above. Except this time, a new read transaction is
          431  +  # started (db3) while the checkpointer is waiting for an old one (db2) to 
          432  +  # finish. The checkpointer can finish, but any subsequent write operations 
          433  +  # must wait until after db3 has closed the read transaction, as db3 is a
          434  +  # "region D" writer.
          435  +  #
          436  +  db busy {}
          437  +  do_test wal-10.$tn.15 {
          438  +    sql2 { BEGIN; SELECT * FROM t1; }
          439  +  } {1 2 3 4 5 6 7 8 9 10 11 12}
          440  +  do_test wal-10.$tn.16 {
          441  +    catchsql { PRAGMA checkpoint } 
          442  +  } {1 {database is locked}}
          443  +  proc busyhandler x {
          444  +    if {$x==3} { sql3 { BEGIN; SELECT * FROM t1 } }
          445  +    if {$x==4} { sql2 COMMIT }
          446  +    if {$x<5}  { return 0 }
          447  +    return 1
          448  +  }
          449  +  db busy busyhandler
          450  +  do_test wal-10.$tn.9 {
          451  +    execsql { PRAGMA checkpoint } 
          452  +  } {}
          453  +  do_test wal-10.$tn.10 {
          454  +    sql3 { SELECT * FROM t1 }
          455  +  } {1 2 3 4 5 6 7 8 9 10 11 12}
          456  +  do_test wal-10.$tn.11 {
          457  +    catchsql { INSERT INTO t1 VALUES(13, 14) }
          458  +  } {1 {database is locked}}
          459  +  do_test wal-10.$tn.12 {
          460  +    execsql { SELECT * FROM t1 }
          461  +  } {1 2 3 4 5 6 7 8 9 10 11 12}
          462  +  do_test wal-10.$tn.13 {
          463  +    sql3 COMMIT
          464  +  } {}
          465  +  do_test wal-10.$tn.14 {
          466  +    execsql { INSERT INTO t1 VALUES(13, 14) }
          467  +    execsql { SELECT * FROM t1 }
          468  +  } {1 2 3 4 5 6 7 8 9 10 11 12 13 14}
   404    469   
   405         -foreach handle {db db2 db3} { catch { $handle close } }
   406         -unset handle
          470  +  catch { db close }
          471  +  catch { code2 { db2 close } }
          472  +  catch { code3 { db3 close } }
          473  +  catch { close $::code2_chan }
          474  +  catch { close $::code3_chan }
          475  +}
          476  +
   407    477   finish_test
   408    478