Database is locked

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Database is locked

Bob Friesenhahn
We are sometimes encountering permanent "Database is locked" errors
from sqlite 3.10.2.  After the database gets in this state it is not
possible to write to it or read from it.  It is not possible to use
sqlite's built in backup mechanism since it produces a backup file of
zero bytes.

What is the recommended approach to diagnosing "Database is locked"
errors and determining the guilty party?

Is there a way to clear the condition which causes "Database is
locked" errors?

Bob
--
Bob Friesenhahn
[hidden email], http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Database is locked

Stephen Chrzanowski
Where is the database being stored?  What OS is the software running
under?  If Windows, I'd suggest looking up SysInternals and downloading the
package to see what has its grubby hands on the database.  If Linux, as
root, run an LSOF (Lower case) and grep for the database or process
accessing the database.

On Wed, Mar 1, 2017 at 2:27 PM, Bob Friesenhahn <
[hidden email]> wrote:

> We are sometimes encountering permanent "Database is locked" errors from
> sqlite 3.10.2.  After the database gets in this state it is not possible to
> write to it or read from it.  It is not possible to use sqlite's built in
> backup mechanism since it produces a backup file of zero bytes.
>
> What is the recommended approach to diagnosing "Database is locked" errors
> and determining the guilty party?
>
> Is there a way to clear the condition which causes "Database is locked"
> errors?
>
> Bob
> --
> Bob Friesenhahn
> [hidden email], http://www.simplesystems.org/users/bfriesen/
> GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
> _______________________________________________
> 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
|  
Report Content as Inappropriate

Re: Database is locked

Bob Friesenhahn
On Wed, 1 Mar 2017, Stephen Chrzanowski wrote:

> Where is the database being stored?  What OS is the software running
> under?  If Windows, I'd suggest looking up SysInternals and downloading the
> package to see what has its grubby hands on the database.  If Linux, as
> root, run an LSOF (Lower case) and grep for the database or process
> accessing the database.

This is an embedded Linux system.  Due to available resources, lsof is
not available, but we can learn about open file descriptors from the
/proc filesystem (via 'ls -l /proc/[pid]/fd').

Many of our processes have a database connection open all the time.  I
am assuming that the problem is a hung (not commited or rolled back)
transaction.  I am hoping that sqlite provides a way to know what
process is currently performing an update transaction.

Bob
--
Bob Friesenhahn
[hidden email], http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Database is locked

Simon Slavin-3
In reply to this post by Bob Friesenhahn

On 1 Mar 2017, at 7:27pm, Bob Friesenhahn <[hidden email]> wrote:

> What is the recommended approach to diagnosing "Database is locked" errors and determining the guilty party?

Are you checking the result codes returned by all SQLite commands you give to make sure they are SQLITE_OK ?

I’m not talking about checking them only after you’re getting "Database is locked" I’m talking about putting the checks in all your code which calls the SQLite API.  If SQLite is working the way it’s meant to, you could get a bad result from one of those calls before you start getting "Database is locked" from everything.

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
|  
Report Content as Inappropriate

Re: Database is locked

Bob Friesenhahn
On Wed, 1 Mar 2017, Simon Slavin wrote:

>
> On 1 Mar 2017, at 7:27pm, Bob Friesenhahn <[hidden email]> wrote:
>
>> What is the recommended approach to diagnosing "Database is locked" errors and determining the guilty party?
>
> Are you checking the result codes returned by all SQLite commands you give to make sure they are SQLITE_OK ?

Some of our C software is very good at doing this, and some C code is
not quite as good.  Our Python code should be robust at reporting
problems since we are using APSW and Python exceptions.

We do capture output from sqlite's tracing facility, including error
reports.

I am not sure of all of the conditions which might result in "Database
is locked".  For example, if the results of a query are being iterated
through and the reader stops iterating part way through the result
set, might this cause "Database is locked" for writers?

Bob
--
Bob Friesenhahn
[hidden email], http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Database is locked

Simon Slavin-3

On 1 Mar 2017, at 9:17pm, Bob Friesenhahn <[hidden email]> wrote:

> I am not sure of all of the conditions which might result in "Database is locked".  For example, if the results of a query are being iterated through and the reader stops iterating part way through the result set, might this cause "Database is locked" for writers?

If you do a sqlite3_step() for a statement then you absolutely must do a sqlite3_reset() or sqlite3_finalize() or both for that statement.  Without it you haven’t finished processing the statement.

Not doing this properly may not necessarily result in a lock which prevents writers (though it might), but it can can cause problems later.  For instance, you can’t close a connection which still has active statements, and that means your database may still be open when your program quits.

