Database locking problems

classic Classic list List threaded Threaded
30 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Database locking problems

Andrew.Goth
I am having significant difficulty getting two process to not slam into each other when accessing the same database.  The behavior I want is for one process to wait while the database is busy being written to by the other, but instead I immediately get SQLITE_BUSY.  This is happening to me when using a local filesystem, so NFS is not the issue.  (Though I expect NFS to bite me soon enough.)

My trouble began on a Red Hat Enterprise Linux 6.4 system, though said computer exhibited other bizarre behaviors in the past (randomly unmounting filesystems and changing existing processes' current working directories to empty string), so I moved to Slackware64-current to make sure I could reproduce the problem in a modern environment.  Sure enough, I can.

sqlite3_busy_timeout(db, 5000) doesn't seem to help, not even when I add -DSQLITE_ENABLE_SETLK_TIMEOUT to my SQLite build command line.  sqlite3_step() doing an INSERT randomly fails to acquire the RESERVED lock.  I even tried writing my own retry-on-busy code (made several attempts), with no success.

I'm using SQLite 3.26.0 [bf8c1b2b7a] with GCC 8.2.0 on Linux 4.19.6, though I also had the same problems with GCC 4.4.7 on Linux 2.6.32-431.3.1.el6.x86_64.

Tracing through the SQLite amalgamation code with gdb, one thing I find odd is line 66334 doesn't seem to actually ever try calling btreeInvokeBusyHandler(pBt) despite rc being SQLITE_BUSY, since pbt->inTransaction isn't TRANS_NONE but rather TRANS_WRITE (I think).  This prevents it from waiting and/or retrying if busy, so the loop immediately bails, then SQLITE_BUSY rapidly percolates up to my application.

However, there's a chance my analysis may be invalid because I made my application pause with SIGSTOP on SQLITE_BUSY, at which point I attached gdb and forced it to call sqlite3_step() again so I could watch the execution path.  So maybe pbt->inTransaction was set sometime after unixFileLock() decided things went wrong.  But I highly doubt it.

Maybe a better debug strategy would have been to set a breakpoint in one process right when it acquires RESERVED, then start a second process in gdb and see what happens when it can't immediately get its own RESERVED lock.  (Or can't get SHARED because the first process had PENDING, etc.)  But it's midnight on Friday and I need to go home.

By the way, the SQLITE_LOCK_TRACE code seems to have a little bitrot.  I had to move the #define sqlite3DebugPrintf section earlier in the amalgamation, plus change the first %d on line 33121 to %ld because my pthread_t is a 64-bit value.

I created a test program that reproduces the issue, pasted at the bottom of this email.  (Are attachments allowed on this mailing list?  I sure hope my mailer doesn't eat my newlines.)  Sometimes it fails every time I try, sometimes I have to try it a dozen times.  To make it fail more frequently, uncomment more fork() calls.  (My original application on the Red Hat system failed every single time I ran two copies at once.)

No clue if this is of any value, but here's part of the output from SQLITE_LOCK_TRACE:

OPEN 11480672 test.db
fcntl 140340145452864 3 SETLK RDLCK 1073741824 1 4222915 0
fcntl 140340145452864 3 SETLK RDLCK 1073741826 510 4222915 0
fcntl 140340145452864 3 SETLK UNLCK 1073741824 1 4222915 0
FETCH 11480672 page 1 hash(00000000)
fcntl 140340145452864 3 SETLK UNLCK 0 0 4221434 0
fcntl 140340145452864 3 SETLK RDLCK 1073741824 1 0 0
fcntl 140340145452864 3 SETLK RDLCK 1073741826 510 0 0
fcntl 140340145452864 3 SETLK UNLCK 1073741824 1 0 0
FETCH 11480672 page 2 hash(00000000)
fcntl 140340145452864 3 SETLK WRLCK 1073741825 1 4273584 0
TRANSACTION 11480672
JOURNAL 11480672 page 2 needSync=1 hash(00000000)
fcntl 140340145452864 3 SETLK WRLCK 1073741824 1 2 0
fcntl 140340145452864 3 SETLK WRLCK 1073741826 510 2 -1
fcntl-failure-reason: RDLCK 1073741826 510 16020
fcntl 140340145452864 3 SETLK WRLCK 1073741826 510 2 -1
fcntl-failure-reason: RDLCK 1073741826 510 16020
(last two lines repeat)

Here's the test SQL being executed:

CREATE TABLE t(a, b, c);              -- Once only
BEGIN;
SELECT count(*) FROM t;               -- Used as :rows below
INSERT INTO t VALUES(:pid, :rows, 0); -- :pid is getpid()
INSERT INTO t VALUES(:pid, :rows, 1);
INSERT INTO t VALUES(:pid, :rows, 2);
COMMIT;

Here's test.c:

#include <signal.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/types.h>
#include <unistd.h>
#include "sqlite3.h"
#define ACTION raise(SIGSTOP)
/*#define ACTION exit(EXIT_FAILURE) */
#define die(format, ...) (fprintf(stderr, "(%d) %s:%d: " format "\n", \
        getpid(), strrchr("/" __FILE__, '/') + 1, __LINE__, ##__VA_ARGS__), \
        ACTION)
int main(void)
{
    sqlite3 *db;
    sqlite3_stmt *stmt;
    char *p;
    int i;
    int rows;
    int rc;
    /* Initialize database if the file does not exist. */
    if (access("test.db", F_OK) < 0) {
        if ((rc = sqlite3_open_v2("test.db", &db,
                SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
                NULL)) != SQLITE_OK) {
            die("sqlite3_open_v2: %s",
                    db ? sqlite3_errmsg(db) : sqlite3_errstr(rc));
        } else if (sqlite3_exec(db, "CREATE TABLE t(a, b, c)",
                NULL, NULL, &p) != SQLITE_OK) {
            die("sqlite3_exec: %s", p);
        } else if ((rc = sqlite3_close(db)) != SQLITE_OK) {
            die("sqlite3_close: %s", sqlite3_errstr(rc));
        }
    }
    /* Do everything simultaneously. */
    fork();
/*  fork();
    fork();
    fork(); Uncomment more of these for increased parallelism. */
    /* Open the database. */
    if ((rc = sqlite3_open_v2("test.db", &db,
            SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
            NULL)) != SQLITE_OK) {
        die("sqlite3_open_v2: %s",
                db ? sqlite3_errmsg(db) : sqlite3_errstr(rc));
    }
    /* Set busy timeout. */
    if (sqlite3_busy_timeout(db, 5000) != SQLITE_OK) {
        die("sqlite3_busy_timeout: %s", sqlite3_errmsg(db));
    }
    /* Begin the transaction. */
    if (sqlite3_exec(db, "BEGIN", NULL, NULL, &p) != SQLITE_OK) {
        die("sqlite3_exec: %s", p);
    }
    /* Get the number of rows already present in the table. */
    sqlite3_prepare_v2(db, "SELECT count(*) FROM t", -1, &stmt, NULL);
    if (!stmt) {
        die("sqlite3_prepare_v2: %s", sqlite3_errmsg(db));
    } else if (sqlite3_step(stmt) != SQLITE_ROW) {
        die("sqlite3_step: %s", sqlite3_errmsg(db));
    }
    rows = sqlite3_column_int(stmt, 0);
    if (sqlite3_finalize(stmt) != SQLITE_OK) {
        die("sqlite3_finalize: %s", sqlite3_errmsg(db));
    }
    /* Insert some rows. */
    sqlite3_prepare_v2(db, "INSERT INTO t VALUES(?, ?, ?)", -1, &stmt, NULL);
    if (!stmt) {
        die("sqlite3_prepare_v2: %s", sqlite3_errmsg(db));
    }
    for (i = 0; i < 3; ++i) {
        if (sqlite3_bind_int(stmt, 1, (int)getpid()) != SQLITE_OK) {
            die("sqlite3_bind_int: %s", sqlite3_errmsg(db));
        } else if (sqlite3_bind_int(stmt, 2, rows) != SQLITE_OK) {
            die("sqlite3_bind_int: %s", sqlite3_errmsg(db));
        } else if (sqlite3_bind_int(stmt, 3, i) != SQLITE_OK) {
            die("sqlite3_bind_int: %s", sqlite3_errmsg(db));
        } else if (sqlite3_step(stmt) != SQLITE_DONE) {
            die("sqlite3_step: %s", sqlite3_errmsg(db));
        } else if (sqlite3_reset(stmt) != SQLITE_OK) {
            die("sqlite3_reset: %s", sqlite3_errmsg(db));
        }
    }
    if (sqlite3_finalize(stmt) != SQLITE_OK) {
        die("sqlite3_finalize: %s", sqlite3_errmsg(db));
    }
    /* Commit the transaction. */
    if (sqlite3_exec(db, "COMMIT", NULL, NULL, &p) != SQLITE_OK) {
        die("sqlite3_exec: %s", p);
    }
    /* Close the database. */
    if ((rc = sqlite3_close(db)) != SQLITE_OK) {
        die("sqlite3_close: %s", sqlite3_errstr(rc));
    }
    /* Congratulations on making it this far. */
    return EXIT_SUCCESS;
}
/* vim: set sts=4 sw=4 tw=80 et ft=c: */

