SQLite

View Ticket
Login
Ticket Hash: 810dc8038872e212361f31daf83ebf32484f3f9f
Title: COMMIT command succeeds even though it didn't commit.
Status: Fixed Type: Code_Defect
Severity: Critical Priority: High
Subsystem: Virtual_Machine Resolution: Fixed
Last Modified: 2020-06-03 19:31:24
Version Found In: 3.32.1
User Comments:
drh added on 2020-06-03 19:24:59:

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;
}


drh added on 2020-06-03 19:31:24:

Test cases in TH3.