AryaWu/sqlite
0
1# 2007 March 242#3# The author disclaims copyright to this source code. In place of4# 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 focus12# 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.tcl19 20ifcapable {!pager_pragmas} {21 finish_test22 return23}24 25forcedelete test2.db-journal26forcedelete test2.db27forcedelete test3.db-journal28forcedelete test3.db29forcedelete test4.db-journal30forcedelete test4.db31 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.db170 execsql {171 INSERT INTO abc VALUES(4, 5, 6);172 SELECT * FROM abc;173 } db2174} {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 be178 # locked out as a writer.179 execsql {180 SELECT * FROM abc;181 } db182} {1 2 3 4 5 6}183do_test exclusive-2.4 {184 execsql {185 SELECT * FROM abc;186 } db2187} {1 2 3 4 5 6}188do_test exclusive-2.5 {189 catchsql {190 INSERT INTO abc VALUES(7, 8, 9);191 } db2192} {1 {database is locked}}193sqlite3_soft_heap_limit 0194do_test exclusive-2.6 {195 # Because connection 'db' only has a shared-lock, the other connection196 # 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 } db2201 catchsql {202 COMMIT203 } db2204} {1 {database is locked}}205do_test exclusive-2.7 {206 catchsql {207 COMMIT208 } db2209} {1 {database is locked}}210do_test exclusive-2.8 {211 execsql {212 ROLLBACK;213 } db2214} {}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 } db222 catchsql {223 SELECT * FROM abc;224 } db2225} {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 } db231 catchsql {232 SELECT * FROM abc;233 } db2234} {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 } db240 execsql {241 SELECT * FROM abc;242 } db2243} {1 2 3 4 5 6 7 8 9}244db2 close245 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 backend252# 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]==0257} {258 259 # Return a list of two booleans (either 0 or 1). The first is true260 # if the named file exists. The second is true only if the file261 # exists and the first 28 bytes contain at least one non-zero byte.262 #263 proc filestate {fname} {264 set exists 0265 set content 0266 if {[file exists $fname]} {267 set exists 1268 set hdr [hexio_read $fname 0 28]269 set content [expr {0==[string match $hdr [string repeat 0 56]]}]270 }271 list $exists $content272 }273 274 do_test exclusive-3.0 {275 filestate test.db-journal276 } {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-journal284 } {1 1}285 do_test exclusive-3.2 {286 execsql {287 COMMIT;288 }289 filestate test.db-journal290 } {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-journal307 } {1 0}308 do_test exclusive-3.6 {309 execsql {310 PRAGMA locking_mode = normal;311 SELECT * FROM abc;312 }313 filestate test.db-journal314 } {0 0}315}316 317#----------------------------------------------------------------------318# Tests exclusive-4.X - test that rollback works correctly when319# in exclusive-access mode.320#321 322# The following procedure computes a "signature" for table "t3". If323# T3 changes in any way, the signature should change. 324#325# This is used to test ROLLBACK. We gather a signature for t3, then326# 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 signature361} $::X362 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 signature373} $::X374 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 truncated394# 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 no399# longer active.400#401db close402sqlite3 db test.db403 404# if we're using proxy locks, we use 3 filedescriptors for a db405# that is open but NOT writing changes, normally406# sqlite uses 1 (proxy locking adds the conch and the local lock)407set using_proxy 0408foreach {name value} [array get env SQLITE_FORCE_PROXY_LOCKING] {409 set using_proxy $value410}411set extrafds 0412if {$using_proxy!=0} {413 set extrafds 2414} 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 lazily427 set sqlite_open_file_count428 expr $sqlite_open_file_count-$extrafds429} {2}430do_test exclusive-5.2 {431 execsql {432 COMMIT;433 }434 # One file open: the db.435 set sqlite_open_file_count436 expr $sqlite_open_file_count-$extrafds437} {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_count446 expr $sqlite_open_file_count-$extrafds447} {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 deferred454 set sqlite_open_file_count455 expr $sqlite_open_file_count-$extrafds456} {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 deferred463 set sqlite_open_file_count464 expr $sqlite_open_file_count-$extrafds465} {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_count475 expr $sqlite_open_file_count-$extrafds476} {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-journal490 copy_file test.db test2.db491 copy_file test.db-journal test2.db-journal492 sqlite3 db test2.db493} {}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 close502 forcedelete test.db test.db-journal503 set fd [open test.db-journal w]504 puts $fd x505 close $fd506 sqlite3 db test.db507} {}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 fb3b3024ea238d5c515if {[permutation]!="journaltest"} {516 # The custom VFS used by the "journaltest" permutation cannot open the517 # shared-memory file. So, while it is able to switch the db file to518 # journal_mode=WAL when locking_mode=EXCLUSIVE, it can no longer access519 # it once the locking_mode is changed back to NORMAL.520 do_test exclusive-7.1 {521 db close522 forcedelete test.db test.db-journal test.db-wal523 sqlite3 db test.db524 # 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==0539 540finish_test541