Re: [sqlite] database is locked

[Simon Slavin]

On 20 Oct 2018, at 1:19pm, p...@geniais.com wrote:

> I'm using exec() to simply get true/false result (just reading) and I'm 
> receiving this error, even in WAL mode:
> SQLite3::exec(): database is locked in ... on line 47
> 
> Any help will be apreciated

I'm assuming that you are aware that something else is acessing the same 
database at the same time.

Set a timeout using either of these:



Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked

[Frank Millman]

On 2/28/18 3:18 PM, Frank Millman wrote:

> 
> On 2/28/18 2:53 PM, Richard Damon wrote:
> 
> > 
> > On 2/28/18 6:59 AM, Frank Millman wrote:
> > > Hi all
> > >
> > > I am using Python 3.6.0 and sqlite3 3.20.1. I am getting the message 
> > > ‘database is locked’ which, from reading the docs, I think is coming from 
> > > an SQL_BUSY error.
> > >
> > > It does not behave in the way I expect.
> > 
[...]
> > 
> > Your description, seeming to imply a total order, has an issue. If this 
> > is happening in a single thread, then if it uses a second connection to 
> > try and insert, that will block and the thread will never get to step 3, 
> > so the commit will not happen. You have a dead lock.
> > 
> 
> Of course! I should have thought of that.
> I am using python’s asyncio module, so everything is happening in a single 
> thread.
> I understand what is happening now. Thanks very much.

>

For the record, in case anyone runs into a similar situation, I found an 
effective solution.

I try to keep my transactions as short as possible, but with multiple 
connections running in the same thread it is possible that one of them starts a 
transaction before another one has committed.

To control that, I created an asyncio.Lock(), which is acquired at the start of 
the transaction and released after the commit. Problem solved.

I tested with 10 concurrent connections, and they all updated perfectly, with 
hardly any slowdown.

Frank
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked

[Frank Millman]

On 2/28/18 2:53 PM, Richard Damon wrote:

> 
> On 2/28/18 6:59 AM, Frank Millman wrote:
> > Hi all
> >
> > I am using Python 3.6.0 and sqlite3 3.20.1. I am getting the message 
> > ‘database is locked’ which, from reading the docs, I think is coming from 
> > an SQL_BUSY error.
> >
> > It does not behave in the way I expect.
> 
[...]
> 
> Your description, seeming to imply a total order, has an issue. If this 
> is happening in a single thread, then if it uses a second connection to 
> try and insert, that will block and the thread will never get to step 3, 
> so the commit will not happen. You have a dead lock.
> 

Of course! I should have thought of that.
I am using python’s asyncio module, so everything is happening in a single 
thread.
I understand what is happening now. Thanks very much.
Frank
 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked

[Richard Damon]

On 2/28/18 6:59 AM, Frank Millman wrote:

Hi all

I am using Python 3.6.0 and sqlite3 3.20.1. I am getting the message ‘database 
is locked’ which, from reading the docs, I think is coming from an SQL_BUSY 
error.

It does not behave in the way I expect. I tested using two concurrent 
connections – call them conn_1 and conn_2.

1. conn_1 performs an INSERT.

2. conn_2 performs an INSERT.

3. conn_1 sleeps for 1 second, then performs a COMMIT.

4. conn_2 sleeps for 1 second, then performs a COMMIT.

After step 2, the database is locked, which I understand. I don’t know which 
connection is holding the lock, but I don’t think that matters.

What I expected to happen was that, after one second, whichever connection was 
holding the lock would COMMIT, which would free up the other connection, which 
would then complete normally.

AFAICT, what happens in practice is that the lock is held for the default 
timeout of 5 seconds, then the connection which did *not* hold the lock fails 
with ‘database is locked’, and the connection which *did* hold the lock 
completes normally.

Is there any way to get the behaviour that I am looking for?

Thanks

Frank Millman
Your description, seeming to imply a total order, has an issue. If this 
is happening in a single thread, then if it uses a second connection to 
try and insert, that will block and the thread will never get to step 3, 
so the commit will not happen. You have a dead lock.


If steps 1 and 3 are in one thread, and 2 and 4 are in a different 
thread then this should normally work. The only possible issue would be 
if the Python wrapper for sqlite doesn't let the first thread run when 
the second thread blocks waiting to get access to perform the insert.


The description here would be described as (note, T1 and T2 are the two 
threads, and ordering between them is generally weak except as enforced 
by locks)


T1-1 conn_1 performs an INSERT
T1-2 conn_1 sleeps for 1 second
T2-1 conn_2 attempts to perfom an INSERT, but blocks
T1-3 conn_1 wakes up and performs a commit
T2-1a conn_2 wakes up and performs an INSERT
T2-2 conn_2 sleeps for 1 second
T2-3 conn_2 wakes up and performs a commit

Note, it is was just a single thread, then your description forces what 
I call T2-1 to complete (in T2-1a) before you get to T1-2, and thus 
before T1-3, but by the lock, T1-3 must complete before you get to 
T2-1a, so you deadlock.


--
Richard Damon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked

[Hick Gunter]

Not directly, but you can query the locks on the file as per documentation:

struct flockv_pending;   // = { F_WRLCK, SEEK_SET, PENDING_BYTE  ,  
 1, 0};
struct flockv_reserved;  // = { F_WRLCK, SEEK_SET, RESERVED_BYTE ,  
 1, 0};
struct flockv_shared;// = { F_WRLCK, SEEK_SET, SHARED_FIRST  , 
SHARED_SIZE, 0};
int v_fd= -1;
int v_ret   = -1;

memset( _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( _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( _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,_pending) == -1) {

};
/* check for a RESERVED lock */
if (fcntl(v_fd,F_GETLK,_reserved) == -1) {
};
/* check for a SHARED/EXCLUSIVE lock */
if (fcntl(v_fd,F_GETLK,_shared) == -1) {
};

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, \n",g_mode[v_mode] 
,v_file);

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Bob Friesenhahn
Gesendet: Mittwoch, 01. März 2017 21:45
An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
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
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
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: h...@scigames.at

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked

[Simon Slavin]

On 1 Mar 2017, at 9:17pm, Bob Friesenhahn  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked

[Bob Friesenhahn]

On Wed, 1 Mar 2017, Simon Slavin wrote:



On 1 Mar 2017, at 7:27pm, Bob Friesenhahn  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
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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 <
bfrie...@simple.dallas.tx.us> 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
> bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
> GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked

[Richard Hipp]

You may not DETACH while a query is running.  This is to prevent the
DETACH from closing a database connection out from under the running
query.

On 11/9/16, Jean-Baptiste Gardette  wrote:
> On Window 7 with tcl/tk 8.6.6 and SQLite 3.13.0
>
> The following tcl script craches :
>
>  package require sqlite3
>
>  sqlite3 db1 dbfile1
>
>  db1 eval "SELECT A FROM TableA" {
>
>  db1 eval "ATTACH DATABASE dbfile2 AS 'dbfile2'"
>
>  db1 eval "SELECT B FROM dbfile2.TableB"
>
>  db1 eval "DETACH DATABASE dbfile2"
>  }
>
> Error message :
> database dbfile2 is locked
>  while executing
> "db1 eval "DETACH DATABASE dbfile2""
>
> Please, if anyone could explain to me what's wrong.
>
> Thanks
> Jean-Baptiste
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked

[Igor Korot]

Dan,

On Sun, Jul 10, 2016 at 11:57 AM, Dan Kennedy  wrote:
> On 07/10/2016 06:21 AM, Igor Korot wrote:
>>
>> Simon,
>>
>> On Sat, Jul 9, 2016 at 7:09 PM, Simon Slavin  wrote:
>>>
>>> On 10 Jul 2016, at 12:06am, Igor Korot  wrote:
>>>
 I'm trying to write some software in C{++}. Everything works fine except
 when I exit the program exit I get the error "Database is locked".
 I am only trying to retrieve the information about the database (queries
 on
 sqlite_master).
>>>
>>> What command are you executing when you get "Database is locked" in
>>> return ?
>>
>> sqlite3_close();
>>
>>> Have you terminated your query properly ?  Did you call sqlite_finalize()
>>> on it ?  Did you get SQLITE_OK back from that call ?
>>
>> Yes, everything is finalized. And no error on finalization is produced.
>> That's why I'm asking if there is a tool that can check what is open.
>
>
> As I think you have surmised, you most likely have an unfinalized statement
> handle hanging around somewhere. After sqlite3_close() fails, use this:
>
>   https://www.sqlite.org/c3ref/next_stmt.html
>
> to loop through any unfinalized statement handles. If you find such a
> handle, this:
>
>   https://www.sqlite.org/c3ref/sql.html
>
> can be useful for figuring out where it came from.

Thank you.
The culprit was identified.

Now it is time to fix it.

>
> Dan.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked

[Dan Kennedy]

On 07/10/2016 06:21 AM, Igor Korot wrote:

Simon,

On Sat, Jul 9, 2016 at 7:09 PM, Simon Slavin  wrote:

On 10 Jul 2016, at 12:06am, Igor Korot  wrote:


I'm trying to write some software in C{++}. Everything works fine except
when I exit the program exit I get the error "Database is locked".
I am only trying to retrieve the information about the database (queries on
sqlite_master).

What command are you executing when you get "Database is locked" in return ?

sqlite3_close();


Have you terminated your query properly ?  Did you call sqlite_finalize() on it 
?  Did you get SQLITE_OK back from that call ?

Yes, everything is finalized. And no error on finalization is produced.
That's why I'm asking if there is a tool that can check what is open.


As I think you have surmised, you most likely have an unfinalized 
statement handle hanging around somewhere. After sqlite3_close() fails, 
use this:


  https://www.sqlite.org/c3ref/next_stmt.html

to loop through any unfinalized statement handles. If you find such a 
handle, this:


  https://www.sqlite.org/c3ref/sql.html

can be useful for figuring out where it came from.

Dan.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked

[Igor Korot]

Simon,

On Sat, Jul 9, 2016 at 7:09 PM, Simon Slavin  wrote:
>
> On 10 Jul 2016, at 12:06am, Igor Korot  wrote:
>
>> I'm trying to write some software in C{++}. Everything works fine except
>> when I exit the program exit I get the error "Database is locked".
>> I am only trying to retrieve the information about the database (queries on
>> sqlite_master).
>
> What command are you executing when you get "Database is locked" in return ?

sqlite3_close();

>
> Have you terminated your query properly ?  Did you call sqlite_finalize() on 
> it ?  Did you get SQLITE_OK back from that call ?

Yes, everything is finalized. And no error on finalization is produced.
That's why I'm asking if there is a tool that can check what is open.

Thank you.

>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked

[Simon Slavin]

On 10 Jul 2016, at 12:06am, Igor Korot  wrote:

> I'm trying to write some software in C{++}. Everything works fine except
> when I exit the program exit I get the error "Database is locked".
> I am only trying to retrieve the information about the database (queries on
> sqlite_master).

What command are you executing when you get "Database is locked" in return ?

Have you terminated your query properly ?  Did you call sqlite_finalize() on it 
?  Did you get SQLITE_OK back from that call ?

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database is locked

[Roman Fleysher]

Wow, thank you Hick! I will try it.

Roman


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Hick Gunter [h...@scigames.at]
Sent: Thursday, January 15, 2015 1:34 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] database is locked

Use the following code snippet (add error checking, set v_file to the full path 
name of your SQLite db file) to check.

It attempts to take the same locks as SQLite would, but prints the pid of the 
blocking process. It also prints the journal mode.

#include 
#include 
#ifdef AIX64
#include 
#endif
#include 
#include 
#include 

#include "sqliteInt.h"

static const char  *g_mode[] = {
"[0/0]", "[del]", "[WAL]", "[?/?]"
};

charv_buff[256];
int v_mode;

struct flockv_pending;   // = { F_WRLCK, SEEK_SET, PENDING_BYTE  ,  
 1, 0};
struct flockv_reserved;  // = { F_WRLCK, SEEK_SET, RESERVED_BYTE ,  
 1, 0};
struct flockv_shared;// = { F_WRLCK, SEEK_SET, SHARED_FIRST  , 
SHARED_SIZE, 0};
int v_fd= -1;
int v_ret   = -1;

memset( _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( _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( _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;

/* open the file readonly */
v_fd = open(v_file,O_RDONLY);

// read the file header
read(v_fd, v_buff, 100);

// interpret the bytes
switch (v_buff[18]) {
case  0: v_mode = (v_buff[18] == v_buff[19]) ? 0 : 3; break; // empty
case  1: v_mode = (v_buff[18] == v_buff[19]) ? 1 : 3; break; // 
journal_mode=delete
case  2: v_mode = (v_buff[18] == v_buff[19]) ? 2 : 3; break; // 
journal_mode=wal
default: v_mode =  3; break; // invalid
};

/* check for a PENDING lock */
fcntl(v_fd,F_GETLK,_pending);

/* check for a RESERVED lock */
fcntl(v_fd,F_GETLK,_reserved);

/* check for a SHARED/EXCLUSIVE lock */
fcntl(v_fd,F_GETLK,_shared);

/* print in ascending restrictivity */
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, \n",g_mode[v_mode] 
,v_file);


-Ursprüngliche Nachricht-
Von: Roman Fleysher [mailto:roman.fleys...@einstein.yu.edu]
Gesendet: Mittwoch, 14. Jänner 2015 18:54
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] database is locked

SQLite shell version 3.7.2
on Linux 2.6.18
NTFS




From: sqlite-users-boun...@sqlite.org<mailto:sqlite-users-boun...@sqlite.org> 
[sqlite-users-boun...@sqlite.org] on behalf of Richard Hipp [d...@sqlite.org]
Sent: Wednesday, January 14, 2015 12:50 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] database is locked

On 1/14/15, Roman Fleysher 
<roman.fleys...@einstein.yu.edu<mailto:roman.fleys...@einstein.yu.edu>> wrote:
> Dear SQLiters,
>
> There has been a lot of discussion, I remember, on this subject by others.
> Please forgive me for asking this for a millionth time.
>
> I somehow got my database in a locked state. I updated a table
> yesterday and I am rather sure that no one on our multi-user system is 
> updating it now.
> The time stamp on the file is from yesterday, showing correct time. I
> need to update a table (I use shell) and I get "database is locked".
> Is there a way to fig

Re: [sqlite] database is locked

[Hick Gunter]

Use the following code snippet (add error checking, set v_file to the full path 
name of your SQLite db file) to check.

It attempts to take the same locks as SQLite would, but prints the pid of the 
blocking process. It also prints the journal mode.

#include 
#include 
#ifdef AIX64
#include 
#endif
#include 
#include 
#include 

#include "sqliteInt.h"

static const char  *g_mode[] = {
"[0/0]", "[del]", "[WAL]", "[?/?]"
};

charv_buff[256];
int v_mode;

struct flockv_pending;   // = { F_WRLCK, SEEK_SET, PENDING_BYTE  ,  
 1, 0};
struct flockv_reserved;  // = { F_WRLCK, SEEK_SET, RESERVED_BYTE ,  
 1, 0};
struct flockv_shared;// = { F_WRLCK, SEEK_SET, SHARED_FIRST  , 
SHARED_SIZE, 0};
int v_fd= -1;
int v_ret   = -1;

memset( _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( _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( _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;

/* open the file readonly */
v_fd = open(v_file,O_RDONLY);

// read the file header
read(v_fd, v_buff, 100);

// interpret the bytes
switch (v_buff[18]) {
case  0: v_mode = (v_buff[18] == v_buff[19]) ? 0 : 3; break; // empty
case  1: v_mode = (v_buff[18] == v_buff[19]) ? 1 : 3; break; // 
journal_mode=delete
case  2: v_mode = (v_buff[18] == v_buff[19]) ? 2 : 3; break; // 
journal_mode=wal
default: v_mode =  3; break; // invalid
};

/* check for a PENDING lock */
fcntl(v_fd,F_GETLK,_pending);

/* check for a RESERVED lock */
fcntl(v_fd,F_GETLK,_reserved);

/* check for a SHARED/EXCLUSIVE lock */
fcntl(v_fd,F_GETLK,_shared);

/* print in ascending restrictivity */
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, \n",g_mode[v_mode] 
,v_file);


-Ursprüngliche Nachricht-
Von: Roman Fleysher [mailto:roman.fleys...@einstein.yu.edu]
Gesendet: Mittwoch, 14. Jänner 2015 18:54
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] database is locked

SQLite shell version 3.7.2
on Linux 2.6.18
NTFS




From: sqlite-users-boun...@sqlite.org<mailto:sqlite-users-boun...@sqlite.org> 
[sqlite-users-boun...@sqlite.org] on behalf of Richard Hipp [d...@sqlite.org]
Sent: Wednesday, January 14, 2015 12:50 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] database is locked

On 1/14/15, Roman Fleysher 
<roman.fleys...@einstein.yu.edu<mailto:roman.fleys...@einstein.yu.edu>> wrote:
> Dear SQLiters,
>
> There has been a lot of discussion, I remember, on this subject by others.
> Please forgive me for asking this for a millionth time.
>
> I somehow got my database in a locked state. I updated a table
> yesterday and I am rather sure that no one on our multi-user system is 
> updating it now.
> The time stamp on the file is from yesterday, showing correct time. I
> need to update a table (I use shell) and I get "database is locked".
> Is there a way to figure out what is happening? Clear the lock?

What operating system and filesystem are you using?  And are you specifying an 
alternative VFS for SQLite or using the default?

>
> Thank you for your help,
>
> Roman
> ___
> sqlite-users mailin

Re: [sqlite] database is locked

[Roman Fleysher]

Thank you!!

