gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/lock.test (about) 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 # EVIDENCE-OF: R-23579-05241 PRAGMA busy_timeout; PRAGMA busy_timeout = 245 # milliseconds; Query or change the setting of the busy timeout. 246 # 247 do_test lock-2.8 { 248 db2 timeout 400 249 execsql BEGIN 250 execsql {UPDATE t1 SET a = 0 WHERE 0} 251 catchsql {BEGIN EXCLUSIVE;} db2 252 } {1 {database is locked}} 253 do_test lock-2.8b { 254 db2 eval {PRAGMA busy_timeout} 255 } {400} 256 do_test lock-2.9 { 257 db2 timeout 0 258 execsql COMMIT 259 } {} 260 do_test lock-2.9b { 261 db2 eval {PRAGMA busy_timeout} 262 } {0} 263 integrity_check lock-2.10 264 do_test lock-2.11 { 265 db2 eval {PRAGMA busy_timeout(400)} 266 execsql BEGIN 267 execsql {UPDATE t1 SET a = 0 WHERE 0} 268 catchsql {BEGIN EXCLUSIVE;} db2 269 } {1 {database is locked}} 270 do_test lock-2.11b { 271 db2 eval {PRAGMA busy_timeout} 272 } {400} 273 do_test lock-2.12 { 274 db2 eval {PRAGMA busy_timeout(0)} 275 execsql COMMIT 276 } {} 277 do_test lock-2.12b { 278 db2 eval {PRAGMA busy_timeout} 279 } {0} 280 integrity_check lock-2.13 281 282 # Try to start two transactions in a row 283 # 284 do_test lock-3.1 { 285 execsql {BEGIN TRANSACTION} 286 set r [catch {execsql {BEGIN TRANSACTION}} msg] 287 execsql {ROLLBACK} 288 lappend r $msg 289 } {1 {cannot start a transaction within a transaction}} 290 integrity_check lock-3.2 291 292 # Make sure the busy handler and error messages work when 293 # opening a new pointer to the database while another pointer 294 # has the database locked. 295 # 296 do_test lock-4.1 { 297 db2 close 298 catch {db eval ROLLBACK} 299 db eval BEGIN 300 db eval {UPDATE t1 SET a=0 WHERE 0} 301 sqlite3 db2 ./test.db 302 catchsql {UPDATE t1 SET a=0} db2 303 } {1 {database is locked}} 304 do_test lock-4.2 { 305 set ::callback_value {} 306 set rc [catch {db2 eval {UPDATE t1 SET a=0}} msg] 307 lappend rc $msg $::callback_value 308 } {1 {database is locked} {}} 309 do_test lock-4.3 { 310 proc callback {count} { 311 lappend ::callback_value $count 312 if {$count>4} break 313 } 314 db2 busy callback 315 set rc [catch {db2 eval {UPDATE t1 SET a=0}} msg] 316 lappend rc $msg $::callback_value 317 } {1 {database is locked} {0 1 2 3 4 5}} 318 execsql {ROLLBACK} 319 320 # When one thread is writing, other threads cannot read. Except if the 321 # writing thread is writing to its temporary tables, the other threads 322 # can still read. -> Not so in 3.0. One thread can read while another 323 # holds a RESERVED lock. 324 # 325 proc tx_exec {sql} { 326 db2 eval $sql 327 } 328 do_test lock-5.1 { 329 execsql { 330 SELECT * FROM t1 331 } 332 } {2 1} 333 do_test lock-5.2 { 334 db function tx_exec tx_exec 335 catchsql { 336 INSERT INTO t1(a,b) SELECT 3, tx_exec('SELECT y FROM t2 LIMIT 1'); 337 } 338 } {0 {}} 339 340 ifcapable tempdb { 341 do_test lock-5.3 { 342 execsql { 343 CREATE TEMP TABLE t3(x); 344 SELECT * FROM t3; 345 } 346 } {} 347 do_test lock-5.4 { 348 catchsql { 349 INSERT INTO t3 SELECT tx_exec('SELECT y FROM t2 LIMIT 1'); 350 } 351 } {0 {}} 352 do_test lock-5.5 { 353 execsql { 354 SELECT * FROM t3; 355 } 356 } {8} 357 do_test lock-5.6 { 358 catchsql { 359 UPDATE t1 SET a=tx_exec('SELECT x FROM t2'); 360 } 361 } {0 {}} 362 do_test lock-5.7 { 363 execsql { 364 SELECT * FROM t1; 365 } 366 } {9 1 9 8} 367 do_test lock-5.8 { 368 catchsql { 369 UPDATE t3 SET x=tx_exec('SELECT x FROM t2'); 370 } 371 } {0 {}} 372 do_test lock-5.9 { 373 execsql { 374 SELECT * FROM t3; 375 } 376 } {9} 377 } 378 379 do_test lock-6.1 { 380 execsql { 381 CREATE TABLE t4(a PRIMARY KEY, b); 382 INSERT INTO t4 VALUES(1, 'one'); 383 INSERT INTO t4 VALUES(2, 'two'); 384 INSERT INTO t4 VALUES(3, 'three'); 385 } 386 387 set STMT [sqlite3_prepare $DB "SELECT * FROM sqlite_master" -1 TAIL] 388 sqlite3_step $STMT 389 390 execsql { DELETE FROM t4 } 391 execsql { SELECT * FROM sqlite_master } db2 392 execsql { SELECT * FROM t4 } db2 393 } {} 394 395 do_test lock-6.2 { 396 execsql { 397 BEGIN; 398 INSERT INTO t4 VALUES(1, 'one'); 399 INSERT INTO t4 VALUES(2, 'two'); 400 INSERT INTO t4 VALUES(3, 'three'); 401 COMMIT; 402 } 403 404 execsql { SELECT * FROM t4 } db2 405 } {1 one 2 two 3 three} 406 407 do_test lock-6.3 { 408 execsql { SELECT a FROM t4 ORDER BY a } db2 409 } {1 2 3} 410 411 do_test lock-6.4 { 412 execsql { PRAGMA integrity_check } db2 413 } {ok} 414 415 do_test lock-6.5 { 416 sqlite3_finalize $STMT 417 } {SQLITE_OK} 418 419 # At one point the following set of conditions would cause SQLite to 420 # retain a RESERVED or EXCLUSIVE lock after the transaction was committed: 421 # 422 # * The journal-mode is set to something other than 'delete', and 423 # * there exists one or more active read-only statements, and 424 # * a transaction that modified zero database pages is committed. 425 # 426 #set temp_status unlocked 427 #if {$TEMP_STORE>=2} {set temp_status unknown} 428 set temp_status unknown 429 do_test lock-7.1 { 430 set STMT [sqlite3_prepare $DB "SELECT * FROM sqlite_master" -1 TAIL] 431 sqlite3_step $STMT 432 } {SQLITE_ROW} 433 do_test lock-7.2 { 434 execsql { PRAGMA lock_status } 435 } [list main shared temp $temp_status] 436 do_test lock-7.3 { 437 execsql { 438 PRAGMA journal_mode = truncate; 439 BEGIN; 440 UPDATE t4 SET a = 10 WHERE 0; 441 COMMIT; 442 } 443 execsql { PRAGMA lock_status } 444 } [list main shared temp $temp_status] 445 do_test lock-7.4 { 446 sqlite3_finalize $STMT 447 } {SQLITE_OK} 448 449 do_test lock-999.1 { 450 rename db2 {} 451 } {} 452 453 finish_test