Here's the makefile:

CFLAGS += -ggdb3
CFLAGS += -Wall
test: LDFLAGS += -ldl
test: LDFLAGS += -lpthread
test: test.o sqlite3.o
test.o: test.c sqlite3.h
sqlite3.o: CPPFLAGS += -D_GNU_SOURCE
sqlite3.o: CPPFLAGS += -DHAVE_INTTYPES_H
sqlite3.o: CPPFLAGS += -DHAVE_STDINT_H
sqlite3.o: CPPFLAGS += -DHAVE_FDATASYNC
sqlite3.o: CPPFLAGS += -DHAVE_USLEEP
sqlite3.o: CPPFLAGS += -DHAVE_LOCALTIME_R
sqlite3.o: CPPFLAGS += -DHAVE_GMTIME_R
sqlite3.o: CPPFLAGS += -DHAVE_STRERROR_R
sqlite3.o: CPPFLAGS += -DHAVE_POSIX_FALLOCATE
#sqlite3.o: CPPFLAGS += -DSQLITE_ENABLE_SETLK_TIMEOUT
sqlite3.o: sqlite3.c
clean:
    -$(RM) test test.o sqlite3.o
# vim: set ts=4 sw=4 tw=80 noet ft=make:
------------------------------------------- CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use of the intended recipient and may contain material that is proprietary, confidential, privileged or otherwise legally protected or restricted under applicable government laws. Any review, disclosure, distributing or other use without expressed permission of the sender is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies without reading, printing, or saving..
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database locking problems

Scott
I'm a newbie on the block and this is my first  post, but have you considered 'threading'? This sounds like something that you need to create a thread to do. Creating a thread should then allow you to set parameters to wait until one process completes and switch back and forth. I've done this in VB and C# but I think you stated below you using Linux and C, which I have no clue... but I found a video here:  https://www.youtube.com/watch?v=rvbGbmXJ0f0
Scott ValleryEcclesiastes 4:9-10

    On Saturday, January 19, 2019, 7:11:57 AM EST, [hidden email] <[hidden email]> wrote:  
 
 I am having significant difficulty getting two process to not slam into each other when accessing the same database.  The behavior I want is for one process to wait while the database is busy being written to by the other, but instead I immediately get SQLITE_BUSY.  This is happening to me when using a local filesystem, so NFS is not the issue.  (Though I expect NFS to bite me soon enough.)

My trouble began on a Red Hat Enterprise Linux 6.4 system, though said computer exhibited other bizarre behaviors in the past (randomly unmounting filesystems and changing existing processes' current working directories to empty string), so I moved to Slackware64-current to make sure I could reproduce the problem in a modern environment.  Sure enough, I can.

sqlite3_busy_timeout(db, 5000) doesn't seem to help, not even when I add -DSQLITE_ENABLE_SETLK_TIMEOUT to my SQLite build command line.  sqlite3_step() doing an INSERT randomly fails to acquire the RESERVED lock.  I even tried writing my own retry-on-busy code (made several attempts), with no success.

I'm using SQLite 3.26.0 [bf8c1b2b7a] with GCC 8.2.0 on Linux 4.19.6, though I also had the same problems with GCC 4.4.7 on Linux 2.6.32-431.3.1.el6.x86_64.

Tracing through the SQLite amalgamation code with gdb, one thing I find odd is line 66334 doesn't seem to actually ever try calling btreeInvokeBusyHandler(pBt) despite rc being SQLITE_BUSY, since pbt->inTransaction isn't TRANS_NONE but rather TRANS_WRITE (I think).  This prevents it from waiting and/or retrying if busy, so the loop immediately bails, then SQLITE_BUSY rapidly percolates up to my application.

However, there's a chance my analysis may be invalid because I made my application pause with SIGSTOP on SQLITE_BUSY, at which point I attached gdb and forced it to call sqlite3_step() again so I could watch the execution path.  So maybe pbt->inTransaction was set sometime after unixFileLock() decided things went wrong.  But I highly doubt it.

Maybe a better debug strategy would have been to set a breakpoint in one process right when it acquires RESERVED, then start a second process in gdb and see what happens when it can't immediately get its own RESERVED lock.  (Or can't get SHARED because the first process had PENDING, etc.)  But it's midnight on Friday and I need to go home.

By the way, the SQLITE_LOCK_TRACE code seems to have a little bitrot.  I had to move the #define sqlite3DebugPrintf section earlier in the amalgamation, plus change the first %d on line 33121 to %ld because my pthread_t is a 64-bit value.

I created a test program that reproduces the issue, pasted at the bottom of this email.  (Are attachments allowed on this mailing list?  I sure hope my mailer doesn't eat my newlines.)  Sometimes it fails every time I try, sometimes I have to try it a dozen times.  To make it fail more frequently, uncomment more fork() calls.  (My original application on the Red Hat system failed every single time I ran two copies at once.)

No clue if this is of any value, but here's part of the output from SQLITE_LOCK_TRACE:

OPEN 11480672 test.db
fcntl 140340145452864 3 SETLK RDLCK 1073741824 1 4222915 0
fcntl 140340145452864 3 SETLK RDLCK 1073741826 510 4222915 0
fcntl 140340145452864 3 SETLK UNLCK 1073741824 1 4222915 0
FETCH 11480672 page 1 hash(00000000)
fcntl 140340145452864 3 SETLK UNLCK 0 0 4221434 0
fcntl 140340145452864 3 SETLK RDLCK 1073741824 1 0 0
fcntl 140340145452864 3 SETLK RDLCK 1073741826 510 0 0
fcntl 140340145452864 3 SETLK UNLCK 1073741824 1 0 0
FETCH 11480672 page 2 hash(00000000)
fcntl 140340145452864 3 SETLK WRLCK 1073741825 1 4273584 0
TRANSACTION 11480672
JOURNAL 11480672 page 2 needSync=1 hash(00000000)
fcntl 140340145452864 3 SETLK WRLCK 1073741824 1 2 0
fcntl 140340145452864 3 SETLK WRLCK 1073741826 510 2 -1
fcntl-failure-reason: RDLCK 1073741826 510 16020
fcntl 140340145452864 3 SETLK WRLCK 1073741826 510 2 -1
fcntl-failure-reason: RDLCK 1073741826 510 16020
(last two lines repeat)

Here's the test SQL being executed:

CREATE TABLE t(a, b, c);              -- Once only
BEGIN;
SELECT count(*) FROM t;              -- Used as :rows below
INSERT INTO t VALUES(:pid, :rows, 0); -- :pid is getpid()
INSERT INTO t VALUES(:pid, :rows, 1);
INSERT INTO t VALUES(:pid, :rows, 2);
COMMIT;

Here's test.c:

#include <signal.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/types.h>
#include <unistd.h>
#include "sqlite3.h"
#define ACTION raise(SIGSTOP)
/*#define ACTION exit(EXIT_FAILURE) */
#define die(format, ...) (fprintf(stderr, "(%d) %s:%d: " format "\n", \
        getpid(), strrchr("/" __FILE__, '/') + 1, __LINE__, ##__VA_ARGS__), \
        ACTION)
