Re: [sqlite] database is locked
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 sys/types.h
#include sys/stat.h
#ifdef AIX64
#include /usr/include/sys/flock.h
#endif
#include unistd.h
#include fcntl.h
#include stdio.h
#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( v_pending, 0, sizeof( v_pending));
v_pending.l_type= F_WRLCK;
v_pending.l_whence = SEEK_SET;
v_pending.l_start = PENDING_BYTE;
v_pending.l_len = 1;
v_pending.l_pid = 0;
memset( v_reserved, 0, sizeof( v_reserved));
v_reserved.l_type = F_WRLCK;
v_reserved.l_whence = SEEK_SET;
v_reserved.l_start = RESERVED_BYTE;
v_reserved.l_len= 1;
v_reserved.l_pid= 0;
memset( v_shared, 0, sizeof( v_shared));
v_shared.l_type = F_WRLCK;
v_shared.l_whence = SEEK_SET;
v_shared.l_start= SHARED_FIRST;
v_shared.l_len = SHARED_SIZE;
v_shared.l_pid = 0;
/* 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,v_pending);
/* check for a RESERVED lock */
fcntl(v_fd,F_GETLK,v_reserved);
/* check for a SHARED/EXCLUSIVE lock */
fcntl(v_fd,F_GETLK,v_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, none\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.orgmailto: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.edumailto: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
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 sys/types.h
#include sys/stat.h
#ifdef AIX64
#include /usr/include/sys/flock.h
#endif
#include unistd.h
#include fcntl.h
#include stdio.h
#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( v_pending, 0, sizeof( v_pending));
v_pending.l_type= F_WRLCK;
v_pending.l_whence = SEEK_SET;
v_pending.l_start = PENDING_BYTE;
v_pending.l_len = 1;
v_pending.l_pid = 0;
memset( v_reserved, 0, sizeof( v_reserved));
v_reserved.l_type = F_WRLCK;
v_reserved.l_whence = SEEK_SET;
v_reserved.l_start = RESERVED_BYTE;
v_reserved.l_len= 1;
v_reserved.l_pid= 0;
memset( v_shared, 0, sizeof( v_shared));
v_shared.l_type = F_WRLCK;
v_shared.l_whence = SEEK_SET;
v_shared.l_start= SHARED_FIRST;
v_shared.l_len = SHARED_SIZE;
v_shared.l_pid = 0;
/* 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,v_pending);
/* check for a RESERVED lock */
fcntl(v_fd,F_GETLK,v_reserved);
/* check for a SHARED/EXCLUSIVE lock */
fcntl(v_fd,F_GETLK,v_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, none\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.orgmailto: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.edumailto: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.orgmailto:sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
D. Richard Hipp
d...@sqlite.orgmailto:d...@sqlite.org
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
Re: [sqlite] database is locked
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
On 14 Jan 2015, at 5:30pm, Roman Fleysher roman.fleys...@einstein.yu.edu 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
[sqlite] database is locked
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? Thank you for your help, Roman ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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
Re: [sqlite] database is locked
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
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
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 for SQLITE_BUSY
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 previous read by process A. Process A now tries to write
Re: [sqlite] database is locked for SQLITE_BUSY
On 4 Dec 2014, at 5:36pm, Jonathan Moules j.mou...@hrwallingford.com 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
[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
Re: [sqlite] database is locked for SQLITE_BUSY
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 for SQLITE_BUSY
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 rsm...@rsweb.co.za 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
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
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
Re: [sqlite] database is locked for SQLITE_BUSY
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
On 3 Dec 2014, at 3:10pm, Hick Gunter h...@scigames.at 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
On Wed, Dec 3, 2014 at 10:18 AM, Simon Slavin slav...@bigfraud.org wrote: On 3 Dec 2014, at 3:10pm, Hick Gunter h...@scigames.at 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
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
On 3 Dec 2014, at 3:20pm, Richard Hipp d...@sqlite.org 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
[sqlite] Database is locked exceptions
Hello,
I have written a program in C# Visual Studio Winforms. In general the SQLite
database is working quite well.
However, lately we have been plagued with database locking errors:
Here is the exeption:
database is locked
database is locked
at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
at System.Data.SQLite.SQLiteDataReader.NextResult()
at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd,
CommandBehavior behave)
at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior
behavior)
at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior
behavior)
at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
at WindowsApplication1.Form1.logExceptionToTable()
at WindowsApplication1.Form1.MyExceptionHandler(Object sender,
ThreadExceptionEventArgs e)
at
System.Windows.Forms.Application.ThreadContext.OnThreadException(Exception
t)
at System.Windows.Forms.Control.WndProcException(Exception e)
at
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
System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32
reason, ApplicationContext context)
at
System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32
reason, ApplicationContext context)
at System.Windows.Forms.Application.RunDialog(Form form)
at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
at System.Windows.Forms.Form.ShowDialog()
at
WindowsApplication1.LabTestPatientForm.enterTestDataButton_Click(Object
sender, EventArgs e)
at System.Windows.Forms.Control.OnClick(EventArgs e)
at DevComponents.DotNetBar.ButtonX.OnClick(EventArgs e)
at System.Windows.Forms.Control.WmMouseUp(Message m,
MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message m)
at DevComponents.DotNetBar.ButtonX.WndProc(Message m)
at
System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message
m)
at
System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg,
IntPtr wparam, IntPtr lparam)
The database resides on a network on a Windows Server 2008 R2 system. The users
have full read/write
permissions to the database file.
The code that I'm using to access the database is:
OMConnection.sqConn = new SQLiteConnection(
URI=file:mydb.db; default timeout=10; Pooling=True; Max Pool Size=100;;
sqConn.Open();
string sql = SELECT +
* +
FROM APPOINTMENTTYPES;
SQLiteCommand cmd = OMConnection.sqConn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
SQLiteDataReader dr = cmd.ExecuteReader();
DataTable myTable = new DataTable();
myTable.Load(dr);
//put the categories into the
for (int rowIndex = 0; rowIndex myTable.Rows.Count; rowIndex++)
{
dataSet1.Tables[ReasonForVisit].Rows.Add();
dataSet1.Tables[ReasonForVisit].Rows[rowIndex][ReasonForVisitName] =
myTable.Rows[rowIndex][Name].ToString().Trim();
dataSet1.Tables[ReasonForVisit].Rows[rowIndex][Duration] =
myTable.Rows[rowIndex][Duration];
}
myTable.Dispose();
OMConnection.sqConn.Close();
OMConnection.sqConn.Dispose();
OMConnection.sqConn = null;
The thing is, the database locking errors are random and do not always occur. I
am using the
c# ADO provider, version 1.0.94.0 (version 3.8.6 of SQLite).
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?
Thanks
Mike
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database is locked exceptions
On 29 Oct 2014, at 4:13pm, Mike McWhinney elja...@sbcglobal.net 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 exceptions
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
[sqlite] Database is locked !
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
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
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 !
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 gets locked for other processes
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 sgb...@googlemail.com escreveu: 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. -- - 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
[sqlite] Database gets locked for other processes
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
Re: [sqlite] Database gets locked for other processes
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 free...@rakor-net.de 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 gets locked for other processes
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. -- - 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
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
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
[sqlite] database is locked with create
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. Regards, David J. Choi ___ 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
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
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
Re: [sqlite] database is locked with create
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 ; sqlitecreate 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
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
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
Re: [sqlite] database is locked with create
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
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
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
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 slav...@bigfraud.org 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
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
Re: [sqlite] Database is Locked
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 b...@ucw.cz 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
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 Hudecb...@ucw.cz
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
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.
2012/1/29 Stephen C pontia...@gmail.com
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 Hudecb...@ucw.cz
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.htmlhttp://www.sqlite.org/c3ref/busy_timeout.html).
The .NET api will be
called similar.
- Plan eventual migration to SQL Server or MySQL or something,
Re: [sqlite] Database is Locked
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
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
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
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
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
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 slav...@bigfraud.org 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
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
On Sun, 29 Jan 2012 17:11:35 +0200, Shahar Weinstein shahar...@gmail.com 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) michael.bla...@ngc.com 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
[sqlite] Database is Locked
Hi, 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. usualy there is no problem and , in the development enviroment I couldn't recreate the problem please help, if I won't manage to find a solution I would have to switch to MsAccess database in thie project, and maybe in future projects too, If I won't find the solution for this issue. thanks in advanced Shahar Weinstein. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database is Locked
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
Re: [sqlite] Database is Locked
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 b...@ucw.cz 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
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 b...@ucw.cz ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database is Locked
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] database is locked on WAL with large number of reader connections
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. --erik ___ 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
On Wed, Jan 25, 2012 at 8:01 PM, Erik Fears str...@strtok.net 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 on WAL with large number of reader connections
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 d...@sqlite.org wrote: On Wed, Jan 25, 2012 at 8:01 PM, Erik Fears str...@strtok.net 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
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 Hippd...@sqlite.org wrote: On Wed, Jan 25, 2012 at 8:01 PM, Erik Fearsstr...@strtok.net 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
[sqlite] Database is locked
I expect that this has been asked before but I cant find anything
useful via google.
I am updating about 20 rows in a table and setting a particular value
to a particular value. The first time I do this all is OK, if I try
again a few seconds later I get a database is locked error.
The code is (using UniDAC), x is passed as a value to the function
try
{
UniConnection1-StartTransaction();
for(int i=0; iCount; i++)
{
Tab-RecNo = i;
Tab-Edit();
Tab-FieldByName(cat)-AsInteger = x;
ThumbTab-Post();
}
}
__finally
{
UniConnection1-Commit();
Beep(1000,200);
}
I have added the Beep message to make sure that commit is returning
quickly, which it is doing
I am updating the same set of records, if I move on within the dataset
by count records and then update a new set of rows all works OK, if I
then move back to the previous set and update all is OK. The error
just happens when I trya nd update the same set of records on two
consecutive occasions
What can cause the dataabse to be locked in this way, are there any
sqliteisms I should be looking for.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] database is locked(5) too often - what am i doing wrong?
Greetings, I've been using sqlite-3.4.2 for a customer-only website for a few years now and the number of users has recently grown to the point where I'm now seeing too many database is locked(5) (i.e. busy) errors. When my code gets a database is locked(5) error, it sleeps for a random period between 0.25-0.5 seconds and tries again. It repeats this up to 50 times before giving up and I only see an error if all of the repeated attempts failed as well. I'm now seeing this error a lot. My use of sqlite seems unable to cope with more than about 30-50 concurrent updaters. I'm not sure of the exact number. Is that typical? Bear in mind that every use of this website is recorded in the database for auditing purposes so all users are updaters. 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? I expect I'll have to migrate it to postgres but I thought I'd ask for opinions here first. Cheers, raf ___ 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?
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
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
Re: [sqlite] Database is locked
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=restaurantsform=MFESRPpubl=WLHMTAGcrea=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
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
[sqlite] Database is locked
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);
while (Keys-Execute(Command)==SQLITE_BUSY) {
#if defined(__unix)
sleep(dRETRYDELAY);
#else
Sleep(dRETRYDELAY*1000);
#endif
}
We use SQL Server 2005 Extended Stored Procedures and User Defined functions
in the following manner in order to insert into the SQLite table using the
above C++ code:
UPDATE MyTestDatabase SET
MatchKey = master.dbo.mdMUIncrementalBuildKeyEx(@Incremental, @Matchcode,
Column12, Column53, Column52, Address, NULL, NULL, NULL, NULL, NULL, NULL),
@RetVal = master.dbo.mdMUIncrementalAddRecord(@Incremental),
Status = master.dbo.mdMUIncrementalGetStatusCode(@Incremental),
DupeGroup = master.dbo.mdMUIncrementalGetDupeGroup(@Incremental)
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? Is it a SQL Server 2005 problem or does it have anything to
do with Sqlite? In particular, we are wondering why we get the SQLite error
message database is locked when we are running concurrent SQL Server client
processes? When we run just one SQL Server client process, everything works
fine and we get no SQLite error messages. Thank you.
_
Bing brings you maps, menus, and reviews organized in one place.
http://www.bing.com/search?q=restaurantsform=MFESRPpubl=WLHMTAGcrea=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
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
[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
[sqlite] database is locked and is malformed ANSWER
This is an answer to the post of Dr. Hipp to my question. I tried several times to send the mail as Re: [sqlite] database is locked and is malformed but it did not work. So I have to create a new thread. Hello Dr. Hipp, thank you for your response. First off all I want to apologize for my entry in the ticket on sqlite.org, feel free to delete it. I will also look for an other possibility to format my emails, we use a Lotus Notes Client in the company and I don`t have an influence on footers, perhaps I find something about the sending format. Should I add some html-tags for line feed? We don`t use the PRAGMA synchronous=off anymore. I read the information about the atomic commit behaviour when I started programing 2 or 3 years ago and now I had problems, in studied it in detail again. As far as I can judge it, we nearly fit all assumtions, but 1. We use a SiliconDrive SSD-D04G-3500 Flash Drive as Harddisk 2. We had problems in the past that fsync() was very slow (that`s why we had to use the pragma command) The compilation of sqlite3 is done by our qnx expert, but I can get all settings and arrange changes very fast. When I logged on to the corrupted machine there was the normal data.s3db with around 200k and a data.s3db-journal file. I tried to use the database, but only got errors. Than I tried to .dump it, but this also did not work. Than I, perhaps unfortunately, deleted the journal file and dumped again, with the result, that the new data.s3db was only 80k big and the most important table was completely empty. Afterwards I told the customer to restore a backup which is one week old (absolutely healthy). I still could get the corrupted database, but after all I read it want help without the journal file. I did that all under extreme time pressure, the machine was standing the whole sunday and the customer was very angry. So I did`t try something with the journal file, I can´t say something about the size and the readability. Next time I know better. To the situation on the machine: I tried to do most ot the inserts to the database by a single thread. All other programs send message to that database manager and it makes the inserts. That is true for about 95% off all inserts. Now, after thinking two days about the crash and reading your advices, I believe that the crash had nothing to do with concurrent inserts. On power off I may get many errors from devices of the machine (most devices loose power earlier than the controll unit). So the possibility of writing exact at the time when the controll unit losses power is very high. What I`m going to do next: 1. I will try to make the behaviour in case off power loss saver. There must be a way to check the situation before starting an insert. 2. I will try to make all Inserts that belong together as one atomic commit. Is it enought to write serveral Inserts in one sqlite3_exec() call or must I do such a procedure with manual steps ? 3. I will try the PRAGMA journal_mode=PERSIST option to increase the speed of inserts 4. I will test the sqlite3_busy_timeout() command as I`m not happy with my current solution Best regards / Mit freundlichen Grüssen Stefan Breitholz --- Staeubli GmbH - Theodor-Schmidt-Str. 19 DE - 95448 Bayreuth Phone: +49(0)921/883-126 Fax: +49(0)921/883-58126 mailto:s.breith...@staubli.com http://www.staubli.com Registered under HRB 175 - Local Court Bayreuth - Managing Director: Karl Kirschner --- This e-mail and any attachment (the 'message') are confidential and privileged and intended solely for the person or the entity to which it is adressed. If you have received it in error, please advise the sender by return e-mail and delete it immediately. Any use not in accordance with its purpose, any dissemination or reproduction, either whole or partial, by entities other than the intended recipient is strictly prohibited. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] database is locked or is malformed
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).
On our machines there are several programs that access the databases (we
use 4 dbs) and also the programs have all several threads. This worked for
over a year now on 10 machines. I only saw two times a database that was
corrupted. Always messages like this: On page 6928 at right child: 2nd
reference to page 7003 Page 3805 is never used. But that never caused a
totally corruption of the database. I added a program to run the
integrity_check and performing a .dump | if a problem was found.
Today I had the first total crash. No program could start at all. All
programs (written in c) got database locked (11) and ended. I was lucky to
have a telnet connection to the customer, so I run an integrity_check and
got a long list with errors. I tried to .dump | but the most important
table was so much damaged, that it was empty after the .dump. Also there
was the data.s3db-journal file, but no connection to the database was
opened.
I dont have an idea how to find the reason for the problem. 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. 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* handle for the database.
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, errmsg);
if((returnCode == SQLITE_BUSY) || (errmsg
!= NULL returnCode != SQLITE_ERROR)){
usleep(2 +
(rand()%1));//several threads wait different times
counter++;
}
}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 a insert function like that?
Should every thread have an own sqlite* connetion?
Should a program hold the sqlite* connection opened or should it close
after every insert and open again?
Please give me some advice. Stefan
Best regards / Mit freundlichen Grüssen
Stefan Breitholz
---
Staeubli GmbH - Theodor-Schmidt-Str. 19
DE - 95448 Bayreuth
Phone: +49(0)921/883-126 Fax: +49(0)921/883-58126
mailto:s.breith...@staubli.com
http://www.staubli.com
Registered under HRB 175 - Local Court Bayreuth - Managing Director: Karl
Kirschner
---
This e-mail and any attachment (the 'message') are confidential and privileged
and intended solely for the person or the entity to which it is adressed. If
you have received it in error, please advise the sender by return e-mail and
delete it immediately. Any use not in accordance with its purpose, any
dissemination or reproduction, either
[sqlite] database is locked and is malformed
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).
On our machines there are several programs that access the databases (we
use 4 dbs) and also the programs have all several threads. This worked for
over a year now on 10 machines. I only saw two times a database that was
corrupted. Always messages like this: On page 6928 at right child: 2nd
reference to page 7003 Page 3805 is never used. But that never caused a
totally corruption of the database. I added a program to run the
integrity_check and performing a .dump | if a problem was found.
Today I had the first total crash. No program could start at all. All
programs (written in c) got database locked (11) and ended. I was lucky to
have a telnet connection to the customer, so I run an integrity_check and
got a long list with errors. I tried to .dump | but the most important
table was so much damaged, that it was empty after the .dump. Also there
was the data.s3db-journal file, but no connection to the database was
opened.
I don`t have an idea how to find the reason for the problem. 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. 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, errmsg);
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?
Should every thread have an own sqlite* connetion?
Should a program hold the sqlite* connection opened or should it close
after every insert and open again?
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. 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 problems?
Please give me some advice.
Best regards / Mit
Re: [sqlite] database is locked and is malformed
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, errmsg);
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 problems?
ATTACHing in a different order should work fine.
Please give me some advice.
D. Richard Hipp
d...@hwaci.com
[sqlite] database is locked
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, db);
sqlite3_prepare(db, SELECT ip_address FROM ips WHERE ip_owner='',
plineInfo, 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
Re: [sqlite] database is locked
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, db);
sqlite3_prepare(db, SELECT ip_address FROM ips WHERE
ip_owner='',
plineInfo, 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
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
[sqlite] Database is Locked
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. Thanks, RobR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database is Locked
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 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
Re: [sqlite] Database is Locked
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
Below is the call to open a new database. This comes back successful. sqlite3_open16(pchFileName, m_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, m_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
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 always locked
[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
[sqlite] database always locked
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(), db);
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, zErrMsg);
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; iargc; 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
Re: [sqlite] database always locked
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(), db);
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, zErrMsg);
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; iargc; 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
[sqlite] database is locked on clean install, empty database?
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 sqlite .databases Error: database is locked sqlite .quit $ ls -l test.db -rw-r--r-- 1 srhea srhea 0 Mar 3 12:38 test.db Searching the web for database is locked pulls up a lot of hits, but none this simple. What am I doing wrong? Thanks in advance, 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?
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 on clean install, empty database?
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?
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
