summaryrefslogtreecommitdiff
path: root/test/lock.test
diff options
context:
space:
mode:
Diffstat (limited to 'test/lock.test')
-rw-r--r--test/lock.test426
1 files changed, 426 insertions, 0 deletions
diff --git a/test/lock.test b/test/lock.test
new file mode 100644
index 0000000..22f359c
--- /dev/null
+++ b/test/lock.test
@@ -0,0 +1,426 @@
+# 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.40 2009/06/16 17:49:36 drh Exp $
+
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+# Create an alternative connection to the database
+#
+do_test lock-1.0 {
+ # Give a complex pathname to stress the path simplification logic in
+ # the vxworks driver and in test_async.
+ file mkdir tempdir/t1/t2
+ sqlite3 db2 ./tempdir/../tempdir/t1/.//t2/../../..//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
+} {0 {8 9}}
+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-6.1 {
+ execsql {
+ CREATE TABLE t4(a PRIMARY KEY, b);
+ INSERT INTO t4 VALUES(1, 'one');
+ INSERT INTO t4 VALUES(2, 'two');
+ INSERT INTO t4 VALUES(3, 'three');
+ }
+
+ set STMT [sqlite3_prepare $DB "SELECT * FROM sqlite_master" -1 TAIL]
+ sqlite3_step $STMT
+
+ execsql { DELETE FROM t4 }
+ execsql { SELECT * FROM sqlite_master } db2
+ execsql { SELECT * FROM t4 } db2
+} {}
+
+do_test lock-6.2 {
+ execsql {
+ BEGIN;
+ INSERT INTO t4 VALUES(1, 'one');
+ INSERT INTO t4 VALUES(2, 'two');
+ INSERT INTO t4 VALUES(3, 'three');
+ COMMIT;
+ }
+
+ execsql { SELECT * FROM t4 } db2
+} {1 one 2 two 3 three}
+
+do_test lock-6.3 {
+ execsql { SELECT a FROM t4 ORDER BY a } db2
+} {1 2 3}
+
+do_test lock-6.4 {
+ execsql { PRAGMA integrity_check } db2
+} {ok}
+
+do_test lock-6.5 {
+ sqlite3_finalize $STMT
+} {SQLITE_OK}
+
+# At one point the following set of conditions would cause SQLite to
+# retain a RESERVED or EXCLUSIVE lock after the transaction was committed:
+#
+# * The journal-mode is set to something other than 'delete', and
+# * there exists one or more active read-only statements, and
+# * a transaction that modified zero database pages is committed.
+#
+set temp_status unlocked
+if {$TEMP_STORE>=2} {set temp_status unknown}
+do_test lock-7.1 {
+ set STMT [sqlite3_prepare $DB "SELECT * FROM sqlite_master" -1 TAIL]
+ sqlite3_step $STMT
+} {SQLITE_ROW}
+do_test lock-7.2 {
+ execsql { PRAGMA lock_status }
+} [list main shared temp $temp_status]
+do_test lock-7.3 {
+ execsql {
+ PRAGMA journal_mode = truncate;
+ BEGIN;
+ UPDATE t4 SET a = 10 WHERE 0;
+ COMMIT;
+ }
+ execsql { PRAGMA lock_status }
+} [list main shared temp $temp_status]
+do_test lock-7.4 {
+ sqlite3_finalize $STMT
+} {SQLITE_OK}
+
+do_test lock-999.1 {
+ rename db2 {}
+} {}
+
+finish_test
generated by cgit v1.2.3 (git 2.25.1) at 2024-06-09 00:18:09 +0000