SQLite

From the forum discussion, in rollback mode, if a COMMIT command fails with SQLITE_BUSY, but is retried repeatedly, it will eventually appear to succeed, when it fact it is still busy and the transaction never committed.

This problem appears to have been introduced by check-in [2361b03b61311aab] for version 3.17.0 on 2017-02-13.

A test program that demonstrates the problem is as follows:

#include <stdio.h>
#include "sqlite3.h"
#include <assert.h>
#include <stdlib.h>

extern int unlink(const char*);

int main(int argc, char **argv){
  sqlite3 *db1;
  sqlite3 *db2;
  sqlite3_stmt *pStmt1;
  sqlite3_stmt *pStmt2;
  int i;
  int rc;

  unlink("test.db");
  rc = sqlite3_open("test.db", &db1);
  assert( rc==SQLITE_OK );
  rc = sqlite3_exec(db1, 
     "CREATE TABLE t1(a,b,c);\n"
     "INSERT INTO t1 VALUES(1,2,3),('a','b','c'),(9,8,7);\n",
     0, 0, 0);
  assert( rc==SQLITE_OK );
  rc = sqlite3_open("test.db", &db2);
  assert( rc==SQLITE_OK );
  rc = sqlite3_prepare_v2(db2, "SELECT * FROM t1;", -1, &pStmt2, 0);
  assert( rc==SQLITE_OK );
  rc = sqlite3_prepare_v2(db1, "COMMIT", -1, &pStmt1, 0);
  assert( rc==SQLITE_OK );

  /* Thread2 starts reading the database */
  printf("T2 reading....\n"); fflush(stdout);
  rc = sqlite3_step(pStmt2);
  assert( rc==SQLITE_ROW );

  /* Thread1 tries to update.  Should get blocked */
  printf("T1 writes\n"); fflush(stdout);
  rc = sqlite3_exec(db1, "BEGIN; INSERT INTO t1 VALUES(99,88,77)", 0, 0, 0);
  for(i=0; i<10; i++){
    rc = sqlite3_step(pStmt1);
    if( rc!=SQLITE_BUSY ) break;
    printf("  .... busy %d\n", i+1);
  }
  printf("return code from the COMMIT: %d\n", rc);
  rc = sqlite3_finalize(pStmt1);
  printf("return code from sqlite3_finalize(): %d\n", rc);
  printf("Database content after COMMIT:\n");
  rc = sqlite3_prepare_v2(db1, "SELECT * FROM t1;", -1, &pStmt1, 0);
  assert( rc==SQLITE_OK );
  while( sqlite3_step(pStmt1)==SQLITE_ROW ){
    printf("Row:");
    for(i=0; i<sqlite3_column_count(pStmt1); i++){
      printf(" c[%d]='%s'", i, sqlite3_column_text(pStmt1,i));
    }
    printf("\n");
  }
  sqlite3_finalize(pStmt1);
  sqlite3_finalize(pStmt2);
  sqlite3_close(db1);
  sqlite3_close(db2);
  return 0;
}

Test cases in TH3.