1# 2001 September 15 2# 3# The author disclaims copyright to this source code. In place of 4# a legal notice, here is a blessing: 5# 6# May you do good and not evil. 7# May you find forgiveness for yourself and forgive others. 8# May you share freely, never taking more than you give. 9# 10#*********************************************************************** 11# This file implements regression tests for SQLite library. The 12# focus of this script is database locks. 13# 14# $Id: lock.test,v 1.40 2009/06/16 17:49:36 drh Exp $ 15 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20# Create an alternative connection to the database 21# 22do_test lock-1.0 { 23 # Give a complex pathname to stress the path simplification logic in 24 # the vxworks driver and in test_async. 25 file mkdir tempdir/t1/t2 26 sqlite3 db2 ./tempdir/../tempdir/t1/.//t2/../../..//test.db 27 set dummy {} 28} {} 29do_test lock-1.1 { 30 execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name} 31} {} 32do_test lock-1.2 { 33 execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name} db2 34} {} 35do_test lock-1.3 { 36 execsql {CREATE TABLE t1(a int, b int)} 37 execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name} 38} {t1} 39do_test lock-1.5 { 40 catchsql { 41 SELECT name FROM sqlite_master WHERE type='table' ORDER BY name 42 } db2 43} {0 t1} 44 45do_test lock-1.6 { 46 execsql {INSERT INTO t1 VALUES(1,2)} 47 execsql {SELECT * FROM t1} 48} {1 2} 49# Update: The schema is now brought up to date by test lock-1.5. 50# do_test lock-1.7.1 { 51# catchsql {SELECT * FROM t1} db2 52# } {1 {no such table: t1}} 53do_test lock-1.7.2 { 54 catchsql {SELECT * FROM t1} db2 55} {0 {1 2}} 56do_test lock-1.8 { 57 execsql {UPDATE t1 SET a=b, b=a} db2 58 execsql {SELECT * FROM t1} db2 59} {2 1} 60do_test lock-1.9 { 61 execsql {SELECT * FROM t1} 62} {2 1} 63do_test lock-1.10 { 64 execsql {BEGIN TRANSACTION} 65 execsql {UPDATE t1 SET a = 0 WHERE 0} 66 execsql {SELECT * FROM t1} 67} {2 1} 68do_test lock-1.11 { 69 catchsql {SELECT * FROM t1} db2 70} {0 {2 1}} 71do_test lock-1.12 { 72 execsql {ROLLBACK} 73 catchsql {SELECT * FROM t1} 74} {0 {2 1}} 75 76do_test lock-1.13 { 77 execsql {CREATE TABLE t2(x int, y int)} 78 execsql {INSERT INTO t2 VALUES(8,9)} 79 execsql {SELECT * FROM t2} 80} {8 9} 81do_test lock-1.14.1 { 82 catchsql {SELECT * FROM t2} db2 83} {0 {8 9}} 84do_test lock-1.14.2 { 85 catchsql {SELECT * FROM t1} db2 86} {0 {2 1}} 87do_test lock-1.15 { 88 catchsql {SELECT * FROM t2} db2 89} {0 {8 9}} 90 91do_test lock-1.16 { 92 db eval {SELECT * FROM t1} qv { 93 set x [db eval {SELECT * FROM t1}] 94 } 95 set x 96} {2 1} 97do_test lock-1.17 { 98 db eval {SELECT * FROM t1} qv { 99 set x [db eval {SELECT * FROM t2}] 100 } 101 set x 102} {8 9} 103 104# You cannot UPDATE a table from within the callback of a SELECT 105# on that same table because the SELECT has the table locked. 106# 107# 2006-08-16: Reads no longer block writes within the same 108# database connection. 109# 110#do_test lock-1.18 { 111# db eval {SELECT * FROM t1} qv { 112# set r [catch {db eval {UPDATE t1 SET a=b, b=a}} msg] 113# lappend r $msg 114# } 115# set r 116#} {1 {database table is locked}} 117 118# But you can UPDATE a different table from the one that is used in 119# the SELECT. 120# 121do_test lock-1.19 { 122 db eval {SELECT * FROM t1} qv { 123 set r [catch {db eval {UPDATE t2 SET x=y, y=x}} msg] 124 lappend r $msg 125 } 126 set r 127} {0 {}} 128do_test lock-1.20 { 129 execsql {SELECT * FROM t2} 130} {9 8} 131 132# It is possible to do a SELECT of the same table within the 133# callback of another SELECT on that same table because two 134# or more read-only cursors can be open at once. 135# 136do_test lock-1.21 { 137 db eval {SELECT * FROM t1} qv { 138 set r [catch {db eval {SELECT a FROM t1}} msg] 139 lappend r $msg 140 } 141 set r 142} {0 2} 143 144# Under UNIX you can do two SELECTs at once with different database 145# connections, because UNIX supports reader/writer locks. Under windows, 146# this is not possible. 147# 148if {$::tcl_platform(platform)=="unix"} { 149 do_test lock-1.22 { 150 db eval {SELECT * FROM t1} qv { 151 set r [catch {db2 eval {SELECT a FROM t1}} msg] 152 lappend r $msg 153 } 154 set r 155 } {0 2} 156} 157integrity_check lock-1.23 158 159# If one thread has a transaction another thread cannot start 160# a transaction. -> Not true in version 3.0. But if one thread 161# as a RESERVED lock another thread cannot acquire one. 162# 163do_test lock-2.1 { 164 execsql {BEGIN TRANSACTION} 165 execsql {UPDATE t1 SET a = 0 WHERE 0} 166 execsql {BEGIN TRANSACTION} db2 167 set r [catch {execsql {UPDATE t1 SET a = 0 WHERE 0} db2} msg] 168 execsql {ROLLBACK} db2 169 lappend r $msg 170} {1 {database is locked}} 171 172# A thread can read when another has a RESERVED lock. 173# 174do_test lock-2.2 { 175 catchsql {SELECT * FROM t2} db2 176} {0 {9 8}} 177 178# If the other thread (the one that does not hold the transaction with 179# a RESERVED lock) tries to get a RESERVED lock, we do get a busy callback 180# as long as we were not orginally holding a READ lock. 181# 182do_test lock-2.3.1 { 183 proc callback {count} { 184 set ::callback_value $count 185 break 186 } 187 set ::callback_value {} 188 db2 busy callback 189 # db2 does not hold a lock so we should get a busy callback here 190 set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg] 191 lappend r $msg 192 lappend r $::callback_value 193} {1 {database is locked} 0} 194do_test lock-2.3.2 { 195 set ::callback_value {} 196 execsql {BEGIN; SELECT rowid FROM sqlite_master LIMIT 1} db2 197 # This time db2 does hold a read lock. No busy callback this time. 198 set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg] 199 lappend r $msg 200 lappend r $::callback_value 201} {1 {database is locked} {}} 202catch {execsql {ROLLBACK} db2} 203do_test lock-2.4.1 { 204 proc callback {count} { 205 lappend ::callback_value $count 206 if {$count>4} break 207 } 208 set ::callback_value {} 209 db2 busy callback 210 # We get a busy callback because db2 is not holding a lock 211 set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg] 212 lappend r $msg 213 lappend r $::callback_value 214} {1 {database is locked} {0 1 2 3 4 5}} 215do_test lock-2.4.2 { 216 proc callback {count} { 217 lappend ::callback_value $count 218 if {$count>4} break 219 } 220 set ::callback_value {} 221 db2 busy callback 222 execsql {BEGIN; SELECT rowid FROM sqlite_master LIMIT 1} db2 223 # No busy callback this time because we are holding a lock 224 set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg] 225 lappend r $msg 226 lappend r $::callback_value 227} {1 {database is locked} {}} 228catch {execsql {ROLLBACK} db2} 229do_test lock-2.5 { 230 proc callback {count} { 231 lappend ::callback_value $count 232 if {$count>4} break 233 } 234 set ::callback_value {} 235 db2 busy callback 236 set r [catch {execsql {SELECT * FROM t1} db2} msg] 237 lappend r $msg 238 lappend r $::callback_value 239} {0 {2 1} {}} 240execsql {ROLLBACK} 241 242# Test the built-in busy timeout handler 243# 244do_test lock-2.8 { 245 db2 timeout 400 246 execsql BEGIN 247 execsql {UPDATE t1 SET a = 0 WHERE 0} 248 catchsql {BEGIN EXCLUSIVE;} db2 249} {1 {database is locked}} 250do_test lock-2.8b { 251 db2 eval {PRAGMA busy_timeout} 252} {400} 253do_test lock-2.9 { 254 db2 timeout 0 255 execsql COMMIT 256} {} 257do_test lock-2.9b { 258 db2 eval {PRAGMA busy_timeout} 259} {0} 260integrity_check lock-2.10 261do_test lock-2.11 { 262 db2 eval {PRAGMA busy_timeout(400)} 263 execsql BEGIN 264 execsql {UPDATE t1 SET a = 0 WHERE 0} 265 catchsql {BEGIN EXCLUSIVE;} db2 266} {1 {database is locked}} 267do_test lock-2.11b { 268 db2 eval {PRAGMA busy_timeout} 269} {400} 270do_test lock-2.12 { 271 db2 eval {PRAGMA busy_timeout(0)} 272 execsql COMMIT 273} {} 274do_test lock-2.11b { 275 db2 eval {PRAGMA busy_timeout} 276} {0} 277integrity_check lock-2.12 278 279# Try to start two transactions in a row 280# 281do_test lock-3.1 { 282 execsql {BEGIN TRANSACTION} 283 set r [catch {execsql {BEGIN TRANSACTION}} msg] 284 execsql {ROLLBACK} 285 lappend r $msg 286} {1 {cannot start a transaction within a transaction}} 287integrity_check lock-3.2 288 289# Make sure the busy handler and error messages work when 290# opening a new pointer to the database while another pointer 291# has the database locked. 292# 293do_test lock-4.1 { 294 db2 close 295 catch {db eval ROLLBACK} 296 db eval BEGIN 297 db eval {UPDATE t1 SET a=0 WHERE 0} 298 sqlite3 db2 ./test.db 299 catchsql {UPDATE t1 SET a=0} db2 300} {1 {database is locked}} 301do_test lock-4.2 { 302 set ::callback_value {} 303 set rc [catch {db2 eval {UPDATE t1 SET a=0}} msg] 304 lappend rc $msg $::callback_value 305} {1 {database is locked} {}} 306do_test lock-4.3 { 307 proc callback {count} { 308 lappend ::callback_value $count 309 if {$count>4} break 310 } 311 db2 busy callback 312 set rc [catch {db2 eval {UPDATE t1 SET a=0}} msg] 313 lappend rc $msg $::callback_value 314} {1 {database is locked} {0 1 2 3 4 5}} 315execsql {ROLLBACK} 316 317# When one thread is writing, other threads cannot read. Except if the 318# writing thread is writing to its temporary tables, the other threads 319# can still read. -> Not so in 3.0. One thread can read while another 320# holds a RESERVED lock. 321# 322proc tx_exec {sql} { 323 db2 eval $sql 324} 325do_test lock-5.1 { 326 execsql { 327 SELECT * FROM t1 328 } 329} {2 1} 330do_test lock-5.2 { 331 db function tx_exec tx_exec 332 catchsql { 333 INSERT INTO t1(a,b) SELECT 3, tx_exec('SELECT y FROM t2 LIMIT 1'); 334 } 335} {0 {}} 336 337ifcapable tempdb { 338 do_test lock-5.3 { 339 execsql { 340 CREATE TEMP TABLE t3(x); 341 SELECT * FROM t3; 342 } 343 } {} 344 do_test lock-5.4 { 345 catchsql { 346 INSERT INTO t3 SELECT tx_exec('SELECT y FROM t2 LIMIT 1'); 347 } 348 } {0 {}} 349 do_test lock-5.5 { 350 execsql { 351 SELECT * FROM t3; 352 } 353 } {8} 354 do_test lock-5.6 { 355 catchsql { 356 UPDATE t1 SET a=tx_exec('SELECT x FROM t2'); 357 } 358 } {0 {}} 359 do_test lock-5.7 { 360 execsql { 361 SELECT * FROM t1; 362 } 363 } {9 1 9 8} 364 do_test lock-5.8 { 365 catchsql { 366 UPDATE t3 SET x=tx_exec('SELECT x FROM t2'); 367 } 368 } {0 {}} 369 do_test lock-5.9 { 370 execsql { 371 SELECT * FROM t3; 372 } 373 } {9} 374} 375 376do_test lock-6.1 { 377 execsql { 378 CREATE TABLE t4(a PRIMARY KEY, b); 379 INSERT INTO t4 VALUES(1, 'one'); 380 INSERT INTO t4 VALUES(2, 'two'); 381 INSERT INTO t4 VALUES(3, 'three'); 382 } 383 384 set STMT [sqlite3_prepare $DB "SELECT * FROM sqlite_master" -1 TAIL] 385 sqlite3_step $STMT 386 387 execsql { DELETE FROM t4 } 388 execsql { SELECT * FROM sqlite_master } db2 389 execsql { SELECT * FROM t4 } db2 390} {} 391 392do_test lock-6.2 { 393 execsql { 394 BEGIN; 395 INSERT INTO t4 VALUES(1, 'one'); 396 INSERT INTO t4 VALUES(2, 'two'); 397 INSERT INTO t4 VALUES(3, 'three'); 398 COMMIT; 399 } 400 401 execsql { SELECT * FROM t4 } db2 402} {1 one 2 two 3 three} 403 404do_test lock-6.3 { 405 execsql { SELECT a FROM t4 ORDER BY a } db2 406} {1 2 3} 407 408do_test lock-6.4 { 409 execsql { PRAGMA integrity_check } db2 410} {ok} 411 412do_test lock-6.5 { 413 sqlite3_finalize $STMT 414} {SQLITE_OK} 415 416# At one point the following set of conditions would cause SQLite to 417# retain a RESERVED or EXCLUSIVE lock after the transaction was committed: 418# 419# * The journal-mode is set to something other than 'delete', and 420# * there exists one or more active read-only statements, and 421# * a transaction that modified zero database pages is committed. 422# 423set temp_status unlocked 424if {$TEMP_STORE>=2} {set temp_status unknown} 425do_test lock-7.1 { 426 set STMT [sqlite3_prepare $DB "SELECT * FROM sqlite_master" -1 TAIL] 427 sqlite3_step $STMT 428} {SQLITE_ROW} 429do_test lock-7.2 { 430 execsql { PRAGMA lock_status } 431} [list main shared temp $temp_status] 432do_test lock-7.3 { 433 execsql { 434 PRAGMA journal_mode = truncate; 435 BEGIN; 436 UPDATE t4 SET a = 10 WHERE 0; 437 COMMIT; 438 } 439 execsql { PRAGMA lock_status } 440} [list main shared temp $temp_status] 441do_test lock-7.4 { 442 sqlite3_finalize $STMT 443} {SQLITE_OK} 444 445do_test lock-999.1 { 446 rename db2 {} 447} {} 448 449finish_test 450