SQLite

Check-in [3cd2da42e9]
Login

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: 3cd2da42e9403b1e6243ad53f3f2bbf89c0fb9b0
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
Unified Diff Ignore Whitespace Patch
Changes to ext/fts3/fts3.c.
2980
2981
2982
2983
2984
2985
2986
2987
2988
2989
2990
2991
2992
2993
2994
2995
2996
2997
2998
        p->azColumn, p->bFts4, p->nColumn, iCol, zQuery, -1, &pCsr->pExpr, 
        &p->base.zErrMsg
    );
    if( rc!=SQLITE_OK ){
      return rc;
    }

    rc = sqlite3Fts3ReadLock(p);
    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







<
<
<

<







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
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
523
524
525
526
527
528
529
    rc = sqlite3_reset(pStmt);
  }
  *pRC = rc;
}


/*
** This function ensures that the caller has obtained a shared-cache
** table-lock on the %_content table. This is required before reading
** data from the fts3 table. If this lock is not acquired first, then
** the caller may end up holding read-locks on the %_segments and %_segdir
** tables, but no read-lock on the %_content table. If this happens 
** a second connection will be able to write to the fts3 table, but
** attempting to commit those writes might return SQLITE_LOCKED or
** SQLITE_LOCKED_SHAREDCACHE (because the commit attempts to obtain 
** write-locks on the %_segments and %_segdir ** tables). 
**
** We try 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 reads data directly from the %_segments or
** %_segdir tables instead of going through FTS3 though.
**
** This reasoning does not apply to a content=xxx table.
*/
int sqlite3Fts3ReadLock(Fts3Table *p){
  int rc;                         /* Return code */
  sqlite3_stmt *pStmt;            /* Statement used to obtain lock */

  if( p->zContentTbl==0 ){

    rc = fts3SqlStmt(p, SQL_SELECT_CONTENT_BY_ROWID, &pStmt, 0);
    if( rc==SQLITE_OK ){
      sqlite3_bind_null(pStmt, 1);
      sqlite3_step(pStmt);
      rc = sqlite3_reset(pStmt);
    }
  }else{
    rc = SQLITE_OK;
  }

  return rc;
}

/*
** FTS maintains a separate indexes for each language-id (a 32-bit integer).







|
|
|
|
<
<
|
|
<

|
|
|
|
|
<
<

|
|
<
|
|
>
|





<
<







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(?,?,?,?,?,?)"