1# 2007 March 24 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 focus 12# of these tests is exclusive access mode (i.e. the thing activated by 13# "PRAGMA locking_mode = EXCLUSIVE"). 14# 15# $Id: exclusive.test,v 1.15 2009/06/26 12:30:40 danielk1977 Exp $ 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20ifcapable {!pager_pragmas} { 21 finish_test 22 return 23} 24 25forcedelete test2.db-journal 26forcedelete test2.db 27forcedelete test3.db-journal 28forcedelete test3.db 29forcedelete test4.db-journal 30forcedelete test4.db 31 32#---------------------------------------------------------------------- 33# Test cases exclusive-1.X test the PRAGMA logic. 34# 35do_test exclusive-1.0 { 36 execsql { 37 pragma locking_mode; 38 pragma main.locking_mode; 39 pragma temp.locking_mode; 40 } 41} [list normal normal exclusive] 42do_test exclusive-1.1 { 43 execsql { 44 pragma locking_mode = exclusive; 45 } 46} {exclusive} 47do_test exclusive-1.2 { 48 execsql { 49 pragma locking_mode; 50 pragma main.locking_mode; 51 pragma temp.locking_mode; 52 } 53} [list exclusive exclusive exclusive] 54do_test exclusive-1.3 { 55 execsql { 56 pragma locking_mode = normal; 57 } 58} {normal} 59do_test exclusive-1.4 { 60 execsql { 61 pragma locking_mode; 62 pragma main.locking_mode; 63 pragma temp.locking_mode; 64 } 65} [list normal normal exclusive] 66do_test exclusive-1.5 { 67 execsql { 68 pragma locking_mode = invalid; 69 } 70} {normal} 71do_test exclusive-1.6 { 72 execsql { 73 pragma locking_mode; 74 pragma main.locking_mode; 75 pragma temp.locking_mode; 76 } 77} [list normal normal exclusive] 78ifcapable attach { 79 do_test exclusive-1.7 { 80 execsql { 81 pragma locking_mode = exclusive; 82 ATTACH 'test2.db' as aux; 83 } 84 execsql { 85 pragma main.locking_mode; 86 pragma aux.locking_mode; 87 } 88 } {exclusive exclusive} 89 do_test exclusive-1.8 { 90 execsql { 91 pragma main.locking_mode = normal; 92 } 93 execsql { 94 pragma main.locking_mode; 95 pragma temp.locking_mode; 96 pragma aux.locking_mode; 97 } 98 } [list normal exclusive exclusive] 99 do_test exclusive-1.9 { 100 execsql { 101 pragma locking_mode; 102 } 103 } {exclusive} 104 do_test exclusive-1.10 { 105 execsql { 106 ATTACH 'test3.db' as aux2; 107 } 108 execsql { 109 pragma main.locking_mode; 110 pragma aux.locking_mode; 111 pragma aux2.locking_mode; 112 } 113 } {normal exclusive exclusive} 114 do_test exclusive-1.11 { 115 execsql { 116 pragma aux.locking_mode = normal; 117 } 118 execsql { 119 pragma main.locking_mode; 120 pragma aux.locking_mode; 121 pragma aux2.locking_mode; 122 } 123 } {normal normal exclusive} 124 do_test exclusive-1.12 { 125 execsql { 126 pragma locking_mode = normal; 127 } 128 execsql { 129 pragma main.locking_mode; 130 pragma temp.locking_mode; 131 pragma aux.locking_mode; 132 pragma aux2.locking_mode; 133 } 134 } [list normal exclusive normal normal] 135 do_test exclusive-1.13 { 136 execsql { 137 ATTACH 'test4.db' as aux3; 138 } 139 execsql { 140 pragma main.locking_mode; 141 pragma temp.locking_mode; 142 pragma aux.locking_mode; 143 pragma aux2.locking_mode; 144 pragma aux3.locking_mode; 145 } 146 } [list normal exclusive normal normal normal] 147 148 do_test exclusive-1.99 { 149 execsql { 150 DETACH aux; 151 DETACH aux2; 152 DETACH aux3; 153 } 154 } {} 155} 156 157#---------------------------------------------------------------------- 158# Test cases exclusive-2.X verify that connections in exclusive 159# locking_mode do not relinquish locks. 160# 161do_test exclusive-2.0 { 162 execsql { 163 CREATE TABLE abc(a, b, c); 164 INSERT INTO abc VALUES(1, 2, 3); 165 PRAGMA locking_mode = exclusive; 166 } 167} {exclusive} 168do_test exclusive-2.1 { 169 sqlite3 db2 test.db 170 execsql { 171 INSERT INTO abc VALUES(4, 5, 6); 172 SELECT * FROM abc; 173 } db2 174} {1 2 3 4 5 6} 175do_test exclusive-2.2 { 176 # This causes connection 'db' (in exclusive mode) to establish 177 # a shared-lock on the db. The other connection should now be 178 # locked out as a writer. 179 execsql { 180 SELECT * FROM abc; 181 } db 182} {1 2 3 4 5 6} 183do_test exclusive-2.4 { 184 execsql { 185 SELECT * FROM abc; 186 } db2 187} {1 2 3 4 5 6} 188do_test exclusive-2.5 { 189 catchsql { 190 INSERT INTO abc VALUES(7, 8, 9); 191 } db2 192} {1 {database is locked}} 193sqlite3_soft_heap_limit 0 194do_test exclusive-2.6 { 195 # Because connection 'db' only has a shared-lock, the other connection 196 # will be able to get a RESERVED, but will fail to upgrade to EXCLUSIVE. 197 execsql { 198 BEGIN; 199 INSERT INTO abc VALUES(7, 8, 9); 200 } db2 201 catchsql { 202 COMMIT 203 } db2 204} {1 {database is locked}} 205do_test exclusive-2.7 { 206 catchsql { 207 COMMIT 208 } db2 209} {1 {database is locked}} 210do_test exclusive-2.8 { 211 execsql { 212 ROLLBACK; 213 } db2 214} {} 215sqlite3_soft_heap_limit $cmdlinearg(soft-heap-limit) 216 217do_test exclusive-2.9 { 218 # Write the database to establish the exclusive lock with connection 'db. 219 execsql { 220 INSERT INTO abc VALUES(7, 8, 9); 221 } db 222 catchsql { 223 SELECT * FROM abc; 224 } db2 225} {1 {database is locked}} 226do_test exclusive-2.10 { 227 # Changing the locking-mode does not release any locks. 228 execsql { 229 PRAGMA locking_mode = normal; 230 } db 231 catchsql { 232 SELECT * FROM abc; 233 } db2 234} {1 {database is locked}} 235do_test exclusive-2.11 { 236 # After changing the locking mode, accessing the db releases locks. 237 execsql { 238 SELECT * FROM abc; 239 } db 240 execsql { 241 SELECT * FROM abc; 242 } db2 243} {1 2 3 4 5 6 7 8 9} 244db2 close 245 246#---------------------------------------------------------------------- 247# Tests exclusive-3.X - test that a connection in exclusive mode 248# truncates instead of deletes the journal file when committing 249# a transaction. 250# 251# These tests are not run on windows because the windows backend 252# opens the journal file for exclusive access, preventing its contents 253# from being inspected externally. 254# 255if {$tcl_platform(platform) != "windows" 256 && [atomic_batch_write test.db]==0 257} { 258 259 # Return a list of two booleans (either 0 or 1). The first is true 260 # if the named file exists. The second is true only if the file 261 # exists and the first 28 bytes contain at least one non-zero byte. 262 # 263 proc filestate {fname} { 264 set exists 0 265 set content 0 266 if {[file exists $fname]} { 267 set exists 1 268 set hdr [hexio_read $fname 0 28] 269 set content [expr {0==[string match $hdr [string repeat 0 56]]}] 270 } 271 list $exists $content 272 } 273 274 do_test exclusive-3.0 { 275 filestate test.db-journal 276 } {0 0} 277 do_test exclusive-3.1 { 278 execsql { 279 PRAGMA locking_mode = exclusive; 280 BEGIN; 281 DELETE FROM abc; 282 } 283 filestate test.db-journal 284 } {1 1} 285 do_test exclusive-3.2 { 286 execsql { 287 COMMIT; 288 } 289 filestate test.db-journal 290 } {1 0} 291 do_test exclusive-3.3 { 292 execsql { 293 INSERT INTO abc VALUES('A', 'B', 'C'); 294 SELECT * FROM abc; 295 } 296 } {A B C} 297 do_test exclusive-3.4 { 298 execsql { 299 BEGIN; 300 UPDATE abc SET a = 1, b = 2, c = 3; 301 ROLLBACK; 302 SELECT * FROM abc; 303 } 304 } {A B C} 305 do_test exclusive-3.5 { 306 filestate test.db-journal 307 } {1 0} 308 do_test exclusive-3.6 { 309 execsql { 310 PRAGMA locking_mode = normal; 311 SELECT * FROM abc; 312 } 313 filestate test.db-journal 314 } {0 0} 315} 316 317#---------------------------------------------------------------------- 318# Tests exclusive-4.X - test that rollback works correctly when 319# in exclusive-access mode. 320# 321 322# The following procedure computes a "signature" for table "t3". If 323# T3 changes in any way, the signature should change. 324# 325# This is used to test ROLLBACK. We gather a signature for t3, then 326# make lots of changes to t3, then rollback and take another signature. 327# The two signatures should be the same. 328# 329proc signature {} { 330 return [db eval {SELECT count(*), md5sum(x) FROM t3}] 331} 332 333do_test exclusive-4.0 { 334 execsql { PRAGMA locking_mode = exclusive; } 335 execsql { PRAGMA default_cache_size = 10; } 336 execsql { 337 BEGIN; 338 CREATE TABLE t3(x TEXT); 339 INSERT INTO t3 VALUES(randstr(10,400)); 340 INSERT INTO t3 VALUES(randstr(10,400)); 341 INSERT INTO t3 SELECT randstr(10,400) FROM t3; 342 INSERT INTO t3 SELECT randstr(10,400) FROM t3; 343 INSERT INTO t3 SELECT randstr(10,400) FROM t3; 344 INSERT INTO t3 SELECT randstr(10,400) FROM t3; 345 COMMIT; 346 } 347 execsql {SELECT count(*) FROM t3;} 348} {32} 349 350set ::X [signature] 351do_test exclusive-4.1 { 352 execsql { 353 BEGIN; 354 DELETE FROM t3 WHERE random()%10!=0; 355 INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; 356 INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; 357 SELECT count(*) FROM t3; 358 ROLLBACK; 359 } 360 signature 361} $::X 362 363do_test exclusive-4.2 { 364 execsql { 365 BEGIN; 366 DELETE FROM t3 WHERE random()%10!=0; 367 INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; 368 DELETE FROM t3 WHERE random()%10!=0; 369 INSERT INTO t3 SELECT randstr(10,10)||x FROM t3; 370 ROLLBACK; 371 } 372 signature 373} $::X 374 375do_test exclusive-4.3 { 376 execsql { 377 INSERT INTO t3 SELECT randstr(10,400) FROM t3 WHERE random()%10==0; 378 } 379} {} 380 381do_test exclusive-4.4 { 382 catch {set ::X [signature]} 383} {0} 384do_test exclusive-4.5 { 385 execsql { 386 PRAGMA locking_mode = NORMAL; 387 DROP TABLE t3; 388 DROP TABLE abc; 389 } 390} {normal} 391 392#---------------------------------------------------------------------- 393# Tests exclusive-5.X - test that statement journals are truncated 394# instead of deleted when in exclusive access mode. 395# 396if {[atomic_batch_write test.db]==0} { 397 398# Close and reopen the database so that the temp database is no 399# longer active. 400# 401db close 402sqlite3 db test.db 403 404# if we're using proxy locks, we use 3 filedescriptors for a db 405# that is open but NOT writing changes, normally 406# sqlite uses 1 (proxy locking adds the conch and the local lock) 407set using_proxy 0 408foreach {name value} [array get env SQLITE_FORCE_PROXY_LOCKING] { 409 set using_proxy $value 410} 411set extrafds 0 412if {$using_proxy!=0} { 413 set extrafds 2 414} 415 416do_test exclusive-5.0 { 417 execsql { 418 CREATE TABLE abc(a UNIQUE, b UNIQUE, c UNIQUE); 419 BEGIN; 420 INSERT INTO abc VALUES(1, 2, 3); 421 INSERT INTO abc SELECT a+1, b+1, c+1 FROM abc; 422 } 423} {} 424do_test exclusive-5.1 { 425 # Three files are open: The db, journal and statement-journal. 426 # (2016-03-04) The statement-journal is now opened lazily 427 set sqlite_open_file_count 428 expr $sqlite_open_file_count-$extrafds 429} {2} 430do_test exclusive-5.2 { 431 execsql { 432 COMMIT; 433 } 434 # One file open: the db. 435 set sqlite_open_file_count 436 expr $sqlite_open_file_count-$extrafds 437} {1} 438do_test exclusive-5.3 { 439 execsql { 440 PRAGMA locking_mode = exclusive; 441 BEGIN; 442 INSERT INTO abc VALUES(5, 6, 7); 443 } 444 # Two files open: the db and journal. 445 set sqlite_open_file_count 446 expr $sqlite_open_file_count-$extrafds 447} {2} 448do_test exclusive-5.4 { 449 execsql { 450 INSERT INTO abc SELECT a+10, b+10, c+10 FROM abc; 451 } 452 # Three files are open: The db, journal and statement-journal. 453 # 2016-03-04: The statement-journal open is deferred 454 set sqlite_open_file_count 455 expr $sqlite_open_file_count-$extrafds 456} {2} 457do_test exclusive-5.5 { 458 execsql { 459 COMMIT; 460 } 461 # Three files are still open: The db, journal and statement-journal. 462 # 2016-03-04: The statement-journal open is deferred 463 set sqlite_open_file_count 464 expr $sqlite_open_file_count-$extrafds 465} {2} 466do_test exclusive-5.6 { 467 execsql { 468 PRAGMA locking_mode = normal; 469 SELECT * FROM abc; 470 } 471} {normal 1 2 3 2 3 4 5 6 7 11 12 13 12 13 14 15 16 17} 472do_test exclusive-5.7 { 473 # Just the db open. 474 set sqlite_open_file_count 475 expr $sqlite_open_file_count-$extrafds 476} {1} 477 478#------------------------------------------------------------------------- 479 480do_execsql_test exclusive-6.1 { 481 CREATE TABLE t4(a, b); 482 INSERT INTO t4 VALUES('Eden', 1955); 483 BEGIN; 484 INSERT INTO t4 VALUES('Macmillan', 1957); 485 INSERT INTO t4 VALUES('Douglas-Home', 1963); 486 INSERT INTO t4 VALUES('Wilson', 1964); 487} 488do_test exclusive-6.2 { 489 forcedelete test2.db test2.db-journal 490 copy_file test.db test2.db 491 copy_file test.db-journal test2.db-journal 492 sqlite3 db test2.db 493} {} 494 495do_execsql_test exclusive-6.3 { 496 PRAGMA locking_mode = EXCLUSIVE; 497 SELECT * FROM t4; 498} {exclusive Eden 1955} 499 500do_test exclusive-6.4 { 501 db close 502 forcedelete test.db test.db-journal 503 set fd [open test.db-journal w] 504 puts $fd x 505 close $fd 506 sqlite3 db test.db 507} {} 508 509do_execsql_test exclusive-6.5 { 510 PRAGMA locking_mode = EXCLUSIVE; 511 SELECT * FROM sqlite_master; 512} {exclusive} 513 514# 2019-12-26 ticket fb3b3024ea238d5c 515if {[permutation]!="journaltest"} { 516 # The custom VFS used by the "journaltest" permutation cannot open the 517 # shared-memory file. So, while it is able to switch the db file to 518 # journal_mode=WAL when locking_mode=EXCLUSIVE, it can no longer access 519 # it once the locking_mode is changed back to NORMAL. 520 do_test exclusive-7.1 { 521 db close 522 forcedelete test.db test.db-journal test.db-wal 523 sqlite3 db test.db 524 # The following sequence of pragmas would trigger an assert() 525 # associated with Pager.changeCountDone inside of assert_pager_state(), 526 # prior to the fix. 527 db eval { 528 PRAGMA locking_mode = EXCLUSIVE; 529 PRAGMA journal_mode = WAL; 530 PRAGMA locking_mode = NORMAL; 531 PRAGMA user_version; 532 PRAGMA journal_mode = DELETE; 533 } 534 } {exclusive wal normal 0 delete} 535} 536 537 538} ;# atomic_batch_write==0 539 540finish_test 541