Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | When writing to an FTS table, take an exclusive shared-cache lock on the %_segdir table before writing. Otherwise, an xCommit() call may report an SQLITE_LOCKED error. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
3cd2da42e9403b1e6243ad53f3f2bbf8 |
User & Date: | dan 2013-05-27 10:11:53.308 |
Context
2013-05-28
| ||
10:52 | Merge autoconf-package branch with trunk. (check-in: a72f50ef76 user: dan tags: trunk) | |
2013-05-27
| ||
17:19 | Add the files used to build the amalgamation-autoconf package. (check-in: 048865e8fd user: dan tags: autoconf-package) | |
10:11 | When writing to an FTS table, take an exclusive shared-cache lock on the %_segdir table before writing. Otherwise, an xCommit() call may report an SQLITE_LOCKED error. (check-in: 3cd2da42e9 user: dan tags: trunk) | |
2013-05-24
| ||
12:47 | Set _XOPEN_SOURCE to 600 so that fchmod() will (hopefully) work on FreeBSD. (check-in: 61a1045239 user: drh tags: trunk) | |
Changes
Changes to ext/fts3/fts3.c.
︙ | ︙ | |||
2980 2981 2982 2983 2984 2985 2986 | p->azColumn, p->bFts4, p->nColumn, iCol, zQuery, -1, &pCsr->pExpr, &p->base.zErrMsg ); if( rc!=SQLITE_OK ){ return rc; } | < < < < | 2980 2981 2982 2983 2984 2985 2986 2987 2988 2989 2990 2991 2992 2993 2994 | p->azColumn, p->bFts4, p->nColumn, iCol, zQuery, -1, &pCsr->pExpr, &p->base.zErrMsg ); if( rc!=SQLITE_OK ){ return rc; } rc = fts3EvalStart(pCsr); sqlite3Fts3SegmentsClose(p); if( rc!=SQLITE_OK ) return rc; pCsr->pNextId = pCsr->aDoclist; pCsr->iPrevId = 0; } /* Compile a SELECT statement for this cursor. For a full-table-scan, the |
︙ | ︙ |
Changes to ext/fts3/fts3_write.c.
︙ | ︙ | |||
485 486 487 488 489 490 491 | rc = sqlite3_reset(pStmt); } *pRC = rc; } /* | | | | | < < | | < | | | | | < < | | < | | > | < < | 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 | rc = sqlite3_reset(pStmt); } *pRC = rc; } /* ** This function ensures that the caller has obtained an exclusive ** shared-cache table-lock on the %_segdir table. This is required before ** writing data to the fts3 table. If this lock is not acquired first, then ** the caller may end up attempting to take this lock as part of committing ** a transaction, causing SQLite to return SQLITE_LOCKED or ** LOCKED_SHAREDCACHEto a COMMIT command. ** ** It is best to avoid this because if FTS3 returns any error when ** committing a transaction, the whole transaction will be rolled back. ** And this is not what users expect when they get SQLITE_LOCKED_SHAREDCACHE. ** It can still happen if the user locks the underlying tables directly ** instead of accessing them via FTS. */ static int fts3Writelock(Fts3Table *p){ int rc = SQLITE_OK; if( p->nPendingData==0 ){ sqlite3_stmt *pStmt; rc = fts3SqlStmt(p, SQL_DELETE_SEGDIR_LEVEL, &pStmt, 0); if( rc==SQLITE_OK ){ sqlite3_bind_null(pStmt, 1); sqlite3_step(pStmt); rc = sqlite3_reset(pStmt); } } return rc; } /* ** FTS maintains a separate indexes for each language-id (a 32-bit integer). |
︙ | ︙ | |||
5293 5294 5295 5296 5297 5298 5299 5300 5301 5302 5303 5304 5305 5306 | if( aSzDel==0 ){ rc = SQLITE_NOMEM; goto update_out; } aSzIns = &aSzDel[p->nColumn+1]; memset(aSzDel, 0, sizeof(aSzDel[0])*(p->nColumn+1)*2); /* If this is an INSERT operation, or an UPDATE that modifies the rowid ** value, then this operation requires constraint handling. ** ** If the on-conflict mode is REPLACE, this means that the existing row ** should be deleted from the database before inserting the new row. Or, ** if the on-conflict mode is other than REPLACE, then this method must ** detect the conflict and return SQLITE_CONSTRAINT before beginning to | > > > | 5286 5287 5288 5289 5290 5291 5292 5293 5294 5295 5296 5297 5298 5299 5300 5301 5302 | if( aSzDel==0 ){ rc = SQLITE_NOMEM; goto update_out; } aSzIns = &aSzDel[p->nColumn+1]; memset(aSzDel, 0, sizeof(aSzDel[0])*(p->nColumn+1)*2); rc = fts3Writelock(p); if( rc!=SQLITE_OK ) goto update_out; /* If this is an INSERT operation, or an UPDATE that modifies the rowid ** value, then this operation requires constraint handling. ** ** If the on-conflict mode is REPLACE, this means that the existing row ** should be deleted from the database before inserting the new row. Or, ** if the on-conflict mode is other than REPLACE, then this method must ** detect the conflict and return SQLITE_CONSTRAINT before beginning to |
︙ | ︙ |
Changes to test/fts3shared.test.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | # 2010 September 17 # # 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. # #*********************************************************************** # set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !fts3||!shared_cache { finish_test return } db close set ::enable_shared_cache [sqlite3_enable_shared_cache 1] # Open two connections to the database in shared-cache mode. # sqlite3 db test.db | > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | # # 2010 September 17 # # 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 the interactions between the FTS3/4 module # and shared-cache mode. # set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !fts3||!shared_cache { finish_test return } set ::testprefix fts3shared db close set ::enable_shared_cache [sqlite3_enable_shared_cache 1] # Open two connections to the database in shared-cache mode. # sqlite3 db test.db |
︙ | ︙ | |||
63 64 65 66 67 68 69 70 71 72 | # Verify that the second connection still has an open transaction. # do_test fts3shared-1.6 { sqlite3_get_autocommit db2 } 0 db close db2 close sqlite3_enable_shared_cache $::enable_shared_cache finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 | # Verify that the second connection still has an open transaction. # do_test fts3shared-1.6 { sqlite3_get_autocommit db2 } 0 db close db2 close #------------------------------------------------------------------------- # The following tests - fts3shared-2.* - test that unless FTS is bypassed # and the underlying tables accessed directly, it is not possible for an # SQLITE_LOCKED error to be enountered when committing an FTS transaction. # # Any SQLITE_LOCKED error should be returned when the fts4 (or fts4aux) # table is first read/written within a transaction, not later on. # set LOCKED {1 {database table is locked}} forcedelete test.db sqlite3 dbR test.db sqlite3 dbW test.db do_test 2.1 { execsql { CREATE VIRTUAL TABLE t1 USING fts4; CREATE TABLE t2ext(a, b); CREATE VIRTUAL TABLE t2 USING fts4(content=t2ext); CREATE VIRTUAL TABLE t1aux USING fts4aux(t1); CREATE VIRTUAL TABLE t2aux USING fts4aux(t2); INSERT INTO t1 VALUES('a b c'); INSERT INTO t2(rowid, a, b) VALUES(1, 'd e f', 'g h i'); } dbW } {} # Test that once [dbW] has written to the FTS table, no client may read # from the FTS or fts4aux table. do_test 2.2.1 { execsql { BEGIN; INSERT INTO t1 VALUES('j k l'); } dbW execsql BEGIN dbR } {} do_test 2.2.2 { catchsql "SELECT * FROM t1 WHERE rowid=1" dbR } $LOCKED do_test 2.2.3 { catchsql "SELECT * FROM t1 WHERE t1 MATCH 'a'" dbR } $LOCKED do_test 2.2.4 { catchsql "SELECT rowid FROM t1 WHERE t1 MATCH 'a'" dbR } $LOCKED do_test 2.2.5 { catchsql "SELECT * FROM t1" dbR } $LOCKED do_test 2.2.6 { catchsql "SELECT * FROM t1aux" dbR } $LOCKED do_test 2.2.7 { execsql COMMIT dbW } {} do_test 2.2.8 { execsql COMMIT dbR } {} # Same test as 2.2.*, except with a content= table. # do_test 2.3.1 { execsql { BEGIN; INSERT INTO t2(rowid, a, b) VALUES(2, 'j k l', 'm n o'); } dbW execsql BEGIN dbR } {} do_test 2.3.3 { catchsql "SELECT * FROM t2 WHERE t2 MATCH 'a'" dbR } $LOCKED do_test 2.3.4 { catchsql "SELECT rowid FROM t2 WHERE t2 MATCH 'a'" dbR } $LOCKED do_test 2.3.6 { catchsql "SELECT * FROM t2aux" dbR } $LOCKED do_test 2.3.7 { execsql COMMIT dbW } {} do_test 2.3.8 { execsql COMMIT dbR } {} # Test that once a connection has read from the FTS or fts4aux table, # another connection may not write to the FTS table. # foreach {tn sql} { 1 "SELECT * FROM t1 WHERE rowid=1" 2 "SELECT * FROM t1 WHERE t1 MATCH 'a'" 3 "SELECT rowid FROM t1 WHERE t1 MATCH 'a'" 4 "SELECT * FROM t1" 5 "SELECT * FROM t1aux" } { do_test 2.4.$tn { execsql BEGIN dbR execsql $::sql dbR execsql BEGIN dbW catchsql "INSERT INTO t1 VALUES('p q r')" dbW } $LOCKED execsql ROLLBACK dbR execsql ROLLBACK dbW } # Same test as 2.4.*, except with a content= table. # foreach {tn sql} { 2 "SELECT * FROM t2 WHERE t2 MATCH 'a'" 3 "SELECT rowid FROM t2 WHERE t2 MATCH 'a'" 5 "SELECT * FROM t2aux" } { do_test 2.5.$tn { execsql BEGIN dbR execsql $::sql dbR execsql BEGIN dbW catchsql "INSERT INTO t2(rowid, a, b) VALUES(3, 's t u', 'v w x')" dbW } $LOCKED execsql ROLLBACK dbR execsql ROLLBACK dbW } dbW close dbR close sqlite3_enable_shared_cache $::enable_shared_cache finish_test |
Changes to test/trace2.test.
︙ | ︙ | |||
124 125 126 127 128 129 130 131 132 133 134 135 136 137 | INSERT INTO x1 VALUES('Wind chill values as low as -13'); } do_trace_test 2.2 { INSERT INTO x1 VALUES('North northwest wind between 8 and 14 mph'); } { "INSERT INTO x1 VALUES('North northwest wind between 8 and 14 mph');" "-- INSERT INTO 'main'.'x1_content' VALUES(?,(?))" "-- REPLACE INTO 'main'.'x1_docsize' VALUES(?,?)" "-- SELECT value FROM 'main'.'x1_stat' WHERE id=?" "-- REPLACE INTO 'main'.'x1_stat' VALUES(?,?)" "-- SELECT (SELECT max(idx) FROM 'main'.'x1_segdir' WHERE level = ?) + 1" "-- SELECT coalesce((SELECT max(blockid) FROM 'main'.'x1_segments') + 1, 1)" "-- REPLACE INTO 'main'.'x1_segdir' VALUES(?,?,?,?,?,?)" | > | 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 | INSERT INTO x1 VALUES('Wind chill values as low as -13'); } do_trace_test 2.2 { INSERT INTO x1 VALUES('North northwest wind between 8 and 14 mph'); } { "INSERT INTO x1 VALUES('North northwest wind between 8 and 14 mph');" "-- DELETE FROM 'main'.'x1_segdir' WHERE level = ?" "-- INSERT INTO 'main'.'x1_content' VALUES(?,(?))" "-- REPLACE INTO 'main'.'x1_docsize' VALUES(?,?)" "-- SELECT value FROM 'main'.'x1_stat' WHERE id=?" "-- REPLACE INTO 'main'.'x1_stat' VALUES(?,?)" "-- SELECT (SELECT max(idx) FROM 'main'.'x1_segdir' WHERE level = ?) + 1" "-- SELECT coalesce((SELECT max(blockid) FROM 'main'.'x1_segments') + 1, 1)" "-- REPLACE INTO 'main'.'x1_segdir' VALUES(?,?,?,?,?,?)" |
︙ | ︙ |