Roman

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Richard Hipp [d...@sqlite.org]
Sent: Wednesday, January 14, 2015 1:31 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] database is locked

On 1/14/15, Roman Fleysher <roman.fleys...@einstein.yu.edu> wrote:
> Thank you, Richard.
>
> You are correct, I made a typo: we have NFS not NTFS and I know they are
> buggy. I always use the same node on our compute cluster to minimize
> buffering issue. So, are you saying I can not clear the database lock and
> must rebuild the database?

Probably you can just restart the NFS lock manager.  Rebuilding the
database seems a bit extreme.


>
> Roman
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Richard Hipp [d...@sqlite.org]
> Sent: Wednesday, January 14, 2015 1:26 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] database is locked
>
> On 1/14/15, Roman Fleysher <roman.fleys...@einstein.yu.edu> wrote:
>> SQLite shell version 3.7.2
>> on Linux 2.6.18
>> NTFS
>
> On Linux, SQLite uses posix advisory locks.  All locks are
> automatically released when the process dies (if they haven't been
> already).  If you have stuck locks, that indicates that you either
> have a stuck process or a busted filesystem.  I'm not sure how well
> NTFS plays with Linux.  I thought linux could only read NTFS.
>
> If you are using a network filesystem, all bets are off.  Those are
> usually pretty buggy with respect to locking.
>
>>
>>
>>
>> 
>> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
>> on
>> behalf of Richard Hipp [d...@sqlite.org]
>> Sent: Wednesday, January 14, 2015 12:50 PM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] database is locked
>>
>> On 1/14/15, Roman Fleysher <roman.fleys...@einstein.yu.edu> wrote:
>>> Dear SQLiters,
>>>
>>> There has been a lot of discussion, I remember, on this subject by
>>> others.
>>> Please forgive me for asking this for a millionth time.
>>>
>>> I somehow got my database in a locked state. I updated a table yesterday
>>> and
>>> I am rather sure that no one on our multi-user system is updating it
>>> now.
>>> The time stamp on the file is from yesterday, showing correct time. I
>>> need
>>> to update a table (I use shell) and I get "database is locked". Is there
>>> a
>>> way to figure out what is happening? Clear the lock?
>>
>> What operating system and filesystem are you using?  And are you
>> specifying an alternative VFS for SQLite or using the default?
>>
>>>
>>> Thank you for your help,
>>>
>>> Roman
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> 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
>>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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
>


--
D. Richard Hipp
d...@sqlite.org
___
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

Re: [sqlite] database is locked

[Richard Hipp]

On 1/14/15, Roman Fleysher <roman.fleys...@einstein.yu.edu> wrote:
> Thank you, Richard.
>
> You are correct, I made a typo: we have NFS not NTFS and I know they are
> buggy. I always use the same node on our compute cluster to minimize
> buffering issue. So, are you saying I can not clear the database lock and
> must rebuild the database?

Probably you can just restart the NFS lock manager.  Rebuilding the
database seems a bit extreme.


>
> Roman
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Richard Hipp [d...@sqlite.org]
> Sent: Wednesday, January 14, 2015 1:26 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] database is locked
>
> On 1/14/15, Roman Fleysher <roman.fleys...@einstein.yu.edu> wrote:
>> SQLite shell version 3.7.2
>> on Linux 2.6.18
>> NTFS
>
> On Linux, SQLite uses posix advisory locks.  All locks are
> automatically released when the process dies (if they haven't been
> already).  If you have stuck locks, that indicates that you either
> have a stuck process or a busted filesystem.  I'm not sure how well
> NTFS plays with Linux.  I thought linux could only read NTFS.
>
> If you are using a network filesystem, all bets are off.  Those are
> usually pretty buggy with respect to locking.
>
>>
>>
>>
>> 
>> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
>> on
>> behalf of Richard Hipp [d...@sqlite.org]
>> Sent: Wednesday, January 14, 2015 12:50 PM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] database is locked
>>
>> On 1/14/15, Roman Fleysher <roman.fleys...@einstein.yu.edu> wrote:
>>> Dear SQLiters,
>>>
>>> There has been a lot of discussion, I remember, on this subject by
>>> others.
>>> Please forgive me for asking this for a millionth time.
>>>
>>> I somehow got my database in a locked state. I updated a table yesterday
>>> and
>>> I am rather sure that no one on our multi-user system is updating it
>>> now.
>>> The time stamp on the file is from yesterday, showing correct time. I
>>> need
>>> to update a table (I use shell) and I get "database is locked". Is there
>>> a
>>> way to figure out what is happening? Clear the lock?
>>
>> What operating system and filesystem are you using?  And are you
>> specifying an alternative VFS for SQLite or using the default?
>>
>>>
>>> Thank you for your help,
>>>
>>> Roman
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> 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
>>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database is locked

[Roman Fleysher]

Thank you, Richard.

You are correct, I made a typo: we have NFS not NTFS and I know they are buggy. 
I always use the same node on our compute cluster to minimize buffering issue. 
So, are you saying I can not clear the database lock and must rebuild the 
database? 

Roman

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Richard Hipp [d...@sqlite.org]
Sent: Wednesday, January 14, 2015 1:26 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] database is locked

On 1/14/15, Roman Fleysher <roman.fleys...@einstein.yu.edu> wrote:
> SQLite shell version 3.7.2
> on Linux 2.6.18
> NTFS

On Linux, SQLite uses posix advisory locks.  All locks are
automatically released when the process dies (if they haven't been
already).  If you have stuck locks, that indicates that you either
have a stuck process or a busted filesystem.  I'm not sure how well
NTFS plays with Linux.  I thought linux could only read NTFS.

If you are using a network filesystem, all bets are off.  Those are
usually pretty buggy with respect to locking.

>
>
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Richard Hipp [d...@sqlite.org]
> Sent: Wednesday, January 14, 2015 12:50 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] database is locked
>
> On 1/14/15, Roman Fleysher <roman.fleys...@einstein.yu.edu> wrote:
>> Dear SQLiters,
>>
>> There has been a lot of discussion, I remember, on this subject by
>> others.
>> Please forgive me for asking this for a millionth time.
>>
>> I somehow got my database in a locked state. I updated a table yesterday
>> and
>> I am rather sure that no one on our multi-user system is updating it now.
>> The time stamp on the file is from yesterday, showing correct time. I
>> need
>> to update a table (I use shell) and I get "database is locked". Is there
>> a
>> way to figure out what is happening? Clear the lock?
>
> What operating system and filesystem are you using?  And are you
> specifying an alternative VFS for SQLite or using the default?
>
>>
>> Thank you for your help,
>>
>> Roman
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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
>


--
D. Richard Hipp
d...@sqlite.org
___
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

Re: [sqlite] database is locked

[Richard Hipp]

On 1/14/15, Roman Fleysher <roman.fleys...@einstein.yu.edu> wrote:
> SQLite shell version 3.7.2
> on Linux 2.6.18
> NTFS

On Linux, SQLite uses posix advisory locks.  All locks are
automatically released when the process dies (if they haven't been
already).  If you have stuck locks, that indicates that you either
have a stuck process or a busted filesystem.  I'm not sure how well
NTFS plays with Linux.  I thought linux could only read NTFS.

If you are using a network filesystem, all bets are off.  Those are
usually pretty buggy with respect to locking.

>
>
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Richard Hipp [d...@sqlite.org]
> Sent: Wednesday, January 14, 2015 12:50 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] database is locked
>
> On 1/14/15, Roman Fleysher <roman.fleys...@einstein.yu.edu> wrote:
>> Dear SQLiters,
>>
>> There has been a lot of discussion, I remember, on this subject by
>> others.
>> Please forgive me for asking this for a millionth time.
>>
>> I somehow got my database in a locked state. I updated a table yesterday
>> and
>> I am rather sure that no one on our multi-user system is updating it now.
>> The time stamp on the file is from yesterday, showing correct time. I
>> need
>> to update a table (I use shell) and I get "database is locked". Is there
>> a
>> way to figure out what is happening? Clear the lock?
>
> What operating system and filesystem are you using?  And are you
> specifying an alternative VFS for SQLite or using the default?
>
>>
>> Thank you for your help,
>>
>> Roman
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database is locked

[Simon Slavin]

On 14 Jan 2015, at 5:30pm, Roman Fleysher  
wrote:

> Is there a way to figure out what is happening? Clear the lock?

We would need specifics of your system to answer this absolutely correctly.  
But you can try these things in turn until one of them works:

Unmount the volume the database is on, then mount it again.
Log out, then log in again.
Reboot the computer.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database is locked

[Roman Fleysher]

SQLite shell version 3.7.2
on Linux 2.6.18
NTFS




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Richard Hipp [d...@sqlite.org]
Sent: Wednesday, January 14, 2015 12:50 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] database is locked

On 1/14/15, Roman Fleysher <roman.fleys...@einstein.yu.edu> wrote:
> Dear SQLiters,
>
> There has been a lot of discussion, I remember, on this subject by others.
> Please forgive me for asking this for a millionth time.
>
> I somehow got my database in a locked state. I updated a table yesterday and
> I am rather sure that no one on our multi-user system is updating it now.
> The time stamp on the file is from yesterday, showing correct time. I need
> to update a table (I use shell) and I get "database is locked". Is there a
> way to figure out what is happening? Clear the lock?

What operating system and filesystem are you using?  And are you
specifying an alternative VFS for SQLite or using the default?

>
> Thank you for your help,
>
> Roman
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
d...@sqlite.org
___
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

Re: [sqlite] database is locked

[Richard Hipp]

On 1/14/15, Roman Fleysher  wrote:
> Dear SQLiters,
>
> There has been a lot of discussion, I remember, on this subject by others.
> Please forgive me for asking this for a millionth time.
>
> I somehow got my database in a locked state. I updated a table yesterday and
> I am rather sure that no one on our multi-user system is updating it now.
> The time stamp on the file is from yesterday, showing correct time. I need
> to update a table (I use shell) and I get "database is locked". Is there a
> way to figure out what is happening? Clear the lock?

What operating system and filesystem are you using?  And are you
specifying an alternative VFS for SQLite or using the default?

>
> Thank you for your help,
>
> Roman
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database is locked" for SQLITE_BUSY

[Simon Slavin]

On 4 Dec 2014, at 5:36pm, Jonathan Moules  wrote:

> Depending on the application, an end user likely won't see the error code, 
> but instead just the error message 

SQlite is not a program.  It's an API, intended for use by a programmer.  Those 
error codes should not be reported to the end user.  They are intended for the 
user of the API: the programmer.  An end user is not expected to know what 
something like

SQLITE_BUSY_SNAPSHOT 

means.  What the programmer has their program do about them is the heart of the 
matter.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database is locked" for SQLITE_BUSY

[Jonathan Moules]

Thanks for the comments.
I appreciate there are nuances of their differences that are likely obvious to 
a developer during development based on the current phrasing, but I'm putting 
this forward from a user's perspective.

Depending on the application, an end user likely won't see the error code, but 
instead just the error message (at least the application where I encountered 
this issue). They then google for "sqlite database locked" but get back 
information about SQLITE_LOCKED which isn't the same thing as SQLITE_BUSY. It 
doesn't particularly matter too much what the message is so long as it helps 
the user correctly find what it actually relates to in the documentation.

Using my simplistic understanding of the differences, what about these two:

SQLITE_BUSY - "Database is busy, locked by another connection" (some use of 
"busy" which should make googling for the actual problem easier to find).
SQLITE_LOCKED - "Database table is locked"

Cheers,
Jonathan

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Keith Medcalf
Sent: Wednesday, December 03, 2014 3:21 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] "database is locked" for SQLITE_BUSY

>From https://www.sqlite.org/rescode.html#busy

In both cases there are specific extended codes that may further pinpoint the 
source just in case you do not know what you are doing at the time the result 
code was returned.  Interpretation is only difficult if you do not know what 
you are doing when the result code was returned.


(5) SQLITE_BUSY
The SQLITE_BUSY result code indicates that the database file could not be 
written (or in some cases read) because of concurrent activity by some other 
database connection, usually a database connection in a separate process.

For example, if process A is in the middle of a large write transaction and at 
the same time process B attempts to start a new write transaction, process B 
will get back an SQLITE_BUSY result because SQLite only supports one writer at 
a time. Process B will need to wait for process A to finish its transaction 
before starting a new transaction. The sqlite3_busy_timeout() and 
sqlite3_busy_handler() interfaces and the busy_timeout pragma are available to 
process B to help it deal with SQLITE_BUSY errors.

An SQLITE_BUSY error can occur at any point in a transaction: when the 
transaction is first started, during any write or update operations, or when 
the transaction commits. To avoid encountering SQLITE_BUSY errors in the middle 
of a transaction, the application can use BEGIN IMMEDIATE instead of just BEGIN 
to start a transaction. The BEGIN IMMEDIATE command might itself return 
SQLITE_BUSY, but if it succeeds, then SQLite guarantees that no subsequent 
operations on the same database through the next COMMIT will return SQLITE_BUSY.

See also: SQLITE_BUSY_RECOVERY and SQLITE_BUSY_SNAPSHOT.

The SQLITE_BUSY result code differs from SQLITE_LOCKED in that SQLITE_BUSY 
indicates a conflict with a separate database connection, probably in a 
separate process, whereas SQLITE_LOCKED indicates a conflict within the same 
database connection (or sometimes a database connection with a shared cache).

(6) SQLITE_LOCKED
The SQLITE_LOCKED result code indicates that a write operation could not 
continue because of a conflict within the same database connection or a 
conflict with a different database connection that uses a shared cache.

For example, a DROP TABLE statement cannot be run while another thread is 
reading from that table on the same database connection because dropping the 
table would delete the table out from under the concurrent reader.

The SQLITE_LOCKED result code differs from SQLITE_BUSY in that SQLITE_LOCKED 
indicates a conflict on the same database connection (or on a connection with a 
shared cache) whereas SQLITE_BUSY indicates a conflict with a different 
database connection, probably in a different process.

(261) SQLITE_BUSY_RECOVERY
The SQLITE_BUSY_RECOVERY error code is an extended error code for SQLITE_BUSY 
that indicates that an operation could not continue because another process is 
busy recovering a WAL mode database file following a crash. The 
SQLITE_BUSY_RECOVERY error code only occurs on WAL mode databases.

(517) SQLITE_BUSY_SNAPSHOT
The SQLITE_BUSY_SNAPSHOT error code is an extended error code for SQLITE_BUSY 
that occurs on WAL mode databases when a database connection tries to promote a 
read transaction into a write transaction but finds that another database 
connection has already written to the database and thus invalidated prior reads.

The following scenario illustrates how an SQLITE_BUSY_SNAPSHOT error might 
arise:

Process A starts a read transaction on the database and does one or more 
SELECT statement. Process A keeps the transaction open.
Process B updates the database, changing values previou

Re: [sqlite] "database is locked" for SQLITE_BUSY

[Simon Slavin]

On 3 Dec 2014, at 3:20pm, Richard Hipp  wrote:

> https://www.sqlite.org/rescode.html#busy

Thanks, Richard.  I have somehow never seen that.

I had no idea that the difference between _BUSY and _LOCKED was purely about 
whether the conflicting access was from the same connection.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database is locked" for SQLITE_BUSY

[Keith Medcalf]

ory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Stephen Chrzanowski
>Sent: Wednesday, 3 December, 2014 07:47
>To: General Discussion of SQLite Database
>Subject: Re: [sqlite] "database is locked" for SQLITE_BUSY
>
>To me, a BUSY state would mean that everything up to actually reading or
>writing the data out is valid, but, the response time coming back was
>just
>too long, so a timeout hit which might mean that a retry later might be
>appropriate.  To me, a timeout = busy, but, locked != busy.  When
>something
>is locked, you're basically denied being able to perform the function for
>one reason or another.  If the file or connection is R/O, that'd be a
>valid
>locked error result for write functions.  If the connection was alive, a
>write to the database was asked, but it took too long to complete, then,
>BUSY would make sense to me.  If the connection was able to send out one
>successful write to the database/WAL, but later down the road the
>transaction took too long to complete, then maybe a BUSY error back would
>be appropriate, but, if the first write failed, then LOCKED might be
>appropriate.
>
>If anything was written to the the DB itself, or the journal file, then
>during the life of that transaction, if write attempt takes too long, the
>result would be BUSY.  If no write was done but took too long, then a
>LOCKED error is the error result.
>
>Disclaimer: I've not had my pot of coffee yet, so I might be missing a
>few
>tidbits of information both in what I've written above, as well as the
>mental think-through when I wrote it. :]
>
>On Wed, Dec 3, 2014 at 9:23 AM, Simon Slavin <slav...@bigfraud.org>
>wrote:
>
>>
>> On 3 Dec 2014, at 2:20pm, Stephen Chrzanowski <pontia...@gmail.com>
>wrote:
>>
>> > Although I think there is already an error result, one situation
>might be
>> > when the DB is in a read only state.
>>
>> I just thought of the database /file/ being marked 'read-only'.  But it
>> turns out that there's a different SQLite result code for that
>situation.
>>
>> Simon.
>> ___
>> 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



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database is locked" for SQLITE_BUSY

[Richard Hipp]

On Wed, Dec 3, 2014 at 10:18 AM, Simon Slavin  wrote:

