Hm nope your expectations are reasonable -- that should work fine using a separate connection for each thread.

How are you handling errors during COMMIT? Do you have a busy handler or a timeout configured?

In the default rollback journal mode, once the updates have run and the change is ready to commit¹, sqlite acquires the PENDING lock to indicate it is ready to modify the main db file, but it has to wait until all readers finish and relinquish their SHARED locks before it can acquire the EXCLUSIVE lock and actually write the new data.

If the busy timeout expires before the readers finish, then you will get an SQLITE_BUSY error. So if your policy on errors is to rollback then that would explain the behaviour you're seeing. The default configuration for a connection is no timeout, ie. you will immediately get SQLITE_BUSY if any readers are active. See https://www.sqlite.org/pragma.html#pragma_busy_timeout

¹ note this can also happen before commit time if your transaction is large enough to spill sqlite's memory cache; see https://www.sqlite.org/pragma.html#pragma_cache_spill

If the call which returned SQLITE_BUSY is from a different thread than the one which is using the DB exclusively, then retrying is indicated. Presumably, the busy state will end at some time. If not, or if the thread which creates the busy state is also making the call which returns SQLITE_BUSY, then you have a design flaw to correct.

You should set a timeout on each connection. This will allow SQLite to handle SQLITE_BUSY internally, not bothering your software but handling the wait-and-retry loop itself. Two threads accessing the same database very quickly will definitely block each other occasionally and setting a timeout is a fast and simple way to solve the problem.

For testing purposes I recommend you set a timeout on each connection to a minute. This will allow you to figure out what your software is doing. Once you have a working system you might bring the value down to five seconds or so.

https://sqlite.org/c3ref/busy_timeout.html

SQLITE_BUSY, sqlite3_get_autocommit(): 0

It looks like you have explicitly started a transaction (which disables autocommit). The documentation for sqlite3_step() says:

SQLITE_BUSY means that the database engine was unable to acquire the database locks it needs to do its job. <...> If the statement is not a COMMIT and occurs within an explicit transaction then you should rollback the transaction before continuing.

Perhaps this SQLITE_BUSY shouldn't be retried?

I wonder is there an easy way to download amalgamation betas chronologically between the releases? My "binary" search then will be able to find the change in question.

fossil bisect, then?

Steps:

1. Clone SQLite using Fossil and open it into a check-out directory.

2. Say:

   fossil bisect good version-3.16.2
   fossil bisect bad  version-3.17.0
   ./configure --with-whatever-args-you-need
   

3. make sqlite3.c

4. Copy sqlite3.c (the amalgamation) to wherever you build from normally, build and test your project, and make a verdict.

5. Either say fossil bisect good or fossil bisect bad to record your verdict and get a new version to test.

6. If Fossil reports that the bisect is complete, skip to next step; else goto 3.

7. Say fossil status and send the result here.

However, realize that there's a fair chance that the behavior was changed on purpose and won't be changed back. Examining the commit comment for the version reported in step 7 or the commit diff might help you to understand which is the case.

I have worked all day trying to reproduce this, but without success. Can you provide more detail, or perhaps a test case?

Never mind. I found a test case. Testing the patch now.

There is now a ticket for this problem and a fix has been checked in.

The following mutation does not seem to trigger any test failures for me (I tried running make test twice with this change):

Index: src/vdbe.c
==================================================================
--- src/vdbe.c
+++ src/vdbe.c
@@ -701,11 +701,10 @@
     ** sqlite3_column_text16() failed.  */
     goto no_mem;
   }
   assert( p->rc==SQLITE_OK || (p->rc&0xff)==SQLITE_BUSY );
   testcase( p->rc!=SQLITE_OK );
-  p->rc = SQLITE_OK;
   assert( p->bIsReader || p->readOnly!=0 );
   p->iCurrentTime = 0;
   assert( p->explain==0 );
   p->pResultSet = 0;
   db->busyHandler.nBusy = 0;

The test program in the ticket does seem to reproduce the issue with the above mutation.

Thanks. The public TCL tests to not verify this condition, but the proprietary TH3 tests do. So that should prevent a regression.

This particular problem is easier expressed in C code than in TCL code. And TH3 test cases can (optionally) be written in C, which is why I put the test case there.

Dan is a genius at writing innovative TCL test cases. He might go back in and add a case to the TCL test suite at some point. But even if he does not, the TH3 cases should prevent the problem from recurring.

but if sqlite3_busy_timeout was 3 seconds, and I tried to repeat the step, the issue seemed to return

Do you mean that "the issue seemed to return" even with the check-in applied, or only without the bug fix?

I made the test case below in TCL, which is partially based on the reproduction program in the ticket.

The test case demonstrates that if the bug fix is removed, the COMMIT would prematurely report SQLITE_OK, with the change not really committed.

I would love to see this or a similar test case added to the test suite and would be happy to sign a public domain statement, if needed.

# partially based on: test/lock.test

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Create an alternative connection to the database
#
do_test repeated-commit-busy-0 {
  sqlite3 db2 test.db
} {}

do_test repeated-commit-busy-1 {
  execsql {
    CREATE TABLE t1(a PRIMARY KEY, b);
  }

  execsql {
    INSERT INTO t1 VALUES(1, 'one');
    INSERT INTO t1 VALUES(2, 'two');
    INSERT INTO t1 VALUES(3, 'three');
  }

  execsql { SELECT * FROM t1 }
} {1 one 2 two 3 three}

do_test repeated-commit-busy-2 {
  set STMT2 [sqlite3_prepare db2 "SELECT * FROM t1" -1 TAIL]
  execsql { SELECT NULL } db2
} {{}}

do_test repeated-commit-busy-3 {
  set STMT1 [sqlite3_prepare db "COMMIT" -1 TAIL]
  execsql { SELECT NULL }
} {{}}

# start reading as if in thread 2
do_test repeated-commit-busy-4 {
  sqlite3_step $STMT2
} {SQLITE_ROW}

# update from thread 1,
# should be blocked as busy
do_test repeated-commit-busy-5 {
  execsql {
    BEGIN;
    INSERT INTO t1 VALUES(4, 'four');
  }
  sqlite3_step $STMT1
} {SQLITE_BUSY}

# should be blocked as busy over and over
for {set i 0} {$i < 10} {incr i} {
  do_test repeated-commit-busy-repeat-$i {
    sqlite3_step $STMT1
  } {SQLITE_BUSY}
}

# release the read lock
do_test repeated-commit-busy-finalize-1 {
  sqlite3_finalize $STMT2
} {SQLITE_OK}

# should now be able to commit the update
do_test repeated-commit-busy-finalize-2 {
  sqlite3_step $STMT1
} {SQLITE_DONE}

# and see the update
do_test repeated-commit-busy-final-check-1 {
  execsql { SELECT * FROM t1 }
} {1 one 2 two 3 three 4 four}