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 

[sqlite] Database is locked error

[Trainor, Chris]

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 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] "database is locked" error using lastest linux kernel

[hua zhou]

My problem is get a "database is locked" error using lastest linux kernel 
(above 2.6.28) ,  while the code can run smoothly on linux 2.6.26.2 
kernel(vmware pc686 host) and on 2.6.26.3 kernel(arm9 embed system).   
The problem code is:
#if 1
 if (sqlite3_exec(gJcDb, "PRAGMA cache_size = 4000", NULL,  NULL, ) != 
SQLITE_OK)
 {
  fprintf(stderr, "!!!cache_size set error, %s\n", errMsg);
  sqlite3_free(errMsg);
 }
#endif
Even if  I comment the the  #if 0/1 #endif code block, still can't  open a 
table and access the table data. I tried sqlite3.6.7 and sqlite3.6.16, the 
problem is same.

Three attached files are compiling sqlite3 Makefile, short test 
code(code.c) and test database(jc.db). 
Compile Sqlite with full functions and NDEBUG option and run, I get following 
info:
fcntl unknown 4 1 0
fcntl unknown 4 2 0
fcntl 1073864000 4 SETLK RDLCK 0 1 0 -1
fcntl-failure-reason: RDLCK 0 1 0
fcntl 1073864000 4 SETLK RDLCK 1073741824 1 1 -1
fcntl-failure-reason: RDLCK 1073741824 1 1
PRAGMA page_size value is 1024
PRAGMA temp_store value is 2
PRAGMA read_uncommitted value is 1
PRAGMA journal_mode value is off
fcntl 1073864000 4 SETLK RDLCK 1073741824 1 229840 -1
fcntl-failure-reason: RDLCK 1073741824 1 229840
fcntl 1073864000 4 SETLK RDLCK 1073741824 1 229840 -1
fcntl-failure-reason: RDLCK 1073741824 1 229840
!!!Load Terminal from db failedfcntl 1073864000 4 SETLK RDLCK 1073741824 1 
229840 -1
fcntl-failure-reason: RDLCK 1073741824 1 229840

Any advise from you will be appreciated!



  inline static int PragmaSetCallback(void * pParam, int pColumnCount, char ** 
pColumnValue, char ** pColumnName)
{
fprintf(stdout,  "%s value is %s\n", (char *)pParam, pColumnValue[0]);
return 0;
}

