# 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