[sqlite] Deadlock when doing threaded updates and inserts

[Dave Hayden]

I'm running into a deadlock, as the subject says, when doing updates on 
a table in one thread while another thread is inserting into the same 
table. (Oh, and this is on 3.0.4, compiled with --enable-threadsafe)

The update thread returns from its UPDATE command (within a 
transaction) with SQLITE_BUSY when it sees a pending lock. The insert 
thread returns SQLITE_BUSY from END TRANSACTION when it can't get an 
exclusive lock.

Attached is a simple C program that demonstrates this. I open two 
database handles on the same file (with a table "test" with a single 
column "num") and do:

db1: BEGIN TRANSACTION;
db2: BEGIN TRANSACTION;
db1: INSERT INTO test VALUES ( 1 );
At this point, both of these return SQLITE_BUSY:
db2: UPDATE test SET num = 2 WHERE num = 1;
db1: END TRANSACTION;
Is this a bug? Or do I have to do something with sqlite 3 I didn't with 
2?

Thanks,
-Dave
#include <stdio.h>
#include <stdlib.h>
#include <unistd.h>
#include "sqlite3.h"

int execQuery(sqlite3* db, char* query)
{
  char* err;
  int rc = sqlite3_exec(db, query, NULL, NULL, &err);

  if ( rc != SQLITE_OK )
  {
    printf("sqlite3_exec error: %s\n", err);
    sqlite3_free(err);
    return 0;
  }

  return 1;
}


int main()
{
  sqlite3* db1;
  sqlite3* db2;
  int rc; 

  unlink("./test.db");
  unlink("./test.db-journal");

  rc = sqlite3_open("test.db", &db1);

  if ( rc != SQLITE_OK )
  {
    printf("thread: Couldn't open database1\n");
    exit(-1);
  }
  
  execQuery(db1, "CREATE TABLE test ( num int );");

  rc = sqlite3_open("test.db", &db2);

  if ( rc != SQLITE_OK )
  {
    printf("thread: Couldn't open database2\n");
    exit(-1);
  }

  execQuery(db1, "BEGIN TRANSACTION;");
  execQuery(db2, "BEGIN TRANSACTION;");

  execQuery(db1, "INSERT INTO test VALUES ( 1 );");

  while ( !execQuery(db2, "UPDATE test SET num = 2 WHERE num = 1;") &&
          !execQuery(db2, "END TRANSACTION;") &&
          !execQuery(db1, "END TRANSACTION;") )
    usleep(1000);

  execQuery(db2, "END TRANSACTION;");

  return 0;
}