# 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.33 2006/08/16 16:42:48 drh 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.

 #

 # 2006-08-16:  Reads no longer block writes within the same

 # database connection.

 #

 #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