>
> On 3 Dec 2014, at 3:10pm, Hick Gunter  wrote:
>
> > SQLITE_BUSY means that some connection is BUSY with a write transaction
> and has locked the database file; presumably, it will be possible to write
> to the database when the current writer has finished, just not now or
> within the specified busy timeout.
> >
> > SQLITE_LOCKED otoh means that the calling application is in error and
> has specified two or more transactions whose table access modes are
> incompatible and whose table access orders differ. This situation is
> resolvable only if at least one involved transaction is rolled back.
>
> This is very illuminating and far better information than I managed to
> find in the official SQLite documentation.  It would be really helpful if
> something like this could be incorporated in an appropriate place.
>
>
https://www.sqlite.org/rescode.html#busy

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database is locked" for SQLITE_BUSY

[Simon Slavin]

On 3 Dec 2014, at 3:10pm, Hick Gunter  wrote:

> SQLITE_BUSY means that some connection is BUSY with a write transaction and 
> has locked the database file; presumably, it will be possible to write to the 
> database when the current writer has finished, just not now or within the 
> specified busy timeout.
> 
> SQLITE_LOCKED otoh means that the calling application is in error and has 
> specified two or more transactions whose table access modes are incompatible 
> and whose table access orders differ. This situation is resolvable only if at 
> least one involved transaction is rolled back.

This is very illuminating and far better information than I managed to find in 
the official SQLite documentation.  It would be really helpful if something 
like this could be incorporated in an appropriate place.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database is locked" for SQLITE_BUSY

[Hick Gunter]

I think the error messages are distinct enough as is.

SQLITE_BUSY means that some connection is BUSY with a write transaction and has 
locked the database file; presumably, it will be possible to write to the 
database when the current writer has finished, just not now or within the 
specified busy timeout.

SQLITE_LOCKED otoh means that the calling application is in error and has 
specified two or more transactions whose table access modes are incompatible 
and whose table access orders differ. This situation is resolvable only if at 
least one involved transaction is rolled back.


const char *sqlite3ErrStr(int rc){
  static const char* const aMsg[] = {
...
/* SQLITE_BUSY*/ "database is locked",
/* SQLITE_LOCKED  */ "database table is locked",
...
  };

-Ursprüngliche Nachricht-
Von: Jonathan Moules [mailto:j.mou...@hrwallingford.com]
Gesendet: Mittwoch, 03. Dezember 2014 12:01
An: 'General Discussion of SQLite Database'
Betreff: [sqlite] "database is locked" for SQLITE_BUSY

Hi,
Just a quick request/suggestion.

Currently SQLITE_BUSY events return an error of "Database is locked". Is it 
possible to change this to "Database is busy" or something similar?
I ask because when someone then goes googling for "SQLite database locked", 
they'll end up thinking they're hitting the SQLITE_LOCKED event which as best I 
can tell is very different to the SQLITE_BUSY event.
I'm sure I can't be the only person who has been tripped up by that one.

Thanks,
Jonathan



HR Wallingford and its subsidiaries uses faxes and emails for confidential and 
legally privileged business communications. They do not of themselves create 
legal commitments. Disclosure to parties other than addressees requires our 
specific consent. We are not liable for unauthorised disclosures nor reliance 
upon them.
If you have received this message in error please advise us immediately and 
destroy all copies of it.

HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom Registered in 
England No. 02562099


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/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: h...@scigames.at

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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database is locked" for SQLITE_BUSY

[Stephen Chrzanowski]

To me, a BUSY state would mean that everything up to actually reading or
writing the data out is valid, but, the response time coming back was just
too long, so a timeout hit which might mean that a retry later might be
appropriate.  To me, a timeout = busy, but, locked != busy.  When something
is locked, you're basically denied being able to perform the function for
one reason or another.  If the file or connection is R/O, that'd be a valid
locked error result for write functions.  If the connection was alive, a
write to the database was asked, but it took too long to complete, then,
BUSY would make sense to me.  If the connection was able to send out one
successful write to the database/WAL, but later down the road the
transaction took too long to complete, then maybe a BUSY error back would
be appropriate, but, if the first write failed, then LOCKED might be
appropriate.

If anything was written to the the DB itself, or the journal file, then
during the life of that transaction, if write attempt takes too long, the
result would be BUSY.  If no write was done but took too long, then a
LOCKED error is the error result.

Disclaimer: I've not had my pot of coffee yet, so I might be missing a few
tidbits of information both in what I've written above, as well as the
mental think-through when I wrote it. :]

On Wed, Dec 3, 2014 at 9:23 AM, Simon Slavin  wrote:

>
> On 3 Dec 2014, at 2:20pm, Stephen Chrzanowski  wrote:
>
> > Although I think there is already an error result, one situation might be
> > when the DB is in a read only state.
>
> I just thought of the database /file/ being marked 'read-only'.  But it
> turns out that there's a different SQLite result code for that situation.
>
> Simon.
> ___
> 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

Re: [sqlite] "database is locked" for SQLITE_BUSY

[Simon Slavin]

On 3 Dec 2014, at 2:20pm, Stephen Chrzanowski  wrote:

> Although I think there is already an error result, one situation might be
> when the DB is in a read only state.

I just thought of the database /file/ being marked 'read-only'.  But it turns 
out that there's a different SQLite result code for that situation.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database is locked" for SQLITE_BUSY

[Stephen Chrzanowski]

Although I think there is already an error result, one situation might be
when the DB is in a read only state.

On Wed, Dec 3, 2014 at 6:15 AM, RSmith  wrote:

>
> On 2014/12/03 13:00, Jonathan Moules wrote:
>
>> Hi,
>> Just a quick request/suggestion.
>>
>> Currently SQLITE_BUSY events return an error of "Database is locked". Is
>> it possible to change this to "Database is busy" or something similar?
>> I ask because when someone then goes googling for "SQLite database
>> locked", they'll end up thinking they're hitting the SQLITE_LOCKED event
>> which as best I can tell is very different to the SQLITE_BUSY event.
>> I'm sure I can't be the only person who has been tripped up by that one.
>>
>
> I see your point, but is it not in principle the exact same thing? How is
> it "very different"? i.e. - Why else would a DB be "Locked" other than
> being explicitly busy? Or, why else would it be "Busy" other than being
> specifically locked?
>
> Is there an event for which the DB can be "Locked" but not necessarily
> because it is busy?  If so I would like to second this request, but I am
> unaware currently that the case exists.
>
> (Would the case where a DB was opened exclusively by another connection
> return a different error?  I have not checked this yet).
>
>
> ___
> 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

Re: [sqlite] "database is locked" for SQLITE_BUSY

[RSmith]

On 2014/12/03 13:00, Jonathan Moules wrote:

Hi,
Just a quick request/suggestion.

Currently SQLITE_BUSY events return an error of "Database is locked". Is it possible to 
change this to "Database is busy" or something similar?
I ask because when someone then goes googling for "SQLite database locked", 
they'll end up thinking they're hitting the SQLITE_LOCKED event which as best I can tell 
is very different to the SQLITE_BUSY event.
I'm sure I can't be the only person who has been tripped up by that one.


I see your point, but is it not in principle the exact same thing? How is it "very different"? i.e. - Why else would a DB be 
"Locked" other than being explicitly busy? Or, why else would it be "Busy" other than being specifically locked?


Is there an event for which the DB can be "Locked" but not necessarily because it is busy?  If so I would like to second this 
request, but I am unaware currently that the case exists.


(Would the case where a DB was opened exclusively by another connection return 
a different error?  I have not checked this yet).

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked exceptions

[Sohail Somani]

On 2014-10-29, 12:13 PM, Mike McWhinney wrote:

System.Windows.Forms.Control.ControlNativeWindow.OnThreadException(Exception
  e)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg,
  IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&
  msg)
at
System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32
  dwComponentID, Int32 reason, Int32 pvLoopData)
at


I'm not very knowledgeable about Windows forms but if you are writing 
the SQLite database from multiple threads, that could be the reason. The 
main reasons I've seen this happen are when something is trying to write 
to the database when something else has it open for a read.


If you are using threads, then ensure that reader threads exhaust their 
result sets. Unfortunately some third party libraries do lazy loading 
which doesn't work well with SQLite. Pseudo-code:


# guithread.pseudo
def fetchResults(query):
  while(query.hasMore())
query.fetchMore() # without this, the sqlite result is active...

# writethread.pseudo
def execQuery(query):
  query.exec() # ...which would lock this

If you are not using threads, then it is possible that having the DB 
itself on a network share is causing the problem. I seem to recall some 
issues along these lines in the docs but I don't have any personal 
experience.


Sohail

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked exceptions

[Simon Slavin]

On 29 Oct 2014, at 4:13pm, Mike McWhinney  wrote:

> "URI=file:mydb.db; default timeout=10; Pooling=True; Max Pool Size=100;";

Can you please change your timeout to 1 (really, 1ms == 10 seconds) and 
see if this makes the problems go away ?

It may not be necessary to leave the setting like that.  But the information 
about whether this does or doesn't solve the problem may tell us what the 
problem is.

> One thing is that I have tried a method where I open the database and leave 
> it open through out the life
> of the program (open it when I start the program and close on exit). Should I 
> perhaps change this so that 
> I open the database, perform the query, then close it right away?

One usually keeps the database open for the life of the program, the way you 
have it working already.  So lets try other solutions first.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked !

[Fabrice MAUPIN]

I found the problem : In fact I forgot well one "close" call.

Sorry

Fabrice


-Message d'origine-
De : sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] De la part de Fabrice MAUPIN
Envoyé : mercredi 27 août 2014 15:27
À : 'General Discussion of SQLite Database'
Cc : 'Alex Trucy'
Objet : Re: [sqlite] Database is locked !

For information, you will find the "SQLiteJdbc" class used.

I verified (normally) after each statement there is a db.close().

Fabrice


-Message d'origine-
De : sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] De la part de J Decker Envoyé :
mercredi 27 août 2014 15:19 À : General Discussion of SQLite Database Cc :
Alex Trucy Objet : Re: [sqlite] Database is locked !


Sounds like you have statements laying around that weren't properly
destroyed... you say executed correctly, but then also released correctly?


On Wed, Aug 27, 2014 at 6:14 AM, Fabrice MAUPIN <fmau...@iback.fr> wrote:

> Hi all,
>
>
>
> I try to execute an "INSERT" query and I obtain this message : 
> "Database is locked".
>
>
>
> I verified all the previous connections which were opened were closed 
> and all the previous statements were correctly executed (UPDATE, 
> INSERT,
.).
>
>
>
> The volume of data to be handled is very low.
>
>
>
> For information my configuration :
>
>
> Windows 7 Pro french x64
>
> Eclipse LUNA (4.4) x64
>
> JavaFX8
>
> Sqlite-jdbc-3.7.15-M1.jar
>
>
>
> What are the use cases which could reveal this message (to part them 
> missing
> "close") ?
>
>
>
> If you have a idea.
>
>
>
> Thanks
>
>
>
> Fabrice
>
>
>
>
>
>
>
>
>
> ___
> 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

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked !

[Fabrice MAUPIN]

For information, you will find the "SQLiteJdbc" class used.

I verified (normally) after each statement there is a db.close().

Fabrice


-Message d'origine-
De : sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] De la part de J Decker
Envoyé : mercredi 27 août 2014 15:19
À : General Discussion of SQLite Database
Cc : Alex Trucy
Objet : Re: [sqlite] Database is locked !


Sounds like you have statements laying around that weren't properly
destroyed... you say executed correctly, but then also released correctly?


On Wed, Aug 27, 2014 at 6:14 AM, Fabrice MAUPIN <fmau...@iback.fr> wrote:

> Hi all,
>
>
>
> I try to execute an "INSERT" query and I obtain this message : 
> "Database is locked".
>
>
>
> I verified all the previous connections which were opened were closed 
> and all the previous statements were correctly executed (UPDATE, INSERT,
.).
>
>
>
> The volume of data to be handled is very low.
>
>
>
> For information my configuration :
>
>
> Windows 7 Pro french x64
>
> Eclipse LUNA (4.4) x64
>
> JavaFX8
>
> Sqlite-jdbc-3.7.15-M1.jar
>
>
>
> What are the use cases which could reveal this message (to part them 
> missing
> "close") ?
>
>
>
> If you have a idea.
>
>
>
> Thanks
>
>
>
> Fabrice
>
>
>
>
>
>
>
>
>
> ___
> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked !

[J Decker]

Sounds like you have statements laying around that weren't properly
destroyed... you say executed correctly, but then also released correctly?


On Wed, Aug 27, 2014 at 6:14 AM, Fabrice MAUPIN  wrote:

> Hi all,
>
>
>
> I try to execute an "INSERT" query and I obtain this message : "Database is
> locked".
>
>
>
> I verified all the previous connections which were opened were closed and
> all the previous statements were correctly executed (UPDATE, INSERT, .).
>
>
>
> The volume of data to be handled is very low.
>
>
>
> For information my configuration :
>
>
> Windows 7 Pro french x64
>
> Eclipse LUNA (4.4) x64
>
> JavaFX8
>
> Sqlite-jdbc-3.7.15-M1.jar
>
>
>
> What are the use cases which could reveal this message (to part them
> missing
> "close") ?
>
>
>
> If you have a idea.
>
>
>
> Thanks
>
>
>
> Fabrice
>
>
>
>
>
>
>
>
>
> ___
> 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

Re: [sqlite] Database gets locked for other processes

[Israel Lins Albuquerque]

I know well the qt sqlite to say, maybe you aren't destroing the QSqlQuery 
class or simple call finish function, and the statement is openned helding the 
lock!

Enviado via iPhone

> Em 29/10/2013, às 10:51, Stephan Beal  escreveu:
> 
>> On Tue, Oct 29, 2013 at 1:52 PM, Martin  wrote:
>> 
>> The program is running on Windows7.
>> ...
> 
> The program runs parallel on multiple machines all sharing the same
>> SQLite-Database-file.
> 
> 
> Connecting multiple clients over a network share is a sure-fire way to
> corrupt your database. See the bottom half of this page:
> 
> http://www.sqlite.org/whentouse.html
> 
> as well as any number of threads in this mailing list archives regarding
> this topic.
> 
> -- 
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> "Since tyranny's the only guaranteed byproduct of those who insist on a
> perfect world, freedom will have to do." -- Bigby Wolf
> ___
> 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

Re: [sqlite] Database gets locked for other processes

[jose isaias cabrera]

"Martin Kropfinger"

Am Tue, 29 Oct 2013 12:00:02 -0400
schrieb sqlite-users-requ...@sqlite.org:


Date: Tue, 29 Oct 2013 14:51:08 +0100
From: Stephan Beal <sgb...@googlemail.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Subject: Re: [sqlite] Database gets locked for other processes
Message-ID:
<cakd4naigijjvwbks7z2-s_v3j+ingts5ugrnft3krjjytex...@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1

On Tue, Oct 29, 2013 at 1:52 PM, Martin <free...@rakor-net.de> wrote:

> The program is running on Windows7.
> ...

The program runs parallel on multiple machines all sharing the same
> SQLite-Database-file.


Connecting multiple clients over a network share is a sure-fire way to
corrupt your database. See the bottom half of this page:

http://www.sqlite.org/whentouse.html

as well as any number of threads in this mailing list archives
regarding this topic.



I can understand this. But I need to have the database unconnected to a
special sever. In fact it has the same issues if I run more than one
program on the same machine tying to access the same lokal databasefile.


First of all, everything that every one has said here damaging the DB, is 
correct. so, if you want to continue, read on...


I have a system of 10 people that use a program manager tool that uses 
SQLite3 as the main DB using a server shared db.  I have been able to use it 
by putting a block outside of SQLite when someone is writing to the DB. 
What I do is that I have a place in the same server, or even same spot where 
the DB lives,  that a file will be created before someone is going to write 
to the DB.  This file will tell the tool that someone is writing to the DB 
and no one else will be allow to write.  They can read, but not write.  When 
the tool is going to write to the DB, it checks to see if that file is 
there.  If it is, it waits for a few seconds and it checks again until that 
file is deleted. However, this causes the users, sometimes, to complain of 
slow response.  I tell them what is going on, but they still harrass me. 
Anyway, again, doing this could damaged your DB, but if you really need this 
to work, there are ways of working around it.  By the way, before figuring 
this out, I damaged my DB a few times, so you better make sure you have a 
good backup system.  Ihth.


josé 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database gets locked for other processes

[Martin Kropfinger]

Am Tue, 29 Oct 2013 12:00:02 -0400
schrieb sqlite-users-requ...@sqlite.org:

> Date: Tue, 29 Oct 2013 14:51:08 +0100
> From: Stephan Beal <sgb...@googlemail.com>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Subject: Re: [sqlite] Database gets locked for other processes
> Message-ID:
>   <cakd4naigijjvwbks7z2-s_v3j+ingts5ugrnft3krjjytex...@mail.gmail.com>
> Content-Type: text/plain; charset=ISO-8859-1
> 
> On Tue, Oct 29, 2013 at 1:52 PM, Martin <free...@rakor-net.de> wrote:
> 
> > The program is running on Windows7.
> > ...  
> 
> The program runs parallel on multiple machines all sharing the same
> > SQLite-Database-file.  
> 
> 
> Connecting multiple clients over a network share is a sure-fire way to
> corrupt your database. See the bottom half of this page:
> 
> http://www.sqlite.org/whentouse.html
> 
> as well as any number of threads in this mailing list archives
> regarding this topic.
> 

I can understand this. But I need to have the database unconnected to a
special sever. In fact it has the same issues if I run more than one
program on the same machine tying to access the same lokal databasefile.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database gets locked for other processes

[Stephan Beal]

On Tue, Oct 29, 2013 at 1:52 PM, Martin  wrote:

> The program is running on Windows7.
> ...

The program runs parallel on multiple machines all sharing the same
> SQLite-Database-file.


Connecting multiple clients over a network share is a sure-fire way to
corrupt your database. See the bottom half of this page:

