1: # 2009 March 04
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 file is testing the sqlite3_unlock_notify() API.
13: #
14: # $Id: notify1.test,v 1.1.1.1 2012/02/21 17:04:16 misho Exp $
15:
16: set testdir [file dirname $argv0]
17: source $testdir/tester.tcl
18:
19: ifcapable !unlock_notify||!shared_cache {
20: finish_test
21: return
22: }
23: db close
24: set ::enable_shared_cache [sqlite3_enable_shared_cache 1]
25:
26: #-------------------------------------------------------------------------
27: # Warm body test. Test that an unlock-notify callback can be registered
28: # and that it is invoked.
29: #
30: do_test notify1-1.1 {
31: sqlite3 db test.db
32: sqlite3 db2 test.db
33: execsql { CREATE TABLE t1(a, b) }
34: } {}
35: do_test notify1-1.2 {
36: execsql {
37: BEGIN;
38: INSERT INTO t1 VALUES(1, 2);
39: }
40: catchsql { INSERT INTO t1 VALUES(3, 4) } db2
41: } {1 {database table is locked}}
42: do_test notify1-1.3 {
43: set zScript ""
44: db2 unlock_notify {
45: set zScript "db2 eval { INSERT INTO t1 VALUES(3, 4) }"
46: }
47: execsql { SELECT * FROM t1 }
48: } {1 2}
49: do_test notify1-1.4 {
50: set zScript
51: } {}
52: do_test notify1-1.5 {
53: execsql { COMMIT }
54: eval $zScript
55: execsql { SELECT * FROM t1 }
56: } {1 2 3 4}
57:
58: #-------------------------------------------------------------------------
59: # Verify that invoking the "unlock_notify" method with no arguments
60: # (which is the equivalent of invoking sqlite3_unlock_notify() with
61: # a NULL xNotify argument) cancels a pending notify callback.
62: #
63: do_test notify1-1.11 {
64: execsql { DROP TABLE t1; CREATE TABLE t1(a, b) }
65: } {}
66: do_test notify1-1.12 {
67: execsql {
68: BEGIN;
69: INSERT INTO t1 VALUES(1, 2);
70: }
71: catchsql { INSERT INTO t1 VALUES(3, 4) } db2
72: } {1 {database table is locked}}
73: do_test notify1-1.13 {
74: set zScript ""
75: db2 unlock_notify {
76: set zScript "db2 eval { INSERT INTO t1 VALUES(3, 4) }"
77: }
78: execsql { SELECT * FROM t1 }
79: } {1 2}
80: do_test notify1-1.14 {
81: set zScript
82: } {}
83: do_test notify1-1.15 {
84: db2 unlock_notify
85: execsql { COMMIT }
86: eval $zScript
87: execsql { SELECT * FROM t1 }
88: } {1 2}
89:
90: #-------------------------------------------------------------------------
91: # The following tests, notify1-2.*, test that deadlock is detected
92: # correctly.
93: #
94: do_test notify1-2.1 {
95: execsql {
96: CREATE TABLE t2(a, b);
97: INSERT INTO t2 VALUES('I', 'II');
98: }
99: } {}
100:
101: #
102: # Test for simple deadlock involving two database connections.
103: #
104: # 1. Grab a write-lock on t1 with [db]. Then grab a read-lock on t2 with [db2].
105: # 2. Try to grab a read-lock on t1 with [db2] (fails).
106: # 3. Have [db2] wait on the read-lock it failed to obtain in step 2.
107: # 4. Try to grab a write-lock on t2 with [db] (fails).
108: # 5. Try to have [db] wait on the lock from step 4. Fails, as the system
109: # would be deadlocked (since [db2] is already waiting on [db], and this
110: # operation would have [db] wait on [db2]).
111: #
112: do_test notify1-2.2.1 {
113: execsql {
114: BEGIN;
115: INSERT INTO t1 VALUES(5, 6);
116: }
117: execsql {
118: BEGIN;
119: SELECT * FROM t2;
120: } db2
121: } {I II}
122: do_test notify1-2.2.2 {
123: catchsql { SELECT * FROM t1 } db2
124: } {1 {database table is locked: t1}}
125: do_test notify1-2.2.3 {
126: db2 unlock_notify {lappend unlock_notify db2}
127: } {}
128: do_test notify1-2.2.4 {
129: catchsql { INSERT INTO t2 VALUES('III', 'IV') }
130: } {1 {database table is locked: t2}}
131: do_test notify1-2.2.5 {
132: set rc [catch { db unlock_notify {lappend unlock_notify db} } msg]
133: list $rc $msg
134: } {1 {database is deadlocked}}
135:
136: #
137: # Test for slightly more complex deadlock involving three database
138: # connections: db, db2 and db3.
139: #
140: do_test notify1-2.3.1 {
141: db close
142: db2 close
143: forcedelete test.db test2.db test3.db
144: foreach con {db db2 db3} {
145: sqlite3 $con test.db
146: $con eval { ATTACH 'test2.db' AS aux2 }
147: $con eval { ATTACH 'test3.db' AS aux3 }
148: }
149: execsql {
150: CREATE TABLE main.t1(a, b);
151: CREATE TABLE aux2.t2(a, b);
152: CREATE TABLE aux3.t3(a, b);
153: }
154: } {}
155: do_test notify1-2.3.2 {
156: execsql { BEGIN ; INSERT INTO t1 VALUES(1, 2) } db
157: execsql { BEGIN ; INSERT INTO t2 VALUES(1, 2) } db2
158: execsql { BEGIN ; INSERT INTO t3 VALUES(1, 2) } db3
159: } {}
160: do_test notify1-2.3.3 {
161: catchsql { SELECT * FROM t2 } db
162: } {1 {database table is locked: t2}}
163: do_test notify1-2.3.4 {
164: catchsql { SELECT * FROM t3 } db2
165: } {1 {database table is locked: t3}}
166: do_test notify1-2.3.5 {
167: catchsql { SELECT * FROM t1 } db3
168: } {1 {database table is locked: t1}}
169: do_test notify1-2.3.6 {
170: set lUnlock [list]
171: db unlock_notify {lappend lUnlock db}
172: db2 unlock_notify {lappend lUnlock db2}
173: } {}
174: do_test notify1-2.3.7 {
175: set rc [catch { db3 unlock_notify {lappend lUnlock db3} } msg]
176: list $rc $msg
177: } {1 {database is deadlocked}}
178: do_test notify1-2.3.8 {
179: execsql { COMMIT }
180: set lUnlock
181: } {}
182: do_test notify1-2.3.9 {
183: db3 unlock_notify {lappend lUnlock db3}
184: set lUnlock
185: } {db3}
186: do_test notify1-2.3.10 {
187: execsql { COMMIT } db2
188: set lUnlock
189: } {db3 db}
190: do_test notify1-2.3.11 {
191: execsql { COMMIT } db3
192: set lUnlock
193: } {db3 db db2}
194: catch { db3 close }
195: catch { db2 close }
196: catch { db close }
197:
198: #-------------------------------------------------------------------------
199: # The following tests, notify1-3.* and notify1-4.*, test that callbacks
200: # can be issued when there are many (>16) connections waiting on a single
201: # unlock event.
202: #
203: foreach {tn nConn} {3 20 4 76} {
204: do_test notify1-$tn.1 {
205: sqlite3 db test.db
206: execsql {
207: BEGIN;
208: INSERT INTO t1 VALUES('a', 'b');
209: }
210: } {}
211: set lUnlock [list]
212: set lUnlockFinal [list]
213: for {set ii 1} {$ii <= $nConn} {incr ii} {
214: do_test notify1-$tn.2.$ii.1 {
215: set cmd "db$ii"
216: sqlite3 $cmd test.db
217: catchsql { SELECT * FROM t1 } $cmd
218: } {1 {database table is locked: t1}}
219: do_test notify1-$tn.2.$ii.2 {
220: $cmd unlock_notify "lappend lUnlock $ii"
221: } {}
222: lappend lUnlockFinal $ii
223: }
224: do_test notify1-$tn.3 {
225: set lUnlock
226: } {}
227: do_test notify1-$tn.4 {
228: execsql {COMMIT}
229: lsort -integer $lUnlock
230: } $lUnlockFinal
231: do_test notify1-$tn.5 {
232: for {set ii 1} {$ii <= $nConn} {incr ii} {
233: "db$ii" close
234: }
235: } {}
236: }
237: db close
238:
239: #-------------------------------------------------------------------------
240: # These tests, notify1-5.*, test that a malloc() failure that occurs while
241: # allocating an array to use as an argument to an unlock-notify callback
242: # is handled correctly.
243: #
244: source $testdir/malloc_common.tcl
245: do_malloc_test notify1-5 -tclprep {
246: set ::lUnlock [list]
247: execsql {
248: CREATE TABLE t1(a, b);
249: BEGIN;
250: INSERT INTO t1 VALUES('a', 'b');
251: }
252: for {set ii 1} {$ii <= 60} {incr ii} {
253: set cmd "db$ii"
254: sqlite3 $cmd test.db
255: catchsql { SELECT * FROM t1 } $cmd
256: $cmd unlock_notify "lappend ::lUnlock $ii"
257: }
258: } -sqlbody {
259: COMMIT;
260: } -cleanup {
261: # One of two things should have happened:
262: #
263: # 1) The transaction opened by [db] was not committed. No unlock-notify
264: # callbacks were invoked, OR
265: # 2) The transaction opened by [db] was committed and 60 unlock-notify
266: # callbacks were invoked.
267: #
268: do_test notify1-5.systemstate {
269: expr { ([llength $::lUnlock]==0 && [sqlite3_get_autocommit db]==0)
270: || ([llength $::lUnlock]==60 && [sqlite3_get_autocommit db]==1)
271: }
272: } {1}
273: for {set ii 1} {$ii <= 60} {incr ii} { "db$ii" close }
274: }
275:
276: #-------------------------------------------------------------------------
277: # Test cases notify1-6.* test cases where the following occur:
278: #
279: # notify1-6.1.*: Test encountering an SQLITE_LOCKED error when the
280: # "blocking connection" has already been set by a previous
281: # SQLITE_LOCKED.
282: #
283: # notify1-6.2.*: Test encountering an SQLITE_LOCKED error when already
284: # waiting on an unlock-notify callback.
285: #
286: # notify1-6.3.*: Test that if an SQLITE_LOCKED error is encountered while
287: # already waiting on an unlock-notify callback, and then
288: # the blocker that caused the SQLITE_LOCKED commits its
289: # transaction, the unlock-notify callback is not invoked.
290: #
291: # notify1-6.4.*: Like 6.3.*, except that instead of the second blocker
292: # committing its transaction, the first does. The
293: # unlock-notify callback is therefore invoked.
294: #
295: db close
296: do_test notify1-6.1.1 {
297: forcedelete test.db test2.db
298: foreach conn {db db2 db3} {
299: sqlite3 $conn test.db
300: execsql { ATTACH 'test2.db' AS two } $conn
301: }
302: execsql {
303: CREATE TABLE t1(a, b);
304: CREATE TABLE two.t2(a, b);
305: }
306: execsql {
307: BEGIN;
308: INSERT INTO t1 VALUES(1, 2);
309: } db2
310: execsql {
311: BEGIN;
312: INSERT INTO t2 VALUES(1, 2);
313: } db3
314: } {}
315: do_test notify1-6.1.2 {
316: catchsql { SELECT * FROM t2 }
317: } {1 {database table is locked: t2}}
318: do_test notify1-6.1.3 {
319: catchsql { SELECT * FROM t1 }
320: } {1 {database table is locked: t1}}
321:
322: do_test notify1-6.2.1 {
323: set unlocked 0
324: db unlock_notify {set unlocked 1}
325: set unlocked
326: } {0}
327: do_test notify1-6.2.2 {
328: catchsql { SELECT * FROM t2 }
329: } {1 {database table is locked: t2}}
330: do_test notify1-6.2.3 {
331: execsql { COMMIT } db2
332: set unlocked
333: } {1}
334:
335: do_test notify1-6.3.1 {
336: execsql {
337: BEGIN;
338: INSERT INTO t1 VALUES(3, 4);
339: } db2
340: } {}
341: do_test notify1-6.3.2 {
342: catchsql { SELECT * FROM t1 }
343: } {1 {database table is locked: t1}}
344: do_test notify1-6.3.3 {
345: set unlocked 0
346: db unlock_notify {set unlocked 1}
347: set unlocked
348: } {0}
349: do_test notify1-6.3.4 {
350: catchsql { SELECT * FROM t2 }
351: } {1 {database table is locked: t2}}
352: do_test notify1-6.3.5 {
353: execsql { COMMIT } db3
354: set unlocked
355: } {0}
356:
357: do_test notify1-6.4.1 {
358: execsql {
359: BEGIN;
360: INSERT INTO t2 VALUES(3, 4);
361: } db3
362: catchsql { SELECT * FROM t2 }
363: } {1 {database table is locked: t2}}
364: do_test notify1-6.4.2 {
365: execsql { COMMIT } db2
366: set unlocked
367: } {1}
368: do_test notify1-6.4.3 {
369: execsql { COMMIT } db3
370: } {}
371: db close
372: db2 close
373: db3 close
374:
375: #-------------------------------------------------------------------------
376: # Test cases notify1-7.* tests that when more than one distinct
377: # unlock-notify function is registered, all are invoked correctly.
378: #
379: proc unlock_notify {} {
380: incr ::unlock_notify
381: }
382: do_test notify1-7.1 {
383: foreach conn {db db2 db3} {
384: sqlite3 $conn test.db
385: }
386: execsql {
387: BEGIN;
388: INSERT INTO t1 VALUES(5, 6);
389: }
390: } {}
391: do_test notify1-7.2 {
392: catchsql { SELECT * FROM t1 } db2
393: } {1 {database table is locked: t1}}
394: do_test notify1-7.3 {
395: catchsql { SELECT * FROM t1 } db3
396: } {1 {database table is locked: t1}}
397: do_test notify1-7.4 {
398: set unlock_notify 0
399: db2 unlock_notify unlock_notify
400: sqlite3_unlock_notify db3
401: } {SQLITE_OK}
402: do_test notify1-7.5 {
403: set unlock_notify
404: } {0}
405: do_test notify1-7.6 {
406: execsql { COMMIT }
407: set unlock_notify
408: } {2}
409:
410: #-------------------------------------------------------------------------
411: # Test cases notify1-8.* tests that the correct SQLITE_LOCKED extended
412: # error code is returned in various scenarios.
413: #
414: do_test notify1-8.1 {
415: execsql {
416: BEGIN;
417: INSERT INTO t1 VALUES(7, 8);
418: }
419: catchsql { SELECT * FROM t1 } db2
420: } {1 {database table is locked: t1}}
421: do_test notify1-8.2 {
422: sqlite3_extended_errcode db2
423: } {SQLITE_LOCKED_SHAREDCACHE}
424:
425: do_test notify1-8.3 {
426: execsql {
427: COMMIT;
428: BEGIN EXCLUSIVE;
429: }
430: catchsql { SELECT * FROM t1 } db2
431: } {1 {database schema is locked: main}}
432: do_test notify1-8.4 {
433: sqlite3_extended_errcode db2
434: } {SQLITE_LOCKED_SHAREDCACHE}
435:
436: do_test notify1-8.X {
437: execsql { COMMIT }
438: } {}
439:
440: #-------------------------------------------------------------------------
441: # Test cases notify1-9.* test the shared-cache 'pending-lock' feature.
442: #
443: do_test notify1-9.1 {
444: execsql {
445: CREATE TABLE t2(a, b);
446: BEGIN;
447: SELECT * FROM t1;
448: } db2
449: } {1 2 3 4 5 6 7 8}
450: do_test notify1-9.2 {
451: execsql { SELECT * FROM t1 } db3
452: } {1 2 3 4 5 6 7 8}
453: do_test notify1-9.3 {
454: catchsql {
455: BEGIN;
456: INSERT INTO t1 VALUES(9, 10);
457: }
458: } {1 {database table is locked: t1}}
459: do_test notify1-9.4 {
460: catchsql { SELECT * FROM t2 } db3
461: } {1 {database table is locked}}
462: do_test notify1-9.5 {
463: execsql { COMMIT } db2
464: execsql { SELECT * FROM t2 } db3
465: } {}
466: do_test notify1-9.6 {
467: execsql { COMMIT }
468: } {}
469:
470: do_test notify1-9.7 {
471: execsql {
472: BEGIN;
473: SELECT * FROM t1;
474: } db2
475: } {1 2 3 4 5 6 7 8}
476: do_test notify1-9.8 {
477: execsql { SELECT * FROM t1 } db3
478: } {1 2 3 4 5 6 7 8}
479: do_test notify1-9.9 {
480: catchsql {
481: BEGIN;
482: INSERT INTO t1 VALUES(9, 10);
483: }
484: } {1 {database table is locked: t1}}
485: do_test notify1-9.10 {
486: catchsql { SELECT * FROM t2 } db3
487: } {1 {database table is locked}}
488: do_test notify1-9.11 {
489: execsql { COMMIT }
490: execsql { SELECT * FROM t2 } db3
491: } {}
492: do_test notify1-9.12 {
493: execsql { COMMIT } db2
494: } {}
495:
496: db close
497: db2 close
498: db3 close
499: sqlite3_enable_shared_cache $::enable_shared_cache
500: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to notify1.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test