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