http://www.sqlite.org/whentouse.html

as well as any number of threads in this mailing list archives regarding
this topic.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Since tyranny's the only guaranteed byproduct of those who insist on a
perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database gets locked for other processes

[Richard Hipp]

Sounds like something changed in Qt 5.1.1 so that it is holding open a read
transaction.  The first process acquires the read transaction, which
permits other processes to read but prevents anybody else from writing.  I
have no idea why Qt would do this, though.

You can change to WAL mode, which will allow other processes to write while
the original process holds the read transaction open.  But with the read
transaction active, you'll never be able to checkpoint and so the WAL file
will grow without bound.  Things will appear to work at first, but you will
eventual run into performance problems with a huge WAL file.

I think your goal should be to figure out why Qt 5.1.1 is holding open the
read transaction.


On Tue, Oct 29, 2013 at 8:52 AM, Martin  wrote:

> Hi there!
>
> I have a program written in Qt and using the SQLite-drivers shipped with
> Qt.
> AFAIK those are the original SQLite-drivers. The program is running on
> Windows7.
>
> Originally the program was written using Qt4.8 (which came with SQLite
> 3.7.14.1). The program runs parallel on multiple machines all sharing the
> same
> SQLite-Database-file. The programs have 99% reading access (SELECT). Some
> times
> there will be some data written/changed. All clients may change the data.
> Until now this worked without issues.
>
> Now I am porting the program from Qt 4.8 to Qt 5.1.1 (shipping SQLite
> 3.7.17)
> and am running in trouble with parallel access to the database-file. The
> first
> program accessing the database can work normally with the database. But all
> programs starting
> later can only access the database for reading. If one of them wants to
> write
> data in the database I get a "database-locked-error".
>
> Can you tell me if I am doing something wrong, or if there is a good
> reason for
> this habbit?
>
> All the programs connect to database at startup and close the
> databaseconnection
> on program close.
>
> Thanks for your help
>
> Martin
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database is locked with create

[Choi, David]

Hi Richard,

Thank you Richard. 

I get the latest source(sqlite-amalgamation-3071502.zip) and cross-compile. And 
follow your instruction:
sqlite3 -vfs unix-none ex1

That issue is gone.

Really appreciated for your quick and exact response.

David J. Choi


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Tuesday, February 19, 2013 4:04 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] database is locked with create

On Tue, Feb 19, 2013 at 6:37 PM, Choi, David <david.c...@micrel.com> wrote:

> Hi Richard,
>
> It seems that my version is not accept the command. By the way, what 
> is the version that you are referring to?
>

Get the latest: 3.7.15.1.  There is no reason not to.


>
> Here is my operation:
> /home # sqlite3 --help
> Usage: sqlite3 [OPTIONS] FILENAME [SQL] FILENAME is the name of an 
> SQLite database. A new database is created if the file does not 
> previously exist.
> OPTIONS include:
>-helpshow this message
>-init filename   read/process named file
>-echoprint commands before execution
>-[no]header  turn headers on or off
>-bailstop after hitting an error
>-interactive force interactive I/O
>-batch   force batch I/O
>-column  set output mode to 'column'
>-csv set output mode to 'csv'
>-htmlset output mode to HTML
>-lineset output mode to 'line'
>-listset output mode to 'list'
>-separator 'x'   set output field separator (|)
>-nullvalue 'text'set text string for NULL values
>-version show SQLite version
> /home # sqlite3 -version
> 3.6.23.1
> /home # sqlite3 -vfs unix-none ex1
> sqlite3: Error: unknown option: -vfs
> Use -help for a list of options.
>
>
> Regards,
> David J. Choi
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
> Sent: Tuesday, February 19, 2013 1:44 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] database is locked with create
>
> On Tue, Feb 19, 2013 at 4:39 PM, Choi, David <david.c...@micrel.com>
> wrote:
>
> > Hi Richard,
> >
> > I think your guess is highly possible because the error comes from 
> > sqliteErrorFromPosixError(). In that case, how can I fix the issue?
> >
>
> Try adding the -vfs unix-none command-line option:
>
>  sqlite3 -vfs unix-none ex1
>
> That will disable file locking.  Be warned, however, that with file 
> locking disabled, two processes trying to access the database at the 
> same time can lead to problems.-- D. Richard Hipp 
> drh@sqlite.org___
> 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
>



--
D. Richard Hipp
d...@sqlite.org
___
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

Re: [sqlite] database is locked with create

[Richard Hipp]

On Tue, Feb 19, 2013 at 6:37 PM, Choi, David <david.c...@micrel.com> wrote:

> Hi Richard,
>
> It seems that my version is not accept the command. By the way, what is
> the version that you are referring to?
>

Get the latest: 3.7.15.1.  There is no reason not to.


>
> Here is my operation:
> /home # sqlite3 --help
> Usage: sqlite3 [OPTIONS] FILENAME [SQL]
> FILENAME is the name of an SQLite database. A new database is created
> if the file does not previously exist.
> OPTIONS include:
>-helpshow this message
>-init filename   read/process named file
>-echoprint commands before execution
>-[no]header  turn headers on or off
>-bailstop after hitting an error
>-interactive force interactive I/O
>-batch   force batch I/O
>-column  set output mode to 'column'
>-csv set output mode to 'csv'
>-htmlset output mode to HTML
>-lineset output mode to 'line'
>-listset output mode to 'list'
>-separator 'x'   set output field separator (|)
>-nullvalue 'text'set text string for NULL values
>-version show SQLite version
> /home # sqlite3 -version
> 3.6.23.1
> /home # sqlite3 -vfs unix-none ex1
> sqlite3: Error: unknown option: -vfs
> Use -help for a list of options.
>
>
> Regards,
> David J. Choi
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
> Sent: Tuesday, February 19, 2013 1:44 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] database is locked with create
>
> On Tue, Feb 19, 2013 at 4:39 PM, Choi, David <david.c...@micrel.com>
> wrote:
>
> > Hi Richard,
> >
> > I think your guess is highly possible because the error comes from
> > sqliteErrorFromPosixError(). In that case, how can I fix the issue?
> >
>
> Try adding the -vfs unix-none command-line option:
>
>  sqlite3 -vfs unix-none ex1
>
> That will disable file locking.  Be warned, however, that with file
> locking disabled, two processes trying to access the database at the same
> time can lead to problems.-- D. Richard Hipp 
> drh@sqlite.org___
> 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
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database is locked with create

[Choi, David]

Hi Richard,

It seems that my version is not accept the command. By the way, what is the 
version that you are referring to?

Here is my operation:
/home # sqlite3 --help
Usage: sqlite3 [OPTIONS] FILENAME [SQL]
FILENAME is the name of an SQLite database. A new database is created
if the file does not previously exist.
OPTIONS include:
   -helpshow this message
   -init filename   read/process named file
   -echoprint commands before execution
   -[no]header  turn headers on or off
   -bailstop after hitting an error
   -interactive force interactive I/O
   -batch   force batch I/O
   -column  set output mode to 'column'
   -csv set output mode to 'csv'
   -htmlset output mode to HTML
   -lineset output mode to 'line'
   -listset output mode to 'list'
   -separator 'x'   set output field separator (|)
   -nullvalue 'text'set text string for NULL values
   -version show SQLite version
/home # sqlite3 -version
3.6.23.1
/home # sqlite3 -vfs unix-none ex1
sqlite3: Error: unknown option: -vfs
Use -help for a list of options.


Regards,
David J. Choi


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Tuesday, February 19, 2013 1:44 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] database is locked with create

On Tue, Feb 19, 2013 at 4:39 PM, Choi, David <david.c...@micrel.com> wrote:

> Hi Richard,
>
> I think your guess is highly possible because the error comes from 
> sqliteErrorFromPosixError(). In that case, how can I fix the issue?
>

Try adding the -vfs unix-none command-line option:

 sqlite3 -vfs unix-none ex1

That will disable file locking.  Be warned, however, that with file locking 
disabled, two processes trying to access the database at the same time can lead 
to problems.-- D. Richard Hipp d...@sqlite.org 
___
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

Re: [sqlite] database is locked with create

[Richard Hipp]

On Tue, Feb 19, 2013 at 4:39 PM, Choi, David  wrote:

> Hi Richard,
>
> I think your guess is highly possible because the error comes from
> sqliteErrorFromPosixError(). In that case, how can I fix the issue?
>

Try adding the -vfs unix-none command-line option:

 sqlite3 -vfs unix-none ex1

That will disable file locking.  Be warned, however, that with file locking
disabled, two processes trying to access the database at the same time can
lead to problems.--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database is locked with create

[Choi, David]

Hi Richard,

I think your guess is highly possible because the error comes from 
sqliteErrorFromPosixError(). In that case, how can I fix the issue?

Regards,
David J. Choi


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Tuesday, February 19, 2013 9:44 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] database is locked with create

On Tue, Feb 19, 2013 at 12:01 PM, Choi, David <david.c...@micrel.com> wrote:

> Hi all,
>
> I am new to sqlite. I build sqlite with buildroot without any issue. 
> After loading sqlite on my embedded board, I always get one error 
> message:"database is locked".
>
> Here is my operation on  my board:
>
> #sqlite3 ex1
> SQLite version 3.6.23.1
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table tbl1(one varchar(10), two smallint);
> Error: database is locked
> sqlite >
>
> Any suggestion will be highly appreciated.
>

I'm guessing that whatever embedded operating system you are using does not
(correctly) support posix advisory locks.   But that's just a guess.

--
D. Richard Hipp
d...@sqlite.org
___
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

Re: [sqlite] database is locked with create

[Choi, David]

Hi Simon,

Thank you for your clear instruction. But still I have the same issue.


Here is my test procedures:

#cd /home
#sqlite3 ex1
SQLite version 3.6.23.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>create table tbl1(one varchar(10),two smallint);
sqlite3_exec: PH3: rc=101sqlite3_exec: PH7:rc=101sqlite3_exec: 
PH8:rc=0sqlite3_exec: PH9sqlite3_exec: PH10:rc=0sqliteErrorFromPosixError: 
SQLITE_BUSY: sqliteIOErr=3850
Error: database is locked
sqlite> .exit
/home # ls -l
total 4
drwxr-xr-x2 default  default  0 Feb 19 11:08 default
-rw-r--r--1 root root 0 Feb 19 13:26 ex1
/home # rm ex1
/home # sqlite3 ex1
SQLite version 3.6.23.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table tbl1(one varchar(10), two smallint);
sqlite3_exec: PH3: rc=101sqlite3_exec: PH7:rc=101sqlite3_exec: 
PH8:rc=0sqlite3_exec: PH9sqlite3_exec: PH10:rc=0sqliteErrorFromPosixError: 
SQLITE_BUSY: sqliteIOErr=3850
Error: database is locked
sqlite> .exit
/home # ls
default  ex1
/home # ls -l ex1
-rw-r--r--1 root root 0 Feb 19 13:27 ex1
/home # rm ex1 
/home # touch aaa
/home # ls
aaa  default  ex1
/home # echo "this is test file" > aaa
/home # cat aaa
this is test file

Regards,
David J. Choi


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: Tuesday, February 19, 2013 9:14 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] database is locked with create


On 19 Feb 2013, at 5:01pm, "Choi, David" <david.c...@micrel.com> wrote:

> I am new to sqlite. I build sqlite with buildroot without any issue. After 
> loading sqlite on my embedded board, I always get one error message:"database 
> is locked".
> 
> Here is my operation on  my board:
> 
> #sqlite3 ex1
> SQLite version 3.6.23.1
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table tbl1(one varchar(10), two smallint);
> Error: database is locked
> sqlite >
> 
> Any suggestion will be highly appreciated.

Quit the shell program.
Locate the database file called 'ex1' and make sure it is in the folder you 
expect it to be in.
If the database file still exists, delete it.
Try again, letting the shell program create a new database.

If this also doesn't work,

Check the privileges in the folder where the database file is created.
Do you have write privileges inside that folder ?
Try using copy command or a simple text editor to make a file in that folder.
Does it work or do you get a similar error to 'file is locked' ?

Simon.
___
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

Re: [sqlite] database is locked with create

[Richard Hipp]

On Tue, Feb 19, 2013 at 12:01 PM, Choi, David  wrote:

> Hi all,
>
> I am new to sqlite. I build sqlite with buildroot without any issue. After
> loading sqlite on my embedded board, I always get one error
> message:"database is locked".
>
> Here is my operation on  my board:
>
> #sqlite3 ex1
> SQLite version 3.6.23.1
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table tbl1(one varchar(10), two smallint);
> Error: database is locked
> sqlite >
>
> Any suggestion will be highly appreciated.
>

I'm guessing that whatever embedded operating system you are using does not
(correctly) support posix advisory locks.   But that's just a guess.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database is locked with create

[Simon Slavin]

On 19 Feb 2013, at 5:01pm, "Choi, David"  wrote:

> I am new to sqlite. I build sqlite with buildroot without any issue. After 
> loading sqlite on my embedded board, I always get one error message:"database 
> is locked".
> 
> Here is my operation on  my board:
> 
> #sqlite3 ex1
> SQLite version 3.6.23.1
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table tbl1(one varchar(10), two smallint);
> Error: database is locked
> sqlite >
> 
> Any suggestion will be highly appreciated.

Quit the shell program.
Locate the database file called 'ex1' and make sure it is in the folder you 
expect it to be in.
If the database file still exists, delete it.
Try again, letting the shell program create a new database.

If this also doesn't work,

Check the privileges in the folder where the database file is created.
Do you have write privileges inside that folder ?
Try using copy command or a simple text editor to make a file in that folder.
Does it work or do you get a similar error to 'file is locked' ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is Locked

[Kees Nuyt]

On Sun, 29 Jan 2012 17:11:35 +0200, Shahar Weinstein
 wrote:

>hi,
>
>thanks for your intention. but it's a shared hosting. I do not have a
>remote desktop access that allows me
>to run any application I desire. and besides that there is no need to
>monitor processes.

Many of the sysinternal tools can sense events on a remote server
without having remote desktop access. A windows account/password
suffices, and of course the management interface (whatever that is) has
to be enabled.

>the situation is quite simple, the sqlite database I'm using is failing to
>serve one update from one process.
>and this problem comes and goes, and when it comes, it stays for some time.

Every sqlite3_*() API call returns an error code when it fails, and
extended error diags can be retrieved after an error. Doesn't that
render something useful?

As Simon already suggested "I bet that the process which has the file
open is not your database program, it's a backup app, or a
virus-checker, or something like that.". 

Some of those blocking issues are more likely if certain file extensions
are used. '.db' is famous for unwanted attention of the MS Windows OS,
but it's not the only one.
Try changing the extension to something innocent, like .sqlite or
.sqlite3 or something similar.

If the cause is a virusscanner, you'll have to contact the hoster to add
scan exclusions.

>there is no connection string settings that I'm familiar with so I see this
>problem with no solution.
>
>Shahar.
>
>2012/1/29 Black, Michael (IS) 
>
>> Try this then:
>>
>> http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx
>>
>>
>> Michael D. Black

-- 
Regards,

Kees Nuyt

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is Locked

[Simon Slavin]

On 29 Jan 2012, at 4:14pm, Shahar Weinstein wrote:

> no, its an ordinary website.
> the situation is simple. I'm trying to save into the database some data
> taken from a web form with one simple,
> update query. the database is being opened and then immidietly being closed.

So you do execute sqlite3_close() on it, and you do check the value being 
returned to make sure it doesn't indicate an error ?

> as I mentioned before. the problem appears. and than stays for
> seconds,minutes or an hour, and than disappears again.

On the computer that the database file is stored on, run an application which 
shows you what processes have which files open.  On a Mac it's 'lsof'.  On 
Windows 'openfiles' will show you only files opened by network users.  You need 
something which shows files opened by local processes too.  Maybe this will do 
it:

http://www.nirsoft.net/utils/process_activity_view.html

> is there a way to influence the sqlite engine to produce a log file in
> order to understand the situation better?

I bet that the process which has the file open is not your database program, 
it's a backup app, or a virus-checker, or something like that.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is Locked

[Shahar Weinstein]

no, its an ordinary website.
the situation is simple. I'm trying to save into the database some data
taken from a web form with one simple,
update query. the database is being opened and then immidietly being closed.

as I mentioned before. the problem appears. and than stays for
seconds,minutes or an hour, and than disappears again.

is there a way to influence the sqlite engine to produce a log file in
order to understand the situation better?

Shahar.



2012/1/29 Simon Slavin 

>
> On 29 Jan 2012, at 3:11pm, Shahar Weinstein wrote:
>
> > the sqlite database I'm using is failing to
> > serve one update from one process.
> > and this problem comes and goes, and when it comes, it stays for some
> time.
>
> Is this for a web-facing service ?  In other words, someone looks up a
> page on a web site and the web server needs access to the database to
> decide what to put on the page ?
>
> If so, you need to be aware that the web server can create multiple
> processes to do its job.  In other words, you may have written only one
> application, but the web server may make one process to answer each web
> request.  So you can still end up with multiple processes even if you
> didn't write more than one app.
>
> Simon.
> ___
> 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

Re: [sqlite] Database is Locked

[Simon Slavin]

On 29 Jan 2012, at 3:11pm, Shahar Weinstein wrote:

> the sqlite database I'm using is failing to
> serve one update from one process.
> and this problem comes and goes, and when it comes, it stays for some time.

Is this for a web-facing service ?  In other words, someone looks up a page on 
a web site and the web server needs access to the database to decide what to 
put on the page ?

If so, you need to be aware that the web server can create multiple processes 
to do its job.  In other words, you may have written only one application, but 
the web server may make one process to answer each web request.  So you can 
still end up with multiple processes even if you didn't write more than one app.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is Locked

