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
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18
19ifcapable !unlock_notify||!shared_cache {
20  finish_test
21  return
22}
23db close
24set ::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#
30do_test notify1-1.1 {
31  sqlite3 db test.db
32  sqlite3 db2 test.db
33  execsql { CREATE TABLE t1(a, b) }
34} {}
35do_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}}
42do_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}
49do_test notify1-1.4 {
50  set zScript
51} {}
52do_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#
63do_test notify1-1.11 {
64  execsql { DROP TABLE t1; CREATE TABLE t1(a, b) }
65} {}
66do_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}}
73do_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}
80do_test notify1-1.14 {
81  set zScript
82} {}
83do_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# 
94do_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#
112do_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}
122do_test notify1-2.2.2 {
123  catchsql { SELECT * FROM t1 } db2
124} {1 {database table is locked: t1}}
125do_test notify1-2.2.3 {
126  db2 unlock_notify {lappend unlock_notify db2}
127} {}
128do_test notify1-2.2.4 {
129  catchsql { INSERT INTO t2 VALUES('III', 'IV') }
130} {1 {database table is locked: t2}}
131do_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#
140do_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} {}
155do_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} {}
160do_test notify1-2.3.3 {
161  catchsql { SELECT * FROM t2 } db
162} {1 {database table is locked: t2}}
163do_test notify1-2.3.4 {
164  catchsql { SELECT * FROM t3 } db2
165} {1 {database table is locked: t3}}
166do_test notify1-2.3.5 {
167  catchsql { SELECT * FROM t1 } db3
168} {1 {database table is locked: t1}}
169do_test notify1-2.3.6 {
170  set lUnlock [list]
171  db  unlock_notify {lappend lUnlock db}
172  db2 unlock_notify {lappend lUnlock db2}
173} {}
174do_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}}
178do_test notify1-2.3.8 {
179  execsql { COMMIT }
180  set lUnlock
181} {}
182do_test notify1-2.3.9 {
183  db3 unlock_notify {lappend lUnlock db3} 
184  set lUnlock
185} {db3}
186do_test notify1-2.3.10 {
187  execsql { COMMIT } db2
188  set lUnlock
189} {db3 db}
190do_test notify1-2.3.11 {
191  execsql { COMMIT } db3
192  set lUnlock
193} {db3 db db2}
194catch { db3 close }
195catch { db2 close }
196catch { 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# 
203foreach {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}
237db 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# 
244source $testdir/malloc_common.tcl
245do_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#
295db close
296do_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} {}
315do_test notify1-6.1.2 {
316  catchsql { SELECT * FROM t2 }
317} {1 {database table is locked: t2}}
318do_test notify1-6.1.3 {
319  catchsql { SELECT * FROM t1 }
320} {1 {database table is locked: t1}}
321
322do_test notify1-6.2.1 {
323  set unlocked 0
324  db unlock_notify {set unlocked 1}
325  set unlocked
326} {0}
327do_test notify1-6.2.2 {
328  catchsql { SELECT * FROM t2 }
329} {1 {database table is locked: t2}}
330do_test notify1-6.2.3 {
331  execsql { COMMIT } db2
332  set unlocked
333} {1}
334
335do_test notify1-6.3.1 {
336  execsql { 
337    BEGIN;
338    INSERT INTO t1 VALUES(3, 4);
339  } db2
340} {}
341do_test notify1-6.3.2 {
342  catchsql { SELECT * FROM t1 }
343} {1 {database table is locked: t1}}
344do_test notify1-6.3.3 {
345  set unlocked 0
346  db unlock_notify {set unlocked 1}
347  set unlocked
348} {0}
349do_test notify1-6.3.4 {
350  catchsql { SELECT * FROM t2 }
351} {1 {database table is locked: t2}}
352do_test notify1-6.3.5 {
353  execsql { COMMIT } db3
354  set unlocked
355} {0}
356
357do_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}}
364do_test notify1-6.4.2 {
365  execsql { COMMIT } db2
366  set unlocked
367} {1}
368do_test notify1-6.4.3 {
369  execsql { COMMIT } db3
370} {}
371db close
372db2 close
373db3 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#
379proc unlock_notify {} {
380  incr ::unlock_notify
381}
382do_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} {}
391do_test notify1-7.2 {
392  catchsql { SELECT * FROM t1 } db2
393} {1 {database table is locked: t1}}
394do_test notify1-7.3 {
395  catchsql { SELECT * FROM t1 } db3
396} {1 {database table is locked: t1}}
397do_test notify1-7.4 {
398  set unlock_notify 0
399  db2 unlock_notify unlock_notify
400  sqlite3_unlock_notify db3
401} {SQLITE_OK}
402do_test notify1-7.5 {
403  set unlock_notify
404} {0}
405do_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#
414do_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}}
421do_test notify1-8.2 {
422  sqlite3_extended_errcode db2
423} {SQLITE_LOCKED_SHAREDCACHE}
424
425do_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}}
432do_test notify1-8.4 {
433  sqlite3_extended_errcode db2
434} {SQLITE_LOCKED_SHAREDCACHE}
435
436do_test notify1-8.X {
437  execsql { COMMIT } 
438} {}
439
440#-------------------------------------------------------------------------
441# Test cases notify1-9.* test the shared-cache 'pending-lock' feature.
442#
443do_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}
450do_test notify1-9.2 {
451  execsql { SELECT * FROM t1 } db3
452} {1 2 3 4 5 6 7 8}
453do_test notify1-9.3 {
454  catchsql { 
455    BEGIN;
456    INSERT INTO t1 VALUES(9, 10);
457  }
458} {1 {database table is locked: t1}}
459do_test notify1-9.4 {
460  catchsql { SELECT * FROM t2 } db3
461} {1 {database table is locked}}
462do_test notify1-9.5 {
463  execsql  { COMMIT } db2
464  execsql { SELECT * FROM t2 } db3
465} {}
466do_test notify1-9.6 {
467  execsql  { COMMIT }
468} {}
469
470do_test notify1-9.7 {
471  execsql {
472    BEGIN;
473    SELECT * FROM t1;
474  } db2
475} {1 2 3 4 5 6 7 8}
476do_test notify1-9.8 {
477  execsql { SELECT * FROM t1 } db3
478} {1 2 3 4 5 6 7 8}
479do_test notify1-9.9 {
480  catchsql { 
481    BEGIN;
482    INSERT INTO t1 VALUES(9, 10);
483  }
484} {1 {database table is locked: t1}}
485do_test notify1-9.10 {
486  catchsql { SELECT * FROM t2 } db3
487} {1 {database table is locked}}
488do_test notify1-9.11 {
489  execsql  { COMMIT }
490  execsql { SELECT * FROM t2 } db3
491} {}
492do_test notify1-9.12 {
493  execsql  { COMMIT } db2
494} {}
495
496db close
497db2 close
498db3 close
499sqlite3_enable_shared_cache $::enable_shared_cache
500finish_test
501