static bool OpenAndInitDb(char * pDbFileName)
{
char * errMsg = NULL;

sqlite3_enable_shared_cache(1);
if (sqlite3_open(pDbFileName, ) != SQLITE_OK)
{
fprintf(stderr, "!!!Open database error: %s\n", 
sqlite3_errmsg(gJcDb));
return false;
}
#if 1
if (sqlite3_exec(gJcDb, "PRAGMA cache_size = 4000", NULL,  NULL, 
) != SQLITE_OK)
{
fprintf(stderr, "!!!cache_size set error, %s\n", errMsg);
sqlite3_free(errMsg);
}
#endif
sqlite3_exec(gJcDb, "PRAGMA cache_size", PragmaSetCallback, "PRAGMA 
cache_size", );



#if 1
if (SQLITE_OK != sqlite3_exec(gJcDb, "PRAGMA synchronous = FULL", NULL, 
 NULL, )) //OFF FULL NORMAL
{
fprintf(stderr, "!!!synchronous set error, %s\n", errMsg);
sqlite3_free(errMsg);
}
#endif
sqlite3_exec(gJcDb, "PRAGMA synchronous", PragmaSetCallback, "PRAGMA 
synchronous", );

return true;
}


static bool LoadTerminalFromDb(sqlite3 * pDb, Terminal * pTerminal)
{
pTerminal->WorkStateId = 1;
pTerminal->DefaultUpChannelTypeId = ChannelType_UpTnGprsClient;

pTerminal->IsChanged = false;

   sqlite3_stmt * stmt = 0;
   if (sqlite3_prepare_v2(pDb, "select * from Terminal",  -1, , 0) != 
SQLITE_OK)
   {
   return false;
   }
   if (sqlite3_step(stmt) != SQLITE_ROW)
   {
sqlite3_finalize(stmt);
return false;
   }
   return true;
}


sqlite3  *gJcDb = NULL;

int main(int argc, char *argv[])
{
char * db = "./jc.db";
if (access(db, F_OK) || !OpenAndInitDb(db))
{
fprintf(stderr, "!!!Open and init db failed");
return  1;
}

if (!LoadTerminalFromDb(gJcDb, ))
{
fprintf(stderr, "!!!Load Terminal from db failed");
CloseDb(gJcDb);
return 2;
}

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

[sqlite] "database is locked" error using lastest linux kernel

[hua zhou]

My problem is get a "database is locked" error using lastest linux kernel 
(above 2.6.28) ,  while the code can run smoothly on linux 2.6.26.2 
kernel(vmware pc686 host) and on 2.6.26.3 kernel(arm9 embed system).   

The problem code is:
#if 1
 if (sqlite3_exec(gJcDb, "PRAGMA cache_size = 4000", NULL,  NULL, ) != 
SQLITE_OK)
 {
  fprintf(stderr, "!!!cache_size set error, %s\n", errMsg);
  sqlite3_free(errMsg);
 }
#endif

Even if  I comment the the  #if 0/1 #endif code block, still can't  open a 
table and access the table data. I tried sqlite3.6.7 and sqlite3.6.16, the 
problem is same.

Three attached files are compiling sqlite3 Makefile, short test 
code(code.c) and test database(jc.db).


Any advise from you will be appreciated!



  inline static int PragmaSetCallback(void * pParam, int pColumnCount, char ** 
pColumnValue, char ** pColumnName)
{
fprintf(stdout,  "%s value is %s\n", (char *)pParam, pColumnValue[0]);
return 0;
}

static bool OpenAndInitDb(char * pDbFileName)
{
char * errMsg = NULL;

sqlite3_enable_shared_cache(1);
if (sqlite3_open(pDbFileName, ) != SQLITE_OK)
{
fprintf(stderr, "!!!Open database error: %s\n", 
sqlite3_errmsg(gJcDb));
return false;
}
#if 1
if (sqlite3_exec(gJcDb, "PRAGMA cache_size = 4000", NULL,  NULL, 
) != SQLITE_OK)
{
fprintf(stderr, "!!!cache_size set error, %s\n", errMsg);
sqlite3_free(errMsg);
}
#endif
sqlite3_exec(gJcDb, "PRAGMA cache_size", PragmaSetCallback, "PRAGMA 
cache_size", );



#if 1
if (SQLITE_OK != sqlite3_exec(gJcDb, "PRAGMA synchronous = FULL", NULL, 
 NULL, )) //OFF FULL NORMAL
{
fprintf(stderr, "!!!synchronous set error, %s\n", errMsg);
sqlite3_free(errMsg);
}
#endif
sqlite3_exec(gJcDb, "PRAGMA synchronous", PragmaSetCallback, "PRAGMA 
synchronous", );

return true;
}


static bool LoadTerminalFromDb(sqlite3 * pDb, Terminal * pTerminal)
{
pTerminal->WorkStateId = 1;
pTerminal->DefaultUpChannelTypeId = ChannelType_UpTnGprsClient;

pTerminal->IsChanged = false;

   sqlite3_stmt * stmt = 0;
   if (sqlite3_prepare_v2(pDb, "select * from Terminal",  -1, , 0) != 
SQLITE_OK)
   {
   return false;
   }
   if (sqlite3_step(stmt) != SQLITE_ROW)
   {
sqlite3_finalize(stmt);
return false;
   }
   return true;
}


sqlite3  *gJcDb = NULL;

int main(int argc, char *argv[])
{
char * db = "./jc.db";
if (access(db, F_OK) || !OpenAndInitDb(db))
{
fprintf(stderr, "!!!Open and init db failed");
return  1;
}

if (!LoadTerminalFromDb(gJcDb, ))
{
fprintf(stderr, "!!!Load Terminal from db failed");
CloseDb(gJcDb);
return 2;
}

return 0;
}#!/usr/make
#
# Makefile for SQLITE
#
# This makefile is suppose to be configured automatically using the
# autoconf.  But if that does not work for you, you can configure
# the makefile manually.  Just set the parameters below to values that
# work well for your system.
#
# If the configure script does not work out-of-the-box, you might
# be able to get it to work by giving it some hints.  See the comment
# at the beginning of configure.in for additional information.
#

# The toplevel directory of the source tree.  This is the directory
# that contains this "Makefile.in" and the "configure.in" script.
#
TOP = .

# C Compiler and options for use in building executables that
# will run on the platform that is doing the build.
#
BCC = gcc  -g

# C Compile and options for use in building executables that 
# will run on the target platform.  (BCC and TCC are usually the
# same unless your are cross-compiling.)
#
TCC = arm-9tdmi-linux-gnueabi-gcc   -g -O2 -DSQLITE_OS_UNIX=1 -I. -I${TOP}/src

# Define this for the autoconf-based build, so that the code knows it can
# include the generated config.h
# 
TCC += -D_HAVE_SQLITE_CONFIG_H

# Define -DNDEBUG to compile without debugging (i.e., for production usage)
# Omitting the define will cause extra debugging code to be inserted and
# includes extra comments when "EXPLAIN stmt" is used.
#
TCC += -DNDEBUG -DSQLITE_ALLOW_XTHREAD_CONNECT=1

# Compiler options needed for programs that use the TCL library.
#
TCC += 

# The library that programs using TCL must link against.
#
LIBTCL =  

# Compiler options needed for programs that use the readline() library.
#
READLINE_FLAGS = -DHAVE_READLINE=0 

# The library that programs using readline() must link against.
#
LIBREADLINE = 

# Should the database engine be compiled threadsafe
#
TCC += -DSQLITE_THREADSAFE=1

# Do threads override each others locks by default (1), or do we test (-1)
#
TCC += -DSQLITE_THREAD_OVERRIDE_LOCK=-1

# Any target libraries which libsqlite must be linked against
# 
TLIBS = -lpthread 

# Flags controlling use of the in memory btree implementation
#
# SQLITE_TEMP_STORE is 0 to force 

[sqlite] "database is locked" error using lastest linux kernel

[hua zhou]

My problem is get a "database is locked" error using lastest linux kernel 
(above 2.6.28) ,  while the code can run smoothly on linux 2.6.26.2 
kernel(vmware pc686 host) and on 2.6.26.3 kernel(arm9 embed system).   

The problem code is:
#if 1
 if (sqlite3_exec(gJcDb, "PRAGMA cache_size = 4000", NULL,  NULL, ) != 
SQLITE_OK)
 {
  fprintf(stderr, "!!!cache_size set error, %s\n", errMsg);
  sqlite3_free(errMsg);
 }
#endif

Even if  I comment the the  #if 0/1 #endif code block, still can't  open a 
table and access the table data. I tried sqlite3.6.7 and sqlite3.6.16, the 
problem is same.

Three attached files are compiling sqlite3 Makefile, short test 
code(code.c) and test database(jc.db).


Any advise from you will be appreciated!



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

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: Fwd: [sqlite] database is locked error on Mac OS X

[P Kishor]

On 3/10/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

T <[EMAIL PROTECTED]> wrote:
> Can someone please tell me (politely ;-) where to put the -
> DSQLITE_ENABLE_LOCKING_STYLE=1
>
> I tried inserting it arbitrarily in the Makefile.in file but it
> didn't seem to make a difference.
>

Do "configure".  Then edit the Makefile that is generated.


I think Tom is trying to figure out where exactly in the Makefile to
insert the D flag. Tom, I am walking on thin ice here, but after you
have run ./configure, you could try inserting a line like so pretty
much anywhere in your Makefile.in AFTER TCC has been declared. So, you
could add a line like so

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

Obviously, I could be way wrong about this, but if I am, hopefully
someone will chime up and tell us so.,


--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

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

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

[drh]

T <[EMAIL PROTECTED]> wrote:
> Can someone please tell me (politely ;-) where to put the - 
> DSQLITE_ENABLE_LOCKING_STYLE=1
> 
> I tried inserting it arbitrarily in the Makefile.in file but it  
> didn't seem to make a difference.
> 

Do "configure".  Then edit the Makefile that is generated.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

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

[T]

Can someone please tell me (politely ;-) where to put the - 
DSQLITE_ENABLE_LOCKING_STYLE=1


I tried inserting it arbitrarily in the Makefile.in file but it  
didn't seem to make a difference.


Don't laugh :-[ ;-)

Thanks,
Tom

 
From: T <[EMAIL PROTECTED]>
Date: 9 March 2007 1:46:21 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] database is locked error on Mac OS X
Reply-To: sqlite-users@sqlite.org


I recently installed SQLite 3.3.13, after having used previous
versions. I now get an error:

Error: database is locked


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.


Thank you :-)


Recompile with

   -DSQLITE_ENABLE_LOCKING_STYLE=1


Pardon my ignorance, but where/how exactly do I enter this before  
recompiling?


Thanks,
Tom

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

[sqlite] database is locked error on Mac OS X

[T]

I recently installed SQLite 3.3.13, after having used previous
versions. I now get an error:

Error: database is locked


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.


Thank you :-)


Recompile with

   -DSQLITE_ENABLE_LOCKING_STYLE=1


Pardon my ignorance, but where/how exactly do I enter this before  
recompiling?


Thanks,
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]
-

Re: [sqlite] database is locked error with 3.3.13

[T]

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]
-

