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 501