int main(void)
{
    sqlite3 *db;
    sqlite3_stmt *stmt;
    char *p;
    int i;
    int rows;
    int rc;
    /* Initialize database if the file does not exist. */
    if (access("test.db", F_OK) < 0) {
        if ((rc = sqlite3_open_v2("test.db", &db,
                SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
                NULL)) != SQLITE_OK) {
            die("sqlite3_open_v2: %s",
                    db ? sqlite3_errmsg(db) : sqlite3_errstr(rc));
        } else if (sqlite3_exec(db, "CREATE TABLE t(a, b, c)",
                NULL, NULL, &p) != SQLITE_OK) {
            die("sqlite3_exec: %s", p);
        } else if ((rc = sqlite3_close(db)) != SQLITE_OK) {
            die("sqlite3_close: %s", sqlite3_errstr(rc));
        }
    }
    /* Do everything simultaneously. */
    fork();
/*  fork();
    fork();
    fork(); Uncomment more of these for increased parallelism. */
    /* Open the database. */
    if ((rc = sqlite3_open_v2("test.db", &db,
            SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
            NULL)) != SQLITE_OK) {
        die("sqlite3_open_v2: %s",
                db ? sqlite3_errmsg(db) : sqlite3_errstr(rc));
    }
    /* Set busy timeout. */
    if (sqlite3_busy_timeout(db, 5000) != SQLITE_OK) {
        die("sqlite3_busy_timeout: %s", sqlite3_errmsg(db));
    }
    /* Begin the transaction. */
    if (sqlite3_exec(db, "BEGIN", NULL, NULL, &p) != SQLITE_OK) {
        die("sqlite3_exec: %s", p);
    }
    /* Get the number of rows already present in the table. */
    sqlite3_prepare_v2(db, "SELECT count(*) FROM t", -1, &stmt, NULL);
    if (!stmt) {
        die("sqlite3_prepare_v2: %s", sqlite3_errmsg(db));
    } else if (sqlite3_step(stmt) != SQLITE_ROW) {
        die("sqlite3_step: %s", sqlite3_errmsg(db));
    }
    rows = sqlite3_column_int(stmt, 0);
    if (sqlite3_finalize(stmt) != SQLITE_OK) {
        die("sqlite3_finalize: %s", sqlite3_errmsg(db));
    }
    /* Insert some rows. */
    sqlite3_prepare_v2(db, "INSERT INTO t VALUES(?, ?, ?)", -1, &stmt, NULL);
    if (!stmt) {
        die("sqlite3_prepare_v2: %s", sqlite3_errmsg(db));
    }
    for (i = 0; i < 3; ++i) {
        if (sqlite3_bind_int(stmt, 1, (int)getpid()) != SQLITE_OK) {
            die("sqlite3_bind_int: %s", sqlite3_errmsg(db));
        } else if (sqlite3_bind_int(stmt, 2, rows) != SQLITE_OK) {
            die("sqlite3_bind_int: %s", sqlite3_errmsg(db));
        } else if (sqlite3_bind_int(stmt, 3, i) != SQLITE_OK) {
            die("sqlite3_bind_int: %s", sqlite3_errmsg(db));
        } else if (sqlite3_step(stmt) != SQLITE_DONE) {
            die("sqlite3_step: %s", sqlite3_errmsg(db));
        } else if (sqlite3_reset(stmt) != SQLITE_OK) {
            die("sqlite3_reset: %s", sqlite3_errmsg(db));
        }
    }
    if (sqlite3_finalize(stmt) != SQLITE_OK) {
        die("sqlite3_finalize: %s", sqlite3_errmsg(db));
    }
    /* Commit the transaction. */
    if (sqlite3_exec(db, "COMMIT", NULL, NULL, &p) != SQLITE_OK) {
        die("sqlite3_exec: %s", p);
    }
    /* Close the database. */
    if ((rc = sqlite3_close(db)) != SQLITE_OK) {
        die("sqlite3_close: %s", sqlite3_errstr(rc));
    }
    /* Congratulations on making it this far. */
    return EXIT_SUCCESS;
}
/* vim: set sts=4 sw=4 tw=80 et ft=c: */

Here's the makefile:

CFLAGS += -ggdb3
CFLAGS += -Wall
test: LDFLAGS += -ldl
test: LDFLAGS += -lpthread
test: test.o sqlite3.o
test.o: test.c sqlite3.h
sqlite3.o: CPPFLAGS += -D_GNU_SOURCE
sqlite3.o: CPPFLAGS += -DHAVE_INTTYPES_H
sqlite3.o: CPPFLAGS += -DHAVE_STDINT_H
sqlite3.o: CPPFLAGS += -DHAVE_FDATASYNC
sqlite3.o: CPPFLAGS += -DHAVE_USLEEP
sqlite3.o: CPPFLAGS += -DHAVE_LOCALTIME_R
sqlite3.o: CPPFLAGS += -DHAVE_GMTIME_R
sqlite3.o: CPPFLAGS += -DHAVE_STRERROR_R
sqlite3.o: CPPFLAGS += -DHAVE_POSIX_FALLOCATE
#sqlite3.o: CPPFLAGS += -DSQLITE_ENABLE_SETLK_TIMEOUT
sqlite3.o: sqlite3.c
clean:
    -$(RM) test test.o sqlite3.o
# vim: set ts=4 sw=4 tw=80 noet ft=make:
------------------------------------------- CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use of the intended recipient and may contain material that is proprietary, confidential, privileged or otherwise legally protected or restricted under applicable government laws. Any review, disclosure, distributing or other use without expressed permission of the sender is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies without reading, printing, or saving..
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
 
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database locking problems

Richard Hipp-3
In reply to this post by Andrew.Goth
Thanks for the detailed trouble analysis!

Short answer:   Probably you want to do "BEGIN IMMEDIATE" rather than
just "BEGIN" to start your transaction.

The busy timeout is not working because you start out your transaction
using a read operation - the first SELECT statement - which gets a
read lock.  Later when you go to COMMIT, this has to elevate to a
write lock.  But SQLite sees that some other process has already
updated the database since you started your read.  In other words,
your attempted writes are based on stale data and will be rejected.
There is no point in waiting for the other process to finish its
transaction because you are not going to be able to write with stale
data anyhow, so it gives you an SQLITE_BUSY immediately.

By using BEGIN IMMEDIATE, your process reserves the right to write
immediately.  If the BEGIN IMMEDIATE is successful, then the COMMIT is
pretty much guaranteed to run without problems (assuming you don't run
out of disk space or have other highly unusual I/O problems).

