SQLite Forum

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