Hello,
The script below fails with
Deadlock detected when executing 'DELETE FROM foo WHERE id=2'
What I think should be happening instead is this:
- When executing statement 1, the main thread obtains a SHARED lock.
- When executing statement 2, the main thread briefly obtains an
EXCLUSIVE lock. After statement 2 is executed, the EXCLUSIVE lock is
released and the main thread continues to hold the SHARED lock (since
statement 1 is still active)
- Thread 2 wants to get an EXCLUSIVE lock but it can't. So the busy
handlers waits for the main thread to release it's lock.
- The main thread once again briefly obtains an EXCLUSIVE lock to
execute statement 4. After that it releases all locks.
- Now thread 2 can execute statement 3.
Obviously, in practice something else is happening. Can someone explain
what and why this is?
This is the code:
----snip----
#include <stdio.h>
#include <assert.h>
#include "sqlite3.h"
#include <unistd.h>
#include <stdlib.h>
#include <pthread.h>
// gcc -c test1.c
// gcc -o test1 test1.o sqlite3.o -lpthread -ldl
void open_db(sqlite3** db)
{
int rc;
rc=sqlite3_open("mydb.db", db);
assert(rc==SQLITE_OK);
rc=sqlite3_busy_timeout(*db, 30*1000);
assert(rc==SQLITE_OK);
}
void execute(char* s, sqlite3* db)
{
sqlite3_stmt *stmt=NULL;
int rc;
rc=sqlite3_prepare_v2(db, s, -1, &stmt, NULL);
assert(rc==SQLITE_OK);
rc=sqlite3_step(stmt);
if (rc == SQLITE_BUSY) {
printf("Deadlock detected when executing '%s'\n", s);
exit(1);
}
if(rc!=SQLITE_DONE) {
printf("Error executing '%s': %s\n", s, sqlite3_errmsg(db));
exit(1);
}
rc=sqlite3_finalize(stmt);
assert(rc==SQLITE_OK);
}
void* execute_statement_3(void *threadid)
{
sqlite3 *db;
int rc;
open_db(&db);
execute("UPDATE foo SET id=42 WHERE id=5", db);
rc=sqlite3_close(db);
if (rc != SQLITE_OK )
printf("Error closing db: %s\n", sqlite3_errmsg(db));
pthread_exit(NULL);
}
int main(int argc, char **argv)
{
int rc;
sqlite3 *db;
char *error=NULL;
sqlite3_stmt *stmt=NULL;
pthread_t thread2;
open_db(&db);
rc=sqlite3_exec(db,
"CREATE TABLE foo(id INTEGER);"
"INSERT INTO foo VALUES(1); "
"INSERT INTO foo VALUES(2); "
"INSERT INTO foo VALUES(3); "
"INSERT INTO foo VALUES(4); "
"INSERT INTO foo VALUES(5)",
NULL, NULL, &error);
assert(rc==SQLITE_OK);
/* Statement 1 */
rc=sqlite3_prepare_v2(db, "SELECT id FROM foo", -1, &stmt, NULL);
assert(rc==SQLITE_OK);
rc=sqlite3_step(stmt);
assert(rc==SQLITE_ROW);
/* Statement 2 */
execute("DELETE FROM foo WHERE id=1", db);
/* Statement 3 executes in a separate thread */
rc = pthread_create(&thread2, NULL, execute_statement_3, NULL);
assert(rc == 0);
sleep(1);
/* Statement 4 */
execute("DELETE FROM foo WHERE id=2", db);
/* Finalize */
rc=sqlite3_finalize(stmt);
assert(rc==SQLITE_OK);
rc=sqlite3_close(db);
if (rc != SQLITE_OK )
printf("Error closing db: %s\n", sqlite3_errmsg(db));
pthread_exit(NULL);
}
----snip----
Thanks,
-Nikolaus
--
»Time flies like an arrow, fruit flies like a Banana.«
PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