If you are daring, you can also try building from the begin-concurrent
branch (https://www.sqlite.org/src/timeline?r=begin-concurrent) and
using "BEGIN CONCURRENT" instead of "BEGIN" to start your transaction.
In that case, the COMMIT might still fail with a conflict, but only if
the intervening transaction modified pages of the database file that
your transaction actually read.  So if the database has two tables,
and you are updating one table, and some other process is updating the
other table, then there is a high chance that both transaction will
run to completion.


On 1/19/19, [hidden email] <[hidden email]> wrote:

> I am having significant difficulty getting two process to not slam into each
> other when accessing the same database.  The behavior I want is for one
> process to wait while the database is busy being written to by the other,
> but instead I immediately get SQLITE_BUSY.  This is happening to me when
> using a local filesystem, so NFS is not the issue.  (Though I expect NFS to
> bite me soon enough.)
>
> My trouble began on a Red Hat Enterprise Linux 6.4 system, though said
> computer exhibited other bizarre behaviors in the past (randomly unmounting
> filesystems and changing existing processes' current working directories to
> empty string), so I moved to Slackware64-current to make sure I could
> reproduce the problem in a modern environment.  Sure enough, I can.
>
> sqlite3_busy_timeout(db, 5000) doesn't seem to help, not even when I add
> -DSQLITE_ENABLE_SETLK_TIMEOUT to my SQLite build command line.
> sqlite3_step() doing an INSERT randomly fails to acquire the RESERVED lock.
> I even tried writing my own retry-on-busy code (made several attempts), with
> no success.
>
> I'm using SQLite 3.26.0 [bf8c1b2b7a] with GCC 8.2.0 on Linux 4.19.6, though
> I also had the same problems with GCC 4.4.7 on Linux
> 2.6.32-431.3.1.el6.x86_64.
>
> Tracing through the SQLite amalgamation code with gdb, one thing I find odd
> is line 66334 doesn't seem to actually ever try calling
> btreeInvokeBusyHandler(pBt) despite rc being SQLITE_BUSY, since
> pbt->inTransaction isn't TRANS_NONE but rather TRANS_WRITE (I think).  This
> prevents it from waiting and/or retrying if busy, so the loop immediately
> bails, then SQLITE_BUSY rapidly percolates up to my application.
>
> However, there's a chance my analysis may be invalid because I made my
> application pause with SIGSTOP on SQLITE_BUSY, at which point I attached gdb
> and forced it to call sqlite3_step() again so I could watch the execution
> path.  So maybe pbt->inTransaction was set sometime after unixFileLock()
> decided things went wrong.  But I highly doubt it.
>
> Maybe a better debug strategy would have been to set a breakpoint in one
> process right when it acquires RESERVED, then start a second process in gdb
> and see what happens when it can't immediately get its own RESERVED lock.
> (Or can't get SHARED because the first process had PENDING, etc.)  But it's
> midnight on Friday and I need to go home.
>
> By the way, the SQLITE_LOCK_TRACE code seems to have a little bitrot.  I had
> to move the #define sqlite3DebugPrintf section earlier in the amalgamation,
> plus change the first %d on line 33121 to %ld because my pthread_t is a
> 64-bit value.
>
> I created a test program that reproduces the issue, pasted at the bottom of
> this email.  (Are attachments allowed on this mailing list?  I sure hope my
> mailer doesn't eat my newlines.)  Sometimes it fails every time I try,
> sometimes I have to try it a dozen times.  To make it fail more frequently,
> uncomment more fork() calls.  (My original application on the Red Hat system
> failed every single time I ran two copies at once.)
>
> No clue if this is of any value, but here's part of the output from
> SQLITE_LOCK_TRACE:
>
> OPEN 11480672 test.db
> fcntl 140340145452864 3 SETLK RDLCK 1073741824 1 4222915 0
> fcntl 140340145452864 3 SETLK RDLCK 1073741826 510 4222915 0
> fcntl 140340145452864 3 SETLK UNLCK 1073741824 1 4222915 0
> FETCH 11480672 page 1 hash(00000000)
> fcntl 140340145452864 3 SETLK UNLCK 0 0 4221434 0
> fcntl 140340145452864 3 SETLK RDLCK 1073741824 1 0 0
> fcntl 140340145452864 3 SETLK RDLCK 1073741826 510 0 0
> fcntl 140340145452864 3 SETLK UNLCK 1073741824 1 0 0
> FETCH 11480672 page 2 hash(00000000)
> fcntl 140340145452864 3 SETLK WRLCK 1073741825 1 4273584 0
> TRANSACTION 11480672
> JOURNAL 11480672 page 2 needSync=1 hash(00000000)
> fcntl 140340145452864 3 SETLK WRLCK 1073741824 1 2 0
> fcntl 140340145452864 3 SETLK WRLCK 1073741826 510 2 -1
> fcntl-failure-reason: RDLCK 1073741826 510 16020
> fcntl 140340145452864 3 SETLK WRLCK 1073741826 510 2 -1
> fcntl-failure-reason: RDLCK 1073741826 510 16020
> (last two lines repeat)
>
> Here's the test SQL being executed:
>
> CREATE TABLE t(a, b, c);              -- Once only
> BEGIN;
> SELECT count(*) FROM t;               -- Used as :rows below
> INSERT INTO t VALUES(:pid, :rows, 0); -- :pid is getpid()
> INSERT INTO t VALUES(:pid, :rows, 1);
> INSERT INTO t VALUES(:pid, :rows, 2);
> COMMIT;
>
> Here's test.c:
>
> #include <signal.h>
> #include <stdio.h>
> #include <stdlib.h>
> #include <string.h>
> #include <sys/types.h>
> #include <unistd.h>
> #include "sqlite3.h"
> #define ACTION raise(SIGSTOP)
> /*#define ACTION exit(EXIT_FAILURE) */
> #define die(format, ...) (fprintf(stderr, "(%d) %s:%d: " format "\n", \
>         getpid(), strrchr("/" __FILE__, '/') + 1, __LINE__, ##__VA_ARGS__),
> \
>         ACTION)
> int main(void)
> {
>     sqlite3 *db;
>     sqlite3_stmt *stmt;
>     char *p;
>     int i;
>     int rows;
>     int rc;
>     /* Initialize database if the file does not exist. */
>     if (access("test.db", F_OK) < 0) {
>         if ((rc = sqlite3_open_v2("test.db", &db,
>                 SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
>                 NULL)) != SQLITE_OK) {
>             die("sqlite3_open_v2: %s",
>                     db ? sqlite3_errmsg(db) : sqlite3_errstr(rc));
>         } else if (sqlite3_exec(db, "CREATE TABLE t(a, b, c)",
>                 NULL, NULL, &p) != SQLITE_OK) {
>             die("sqlite3_exec: %s", p);
>         } else if ((rc = sqlite3_close(db)) != SQLITE_OK) {
>             die("sqlite3_close: %s", sqlite3_errstr(rc));
>         }
>     }
>     /* Do everything simultaneously. */
>     fork();
> /*  fork();
>     fork();
>     fork(); Uncomment more of these for increased parallelism. */
>     /* Open the database. */
>     if ((rc = sqlite3_open_v2("test.db", &db,
>             SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
>             NULL)) != SQLITE_OK) {
>         die("sqlite3_open_v2: %s",
>                 db ? sqlite3_errmsg(db) : sqlite3_errstr(rc));
>     }
>     /* Set busy timeout. */
>     if (sqlite3_busy_timeout(db, 5000) != SQLITE_OK) {
>         die("sqlite3_busy_timeout: %s", sqlite3_errmsg(db));
>     }
>     /* Begin the transaction. */
>     if (sqlite3_exec(db, "BEGIN", NULL, NULL, &p) != SQLITE_OK) {
>         die("sqlite3_exec: %s", p);
>     }
>     /* Get the number of rows already present in the table. */
>     sqlite3_prepare_v2(db, "SELECT count(*) FROM t", -1, &stmt, NULL);
>     if (!stmt) {
>         die("sqlite3_prepare_v2: %s", sqlite3_errmsg(db));
>     } else if (sqlite3_step(stmt) != SQLITE_ROW) {
>         die("sqlite3_step: %s", sqlite3_errmsg(db));
>     }
>     rows = sqlite3_column_int(stmt, 0);
>     if (sqlite3_finalize(stmt) != SQLITE_OK) {
>         die("sqlite3_finalize: %s", sqlite3_errmsg(db));
>     }
>     /* Insert some rows. */
>     sqlite3_prepare_v2(db, "INSERT INTO t VALUES(?, ?, ?)", -1, &stmt,
> NULL);
>     if (!stmt) {
>         die("sqlite3_prepare_v2: %s", sqlite3_errmsg(db));
>     }
>     for (i = 0; i < 3; ++i) {
>         if (sqlite3_bind_int(stmt, 1, (int)getpid()) != SQLITE_OK) {
>             die("sqlite3_bind_int: %s", sqlite3_errmsg(db));
>         } else if (sqlite3_bind_int(stmt, 2, rows) != SQLITE_OK) {
>             die("sqlite3_bind_int: %s", sqlite3_errmsg(db));
>         } else if (sqlite3_bind_int(stmt, 3, i) != SQLITE_OK) {
>             die("sqlite3_bind_int: %s", sqlite3_errmsg(db));
>         } else if (sqlite3_step(stmt) != SQLITE_DONE) {
>             die("sqlite3_step: %s", sqlite3_errmsg(db));
>         } else if (sqlite3_reset(stmt) != SQLITE_OK) {
>             die("sqlite3_reset: %s", sqlite3_errmsg(db));
>         }
>     }
>     if (sqlite3_finalize(stmt) != SQLITE_OK) {
>         die("sqlite3_finalize: %s", sqlite3_errmsg(db));
>     }
>     /* Commit the transaction. */
>     if (sqlite3_exec(db, "COMMIT", NULL, NULL, &p) != SQLITE_OK) {
>         die("sqlite3_exec: %s", p);
>     }
>     /* Close the database. */
>     if ((rc = sqlite3_close(db)) != SQLITE_OK) {
>         die("sqlite3_close: %s", sqlite3_errstr(rc));
>     }
>     /* Congratulations on making it this far. */
>     return EXIT_SUCCESS;
> }
> /* vim: set sts=4 sw=4 tw=80 et ft=c: */
>
> Here's the makefile:
>
> CFLAGS += -ggdb3
> CFLAGS += -Wall
> test: LDFLAGS += -ldl
> test: LDFLAGS += -lpthread
> test: test.o sqlite3.o
> test.o: test.c sqlite3.h
> sqlite3.o: CPPFLAGS += -D_GNU_SOURCE
> sqlite3.o: CPPFLAGS += -DHAVE_INTTYPES_H
> sqlite3.o: CPPFLAGS += -DHAVE_STDINT_H
> sqlite3.o: CPPFLAGS += -DHAVE_FDATASYNC
> sqlite3.o: CPPFLAGS += -DHAVE_USLEEP
> sqlite3.o: CPPFLAGS += -DHAVE_LOCALTIME_R
> sqlite3.o: CPPFLAGS += -DHAVE_GMTIME_R
> sqlite3.o: CPPFLAGS += -DHAVE_STRERROR_R
> sqlite3.o: CPPFLAGS += -DHAVE_POSIX_FALLOCATE
> #sqlite3.o: CPPFLAGS += -DSQLITE_ENABLE_SETLK_TIMEOUT
> sqlite3.o: sqlite3.c
> clean:
>     -$(RM) test test.o sqlite3.o
> # vim: set ts=4 sw=4 tw=80 noet ft=make:
> ------------------------------------------- CONFIDENTIALITY NOTICE: This
> email and any attachments are for the sole use of the intended recipient and
> may contain material that is proprietary, confidential, privileged or
> otherwise legally protected or restricted under applicable government laws.
> Any review, disclosure, distributing or other use without expressed
> permission of the sender is strictly prohibited. If you are not the intended
> recipient, please contact the sender and delete all copies without reading,
> printing, or saving..
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database locking problems

Jesse Rittner
In reply to this post by Andrew.Goth
Not sure if this is the problem you are running into, but it might be because
SQLite is detecting a potential deadlock. When you just use BEGIN, SQLite
will acquire locks lazily - the first read operation will acquire a shared
lock, and the first write operation will acquire a reserved lock. Any number
of connections can hold a shared lock, but only one can hold a reserved lock
- all others will get back SQLITE_BUSY and have to wait. Furthermore, to
actually commit a write transaction, it has to wait for all pending shared
locks to be released.

Consider the following situation:
1. Connection A begins a transaction. No locks acquired yet.
2. Connection B begins a transaction. No locks acquired yet.
3. Connection A reads from the database. It acquires a shared lock.
4. Connection B begin a transaction. No locks acquired yet.
5. Connection B writes to the database It acquires a reserved lock.
6. Connection A writes to the database. It tries to acquire an exclusive
lock. But connection B already has one. Presumably, connection B would try
to actually commit to the database at some point. But doing so would require
it to wait for all shared locks to be released. Connection A is holding a
shared lock. So now we could have a deadlock - A is waiting for B so it can
acquire a reserved lock, and B is waiting for A so it can acquire an
exclusive lock. SQLite detects this and connection A immediately returns
SQLITE_BUSY, bypassing any busy timeout or busy handler.

To avoid this issue, use "BEGIN IMMEDIATE" instead of "BEGIN". This makes it
so the writer immediately acquires a reserved lock. Since it jumps directly
there instead of acquiring a shared lock first, there's no deadlock
potential, so the busy timeout will actually be respected.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database locking problems

R Smith-2
In reply to this post by Richard Hipp-3
Hi RIchard, any chance this BEGIN CONCURRENT branch will make it into a
next SQLite standard release?

On 2019/01/19 3:07 PM, Richard Hipp wrote:
> If you are daring, you can also try building from the begin-concurrent
> branch (https://www.sqlite.org/src/timeline?r=begin-concurrent) and
> using "BEGIN CONCURRENT" instead of "BEGIN" to start your transaction.
> In that case, the COMMIT might still fail with a conflict, but only if
> the intervening transaction modified pages of the database file that
> your transaction actually read.  So if the database has two tables,
> and you are updating one table, and some other process is updating the
> other table, then there is a high chance that both transaction will
> run to completion.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database locking problems

Richard Hipp-3
On 1/19/19, R Smith <[hidden email]> wrote:
> Hi RIchard, any chance this BEGIN CONCURRENT branch will make it into a
> next SQLite standard release?

You can always pull it from the branch.  The branch will not go away.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database locking problems

R Smith-2
On 2019/01/19 4:55 PM, Richard Hipp wrote:
> On 1/19/19, R Smith <[hidden email]> wrote:
>> Hi RIchard, any chance this BEGIN CONCURRENT branch will make it into a
>> next SQLite standard release?
> You can always pull it from the branch.  The branch will not go away.


Thank you kindly, this much is known.

Allow me to rephrase, I make scripts/software for SQLite used by many, I
can dictate the lowest version number for a script, but usually they use
whatever engine is downloadable from the site (sqlite3.exe, sqlite3.dll
etc.), so if the very useful BEGIN CONCURRENT won't be merged at some
stage, there is no point for me to investigate/test/learn about it. The
opposite is also true, and preferred.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database locking problems

Richard Hipp-3
On 1/19/19, R Smith <[hidden email]> wrote:
>  I make scripts/software for SQLite used by many, I
> can dictate the lowest version number for a script, but usually they use
> whatever engine is downloadable from the site (sqlite3.exe, sqlite3.dll
> etc.), so if the very useful BEGIN CONCURRENT won't be merged at some
> stage, there is no point for me to investigate/test/learn about it. The
> opposite is also true, and preferred.

"merged at some stage" and "merged into the next release" are
different things.  The latter will likely not happen, but I cannot say
about the former, just yet.


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database locking problems

R Smith-2
On 2019/01/19 5:20 PM, Richard Hipp wrote:
> On 1/19/19, R Smith <[hidden email]> wrote:
>>  
>> "merged at some stage" and "merged into the next release" are
>> different things.  The latter will likely not happen, but I cannot say
>> about the former, just yet.

Thank you - I believe my original mail did say "... a next release ..."
as opposed to  "the next release", which is probably a confusing way to
put it, I apologise, and take the point that neither is confirm-able yet.

I assume the decision will come after a lot of testing due to the nature
of this alteration. Either way, I think it's a great one - thank you for
the efforts!


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database locking problems

Andrew.Goth
In reply to this post by Andrew.Goth
Scott Vallery wrote:
> have you considered 'threading'? This sounds like something that you need to
> create a thread to do. Creating a thread should then allow you to set
> parameters to wait until one process completes and switch back and forth.

Yes, threads would make cooperation easier.  SQLite has a shared cache mode, allowing multiple threads to share a single database connection and all the database cache information therein.  Piggybacking on this shared database connection, SQLite's unlock-notify callback system can be used to let one thread notify (wake up) another when it's time to pass the baton.  All this existing capability would take minimal integration effort on my part.  I wish this were available to me.

The fact is threads are completely unsuited for my actual task.  I specifically need to inject SQLite database access into a wide variety of existing programs that, for the most part, only talk to each other via the filesystem.  I can't very well rewrite all of them to know and love each other like longtime couples who subconsciously complete each other's sentences.  Thus, I must rely on SQLite's built-in cross-process synchronization, which on Unix-like systems is implemented using fcntl(), which in turn is implemented using the filesystem.

SQLite is extensible enough to allow me to plug in alternative locking implementations.  For example, I could write a concurrency server to which each instance of SQLite would connect (via Unix domain socket, TCP, etc.), through which access would be coordinated.  But before doing anything drastic like that, I want to see how far vanilla SQLite will take me.  I might end up having to do that once I am forced to start running on NFS, given its broken locking.

All that being said, I am well familiar with threads, which is why I prefer to avoid them for all purposes besides leveraging additional CPU cores, and even then I will do my best to have the additional threads do little more than computation.  Grudgingly, I will also use them to work around broken APIs with functions that don't return for a long time, such as DNS resolvers and certain GUI toolkits, but that is not a happy situation.  As Richard Suchenwirth said, threads are for hangmen.
------------------------------------------- CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use of the intended recipient and may contain material that is proprietary, confidential, privileged or otherwise legally protected or restricted under applicable government laws. Any review, disclosure, distributing or other use without expressed permission of the sender is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies without reading, printing, or saving..
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database locking problems

Andrew.Goth
In reply to this post by Jesse Rittner
> Not sure if this is the problem you are running into, but it might be
> because SQLite is detecting a potential deadlock.

I think that's exactly what's going on.  SQLite is not bothering with the busy handler since it already knows no amount of waiting will solve the problem.

> When you just use BEGIN, SQLite will acquire locks lazily - the first
> read operation will acquire a shared lock, and the first write operation
> will acquire a reserved lock.

If I understand him correctly, drh said in another email that I could try using BEGIN IMMEDIATE rather than unqualified BEGIN to signal my intent to eventually do a write even though my first operation is a read.  You say the same thing.

> 1. Connection A begins a transaction. No locks acquired yet.
> 2. Connection B begins a transaction. No locks acquired yet.
> 3. Connection A reads from the database. It acquires a shared lock.
> 4. Connection B begin a transaction. No locks acquired yet.
> 5. Connection B writes to the database. It acquires a reserved lock.

(5) is the point where your example deviates from what my example program is actually doing.  My example program always starts with a read.  Thus, both connections will first try to acquire a shared lock.

> 6. Connection A writes to the database. It tries to acquire an exclusive
> lock.  But connection B already has one.

Wouldn't connection A try to acquire a reserved lock?  It's not going to go for the exclusive lock (by way of pending) until the COMMIT comes along.  But nevertheless, in your example, A can't get reserved because B already has it.

> Presumably, connection B would try to actually commit to the database at
> some point. But doing so would require it to wait for all shared locks
> to be released. Connection A is holding a shared lock. So now we could
> have a deadlock - A is waiting for B so it can acquire a reserved lock,
> and B is waiting for A so it can acquire an exclusive lock. SQLite
> detects this and connection A immediately returns SQLITE_BUSY, bypassing
> any busy timeout or busy handler.

I'm thinking one possible sequence might be:

1. A and B both run BEGIN.
2. A and B both run SELECT and both acquire SHARED.
3. A runs INSERT and acquires RESERVED.
4. B runs INSERT, but it can't acquire RESERVED because of A.  B is now blocked, and the busy handler is invoked.
5. A runs COMMIT and acquires PENDING.  However, it can't acquire EXCLUSIVE because B still holds SHARED.

SQLite breaks this deadlock by smiting A with SQLITE_BUSY, at which point A is expected to give up and let B proceed.

> To avoid this issue, use "BEGIN IMMEDIATE" instead of "BEGIN". This
> makes it so the writer immediately acquires a reserved lock. Since it
> jumps directly there instead of acquiring a shared lock first, there's
> no deadlock potential, so the busy timeout will actually be respected.

Like this?

1. A runs BEGIN IMMEDIATE and acquires RESERVED.
2. B runs BEGIN IMMEDIATE but can't acquire RESERVED.  B is now blocked, and the busy handler is invoked.
3. A runs SELECT and INSERT.
4. A runs COMMIT and acquires PENDING and EXCLUSIVE.
5. A completes and drops all locks.  B is now free to run.
6. B does everything A just did.
------------------------------------------- CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use of the intended recipient and may contain material that is proprietary, confidential, privileged or otherwise legally protected or restricted under applicable government laws. Any review, disclosure, distributing or other use without expressed permission of the sender is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies without reading, printing, or saving..
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database locking problems

Simon Slavin-3
On 19 Jan 2019, at 4:43pm, <[hidden email]> <[hidden email]> wrote:

> I'm thinking one possible sequence might be:

It's the right suggestion, but you're still in diagnostic stage, so this is a faster test:

1) Make sure your code still sets timeouts on every connection.  5000 ms should be fine.
2) Replace every BEGIN with BEGIN IMMEDIATE

Test your program.  If it still doesn't work, you need more diagnostics.  But if it does work

3) Replace every BEGIN IMMEDIATE with BEGIN EXCLUSIVE

