Debian Codegraph @

test/exclusive.test

raw
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
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
19
20 ifcapable {!pager_pragmas} {
21 finish_test
22 return
23 }
24
25 forcedelete test2.db-journal
26 forcedelete test2.db
27 forcedelete test3.db-journal
28 forcedelete test3.db
29 forcedelete test4.db-journal
30 forcedelete test4.db
31
32 #----------------------------------------------------------------------
33 # Test cases exclusive-1.X test the PRAGMA logic.
34 #
35 do_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]
42 do_test exclusive-1.1 {
43 execsql {
44 pragma locking_mode = exclusive;
45 }
46 } {exclusive}
47 do_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]
54 do_test exclusive-1.3 {
55 execsql {
56 pragma locking_mode = normal;
57 }
58 } {normal}
59 do_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]
66 do_test exclusive-1.5 {
67 execsql {
68 pragma locking_mode = invalid;
69 }
70 } {normal}
71 do_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]
78 ifcapable 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 #
161 do_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}
168 do_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}
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.
179 execsql {
180 SELECT * FROM abc;
181 } db
182 } {1 2 3 4 5 6}
183 do_test exclusive-2.4 {
184 execsql {
185 SELECT * FROM abc;
186 } db2
187 } {1 2 3 4 5 6}
188 do_test exclusive-2.5 {
189 catchsql {
190 INSERT INTO abc VALUES(7, 8, 9);
191 } db2
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.
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}}
205 do_test exclusive-2.7 {
206 catchsql {
207 COMMIT
208 } db2
209 } {1 {database is locked}}
210 do_test exclusive-2.8 {
211 execsql {
212 ROLLBACK;
213 } db2
214 } {}
215 sqlite3_soft_heap_limit $cmdlinearg(soft-heap-limit)
216
217 do_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}}
226 do_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}}
235 do_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}
244 db2 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 #
255 if {$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 #
329 proc signature {} {
330 return [db eval {SELECT count(*), md5sum(x) FROM t3}]
331 }
332
333 do_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
350 set ::X [signature]
351 do_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
363 do_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
375 do_test exclusive-4.3 {
376 execsql {
377 INSERT INTO t3 SELECT randstr(10,400) FROM t3 WHERE random()%10==0;
378 }
379 } {}
380
381 do_test exclusive-4.4 {
382 catch {set ::X [signature]}
383 } {0}
384 do_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 #
396 if {[atomic_batch_write test.db]==0} {
397
398 # Close and reopen the database so that the temp database is no
399 # longer active.
400 #
401 db close
402 sqlite3 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)
407 set using_proxy 0
408 foreach {name value} [array get env SQLITE_FORCE_PROXY_LOCKING] {
409 set using_proxy $value
410 }
411 set extrafds 0
412 if {$using_proxy!=0} {
413 set extrafds 2
414 }
415
416 do_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 } {}
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
429 } {2}
430 do_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}
438 do_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}
448 do_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}
457 do_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}
466 do_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}
472 do_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
480 do_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 }
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
492 sqlite3 db test2.db
493 } {}
494
495 do_execsql_test exclusive-6.3 {
496 PRAGMA locking_mode = EXCLUSIVE;
497 SELECT * FROM t4;
498 } {exclusive Eden 1955}
499
500 do_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
509 do_execsql_test exclusive-6.5 {
510 PRAGMA locking_mode = EXCLUSIVE;
511 SELECT * FROM sqlite_master;
512 } {exclusive}
513
514 # 2019-12-26 ticket fb3b3024ea238d5c
515 if {[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
540 finish_test