# 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.18 2002/09/14 13:47:33 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create an alternative connection to the database # do_test lock-1.0 { sqlite 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.4 { catchsql { SELECT name FROM sqlite_master WHERE type='table' ORDER BY name } db2 } {1 {database schema has changed}} 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} do_test lock-1.7 { execsql {SELECT * FROM t1} db2 } {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 {SELECT * FROM t1} } {2 1} do_test lock-1.11 { catchsql {SELECT * FROM t1} db2 } {1 {database is locked}} 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 { catchsql {SELECT * FROM t1} db2 } {1 {database schema has changed}} 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. # do_test lock-2.1 { execsql {BEGIN TRANSACTION} set r [catch {execsql {BEGIN TRANSACTION} db2} msg] lappend r $msg } {1 {database is locked}} # Nor can the other thread do a query. # do_test lock-2.2 { set r [catch {execsql {SELECT * FROM t2} db2} msg] lappend r $msg } {1 {database is locked}} # If the other thread (the one that does not hold the transaction) # tries to start a transaction, we get a busy callback. # do_test lock-2.3 { proc callback {args} { set ::callback_value $args break } set ::callback_value {} db2 busy callback set r [catch {execsql {BEGIN TRANSACTION} db2} msg] lappend r $msg lappend r $::callback_value } {1 {database is locked} {{} 1}} do_test lock-2.4 { proc callback {file count} { lappend ::callback_value $count if {$count>4} break } set ::callback_value {} db2 busy callback set r [catch {execsql {BEGIN TRANSACTION} db2} msg] lappend r $msg lappend r $::callback_value } {1 {database is locked} {1 2 3 4 5}} do_test lock-2.5 { proc callback {file 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 } {1 {database is locked} {1 2 3 4 5}} # In this test, the 3rd invocation of the busy callback causes # the first thread to release its transaction. That allows the # second thread to continue. # do_test lock-2.6 { proc callback {file count} { lappend ::callback_value $count if {$count>2} { execsql {ROLLBACK} } } set ::callback_value {} db2 busy callback set r [catch {execsql {SELECT * FROM t2} db2} msg] lappend r $msg lappend r $::callback_value } {0 {9 8} {1 2 3}} do_test lock-2.7 { execsql {BEGIN TRANSACTION} proc callback {file count} { lappend ::callback_value $count if {$count>2} { execsql {ROLLBACK} } } set ::callback_value {} db2 busy callback set r [catch {execsql {BEGIN TRANSACTION} db2} msg] execsql {ROLLBACK} db2 lappend r $msg lappend r $::callback_value } {0 {} {1 2 3}} integrity_check lock-2.8 # 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 sqlite db2 ./test.db set rc [catch {db2 eval {SELECT * FROM t1}} msg] lappend rc $msg } {1 {database is locked}} do_test lock-4.2 { set ::callback_value {} set rc [catch {db2 eval {SELECT * FROM t1}} msg] lappend rc $msg $::callback_value } {1 {database is locked} {}} do_test lock-4.3 { proc callback {file count} { lappend ::callback_value $count if {$count>4} break } db2 busy callback set rc [catch {db2 eval {SELECT * FROM t1}} msg] lappend rc $msg $::callback_value } {1 {database is locked} {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. # 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'); } } {1 {database is locked}} 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'); } } {1 {database is locked}} do_test lock-5.7 { execsql { SELECT * FROM t1; } } {2 1} 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