Test your program.  BEGIN EXCLUSIVE is better, because it allows other connections access to the database for longer before it locks up the database.  But there are circumstances where it can allow a deadlock which IMMEDIATE could avoid.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database locking problems

James K. Lowden
In reply to this post by Richard Hipp-3
On Sat, 19 Jan 2019 08:07:42 -0500
Richard Hipp <[hidden email]> wrote:

> The busy timeout is not working because you start out your transaction
> using a read operation - the first SELECT statement - which gets a
> read lock.  Later when you go to COMMIT, this has to elevate to a
> write lock.  But SQLite sees that some other process has already
> updated the database since you started your read.  

Another solution is to rely on atomicity in SQL:

        insert into t
        select :pid, nrows, N
        from (select 1 as N union select 2 union select 3) as cardinals
        cross join (select :pid, count(*) as nrows from t) as how_many;

By using a single SQL statement, you avoid a user-defined transaction
and any proprietary transaction qualifiers.  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database locking problems

Andrew.Goth
In reply to this post by Andrew.Goth
James K. Lowden wrote:

> On Sat, 19 Jan 2019 08:07:42 -0500 Richard Hipp <[hidden email]> wrote:
>> The busy timeout is not working because you start out your transaction
>> using a read operation - the first SELECT statement - which gets a read
>> lock.  Later when you go to COMMIT, this has to elevate to a write
>> lock.  But SQLite sees that some other process has already updated the
>> database since you started your read.
>
> Another solution is to rely on atomicity in SQL:
>
> insert into t
> select :pid, nrows, N
> from (select 1 as N union select 2 union select 3) as cardinals
> cross join (select :pid, count(*) as nrows from t) as how_many;
>
> By using a single SQL statement, you avoid a user-defined transaction
> and any proprietary transaction qualifiers.

