1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/lock.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,354 @@
1.4 +# 2001 September 15
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library. The
1.15 +# focus of this script is database locks.
1.16 +#
1.17 +# $Id: lock.test,v 1.33 2006/08/16 16:42:48 drh Exp $
1.18 +
1.19 +
1.20 +set testdir [file dirname $argv0]
1.21 +source $testdir/tester.tcl
1.22 +
1.23 +# Create an alternative connection to the database
1.24 +#
1.25 +do_test lock-1.0 {
1.26 + sqlite3 db2 ./test.db
1.27 + set dummy {}
1.28 +} {}
1.29 +do_test lock-1.1 {
1.30 + execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}
1.31 +} {}
1.32 +do_test lock-1.2 {
1.33 + execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name} db2
1.34 +} {}
1.35 +do_test lock-1.3 {
1.36 + execsql {CREATE TABLE t1(a int, b int)}
1.37 + execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}
1.38 +} {t1}
1.39 +do_test lock-1.5 {
1.40 + catchsql {
1.41 + SELECT name FROM sqlite_master WHERE type='table' ORDER BY name
1.42 + } db2
1.43 +} {0 t1}
1.44 +
1.45 +do_test lock-1.6 {
1.46 + execsql {INSERT INTO t1 VALUES(1,2)}
1.47 + execsql {SELECT * FROM t1}
1.48 +} {1 2}
1.49 +# Update: The schema is now brought up to date by test lock-1.5.
1.50 +# do_test lock-1.7.1 {
1.51 +# catchsql {SELECT * FROM t1} db2
1.52 +# } {1 {no such table: t1}}
1.53 +do_test lock-1.7.2 {
1.54 + catchsql {SELECT * FROM t1} db2
1.55 +} {0 {1 2}}
1.56 +do_test lock-1.8 {
1.57 + execsql {UPDATE t1 SET a=b, b=a} db2
1.58 + execsql {SELECT * FROM t1} db2
1.59 +} {2 1}
1.60 +do_test lock-1.9 {
1.61 + execsql {SELECT * FROM t1}
1.62 +} {2 1}
1.63 +do_test lock-1.10 {
1.64 + execsql {BEGIN TRANSACTION}
1.65 + execsql {UPDATE t1 SET a = 0 WHERE 0}
1.66 + execsql {SELECT * FROM t1}
1.67 +} {2 1}
1.68 +do_test lock-1.11 {
1.69 + catchsql {SELECT * FROM t1} db2
1.70 +} {0 {2 1}}
1.71 +do_test lock-1.12 {
1.72 + execsql {ROLLBACK}
1.73 + catchsql {SELECT * FROM t1}
1.74 +} {0 {2 1}}
1.75 +
1.76 +do_test lock-1.13 {
1.77 + execsql {CREATE TABLE t2(x int, y int)}
1.78 + execsql {INSERT INTO t2 VALUES(8,9)}
1.79 + execsql {SELECT * FROM t2}
1.80 +} {8 9}
1.81 +do_test lock-1.14.1 {
1.82 + catchsql {SELECT * FROM t2} db2
1.83 +} {1 {no such table: t2}}
1.84 +do_test lock-1.14.2 {
1.85 + catchsql {SELECT * FROM t1} db2
1.86 +} {0 {2 1}}
1.87 +do_test lock-1.15 {
1.88 + catchsql {SELECT * FROM t2} db2
1.89 +} {0 {8 9}}
1.90 +
1.91 +do_test lock-1.16 {
1.92 + db eval {SELECT * FROM t1} qv {
1.93 + set x [db eval {SELECT * FROM t1}]
1.94 + }
1.95 + set x
1.96 +} {2 1}
1.97 +do_test lock-1.17 {
1.98 + db eval {SELECT * FROM t1} qv {
1.99 + set x [db eval {SELECT * FROM t2}]
1.100 + }
1.101 + set x
1.102 +} {8 9}
1.103 +
1.104 +# You cannot UPDATE a table from within the callback of a SELECT
1.105 +# on that same table because the SELECT has the table locked.
1.106 +#
1.107 +# 2006-08-16: Reads no longer block writes within the same
1.108 +# database connection.
1.109 +#
1.110 +#do_test lock-1.18 {
1.111 +# db eval {SELECT * FROM t1} qv {
1.112 +# set r [catch {db eval {UPDATE t1 SET a=b, b=a}} msg]
1.113 +# lappend r $msg
1.114 +# }
1.115 +# set r
1.116 +#} {1 {database table is locked}}
1.117 +
1.118 +# But you can UPDATE a different table from the one that is used in
1.119 +# the SELECT.
1.120 +#
1.121 +do_test lock-1.19 {
1.122 + db eval {SELECT * FROM t1} qv {
1.123 + set r [catch {db eval {UPDATE t2 SET x=y, y=x}} msg]
1.124 + lappend r $msg
1.125 + }
1.126 + set r
1.127 +} {0 {}}
1.128 +do_test lock-1.20 {
1.129 + execsql {SELECT * FROM t2}
1.130 +} {9 8}
1.131 +
1.132 +# It is possible to do a SELECT of the same table within the
1.133 +# callback of another SELECT on that same table because two
1.134 +# or more read-only cursors can be open at once.
1.135 +#
1.136 +do_test lock-1.21 {
1.137 + db eval {SELECT * FROM t1} qv {
1.138 + set r [catch {db eval {SELECT a FROM t1}} msg]
1.139 + lappend r $msg
1.140 + }
1.141 + set r
1.142 +} {0 2}
1.143 +
1.144 +# Under UNIX you can do two SELECTs at once with different database
1.145 +# connections, because UNIX supports reader/writer locks. Under windows,
1.146 +# this is not possible.
1.147 +#
1.148 +if {$::tcl_platform(platform)=="unix"} {
1.149 + do_test lock-1.22 {
1.150 + db eval {SELECT * FROM t1} qv {
1.151 + set r [catch {db2 eval {SELECT a FROM t1}} msg]
1.152 + lappend r $msg
1.153 + }
1.154 + set r
1.155 + } {0 2}
1.156 +}
1.157 +integrity_check lock-1.23
1.158 +
1.159 +# If one thread has a transaction another thread cannot start
1.160 +# a transaction. -> Not true in version 3.0. But if one thread
1.161 +# as a RESERVED lock another thread cannot acquire one.
1.162 +#
1.163 +do_test lock-2.1 {
1.164 + execsql {BEGIN TRANSACTION}
1.165 + execsql {UPDATE t1 SET a = 0 WHERE 0}
1.166 + execsql {BEGIN TRANSACTION} db2
1.167 + set r [catch {execsql {UPDATE t1 SET a = 0 WHERE 0} db2} msg]
1.168 + execsql {ROLLBACK} db2
1.169 + lappend r $msg
1.170 +} {1 {database is locked}}
1.171 +
1.172 +# A thread can read when another has a RESERVED lock.
1.173 +#
1.174 +do_test lock-2.2 {
1.175 + catchsql {SELECT * FROM t2} db2
1.176 +} {0 {9 8}}
1.177 +
1.178 +# If the other thread (the one that does not hold the transaction with
1.179 +# a RESERVED lock) tries to get a RESERVED lock, we do get a busy callback
1.180 +# as long as we were not orginally holding a READ lock.
1.181 +#
1.182 +do_test lock-2.3.1 {
1.183 + proc callback {count} {
1.184 + set ::callback_value $count
1.185 + break
1.186 + }
1.187 + set ::callback_value {}
1.188 + db2 busy callback
1.189 + # db2 does not hold a lock so we should get a busy callback here
1.190 + set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]
1.191 + lappend r $msg
1.192 + lappend r $::callback_value
1.193 +} {1 {database is locked} 0}
1.194 +do_test lock-2.3.2 {
1.195 + set ::callback_value {}
1.196 + execsql {BEGIN; SELECT rowid FROM sqlite_master LIMIT 1} db2
1.197 + # This time db2 does hold a read lock. No busy callback this time.
1.198 + set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]
1.199 + lappend r $msg
1.200 + lappend r $::callback_value
1.201 +} {1 {database is locked} {}}
1.202 +catch {execsql {ROLLBACK} db2}
1.203 +do_test lock-2.4.1 {
1.204 + proc callback {count} {
1.205 + lappend ::callback_value $count
1.206 + if {$count>4} break
1.207 + }
1.208 + set ::callback_value {}
1.209 + db2 busy callback
1.210 + # We get a busy callback because db2 is not holding a lock
1.211 + set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]
1.212 + lappend r $msg
1.213 + lappend r $::callback_value
1.214 +} {1 {database is locked} {0 1 2 3 4 5}}
1.215 +do_test lock-2.4.2 {
1.216 + proc callback {count} {
1.217 + lappend ::callback_value $count
1.218 + if {$count>4} break
1.219 + }
1.220 + set ::callback_value {}
1.221 + db2 busy callback
1.222 + execsql {BEGIN; SELECT rowid FROM sqlite_master LIMIT 1} db2
1.223 + # No busy callback this time because we are holding a lock
1.224 + set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]
1.225 + lappend r $msg
1.226 + lappend r $::callback_value
1.227 +} {1 {database is locked} {}}
1.228 +catch {execsql {ROLLBACK} db2}
1.229 +do_test lock-2.5 {
1.230 + proc callback {count} {
1.231 + lappend ::callback_value $count
1.232 + if {$count>4} break
1.233 + }
1.234 + set ::callback_value {}
1.235 + db2 busy callback
1.236 + set r [catch {execsql {SELECT * FROM t1} db2} msg]
1.237 + lappend r $msg
1.238 + lappend r $::callback_value
1.239 +} {0 {2 1} {}}
1.240 +execsql {ROLLBACK}
1.241 +
1.242 +# Test the built-in busy timeout handler
1.243 +#
1.244 +do_test lock-2.8 {
1.245 + db2 timeout 400
1.246 + execsql BEGIN
1.247 + execsql {UPDATE t1 SET a = 0 WHERE 0}
1.248 + catchsql {BEGIN EXCLUSIVE;} db2
1.249 +} {1 {database is locked}}
1.250 +do_test lock-2.9 {
1.251 + db2 timeout 0
1.252 + execsql COMMIT
1.253 +} {}
1.254 +integrity_check lock-2.10
1.255 +
1.256 +# Try to start two transactions in a row
1.257 +#
1.258 +do_test lock-3.1 {
1.259 + execsql {BEGIN TRANSACTION}
1.260 + set r [catch {execsql {BEGIN TRANSACTION}} msg]
1.261 + execsql {ROLLBACK}
1.262 + lappend r $msg
1.263 +} {1 {cannot start a transaction within a transaction}}
1.264 +integrity_check lock-3.2
1.265 +
1.266 +# Make sure the busy handler and error messages work when
1.267 +# opening a new pointer to the database while another pointer
1.268 +# has the database locked.
1.269 +#
1.270 +do_test lock-4.1 {
1.271 + db2 close
1.272 + catch {db eval ROLLBACK}
1.273 + db eval BEGIN
1.274 + db eval {UPDATE t1 SET a=0 WHERE 0}
1.275 + sqlite3 db2 ./test.db
1.276 + catchsql {UPDATE t1 SET a=0} db2
1.277 +} {1 {database is locked}}
1.278 +do_test lock-4.2 {
1.279 + set ::callback_value {}
1.280 + set rc [catch {db2 eval {UPDATE t1 SET a=0}} msg]
1.281 + lappend rc $msg $::callback_value
1.282 +} {1 {database is locked} {}}
1.283 +do_test lock-4.3 {
1.284 + proc callback {count} {
1.285 + lappend ::callback_value $count
1.286 + if {$count>4} break
1.287 + }
1.288 + db2 busy callback
1.289 + set rc [catch {db2 eval {UPDATE t1 SET a=0}} msg]
1.290 + lappend rc $msg $::callback_value
1.291 +} {1 {database is locked} {0 1 2 3 4 5}}
1.292 +execsql {ROLLBACK}
1.293 +
1.294 +# When one thread is writing, other threads cannot read. Except if the
1.295 +# writing thread is writing to its temporary tables, the other threads
1.296 +# can still read. -> Not so in 3.0. One thread can read while another
1.297 +# holds a RESERVED lock.
1.298 +#
1.299 +proc tx_exec {sql} {
1.300 + db2 eval $sql
1.301 +}
1.302 +do_test lock-5.1 {
1.303 + execsql {
1.304 + SELECT * FROM t1
1.305 + }
1.306 +} {2 1}
1.307 +do_test lock-5.2 {
1.308 + db function tx_exec tx_exec
1.309 + catchsql {
1.310 + INSERT INTO t1(a,b) SELECT 3, tx_exec('SELECT y FROM t2 LIMIT 1');
1.311 + }
1.312 +} {0 {}}
1.313 +
1.314 +ifcapable tempdb {
1.315 + do_test lock-5.3 {
1.316 + execsql {
1.317 + CREATE TEMP TABLE t3(x);
1.318 + SELECT * FROM t3;
1.319 + }
1.320 + } {}
1.321 + do_test lock-5.4 {
1.322 + catchsql {
1.323 + INSERT INTO t3 SELECT tx_exec('SELECT y FROM t2 LIMIT 1');
1.324 + }
1.325 + } {0 {}}
1.326 + do_test lock-5.5 {
1.327 + execsql {
1.328 + SELECT * FROM t3;
1.329 + }
1.330 + } {8}
1.331 + do_test lock-5.6 {
1.332 + catchsql {
1.333 + UPDATE t1 SET a=tx_exec('SELECT x FROM t2');
1.334 + }
1.335 + } {0 {}}
1.336 + do_test lock-5.7 {
1.337 + execsql {
1.338 + SELECT * FROM t1;
1.339 + }
1.340 + } {9 1 9 8}
1.341 + do_test lock-5.8 {
1.342 + catchsql {
1.343 + UPDATE t3 SET x=tx_exec('SELECT x FROM t2');
1.344 + }
1.345 + } {0 {}}
1.346 + do_test lock-5.9 {
1.347 + execsql {
1.348 + SELECT * FROM t3;
1.349 + }
1.350 + } {9}
1.351 +}
1.352 +
1.353 +do_test lock-999.1 {
1.354 + rename db2 {}
1.355 +} {}
1.356 +
1.357 +finish_test