AryaWu/sqlite
0
1# 2009 March 042#3# The author disclaims copyright to this source code. In place of4# 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. The12# 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.tcl18 19ifcapable !unlock_notify||!shared_cache {20 finish_test21 return22}23db close24set ::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.db32 sqlite3 db2 test.db33 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) } db241} {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 zScript51} {}52do_test notify1-1.5 {53 execsql { COMMIT }54 eval $zScript55 execsql { SELECT * FROM t1 }56} {1 2 3 4}57 58#-------------------------------------------------------------------------59# Verify that invoking the "unlock_notify" method with no arguments60# (which is the equivalent of invoking sqlite3_unlock_notify() with61# 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) } db272} {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 zScript82} {}83do_test notify1-1.15 {84 db2 unlock_notify85 execsql { COMMIT }86 eval $zScript87 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 system109# would be deadlocked (since [db2] is already waiting on [db], and this110# 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 } db2121} {I II}122do_test notify1-2.2.2 {123 catchsql { SELECT * FROM t1 } db2124} {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 $msg134} {1 {database is deadlocked}}135 136#137# Test for slightly more complex deadlock involving three database138# connections: db, db2 and db3.139#140do_test notify1-2.3.1 {141 db close142 db2 close143 forcedelete test.db test2.db test3.db144 foreach con {db db2 db3} {145 sqlite3 $con test.db146 $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) } db157 execsql { BEGIN ; INSERT INTO t2 VALUES(1, 2) } db2158 execsql { BEGIN ; INSERT INTO t3 VALUES(1, 2) } db3159} {}160do_test notify1-2.3.3 {161 catchsql { SELECT * FROM t2 } db162} {1 {database table is locked: t2}}163do_test notify1-2.3.4 {164 catchsql { SELECT * FROM t3 } db2165} {1 {database table is locked: t3}}166do_test notify1-2.3.5 {167 catchsql { SELECT * FROM t1 } db3168} {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 $msg177} {1 {database is deadlocked}}178do_test notify1-2.3.8 {179 execsql { COMMIT }180 set lUnlock181} {}182do_test notify1-2.3.9 {183 db3 unlock_notify {lappend lUnlock db3} 184 set lUnlock185} {db3}186do_test notify1-2.3.10 {187 execsql { COMMIT } db2188 set lUnlock189} {db3 db}190do_test notify1-2.3.11 {191 execsql { COMMIT } db3192 set lUnlock193} {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.db206 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.db217 catchsql { SELECT * FROM t1 } $cmd218 } {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 $ii223 }224 do_test notify1-$tn.3 {225 set lUnlock226 } {}227 do_test notify1-$tn.4 {228 execsql {COMMIT}229 lsort -integer $lUnlock230 } $lUnlockFinal231 do_test notify1-$tn.5 {232 for {set ii 1} {$ii <= $nConn} {incr ii} {233 "db$ii" close234 }235 } {}236}237db close238 239#-------------------------------------------------------------------------240# These tests, notify1-5.*, test that a malloc() failure that occurs while241# allocating an array to use as an argument to an unlock-notify callback242# is handled correctly.243# 244source $testdir/malloc_common.tcl245do_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.db255 catchsql { SELECT * FROM t1 } $cmd256 $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-notify264 # callbacks were invoked, OR265 # 2) The transaction opened by [db] was committed and 60 unlock-notify266 # 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 the280# "blocking connection" has already been set by a previous281# SQLITE_LOCKED.282#283# notify1-6.2.*: Test encountering an SQLITE_LOCKED error when already284# waiting on an unlock-notify callback.285#286# notify1-6.3.*: Test that if an SQLITE_LOCKED error is encountered while287# already waiting on an unlock-notify callback, and then288# the blocker that caused the SQLITE_LOCKED commits its289# transaction, the unlock-notify callback is not invoked.290#291# notify1-6.4.*: Like 6.3.*, except that instead of the second blocker292# committing its transaction, the first does. The 293# unlock-notify callback is therefore invoked.294#295db close296do_test notify1-6.1.1 {297 forcedelete test.db test2.db298 foreach conn {db db2 db3} {299 sqlite3 $conn test.db300 execsql { ATTACH 'test2.db' AS two } $conn301 }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 } db2310 execsql { 311 BEGIN;312 INSERT INTO t2 VALUES(1, 2);313 } db3314} {}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 0324 db unlock_notify {set unlocked 1}325 set unlocked326} {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 } db2332 set unlocked333} {1}334 335do_test notify1-6.3.1 {336 execsql { 337 BEGIN;338 INSERT INTO t1 VALUES(3, 4);339 } db2340} {}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 0346 db unlock_notify {set unlocked 1}347 set unlocked348} {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 } db3354 set unlocked355} {0}356 357do_test notify1-6.4.1 {358 execsql { 359 BEGIN;360 INSERT INTO t2 VALUES(3, 4);361 } db3362 catchsql { SELECT * FROM t2 }363} {1 {database table is locked: t2}}364do_test notify1-6.4.2 {365 execsql { COMMIT } db2366 set unlocked367} {1}368do_test notify1-6.4.3 {369 execsql { COMMIT } db3370} {}371db close372db2 close373db3 close374 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_notify381}382do_test notify1-7.1 {383 foreach conn {db db2 db3} {384 sqlite3 $conn test.db385 }386 execsql {387 BEGIN;388 INSERT INTO t1 VALUES(5, 6);389 }390} {}391do_test notify1-7.2 {392 catchsql { SELECT * FROM t1 } db2393} {1 {database table is locked: t1}}394do_test notify1-7.3 {395 catchsql { SELECT * FROM t1 } db3396} {1 {database table is locked: t1}}397do_test notify1-7.4 {398 set unlock_notify 0399 db2 unlock_notify unlock_notify400 sqlite3_unlock_notify db3401} {SQLITE_OK}402do_test notify1-7.5 {403 set unlock_notify404} {0}405do_test notify1-7.6 {406 execsql { COMMIT }407 set unlock_notify408} {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 } db2420} {1 {database table is locked: t1}}421do_test notify1-8.2 {422 sqlite3_extended_errcode db2423} {SQLITE_LOCKED_SHAREDCACHE}424 425do_test notify1-8.3 {426 execsql {427 COMMIT;428 BEGIN EXCLUSIVE;429 }430 catchsql { SELECT * FROM t1 } db2431} {1 {database schema is locked: main}}432do_test notify1-8.4 {433 sqlite3_extended_errcode db2434} {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 } db2449} {1 2 3 4 5 6 7 8}450do_test notify1-9.2 {451 execsql { SELECT * FROM t1 } db3452} {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 } db3461} {1 {database table is locked}}462do_test notify1-9.5 {463 execsql { COMMIT } db2464 execsql { SELECT * FROM t2 } db3465} {}466do_test notify1-9.6 {467 execsql { COMMIT }468} {}469 470do_test notify1-9.7 {471 execsql {472 BEGIN;473 SELECT * FROM t1;474 } db2475} {1 2 3 4 5 6 7 8}476do_test notify1-9.8 {477 execsql { SELECT * FROM t1 } db3478} {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 } db3487} {1 {database table is locked}}488do_test notify1-9.11 {489 execsql { COMMIT }490 execsql { SELECT * FROM t2 } db3491} {}492do_test notify1-9.12 {493 execsql { COMMIT } db2494} {}495 496db close497db2 close498db3 close499sqlite3_enable_shared_cache $::enable_shared_cache500finish_test501