[Shahar Weinstein]

hi,

thanks for your intention. but it's a shared hosting. I do not have a
remote desktop access that allows me
to run any application I desire. and besides that there is no need to
monitor processes.
the situation is quite simple, the sqlite database I'm using is failing to
serve one update from one process.
and this problem comes and goes, and when it comes, it stays for some time.

there is no connection string settings that I'm familiar with so I see this
problem with no solution.

Shahar.

2012/1/29 Black, Michael (IS) <michael.bla...@ngc.com>

> Try this then:
>
> http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx
>
>
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Shahar Weinstein [shahar...@gmail.com]
> Sent: Sunday, January 29, 2012 9:03 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] Database is Locked
>
> thanks for the reply.
>
> but, I'm using a shared hosting running Windows. so, no shell no fuser.
>
> Shahar.
>
>
> 2012/1/29 Black, Michael (IS) <michael.bla...@ngc.com>
>
> > Do you have shell access I hope?
> >
> >
> >
> > Check out "fuser" and see if you can find a process attached to it.
> >
> >
> >
> > Michael D. Black
> >
> > Senior Scientist
> >
> > Advanced Analytics Directorate
> >
> > Advanced GEOINT Solutions Operating Unit
> >
> > Northrop Grumman Information Systems
> >
> > 
> > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> > on behalf of Shahar Weinstein [shahar...@gmail.com]
> > Sent: Sunday, January 29, 2012 8:40 AM
> > To: General Discussion of SQLite Database
> > Subject: EXT :Re: [sqlite] Database is Locked
> >
> > thanks for the reply but its not a permission issue, since after few
> hours
> > the problem dissapears
> > and there is no problem to save data into the database.
> > the problem comes, stays for few hours and than disappears.
> > it's very strange since on my development machine it does not happen.
> >
> > Shahar.
> >
> >
> > ___
> > 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
> ___
> 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

Re: [sqlite] Database is Locked

[Black, Michael (IS)]

Try this then:

http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Shahar Weinstein [shahar...@gmail.com]
Sent: Sunday, January 29, 2012 9:03 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Database is Locked

thanks for the reply.

but, I'm using a shared hosting running Windows. so, no shell no fuser.

Shahar.


2012/1/29 Black, Michael (IS) <michael.bla...@ngc.com>

> Do you have shell access I hope?
>
>
>
> Check out "fuser" and see if you can find a process attached to it.
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Shahar Weinstein [shahar...@gmail.com]
> Sent: Sunday, January 29, 2012 8:40 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] Database is Locked
>
> thanks for the reply but its not a permission issue, since after few hours
> the problem dissapears
> and there is no problem to save data into the database.
> the problem comes, stays for few hours and than disappears.
> it's very strange since on my development machine it does not happen.
>
> Shahar.
>
>
> ___
> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is Locked

[Shahar Weinstein]

thanks for the reply.

but, I'm using a shared hosting running Windows. so, no shell no fuser.

Shahar.


2012/1/29 Black, Michael (IS) <michael.bla...@ngc.com>

> Do you have shell access I hope?
>
>
>
> Check out "fuser" and see if you can find a process attached to it.
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Shahar Weinstein [shahar...@gmail.com]
> Sent: Sunday, January 29, 2012 8:40 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] Database is Locked
>
> thanks for the reply but its not a permission issue, since after few hours
> the problem dissapears
> and there is no problem to save data into the database.
> the problem comes, stays for few hours and than disappears.
> it's very strange since on my development machine it does not happen.
>
> Shahar.
>
>
> ___
> 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

Re: [sqlite] Database is Locked

[Black, Michael (IS)]

Do you have shell access I hope?



Check out "fuser" and see if you can find a process attached to it.



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Shahar Weinstein [shahar...@gmail.com]
Sent: Sunday, January 29, 2012 8:40 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Database is Locked

thanks for the reply but its not a permission issue, since after few hours
the problem dissapears
and there is no problem to save data into the database.
the problem comes, stays for few hours and than disappears.
it's very strange since on my development machine it does not happen.

Shahar.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is Locked

[Shahar Weinstein]

thanks for the reply but its not a permission issue, since after few hours
the problem dissapears
and there is no problem to save data into the database.
the problem comes, stays for few hours and than disappears.
it's very strange since on my development machine it does not happen.

Shahar.




2012/1/29 Stephen C 

> Is the hosting company running linux or windows?  You say .net so that
> indicates to me windows, but I've been wrong before. (Wife lets me know
> frequently. {smirk})
>
> This still "smells" like a permission issue to me.  I acknowledge that
> you're able to read/write/modify the web files then upload them, but the
> user who is serving the pages to the web clients may not have WRITE
> permissions to the directory where the web pages are hosted.  I don't know
> exactly how you upload your pages to your site, but via FTP, or Samba, a
> specialized web interface, or whatever, you're typically provided with a
> username and password to log in.  However, the "user" that actually reads
> and writes to that directory that hosts the data to the web CLIENTS is
> typically the web service.  Can't think of the user for windows (iisadmin?
>  iisuser? Something like that anyways), but for linux it is usually either
> the user "httpd" or "apache2".  If the users "httpd" or "apache2" don't
> have WRITE access to that directory, you could have problems.  The SQLite
> engine may need to create a temporary file.  If the temp file can't be
> written, it may throw an exception which is why you're getting the error.
>
> Depending on your site provider, you may have a "public" directory where
> all your web pages live, then a "private" directory where other stuff sits,
> like databases.  The PUBLIC directory may be locked down by default so that
> nothing via the web interface can modify files, including writing to your
> SQLite database, and a private place that anyone can write to.  Try moving
> your database into a private directory, set your application to open the
> directory from there, and see what happens.
>
> SQLite doesn't have any problem hosting a database for web applications
> for light to medium use, according to documentation.  Which means you're
> not going to run a Google-ish size of site, nor even EBay, but, it should
> be able to handle something like forums, wikis, and so on.  If you're going
> to start using MS-Access for your database back end, I *KNOW* you're not
> going to towards a Google-ish size of site.
>
> On 01/29/2012 06:54 AM, Shahar Weinstein wrote:
>
>> thanks for your reply.
>> since my situation IS one process that tries to write to the database, I'm
>> trying to use the forums help.
>> if the situation was multiple processes as you described I would move to
>> mysql or MSaccess database which is quite stable for web applications.
>> I've programmed few web projects with the sqlite database which never had
>> this problem.
>> and those projects are running well, that's why I think I've got a special
>> situation.
>>
>> it seems I will, have to move to another database.
>>
>> thanks
>> Shahar.
>>
>>
>>
>> 2012/1/29 Jan Hudec
>>
>>  On Sun, Jan 29, 2012 at 12:57:21 +0200, Shahar Weinstein wrote:
>>>
 I don't think that the TMP folder is the right direction but I'll check

>>> it
>>>
 anyway with the hosting company.

>>> No, *NOT* *HOSTING* *COMPANY*. Your code.
>>>
>>> I agree it's probably not the right direction though.
>>>
>>>  besides that, I do know that there is only one process running that
 tries
 to write to the database what makes my situation a sad joke. sqlite
 supposed to be a strong database that knows how to deal such simple
 situations.

>>> So you are saying, that there is absolutely no other client using the
>>> code
>>> at
>>> the same time or even that the same client isn't using two windows?
>>> Because
>>> otherwise any serious web server will run mutliple instances of your code
>>> in
>>> either multiple threads or multiple processes.
>>>
>>>  even of 2 processes that tries to write to the database the same time,

>>> else
>>>
 it's probably a very weak database system that doesn't have transactions
 locks mechanism inside.

>>> It DOES. But very coarse-grained and is telling you the one process has
>>> it
>>> locked, so the other process can't access it.
>>>
>>> Sqlite is designed to be lightweight. The cost for this is, that it does
>>> not
>>> have per-row or per-table locks. A write transactions always needs to
>>> lock
>>> the whole database. Additionally by default when the database is locked,
>>> the
>>> operation immediately fails with the "database is locked" error you are
>>> seeing.
>>>
>>> You need to do three things:
>>>
>>>  - Run 'PRAGMA journal=wal' on the database once (see
>>>   http://www.sqlite.org/wal.html**). This requires sqlite at least
>>> 3.7.0.
>>>  - Set the timeout, so the operations wait for the other process to
>>> finish
>>>   for some 

Re: [sqlite] Database is Locked

[Stephen C]

Is the hosting company running linux or windows?  You say .net so that 
indicates to me windows, but I've been wrong before. (Wife lets me know 
frequently. {smirk})


This still "smells" like a permission issue to me.  I acknowledge that 
you're able to read/write/modify the web files then upload them, but the 
user who is serving the pages to the web clients may not have WRITE 
permissions to the directory where the web pages are hosted.  I don't 
know exactly how you upload your pages to your site, but via FTP, or 
Samba, a specialized web interface, or whatever, you're typically 
provided with a username and password to log in.  However, the "user" 
that actually reads and writes to that directory that hosts the data to 
the web CLIENTS is typically the web service.  Can't think of the user 
for windows (iisadmin?  iisuser? Something like that anyways), but for 
linux it is usually either the user "httpd" or "apache2".  If the users 
"httpd" or "apache2" don't have WRITE access to that directory, you 
could have problems.  The SQLite engine may need to create a temporary 
file.  If the temp file can't be written, it may throw an exception 
which is why you're getting the error.


Depending on your site provider, you may have a "public" directory where 
all your web pages live, then a "private" directory where other stuff 
sits, like databases.  The PUBLIC directory may be locked down by 
default so that nothing via the web interface can modify files, 
including writing to your SQLite database, and a private place that 
anyone can write to.  Try moving your database into a private directory, 
set your application to open the directory from there, and see what happens.


SQLite doesn't have any problem hosting a database for web applications 
for light to medium use, according to documentation.  Which means you're 
not going to run a Google-ish size of site, nor even EBay, but, it 
should be able to handle something like forums, wikis, and so on.  If 
you're going to start using MS-Access for your database back end, I 
*KNOW* you're not going to towards a Google-ish size of site.


On 01/29/2012 06:54 AM, Shahar Weinstein wrote:

thanks for your reply.
since my situation IS one process that tries to write to the database, I'm
trying to use the forums help.
if the situation was multiple processes as you described I would move to
mysql or MSaccess database which is quite stable for web applications.
I've programmed few web projects with the sqlite database which never had
this problem.
and those projects are running well, that's why I think I've got a special
situation.

it seems I will, have to move to another database.

thanks
Shahar.



2012/1/29 Jan Hudec


On Sun, Jan 29, 2012 at 12:57:21 +0200, Shahar Weinstein wrote:

I don't think that the TMP folder is the right direction but I'll check

it

anyway with the hosting company.

No, *NOT* *HOSTING* *COMPANY*. Your code.

I agree it's probably not the right direction though.


besides that, I do know that there is only one process running that tries
to write to the database what makes my situation a sad joke. sqlite
supposed to be a strong database that knows how to deal such simple
situations.

So you are saying, that there is absolutely no other client using the code
at
the same time or even that the same client isn't using two windows? Because
otherwise any serious web server will run mutliple instances of your code
in
either multiple threads or multiple processes.


even of 2 processes that tries to write to the database the same time,

else

it's probably a very weak database system that doesn't have transactions
locks mechanism inside.

It DOES. But very coarse-grained and is telling you the one process has it
locked, so the other process can't access it.

Sqlite is designed to be lightweight. The cost for this is, that it does
not
have per-row or per-table locks. A write transactions always needs to lock
the whole database. Additionally by default when the database is locked,
the
operation immediately fails with the "database is locked" error you are
seeing.