[sqlite] database is locked error with 3.3.13

[T]

Hi all,

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?

Thanks,
Tom


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

Re: [sqlite] "Database is locked" error in PHP via PDO despite setting timeout

[John Stanton]

If you want to use a lightweight DB like Sqlite and you are setting up 
your own daemon and server situation then you can place the DB 
synchronization function in the daemon around the Sqlite so that its 
action is single streamed.  In a similar situation we have installations 
which manage many hundreds of simultaneous users.


If you don't want to do that, use a DBMS like PostgreSQL which manages 
it all for you by having a DB server, not linking the DB function into 
the application.


Mark Robson wrote:

On Monday 20 March 2006 11:47, [EMAIL PROTECTED] wrote:


BTW: Lots of people have multiple processes writing to the same
SQLite database without problems - the SQLite website is a good
example.  I do not know what you are doing wrong to get the
locking problems you are experiencing.



I don't know how they manage it (unless of course, many of their writes fail 
and the txns roll back, and they don't notice or care).


On Monday 20 March 2006 11:58, Roger wrote:


I am developing a web based application in PHP/Sqlite and i am forever
getting that error. What i normally do is a simple

service httpd restart.



This is no good. I'm creating a daemon-based server application, which is 
carrying out autonomous tasks. It does not currently run under httpd, and I 
have no plans to make it do so.


