000001 # 2001 September 15
000002 #
000003 # The author disclaims copyright to this source code. In place of
000004 # a legal notice, here is a blessing:
000005 #
000006 # May you do good and not evil.
000007 # May you find forgiveness for yourself and forgive others.
000008 # May you share freely, never taking more than you give.
000009 #
000010 #***********************************************************************
000011 # This file implements regression tests for SQLite library. The
000012 # focus of this script is database locks.
000013 #
000014 # $Id: lock.test,v 1.40 2009/06/16 17:49:36 drh Exp $
000015
000016
000017 set testdir [file dirname $argv0]
000018 source $testdir/tester.tcl
000019
000020 # Create an alternative connection to the database
000021 #
000022 do_test lock-1.0 {
000023 # Give a complex pathname to stress the path simplification logic in
000024 # the vxworks driver and in test_async.
000025 file mkdir tempdir/t1/t2
000026 sqlite3 db2 ./tempdir/../tempdir/t1/.//t2/../../..//test.db
000027 set dummy {}
000028 } {}
000029 do_test lock-1.1 {
000030 execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}
000031 } {}
000032 do_test lock-1.2 {
000033 execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name} db2
000034 } {}
000035 do_test lock-1.3 {
000036 execsql {CREATE TABLE t1(a int, b int)}
000037 execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}
000038 } {t1}
000039 do_test lock-1.5 {
000040 catchsql {
000041 SELECT name FROM sqlite_master WHERE type='table' ORDER BY name
000042 } db2
000043 } {0 t1}
000044
000045 do_test lock-1.6 {
000046 execsql {INSERT INTO t1 VALUES(1,2)}
000047 execsql {SELECT * FROM t1}
000048 } {1 2}
000049 # Update: The schema is now brought up to date by test lock-1.5.
000050 # do_test lock-1.7.1 {
000051 # catchsql {SELECT * FROM t1} db2
000052 # } {1 {no such table: t1}}
000053 do_test lock-1.7.2 {
000054 catchsql {SELECT * FROM t1} db2
000055 } {0 {1 2}}
000056 do_test lock-1.8 {
000057 execsql {UPDATE t1 SET a=b, b=a} db2
000058 execsql {SELECT * FROM t1} db2
000059 } {2 1}
000060 do_test lock-1.9 {
000061 execsql {SELECT * FROM t1}
000062 } {2 1}
000063 do_test lock-1.10 {
000064 execsql {BEGIN TRANSACTION}
000065 execsql {UPDATE t1 SET a = 0 WHERE 0}
000066 execsql {SELECT * FROM t1}
000067 } {2 1}
000068 do_test lock-1.11 {
000069 catchsql {SELECT * FROM t1} db2
000070 } {0 {2 1}}
000071 do_test lock-1.12 {
000072 execsql {ROLLBACK}
000073 catchsql {SELECT * FROM t1}
000074 } {0 {2 1}}
000075
000076 do_test lock-1.13 {
000077 execsql {CREATE TABLE t2(x int, y int)}
000078 execsql {INSERT INTO t2 VALUES(8,9)}
000079 execsql {SELECT * FROM t2}
000080 } {8 9}
000081 do_test lock-1.14.1 {
000082 catchsql {SELECT * FROM t2} db2
000083 } {0 {8 9}}
000084 do_test lock-1.14.2 {
000085 catchsql {SELECT * FROM t1} db2
000086 } {0 {2 1}}
000087 do_test lock-1.15 {
000088 catchsql {SELECT * FROM t2} db2
000089 } {0 {8 9}}
000090
000091 do_test lock-1.16 {
000092 db eval {SELECT * FROM t1} qv {
000093 set x [db eval {SELECT * FROM t1}]
000094 }
000095 set x
000096 } {2 1}
000097 do_test lock-1.17 {
000098 db eval {SELECT * FROM t1} qv {
000099 set x [db eval {SELECT * FROM t2}]
000100 }
000101 set x
000102 } {8 9}
000103
000104 # You cannot UPDATE a table from within the callback of a SELECT
000105 # on that same table because the SELECT has the table locked.
000106 #
000107 # 2006-08-16: Reads no longer block writes within the same
000108 # database connection.
000109 #
000110 #do_test lock-1.18 {
000111 # db eval {SELECT * FROM t1} qv {
000112 # set r [catch {db eval {UPDATE t1 SET a=b, b=a}} msg]
000113 # lappend r $msg
000114 # }
000115 # set r
000116 #} {1 {database table is locked}}
000117
000118 # But you can UPDATE a different table from the one that is used in
000119 # the SELECT.
000120 #
000121 do_test lock-1.19 {
000122 db eval {SELECT * FROM t1} qv {
000123 set r [catch {db eval {UPDATE t2 SET x=y, y=x}} msg]
000124 lappend r $msg
000125 }
000126 set r
000127 } {0 {}}
000128 do_test lock-1.20 {
000129 execsql {SELECT * FROM t2}
000130 } {9 8}
000131
000132 # It is possible to do a SELECT of the same table within the
000133 # callback of another SELECT on that same table because two
000134 # or more read-only cursors can be open at once.
000135 #
000136 do_test lock-1.21 {
000137 db eval {SELECT * FROM t1} qv {
000138 set r [catch {db eval {SELECT a FROM t1}} msg]
000139 lappend r $msg
000140 }
000141 set r
000142 } {0 2}
000143
000144 # Under UNIX you can do two SELECTs at once with different database
000145 # connections, because UNIX supports reader/writer locks. Under windows,
000146 # this is not possible.
000147 #
000148 if {$::tcl_platform(platform)=="unix"} {
000149 do_test lock-1.22 {
000150 db eval {SELECT * FROM t1} qv {
000151 set r [catch {db2 eval {SELECT a FROM t1}} msg]
000152 lappend r $msg
000153 }
000154 set r
000155 } {0 2}
000156 }
000157 integrity_check lock-1.23
000158
000159 # If one thread has a transaction another thread cannot start
000160 # a transaction. -> Not true in version 3.0. But if one thread
000161 # as a RESERVED lock another thread cannot acquire one.
000162 #
000163 do_test lock-2.1 {
000164 execsql {BEGIN TRANSACTION}
000165 execsql {UPDATE t1 SET a = 0 WHERE 0}
000166 execsql {BEGIN TRANSACTION} db2
000167 set r [catch {execsql {UPDATE t1 SET a = 0 WHERE 0} db2} msg]
000168 execsql {ROLLBACK} db2
000169 lappend r $msg
000170 } {1 {database is locked}}
000171
000172 # A thread can read when another has a RESERVED lock.
000173 #
000174 do_test lock-2.2 {
000175 catchsql {SELECT * FROM t2} db2
000176 } {0 {9 8}}
000177
000178 # If the other thread (the one that does not hold the transaction with
000179 # a RESERVED lock) tries to get a RESERVED lock, we do get a busy callback
000180 # as long as we were not orginally holding a READ lock.
000181 #
000182 do_test lock-2.3.1 {
000183 proc callback {count} {
000184 set ::callback_value $count
000185 break
000186 }
000187 set ::callback_value {}
000188 db2 busy callback
000189 # db2 does not hold a lock so we should get a busy callback here
000190 set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]
000191 lappend r $msg
000192 lappend r $::callback_value
000193 } {1 {database is locked} 0}
000194 do_test lock-2.3.2 {
000195 set ::callback_value {}
000196 execsql {BEGIN; SELECT rowid FROM sqlite_master LIMIT 1} db2
000197 # This time db2 does hold a read lock. No busy callback this time.
000198 set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]
000199 lappend r $msg
000200 lappend r $::callback_value
000201 } {1 {database is locked} {}}
000202 catch {execsql {ROLLBACK} db2}
000203 do_test lock-2.4.1 {
000204 proc callback {count} {
000205 lappend ::callback_value $count
000206 if {$count>4} break
000207 }
000208 set ::callback_value {}
000209 db2 busy callback
000210 # We get a busy callback because db2 is not holding a lock
000211 set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]
000212 lappend r $msg
000213 lappend r $::callback_value
000214 } {1 {database is locked} {0 1 2 3 4 5}}
000215 do_test lock-2.4.2 {
000216 proc callback {count} {
000217 lappend ::callback_value $count
000218 if {$count>4} break
000219 }
000220 set ::callback_value {}
000221 db2 busy callback
000222 execsql {BEGIN; SELECT rowid FROM sqlite_master LIMIT 1} db2
000223 # No busy callback this time because we are holding a lock
000224 set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]
000225 lappend r $msg
000226 lappend r $::callback_value
000227 } {1 {database is locked} {}}
000228 catch {execsql {ROLLBACK} db2}
000229 do_test lock-2.5 {
000230 proc callback {count} {
000231 lappend ::callback_value $count
000232 if {$count>4} break
000233 }
000234 set ::callback_value {}
000235 db2 busy callback
000236 set r [catch {execsql {SELECT * FROM t1} db2} msg]
000237 lappend r $msg
000238 lappend r $::callback_value
000239 } {0 {2 1} {}}
000240 execsql {ROLLBACK}
000241
000242 # Test the built-in busy timeout handler
000243 #
000244 # EVIDENCE-OF: R-23579-05241 PRAGMA busy_timeout; PRAGMA busy_timeout =
000245 # milliseconds; Query or change the setting of the busy timeout.
000246 #
000247 do_test lock-2.8 {
000248 db2 timeout 400
000249 execsql BEGIN
000250 execsql {UPDATE t1 SET a = 0 WHERE 0}
000251 catchsql {BEGIN EXCLUSIVE;} db2
000252 } {1 {database is locked}}
000253 do_test lock-2.8b {
000254 db2 eval {PRAGMA busy_timeout}
000255 } {400}
000256 do_test lock-2.9 {
000257 db2 timeout 0
000258 execsql COMMIT
000259 } {}
000260 do_test lock-2.9b {
000261 db2 eval {PRAGMA busy_timeout}
000262 } {0}
000263 integrity_check lock-2.10
000264 do_test lock-2.11 {
000265 db2 eval {PRAGMA busy_timeout(400)}
000266 execsql BEGIN
000267 execsql {UPDATE t1 SET a = 0 WHERE 0}
000268 catchsql {BEGIN EXCLUSIVE;} db2
000269 } {1 {database is locked}}
000270 do_test lock-2.11b {
000271 db2 eval {PRAGMA busy_timeout}
000272 } {400}
000273 do_test lock-2.12 {
000274 db2 eval {PRAGMA busy_timeout(0)}
000275 execsql COMMIT
000276 } {}
000277 do_test lock-2.12b {
000278 db2 eval {PRAGMA busy_timeout}
000279 } {0}
000280 integrity_check lock-2.13
000281
000282 # Try to start two transactions in a row
000283 #
000284 do_test lock-3.1 {
000285 execsql {BEGIN TRANSACTION}
000286 set r [catch {execsql {BEGIN TRANSACTION}} msg]
000287 execsql {ROLLBACK}
000288 lappend r $msg
000289 } {1 {cannot start a transaction within a transaction}}
000290 integrity_check lock-3.2
000291
000292 # Make sure the busy handler and error messages work when
000293 # opening a new pointer to the database while another pointer
000294 # has the database locked.
000295 #
000296 do_test lock-4.1 {
000297 db2 close
000298 catch {db eval ROLLBACK}
000299 db eval BEGIN
000300 db eval {UPDATE t1 SET a=0 WHERE 0}
000301 sqlite3 db2 ./test.db
000302 catchsql {UPDATE t1 SET a=0} db2
000303 } {1 {database is locked}}
000304 do_test lock-4.2 {
000305 set ::callback_value {}
000306 set rc [catch {db2 eval {UPDATE t1 SET a=0}} msg]
000307 lappend rc $msg $::callback_value
000308 } {1 {database is locked} {}}
000309 do_test lock-4.3 {
000310 proc callback {count} {
000311 lappend ::callback_value $count
000312 if {$count>4} break
000313 }
000314 db2 busy callback
000315 set rc [catch {db2 eval {UPDATE t1 SET a=0}} msg]
000316 lappend rc $msg $::callback_value
000317 } {1 {database is locked} {0 1 2 3 4 5}}
000318 execsql {ROLLBACK}
000319
000320 # When one thread is writing, other threads cannot read. Except if the
000321 # writing thread is writing to its temporary tables, the other threads
000322 # can still read. -> Not so in 3.0. One thread can read while another
000323 # holds a RESERVED lock.
000324 #
000325 proc tx_exec {sql} {
000326 db2 eval $sql
000327 }
000328 do_test lock-5.1 {
000329 execsql {
000330 SELECT * FROM t1
000331 }
000332 } {2 1}
000333 do_test lock-5.2 {
000334 db function tx_exec tx_exec
000335 catchsql {
000336 INSERT INTO t1(a,b) SELECT 3, tx_exec('SELECT y FROM t2 LIMIT 1');
000337 }
000338 } {0 {}}
000339
000340 ifcapable tempdb {
000341 do_test lock-5.3 {
000342 execsql {
000343 CREATE TEMP TABLE t3(x);
000344 SELECT * FROM t3;
000345 }
000346 } {}
000347 do_test lock-5.4 {
000348 catchsql {
000349 INSERT INTO t3 SELECT tx_exec('SELECT y FROM t2 LIMIT 1');
000350 }
000351 } {0 {}}
000352 do_test lock-5.5 {
000353 execsql {
000354 SELECT * FROM t3;
000355 }
000356 } {8}
000357 do_test lock-5.6 {
000358 catchsql {
000359 UPDATE t1 SET a=tx_exec('SELECT x FROM t2');
000360 }
000361 } {0 {}}
000362 do_test lock-5.7 {
000363 execsql {
000364 SELECT * FROM t1;
000365 }
000366 } {9 1 9 8}
000367 do_test lock-5.8 {
000368 catchsql {
000369 UPDATE t3 SET x=tx_exec('SELECT x FROM t2');
000370 }
000371 } {0 {}}
000372 do_test lock-5.9 {
000373 execsql {
000374 SELECT * FROM t3;
000375 }
000376 } {9}
000377 }
000378
000379 do_test lock-6.1 {
000380 execsql {
000381 CREATE TABLE t4(a PRIMARY KEY, b);
000382 INSERT INTO t4 VALUES(1, 'one');
000383 INSERT INTO t4 VALUES(2, 'two');
000384 INSERT INTO t4 VALUES(3, 'three');
000385 }
000386
000387 set STMT [sqlite3_prepare $DB "SELECT * FROM sqlite_master" -1 TAIL]
000388 sqlite3_step $STMT
000389
000390 execsql { DELETE FROM t4 }
000391 execsql { SELECT * FROM sqlite_master } db2
000392 execsql { SELECT * FROM t4 } db2
000393 } {}
000394
000395 do_test lock-6.2 {
000396 execsql {
000397 BEGIN;
000398 INSERT INTO t4 VALUES(1, 'one');
000399 INSERT INTO t4 VALUES(2, 'two');
000400 INSERT INTO t4 VALUES(3, 'three');
000401 COMMIT;
000402 }
000403
000404 execsql { SELECT * FROM t4 } db2
000405 } {1 one 2 two 3 three}
000406
000407 do_test lock-6.3 {
000408 execsql { SELECT a FROM t4 ORDER BY a } db2
000409 } {1 2 3}
000410
000411 do_test lock-6.4 {
000412 execsql { PRAGMA integrity_check } db2
000413 } {ok}
000414
000415 do_test lock-6.5 {
000416 sqlite3_finalize $STMT
000417 } {SQLITE_OK}
000418
000419 # At one point the following set of conditions would cause SQLite to
000420 # retain a RESERVED or EXCLUSIVE lock after the transaction was committed:
000421 #
000422 # * The journal-mode is set to something other than 'delete', and
000423 # * there exists one or more active read-only statements, and
000424 # * a transaction that modified zero database pages is committed.
000425 #
000426 #set temp_status unlocked
000427 #if {$TEMP_STORE>=2} {set temp_status unknown}
000428 set temp_status unknown
000429 do_test lock-7.1 {
000430 set STMT [sqlite3_prepare $DB "SELECT * FROM sqlite_master" -1 TAIL]
000431 sqlite3_step $STMT
000432 } {SQLITE_ROW}
000433 do_test lock-7.2 {
000434 execsql { PRAGMA lock_status }
000435 } [list main shared temp $temp_status]
000436 do_test lock-7.3 {
000437 execsql {
000438 PRAGMA journal_mode = truncate;
000439 BEGIN;
000440 UPDATE t4 SET a = 10 WHERE 0;
000441 COMMIT;
000442 }
000443 execsql { PRAGMA lock_status }
000444 } [list main shared temp $temp_status]
000445 do_test lock-7.4 {
000446 sqlite3_finalize $STMT
000447 } {SQLITE_OK}
000448
000449 do_test lock-999.1 {
000450 rename db2 {}
000451 } {}
000452
000453 finish_test