# 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.13 2001/10/09 04:19:47 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 } {} 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} } # 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}} # 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 } {0 {}} do_test lock-999.1 { rename db2 {} } {} finish_test