Annotation of embedaddon/sqlite3/test/lock.test, revision 1.1
1.1 ! misho 1: # 2001 September 15
! 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 script is database locks.
! 13: #
! 14: # $Id: lock.test,v 1.40 2009/06/16 17:49:36 drh Exp $
! 15:
! 16:
! 17: set testdir [file dirname $argv0]
! 18: source $testdir/tester.tcl
! 19:
! 20: # Create an alternative connection to the database
! 21: #
! 22: do_test lock-1.0 {
! 23: # Give a complex pathname to stress the path simplification logic in
! 24: # the vxworks driver and in test_async.
! 25: file mkdir tempdir/t1/t2
! 26: sqlite3 db2 ./tempdir/../tempdir/t1/.//t2/../../..//test.db
! 27: set dummy {}
! 28: } {}
! 29: do_test lock-1.1 {
! 30: execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}
! 31: } {}
! 32: do_test lock-1.2 {
! 33: execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name} db2
! 34: } {}
! 35: do_test lock-1.3 {
! 36: execsql {CREATE TABLE t1(a int, b int)}
! 37: execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}
! 38: } {t1}
! 39: do_test lock-1.5 {
! 40: catchsql {
! 41: SELECT name FROM sqlite_master WHERE type='table' ORDER BY name
! 42: } db2
! 43: } {0 t1}
! 44:
! 45: do_test lock-1.6 {
! 46: execsql {INSERT INTO t1 VALUES(1,2)}
! 47: execsql {SELECT * FROM t1}
! 48: } {1 2}
! 49: # Update: The schema is now brought up to date by test lock-1.5.
! 50: # do_test lock-1.7.1 {
! 51: # catchsql {SELECT * FROM t1} db2
! 52: # } {1 {no such table: t1}}
! 53: do_test lock-1.7.2 {
! 54: catchsql {SELECT * FROM t1} db2
! 55: } {0 {1 2}}
! 56: do_test lock-1.8 {
! 57: execsql {UPDATE t1 SET a=b, b=a} db2
! 58: execsql {SELECT * FROM t1} db2
! 59: } {2 1}
! 60: do_test lock-1.9 {
! 61: execsql {SELECT * FROM t1}
! 62: } {2 1}
! 63: do_test lock-1.10 {
! 64: execsql {BEGIN TRANSACTION}
! 65: execsql {UPDATE t1 SET a = 0 WHERE 0}
! 66: execsql {SELECT * FROM t1}
! 67: } {2 1}
! 68: do_test lock-1.11 {
! 69: catchsql {SELECT * FROM t1} db2
! 70: } {0 {2 1}}
! 71: do_test lock-1.12 {
! 72: execsql {ROLLBACK}
! 73: catchsql {SELECT * FROM t1}
! 74: } {0 {2 1}}
! 75:
! 76: do_test lock-1.13 {
! 77: execsql {CREATE TABLE t2(x int, y int)}
! 78: execsql {INSERT INTO t2 VALUES(8,9)}
! 79: execsql {SELECT * FROM t2}
! 80: } {8 9}
! 81: do_test lock-1.14.1 {
! 82: catchsql {SELECT * FROM t2} db2
! 83: } {0 {8 9}}
! 84: do_test lock-1.14.2 {
! 85: catchsql {SELECT * FROM t1} db2
! 86: } {0 {2 1}}
! 87: do_test lock-1.15 {
! 88: catchsql {SELECT * FROM t2} db2
! 89: } {0 {8 9}}
! 90:
! 91: do_test lock-1.16 {
! 92: db eval {SELECT * FROM t1} qv {
! 93: set x [db eval {SELECT * FROM t1}]
! 94: }
! 95: set x
! 96: } {2 1}
! 97: do_test lock-1.17 {
! 98: db eval {SELECT * FROM t1} qv {
! 99: set x [db eval {SELECT * FROM t2}]
! 100: }
! 101: set x
! 102: } {8 9}
! 103:
! 104: # You cannot UPDATE a table from within the callback of a SELECT
! 105: # on that same table because the SELECT has the table locked.
! 106: #
! 107: # 2006-08-16: Reads no longer block writes within the same
! 108: # database connection.
! 109: #
! 110: #do_test lock-1.18 {
! 111: # db eval {SELECT * FROM t1} qv {
! 112: # set r [catch {db eval {UPDATE t1 SET a=b, b=a}} msg]
! 113: # lappend r $msg
! 114: # }
! 115: # set r
! 116: #} {1 {database table is locked}}
! 117:
! 118: # But you can UPDATE a different table from the one that is used in
! 119: # the SELECT.
! 120: #
! 121: do_test lock-1.19 {
! 122: db eval {SELECT * FROM t1} qv {
! 123: set r [catch {db eval {UPDATE t2 SET x=y, y=x}} msg]
! 124: lappend r $msg
! 125: }
! 126: set r
! 127: } {0 {}}
! 128: do_test lock-1.20 {
! 129: execsql {SELECT * FROM t2}
! 130: } {9 8}
! 131:
! 132: # It is possible to do a SELECT of the same table within the
! 133: # callback of another SELECT on that same table because two
! 134: # or more read-only cursors can be open at once.
! 135: #
! 136: do_test lock-1.21 {
! 137: db eval {SELECT * FROM t1} qv {
! 138: set r [catch {db eval {SELECT a FROM t1}} msg]
! 139: lappend r $msg
! 140: }
! 141: set r
! 142: } {0 2}
! 143:
! 144: # Under UNIX you can do two SELECTs at once with different database
! 145: # connections, because UNIX supports reader/writer locks. Under windows,
! 146: # this is not possible.
! 147: #
! 148: if {$::tcl_platform(platform)=="unix"} {
! 149: do_test lock-1.22 {
! 150: db eval {SELECT * FROM t1} qv {
! 151: set r [catch {db2 eval {SELECT a FROM t1}} msg]
! 152: lappend r $msg
! 153: }
! 154: set r
! 155: } {0 2}
! 156: }
! 157: integrity_check lock-1.23
! 158:
! 159: # If one thread has a transaction another thread cannot start
! 160: # a transaction. -> Not true in version 3.0. But if one thread
! 161: # as a RESERVED lock another thread cannot acquire one.
! 162: #
! 163: do_test lock-2.1 {
! 164: execsql {BEGIN TRANSACTION}
! 165: execsql {UPDATE t1 SET a = 0 WHERE 0}
! 166: execsql {BEGIN TRANSACTION} db2
! 167: set r [catch {execsql {UPDATE t1 SET a = 0 WHERE 0} db2} msg]
! 168: execsql {ROLLBACK} db2
! 169: lappend r $msg
! 170: } {1 {database is locked}}
! 171:
! 172: # A thread can read when another has a RESERVED lock.
! 173: #
! 174: do_test lock-2.2 {
! 175: catchsql {SELECT * FROM t2} db2
! 176: } {0 {9 8}}
! 177:
! 178: # If the other thread (the one that does not hold the transaction with
! 179: # a RESERVED lock) tries to get a RESERVED lock, we do get a busy callback
! 180: # as long as we were not orginally holding a READ lock.
! 181: #
! 182: do_test lock-2.3.1 {
! 183: proc callback {count} {
! 184: set ::callback_value $count
! 185: break
! 186: }
! 187: set ::callback_value {}
! 188: db2 busy callback
! 189: # db2 does not hold a lock so we should get a busy callback here
! 190: set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]
! 191: lappend r $msg
! 192: lappend r $::callback_value
! 193: } {1 {database is locked} 0}
! 194: do_test lock-2.3.2 {
! 195: set ::callback_value {}
! 196: execsql {BEGIN; SELECT rowid FROM sqlite_master LIMIT 1} db2
! 197: # This time db2 does hold a read lock. No busy callback this time.
! 198: set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]
! 199: lappend r $msg
! 200: lappend r $::callback_value
! 201: } {1 {database is locked} {}}
! 202: catch {execsql {ROLLBACK} db2}
! 203: do_test lock-2.4.1 {
! 204: proc callback {count} {
! 205: lappend ::callback_value $count
! 206: if {$count>4} break
! 207: }
! 208: set ::callback_value {}
! 209: db2 busy callback
! 210: # We get a busy callback because db2 is not holding a lock
! 211: set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]
! 212: lappend r $msg
! 213: lappend r $::callback_value
! 214: } {1 {database is locked} {0 1 2 3 4 5}}
! 215: do_test lock-2.4.2 {
! 216: proc callback {count} {
! 217: lappend ::callback_value $count
! 218: if {$count>4} break
! 219: }
! 220: set ::callback_value {}
! 221: db2 busy callback
! 222: execsql {BEGIN; SELECT rowid FROM sqlite_master LIMIT 1} db2
! 223: # No busy callback this time because we are holding a lock
! 224: set r [catch {execsql {UPDATE t1 SET a=b, b=a} db2} msg]
! 225: lappend r $msg
! 226: lappend r $::callback_value
! 227: } {1 {database is locked} {}}
! 228: catch {execsql {ROLLBACK} db2}
! 229: do_test lock-2.5 {
! 230: proc callback {count} {
! 231: lappend ::callback_value $count
! 232: if {$count>4} break
! 233: }
! 234: set ::callback_value {}
! 235: db2 busy callback
! 236: set r [catch {execsql {SELECT * FROM t1} db2} msg]
! 237: lappend r $msg
! 238: lappend r $::callback_value
! 239: } {0 {2 1} {}}
! 240: execsql {ROLLBACK}
! 241:
! 242: # Test the built-in busy timeout handler
! 243: #
! 244: do_test lock-2.8 {
! 245: db2 timeout 400
! 246: execsql BEGIN
! 247: execsql {UPDATE t1 SET a = 0 WHERE 0}
! 248: catchsql {BEGIN EXCLUSIVE;} db2
! 249: } {1 {database is locked}}
! 250: do_test lock-2.9 {
! 251: db2 timeout 0
! 252: execsql COMMIT
! 253: } {}
! 254: integrity_check lock-2.10
! 255:
! 256: # Try to start two transactions in a row
! 257: #
! 258: do_test lock-3.1 {
! 259: execsql {BEGIN TRANSACTION}
! 260: set r [catch {execsql {BEGIN TRANSACTION}} msg]
! 261: execsql {ROLLBACK}
! 262: lappend r $msg
! 263: } {1 {cannot start a transaction within a transaction}}
! 264: integrity_check lock-3.2
! 265:
! 266: # Make sure the busy handler and error messages work when
! 267: # opening a new pointer to the database while another pointer
! 268: # has the database locked.
! 269: #
! 270: do_test lock-4.1 {
! 271: db2 close
! 272: catch {db eval ROLLBACK}
! 273: db eval BEGIN
! 274: db eval {UPDATE t1 SET a=0 WHERE 0}
! 275: sqlite3 db2 ./test.db
! 276: catchsql {UPDATE t1 SET a=0} db2
! 277: } {1 {database is locked}}
! 278: do_test lock-4.2 {
! 279: set ::callback_value {}
! 280: set rc [catch {db2 eval {UPDATE t1 SET a=0}} msg]
! 281: lappend rc $msg $::callback_value
! 282: } {1 {database is locked} {}}
! 283: do_test lock-4.3 {
! 284: proc callback {count} {
! 285: lappend ::callback_value $count
! 286: if {$count>4} break
! 287: }
! 288: db2 busy callback
! 289: set rc [catch {db2 eval {UPDATE t1 SET a=0}} msg]
! 290: lappend rc $msg $::callback_value
! 291: } {1 {database is locked} {0 1 2 3 4 5}}
! 292: execsql {ROLLBACK}
! 293:
! 294: # When one thread is writing, other threads cannot read. Except if the
! 295: # writing thread is writing to its temporary tables, the other threads
! 296: # can still read. -> Not so in 3.0. One thread can read while another
! 297: # holds a RESERVED lock.
! 298: #
! 299: proc tx_exec {sql} {
! 300: db2 eval $sql
! 301: }
! 302: do_test lock-5.1 {
! 303: execsql {
! 304: SELECT * FROM t1
! 305: }
! 306: } {2 1}
! 307: do_test lock-5.2 {
! 308: db function tx_exec tx_exec
! 309: catchsql {
! 310: INSERT INTO t1(a,b) SELECT 3, tx_exec('SELECT y FROM t2 LIMIT 1');
! 311: }
! 312: } {0 {}}
! 313:
! 314: ifcapable tempdb {
! 315: do_test lock-5.3 {
! 316: execsql {
! 317: CREATE TEMP TABLE t3(x);
! 318: SELECT * FROM t3;
! 319: }
! 320: } {}
! 321: do_test lock-5.4 {
! 322: catchsql {
! 323: INSERT INTO t3 SELECT tx_exec('SELECT y FROM t2 LIMIT 1');
! 324: }
! 325: } {0 {}}
! 326: do_test lock-5.5 {
! 327: execsql {
! 328: SELECT * FROM t3;
! 329: }
! 330: } {8}
! 331: do_test lock-5.6 {
! 332: catchsql {
! 333: UPDATE t1 SET a=tx_exec('SELECT x FROM t2');
! 334: }
! 335: } {0 {}}
! 336: do_test lock-5.7 {
! 337: execsql {
! 338: SELECT * FROM t1;
! 339: }
! 340: } {9 1 9 8}
! 341: do_test lock-5.8 {
! 342: catchsql {
! 343: UPDATE t3 SET x=tx_exec('SELECT x FROM t2');
! 344: }
! 345: } {0 {}}
! 346: do_test lock-5.9 {
! 347: execsql {
! 348: SELECT * FROM t3;
! 349: }
! 350: } {9}
! 351: }
! 352:
! 353: do_test lock-6.1 {
! 354: execsql {
! 355: CREATE TABLE t4(a PRIMARY KEY, b);
! 356: INSERT INTO t4 VALUES(1, 'one');
! 357: INSERT INTO t4 VALUES(2, 'two');
! 358: INSERT INTO t4 VALUES(3, 'three');
! 359: }
! 360:
! 361: set STMT [sqlite3_prepare $DB "SELECT * FROM sqlite_master" -1 TAIL]
! 362: sqlite3_step $STMT
! 363:
! 364: execsql { DELETE FROM t4 }
! 365: execsql { SELECT * FROM sqlite_master } db2
! 366: execsql { SELECT * FROM t4 } db2
! 367: } {}
! 368:
! 369: do_test lock-6.2 {
! 370: execsql {
! 371: BEGIN;
! 372: INSERT INTO t4 VALUES(1, 'one');
! 373: INSERT INTO t4 VALUES(2, 'two');
! 374: INSERT INTO t4 VALUES(3, 'three');
! 375: COMMIT;
! 376: }
! 377:
! 378: execsql { SELECT * FROM t4 } db2
! 379: } {1 one 2 two 3 three}
! 380:
! 381: do_test lock-6.3 {
! 382: execsql { SELECT a FROM t4 ORDER BY a } db2
! 383: } {1 2 3}
! 384:
! 385: do_test lock-6.4 {
! 386: execsql { PRAGMA integrity_check } db2
! 387: } {ok}
! 388:
! 389: do_test lock-6.5 {
! 390: sqlite3_finalize $STMT
! 391: } {SQLITE_OK}
! 392:
! 393: # At one point the following set of conditions would cause SQLite to
! 394: # retain a RESERVED or EXCLUSIVE lock after the transaction was committed:
! 395: #
! 396: # * The journal-mode is set to something other than 'delete', and
! 397: # * there exists one or more active read-only statements, and
! 398: # * a transaction that modified zero database pages is committed.
! 399: #
! 400: set temp_status unlocked
! 401: if {$TEMP_STORE>=2} {set temp_status unknown}
! 402: do_test lock-7.1 {
! 403: set STMT [sqlite3_prepare $DB "SELECT * FROM sqlite_master" -1 TAIL]
! 404: sqlite3_step $STMT
! 405: } {SQLITE_ROW}
! 406: do_test lock-7.2 {
! 407: execsql { PRAGMA lock_status }
! 408: } [list main shared temp $temp_status]
! 409: do_test lock-7.3 {
! 410: execsql {
! 411: PRAGMA journal_mode = truncate;
! 412: BEGIN;
! 413: UPDATE t4 SET a = 10 WHERE 0;
! 414: COMMIT;
! 415: }
! 416: execsql { PRAGMA lock_status }
! 417: } [list main shared temp $temp_status]
! 418: do_test lock-7.4 {
! 419: sqlite3_finalize $STMT
! 420: } {SQLITE_OK}
! 421:
! 422: do_test lock-999.1 {
! 423: rename db2 {}
! 424: } {}
! 425:
! 426: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to lock.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test