I have several processes which are carrying out a fair amount of work inside a 
transaction - doing several writes, then doing some other time-consuming 
operations, then providing everything goes OK, committing these transactions.


This means that there are some relatively long-lived transactions (several 
seconds, anyway) in progress.


However, with proper locking this should NOT cause a problem - it should 
simply serialise the transactional operations (or so I thought).


As it is, I've actually tried to port this to MySQL (using Mysql5 and InnoDB), 
but I'm getting some problems there too - I think I'll have to review my use 
of transactions etc.


Regards
 Mark

Re: [sqlite] "Database is locked" error in PHP via PDO despite setting timeout

[Mark Robson]

On Monday 20 March 2006 11:47, [EMAIL PROTECTED] wrote:
> BTW: Lots of people have multiple processes writing to the same
> SQLite database without problems - the SQLite website is a good
> example.  I do not know what you are doing wrong to get the
> locking problems you are experiencing.

I don't know how they manage it (unless of course, many of their writes fail 
and the txns roll back, and they don't notice or care).

On Monday 20 March 2006 11:58, Roger wrote:
> I am developing a web based application in PHP/Sqlite and i am forever
> getting that error. What i normally do is a simple
>
> service httpd restart.

This is no good. I'm creating a daemon-based server application, which is 
carrying out autonomous tasks. It does not currently run under httpd, and I 
have no plans to make it do so.

