000001  # 2001 September 15
000002  #
000003  # The author disclaims copyright to this source code.  In place of
000004  # a legal notice, here is a blessing:
000005  #
000006  #    May you do good and not evil.
000007  #    May you find forgiveness for yourself and forgive others.
000008  #    May you share freely, never taking more than you give.
000009  #
000010  #***********************************************************************
000011  # This file implements regression tests for SQLite library.  The
000012  # focus of this script is database locks.
000013  #
000014  # $Id: lock.test,v 1.40 2009/06/16 17:49:36 drh Exp $
000015  
000016  
000017  set testdir [file dirname $argv0]
000018  source $testdir/tester.tcl
000019  
000020  # Create an alternative connection to the database
000021  #
000022  do_test lock-1.0 {
000023    # Give a complex pathname to stress the path simplification logic in
000024    # the vxworks driver and in test_async.
000025    file mkdir tempdir/t1/t2
000026    sqlite3 db2 ./tempdir/../tempdir/t1/.//t2/../../..//test.db
000027    set dummy {}
000028  } {}
000029  do_test lock-1.1 {
000030    execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}
000031  } {}
000032  do_test lock-1.2 {
000033    execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name} db2
000034  } {}
000035  do_test lock-1.3 {
000036    execsql {CREATE TABLE t1(a int, b int)}
000037    execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}
000038  } {t1}
000039  do_test lock-1.5 {
000040    catchsql {
000041       SELECT name FROM sqlite_master WHERE type='table' ORDER BY name
000042    } db2
000043  } {0 t1}
000044  
000045  do_test lock-1.6 {
000046    execsql {INSERT INTO t1 VALUES(1,2)}
000047    execsql {SELECT * FROM t1}
000048  } {1 2}
000049  # Update: The schema is now brought up to date by test lock-1.5.
000050  # do_test lock-1.7.1 {
000051  #   catchsql {SELECT * FROM t1} db2
000052  # } {1 {no such table: t1}}
000053  do_test lock-1.7.2 {
000054    catchsql {SELECT * FROM t1} db2
000055  } {0 {1 2}}
000056  do_test lock-1.8 {
000057    execsql {UPDATE t1 SET a=b, b=a} db2
000058    execsql {SELECT * FROM t1} db2
000059  } {2 1}
000060  do_test lock-1.9 {
000061    execsql {SELECT * FROM t1}
000062  } {2 1}
000063  do_test lock-1.10 {
000064    execsql {BEGIN TRANSACTION}
000065    execsql {UPDATE t1 SET a = 0 WHERE 0}
000066    execsql {SELECT * FROM t1}
000067  } {2 1}
000068  do_test lock-1.11 {
000069    catchsql {SELECT * FROM t1} db2
000070  } {0 {2 1}}
000071  do_test lock-1.12 {
000072    execsql {ROLLBACK}
000073    catchsql {SELECT * FROM t1}
000074  } {0 {2 1}}
000075  
000076  do_test lock-1.13 {
000077    execsql {CREATE TABLE t2(x int, y int)}
000078    execsql {INSERT INTO t2 VALUES(8,9)}
000079    execsql {SELECT * FROM t2}
000080  } {8 9}
000081  do_test lock-1.14.1 {
000082    catchsql {SELECT * FROM t2} db2
000083  } {0 {8 9}}
000084  do_test lock-1.14.2 {
000085    catchsql {SELECT * FROM t1} db2
000086  } {0 {2 1}}
000087  do_test lock-1.15 {
000088    catchsql {SELECT * FROM t2} db2
000089  } {0 {8 9}}
000090  
000091  do_test lock-1.16 {
000092    db eval {SELECT * FROM t1} qv {
000093      set x [db eval {SELECT * FROM t1}]
000094    }
000095    set x
000096  } {2 1}
000097  do_test lock-1.17 {
000098    db eval {SELECT * FROM t1} qv {
000099      set x [db eval {SELECT * FROM t2}]
000100    }
000101    set x
000102  } {8 9}
000103  
000104  # You cannot UPDATE a table from within the callback of a SELECT
000105  # on that same table because the SELECT has the table locked.
000106  #
000107  # 2006-08-16:  Reads no longer block writes within the same
000108  # database connection.
000109  #
000110  #do_test lock-1.18 {
000111  #  db eval {SELECT * FROM t1} qv {
000112  #    set r [catch {db eval {UPDATE t1 SET a=b, b=a}} msg]
000113  #    lappend r $msg
000114  #  }
000115  #  set r
000116  #} {1 {database table is locked}}
000117  
000118  # But you can UPDATE a different table from the one that is used in
000119  # the SELECT.
000120  #
000121  do_test lock-1.19 {
000122    db eval {SELECT * FROM t1} qv {
000123      set r [catch {db eval {UPDATE t2 SET x=y, y=x}} msg]
000124      lappend r $msg
000125    }
000126    set r
000127  } {0 {}}
000128  do_test lock-1.20 {
000129    execsql {SELECT * FROM t2}
000130  } {9 8}
000131  
000132  # It is possible to do a SELECT of the same table within the
000133  # callback of another SELECT on that same table because two
000134  # or more read-only cursors can be open at once.
000135  #
000136  do_test lock-1.21 {
000137    db eval {SELECT * FROM t1} qv {
000138      set r [catch {db eval {SELECT a FROM t1}} msg]
000139      lappend r $msg
000140    }
000141    set r
000142  } {0 2}
000143  
000144  # Under UNIX you can do two SELECTs at once with different database
000145  # connections, because UNIX supports reader/writer locks.  Under windows,
000146  # this is not possible.
000147  #
000148  if {$::tcl_platform(platform)=="unix"} {
000149    do_test lock-1.22 {
000150      db eval {SELECT * FROM t1} qv {
000151        set r [catch {db2 eval {SELECT a FROM t1}} msg]
000152        lappend r $msg
000153      }
000154      set r
000155    } {0 2}
000156  }
000157  integrity_check lock-1.23
000158  
000159  # If one thread has a transaction another thread cannot start
000160  # a transaction.  -> Not true in version 3.0.  But if one thread
000161  # as a RESERVED lock another thread cannot acquire one.
000162  #
000163  do_test lock-2.1 {
000164    execsql {BEGIN TRANSACTION}
000165    execsql {UPDATE t1 SET a = 0 WHERE 0}
000166    execsql {BEGIN TRANSACTION} db2
000167    set r [catch {execsql {UPDATE t1 SET a = 0 WHERE 0} db2} msg]
000168    execsql {ROLLBACK} db2
000169    lappend r $msg
000170  } {1 {database is locked}}
000171  
000172  # A thread can read when another has a RESERVED lock.
000173  #
000174  do_test lock-2.2 {
000175    catchsql {SELECT * FROM t2} db2
000176  } {0 {9 8}}
000177  
000178  # If the other thread (the one that does not hold the transaction with
000179  # a RESERVED lock) tries to get a RESERVED lock, we do get a busy callback
000180  # as long as we were not orginally holding a READ lock.
000181  #
000182  do_test lock-2.3.1 {
000183    proc callback {count} {
000184      set ::callback_value $count
000185      break
000186    }
000187    set ::callback_value {}
000188    db2 busy callback
000189    # db2 does not hold a lock so we should get a busy callback here
000190    set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]
000191    lappend r $msg
000192    lappend r $::callback_value
000193  } {1 {database is locked} 0}
000194  do_test lock-2.3.2 {
000195    set ::callback_value {}
000196    execsql {BEGIN; SELECT rowid FROM sqlite_master LIMIT 1} db2
000197    # This time db2 does hold a read lock.  No busy callback this time.
000198    set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]
000199    lappend r $msg
000200    lappend r $::callback_value
000201  } {1 {database is locked} {}}
000202  catch {execsql {ROLLBACK} db2}
000203  do_test lock-2.4.1 {
000204    proc callback {count} {
000205      lappend ::callback_value $count
000206      if {$count>4} break
000207    }
000208    set ::callback_value {}
000209    db2 busy callback
000210    # We get a busy callback because db2 is not holding a lock
000211    set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]
000212    lappend r $msg
000213    lappend r $::callback_value
000214  } {1 {database is locked} {0 1 2 3 4 5}}
000215  do_test lock-2.4.2 {
000216    proc callback {count} {
000217      lappend ::callback_value $count
000218      if {$count>4} break
000219    }
000220    set ::callback_value {}
000221    db2 busy callback
000222    execsql {BEGIN; SELECT rowid FROM sqlite_master LIMIT 1} db2
000223    # No busy callback this time because we are holding a lock
000224    set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]
000225    lappend r $msg
000226    lappend r $::callback_value
000227  } {1 {database is locked} {}}
000228  catch {execsql {ROLLBACK} db2}
000229  do_test lock-2.5 {
000230    proc callback {count} {
000231      lappend ::callback_value $count
000232      if {$count>4} break
000233    }
000234    set ::callback_value {}
000235    db2 busy callback
000236    set r [catch {execsql {SELECT * FROM t1} db2} msg]
000237    lappend r $msg
000238    lappend r $::callback_value
000239  } {0 {2 1} {}}
000240  execsql {ROLLBACK}
000241  
000242  # Test the built-in busy timeout handler
000243  #
000244  # EVIDENCE-OF: R-23579-05241 PRAGMA busy_timeout; PRAGMA busy_timeout =
000245  # milliseconds; Query or change the setting of the busy timeout.
000246  #
000247  do_test lock-2.8 {
000248    db2 timeout 400
000249    execsql BEGIN
000250    execsql {UPDATE t1 SET a = 0 WHERE 0}
000251    catchsql {BEGIN EXCLUSIVE;} db2
000252  } {1 {database is locked}}
000253  do_test lock-2.8b {
000254    db2 eval {PRAGMA busy_timeout}
000255  } {400}
000256  do_test lock-2.9 {
000257    db2 timeout 0
000258    execsql COMMIT
000259  } {}
000260  do_test lock-2.9b {
000261    db2 eval {PRAGMA busy_timeout}
000262  } {0}
000263  integrity_check lock-2.10
000264  do_test lock-2.11 {
000265    db2 eval {PRAGMA busy_timeout(400)}
000266    execsql BEGIN
000267    execsql {UPDATE t1 SET a = 0 WHERE 0}
000268    catchsql {BEGIN EXCLUSIVE;} db2
000269  } {1 {database is locked}}
000270  do_test lock-2.11b {
000271    db2 eval {PRAGMA busy_timeout}
000272  } {400}
000273  do_test lock-2.12 {
000274    db2 eval {PRAGMA busy_timeout(0)}
000275    execsql COMMIT
000276  } {}
000277  do_test lock-2.12b {
000278    db2 eval {PRAGMA busy_timeout}
000279  } {0}
000280  integrity_check lock-2.13
000281  
000282  # Try to start two transactions in a row
000283  #
000284  do_test lock-3.1 {
000285    execsql {BEGIN TRANSACTION}
000286    set r [catch {execsql {BEGIN TRANSACTION}} msg]
000287    execsql {ROLLBACK}
000288    lappend r $msg
000289  } {1 {cannot start a transaction within a transaction}}
000290  integrity_check lock-3.2
000291  
000292  # Make sure the busy handler and error messages work when
000293  # opening a new pointer to the database while another pointer
000294  # has the database locked.
000295  #
000296  do_test lock-4.1 {
000297    db2 close
000298    catch {db eval ROLLBACK}
000299    db eval BEGIN
000300    db eval {UPDATE t1 SET a=0 WHERE 0}
000301    sqlite3 db2 ./test.db
000302    catchsql {UPDATE t1 SET a=0} db2
000303  } {1 {database is locked}}
000304  do_test lock-4.2 {
000305    set ::callback_value {}
000306    set rc [catch {db2 eval {UPDATE t1 SET a=0}} msg]
000307    lappend rc $msg $::callback_value
000308  } {1 {database is locked} {}}
000309  do_test lock-4.3 {
000310    proc callback {count} {
000311      lappend ::callback_value $count
000312      if {$count>4} break
000313    }
000314    db2 busy callback
000315    set rc [catch {db2 eval {UPDATE t1 SET a=0}} msg]
000316    lappend rc $msg $::callback_value
000317  } {1 {database is locked} {0 1 2 3 4 5}}
000318  execsql {ROLLBACK}
000319  
000320  # When one thread is writing, other threads cannot read.  Except if the
000321  # writing thread is writing to its temporary tables, the other threads
000322  # can still read.  -> Not so in 3.0.  One thread can read while another
000323  # holds a RESERVED lock.
000324  #
000325  proc tx_exec {sql} {
000326    db2 eval $sql
000327  }
000328  do_test lock-5.1 {
000329    execsql {
000330      SELECT * FROM t1
000331    }
000332  } {2 1}
000333  do_test lock-5.2 {
000334    db function tx_exec tx_exec
000335    catchsql {
000336      INSERT INTO t1(a,b) SELECT 3, tx_exec('SELECT y FROM t2 LIMIT 1');
000337    }
000338  } {0 {}}
000339  
000340  ifcapable tempdb {
000341    do_test lock-5.3 {
000342      execsql {
000343        CREATE TEMP TABLE t3(x);
000344        SELECT * FROM t3;
000345      }
000346    } {}
000347    do_test lock-5.4 {
000348      catchsql {
000349        INSERT INTO t3 SELECT tx_exec('SELECT y FROM t2 LIMIT 1');
000350      }
000351    } {0 {}}
000352    do_test lock-5.5 {
000353      execsql {
000354        SELECT * FROM t3;
000355      }
000356    } {8}
000357    do_test lock-5.6 {
000358      catchsql {
000359        UPDATE t1 SET a=tx_exec('SELECT x FROM t2');
000360      }
000361    } {0 {}}
000362    do_test lock-5.7 {
000363      execsql {
000364        SELECT * FROM t1;
000365      }
000366    } {9 1 9 8}
000367    do_test lock-5.8 {
000368      catchsql {
000369        UPDATE t3 SET x=tx_exec('SELECT x FROM t2');
000370      }
000371    } {0 {}}
000372    do_test lock-5.9 {
000373      execsql {
000374        SELECT * FROM t3;
000375      }
000376    } {9}
000377  }
000378  
000379  do_test lock-6.1 {
000380    execsql {
000381      CREATE TABLE t4(a PRIMARY KEY, b);
000382      INSERT INTO t4 VALUES(1, 'one');
000383      INSERT INTO t4 VALUES(2, 'two');
000384      INSERT INTO t4 VALUES(3, 'three');
000385    }
000386  
000387    set STMT [sqlite3_prepare $DB "SELECT * FROM sqlite_master" -1 TAIL]
000388    sqlite3_step $STMT
000389  
000390    execsql { DELETE FROM t4 }
000391    execsql { SELECT * FROM sqlite_master } db2
000392    execsql { SELECT * FROM t4 } db2
000393  } {}
000394  
000395  do_test lock-6.2 {
000396    execsql { 
000397      BEGIN;
000398      INSERT INTO t4 VALUES(1, 'one');
000399      INSERT INTO t4 VALUES(2, 'two');
000400      INSERT INTO t4 VALUES(3, 'three');
000401      COMMIT;
000402    }
000403  
000404    execsql { SELECT * FROM t4 } db2
000405  } {1 one 2 two 3 three}
000406  
000407  do_test lock-6.3 {
000408    execsql { SELECT a FROM t4 ORDER BY a } db2
000409  } {1 2 3}
000410  
000411  do_test lock-6.4 {
000412    execsql { PRAGMA integrity_check } db2
000413  } {ok}
000414  
000415  do_test lock-6.5 {
000416    sqlite3_finalize $STMT
000417  } {SQLITE_OK}
000418  
000419  # At one point the following set of conditions would cause SQLite to 
000420  # retain a RESERVED or EXCLUSIVE lock after the transaction was committed:
000421  # 
000422  #   * The journal-mode is set to something other than 'delete', and
000423  #   * there exists one or more active read-only statements, and
000424  #   * a transaction that modified zero database pages is committed.
000425  # 
000426  #set temp_status unlocked
000427  #if {$TEMP_STORE>=2} {set temp_status unknown}
000428  set temp_status unknown
000429  do_test lock-7.1 {
000430    set STMT [sqlite3_prepare $DB "SELECT * FROM sqlite_master" -1 TAIL]
000431    sqlite3_step $STMT
000432  } {SQLITE_ROW}
000433  do_test lock-7.2 {
000434    execsql { PRAGMA lock_status }
000435  } [list main shared temp $temp_status]
000436  do_test lock-7.3 {
000437    execsql {
000438      PRAGMA journal_mode = truncate;
000439      BEGIN;
000440      UPDATE t4 SET a = 10 WHERE 0;
000441      COMMIT;
000442    }
000443    execsql { PRAGMA lock_status }
000444  } [list main shared temp $temp_status]
000445  do_test lock-7.4 {
000446    sqlite3_finalize $STMT
000447  } {SQLITE_OK}
000448  
000449  do_test lock-999.1 {
000450    rename db2 {}
000451  } {}
000452  
000453  finish_test