You need to do three things:

  - Run 'PRAGMA journal=wal' on the database once (see
   http://www.sqlite.org/wal.html). This requires sqlite at least 3.7.0.
  - Set the timeout, so the operations wait for the other process to finish
   for some time instead of failing. The C-level API is sqlite3_busy_timeout
   (http://www.sqlite.org/c3ref/busy_timeout.html). The .NET api will be
   called similar.
  - Plan eventual migration to SQL Server or MySQL or something, because
   sqlite will not scale. It's designed to be fast for data processing and
   small so it can be embedded in desktop or even mobile applications, but
   that means it does not support multiple servers and parallel
transactions.
   It can work fine in web app that gets at most few hits a minute, but for
   higher load you will definitiely need a database server.

--
  

Re: [sqlite] Database is Locked

[Shahar Weinstein]

thanks for your reply.
since my situation IS one process that tries to write to the database, I'm
trying to use the forums help.
if the situation was multiple processes as you described I would move to
mysql or MSaccess database which is quite stable for web applications.
I've programmed few web projects with the sqlite database which never had
this problem.
and those projects are running well, that's why I think I've got a special
situation.

it seems I will, have to move to another database.

thanks
Shahar.



2012/1/29 Jan Hudec 

> On Sun, Jan 29, 2012 at 12:57:21 +0200, Shahar Weinstein wrote:
> > I don't think that the TMP folder is the right direction but I'll check
> it
> > anyway with the hosting company.
>
> No, *NOT* *HOSTING* *COMPANY*. Your code.
>
> I agree it's probably not the right direction though.
>
> > besides that, I do know that there is only one process running that tries
> > to write to the database what makes my situation a sad joke. sqlite
> > supposed to be a strong database that knows how to deal such simple
> > situations.
>
> So you are saying, that there is absolutely no other client using the code
> at
> the same time or even that the same client isn't using two windows? Because
> otherwise any serious web server will run mutliple instances of your code
> in
> either multiple threads or multiple processes.
>
> > even of 2 processes that tries to write to the database the same time,
> else
> > it's probably a very weak database system that doesn't have transactions
> > locks mechanism inside.
>
> It DOES. But very coarse-grained and is telling you the one process has it
> locked, so the other process can't access it.
>
> Sqlite is designed to be lightweight. The cost for this is, that it does
> not
> have per-row or per-table locks. A write transactions always needs to lock
> the whole database. Additionally by default when the database is locked,
> the
> operation immediately fails with the "database is locked" error you are
> seeing.
>
> You need to do three things:
>
>  - Run 'PRAGMA journal=wal' on the database once (see
>   http://www.sqlite.org/wal.html). This requires sqlite at least 3.7.0.
>  - Set the timeout, so the operations wait for the other process to finish
>   for some time instead of failing. The C-level API is sqlite3_busy_timeout
>   (http://www.sqlite.org/c3ref/busy_timeout.html). The .NET api will be
>   called similar.
>  - Plan eventual migration to SQL Server or MySQL or something, because
>   sqlite will not scale. It's designed to be fast for data processing and
>   small so it can be embedded in desktop or even mobile applications, but
>   that means it does not support multiple servers and parallel
> transactions.
>   It can work fine in web app that gets at most few hits a minute, but for
>   higher load you will definitiely need a database server.
>
> --
> Jan 'Bulb' Hudec <
> b...@ucw.cz>
> ___
> 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

Re: [sqlite] Database is Locked

[Jan Hudec]

On Sun, Jan 29, 2012 at 12:57:21 +0200, Shahar Weinstein wrote:
> I don't think that the TMP folder is the right direction but I'll check it
> anyway with the hosting company.

No, *NOT* *HOSTING* *COMPANY*. Your code.

I agree it's probably not the right direction though.

> besides that, I do know that there is only one process running that tries
> to write to the database what makes my situation a sad joke. sqlite
> supposed to be a strong database that knows how to deal such simple
> situations.

So you are saying, that there is absolutely no other client using the code at
the same time or even that the same client isn't using two windows? Because
otherwise any serious web server will run mutliple instances of your code in
either multiple threads or multiple processes.

> even of 2 processes that tries to write to the database the same time, else
> it's probably a very weak database system that doesn't have transactions
> locks mechanism inside.

It DOES. But very coarse-grained and is telling you the one process has it
locked, so the other process can't access it.

Sqlite is designed to be lightweight. The cost for this is, that it does not
have per-row or per-table locks. A write transactions always needs to lock
the whole database. Additionally by default when the database is locked, the
operation immediately fails with the "database is locked" error you are
seeing.

You need to do three things:

 - Run 'PRAGMA journal=wal' on the database once (see
   http://www.sqlite.org/wal.html). This requires sqlite at least 3.7.0.
 - Set the timeout, so the operations wait for the other process to finish
   for some time instead of failing. The C-level API is sqlite3_busy_timeout
   (http://www.sqlite.org/c3ref/busy_timeout.html). The .NET api will be
   called similar.
 - Plan eventual migration to SQL Server or MySQL or something, because
   sqlite will not scale. It's designed to be fast for data processing and
   small so it can be embedded in desktop or even mobile applications, but
   that means it does not support multiple servers and parallel transactions.
   It can work fine in web app that gets at most few hits a minute, but for
   higher load you will definitiely need a database server.

-- 
 Jan 'Bulb' Hudec 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is Locked

[Shahar Weinstein]

I don't think that the TMP folder is the right direction but I'll check it
anyway with the hosting company.
besides that, I do know that there is only one process running that tries
to write to the database what makes my situation a sad joke. sqlite
supposed to be a strong database that knows how to deal such simple
situations.
even of 2 processes that tries to write to the database the same time, else
it's probably a very weak database system that doesn't have transactions
locks mechanism inside.

does anyone knows if sqlite got any connection string definitions that
deals with locks? or any other setting that influence the enviroment?


Shahar.


2012/1/29 Simon Slavin 

>
> On 28 Jan 2012, at 11:47pm, Jan Hudec wrote:
>
> > On Sun, Jan 29, 2012 at 00:43:07 +0200, Shahar Weinstein wrote:
> >> I know there is no permissions problems since after some time when I try
> >> the same action, it succeeds. in the website, I manage to
> read/write/delete
> >> in different locations of the code.
> >
> > Can it be that the database is really locked by another request running
> in
> > parallel? Sqlite only allows one writer to the whole file and unless you
> set
> > WAL mode, no readers while writing.
>
> If I get the setup correctly, this is a web-facing app.  Some web servers
> start a process for each HTTP session, or for each item they serve.  That
> means that even though he has only one application that accesses the
> database (an active web page) he might have two concurrent processes using
> the SQLite API to access the database.
>
> Simon.
> ___
> 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

Re: [sqlite] Database is Locked

[Simon Slavin]

On 28 Jan 2012, at 11:47pm, Jan Hudec wrote:

> On Sun, Jan 29, 2012 at 00:43:07 +0200, Shahar Weinstein wrote:
>> I know there is no permissions problems since after some time when I try
>> the same action, it succeeds. in the website, I manage to read/write/delete
>> in different locations of the code.
> 
> Can it be that the database is really locked by another request running in
> parallel? Sqlite only allows one writer to the whole file and unless you set
> WAL mode, no readers while writing.

If I get the setup correctly, this is a web-facing app.  Some web servers start 
a process for each HTTP session, or for each item they serve.  That means that 
even though he has only one application that accesses the database (an active 
web page) he might have two concurrent processes using the SQLite API to access 
the database.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is Locked

[Jan Hudec]

On Sun, Jan 29, 2012 at 00:43:07 +0200, Shahar Weinstein wrote:
> I know there is no permissions problems since after some time when I try
> the same action, it succeeds. in the website, I manage to read/write/delete
> in different locations of the code.

Can it be that the database is really locked by another request running in
parallel? Sqlite only allows one writer to the whole file and unless you set
WAL mode, no readers while writing.

> and since the website is on a shared hosting I cannot modify the TMP

Yes, you can. You should be setting them *inside* the application. At C level
it's setenv() call, but I don't remember the exact .NET equivalent.

> definitions, which I assume they exist.

They are almost certainly wrong on Windows, which it probably is.

-- 
 Jan 'Bulb' Hudec 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is Locked

[Shahar Weinstein]

hi,

thanks for your reply.
I know there is no permissions problems since after some time when I try
the same action,
it succeeds. in the website, I manage to read/write/delete in different
locations of the code.
and since the website is on a shared hosting I cannot modify the TMP
definitions, which I assume they exist.
the fact that it does work for time to time , makes it difficult to debug.


Shahar.


2012/1/29 Jan Hudec 

> On Sat, Jan 28, 2012 at 23:36:36 +0200, Shahar Weinstein wrote:
> > I'm using System.Data.Sqlite the latest version that supports .NET 4
> > in an ordinary .NET website.
> > when trying to issue a single update to the database I'm receiving the
> > error message saying that the database file is locked.
> > there is only one connection open. In the connection string connection
> > pooling has been set to off
> > the website is being hosted on a shared hosting server.
>
> Sqlite needs wrtie access to the database file itself, ability to create
> and
> write to a file in the same directory as the database file and ability to
> create and write to a file in temporary directory.
>
> So first check permissions whether it can do all those operations. To
> select
> temporary directory, set TMP variable in environment before opening the
> database. And make sure you set the same temporary directory in all
> processes
> that will access the same database.
>
> --
> Jan 'Bulb' Hudec <
> b...@ucw.cz>
> ___
> 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

Re: [sqlite] Database is Locked

[Jan Hudec]

On Sat, Jan 28, 2012 at 23:36:36 +0200, Shahar Weinstein wrote:
> I'm using System.Data.Sqlite the latest version that supports .NET 4
> in an ordinary .NET website.
> when trying to issue a single update to the database I'm receiving the
> error message saying that the database file is locked.
> there is only one connection open. In the connection string connection
> pooling has been set to off
> the website is being hosted on a shared hosting server.

Sqlite needs wrtie access to the database file itself, ability to create and
write to a file in the same directory as the database file and ability to
create and write to a file in temporary directory.

So first check permissions whether it can do all those operations. To select
temporary directory, set TMP variable in environment before opening the
database. And make sure you set the same temporary directory in all processes
that will access the same database.

-- 
 Jan 'Bulb' Hudec 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database is locked" on WAL with large number of reader connections

[Dan Kennedy]

On 01/26/2012 09:05 AM, Erik Fears wrote:

I have a writer, but the thread is not writing during this test.

This is built by me.

This is single process, one connection per thread, 100 threads.

The threads are being created/destroyed often, though, and each time one is
created it creates a new DB connection.

I understand this isn't ideal, and plan to move to having less connections
or using a pool, but I still don't understand why this would be occurring
with WAL.


When a new connection is made to a WAL database, SQLite checks
to see if there already exist any other connections. If there
do not, then it needs to initialize the shared-memory region
based on the current contents of the *-wal file. This is referred
to as "recovering the wal file" in some places.

While recovering the WAL file, SQLite takes an exclusive lock
on the database file. So if two processes try to connect
simultaneously, there is a race to recover the wal file. One
process does the work of reading the wal file from disk and
initializing shared-memory, and the other will get the
SQLITE_BUSY error.

If the wal file does not exist or is zero bytes in size, SQLite
still takes this lock and runs the recovery code to initialize
the shared-memory. Of course, it is very fast in this case. And
since, assuming there are no IO errors and all processes call
sqlite3_close() to close connections before exiting, SQLite
always deletes the WAL file when the last connection disconnects,
this is the usual case.

Anyhow, that could be what is happening here. Two processes
trying to run recovery simultaneously.

To test, try opening the WAL database with the command line tool.
Read some data (e.g. by querying the sqlite_master table) to
make sure the db shared-memory region is initialized. Then run
your experiment (leaving the command line client connected to
the db). Since none of your threads should ever need to run recovery,
the SQLITE_BUSY errors might disappear.

Dan.







--erik

On Wed, Jan 25, 2012 at 5:39 PM, Richard Hipp  wrote:


On Wed, Jan 25, 2012 at 8:01 PM, Erik Fears  wrote:


I've got a WAL database with only readers right now. I'm opening and

close

the database
at pretty rapid rate, and usually have about 100 connections open at a
time. I'm
using prepared statements for SELECTS and I'm pretty sure I'm finalizing
the statements.

After a while I start getting back "database is locked".

How can this happen with a WAL database? This is OS X.



I didn't think it could, under the circumstances you describe.  Are you
sure your description of the scenerio is correct?  Are you building SQLite
yourself, our using the one that comes built-in to OSX?

Is this all happening in a single process?  Or lots of separately
processes?

If you have only readers, why both with WAL mode, single DELETE mode works
just as well in that case?





--erik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users





--
D. Richard Hipp
d...@sqlite.org
___
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



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database is locked" on WAL with large number of reader connections

[Erik Fears]

I have a writer, but the thread is not writing during this test.

This is built by me.

This is single process, one connection per thread, 100 threads.

The threads are being created/destroyed often, though, and each time one is
created it creates a new DB connection.

I understand this isn't ideal, and plan to move to having less connections
or using a pool, but I still don't understand why this would be occurring
with WAL.

--erik

On Wed, Jan 25, 2012 at 5:39 PM, Richard Hipp  wrote:

> On Wed, Jan 25, 2012 at 8:01 PM, Erik Fears  wrote:
>
> > I've got a WAL database with only readers right now. I'm opening and
> close
> > the database
> > at pretty rapid rate, and usually have about 100 connections open at a
> > time. I'm
> > using prepared statements for SELECTS and I'm pretty sure I'm finalizing
> > the statements.
> >
> > After a while I start getting back "database is locked".
> >
> > How can this happen with a WAL database? This is OS X.
> >
>
> I didn't think it could, under the circumstances you describe.  Are you
> sure your description of the scenerio is correct?  Are you building SQLite
> yourself, our using the one that comes built-in to OSX?
>
> Is this all happening in a single process?  Or lots of separately
> processes?
>
> If you have only readers, why both with WAL mode, single DELETE mode works
> just as well in that case?
>
>
>
> >
> > --erik
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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

Re: [sqlite] "database is locked" on WAL with large number of reader connections

[Richard Hipp]

On Wed, Jan 25, 2012 at 8:01 PM, Erik Fears  wrote:

> I've got a WAL database with only readers right now. I'm opening and close
> the database
> at pretty rapid rate, and usually have about 100 connections open at a
> time. I'm
> using prepared statements for SELECTS and I'm pretty sure I'm finalizing
> the statements.
>
> After a while I start getting back "database is locked".
>
> How can this happen with a WAL database? This is OS X.
>

I didn't think it could, under the circumstances you describe.  Are you
sure your description of the scenerio is correct?  Are you building SQLite
yourself, our using the one that comes built-in to OSX?

Is this all happening in a single process?  Or lots of separately processes?

If you have only readers, why both with WAL mode, single DELETE mode works
just as well in that case?



>
> --erik
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database is locked(5) too often - what am i doing wrong?

[Simon Slavin]

On 12 Jul 2010, at 5:37am, raf wrote:

> Each page hit results in several database writes each of which is currently
> in a separate transaction. Is combining these into a single transaction likely
> to help or make matters worse? Or is this just an inappropriate use of sqlite?

It will probably make it better.  A transaction is an expensive time-consuming 
thing.  Adding a single UPDATE or INSERT to a transaction you have to do anyway 
is cheaper.

There are some other things you might want to do to improve speed.  For 
instance, if you do lots of writes but few reads, it's best to reduce the 
number of indices you have to cover just the SELECTs you do frequently.  On the 
other hand, if you do many SELECTs but few writes, it's worth checking to see 
that you understand how to make an INDEX ideal for each of your SELECT commands.

You might also take a look at your schema, and try to work out ways of 
minimising the number of commands you have to execute.  For instance, you may 
have toed the party line and normalised all your TABLEs so that data is never 
duplicated.  But if you can save an entire INSERT command it might be worth 
duplicating a little information.

Lastly, look at your functions.  If it's easy, update to the most recent 
version of SQLite to fix bugs, which may well including bugs which slowed down 
locking.  If you're using a library or framework instead of calling SQLite 
directly, consider swapping to another one, e.g. if you're writing in PHP and 
using the PDO module, consider switching to the SQLITE3 module.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked error

[Pavel Ivanov]

> Does that seem correct?

Yes.

> If so, my options are:
>
> 1)  rollback/commit one of the transactions
>
> 2)  use begin exclusive

That's correct, but it's better to be 'begin immediate' than 'exclusive'.

> I don't think the second one will work, since I need nested transactions
> and the savepoint syntax doesn't seem to support the exclusive option.

You can easily check if transaction is already started (see
http://www.sqlite.org/c3ref/get_autocommit.html). If it is then you'll
use savepoint syntax otherwise you'll use 'begin immediate' syntax.
When you need to commit (nested) transaction you'll need to use
'release savepoint' syntax if you used savepoint at the beginning and
'commit' if you used 'begin'.


Pavel

On Mon, Feb 22, 2010 at 4:59 PM, Trainor, Chris
 wrote:
> Thanks for the response Pavel.  The order that the events were getting
> logged wasn't accurate enough so I increased the timing precision for my
> logging.  I didn't leave anything out, but some commands were logged
> slightly out of order.
>
>
>
> Thread1 is doing reads and writes for a while, with Thread2 attempting
> to insert.  Thread2 ends up in the BusyHandler for a while and then the
> following occurs in this order:
>
>
>
> Thread      Command                       Lock        Step
>
> 1                  RELEASE SAVEPOINT Thread1        None            1
>
> 2                              INSERT INTO TableA
> Exclusive       2
>
> 2                  RELEASE SAVEPOINT Thread2        None            3
>
> 2                  SAVEPOINT Thread2                      None
> 4
>
> 2                              INSERT INTO TableA
> Exclusive       5
>
> 1                  SAVEPOINT Thread1                     None
> 6
>
> 2                  RELEASE SAVEPOINT Thread2        None            7
>
> 1                              SELECT FROM TableA
> Shared          8
>
> 2                  SAVEPOINT Thread2                      None
> 9
>
> 1                              SELECT FROM TableB
> Shared          10
>
> 2                              INSERT INTO TableA
> Reserved?     11
>
> 1                              INSERT INTO TableB
> *           12
>
>
>
>
>
> Step 1 - The transaction is closed on thread1, so it no longer has a
> lock.
>
> Step 2 - This is the insert that was failing, with Thread2 ending up in
> the busyhandler.  When thread2 first tried to insert, it obtained a
> reserved lock.  Now that thread1 released its lock, thread2 gets an
> exclusive lock and the insert finally succeeds at this point.
>
>
>
> I think what is going wrong is this:
>
> Step 11 - Thread2 tries to do an insert.  Since Thread1 has a shared
> lock, thread2 acquires a reserved lock but it cannot be promoted to
> Exclusive.
>
> Step 12 - BusyHandler is not called.  Database is locked error is
> returned.  Thread1's shared lock cannot be promoted to a reserved lock,
> since Thread2 already has one.
>
>
>
> Does that seem correct?
>
>
>
> If so, my options are:
>
> 1)  rollback/commit one of the transactions
>
> 2)  use begin exclusive
>
>
>
> I don't think the second one will work, since I need nested transactions
> and the savepoint syntax doesn't seem to support the exclusive option.
>
>
>
> Thanks,
>
> Chris
>
>
>
>
>
> The information contained in this email message and its attachments
> is intended
> only for the private and confidential use of the recipient(s) named
> above, unless the sender expressly agrees otherwise. Transmission
> of email over the Internet
>  is not a secure communications medium. If you are requesting or
> have requested
> the transmittal of personal data, as defined in applicable privacy
> laws by means
>  of email or in an attachment to email you must select a more
> secure alternate means of transmittal that supports your
> obligations to protect such personal data. If the reader of this
> message is not the intended recipient and/or you have received this
> email in error, you must take no action based on the information in
> this email and you are hereby notified that any dissemination,
> misuse, copying, or disclosure of this communication is strictly
> prohibited. If you have received
> this communication in error, please notify us immediately by email
> and delete the original message.
> ___
> 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

Re: [sqlite] Database is locked error

[Trainor, Chris]

Thanks for the response Pavel.  The order that the events were getting
logged wasn't accurate enough so I increased the timing precision for my
logging.  I didn't leave anything out, but some commands were logged
slightly out of order.

 

Thread1 is doing reads and writes for a while, with Thread2 attempting
to insert.  Thread2 ends up in the BusyHandler for a while and then the
following occurs in this order:

 

Thread  Command   LockStep

1  RELEASE SAVEPOINT Thread1None1

2  INSERT INTO TableA
Exclusive   2

2  RELEASE SAVEPOINT Thread2None3

2  SAVEPOINT Thread2  None
4

2  INSERT INTO TableA
Exclusive   5

1  SAVEPOINT Thread1 None
6

2  RELEASE SAVEPOINT Thread2None7

1  SELECT FROM TableA
Shared  8

2  SAVEPOINT Thread2  None
9

1  SELECT FROM TableB
Shared  10

2  INSERT INTO TableA
Reserved? 11

1  INSERT INTO TableB
*   12

  

 

Step 1 - The transaction is closed on thread1, so it no longer has a
lock.

Step 2 - This is the insert that was failing, with Thread2 ending up in
the busyhandler.  When thread2 first tried to insert, it obtained a
reserved lock.  Now that thread1 released its lock, thread2 gets an
exclusive lock and the insert finally succeeds at this point.

 

I think what is going wrong is this:

Step 11 - Thread2 tries to do an insert.  Since Thread1 has a shared
lock, thread2 acquires a reserved lock but it cannot be promoted to
Exclusive.

Step 12 - BusyHandler is not called.  Database is locked error is
returned.  Thread1's shared lock cannot be promoted to a reserved lock,
since Thread2 already has one.

 

Does that seem correct?

 

If so, my options are:

1)  rollback/commit one of the transactions

2)  use begin exclusive

 

I don't think the second one will work, since I need nested transactions
and the savepoint syntax doesn't seem to support the exclusive option.

 

Thanks,

Chris

 



The information contained in this email message and its attachments
is intended
only for the private and confidential use of the recipient(s) named
above, unless the sender expressly agrees otherwise. Transmission
of email over the Internet
 is not a secure communications medium. If you are requesting or
have requested
the transmittal of personal data, as defined in applicable privacy
laws by means
 of email or in an attachment to email you must select a more
secure alternate means of transmittal that supports your
obligations to protect such personal data. If the reader of this
message is not the intended recipient and/or you have received this
email in error, you must take no action based on the information in
this email and you are hereby notified that any dissemination,
misuse, copying, or disclosure of this communication is strictly
prohibited. If you have received
this communication in error, please notify us immediately by email
and delete the original message.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked error

[Pavel Ivanov]

Apparently the following happens:

13875           Thread2 SAVEPOINT Thread2                       success
(shared lock acquired)
13875           Thread1 SAVEPOINT Thread1                       success
(shared lock acquired)
13880           Thread2 INSERT INTO TableB              success
(reserved lock)
13890           Thread2 RELEASE SAVEPOINT Thread2       fail
(pending lock is acquired, exclusive lock cannot be acquired because
of existing shared lock, the busyhandler callback is called here)
13890           Thread1 SELECT blah FROM TableB         success
(shared lock is still active)
13906           Thread1 SELECT blah FROM TableC         success
(shared lock is still active)
13906           Thread1 INSERT INTO TableD              fail
(reserved lock cannot be acquired because another thread already
acquired one, busyhandler can NOT be called because another thread
already have pending lock and waiting is senseless - error database is
locked)

I'm not sure if SAVEPOINT actually acquires shared lock but probably
something happened which you didn't tell and it acquired shared lock.

General rule: if you're trying to promote read-only transaction into
writing transaction and get SQLITE_BUSY you MUST rollback and try
again. Another option - start transaction with BEGIN IMMEDIATE in the
first place.


Pavel

On Fri, Feb 19, 2010 at 6:47 PM, Trainor, Chris
 wrote:
> I am running into a "database is locked" error and I don't quite
> understand what is going on. If someone could explain what is happening,
> I'd appreciate it.  (I am using sqlite version 3.6.17 on Windows XP.)
>
> A busy handler callback has been set up with sqlite3_busy_handler().  In
> addition to some logging, the callback simply checks the number of
> retries and either gives up (by returning 0) if it reaches the max retry
> count or sleeps for 50 msecs then returns 1.
>
> I have two threads (each with their own connection) that are trying to
> access the database.  The 1st thread is doing some reads and writes and
> the other one is just writing.  For a while, things work as expected.
> Thread1 does some reads and writes within explicit transactions (using
> Savepoint and Release Savepoint syntax).  Thread2 attempts to write to
> the database and can't, so the busyhandler callback is called.
> Eventually, Thread2 is able to get the exclusive lock and write to the
> database (again within an explicit transaction).  Thread1 does some more
> reads and writes, etc.
>
> Here's where it stops making sense.  I'm paraphrasing what is logged out
> by our app.  The first column is the number of msecs since the process
> started.  The second obviously is the active thread.  3rd is the query
> (simplified for easy reading).  The 4th indicates success or failure.
> The 5th contains notes as to what appears to be happening.
>
>
> 13875           Thread2 SAVEPOINT Thread2                       success
> (No locks acquired)
> 13875           Thread1 SAVEPOINT Thread1                       success
> (No locks acquired)
> 13880           Thread2 INSERT INTO TableB              success
> (exclusive lock)
> 13890           Thread2 RELEASE SAVEPOINT Thread2       fail
> (the busyhandler callback is called here)
> 13890           Thread1 SELECT blah FROM TableB         success
> (shared lock???)
> 13906           Thread1 SELECT blah FROM TableC         success
> (shared lock???)
> 13906           Thread1 INSERT INTO TableD              fail
> (busyhandler is NOT called - database is locked)
>
> The call to sqlite3_step succeeds for the query "INSERT INTO TableB" at
> 13880 msecs.  I believe this means that an exclusive lock must have been
> obtained for the connection on thread2.  Thread2 attempts to release the
> savepoint right around the same time as thread1 attempts to read from
> TableB (at 13890 msecs).  Thread1 is able to read from TableB, then is
> able to read from TableC.  This seems to indicate that the connection on
> thread1 acquired a shared lock.  I don't see how this is possible, since
> thread2 should have had an exclusive lock at that point.  I am assuming
> that sqlite thinks that a deadlock will occur when thread1 tries to
> write to the database at 13906 msecs and that is why the busyhandler
> callback is not invoked.
>
> Also I'm not sure if it matters, but I am using sqlite3_exec to execute
> the SAVEPOINT and RELEASE SAVEPOINT statements.  All other queries are
> executed using prepared statements and calls to sqlite3_step.  Note that
> there are NO nested transactions created.
>
> Can anyone shed some light on this?
>
> Thanks,
> Chris
> The information contained in this email message and its attachments
> is intended
> only for the private and confidential use of the recipient(s) named
> above, unless the sender expressly agrees otherwise. Transmission
> of email over the Internet
>  is not a secure communications medium. If you are requesting or
> have requested
> the transmittal of personal data, as 

Re: [sqlite] Database is locked

[Frank Chang]

Hello, Yesterday, we recognized that we had two concurrent SQL Server 
threads reading and writing to the same sqlite database. Furthermore,the reader 
thread was not releasing it's lock. So, now we release the lock by commiting 
the transaction. As a result, we no longer get the SQLite database is locked 
message. Thank you

 

 

Retval = Keys->Execute("BEGIN EXCLUSIVE");

sprintf(Command,"SELECT [Key], [RowId], [DupeGroup] "

   "FROM [Keys] WHERE [Cluster]=\"%*.*s\"",BlockSize,BlockSize,_Key);

Keys->Prepare(Command);

while (Keys->Step()==SQLITE_ROW) {

   Keys->ColumnText(0,TestKey);

   if ((rc=CompareKeys(TestKey,_Key,0))!=0) {

   ErrorMask|=rc;

   if (DupeCount>=DedupeBlockSize)

  IncreaseDedupeBlocks();

   RowIds[DupeCount]=Keys->ColumnInt(1);

   DupeGroups[DupeCount]=Keys->ColumnInt(2);

   }

   }

   Retval = Keys->Execute("COMMIT");
  
_
Bing brings you maps, menus, and reviews organized in one place.
http://www.bing.com/search?q=restaurants=MFESRP=WLHMTAG=TEXT_MFESRP_Local_MapsMenu_Resturants_1x1
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database is locked

[Frank Chang]

  Jay Kreibich, Thank for your reply about sqlite3_busy_timeout. We 
found that the sqlite 'database is locked' error message can be fixed by 
updating two different tables in the two SQL Server 2005 client processes. 

 

UPDATE mdMatchUp SET

MatchKey = master.dbo.mdMUIncrementalBuildKeyEx(@Incremental,   Matchcode, 
Zip, Last, First, Address, NULL, NULL, NULL, NULL, NULL, NULL),

@RetVal = master.dbo.mdMUIncrementalAddRecord(@Incremental),

Status = master.dbo.mdMUIncrementalGetStatusCode(@Incremental),

DupeGroup = master.dbo.mdMUIncrementalGetDupeGroup(@Incremental)

 

Perhaps, the problem we were experiencing with the sqlite 'database is locked' 
error message is related to SQL Server 2005 locks.

 The SQL Server 2005 extended stored procedure 
master.dbo.mdMUIncrementalAddRecord(@Incremental) is a wrapper around the C/C++ 
code: 

   



sprintf(Command,"INSERT INTO [Keys] ([Key], [Cluster], "

   "[DupeGroup]) VALUES (\"%s\", \"%*.*s\", %d)",

MCKey,BlockSize,BlockSize,MCKey,DupeGroup);

 



 while (Keys->Execute(Command)==SQLITE_BUSY) {

#if defined(__unix)

   sleep(dRETRYDELAY);

#else

   Sleep(dRETRYDELAY*1000);

 #endif

 }

 

 Thank you.
  
_
Hotmail: Trusted email with powerful SPAM protection.
http://clk.atdmt.com/GBL/go/177141665/direct/01/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is locked

[Jay A. Kreibich]

On Tue, Nov 10, 2009 at 06:04:20PM -0500, Frank Chang scratched on the wall:
> 
>  We have an application which uses Microsoft SQL Server 2005 
> Extended stored procedures in conjunction with Sqlite databases. 
> We have a C++ DLL which uses the following code to insert rows
> into a SQLite database:
> 
> sprintf(Command,"INSERT INTO [Keys] ([Key], [Cluster], "
> "[DupeGroup]) VALUES (\"%s\", \"%*.*s\", %d)",
> MCKey,BlockSize,BlockSize,MCKey,DupeGroup);

  Aside: using string functions to build query strings is a Bad Idea,
  and string constants in SQL use single-quotes, not double.


> When we run this UPDATE statement from two SQL Server 2005 Management 
> Studio clients concurrently, one of the client processes returns with
> the error code Database is locked and the other client process is
> suspended. Has anyone seen this problem?

  If you keep getting SQLITE_BUSY return codes sooner or later you're
  obligated too cancel the current statement and rollback any open
  transactions.

  See:   http://sqlite.org/c3ref/busy_handler.html

  In specific:

 The presence of a busy handler does not guarantee that it will be
 invoked when there is lock contention. If SQLite determines that
 invoking the busy handler could result in a deadlock, it will go
 ahead and return SQLITE_BUSY or SQLITE_IOERR_BLOCKED instead of
 invoking the busy handler. Consider a scenario where one process
 is holding a read lock that it is trying to promote to a reserved
 lock and a second process is holding a reserved lock that it is
 trying to promote to an exclusive lock. The first process cannot
 proceed because it is blocked by the second and the second process
 cannot proceed because it is blocked by the first. If both
 processes invoke the busy handlers, neither will make any
 progress. Therefore, SQLite returns SQLITE_BUSY for the first
 process, hoping that this will induce the first process to release
 its read lock and allow the second process to proceed.

  You're basically implementing a manual busy handler with your loop.
  Sooner or later it needs to give up and start over or things can
  remain locked.  If all you're doing is waiting, you might want to
  look at sqlite3_busy_timeout():

  http://sqlite.org/c3ref/busy_timeout.html

  This will keep trying if things still look safe, but will return
  SQLITE_BUSY right away if SQLite detects a possible deadlock.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database is locked and is malformed

[D. Richard Hipp]

On May 13, 2009, at 4:09 AM, s.breith...@staubli.com wrote:

> We use QNX 6.3 on a ppc with sqlite as database for our application.  
> As
> there was a speed problem in older sqlite versions we used the PRAGMA
> SYNCHRONOUS = OFF command before creating an INSERT and the PRAGMA
> SYNCHRONOUS = ON afterwards. We used version 3.4.2 for a long time, 2
> month ago we changed to version 3.6.11 (now without pragma options).

To be clear:  Do you, or do you not use PRAGMA synchronous=OFF?

>
>  The customer
> had problems with the machine, so he switched off/on very often the  
> last
> days. But I thought sqlite should be save enough to handle power  
> fails.

Have you read http://www.sqlite.org/atomiccommit.html to understand  
the assumptions SQLite makes about the hardware during a power cycle?   
Are these assumption met in your hardware?

Does your device use flash memory?  We are told that some flash memory  
devices, when powered off in the middle of write operation, will  
randomly corrupt sectors - sectors that were completely unrelated to  
the sectors actually being written.  If you have such a situation,  
database corruption might occur on a power loss even if the database  
files were completely unused at the time of the power cycle.


> I
> need to find a solution where quickly, if I have the same problem at a
> customer without internet connection I will have a big problem.
> I think my greatest risk of database corruptions are the inserts. As  
> many
> programs may access the database, it is often locked. So I wrote a
> function that handles every insert to the database, it tries several  
> time
> to access it. Perhaps it is also wrong, that different threads  
> inside a
> program use the same sqlite3* connection for the database. (But it`s  
> OK
> according to sqlite.org, files are compiled SQLITE_THREADSAFE=1 )
> int sqlite3_exec_save(sqlite3* database, const char *command, int
> (*callback)(void *ag1,int arg2,char **arg3,char **arg4), void  
> *arg1 , char
> **aErrmsg, const char *errString, int printMessage ){
> int counter = 0;
> int returnCode;
> char help_str[500];
>
> char *errmsg = 0;
>
> srand (pthread_self());
>
> //try up to 300 times in up to 9 sec
> do{
> returnCode = sqlite3_exec(database,
> command, NULL, NULL, );
> if((returnCode == SQLITE_BUSY) ||  
> (errmsg
> != NULL && returnCode != SQLITE_ERROR)){
> usleep(2 +
> (rand()%1));//several threads wait different timescounter++;
> }
> }while ((returnCode == SQLITE_BUSY && counter < 300)  
> ||
> (errmsg != NULL && counter < 300 && returnCode != SQLITE_ERROR)); // 
> only
> repeat non SQL-Errors
> if (errmsg != NULL ){
> sprintf(help_str,"%s (%i:%s) [attempt
> %i]:", errString, returnCode, errmsg, counter);
> eprintf("could not insert to  
> database");
> logPoint(help_str);
> logPoint(command);
> }else if( counter > 5){
> eprintf("needed %i attempts to  
> insert to
> database", counter);
> }
>
> //if somebody wants to use the error messagae  
> outside the
> function
> if (aErrmsg != NULL && errmsg != NULL){
> *aErrmsg = malloc(strlen(errmsg)+1);
> strcpy(*aErrmsg,errmsg);
> }
>
> return returnCode;
> }
>
> Is it a good way to use an insert function like that?

Using sqlite3_busy_timeout() would probably be easier.

>
> Should every thread have an own sqlite* connetion?

Not necessarily.

>
> Should a program hold the sqlite* connection opened or should it close
> after every insert and open again?

Keep the connection open.

>
> I  read much about the journal handling of sqlite. As I use 4  
> databases I
> should get a Master Journal File if I update more then one database  
> at a
> time. But I´m not doing that, so there should only be a "normal" - 
> journal
> file for the database to be changed. When I looked on the corrupted
> database, there still was a -journal file, I thried to open the  
> database,
> but got an error with every SELECT. On sqlite.org is written, that the
> database should repair itself on next open, but that seems not to work
> every time.

Is the journal file readable?  Is it empty?


> Now me question to this section: Could that be a problem with
> ATTACH. I didn`t care until now if all databases are attached in every
> program or the order of attaching. Could the scenario "Open 3 dbs with
> attach, power failure while writing, after start open 4 dbs in other
> order" cause my 

Re: [sqlite] database is locked

[Shaun R.]

it wont it was a quick example, that prep is suppose to be sqlite3_step

~Shaun

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database is locked

[Ken]

you don't need to prepare when you use exec...

The following is not a step.. Don't see how this even would compile...

while(sqlite3_prep(plineInfo) == SQLITE_ROW) 

--- On Fri, 10/10/08, Shaun R. <[EMAIL PROTECTED]> wrote:

> From: Shaun R. <[EMAIL PROTECTED]>
> Subject: [sqlite] database is locked
> To: sqlite-users@sqlite.org
> Date: Friday, October 10, 2008, 3:30 PM
> I have some code that i'm having problems with.  i open
> the database, 
> prepare, and the step through.  During each step i'm
> trying to run a 
> seperate exec which is attempting to update a row.  I keep
> getting a error 
> that says database is locked.  I tried creating a
> additional open to just 
> use on the updates but i get the same error.  Short example
> below with no 
> error checking.
> 
> 
> 
> sqlite3_open("my.db", );
> 
> sqlite3_prepare(db, "SELECT ip_address FROM ips WHERE
> ip_owner=''", 
> , 0);
> 
> while(sqlite3_prep(plineInfo) == SQLITE_ROW) {
> 
>zSQL = sqlite3_mprintf("UPDATE ips set
> ip_owner='%q' WHERE ip_owner='' 
> and ip_address='%q'", username,
> sqlite3_column_text(plineInfo, 0));
> 
> sqlite3_exec(db, zSQL, 0, 0, 0) == SQLITE_OK);
> 
> }
> 
> Agian this is just a quick/basic example showing what
> i'm trying to do.  I 
> keep getting database is locked when checking the error
> returned by exec.
> 
> ~Shaun 
> 
> 
> ___
> 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

Re: [sqlite] "Database is Locked"

[D. Richard Hipp]

On Aug 19, 2008, at 11:14 AM, <[EMAIL PROTECTED]> <[EMAIL PROTECTED]>  
wrote:

> Are there
> any problems with creating the database with 3.5.7 and then reading  
> it with 3.6.1?

There are not suppose to be any difference.  Nobody else has reported  
differences.

D. Richard Hipp
[EMAIL PROTECTED]



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Database is Locked"

[rob]

Below is the call to open a new database. This comes back successful.
   sqlite3_open16(pchFileName, _sqliteDB) 
pchFileName: is the path to the database 
m_sqliteDB: is the database handler

This function executes a PRAGMA statement.
sqlite3_exec(m_sqliteDB, m_formatBuffer, g_sqlCallback, this, _sqliteErrMsg);

These are the two functions that are called. The open comes back successful, 
but when
the exec is called it returns with a "database is locked" error.


>  ---Original Message---
>  From: Robert Simpson <[EMAIL PROTECTED]>
>  Subject: Re: [sqlite] "Database is Locked"
>  Sent: Aug 19 '08 15:16
>  
>  Show and tell time!  Lets see some code :)
>  
>  -Original Message-
>  From: [EMAIL PROTECTED]
>  [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]
>  Sent: Tuesday, August 19, 2008 8:15 AM
>  To: General Discussion of SQLite Database
>  Subject: Re: [sqlite] "Database is Locked"
>  
>  I haven't changed anything. I recompiled with 3.5.7 and it worked fine. Are
>  there
>  any problems with creating the database with 3.5.7 and then reading it with
>  3.6.1?
>  
>  Thanks
>  
>  >  ---Original Message---
>  >  From: D. Richard Hipp <[EMAIL PROTECTED]>
>  >  Subject: Re: [sqlite] "Database is Locked"
>  >  Sent: Aug 19 '08 14:38
>  >  
>  >  
>  >  On Aug 19, 2008, at 10:33 AM, <[EMAIL PROTECTED]> <[EMAIL PROTECTED]>  
>  >  wrote:
>  >  
>  >  > I currently have a project that runs on a Windows XP machine. The  
>  >  > project was working with
>  >  > SQLite 3.5.7, but I recently upgraded to 3.6.1 . Since the upgrade I  
>  >  > have been getting
>  >  > an "Database is Locked" error message on any type of query that is  
>  >  > ran after the database is
>  >  > "opened". I am assuming that when the database is opened it is being  
>  >  > locked somehow. I am
>  >  > using sqlite3_open16 to open the database and sqlite3_exec to  
>  >  > execute a simple PRAGMA
>  >  > statement. If anyone knows of a solution to this problem I would  
>  >  > appreciate the help.
>  >  >
>  >  
>  >  What else have you changed other than 3.5.7 ->  3.6.1?  If you pull  
>  >  out 3.6.1 and recompile with 3.5.7 again does the problem go away?
>  >  
>  >  I do not recall making any changes to locking behavior between 3.5.7  
>  >  and 3.6.1
>  >  
>  >  D. Richard Hipp
>  >[EMAIL PROTECTED]
>  >  
>  >  
>  >  
>  >  ___
>  >  sqlite-users mailing list
>  >[EMAIL PROTECTED]
>  >  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
>  
>  
>  ___
>  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

