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 17 set testdir [file dirname $argv0] 18 source $testdir/tester.tcl 19 20 # Create an alternative connection to the database 21 # 22 do_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 } {} 29 do_test lock-1.1 { 30 execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name} 31 } {} 32 do_test lock-1.2 { 33 execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name} db2 34 } {} 35 do_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} 39 do_test lock-1.5 { 40 catchsql { 41 SELECT name FROM sqlite_master WHERE type='table' ORDER BY name 42 } db2 43 } {0 t1} 44 45 do_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}} 53 do_test lock-1.7.2 { 54 catchsql {SELECT * FROM t1} db2 55 } {0 {1 2}} 56 do_test lock-1.8 { 57 execsql {UPDATE t1 SET a=b, b=a} db2 58 execsql {SELECT * FROM t1} db2 59 } {2 1} 60 do_test lock-1.9 { 61 execsql {SELECT * FROM t1} 62 } {2 1} 63 do_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} 68 do_test lock-1.11 { 69 catchsql {SELECT * FROM t1} db2 70 } {0 {2 1}} 71 do_test lock-1.12 { 72 execsql {ROLLBACK} 73 catchsql {SELECT * FROM t1} 74 } {0 {2 1}} 75 76 do_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} 81 do_test lock-1.14.1 { 82 catchsql {SELECT * FROM t2} db2 83 } {0 {8 9}} 84 do_test lock-1.14.2 { 85 catchsql {SELECT * FROM t1} db2 86 } {0 {2 1}} 87 do_test lock-1.15 { 88 catchsql {SELECT * FROM t2} db2 89 } {0 {8 9}} 90 91 do_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} 97 do_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 # 121 do_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 {}} 128 do_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 # 136 do_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 # 148 if {$::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 } 157 integrity_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 # 163 do_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 # 174 do_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 # 182 do_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} 194 do_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} {}} 202 catch {execsql {ROLLBACK} db2} 203 do_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}} 215 do_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} {}} 228 catch {execsql {ROLLBACK} db2} 229 do_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} {}} 240 execsql {ROLLBACK} 241 242 # Test the built-in busy timeout handler 243 # 244 do_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}} 250 do_test lock-2.9 { 251 db2 timeout 0 252 execsql COMMIT 253 } {} 254 integrity_check lock-2.10 255 256 # Try to start two transactions in a row 257 # 258 do_test lock-3.1 { 259 execsql {BEGIN TRANSACTION} 260 set r [catch {execsql {BEGIN TRANSACTION}} msg] 261 execsql {ROLLBACK} 262 lappend r $msg 263 } {1 {cannot start a transaction within a transaction}} 264 integrity_check lock-3.2 265 266 # Make sure the busy handler and error messages work when 267 # opening a new pointer to the database while another pointer 268 # has the database locked. 269 # 270 do_test lock-4.1 { 271 db2 close 272 catch {db eval ROLLBACK} 273 db eval BEGIN 274 db eval {UPDATE t1 SET a=0 WHERE 0} 275 sqlite3 db2 ./test.db 276 catchsql {UPDATE t1 SET a=0} db2 277 } {1 {database is locked}} 278 do_test lock-4.2 { 279 set ::callback_value {} 280 set rc [catch {db2 eval {UPDATE t1 SET a=0}} msg] 281 lappend rc $msg $::callback_value 282 } {1 {database is locked} {}} 283 do_test lock-4.3 { 284 proc callback {count} { 285 lappend ::callback_value $count 286 if {$count>4} break 287 } 288 db2 busy callback 289 set rc [catch {db2 eval {UPDATE t1 SET a=0}} msg] 290 lappend rc $msg $::callback_value 291 } {1 {database is locked} {0 1 2 3 4 5}} 292 execsql {ROLLBACK} 293 294 # When one thread is writing, other threads cannot read. Except if the 295 # writing thread is writing to its temporary tables, the other threads 296 # can still read. -> Not so in 3.0. One thread can read while another 297 # holds a RESERVED lock. 298 # 299 proc tx_exec {sql} { 300 db2 eval $sql 301 } 302 do_test lock-5.1 { 303 execsql { 304 SELECT * FROM t1 305 } 306 } {2 1} 307 do_test lock-5.2 { 308 db function tx_exec tx_exec 309 catchsql { 310 INSERT INTO t1(a,b) SELECT 3, tx_exec('SELECT y FROM t2 LIMIT 1'); 311 } 312 } {0 {}} 313 314 ifcapable tempdb { 315 do_test lock-5.3 { 316 execsql { 317 CREATE TEMP TABLE t3(x); 318 SELECT * FROM t3; 319 } 320 } {} 321 do_test lock-5.4 { 322 catchsql { 323 INSERT INTO t3 SELECT tx_exec('SELECT y FROM t2 LIMIT 1'); 324 } 325 } {0 {}} 326 do_test lock-5.5 { 327 execsql { 328 SELECT * FROM t3; 329 } 330 } {8} 331 do_test lock-5.6 { 332 catchsql { 333 UPDATE t1 SET a=tx_exec('SELECT x FROM t2'); 334 } 335 } {0 {}} 336 do_test lock-5.7 { 337 execsql { 338 SELECT * FROM t1; 339 } 340 } {9 1 9 8} 341 do_test lock-5.8 { 342 catchsql { 343 UPDATE t3 SET x=tx_exec('SELECT x FROM t2'); 344 } 345 } {0 {}} 346 do_test lock-5.9 { 347 execsql { 348 SELECT * FROM t3; 349 } 350 } {9} 351 } 352 353 do_test lock-6.1 { 354 execsql { 355 CREATE TABLE t4(a PRIMARY KEY, b); 356 INSERT INTO t4 VALUES(1, 'one'); 357 INSERT INTO t4 VALUES(2, 'two'); 358 INSERT INTO t4 VALUES(3, 'three'); 359 } 360 361 set STMT [sqlite3_prepare $DB "SELECT * FROM sqlite_master" -1 TAIL] 362 sqlite3_step $STMT 363 364 execsql { DELETE FROM t4 } 365 execsql { SELECT * FROM sqlite_master } db2 366 execsql { SELECT * FROM t4 } db2 367 } {} 368 369 do_test lock-6.2 { 370 execsql { 371 BEGIN; 372 INSERT INTO t4 VALUES(1, 'one'); 373 INSERT INTO t4 VALUES(2, 'two'); 374 INSERT INTO t4 VALUES(3, 'three'); 375 COMMIT; 376 } 377 378 execsql { SELECT * FROM t4 } db2 379 } {1 one 2 two 3 three} 380 381 do_test lock-6.3 { 382 execsql { SELECT a FROM t4 ORDER BY a } db2 383 } {1 2 3} 384 385 do_test lock-6.4 { 386 execsql { PRAGMA integrity_check } db2 387 } {ok} 388 389 do_test lock-6.5 { 390 sqlite3_finalize $STMT 391 } {SQLITE_OK} 392 393 # At one point the following set of conditions would cause SQLite to 394 # retain a RESERVED or EXCLUSIVE lock after the transaction was committed: 395 # 396 # * The journal-mode is set to something other than 'delete', and 397 # * there exists one or more active read-only statements, and 398 # * a transaction that modified zero database pages is committed. 399 # 400 set temp_status unlocked 401 if {$TEMP_STORE>=2} {set temp_status unknown} 402 do_test lock-7.1 { 403 set STMT [sqlite3_prepare $DB "SELECT * FROM sqlite_master" -1 TAIL] 404 sqlite3_step $STMT 405 } {SQLITE_ROW} 406 do_test lock-7.2 { 407 execsql { PRAGMA lock_status } 408 } [list main shared temp $temp_status] 409 do_test lock-7.3 { 410 execsql { 411 PRAGMA journal_mode = truncate; 412 BEGIN; 413 UPDATE t4 SET a = 10 WHERE 0; 414 COMMIT; 415 } 416 execsql { PRAGMA lock_status } 417 } [list main shared temp $temp_status] 418 do_test lock-7.4 { 419 sqlite3_finalize $STMT 420 } {SQLITE_OK} 421 422 do_test lock-999.1 { 423 rename db2 {} 424 } {} 425 426 finish_test 427
