Re: [sqlite] Database is locked error
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
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 chris.trai...@ironmountain.com 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
[sqlite] Database is locked error
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
Re: [sqlite] Database is locked error
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 chris.trai...@ironmountain.com 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 defined in applicable privacy laws by means of email or in an attachment
[sqlite] database is locked error using lastest linux kernel
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, errMsg) != 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 compilingmaking 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, gJcDb) != 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, errMsg) != 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, errMsg); #if 1 if (SQLITE_OK != sqlite3_exec(gJcDb, PRAGMA synchronous = FULL, NULL, NULL, errMsg)) //OFF FULL NORMAL { fprintf(stderr, !!!synchronous set error, %s\n, errMsg); sqlite3_free(errMsg); } #endif sqlite3_exec(gJcDb, PRAGMA synchronous, PragmaSetCallback, PRAGMA synchronous, errMsg); 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, stmt, 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, gTerminal)) { 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
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, errMsg) != 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 compilingmaking 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
[sqlite] database is locked error using lastest linux kernel
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, errMsg) != 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 compilingmaking 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, gJcDb) != 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, errMsg) != 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, errMsg); #if 1 if (SQLITE_OK != sqlite3_exec(gJcDb, PRAGMA synchronous = FULL, NULL, NULL, errMsg)) //OFF FULL NORMAL { fprintf(stderr, !!!synchronous set error, %s\n, errMsg); sqlite3_free(errMsg); } #endif sqlite3_exec(gJcDb, PRAGMA synchronous, PragmaSetCallback, PRAGMA synchronous, errMsg); 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, stmt, 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, gTerminal)) { 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
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: TB [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
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
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
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] -
Fwd: [sqlite] database is locked error on Mac OS X
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: TB [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] -
Re: Fwd: [sqlite] database is locked error on Mac OS X
TB [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] -
Re: Fwd: [sqlite] database is locked error on Mac OS X
On 3/10/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: TB [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: [sqlite] database is locked error with 3.3.13
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
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 TB [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
TB [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] -
[sqlite] database is locked error on Mac OS X
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
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
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
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 [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
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
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
[sqlite] Database is locked error in PHP via PDO despite setting timeout
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