# 2001 September 15## 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 script is database locks.## $Id: lock.test,v 1.32 2005/03/29 03:11:00 danielk1977 Exp $set testdir [file dirname $argv0]source $testdir/tester.tcl# Create an alternative connection to the database#do_test lock-1.0 {  sqlite3 db2 ./test.db  set dummy {}} {}do_test lock-1.1 {  execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}} {}do_test lock-1.2 {  execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name} db2} {}do_test lock-1.3 {  execsql {CREATE TABLE t1(a int, b int)}  execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}} {t1}do_test lock-1.5 {  catchsql {     SELECT name FROM sqlite_master WHERE type='table' ORDER BY name  } db2} {0 t1}do_test lock-1.6 {  execsql {INSERT INTO t1 VALUES(1,2)}  execsql {SELECT * FROM t1}} {1 2}# Update: The schema is now brought up to date by test lock-1.5.# do_test lock-1.7.1 {#   catchsql {SELECT * FROM t1} db2# } {1 {no such table: t1}}do_test lock-1.7.2 {  catchsql {SELECT * FROM t1} db2} {0 {1 2}}do_test lock-1.8 {  execsql {UPDATE t1 SET a=b, b=a} db2  execsql {SELECT * FROM t1} db2} {2 1}do_test lock-1.9 {  execsql {SELECT * FROM t1}} {2 1}do_test lock-1.10 {  execsql {BEGIN TRANSACTION}  execsql {UPDATE t1 SET a = 0 WHERE 0}  execsql {SELECT * FROM t1}} {2 1}do_test lock-1.11 {  catchsql {SELECT * FROM t1} db2} {0 {2 1}}do_test lock-1.12 {  execsql {ROLLBACK}  catchsql {SELECT * FROM t1}} {0 {2 1}}do_test lock-1.13 {  execsql {CREATE TABLE t2(x int, y int)}  execsql {INSERT INTO t2 VALUES(8,9)}  execsql {SELECT * FROM t2}} {8 9}do_test lock-1.14.1 {  catchsql {SELECT * FROM t2} db2} {1 {no such table: t2}}do_test lock-1.14.2 {  catchsql {SELECT * FROM t1} db2} {0 {2 1}}do_test lock-1.15 {  catchsql {SELECT * FROM t2} db2} {0 {8 9}}do_test lock-1.16 {  db eval {SELECT * FROM t1} qv {    set x [db eval {SELECT * FROM t1}]  }  set x} {2 1}do_test lock-1.17 {  db eval {SELECT * FROM t1} qv {    set x [db eval {SELECT * FROM t2}]  }  set x} {8 9}# You cannot UPDATE a table from within the callback of a SELECT# on that same table because the SELECT has the table locked.do_test lock-1.18 {  db eval {SELECT * FROM t1} qv {    set r [catch {db eval {UPDATE t1 SET a=b, b=a}} msg]    lappend r $msg  }  set r} {1 {database table is locked}}# But you can UPDATE a different table from the one that is used in# the SELECT.#do_test lock-1.19 {  db eval {SELECT * FROM t1} qv {    set r [catch {db eval {UPDATE t2 SET x=y, y=x}} msg]    lappend r $msg  }  set r} {0 {}}do_test lock-1.20 {  execsql {SELECT * FROM t2}} {9 8}# It is possible to do a SELECT of the same table within the# callback of another SELECT on that same table because two# or more read-only cursors can be open at once.#do_test lock-1.21 {  db eval {SELECT * FROM t1} qv {    set r [catch {db eval {SELECT a FROM t1}} msg]    lappend r $msg  }  set r} {0 2}# Under UNIX you can do two SELECTs at once with different database# connections, because UNIX supports reader/writer locks.  Under windows,# this is not possible.#if {$::tcl_platform(platform)=="unix"} {  do_test lock-1.22 {    db eval {SELECT * FROM t1} qv {      set r [catch {db2 eval {SELECT a FROM t1}} msg]      lappend r $msg    }    set r  } {0 2}}integrity_check lock-1.23# If one thread has a transaction another thread cannot start# a transaction.  -> Not true in version 3.0.  But if one thread# as a RESERVED lock another thread cannot acquire one.#do_test lock-2.1 {  execsql {BEGIN TRANSACTION}  execsql {UPDATE t1 SET a = 0 WHERE 0}  execsql {BEGIN TRANSACTION} db2  set r [catch {execsql {UPDATE t1 SET a = 0 WHERE 0} db2} msg]  execsql {ROLLBACK} db2  lappend r $msg} {1 {database is locked}}# A thread can read when another has a RESERVED lock.#do_test lock-2.2 {  catchsql {SELECT * FROM t2} db2} {0 {9 8}}# If the other thread (the one that does not hold the transaction with# a RESERVED lock) tries to get a RESERVED lock, we do get a busy callback# as long as we were not orginally holding a READ lock.#do_test lock-2.3.1 {  proc callback {count} {    set ::callback_value $count    break  }  set ::callback_value {}  db2 busy callback  # db2 does not hold a lock so we should get a busy callback here  set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]  lappend r $msg  lappend r $::callback_value} {1 {database is locked} 0}do_test lock-2.3.2 {  set ::callback_value {}  execsql {BEGIN; SELECT rowid FROM sqlite_master LIMIT 1} db2  # This time db2 does hold a read lock.  No busy callback this time.  set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]  lappend r $msg  lappend r $::callback_value} {1 {database is locked} {}}catch {execsql {ROLLBACK} db2}do_test lock-2.4.1 {  proc callback {count} {    lappend ::callback_value $count    if {$count>4} break  }  set ::callback_value {}  db2 busy callback  # We get a busy callback because db2 is not holding a lock  set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]  lappend r $msg  lappend r $::callback_value} {1 {database is locked} {0 1 2 3 4 5}}do_test lock-2.4.2 {  proc callback {count} {    lappend ::callback_value $count    if {$count>4} break  }  set ::callback_value {}  db2 busy callback  execsql {BEGIN; SELECT rowid FROM sqlite_master LIMIT 1} db2  # No busy callback this time because we are holding a lock  set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]  lappend r $msg  lappend r $::callback_value} {1 {database is locked} {}}catch {execsql {ROLLBACK} db2}do_test lock-2.5 {  proc callback {count} {    lappend ::callback_value $count    if {$count>4} break  }  set ::callback_value {}  db2 busy callback  set r [catch {execsql {SELECT * FROM t1} db2} msg]  lappend r $msg  lappend r $::callback_value} {0 {2 1} {}}execsql {ROLLBACK}# Test the built-in busy timeout handler#do_test lock-2.8 {  db2 timeout 400  execsql BEGIN  execsql {UPDATE t1 SET a = 0 WHERE 0}  catchsql {BEGIN EXCLUSIVE;} db2} {1 {database is locked}}do_test lock-2.9 {  db2 timeout 0  execsql COMMIT} {}integrity_check lock-2.10# Try to start two transactions in a row#do_test lock-3.1 {  execsql {BEGIN TRANSACTION}  set r [catch {execsql {BEGIN TRANSACTION}} msg]  execsql {ROLLBACK}  lappend r $msg} {1 {cannot start a transaction within a transaction}}integrity_check lock-3.2# Make sure the busy handler and error messages work when# opening a new pointer to the database while another pointer# has the database locked.#do_test lock-4.1 {  db2 close  catch {db eval ROLLBACK}  db eval BEGIN  db eval {UPDATE t1 SET a=0 WHERE 0}  sqlite3 db2 ./test.db  catchsql {UPDATE t1 SET a=0} db2} {1 {database is locked}}do_test lock-4.2 {  set ::callback_value {}  set rc [catch {db2 eval {UPDATE t1 SET a=0}} msg]  lappend rc $msg $::callback_value} {1 {database is locked} {}}do_test lock-4.3 {  proc callback {count} {    lappend ::callback_value $count    if {$count>4} break  }  db2 busy callback  set rc [catch {db2 eval {UPDATE t1 SET a=0}} msg]  lappend rc $msg $::callback_value} {1 {database is locked} {0 1 2 3 4 5}}execsql {ROLLBACK}# When one thread is writing, other threads cannot read.  Except if the# writing thread is writing to its temporary tables, the other threads# can still read.  -> Not so in 3.0.  One thread can read while another# holds a RESERVED lock.#proc tx_exec {sql} {  db2 eval $sql}do_test lock-5.1 {  execsql {    SELECT * FROM t1  }} {2 1}do_test lock-5.2 {  db function tx_exec tx_exec  catchsql {    INSERT INTO t1(a,b) SELECT 3, tx_exec('SELECT y FROM t2 LIMIT 1');  }} {0 {}}ifcapable tempdb {  do_test lock-5.3 {    execsql {      CREATE TEMP TABLE t3(x);      SELECT * FROM t3;    }  } {}  do_test lock-5.4 {    catchsql {      INSERT INTO t3 SELECT tx_exec('SELECT y FROM t2 LIMIT 1');    }  } {0 {}}  do_test lock-5.5 {    execsql {      SELECT * FROM t3;    }  } {8}  do_test lock-5.6 {    catchsql {      UPDATE t1 SET a=tx_exec('SELECT x FROM t2');    }  } {0 {}}  do_test lock-5.7 {    execsql {      SELECT * FROM t1;    }  } {9 1 9 8}  do_test lock-5.8 {    catchsql {      UPDATE t3 SET x=tx_exec('SELECT x FROM t2');    }  } {0 {}}  do_test lock-5.9 {    execsql {      SELECT * FROM t3;    }  } {9}}do_test lock-999.1 {  rename db2 {}} {}finish_test