Re: [sqlite] Deadlock when doing threaded updates and inserts

[Richard Boulton]

I ran into a similar problem, I used the sqlite3_busy_timeout so that SQLite
automatically retries the locks. In 3.0.4 a change was made so that
SQLite doesn't retry a RESERVED lock (to avoid the deadlock), therefore if I
get a SQLITE_BUSY
return code I rollback the offending transaction and retry it, the
sqlite3_busy_timeout takes care of the other thread/process that's trying to
get the EXCLUSIVE lock.

I use sqlite3_prepare/sqlite3_step so I'm guessing it'll be similar with
sqlite3_exec. Here's an example of the logic I'm using:

sqlite3_busy_timeout(db, 5000)
do {
  rc = execQuery(db, "BEGIN TRANSACTION;");
  if (rc == SQLITE_DONE) rc = execQuery(db, "UPDATE test SET num = 2 WHERE
num = 1;");
  if (rc != SQLITE_DONE) {
    execQuery(db, "ROLLBACK TRANSACTION;");
  } else {
    execQuery(db, "COMMIT TRANSACTION;");
  }
  if (rc = SQLITE_BUSY) sleep(1);
} while(rc = SQLITE_BUSY)

I'm new to SQLite so I don't know if this is the "right" way to handle this,
maybe someone can comment on this?

Hope this helps,

Richard

----- Original Message ----- 
From: "Dave Hayden" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, August 11, 2004 2:12 AM
Subject: [sqlite] Deadlock when doing threaded updates and inserts


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