Thank you for the suggestion, but I don't believe this is an option in my application.  There's too much back-and-forth between the database and my logic to put it all into a single statement.  Thus, transactions are necessary.  Transactions exist to allow multiple statements to become an atomic unit, so eschewing them is basically the same thing as admitting they don't work.  There are two possibilities:

1. Transactions do work, but I'm misusing them and must learn how to be more careful.  In this case, I will update documentation to properly explain their use to others.

2. Transactions don't work, at least not for my task.  In this case, I will do my best to investigate the problem and suggest a correction.

Either way, the right thing for me to do is continue digging in.

Going from BEGIN to BEGIN IMMEDIATE indeed fixed the test program I wrote, but my application is still broken, and I don't know what's different about it.  I'm working on instrumenting the fcntl() calls to log the sequence of operations.
------------------------------------------- CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use of the intended recipient and may contain material that is proprietary, confidential, privileged or otherwise legally protected or restricted under applicable government laws. Any review, disclosure, distributing or other use without expressed permission of the sender is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies without reading, printing, or saving..
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database locking problems

Keith Medcalf

>There are two possibilities:

>1. Transactions do work, but I'm misusing them and must learn how to
>be more careful.  In this case, I will update documentation to
>properly explain their use to others.

>2. Transactions don't work, at least not for my task.  In this case,
>I will do my best to investigate the problem and suggest a
>correction.