> Some of our C software is very good at doing this, and some C code is not quite as good.  Our Python code should be robust at reporting problems since we are using APSW and Python exceptions.

If possible, fix your C code.  But you knew that.  And there can be reasons why that’s not possible (legacy code you’re not allowed to change, libraries you can’t recompile).

> We do capture output from sqlite's tracing facility, including error reports.

Unfortunately, the tracing facility is triggered by your program doing an API call.  If you don’t do the API call, you don’t get an error report saying what you did wrong.

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
|  
Report Content as Inappropriate

Re: Database is locked

Hick Gunter
In reply to this post by Bob Friesenhahn
Not directly, but you can query the locks on the file as per documentation:

    struct flock    v_pending;   // = { F_WRLCK, SEEK_SET, PENDING_BYTE  ,           1, 0};
    struct flock    v_reserved;  // = { F_WRLCK, SEEK_SET, RESERVED_BYTE ,           1, 0};
    struct flock    v_shared;    // = { F_WRLCK, SEEK_SET, SHARED_FIRST  , SHARED_SIZE, 0};
    int             v_fd        = -1;
    int             v_ret       = -1;

        memset( &v_pending, 0, sizeof( v_pending));
        v_pending.l_type        = F_WRLCK;
        v_pending.l_whence      = SEEK_SET;
        v_pending.l_start       = PENDING_BYTE;
        v_pending.l_len         = 1;
        v_pending.l_pid         = 0;

        memset( &v_reserved, 0, sizeof( v_reserved));
        v_reserved.l_type       = F_WRLCK;
        v_reserved.l_whence     = SEEK_SET;
        v_reserved.l_start      = RESERVED_BYTE;
        v_reserved.l_len        = 1;
        v_reserved.l_pid        = 0;

        memset( &v_shared, 0, sizeof( v_shared));
        v_shared.l_type         = F_WRLCK;
        v_shared.l_whence       = SEEK_SET;
        v_shared.l_start        = SHARED_FIRST;
        v_shared.l_len          = SHARED_SIZE;
        v_shared.l_pid          = 0;

    /* check for a PENDING lock */
    if (fcntl(v_fd,F_GETLK,&v_pending) == -1) {
<error handling>
    };
    /* check for a RESERVED lock */
    if (fcntl(v_fd,F_GETLK,&v_reserved) == -1) {
<error handling>    };
    /* check for a SHARED/EXCLUSIVE lock */
    if (fcntl(v_fd,F_GETLK,&v_shared) == -1) {
<error handling>    };

    if (v_pending.l_type == F_RDLCK)
        printf("%s File:%s, Process %d PENDING (SHARED)\n"   ,g_mode[v_mode] ,v_file, (v_ret = v_pending .l_pid));

    if (v_shared .l_type == F_RDLCK)
        printf("%s File:%s, Process %d SHARED\n"             ,g_mode[v_mode] ,v_file, (v_ret = v_shared  .l_pid));

    switch (v_reserved.l_type) {
        case F_WRLCK:
        case F_RDLCK:
        printf("%s File:%s, Process %d RESERVED\n"           ,g_mode[v_mode] ,v_file, (v_ret = v_reserved.l_pid));
            break;
        default: break;
    }

    if (v_pending.l_type == F_WRLCK)
        printf("%s File: %s,Process %d PENDING (EXCLUSIVE)\n",g_mode[v_mode] ,v_file, (v_ret = v_pending .l_pid));

    if (v_shared .l_type == F_WRLCK)
        printf("%s File %s, Process %d EXCLUSIVE\n"          ,g_mode[v_mode] ,v_file, (v_ret = v_shared  .l_pid));

    if (v_ret == -1)
        printf("%s File:%s, <none>\n"                        ,g_mode[v_mode] ,v_file);

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Bob Friesenhahn
Gesendet: Mittwoch, 01. März 2017 21:45
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] Database is locked

On Wed, 1 Mar 2017, Stephen Chrzanowski wrote:

> Where is the database being stored?  What OS is the software running
> under?  If Windows, I'd suggest looking up SysInternals and
> downloading the package to see what has its grubby hands on the
> database.  If Linux, as root, run an LSOF (Lower case) and grep for
> the database or process accessing the database.

This is an embedded Linux system.  Due to available resources, lsof is not available, but we can learn about open file descriptors from the /proc filesystem (via 'ls -l /proc/[pid]/fd').

Many of our processes have a database connection open all the time.  I am assuming that the problem is a hung (not commited or rolled back) transaction.  I am hoping that sqlite provides a way to know what process is currently performing an update transaction.

Bob
--
Bob Friesenhahn
[hidden email], http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


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