wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 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.4 2009/06/05 17:09:12 drh 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 file delete -force 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 file delete -force 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