I don't know if this is of any use...but it appears that there are some
RD/WRLCKs that are not released. In particular 825 and 826 get read/write
locks but I don't see them ever UNLCK them.
They appear to be related to paging and I'm completely unsure as to how relevan
this is.
I compiled thusly:
cc -o thread2 thread2.c -DSQLITE_LOCK_TRACE -DSQLITE_DEBUG sqlite3.c -lpthread
-ldl
fcntl unknown 3 1 0
fcntl unknown 3 2 0
fcntl 1963703968 3 SETLK RDLCK 1073741824 1 0 0
fcntl 1963703968 3 SETLK RDLCK 1073741826 510 0 0
fcntl 1963703968 3 SETLK UNLCK 1073741824 1 0 0
fcntl 1963703968 3 GETLK WRLCK 1073741825 1 0 0
fcntl 1963703968 3 SETLK WRLCK 1073741825 1 400669632 0
fcntl 1963703968 3 SETLK RDLCK 1073741826 510 1963544576 0
fcntl 1963703968 3 SETLK UNLCK 1073741824 2 1963544576 0
fcntl 1963703968 3 SETLK UNLCK 0 0 400669632 0
fcntl 1963703968 3 SETLK RDLCK 1073741824 1 7109056 0
fcntl 1963703968 3 SETLK RDLCK 1073741826 510 7109056 0
fcntl 1963703968 3 SETLK UNLCK 1073741824 1 7109056 0
fcntl 1963703968 3 SETLK WRLCK 1073741825 1 0 0
fcntl unknown 5 1 0
fcntl unknown 5 2 0
fcntl unknown 4 1 0
fcntl unknown 4 2 0
fcntl 1963703968 3 SETLK WRLCK 1073741824 1 0 0
fcntl 1963703968 3 SETLK WRLCK 1073741826 510 0 0
fcntl 1963703968 3 SETLK RDLCK 1073741826 510 7109056 0
fcntl 1963703968 3 SETLK UNLCK 1073741824 2 7109056 0
fcntl 1963703968 3 SETLK UNLCK 0 0 4217775 0
fcntl 1963703968 3 SETLK RDLCK 1073741824 1 4641153 0
fcntl 1963703968 3 SETLK RDLCK 1073741826 510 4641153 0
fcntl 1963703968 3 SETLK UNLCK 1073741824 1 4641153 0
fcntl 1963703968 3 SETLK WRLCK 1073741825 1 390890896 0
fcntl unknown 5 1 0
fcntl unknown 5 2 0
fcntl unknown 4 1 0
fcntl unknown 4 2 0
fcntl 1963703968 3 SETLK WRLCK 1073741824 1 0 0
fcntl 1963703968 3 SETLK WRLCK 1073741826 510 0 0
fcntl 1963703968 3 SETLK RDLCK 1073741826 510 7109056 0
fcntl 1963703968 3 SETLK UNLCK 1073741824 2 7109056 0
fcntl 1963703968 3 SETLK UNLCK 0 0 4217775 0
fcntl 1963703968 3 SETLK RDLCK 1073741824 1 4641153 0
fcntl 1963703968 3 SETLK RDLCK 1073741826 510 4641153 0
fcntl 1963703968 3 SETLK UNLCK 1073741824 1 4641153 0
fcntl 1963703968 3 SETLK WRLCK 1073741825 1 390890896 0
fcntl unknown 5 1 0
fcntl unknown 5 2 0
fcntl unknown 4 1 0
fcntl unknown 4 2 0
fcntl 1963703968 3 SETLK WRLCK 1073741824 1 0 0
fcntl 1963703968 3 SETLK WRLCK 1073741826 510 0 0
fcntl 1963703968 3 SETLK RDLCK 1073741826 510 7109056 0
fcntl 1963703968 3 SETLK UNLCK 1073741824 2 7109056 0
fcntl 1963703968 3 SETLK UNLCK 0 0 4217775 0
fcntl 1963703968 3 SETLK RDLCK 1073741824 1 4641153 0
fcntl 1963703968 3 SETLK RDLCK 1073741826 510 4641153 0
fcntl 1963703968 3 SETLK UNLCK 1073741824 1 4641153 0
fcntl 1963703968 3 SETLK WRLCK 1073741825 1 390890896 0
fcntl unknown 5 1 0
fcntl unknown 5 2 0
fcntl unknown 4 1 0
fcntl unknown 4 2 0
fcntl 1963703968 3 SETLK WRLCK 1073741824 1 0 0
fcntl 1963703968 3 SETLK WRLCK 1073741826 510 0 0
fcntl 1963703968 3 SETLK RDLCK 1073741826 510 7109056 0
fcntl 1963703968 3 SETLK UNLCK 1073741824 2 7109056 0
fcntl 1963703968 3 SETLK UNLCK 0 0 4217775 0
fcntl 1963703968 3 SETLK RDLCK 1073741824 1 4641153 0
fcntl 1963703968 3 SETLK RDLCK 1073741826 510 4641153 0
fcntl 1963703968 3 SETLK UNLCK 1073741824 1 4641153 0
fcntl 1963703968 3 SETLK WRLCK 1073741825 1 390890896 0
fcntl unknown 5 1 0
fcntl unknown 5 2 0
fcntl unknown 4 1 0
fcntl unknown 4 2 0
fcntl 1963703968 3 SETLK WRLCK 1073741824 1 0 0
fcntl 1963703968 3 SETLK WRLCK 1073741826 510 0 0
fcntl 1963703968 3 SETLK RDLCK 1073741826 510 7109056 0
fcntl 1963703968 3 SETLK UNLCK 1073741824 2 7109056 0
fcntl 1963703968 3 SETLK UNLCK 0 0 4217775 0
fcntl 1963703968 3 SETLK RDLCK 1073741824 1 4641153 0
fcntl 1963703968 3 SETLK RDLCK 1073741826 510 4641153 0
fcntl 1963703968 3 SETLK UNLCK 1073741824 1 4641153 0
fcntl 1963703968 3 SETLK WRLCK 1073741825 1 390890896 0
fcntl unknown 5 1 0
fcntl unknown 5 2 0
fcntl unknown 4 1 0
fcntl unknown 4 2 0
fcntl 1963703968 3 SETLK WRLCK 1073741824 1 0 0
fcntl 1963703968 3 SETLK WRLCK 1073741826 510 0 0
fcntl 1963703968 3 SETLK RDLCK 1073741826 510 7109056 0
fcntl 1963703968 3 SETLK UNLCK 1073741824 2 7109056 0
fcntl 1963703968 3 SETLK UNLCK 0 0 4217775 0
fcntl 1963703968 3 SETLK RDLCK 1073741824 1 7109056 0
fcntl 1963703968 3 SETLK RDLCK 1073741826 510 7109056 0
fcntl 1963703968 3 SETLK UNLCK 1073741824 1 7109056 0
fcntl 1963703968 3 SETLK WRLCK 1073741825 1 390894312 0
fcntl unknown 5 1 0
fcntl unknown 5 2 0
fcntl unknown 4 1 0
fcntl unknown 4 2 0
fcntl 1963703968 3 SETLK WRLCK 1073741824 1 0 0
fcntl 1963703968 3 SETLK WRLCK 1073741826 510 0 0
fcntl 1963703968 3 SETLK RDLCK 1073741826 510 7109056 0
fcntl 1963703968 3 SETLK UNLCK 1073741824 2 7109056 0
fcntl unknown 4 1 0
fcntl unknown 4 2 0
fcntl 1090562368 4 SETLK WRLCK 1073741825 1 390958064 0
fcntl unknown 6 1 0
fcntl unknown 6 2 0
fcntl unknown 5 1 0
fcntl unknown 5 2 0
fcntl 1090562368 4 SETLK WRLCK 1073741824 1 0 0
Deadlock detected when executing 'DELETE FROM foo WHERE id=2'
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
________________________________
From: sqlite-users-boun...@sqlite.org on behalf of Nikolaus Rath
Sent: Mon 8/16/2010 1:48 PM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:[sqlite] Why the deadlock?
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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
