Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add some simple tests and fixes for shared-schema locking. (CVS 2869) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
d054bed15aee3edef93cc84c83be443c |
User & Date: | danielk1977 2006-01-06 13:00:29.000 |
Context
2006-01-06
| ||
14:32 | Change the OS interface layer to use traditional direct function call implementations instead of the more complicated virtual function table. Omit the asynchronous I/O demo. (CVS 2870) (check-in: 2529c2e11f user: drh tags: trunk) | |
13:00 | Add some simple tests and fixes for shared-schema locking. (CVS 2869) (check-in: d054bed15a user: danielk1977 tags: trunk) | |
12:03 | Back out the addition of the new header file - bad idea. (CVS 2868) (check-in: 0c4c45c36f user: drh tags: trunk) | |
Changes
Changes to src/btree.c.
1 2 3 4 5 6 7 8 9 10 11 | /* ** 2004 April 6 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | /* ** 2004 April 6 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** $Id: btree.c,v 1.282 2006/01/06 13:00:29 danielk1977 Exp $ ** ** This file implements a external (disk-based) database using BTrees. ** For a detailed discussion of BTrees, refer to ** ** Donald E. Knuth, THE ART OF COMPUTER PROGRAMMING, Volume 3: ** "Sorting And Searching", pages 473-480. Addison-Wesley ** Publishing Company, Reading, Massachusetts. |
︙ | ︙ | |||
597 598 599 600 601 602 603 | return rc; } /* ** Query to see if btree handle p may obtain a lock of type eLock ** (READ_LOCK or WRITE_LOCK) on the table with root-page iTab. Return ** SQLITE_OK if the lock may be obtained (by calling lockTable()), or | | | 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 | return rc; } /* ** Query to see if btree handle p may obtain a lock of type eLock ** (READ_LOCK or WRITE_LOCK) on the table with root-page iTab. Return ** SQLITE_OK if the lock may be obtained (by calling lockTable()), or ** SQLITE_LOCKED if not. */ static int queryTableLock(Btree *p, Pgno iTab, u8 eLock){ BtShared *pBt = p->pBt; BtLock *pIter; /* This is a no-op if the shared-cache is not enabled */ if( 0==sqlite3Tsd()->useSharedData ){ |
︙ | ︙ | |||
631 632 633 634 635 636 637 | 0==(p->pSqlite->flags&SQLITE_ReadUncommitted) || eLock==WRITE_LOCK || iTab==MASTER_ROOT ){ for(pIter=pBt->pLock; pIter; pIter=pIter->pNext){ if( pIter->pBtree!=p && pIter->iTable==iTab && (pIter->eLock!=eLock || eLock!=READ_LOCK) ){ | | | 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 | 0==(p->pSqlite->flags&SQLITE_ReadUncommitted) || eLock==WRITE_LOCK || iTab==MASTER_ROOT ){ for(pIter=pBt->pLock; pIter; pIter=pIter->pNext){ if( pIter->pBtree!=p && pIter->iTable==iTab && (pIter->eLock!=eLock || eLock!=READ_LOCK) ){ return SQLITE_LOCKED; } } } return SQLITE_OK; } /* |
︙ | ︙ | |||
6479 6480 6481 6482 6483 6484 6485 6486 6487 6488 6489 6490 6491 6492 | BtShared *pBt = p->pBt; if( !pBt->pSchema ){ pBt->pSchema = sqliteMalloc(nBytes); pBt->xFreeSchema = xFree; } return pBt->pSchema; } #ifndef SQLITE_OMIT_SHARED_CACHE /* ** Enable the shared pager and schema features. */ int sqlite3_enable_shared_cache(int enable){ SqliteTsd *pTsd = sqlite3Tsd(); | > > > > > > > > | 6479 6480 6481 6482 6483 6484 6485 6486 6487 6488 6489 6490 6491 6492 6493 6494 6495 6496 6497 6498 6499 6500 | BtShared *pBt = p->pBt; if( !pBt->pSchema ){ pBt->pSchema = sqliteMalloc(nBytes); pBt->xFreeSchema = xFree; } return pBt->pSchema; } /* ** Return true if another user of the same shared btree as the argument ** handle holds an exclusive lock on the sqlite_master table. */ int sqlite3BtreeSchemaLocked(Btree *p){ return (queryTableLock(p, MASTER_ROOT, READ_LOCK)!=SQLITE_OK); } #ifndef SQLITE_OMIT_SHARED_CACHE /* ** Enable the shared pager and schema features. */ int sqlite3_enable_shared_cache(int enable){ SqliteTsd *pTsd = sqlite3Tsd(); |
︙ | ︙ |
Changes to src/btree.h.
︙ | ︙ | |||
9 10 11 12 13 14 15 | ** May you share freely, never taking more than you give. ** ************************************************************************* ** This header file defines the interface that the sqlite B-Tree file ** subsystem. See comments in the source code for a detailed description ** of what each interface routine does. ** | | | 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | ** May you share freely, never taking more than you give. ** ************************************************************************* ** This header file defines the interface that the sqlite B-Tree file ** subsystem. See comments in the source code for a detailed description ** of what each interface routine does. ** ** @(#) $Id: btree.h,v 1.68 2006/01/06 13:00:30 danielk1977 Exp $ */ #ifndef _BTREE_H_ #define _BTREE_H_ /* TODO: This definition is just included so other modules compile. It ** needs to be revisited. */ |
︙ | ︙ | |||
73 74 75 76 77 78 79 80 81 82 83 84 85 86 | int sqlite3BtreeCommitStmt(Btree*); int sqlite3BtreeRollbackStmt(Btree*); int sqlite3BtreeCreateTable(Btree*, int*, int flags); int sqlite3BtreeIsInTrans(Btree*); int sqlite3BtreeIsInStmt(Btree*); int sqlite3BtreeSync(Btree*, const char *zMaster); void *sqlite3BtreeSchema(Btree *, int, void(*)(void *)); const char *sqlite3BtreeGetFilename(Btree *); const char *sqlite3BtreeGetDirname(Btree *); const char *sqlite3BtreeGetJournalname(Btree *); int sqlite3BtreeCopyFile(Btree *, Btree *); /* The flags parameter to sqlite3BtreeCreateTable can be the bitwise OR | > | 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 | int sqlite3BtreeCommitStmt(Btree*); int sqlite3BtreeRollbackStmt(Btree*); int sqlite3BtreeCreateTable(Btree*, int*, int flags); int sqlite3BtreeIsInTrans(Btree*); int sqlite3BtreeIsInStmt(Btree*); int sqlite3BtreeSync(Btree*, const char *zMaster); void *sqlite3BtreeSchema(Btree *, int, void(*)(void *)); int sqlite3BtreeSchemaLocked(Btree *); const char *sqlite3BtreeGetFilename(Btree *); const char *sqlite3BtreeGetDirname(Btree *); const char *sqlite3BtreeGetJournalname(Btree *); int sqlite3BtreeCopyFile(Btree *, Btree *); /* The flags parameter to sqlite3BtreeCreateTable can be the bitwise OR |
︙ | ︙ |
Changes to src/prepare.c.
︙ | ︙ | |||
9 10 11 12 13 14 15 | ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains the implementation of the sqlite3_prepare() ** interface, and routines that contribute to loading the database schema ** from disk. ** | | | 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains the implementation of the sqlite3_prepare() ** interface, and routines that contribute to loading the database schema ** from disk. ** ** $Id: prepare.c,v 1.14 2006/01/06 13:00:30 danielk1977 Exp $ */ #include "sqliteInt.h" #include "os.h" #include <ctype.h> /* ** Fill the InitData structure with an error message that indicates |
︙ | ︙ | |||
432 433 434 435 436 437 438 439 | int nBytes, /* Length of zSql in bytes. */ sqlite3_stmt **ppStmt, /* OUT: A pointer to the prepared statement */ const char** pzTail /* OUT: End of parsed string */ ){ Parse sParse; char *zErrMsg = 0; int rc = SQLITE_OK; | > | > > > > > > > > > > > > > | 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 | int nBytes, /* Length of zSql in bytes. */ sqlite3_stmt **ppStmt, /* OUT: A pointer to the prepared statement */ const char** pzTail /* OUT: End of parsed string */ ){ Parse sParse; char *zErrMsg = 0; int rc = SQLITE_OK; int i; assert( !sqlite3Tsd()->mallocFailed ); assert( ppStmt ); *ppStmt = 0; if( sqlite3SafetyOn(db) ){ return SQLITE_MISUSE; } /* If any attached database schemas are locked, do not proceed with ** compilation. Instead return SQLITE_LOCKED immediately. */ for(i=0; i<db->nDb; i++) { Btree *pBt = db->aDb[i].pBt; if( pBt && sqlite3BtreeSchemaLocked(pBt) ){ const char *zDb = db->aDb[i].zName; sqlite3Error(db, SQLITE_LOCKED, "database schema is locked: %s", zDb); sqlite3SafetyOff(db); return SQLITE_LOCKED; } } memset(&sParse, 0, sizeof(sParse)); sParse.db = db; sqlite3RunParser(&sParse, zSql, &zErrMsg); if( sqlite3Tsd()->mallocFailed ){ sParse.rc = SQLITE_NOMEM; } |
︙ | ︙ | |||
520 521 522 523 524 525 526 | const void **pzTail /* OUT: End of parsed string */ ){ /* This function currently works by first transforming the UTF-16 ** encoded string to UTF-8, then invoking sqlite3_prepare(). The ** tricky bit is figuring out the pointer to return in *pzTail. */ char *zSql8 = 0; | | | 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 | const void **pzTail /* OUT: End of parsed string */ ){ /* This function currently works by first transforming the UTF-16 ** encoded string to UTF-8, then invoking sqlite3_prepare(). The ** tricky bit is figuring out the pointer to return in *pzTail. */ char *zSql8 = 0; const char *zTail8 = 0; int rc; if( sqlite3SafetyCheck(db) ){ return SQLITE_MISUSE; } zSql8 = sqlite3utf16to8(zSql, nBytes); if( !zSql8 ){ |
︙ | ︙ |
Changes to test/shared.test.
1 2 3 4 5 6 7 8 9 10 11 12 13 | # 2005 December 30 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the SELECT statement. # | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | # 2005 December 30 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the SELECT statement. # # $Id: shared.test,v 1.4 2006/01/06 13:00:30 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl db close ifcapable !shared_cache { finish_test |
︙ | ︙ | |||
69 70 71 72 73 74 75 | do_test shared-1.4 { # Try to insert a row into abc via connection 2. This should fail because # of the read-lock connection 1 is holding on table abc (obtained in the # previous test case). catchsql { INSERT INTO abc VALUES(4, 5, 6); } db2 | | | | 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 | do_test shared-1.4 { # Try to insert a row into abc via connection 2. This should fail because # of the read-lock connection 1 is holding on table abc (obtained in the # previous test case). catchsql { INSERT INTO abc VALUES(4, 5, 6); } db2 } {1 {database table is locked}} do_test shared-1.5 { # Using connection 2 (the one without the open transaction), try to create # a new table. This should fail because of the open read transaction # held by connection 1. catchsql { CREATE TABLE def(d, e, f); } db2 } {1 {database table is locked}} do_test shared-1.6 { # Upgrade connection 1's transaction to a write transaction. Create # a new table - def - and insert a row into it. Because the connection 1 # transaction modifies the schema, it should not be possible for # connection 2 to access the database at all until the connection 1 # has finished the transaction. execsql { |
︙ | ︙ | |||
100 101 102 103 104 105 106 | # in the previous test case. execsql { SELECT * FROM sqlite_master; } catchsql { SELECT * FROM sqlite_master; } db2 | | | 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 | # in the previous test case. execsql { SELECT * FROM sqlite_master; } catchsql { SELECT * FROM sqlite_master; } db2 } {1 {database schema is locked: main}} do_test shared-1.8 { # Commit the connection 1 transaction. execsql { COMMIT; } } {} |
︙ | ︙ | |||
129 130 131 132 133 134 135 | execsql { BEGIN; SELECT * FROM abc; } db2 catchsql { INSERT INTO abc VALUES(1, 2, 3); } db2 | | | 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 | execsql { BEGIN; SELECT * FROM abc; } db2 catchsql { INSERT INTO abc VALUES(1, 2, 3); } db2 } {1 {database table is locked}} do_test shared-2.3 { # Turn db's transaction into a write-transaction. db3 should still be # able to read from table def (but will not see the new row). Connection # db2 should not be able to read def (because of the write-lock). # Todo: The failed "INSERT INTO abc ..." statement in the above test # has started a write-transaction on db2 (should this be so?). This |
︙ | ︙ | |||
153 154 155 156 157 158 159 | INSERT INTO def VALUES('VII', 'VIII', 'IX'); } concat [ catchsql { SELECT * FROM def; } db3 ] [ catchsql { SELECT * FROM def; } db2 ] | | | 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 | INSERT INTO def VALUES('VII', 'VIII', 'IX'); } concat [ catchsql { SELECT * FROM def; } db3 ] [ catchsql { SELECT * FROM def; } db2 ] } {0 {IV V VI} 1 {database table is locked}} do_test shared-2.4 { # Commit the open transaction on db. db2 still holds a read-transaction. # This should prevent db3 from writing to the database, but not from # reading. execsql { COMMIT; } |
︙ | ︙ | |||
253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 | set sqlite_open_file_count } {2} do_test shared-4.1.3 { execsql {ATTACH 'test.db' AS test} db2 set sqlite_open_file_count } {2} do_test shared-4.2.1 { execsql { CREATE TABLE abc(a, b, c); INSERT INTO abc VALUES('i', 'ii', 'iii'); } } {} do_test shared-4.2.2 { execsql { SELECT * FROM test.abc; } db2 } {i ii iii} catch {db2 close} catch {db close} finish_test sqlite3_enable_shared_cache $::enable_shared_cache | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 | set sqlite_open_file_count } {2} do_test shared-4.1.3 { execsql {ATTACH 'test.db' AS test} db2 set sqlite_open_file_count } {2} # Sanity check: Create a table in ./test.db via handle db, and test that handle # db2 can "see" the new table immediately. A handle using a seperate pager # cache would have to reload the database schema before this were possible. # do_test shared-4.2.1 { execsql { CREATE TABLE abc(a, b, c); CREATE TABLE def(d, e, f); INSERT INTO abc VALUES('i', 'ii', 'iii'); INSERT INTO def VALUES('I', 'II', 'III'); } } {} do_test shared-4.2.2 { execsql { SELECT * FROM test.abc; } db2 } {i ii iii} # Open a read-transaction and read from table abc via handle 2. Check that # handle 1 can read table abc. Check that handle 1 cannot modify table abc # or the database schema. Then check that handle 1 can modify table def. # do_test shared-4.3.1 { execsql { BEGIN; SELECT * FROM test.abc; } db2 } {i ii iii} do_test shared-4.3.2 { catchsql { INSERT INTO abc VALUES('iv', 'v', 'vi'); } } {1 {database table is locked}} do_test shared-4.3.3 { catchsql { CREATE TABLE ghi(g, h, i); } } {1 {database table is locked}} do_test shared-4.3.3 { catchsql { INSERT INTO def VALUES('IV', 'V', 'VI'); } } {0 {}} do_test shared-4.3.4 { # Cleanup: commit the transaction opened by db2. execsql { COMMIT } db2 } {} # Open a write-transaction using handle 1 and modify the database schema. # Then try to execute a compiled statement to read from the same # database via handle 2 (fails to get the lock on sqlite_master). Also # try to compile a read of the same database using handle 2 (also fails). # Finally, compile a read of the other database using handle 2. This # should also fail. # do_test shared-4.4.1.2 { # Sanity check 1: Check that the schema is what we think it is when viewed # via handle 1. execsql { CREATE TABLE test2.ghi(g, h, i); SELECT 'test.db:'||name FROM sqlite_master UNION ALL SELECT 'test2.db:'||name FROM test2.sqlite_master; } } {test.db:abc test.db:def test2.db:ghi} do_test shared-4.4.1.2 { # Sanity check 2: Check that the schema is what we think it is when viewed # via handle 2. execsql { SELECT 'test2.db:'||name FROM sqlite_master UNION ALL SELECT 'test.db:'||name FROM test.sqlite_master; } db2 } {test2.db:ghi test.db:abc test.db:def} do_test shared-4.4.2 { set ::DB2 [sqlite3_connection_pointer db2] set sql {SELECT * FROM abc} set ::STMT1 [sqlite3_prepare $::DB2 $sql -1 DUMMY] execsql { BEGIN; CREATE TABLE jkl(j, k, l); } sqlite3_step $::STMT1 } {SQLITE_ERROR} do_test shared-4.4.3 { sqlite3_finalize $::STMT1 } {SQLITE_LOCKED} do_test shared-4.4.4 { set rc [catch { set ::STMT1 [sqlite3_prepare $::DB2 $sql -1 DUMMY] } msg] list $rc $msg } {1 {(6) database schema is locked: test}} do_test shared-4.4.5 { set rc [catch { set ::STMT1 [sqlite3_prepare $::DB2 "SELECT * FROM ghi" -1 DUMMY] } msg] list $rc $msg } {1 {(6) database schema is locked: test}} catch {db2 close} catch {db close} finish_test sqlite3_enable_shared_cache $::enable_shared_cache |
Changes to www/sharedcache.tcl.
︙ | ︙ | |||
116 117 118 119 120 121 122 | read-lock, write-lock or no lock on each database table. </p> <p>At any one time, a single table may have any number of active read-locks or a single active write lock. To read data a table, a connection must first obtain a read-lock. To write to a table, a connection must obtain a write-lock on that table. If a required table lock cannot be obtained, | | < < < | 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 | read-lock, write-lock or no lock on each database table. </p> <p>At any one time, a single table may have any number of active read-locks or a single active write lock. To read data a table, a connection must first obtain a read-lock. To write to a table, a connection must obtain a write-lock on that table. If a required table lock cannot be obtained, the query fails and SQLITE_LOCKED is returned to the caller. </p> <p>Once a connection obtains a table lock, it is not released until the current transaction (read or write) is concluded. </p> } HEADING 3 {Read-Uncommitted Isolation Mode} |
︙ | ︙ | |||
177 178 179 180 181 182 183 | <ul> <li>A connection must obtain a read-lock on <i>sqlite_master</i> before accessing any database tables or obtaining any other read or write locks.</li> <li>Before executing a statement that modifies the database schema (i.e. a CREATE or DROP TABLE statement), a connection must obtain a write-lock on <i>sqlite_master</i>. </li> | | | > > > > > > > > > > > > > > > > > > > > | 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 | <ul> <li>A connection must obtain a read-lock on <i>sqlite_master</i> before accessing any database tables or obtaining any other read or write locks.</li> <li>Before executing a statement that modifies the database schema (i.e. a CREATE or DROP TABLE statement), a connection must obtain a write-lock on <i>sqlite_master</i>. </li> <li>A connection may not compile an SQL statement if any other connection is holding a write-lock on the <i>sqlite_master</i> table of any attached database (including the default database, "main"). </li> </ul> } HEADING 3 {Schema locking and attached databases} puts { <p>The final point in the bullet list is deceptively complicated when multiple databases are attached to connections. Exactly when is access to a specific database schema "required" to compile a statement? The way in which SQLite resolves the names of schema objects (i.e. tables, indices, triggers and views) depends on whether or not the name was qualified or unqualified in the original SQL statement. The first statement below uses a qualified table name, the second uses an unqualified table name. Both refer to the same underlying table. </p> <pre> SELECT name FROM main.sqlite_master; SELECT name FROM sqlite_master; </pre> 1 } HEADING 1 {Thread Related Issues} puts { <p>When shared-cache mode is enabled, a database connection may only be used by the thread that called sqlite3_open() to create it. If another thread attempts to use the database connection, in most cases an |
︙ | ︙ |