>Either way, the right thing for me to do is continue digging in.

>Going from BEGIN to BEGIN IMMEDIATE indeed fixed the test program I
>wrote, but my application is still broken, and I don't know what's
>different about it.  I'm working on instrumenting the fcntl() calls
>to log the sequence of operations.

Transactions do work provided you use them properly.  The rules you need to obey are:

Make sure you are doing nothing foolish like using "shared cache".  Doing so changes how things work considerably and open a completely different kettle of fish.

Make sure that you are using the default SERIALIZED or FULLMUTEX thread management on all connections whether you think you know better or do not need it or whatever.  A few microseconds of CPU might save you many MONTHS of trying to locate a defect in your code.

If you think that you need to use either "shared cache" or some threading mode other than the default serialized, then do that ONLY AFTER your application is working using the defaults.  Do not fall into the premature optimization trap.

Place all read and write statements which require a consistent database context in the same transaction.  Context may change BETWEEN transactions but will not change WITHIN a transaction.  That is, if you are executing multiple SELECT statements which require a consistent view of the database then they should all be located in the same read transaction.  If you are selecting data and then performing updates based on the retrieved data, those operations need to occur within the same write transaction.  SQLite3 does not have cursors and does not do UPDATE ... WHERE CURRENT OF CURSOR nor does it do SELECT ... FOR UPDATE OF ... which means that processing an update may mutate what you are reading and therefore you should complete your read before performing the update.

IF THE TRANSACTION WILL OR MAY WRITE TO THE DATABASE AT SOME POINT BEFORE COMMIT/ROLLBACK then start the transaction with BEGIN IMMEDIATE.

IF THE TRANSACTION WILL ONLY READ AND CANNOT EVER WRITE TO THE DATABASE BEFORE COMMIT/ROLLBACK then start the transaction with BEGIN DEFERRED (this is the default for a naked BEGIN).

Transactions are an attribute of the DATABASE CONNECTION and not of a STATEMENT (a STATEMENT is an attribute of a CONNECTION).  Multiple STATEMENTs may execute concurrently sharing a single CONNECTION (and hence transaction) context.

In DELETE or TRUNCATE (that is, all modes except WAL) a READ transaction in progress blocks a WRITE transaction and a WRITE transaction in progress blocks all other attempts to commence a transaction of any type on any other connection.  WAL mode permits READ and WRITE to proceed independantly provided that the WRITE transaction must only be performed against the "current" (head) database context and will fail if the context is not the head context at the time the attempt is made to obtain the WRITE lock (and since there is only one "current" head, there can only be one WRITE in progress at a time).

An attempt to UPGRADE a READ transaction to a WRITE transaction will DEADLOCK (and immediately return an error) if another WRITE TRANSACTION is pending.  (Hence always signal your intent when commencing a transaction by using the appropriate BEGIN syntax in order to avoid this state of affairs).

WAL journalling only works where all database user processes are local to each other (ie, between connections all originating from the same computer -- where "same computer" means all processes have access to the same shared memory, so that you may determine whether a SYSPLEX or NUMA architecture constitutes "same computer" or not, by whether or not they can all access the same "shared memory" region).

File locking only works reliably on local filesystems.  It is unreliable for *ALL* remote filesystems, even if the target "remote" filesystem is local.

A local filesystem is defined as one wherein the filesystem code resides and is executed "on the same computer" as the application.  See the requirement 2 above to determine the meaning of "same computer".  The cable between the "computer running the filesystem code" and the "block storage device" may have a length varying from inches to many thousands of miles.  The command language the "filesystem" uses to access the underlying block storage is irrelevant (it may be ATA, SATA, SCSI, SAS, iSCSI, SAC (SCSI over Avian Carriers) or what ever your heart may desire).

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database locking problems

Richard Damon
In reply to this post by Andrew.Goth
On 1/20/19 4:51 PM, [hidden email] wrote:

> James K. Lowden wrote:
>> On Sat, 19 Jan 2019 08:07:42 -0500 Richard Hipp <[hidden email]> wrote:
>>> The busy timeout is not working because you start out your transaction
>>> using a read operation - the first SELECT statement - which gets a read
>>> lock.  Later when you go to COMMIT, this has to elevate to a write
>>> lock.  But SQLite sees that some other process has already updated the
>>> database since you started your read.
>> Another solution is to rely on atomicity in SQL:
>>
>> insert into t
>> select :pid, nrows, N
>> from (select 1 as N union select 2 union select 3) as cardinals
>> cross join (select :pid, count(*) as nrows from t) as how_many;
>>
>> By using a single SQL statement, you avoid a user-defined transaction
>> and any proprietary transaction qualifiers.
> Thank you for the suggestion, but I don't believe this is an option in my application.  There's too much back-and-forth between the database and my logic to put it all into a single statement.  Thus, transactions are necessary.  Transactions exist to allow multiple statements to become an atomic unit, so eschewing them is basically the same thing as admitting they don't work.  There are two possibilities:
>
> 1. Transactions do work, but I'm misusing them and must learn how to be more careful.  In this case, I will update documentation to properly explain their use to others.
>
> 2. Transactions don't work, at least not for my task.  In this case, I will do my best to investigate the problem and suggest a correction.
>
> Either way, the right thing for me to do is continue digging in.
>
> Going from BEGIN to BEGIN IMMEDIATE indeed fixed the test program I wrote, but my application is still broken, and I don't know what's different about it.  I'm working on instrumenting the fcntl() calls to log the sequence of operations.

Transactions work, but the way you have described your use of them has a
predictable issue. The problem being if you begin with a shared lock,
try to promote to an exclusive lock to write, then you need to wait for
all the other shared locks to clear, and if another of them also tries
to promote to an exclusive lock, which cause a deadlock, which sounds to
be your issue.