I have several processes which are carrying out a fair amount of work inside a 
transaction - doing several writes, then doing some other time-consuming 
operations, then providing everything goes OK, committing these transactions.

This means that there are some relatively long-lived transactions (several 
seconds, anyway) in progress.

However, with proper locking this should NOT cause a problem - it should 
simply serialise the transactional operations (or so I thought).

As it is, I've actually tried to port this to MySQL (using Mysql5 and InnoDB), 
but I'm getting some problems there too - I think I'll have to review my use 
of transactions etc.

Regards
 Mark

Re: [sqlite] "Database is locked" error in PHP via PDO despite setting timeout

[Roger]

What i normally do in this scenario is just a simple 
httpd service restart.

That normally does the trick because i am building an application also
with PHP/Sqlite.

On Mon, 2006-03-20 at 06:47 -0500, [EMAIL PROTECTED] wrote:
> Mark Robson <[EMAIL PROTECTED]> wrote:
> > 
> > If the answer is "nothing", I'm going straight over to MySQL :)
> > 
> 
> The advantages of SQLite are that there are no administrative
> hassles - there is nothing to set up or configure and the
> database is contained in a single disk file that you can copy
> to a flash drive or something.  Client/server database engines
> like MySQL normally default to READ COMMITTED isolation, which
> means you never have database locking problems, but at the expense
> of considerable setup and configuration complexity.
> 
> It sounds to me like you are more interested in READ COMMITTED
> isolation and do not mind the added complexity, in which case
> you should be using a client/server database, such as MySQL.
> 
> BTW: Lots of people have multiple processes writing to the same
> SQLite database without problems - the SQLite website is a good
> example.  I do not know what you are doing wrong to get the
> locking problems you are experiencing.
> 
> --
> D. Richard Hipp   <[EMAIL PROTECTED]>
> 

Re: [sqlite] "Database is locked" error in PHP via PDO despite setting timeout

[drh]

Mark Robson <[EMAIL PROTECTED]> wrote:
> 
> If the answer is "nothing", I'm going straight over to MySQL :)
> 

The advantages of SQLite are that there are no administrative
hassles - there is nothing to set up or configure and the
database is contained in a single disk file that you can copy
to a flash drive or something.  Client/server database engines
like MySQL normally default to READ COMMITTED isolation, which
means you never have database locking problems, but at the expense
of considerable setup and configuration complexity.

It sounds to me like you are more interested in READ COMMITTED
isolation and do not mind the added complexity, in which case
you should be using a client/server database, such as MySQL.

BTW: Lots of people have multiple processes writing to the same
SQLite database without problems - the SQLite website is a good
example.  I do not know what you are doing wrong to get the
locking problems you are experiencing.

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

[sqlite] "Database is locked" error in PHP via PDO despite setting timeout

[Mark Robson]

Hi all,

I'm using Sqlite3 from PHP via PDO.

My application was working fine as long as there was only one process 
accessing the database, then I ran two instances at once. Now one of the 
processes is getting 

"Error message: SQLSTATE[HY000]: General error: 5 database is locked"

when trying to execute a statement which modifies the database.

I understand that this is liable to happen, unless a timeout is set via 
sqlite3_busy_timeout().

I have set this timeout using the $db->setAttribute(PDO::ATTR_TIMEOUT, 5.0) 
however it's made absolutely no difference to the behaviour.

It's certainly not waiting 5 seconds before giving me this error, and it's 
happening with exactly the same frequency as before. It's as if it's being 
ignored.

I've stepped through PHP in the debugger (gdb) and it's definitely calling 
sqlite3_busy_timeout with the appropriate parameters (5000 ms).

What else can I do to prevent this?

If the answer is "nothing", I'm going straight over to MySQL :)

Mark