Re: [sqlite] "Database is Locked"

[Robert Simpson]

Show and tell time!  Lets see some code :)

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]
Sent: Tuesday, August 19, 2008 8:15 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] "Database is Locked"

I haven't changed anything. I recompiled with 3.5.7 and it worked fine. Are
there
any problems with creating the database with 3.5.7 and then reading it with
3.6.1?

Thanks

>  ---Original Message---
>  From: D. Richard Hipp <[EMAIL PROTECTED]>
>  Subject: Re: [sqlite] "Database is Locked"
>  Sent: Aug 19 '08 14:38
>  
>  
>  On Aug 19, 2008, at 10:33 AM, <[EMAIL PROTECTED]> <[EMAIL PROTECTED]>  
>  wrote:
>  
>  > I currently have a project that runs on a Windows XP machine. The  
>  > project was working with
>  > SQLite 3.5.7, but I recently upgraded to 3.6.1 . Since the upgrade I  
>  > have been getting
>  > an "Database is Locked" error message on any type of query that is  
>  > ran after the database is
>  > "opened". I am assuming that when the database is opened it is being  
>  > locked somehow. I am
>  > using sqlite3_open16 to open the database and sqlite3_exec to  
>  > execute a simple PRAGMA
>  > statement. If anyone knows of a solution to this problem I would  
>  > appreciate the help.
>  >
>  
>  What else have you changed other than 3.5.7 ->  3.6.1?  If you pull  
>  out 3.6.1 and recompile with 3.5.7 again does the problem go away?
>  
>  I do not recall making any changes to locking behavior between 3.5.7  
>  and 3.6.1
>  
>  D. Richard Hipp
>  [EMAIL PROTECTED]
>  
>  
>  
>  ___
>  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


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Database is Locked"

