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