Starting with a BEGIN IMMEDIATE breaks this deadlock situation, so it
can fix your problem, at the cost that you get less concurrency.

The experimental concurrency option might also help, in that a write
operation doesn't need an exclusive lock for the whole database, but
does introduce more points where a transaction might fail and need to be
rolled back.


--
Richard DamonTh

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database locking problems

Thomas Kurz
Just for curiosity: how do other DBMS (MySQL, etc.) solve this issue? I guess the keypoint is that no matter where the query comes from, the database files are always under control of the same process which then can take care of the correct order in which to read and write data.

But the problem that the same database page cannot be written to from 2 different statements (or being read while another statement is writing into it) should arise there as well, shouldn't it? However, I have never seen MySQL return some kind of "busy" (whatsoever it may be named there).

As I said, just for curiosity - no offense against SQlite because I can well understand the problem that SQlite has to deal with :-)


----- Original Message -----
From: Richard Damon <[hidden email]>
To: [hidden email] <[hidden email]>
Sent: Monday, January 21, 2019, 00:21:48
Subject: [sqlite] Database locking problems

On 1/20/19 4:51 PM, [hidden email] wrote:
> James K. Lowden wrote:
>> On Sat, 19 Jan 2019 08:07:42 -0500 Richard Hipp <[hidden email]> wrote:
>>> The busy timeout is not working because you start out your transaction
>>> using a read operation - the first SELECT statement - which gets a read
>>> lock.  Later when you go to COMMIT, this has to elevate to a write
>>> lock.  But SQLite sees that some other process has already updated the
>>> database since you started your read.
>> Another solution is to rely on atomicity in SQL:

>> insert into t
>> select :pid, nrows, N
>> from (select 1 as N union select 2 union select 3) as cardinals
>> cross join (select :pid, count(*) as nrows from t) as how_many;

>> By using a single SQL statement, you avoid a user-defined transaction
>> and any proprietary transaction qualifiers.
> Thank you for the suggestion, but I don't believe this is an option in my application.  There's too much back-and-forth between the database and my logic to put it all into a single statement.  Thus, transactions are necessary.  Transactions exist to allow multiple statements to become an atomic unit, so eschewing them is basically the same thing as admitting they don't work.  There are two possibilities:

> 1. Transactions do work, but I'm misusing them and must learn how to be more careful.  In this case, I will update documentation to properly explain their use to others.

> 2. Transactions don't work, at least not for my task.  In this case, I will do my best to investigate the problem and suggest a correction.

> Either way, the right thing for me to do is continue digging in.

> Going from BEGIN to BEGIN IMMEDIATE indeed fixed the test program I wrote, but my application is still broken, and I don't know what's different about it.  I'm working on instrumenting the fcntl() calls to log the sequence of operations.

Transactions work, but the way you have described your use of them has a
predictable issue. The problem being if you begin with a shared lock,
try to promote to an exclusive lock to write, then you need to wait for
all the other shared locks to clear, and if another of them also tries
to promote to an exclusive lock, which cause a deadlock, which sounds to
be your issue.

Starting with a BEGIN IMMEDIATE breaks this deadlock situation, so it
can fix your problem, at the cost that you get less concurrency.

The experimental concurrency option might also help, in that a write
operation doesn't need an exclusive lock for the whole database, but
does introduce more points where a transaction might fail and need to be
rolled back.


--
Richard DamonTh

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database locking problems

Richard Damon
On 1/20/19 6:32 PM, Thomas Kurz wrote:
> Just for curiosity: how do other DBMS (MySQL, etc.) solve this issue? I guess the keypoint is that no matter where the query comes from, the database files are always under control of the same process which then can take care of the correct order in which to read and write data.
>
> But the problem that the same database page cannot be written to from 2 different statements (or being read while another statement is writing into it) should arise there as well, shouldn't it? However, I have never seen MySQL return some kind of "busy" (whatsoever it may be named there).
>
> As I said, just for curiosity - no offense against SQlite because I can well understand the problem that SQlite has to deal with :-)
>
If the issue is the dead lock, you get similar issues with all DBMSes.
One thing that does help is that many DBMS make the transaction locking
on a record level, not the full database (at an overhead cost). somewhat
like that experimental concurrency option. Running into deadlocks is a
real issue with MySQL databases.

If the issue is improper sharing between threads, then the fact that
other DBMS run the database server in a separate process handles a lot
of the sharing issues (at the cost of overhead). SQLite, gets rid of a
lot of that overhead at the cost of the application needs to follow a
set of rules.

--
Richard Damon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database locking problems

Keith Medcalf
In reply to this post by Thomas Kurz

On Sunday, 20 January, 2019 16:32, Thomas Kurz <[hidden email]> wrote:

>Just for curiosity: how do other DBMS (MySQL, etc.) solve this issue?
>I guess the keypoint is that no matter where the query comes from,
>the database files are always under control of the same process which
>then can take care of the correct order in which to read and write
>data.

>But the problem that the same database page cannot be written to from
>2 different statements (or being read while another statement is
>writing into it) should arise there as well, shouldn't it? However, I
>have never seen MySQL return some kind of "busy" (whatsoever it may
>be named there).

>As I said, just for curiosity - no offense against SQlite because I
>can well understand the problem that SQlite has to deal with :-)

The "other DBMS" to which you refer are all of a class called a Database Server.  As such they are accessed remotely and thus have huge latency between when "your application" requests for something to be done and when that request gets to the server so that it can be processed.  This means that these database engines have to be optimized for concurrency and this is done by using what is called "row level locking" in which locks apply to a specific row in a specific table.  You can indeed "write" the same page at the same time from a single process, you just need to make sure that the row of data that "A" is writing to is not the same row of data that "B" is writing to.

Furthermore, many client/server systems use the "opportunistic locking" model which is based on the "plow ahead at full speed but keep enough history to go "Oh Shit" when required" because it is probabilistically unlikely that your 1000 clients will all try and modify the same thing at once and you only need to be able to detect that and go "Oh Shit" when necessary.  On the balance the "plow ahead and pray" (also known as opportunistic locking) works adequately for most things.  That is, it is probabilistically unlikely that your clients will all try to update the same row in the same table at the same time (a given customer can only be calling ONE of your 5000 customer service representatives to update their shipping address, for example, so having TWO agents submitting an update for the same row at the same time is extremely highly improbable).  And if they do, you can raise the "Oh Shit" flag and cancel the transaction(s).

So a client/server architecture has massive latencies on the order of hundreds of milliseconds but uses very fine grained locking to allow thousands of turnarounds to occur simultaneously and the updates to "appear to a lay external observer" to be occurring concurrently.

SQLite3 however has latencies on the order of microseconds and does not need to utilize the overhead associated with managing such fine grained locking in order to obtain the same or better local throughput, and being a local database at the whim of one client application accessing the database, it does not have the same opportunity for opportunistic behaviour as does a client/server database which may be serving thousands of concurrent (but different) applications.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.





_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database locking problems

Simon Slavin-3
In reply to this post by Richard Damon
On 20 Jan 2019, at 11:54pm, Richard Damon <[hidden email]> wrote:

> If the issue is the dead lock, you get similar issues with all DBMSes.

I'm not perfectly sure of my logic here, but OP posted elsewhere that replacing BEGIN with BEGIN IMMEDIATE cures the problem.  I think this indicates that his problem isn't deadlock.

The problem is more about how SQLite implements BEGIN without IMMEDIATE.  As others have explained in this thread, SQLite does its best to avoid locking the database until it really needs to.  But it does need to obtain a shared lock, and this fails in the specific pattern OP uses.

Which is why BEGIN IMMEDIATE exists.  So use BEGIN IMMEDIATE.  Done.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12