[rob]

I haven't changed anything. I recompiled with 3.5.7 and it worked fine. Are 
there
any problems with creating the database with 3.5.7 and then reading it with 
3.6.1?

Thanks

>  ---Original Message---
>  From: D. Richard Hipp <[EMAIL PROTECTED]>
>  Subject: Re: [sqlite] "Database is Locked"
>  Sent: Aug 19 '08 14:38
>  
>  
>  On Aug 19, 2008, at 10:33 AM, <[EMAIL PROTECTED]> <[EMAIL PROTECTED]>  
>  wrote:
>  
>  > I currently have a project that runs on a Windows XP machine. The  
>  > project was working with
>  > SQLite 3.5.7, but I recently upgraded to 3.6.1 . Since the upgrade I  
>  > have been getting
>  > an "Database is Locked" error message on any type of query that is  
>  > ran after the database is
>  > "opened". I am assuming that when the database is opened it is being  
>  > locked somehow. I am
>  > using sqlite3_open16 to open the database and sqlite3_exec to  
>  > execute a simple PRAGMA
>  > statement. If anyone knows of a solution to this problem I would  
>  > appreciate the help.
>  >
>  
>  What else have you changed other than 3.5.7 ->  3.6.1?  If you pull  
>  out 3.6.1 and recompile with 3.5.7 again does the problem go away?
>  
>  I do not recall making any changes to locking behavior between 3.5.7  
>  and 3.6.1
>  
>  D. Richard Hipp
>  [EMAIL PROTECTED]
>  
>  
>  
>  ___
>  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

Re: [sqlite] "Database is Locked"

[D. Richard Hipp]

On Aug 19, 2008, at 10:33 AM, <[EMAIL PROTECTED]> <[EMAIL PROTECTED]>  
wrote:

> I currently have a project that runs on a Windows XP machine. The  
> project was working with
> SQLite 3.5.7, but I recently upgraded to 3.6.1 . Since the upgrade I  
> have been getting
> an "Database is Locked" error message on any type of query that is  
> ran after the database is
> "opened". I am assuming that when the database is opened it is being  
> locked somehow. I am
> using sqlite3_open16 to open the database and sqlite3_exec to  
> execute a simple PRAGMA
> statement. If anyone knows of a solution to this problem I would  
> appreciate the help.
>

What else have you changed other than 3.5.7 ->  3.6.1?  If you pull  
out 3.6.1 and recompile with 3.5.7 again does the problem go away?

I do not recall making any changes to locking behavior between 3.5.7  
and 3.6.1

D. Richard Hipp
[EMAIL PROTECTED]



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database always locked

[Dennis Cote]

[EMAIL PROTECTED] wrote:
> I use a c++ program to  
> open a connection to my database with no issues, but when trying to  
> exec any sql statements after that, i get SQLITE_BUSY (i.e. database  
> is locked). 
> 
> Even if I change the sql statement, I always get SQLITE_BUSY. This is  
> frustrating because this is a single-threaded app, so I have no idea  
> why the database would be locked, especially before I have called any  
> write operations to it (i.e. before a CREATE, INSERT, etc). Any ideas  
> as to why this is happening and how I can fix it? Thanks.

I don't see anything obviously wrong with your code. I suspect you may 
have another instance of your code running or possibly an sqlite command 
shell with the same file open. Others have also had issues with things 
such as antivirus software that opens the database file after it's 
created but before SQLite tries to get a write lock. You should try 
disabling any antivirus software.

You should also probably look at changing your code to use the new 
prepared staetment API (see http://www.sqlite.org/capi3.html for more 
info). The callback feature of the sqlite_exec API is retained for 
backwards compatibility, but it is not the best way to read data out of 
a database. The SQLite quickstart documentation should be revised to use 
the preferred interface, but it has not been done yet. In any event you 
do not need a callback function to execute a "CREATE TABLE" statement 
(but it should not cause a problem either).

HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database always locked

[John Stanton]

You define a callback in your CREATE statement.  Why?  Where is it?

[EMAIL PROTECTED] wrote:
> I'm a beginning sqlite user (v3.5.8 for Linux). I use a c++ program to  
> open a connection to my database with no issues, but when trying to  
> exec any sql statements after that, i get SQLITE_BUSY (i.e. database  
> is locked). Here is the code I use:
> 
> int main() {
>sqlite3 *db;
>int ret; // Return value of sqlite3 calls
>string dbName = "emails.db"; // Database name
>char *zErrMsg;
> 
>// Open a connection to the database
>ret = sqlite3_open(dbName.c_str(), );
>if (ret) {
>  cout << "Can't open database: " << sqlite3_errmsg(db) << endl;
>  sqlite3_close(db);
>  exit(1);
>}
> 
>string stmt = "CREATE TABLE IF NOT EXISTS";
>string cols = "Received (Sender varchar(80), Receiver varchar(80), Subject
> varchar(512), Message varchar(512))";
>stmt = stmt + " " + cols;
>ret = sqlite3_exec(db, stmt.c_str(), callback, 0, );
>if (ret != SQLITE_OK) {
>  cout << "SQL error: " << zErrMsg << endl;
>  sqlite3_free(zErrMsg);
>}
> 
>sqlite3_close(db);
> }
> 
> // Based on the example code provided at www.sqlite.org
> static int callback(void *NotUsed, int argc, char **argv, char
> **azColName){
>int i;
>for(i=0; i  printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
>}
>printf("\n");
>return 0;
> }
> 
> Even if I change the sql statement, I always get SQLITE_BUSY. This is  
> frustrating because this is a single-threaded app, so I have no idea  
> why the database would be locked, especially before I have called any  
> write operations to it (i.e. before a CREATE, INSERT, etc). Any ideas  
> as to why this is happening and how I can fix it? Thanks.
> ___
> 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

Re: [sqlite] "database is locked" on clean install, empty database?

[Sean Rhea]

On Mon, Mar 3, 2008 at 12:59 PM, Andreas Kupries
<[EMAIL PROTECTED]> wrote:
>  Is the database file by chance in a NFS mounted directory ?

That was it.  Thanks!

Sean
-- 
"Humanity has advanced, when it has advanced, not because it has been
sober, responsible, and cautious, but because it has been playful,
rebellious, and immature." -- Tom Robbins
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database is locked" on clean install, empty database?

[Dennis Cote]

Sean Rhea wrote:
> sqlite> create table foo (node_id, timestamp, tput);
> SQL error: database is locked
> 
> What am I doing wrong?
> 

Sean,

Ensure that you have write privileges for the database file and the 
directory it is in. SQLite needs to create a journal file in the same 
directory for you to make any changes.

HTH
Dennis Cote

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database is locked" on clean install, empty database?

[Andreas Kupries]

> 
> I must be missing something:
> 
> $ sudo apt-get install sqlite3
> ...
> Selecting previously deselected package sqlite3.
> ...
> Setting up sqlite3 (3.3.8-1.1) ...
> $ ls -l test.db
> ls: test.db: No such file or directory
> $ sqlite3 test.db
> SQLite version 3.3.8
> Enter ".help" for instructions
> sqlite> create table foo (node_id, timestamp, tput);
> SQL error: database is locked

> What am I doing wrong?

Is the database file by chance in a NFS mounted directory ?

--
Andreas Kupries <[EMAIL PROTECTED]>
Developer @ http://www.ActiveState.com
Tel: +1 778-786-1122
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database is locked" SQLITE_BUSY when db is on network drive...

[T]

Hi Chase,

it connects fine.  no errors.  but then i try to create a temp  
table (which, like i said, works if the db is local) it fails  
immediately with SQLITE_BUSY "database is locked".


It's some Mac vs SQLite bug. You can recompile SQLite to fix it. See  
the old post below. And search the archives for "locked" in the subject.


Tom

 
From: T <[EMAIL PROTECTED]>
Date: 11 May 2007 10:54:02 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] database is locked error

Is there a timetable for including the fix for opening SQLite files  
on a shared volume? Since it's fairly trivial, is there a reason why  
it hasn't been included yet?


Thanks,
Tom

 
From: T <[EMAIL PROTECTED]>
Date: 29 April 2007 3:35:00 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] database is locked error

Following up an old thread:

The SQLite sources include an (Apple-supplied) patch to work around  
the problem. Recompile

with

   -DSQLITE_ENABLE_LOCKING_STYLE=1

We are working toward turning on this patch by default, but we are  
not quite there yet.


I compiled and ran SQLite 3.3.17 and got the old error again when  
accessing a database file on a server volume, with SQLite saying it  
is locked.


Does this mean that we are still "not quite there yet" with a default  
fix? Any time frame?


Thanks,
Tom

--
Best value broadband in Australia.
$3 per GB excess instead of $150 from BigPond
Free uploads, free morning downloads.
http://www.tandb.com.au/broadband/?sig


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] "database is locked" SQLITE_BUSY when db is on network drive...

[Mitchell Vincent]

Can you do any other write queries (INSERT or UPDATE for example)?

On 8/2/07, Chase <[EMAIL PROTECTED]> wrote:
>
> running sqlite 3.4.1 on mac os x 10.4
>
> i've set up a file share on another mac running 10.4 and placed a small
> db file on the share and chmod'ed it to 777 (full access).
>
> i go to the development mac and log into the other mac as the user who
> owns that db file.
>
> the path on my dev mac to that shared db is now:
> /Volumes/SharedFolder/smalldb.db
>
> the test app (which works perfectly with the same exact small db
> sitting on its local drive) is launched and a connection is made to the
> db sitting on the other mac.
>
> it connects fine.  no errors.  but then i try to create a temp table
> (which, like i said, works if the db is local) it fails immediately
> with SQLITE_BUSY "database is locked".
>
> NO ONE else is accessing this database file, so how is that possible?
>
>
> what am i doing wrong?
>
> - chase
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>


-- 
- Mitchell Vincent
- K Software - Innovative Software Solutions
- Visit our website and check out our great software!
- http://www.ksoftware.net

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] database is locked error

[T]

Is there a timetable for including the fix for opening SQLite files  
on a shared volume? Since it's fairly trivial, is there a reason why  
it hasn't been included yet?


Thanks,
Tom

 
From: T <[EMAIL PROTECTED]>
Date: 29 April 2007 3:35:00 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] database is locked error

Following up an old thread:

The SQLite sources include an (Apple-supplied) patch to work around  
the problem. Recompile

with

   -DSQLITE_ENABLE_LOCKING_STYLE=1

We are working toward turning on this patch by default, but we are  
not quite there yet.


I compiled and ran SQLite 3.3.17 and got the old error again when  
accessing a database file on a server volume, with SQLite saying it  
is locked.


Does this mean that we are still "not quite there yet" with a default  
fix? Any time frame?


Thanks,
Tom




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] database is locked error

[T]

Following up an old thread:

The SQLite sources include an (Apple-supplied) patch to work around  
the problem. Recompile

with

   -DSQLITE_ENABLE_LOCKING_STYLE=1

We are working toward turning on this patch by default, but we are  
not quite there yet.


I compiled and ran SQLite 3.3.17 and got the old error again when  
accessing a database file on a server volume, with SQLite saying it  
is locked.


Does this mean that we are still "not quite there yet" with a default  
fix? Any time frame?


Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] database is locked error with 3.3.13

[Kuznetsov, Mike]

I'm experiencing the same problem on QNX compiled for Renesas SH4...

> This is not a bug in SQLite - it is a bug in Apple's implementation
>(or more precisely their lack of implementation) of POSIX advisory
> locks for AppleShare mounted volumes.  The SQLite sources include
> an (Apple-supplied) patch to work around the problem. Recompile
> with
> 
>-DSQLITE_ENABLE_LOCKING_STYLE=1
> 
> We are working toward turning on this patch by default, but we are not
> quite there yet.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] database is locked error on Mac OS X

[T]

Hi Richard and Puneet,

I just wanted to say thanks, and to record your combined advice that  
worked. Much of this may be superfluous or painfully obvious, but it  
worked:


1. In the sqlite-3.3.13 downloaded source directory, execute:

./configure

2. That creates a new file "MakeFile". Edit that file in any text  
editor, and add these lines:


# flag to deal with Mac OS X file locking on shared drives
TCC += -DSQLITE_ENABLE_LOCKING_STYLE=1

I put them just before the line:

# You should not have to change anything below this line

3. Execute:

sudo make
sudo make install

Done.

You can now run/test the new sqlite3 command line tool by executing:

/usr/local/bin/sqlite3

which will show:

SQLite version 3.3.13
Enter ".help" for instructions

Editing a database file on a remote volume now works with this  
modified latest version.


Thanks again.
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] database is locked error with 3.3.13

[drh]

T <[EMAIL PROTECTED]> wrote:
> 
> I recently installed SQLite 3.3.13, after having used previous  
> versions. I now get an error:
> 
> Error: database is locked
> 
> when I use the sqlite3 command line tool to access a database on a  
> shared volume.
> 
> But opening the same file with an earlier version works fine.
> 
> I'm not sure what version introduced this problem. I suspect it's  
> after 3.3.9. It's definitely after 3.1.3.
> 
> I'm using Mac OS X 10.4.8, with the database file on an AppleShare  
> mounted volume.
> 
> What's the issue here? Is it a known bug? Will it be fixed?
> 

This is not a bug in SQLite - it is a bug in Apple's implementation
(or more precisely their lack of implementation) of POSIX advisory
locks for AppleShare mounted volumes.  The SQLite sources include
an (Apple-supplied) patch to work around the problem. Recompile
with

   -DSQLITE_ENABLE_LOCKING_STYLE=1

We are working toward turning on this patch by default, but we are not
quite there yet.

--
D. Richard Hipp  <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] database is locked error with 3.3.13

[Ken]

I have the following sources which one would you like?
 
 sqlite-2.8.16.tag.gz  sqlite-3.3.8.tar.gz
 sqlite-3.2.2.tar.gz   sqlite-3.3.9.tar.gz
 sqlite-3.2.8.tar.gz 
 sqlite-3.3.10.tar.gz
 sqlite-3.3.12.tar.gz 
 sqlite-3.3.13.tar.gz 
 sqlite-3.3.5.tar.gz   
 sqlite-3.3.7.tar.gz
 
 Ken
 

T <[EMAIL PROTECTED]> wrote: Hi all,

Following up:

> I recently installed SQLite 3.3.13, after having used previous  
> versions. I now get an error:
>
> Error: database is locked
>
> when I use the sqlite3 command line tool to access a database on a  
> shared volume.
>
> But opening the same file with an earlier version works fine.
>
> I'm not sure what version introduced this problem. I suspect it's  
> after 3.3.9. It's definitely after 3.1.3.
>
> I'm using Mac OS X 10.4.8, with the database file on an AppleShare  
> mounted volume.

I tried using sqlite 3.3.10 (which I had on another machine) and  
still have the problem. I looked for 3.3.9 to re-install it, to try  
that version, but couldn't find it on the sqlite.org web site.

Have others experienced a locking error on remote volumes?

Where can I get 3.3.9 and earlier source code?

Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] database is locked error with 3.3.13

[BardzoTajneKonto]

> Where can I get 3.3.9 and earlier source code?

http://www.sqlite.org/sqlite-source-3_3_0.zip
...
http://www.sqlite.org/sqlite-source-3_3_9.zip

But it would be nice to have direct link on the website, or even better to 
have tags for all releases in CVS.

-
Aparat cyfrowy, odtwarzacz mp3 i inne nagrody. Sprawd¼ nowy konkurs na 
TeleInteria!>> http://link.interia.pl/f1a2d


-
To unsubscribe, send email to [EMAIL PROTECTED]
-