3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
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").
15 # $Id: exclusive.test,v 1.15 2009/06/26 12:30:40 danielk1977 Exp $
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
20 ifcapable {!pager_pragmas} {
25 forcedelete test2.db-journal
27 forcedelete test3.db-journal
29 forcedelete test4.db-journal
32 #----------------------------------------------------------------------
33 # Test cases exclusive-1.X test the PRAGMA logic.
35 do_test exclusive-1.0 {
38 pragma main.locking_mode;
39 pragma temp.locking_mode;
41 } [list normal normal exclusive]
42 do_test exclusive-1.1 {
44 pragma locking_mode = exclusive;
47 do_test exclusive-1.2 {
50 pragma main.locking_mode;
51 pragma temp.locking_mode;
53 } [list exclusive exclusive exclusive]
54 do_test exclusive-1.3 {
56 pragma locking_mode = normal;
59 do_test exclusive-1.4 {
62 pragma main.locking_mode;
63 pragma temp.locking_mode;
65 } [list normal normal exclusive]
66 do_test exclusive-1.5 {
68 pragma locking_mode = invalid;
71 do_test exclusive-1.6 {
74 pragma main.locking_mode;
75 pragma temp.locking_mode;
77 } [list normal normal exclusive]
79 do_test exclusive-1.7 {
81 pragma locking_mode = exclusive;
82 ATTACH 'test2.db' as aux;
85 pragma main.locking_mode;
86 pragma aux.locking_mode;
88 } {exclusive exclusive}
89 do_test exclusive-1.8 {
91 pragma main.locking_mode = normal;
94 pragma main.locking_mode;
95 pragma temp.locking_mode;
96 pragma aux.locking_mode;
98 } [list normal exclusive exclusive]
99 do_test exclusive-1.9 {
104 do_test exclusive-1.10 {
106 ATTACH 'test3.db' as aux2;
109 pragma main.locking_mode;
110 pragma aux.locking_mode;
111 pragma aux2.locking_mode;
113 } {normal exclusive exclusive}
114 do_test exclusive-1.11 {
116 pragma aux.locking_mode = normal;
119 pragma main.locking_mode;
120 pragma aux.locking_mode;
121 pragma aux2.locking_mode;
123 } {normal normal exclusive}
124 do_test exclusive-1.12 {
126 pragma locking_mode = normal;
129 pragma main.locking_mode;
130 pragma temp.locking_mode;
131 pragma aux.locking_mode;
132 pragma aux2.locking_mode;
134 } [list normal exclusive normal normal]
135 do_test exclusive-1.13 {
137 ATTACH 'test4.db' as aux3;
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;
146 } [list normal exclusive normal normal normal]
148 do_test exclusive-1.99 {
157 #----------------------------------------------------------------------
158 # Test cases exclusive-2.X verify that connections in exclusive
159 # locking_mode do not relinquish locks.
161 do_test exclusive-2.0 {
163 CREATE TABLE abc(a, b, c);
164 INSERT INTO abc VALUES(1, 2, 3);
165 PRAGMA locking_mode = exclusive;
168 do_test exclusive-2.1 {
171 INSERT INTO abc VALUES(4, 5, 6);
175 do_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.
183 do_test exclusive-2.4 {
188 do_test exclusive-2.5 {
190 INSERT INTO abc VALUES(7, 8, 9);
192 } {1 {database is locked}}
193 sqlite3_soft_heap_limit 0
194 do_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.
199 INSERT INTO abc VALUES(7, 8, 9);
204 } {1 {database is locked}}
205 do_test exclusive-2.7 {
209 } {1 {database is locked}}
210 do_test exclusive-2.8 {
215 sqlite3_soft_heap_limit $cmdlinearg(soft-heap-limit)
217 do_test exclusive-2.9 {
218 # Write the database to establish the exclusive lock with connection 'db.
220 INSERT INTO abc VALUES(7, 8, 9);
225 } {1 {database is locked}}
226 do_test exclusive-2.10 {
227 # Changing the locking-mode does not release any locks.
229 PRAGMA locking_mode = normal;
234 } {1 {database is locked}}
235 do_test exclusive-2.11 {
236 # After changing the locking mode, accessing the db releases locks.
243 } {1 2 3 4 5 6 7 8 9}
246 #----------------------------------------------------------------------
247 # Tests exclusive-3.X - test that a connection in exclusive mode
248 # truncates instead of deletes the journal file when committing
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.
255 if {$tcl_platform(platform) != "windows"
256 && [atomic_batch_write test.db]==0
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.
263 proc filestate {fname} {
266 if {[file exists $fname]} {
268 set hdr [hexio_read $fname 0 28]
269 set content [expr {0==[string match $hdr [string repeat 0 56]]}]
271 list $exists $content
274 do_test exclusive-3.0 {
275 filestate test.db-journal
277 do_test exclusive-3.1 {
279 PRAGMA locking_mode = exclusive;
283 filestate test.db-journal
285 do_test exclusive-3.2 {
289 filestate test.db-journal
291 do_test exclusive-3.3 {
293 INSERT INTO abc VALUES('A', 'B', 'C');
297 do_test exclusive-3.4 {
300 UPDATE abc SET a = 1, b = 2, c = 3;
305 do_test exclusive-3.5 {
306 filestate test.db-journal
308 do_test exclusive-3.6 {
310 PRAGMA locking_mode = normal;
313 filestate test.db-journal
317 #----------------------------------------------------------------------
318 # Tests exclusive-4.X - test that rollback works correctly when
319 # in exclusive-access mode.
322 # The following procedure computes a "signature" for table "t3". If
323 # T3 changes in any way, the signature should change.
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.
330 return [db eval {SELECT count(*), md5sum(x) FROM t3}]
333 do_test exclusive-4.0 {
334 execsql { PRAGMA locking_mode = exclusive; }
335 execsql { PRAGMA default_cache_size = 10; }
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;
347 execsql {SELECT count(*) FROM t3;}
351 do_test exclusive-4.1 {
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;
363 do_test exclusive-4.2 {
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;
375 do_test exclusive-4.3 {
377 INSERT INTO t3 SELECT randstr(10,400) FROM t3 WHERE random()%10==0;
381 do_test exclusive-4.4 {
382 catch {set ::X [signature]}
384 do_test exclusive-4.5 {
386 PRAGMA locking_mode = NORMAL;
392 #----------------------------------------------------------------------
393 # Tests exclusive-5.X - test that statement journals are truncated
394 # instead of deleted when in exclusive access mode.
396 if {[atomic_batch_write test.db]==0} {
398 # Close and reopen the database so that the temp database is no
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)
408 foreach {name value} [array get env SQLITE_FORCE_PROXY_LOCKING] {
409 set using_proxy $value
412 if {$using_proxy!=0} {
416 do_test exclusive-5.0 {
418 CREATE TABLE abc(a UNIQUE, b UNIQUE, c UNIQUE);
420 INSERT INTO abc VALUES(1, 2, 3);
421 INSERT INTO abc SELECT a+1, b+1, c+1 FROM abc;
424 do_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
430 do_test exclusive-5.2 {
434 # One file open: the db.
435 set sqlite_open_file_count
436 expr $sqlite_open_file_count-$extrafds
438 do_test exclusive-5.3 {
440 PRAGMA locking_mode = exclusive;
442 INSERT INTO abc VALUES(5, 6, 7);
444 # Two files open: the db and journal.
445 set sqlite_open_file_count
446 expr $sqlite_open_file_count-$extrafds
448 do_test exclusive-5.4 {
450 INSERT INTO abc SELECT a+10, b+10, c+10 FROM abc;
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
457 do_test exclusive-5.5 {
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
466 do_test exclusive-5.6 {
468 PRAGMA locking_mode = normal;
471 } {normal 1 2 3 2 3 4 5 6 7 11 12 13 12 13 14 15 16 17}
472 do_test exclusive-5.7 {
474 set sqlite_open_file_count
475 expr $sqlite_open_file_count-$extrafds
478 #-------------------------------------------------------------------------
480 do_execsql_test exclusive-6.1 {
481 CREATE TABLE t4(a, b);
482 INSERT INTO t4 VALUES('Eden', 1955);
484 INSERT INTO t4 VALUES('Macmillan', 1957);
485 INSERT INTO t4 VALUES('Douglas-Home', 1963);
486 INSERT INTO t4 VALUES('Wilson', 1964);
488 do_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
495 do_execsql_test exclusive-6.3 {
496 PRAGMA locking_mode = EXCLUSIVE;
498 } {exclusive Eden 1955}
500 do_test exclusive-6.4 {
502 forcedelete test.db test.db-journal
503 set fd [open test.db-journal w]
509 do_execsql_test exclusive-6.5 {
510 PRAGMA locking_mode = EXCLUSIVE;
511 SELECT * FROM sqlite_master;
514 } ;# atomic_batch_write==0