Re: [sqlite] database locked in PHP
Maybe you can use the (linux, c) code I posted recently to determine which process/thread is locking the database file. -Ursprüngliche Nachricht- Von: Lev [mailto:leventel...@gmail.com] Gesendet: Sonntag, 25. Jänner 2015 01:36 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] database locked in PHP On Sat, 24 Jan 2015 20:59:22 + Simon Slavin slavins-drl2ohjjk6tg9huczpv...@public.gmane.org wrote: and set it to 6 (60 seconds) or so. Okay, I try that, but I still don't understand how can a single threaded application get a locked error. Levente ___ 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 locked in PHP
On 01/25/2015 07:35 AM, Lev wrote: On Sat, 24 Jan 2015 20:59:22 + Simon Slavin slavins-drl2ohjjk6tg9huczpv...@public.gmane.org wrote: and set it to 6 (60 seconds) or so. Okay, I try that, but I still don't understand how can a single threaded application get a locked error. Was the error message database is locked or database table is locked? When you say single threaded, are you also implying uses a single connection? Is the statement that is hitting the SQLITE_LOCKED a DROP TABLE or DROP INDEX statement? If so, do you also have active statements associated with the same connection? Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] database locked in PHP
Hi list, I'm currently working on a www frontend that uses SQLite. I sometimes get a database locked error when I access the database by calling the execute() call. This is on PHP. Reading the document http://www.sqlite.org/rescode.html#locked I learned that locked error occurs only on the same database connection or when shared cache is used. I open the database with $db = new SQLite3($db_filename); then $stmt = @$db-prepare($query); $result = $stmt-execute(); Does these function calls enable shared cache? Or I can't see why I get locking error. Could you please help. Thanks, Levente ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database locked in PHP
On Sat, 24 Jan 2015 20:59:22 + Simon Slavin slavins-drl2ohjjk6tg9huczpv...@public.gmane.org wrote: and set it to 6 (60 seconds) or so. Okay, I try that, but I still don't understand how can a single threaded application get a locked error. Levente ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database locked in PHP
On 24 Jan 2015, at 8:13pm, Lev leventel...@gmail.com wrote: I sometimes get a database locked error when I access the database by calling the execute() call. This is on PHP. Have you set a timeout ? Immediately after opening the connection use http://php.net/manual/en/sqlite3.busytimeout.php and set it to 6 (60 seconds) or so. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Database Locked Error
Dear All, Can you please help me in solving below error.When I am compiling sqlite 3.6.12 on UCLINUX. Error : database locked or Can you please assist me in executing sqlite 3.6.12 on UCLINUX. -- Thanks Regards, Mohammed Aijas +91-9642933393 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Database locked after read
I have found that when using SQLite under Windows if you execute an
update statement and then a select statement, the database is locked
until the select statement is finished. How can I stop this from happening?
Here is a simple test program that simulates this situation. If you run
the program you will get the message Update executed without
problems. Without closing this message you run the program again you
will get Table is locked??.
void DisplayMessage(_TCHAR* message)
{
MessageBox(NULL, message, Test Lock, 0);
}
void Check(int status, char* message)
{
if (status != 0 status != SQLITE_ROW status != SQLITE_DONE)
DisplayMessage(message);
}
int _tmain(int argc, _TCHAR* argv[])
{
sqlite3* connection;
bool needCreate = sqlite3_open_v2(test.sqlite, connection,
SQLITE_OPEN_READWRITE, NULL) != SQLITE_OK;
if (!needCreate || sqlite3_open_v2(test.sqlite, connection,
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL) == SQLITE_OK)
{
if (needCreate)
{
Check(sqlite3_exec(connection, CREATE TABLE TESTFILE (id LONG,
data CHAR(30)), NULL, NULL, NULL), Create Table);
Check(sqlite3_exec(connection, CREATE TABLE TESTFILE2 (id LONG,
data CHAR(30)), NULL, NULL, NULL), Create Table 2);
Check(sqlite3_exec(connection, INSERT INTO TESTFILE VALUES (1,
'A'), NULL, NULL, NULL), Insert 1);
Check(sqlite3_exec(connection, INSERT INTO TESTFILE VALUES (2,
'B'), NULL, NULL, NULL), Insert 2);
Check(sqlite3_exec(connection, INSERT INTO TESTFILE2 VALUES (1,
'A'), NULL, NULL, NULL), Insert 1);
Check(sqlite3_exec(connection, INSERT INTO TESTFILE2 VALUES (2,
'B'), NULL, NULL, NULL), Insert 2);
}
sqlite3_stmt* statement1;
Check(sqlite3_prepare_v2(connection, UPDATE TESTFILE SET data='A'
WHERE id=1, -1, statement1, NULL), Update);
int status = sqlite3_step(statement1);
sqlite3_finalize(statement1);
sqlite3_stmt* statement2;
Check(sqlite3_prepare_v2(connection, SELECT ID,data FROM
TESTFILE2, -1, statement2, NULL), SELECT);
Check(sqlite3_step(statement2), stepping Select);
if (status == SQLITE_DONE)
DisplayMessage(Update executed without problems); // This
simulates a select statement that takes a long time to execute
else if (status == SQLITE_BUSY)
DisplayMessage(Table is locked??);
else
DisplayMessage(There was a problem);
sqlite3_finalize(statement2);
sqlite3_close(connection);
}
return 0;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked after read
I have found that when using SQLite under Windows if you execute an update
statement and then a select statement, the database is locked until the
select statement is finished. How can I stop this from happening?
Note that you don't have to execute UPDATE before the SELECT to
reproduce that. SELECT places shared lock on the database. UPDATE
cannot proceed while any shared locks are active on the database. To
avoid such situation you can switch your database to the WAL journal
mode (http://www.sqlite.org/wal.html).
Pavel
On Sat, Dec 1, 2012 at 2:02 PM, Scott Ferrett
sc...@ferrettconsulting.com wrote:
I have found that when using SQLite under Windows if you execute an update
statement and then a select statement, the database is locked until the
select statement is finished. How can I stop this from happening?
Here is a simple test program that simulates this situation. If you run the
program you will get the message Update executed without problems.
Without closing this message you run the program again you will get Table
is locked??.
void DisplayMessage(_TCHAR* message)
{
MessageBox(NULL, message, Test Lock, 0);
}
void Check(int status, char* message)
{
if (status != 0 status != SQLITE_ROW status != SQLITE_DONE)
DisplayMessage(message);
}
int _tmain(int argc, _TCHAR* argv[])
{
sqlite3* connection;
bool needCreate = sqlite3_open_v2(test.sqlite, connection,
SQLITE_OPEN_READWRITE, NULL) != SQLITE_OK;
if (!needCreate || sqlite3_open_v2(test.sqlite, connection,
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL) == SQLITE_OK)
{
if (needCreate)
{
Check(sqlite3_exec(connection, CREATE TABLE TESTFILE (id LONG, data
CHAR(30)), NULL, NULL, NULL), Create Table);
Check(sqlite3_exec(connection, CREATE TABLE TESTFILE2 (id LONG, data
CHAR(30)), NULL, NULL, NULL), Create Table 2);
Check(sqlite3_exec(connection, INSERT INTO TESTFILE VALUES (1, 'A'),
NULL, NULL, NULL), Insert 1);
Check(sqlite3_exec(connection, INSERT INTO TESTFILE VALUES (2, 'B'),
NULL, NULL, NULL), Insert 2);
Check(sqlite3_exec(connection, INSERT INTO TESTFILE2 VALUES (1,
'A'), NULL, NULL, NULL), Insert 1);
Check(sqlite3_exec(connection, INSERT INTO TESTFILE2 VALUES (2,
'B'), NULL, NULL, NULL), Insert 2);
}
sqlite3_stmt* statement1;
Check(sqlite3_prepare_v2(connection, UPDATE TESTFILE SET data='A' WHERE
id=1, -1, statement1, NULL), Update);
int status = sqlite3_step(statement1);
sqlite3_finalize(statement1);
sqlite3_stmt* statement2;
Check(sqlite3_prepare_v2(connection, SELECT ID,data FROM TESTFILE2,
-1, statement2, NULL), SELECT);
Check(sqlite3_step(statement2), stepping Select);
if (status == SQLITE_DONE)
DisplayMessage(Update executed without problems); // This simulates
a select statement that takes a long time to execute
else if (status == SQLITE_BUSY)
DisplayMessage(Table is locked??);
else
DisplayMessage(There was a problem);
sqlite3_finalize(statement2);
sqlite3_close(connection);
}
return 0;
}
___
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 locked in multi process scenario
I have a 'database is locked' issued which can be reproduced as follows. I have two applications opening the database in WAL mode. The threading mode is SERIALIZED. Environment is PC/Linux. Step1: Launch App1 followed by App 2 ( same executables) Step 2: App1 Prepares a SELECT statement and executes the statement. Step 3: The statement is not reset and finalized. Step 4: App1 wait on keyboard input Step 5 : App2 prepares a statement to update a few records. The statement is executed, reset and finalized Step 6: Go back to App1, prepare an UPDATE/INSERT statement and execute it. Result: 'database is locked' error is thrown by App 1 App2 should have released the exclusive lock after it has reset and finalized the statement.Hence App1 should not have encountered SQLITE_BUSY when writing. What could be the cause ? Kind Regards, Sreekumar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked in multi process scenario
On Fri, Feb 10, 2012 at 6:47 AM, Sreekumar TP sreekumar...@gmail.comwrote: I have a 'database is locked' issued which can be reproduced as follows. I have two applications opening the database in WAL mode. The threading mode is SERIALIZED. Environment is PC/Linux. Step1: Launch App1 followed by App 2 ( same executables) Step 2: App1 Prepares a SELECT statement and executes the statement. Step 3: The statement is not reset and finalized. Step 4: App1 wait on keyboard input Step 5 : App2 prepares a statement to update a few records. The statement is executed, reset and finalized Step 6: Go back to App1, prepare an UPDATE/INSERT statement and execute it. At this point, App1 is still holding a read transaction open that points to an image of the database from an earlier point in time. App1 is not allowed to write to the database because that would cause the database content to fork. Any database connection must be in a transaction that shows the very latest content of the database in order for it to write without forking. Result: 'database is locked' error is thrown by App 1 App2 should have released the exclusive lock after it has reset and finalized the statement.Hence App1 should not have encountered SQLITE_BUSY when writing. What could be the cause ? Kind Regards, Sreekumar ___ 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 locked in multi process scenario
How is this different from two threads each with a db connection in a single process? Moreover the journal mode is WAL. Hence the writer should be able to append changes to the WAL file as there are no other write transaction. Sreekumar On Feb 10, 2012 6:22 PM, Richard Hipp d...@sqlite.org wrote: On Fri, Feb 10, 2012 at 6:47 AM, Sreekumar TP sreekumar...@gmail.com wrote: I have a 'database is locked' issued which can be reproduced as follows. I have two applications opening the database in WAL mode. The threading mode is SERIALIZED. Environment is PC/Linux. Step1: Launch App1 followed by App 2 ( same executables) Step 2: App1 Prepares a SELECT statement and executes the statement. Step 3: The statement is not reset and finalized. Step 4: App1 wait on keyboard input Step 5 : App2 prepares a statement to update a few records. The statement is executed, reset and finalized Step 6: Go back to App1, prepare an UPDATE/INSERT statement and execute it. At this point, App1 is still holding a read transaction open that points to an image of the database from an earlier point in time. App1 is not allowed to write to the database because that would cause the database content to fork. Any database connection must be in a transaction that shows the very latest content of the database in order for it to write without forking. Result: 'database is locked' error is thrown by App 1 App2 should have released the exclusive lock after it has reset and finalized the statement.Hence App1 should not have encountered SQLITE_BUSY when writing. What could be the cause ? Kind Regards, Sreekumar ___ 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 locked in multi process scenario
On 10 Feb 2012, at 11:47am, Sreekumar TP wrote: I have a 'database is locked' issued which can be reproduced as follows. I have two applications opening the database in WAL mode. The threading mode is SERIALIZED. Environment is PC/Linux. Step1: Launch App1 followed by App 2 ( same executables) Step 2: App1 Prepares a SELECT statement and executes the statement. Step 3: The statement is not reset and finalized. Step 4: App1 wait on keyboard input Step 5 : App2 prepares a statement to update a few records. The statement is executed, reset and finalized Step 6: Go back to App1, prepare an UPDATE/INSERT statement and execute it. Sreekumar, imagine what would happen if two users both got to step 4 at the same point at the same time. There are no circumstances under which either of them could proceed. Whichever of them tried to proceed would invalidate the SELECT that the other one was still in the middle of. When writing your software, imagine that the database is locked from the 'execute' until you have done the 'finalize'. You want to do the finalize as soon as possible, to unlock the database for other users. You can't wait for a user to type something: their phone might ring or something. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked in multi process scenario
Hi Simon, well, the 'wait' is a simulation of what happens in the real code. The error is fatal to the application as it never ever recovers from it even though the writer has finalized and terminated. Sreekumar On Feb 10, 2012 6:57 PM, Simon Slavin slav...@bigfraud.org wrote: On 10 Feb 2012, at 11:47am, Sreekumar TP wrote: I have a 'database is locked' issued which can be reproduced as follows. I have two applications opening the database in WAL mode. The threading mode is SERIALIZED. Environment is PC/Linux. Step1: Launch App1 followed by App 2 ( same executables) Step 2: App1 Prepares a SELECT statement and executes the statement. Step 3: The statement is not reset and finalized. Step 4: App1 wait on keyboard input Step 5 : App2 prepares a statement to update a few records. The statement is executed, reset and finalized Step 6: Go back to App1, prepare an UPDATE/INSERT statement and execute it. Sreekumar, imagine what would happen if two users both got to step 4 at the same point at the same time. There are no circumstances under which either of them could proceed. Whichever of them tried to proceed would invalidate the SELECT that the other one was still in the middle of. When writing your software, imagine that the database is locked from the 'execute' until you have done the 'finalize'. You want to do the finalize as soon as possible, to unlock the database for other users. You can't wait for a user to type something: their phone might ring or something. 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 locked in multi process scenario
On 10 Feb 2012, at 1:32pm, Sreekumar TP wrote: well, the 'wait' is a simulation of what happens in the real code. The error is fatal to the application as it never ever recovers from it even though the writer has finalized and terminated. In a multi-process environment I recommend that you do not pause for such a long time between the first _step() and the _reset() or _finalize(). You can _bind() a statement then wait a long time to execute it, but once you have done your first _step() you want to get through the data and release the database for other processes. If you still have the database locked and another process tries to modify it, one process or the other will have to deal with a BUSY, or a LOCKED, or something like that. In your own setup, it turns out to be process 1. But a slightly different setup would make process 2 see a BUSY instead. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked in multi process scenario
In the real code, there is no sleep/wait or pause. It so happens that the write of the app2 is scheduled in between. What you are suggesting is that at any point of time only one process can have a transaction open in a database? Sreekumar On Feb 10, 2012 7:12 PM, Simon Slavin slav...@bigfraud.org wrote: On 10 Feb 2012, at 1:32pm, Sreekumar TP wrote: well, the 'wait' is a simulation of what happens in the real code. The error is fatal to the application as it never ever recovers from it even though the writer has finalized and terminated. In a multi-process environment I recommend that you do not pause for such a long time between the first _step() and the _reset() or _finalize(). You can _bind() a statement then wait a long time to execute it, but once you have done your first _step() you want to get through the data and release the database for other processes. If you still have the database locked and another process tries to modify it, one process or the other will have to deal with a BUSY, or a LOCKED, or something like that. In your own setup, it turns out to be process 1. But a slightly different setup would make process 2 see a BUSY instead. 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 locked in multi process scenario
On 10 Feb 2012, at 1:52pm, Sreekumar TP wrote: In the real code, there is no sleep/wait or pause. It so happens that the write of the app2 is scheduled in between. What you are suggesting is that at any point of time only one process can have a transaction open in a database? I understand your question but I don't know enough of the internals of SQLite to answer it. Also, I'm not sure how the time between the first _step() and the step which returns 'no more data' resembles a transaction. I hope someone who knows more than I do will contribute. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked in multi process scenario
I'm not sure I'm even following how this scenario can happen. Doesn't App1 have a Shared lock on the DB? Doesn't App2 require an Exclusive lock before it can update something? When given the initial scenario, I thought that Step 5 would block waiting for App1 to finalize. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Friday, February 10, 2012 8:55 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Database locked in multi process scenario On 10 Feb 2012, at 1:52pm, Sreekumar TP wrote: In the real code, there is no sleep/wait or pause. It so happens that the write of the app2 is scheduled in between. What you are suggesting is that at any point of time only one process can have a transaction open in a database? I understand your question but I don't know enough of the internals of SQLite to answer it. Also, I'm not sure how the time between the first _step() and the step which returns 'no more data' resembles a transaction. I hope someone who knows more than I do will contribute. 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 locked in multi process scenario
Isn't it almost a requirement of a transaction that only one be open at a time in a database? If there could be more than one transaction, then transaction 1 might start, transaction 2 starts, transaction 1 fails, transaction 1 is rolled back, and what happens to transaction 2? One could imagine one transaction working an table 1 and a second working on table 2 which has no connection, but then someone comes along and adds a trigger to table 1 that updates table 2. Now we have two simultaneous independent transactions working on table 2. RobR, who has been struggling for months with a program that might open the same SQLite file at the same time from two points in the program, and who has realized that the program is not well designed. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Sreekumar TP Sent: Friday, February 10, 2012 8:52 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Database locked in multi process scenario In the real code, there is no sleep/wait or pause. It so happens that the write of the app2 is scheduled in between. What you are suggesting is that at any point of time only one process can have a transaction open in a database? Sreekumar On Feb 10, 2012 7:12 PM, Simon Slavin slav...@bigfraud.org wrote: On 10 Feb 2012, at 1:32pm, Sreekumar TP wrote: well, the 'wait' is a simulation of what happens in the real code. The error is fatal to the application as it never ever recovers from it even though the writer has finalized and terminated. In a multi-process environment I recommend that you do not pause for such a long time between the first _step() and the _reset() or _finalize(). You can _bind() a statement then wait a long time to execute it, but once you have done your first _step() you want to get through the data and release the database for other processes. If you still have the database locked and another process tries to modify it, one process or the other will have to deal with a BUSY, or a LOCKED, or something like that. In your own setup, it turns out to be process 1. But a slightly different setup would make process 2 see a BUSY instead. 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 locked in multi process scenario
On Fri, Feb 10, 2012 at 9:01 AM, Marc L. Allen mlal...@outsitenetworks.comwrote: I'm not sure I'm even following how this scenario can happen. Doesn't App1 have a Shared lock on the DB? Doesn't App2 require an Exclusive lock before it can update something? The OP is running in WAL mode. Different rules apply. In WAL mode, you can have multiple simultaneous readers concurrently with a single writer. And the readers all see (possibly different) snapshots of the database from the point in time where their read transaction was first started. When given the initial scenario, I thought that Step 5 would block waiting for App1 to finalize. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Friday, February 10, 2012 8:55 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Database locked in multi process scenario On 10 Feb 2012, at 1:52pm, Sreekumar TP wrote: In the real code, there is no sleep/wait or pause. It so happens that the write of the app2 is scheduled in between. What you are suggesting is that at any point of time only one process can have a transaction open in a database? I understand your question but I don't know enough of the internals of SQLite to answer it. Also, I'm not sure how the time between the first _step() and the step which returns 'no more data' resembles a transaction. I hope someone who knows more than I do will contribute. 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 -- 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 locked in multi process scenario
On Fri, Feb 10, 2012 at 9:05 AM, Rob Richardson rdrichard...@rad-con.comwrote: Isn't it almost a requirement of a transaction that only one be open at a time in a database? If there could be more than one transaction, then transaction 1 might start, transaction 2 starts, transaction 1 fails, transaction 1 is rolled back, and what happens to transaction 2? One could imagine one transaction working an table 1 and a second working on table 2 which has no connection, but then someone comes along and adds a trigger to table 1 that updates table 2. Now we have two simultaneous independent transactions working on table 2. SQLite supports only SERIALIZABLE transaction semantics. That means the end result of the database is as if the various transactions had occurred in a strictly linear sequence. But SQLite does allow multiple simultaneous transactions to be in play, as long as no more than one of them is a write transaction. When a read transaction begins, it sees a snapshot of the database from the moment in time when the transaction started. In change that occur to the database file from other database connections are invisible to that transaction. The OPs problem is that he has a old read transaction open which is looking at an historical snapshot of the database, that does not contain the latest changes to the database. Then he tries to promote that read transaction to a write transaction. But that is not allowed, because doing so would fork the history of the database file. The result might not be serializable. Before you can write, you have to first be looking at the most up-to-date copy of the database. RobR, who has been struggling for months with a program that might open the same SQLite file at the same time from two points in the program, and who has realized that the program is not well designed. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto: sqlite-users-boun...@sqlite.org] On Behalf Of Sreekumar TP Sent: Friday, February 10, 2012 8:52 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Database locked in multi process scenario In the real code, there is no sleep/wait or pause. It so happens that the write of the app2 is scheduled in between. What you are suggesting is that at any point of time only one process can have a transaction open in a database? Sreekumar On Feb 10, 2012 7:12 PM, Simon Slavin slav...@bigfraud.org wrote: On 10 Feb 2012, at 1:32pm, Sreekumar TP wrote: well, the 'wait' is a simulation of what happens in the real code. The error is fatal to the application as it never ever recovers from it even though the writer has finalized and terminated. In a multi-process environment I recommend that you do not pause for such a long time between the first _step() and the _reset() or _finalize(). You can _bind() a statement then wait a long time to execute it, but once you have done your first _step() you want to get through the data and release the database for other processes. If you still have the database locked and another process tries to modify it, one process or the other will have to deal with a BUSY, or a LOCKED, or something like that. In your own setup, it turns out to be process 1. But a slightly different setup would make process 2 see a BUSY instead. 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 -- 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 locked in multi process scenario
So, you're assuming the OP actually started a transaction? Because, otherwise, isn't the SELECT in step 2 and the UPDATE in step 5 separate transactions? If there is a BEGIN in there somewhere, we're talking about: App1: BEGIN SELECT UPDATE .. App2BEGIN UPDATE ... Right? And you're saying that this causes a problem if App2 gets in between App1's SELECT and UPDATE? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Friday, February 10, 2012 9:13 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Database locked in multi process scenario On Fri, Feb 10, 2012 at 9:05 AM, Rob Richardson RDRichardson@rad- con.comwrote: Isn't it almost a requirement of a transaction that only one be open at a time in a database? If there could be more than one transaction, then transaction 1 might start, transaction 2 starts, transaction 1 fails, transaction 1 is rolled back, and what happens to transaction 2? One could imagine one transaction working an table 1 and a second working on table 2 which has no connection, but then someone comes along and adds a trigger to table 1 that updates table 2. Now we have two simultaneous independent transactions working on table 2. SQLite supports only SERIALIZABLE transaction semantics. That means the end result of the database is as if the various transactions had occurred in a strictly linear sequence. But SQLite does allow multiple simultaneous transactions to be in play, as long as no more than one of them is a write transaction. When a read transaction begins, it sees a snapshot of the database from the moment in time when the transaction started. In change that occur to the database file from other database connections are invisible to that transaction. The OPs problem is that he has a old read transaction open which is looking at an historical snapshot of the database, that does not contain the latest changes to the database. Then he tries to promote that read transaction to a write transaction. But that is not allowed, because doing so would fork the history of the database file. The result might not be serializable. Before you can write, you have to first be looking at the most up-to-date copy of the database. RobR, who has been struggling for months with a program that might open the same SQLite file at the same time from two points in the program, and who has realized that the program is not well designed. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto: sqlite-users-boun...@sqlite.org] On Behalf Of Sreekumar TP Sent: Friday, February 10, 2012 8:52 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Database locked in multi process scenario In the real code, there is no sleep/wait or pause. It so happens that the write of the app2 is scheduled in between. What you are suggesting is that at any point of time only one process can have a transaction open in a database? Sreekumar On Feb 10, 2012 7:12 PM, Simon Slavin slav...@bigfraud.org wrote: On 10 Feb 2012, at 1:32pm, Sreekumar TP wrote: well, the 'wait' is a simulation of what happens in the real code. The error is fatal to the application as it never ever recovers from it even though the writer has finalized and terminated. In a multi-process environment I recommend that you do not pause for such a long time between the first _step() and the _reset() or _finalize(). You can _bind() a statement then wait a long time to execute it, but once you have done your first _step() you want to get through the data and release the database for other processes. If you still have the database locked and another process tries to modify it, one process or the other will have to deal with a BUSY, or a LOCKED, or something like that. In your own setup, it turns out to be process 1. But a slightly different setup would make process 2 see a BUSY instead. 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 -- 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
Re: [sqlite] Database locked in multi process scenario
On Fri, Feb 10, 2012 at 9:19 AM, Marc L. Allen mlal...@outsitenetworks.comwrote: So, you're assuming the OP actually started a transaction? Because, otherwise, isn't the SELECT in step 2 and the UPDATE in step 5 separate transactions? The OP said Step 3: The statement is not reset or finalized. That doesn't guarantee that the automatic read transaction that was started by the statement is still open, but it is pretty good hint. Remember, every statement runs within a transaction. Otherwise, the information coming out of the SELECT at the beginning might be incompatible with information that comes out at the end, if another connection modified the database while the select was running. It is not necessary to explicitly start a transaction with BEGIN in order to be in a transaction. If you don't manually do BEGIN, then BEGIN ... COMMIT is automatically inserted around each SQL statement you run. If there is a BEGIN in there somewhere, we're talking about: App1: BEGIN SELECT UPDATE .. App2BEGIN UPDATE ... Right? And you're saying that this causes a problem if App2 gets in between App1's SELECT and UPDATE? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Friday, February 10, 2012 9:13 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Database locked in multi process scenario On Fri, Feb 10, 2012 at 9:05 AM, Rob Richardson RDRichardson@rad- con.comwrote: Isn't it almost a requirement of a transaction that only one be open at a time in a database? If there could be more than one transaction, then transaction 1 might start, transaction 2 starts, transaction 1 fails, transaction 1 is rolled back, and what happens to transaction 2? One could imagine one transaction working an table 1 and a second working on table 2 which has no connection, but then someone comes along and adds a trigger to table 1 that updates table 2. Now we have two simultaneous independent transactions working on table 2. SQLite supports only SERIALIZABLE transaction semantics. That means the end result of the database is as if the various transactions had occurred in a strictly linear sequence. But SQLite does allow multiple simultaneous transactions to be in play, as long as no more than one of them is a write transaction. When a read transaction begins, it sees a snapshot of the database from the moment in time when the transaction started. In change that occur to the database file from other database connections are invisible to that transaction. The OPs problem is that he has a old read transaction open which is looking at an historical snapshot of the database, that does not contain the latest changes to the database. Then he tries to promote that read transaction to a write transaction. But that is not allowed, because doing so would fork the history of the database file. The result might not be serializable. Before you can write, you have to first be looking at the most up-to-date copy of the database. RobR, who has been struggling for months with a program that might open the same SQLite file at the same time from two points in the program, and who has realized that the program is not well designed. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto: sqlite-users-boun...@sqlite.org] On Behalf Of Sreekumar TP Sent: Friday, February 10, 2012 8:52 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Database locked in multi process scenario In the real code, there is no sleep/wait or pause. It so happens that the write of the app2 is scheduled in between. What you are suggesting is that at any point of time only one process can have a transaction open in a database? Sreekumar On Feb 10, 2012 7:12 PM, Simon Slavin slav...@bigfraud.org wrote: On 10 Feb 2012, at 1:32pm, Sreekumar TP wrote: well, the 'wait' is a simulation of what happens in the real code. The error is fatal to the application as it never ever recovers from it even though the writer has finalized and terminated. In a multi-process environment I recommend that you do not pause for such a long time between the first _step() and the _reset() or _finalize(). You can _bind() a statement then wait a long time to execute it, but once you have done your first _step() you want to get through the data and release the database for other processes. If you still have the database locked and another process tries to modify it, one process or the other will have to deal with a BUSY, or a LOCKED, or something like that. In your own setup, it turns out to be process 1. But a slightly different setup would make process 2
Re: [sqlite] Database locked in multi process scenario
I see. So, the implied commit doesn't occur until you finalize? As a result, the subsequent update in step 5 was added to his non-finalized select? Still.. what is the correct way to handle the explicit scenario? I mean, having one process do a BEGIN SELECT UPDATE and another do BEGIN UPDATE is perfectly reasonable, isn't it? How do you protect from a problem? Detect the error, rollback, and try again? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Friday, February 10, 2012 9:28 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Database locked in multi process scenario On Fri, Feb 10, 2012 at 9:19 AM, Marc L. Allen mlal...@outsitenetworks.comwrote: So, you're assuming the OP actually started a transaction? Because, otherwise, isn't the SELECT in step 2 and the UPDATE in step 5 separate transactions? The OP said Step 3: The statement is not reset or finalized. That doesn't guarantee that the automatic read transaction that was started by the statement is still open, but it is pretty good hint. Remember, every statement runs within a transaction. Otherwise, the information coming out of the SELECT at the beginning might be incompatible with information that comes out at the end, if another connection modified the database while the select was running. It is not necessary to explicitly start a transaction with BEGIN in order to be in a transaction. If you don't manually do BEGIN, then BEGIN ... COMMIT is automatically inserted around each SQL statement you run. If there is a BEGIN in there somewhere, we're talking about: App1: BEGIN SELECT UPDATE .. App2BEGIN UPDATE ... Right? And you're saying that this causes a problem if App2 gets in between App1's SELECT and UPDATE? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Friday, February 10, 2012 9:13 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Database locked in multi process scenario On Fri, Feb 10, 2012 at 9:05 AM, Rob Richardson RDRichardson@rad- con.comwrote: Isn't it almost a requirement of a transaction that only one be open at a time in a database? If there could be more than one transaction, then transaction 1 might start, transaction 2 starts, transaction 1 fails, transaction 1 is rolled back, and what happens to transaction 2? One could imagine one transaction working an table 1 and a second working on table 2 which has no connection, but then someone comes along and adds a trigger to table 1 that updates table 2. Now we have two simultaneous independent transactions working on table 2. SQLite supports only SERIALIZABLE transaction semantics. That means the end result of the database is as if the various transactions had occurred in a strictly linear sequence. But SQLite does allow multiple simultaneous transactions to be in play, as long as no more than one of them is a write transaction. When a read transaction begins, it sees a snapshot of the database from the moment in time when the transaction started. In change that occur to the database file from other database connections are invisible to that transaction. The OPs problem is that he has a old read transaction open which is looking at an historical snapshot of the database, that does not contain the latest changes to the database. Then he tries to promote that read transaction to a write transaction. But that is not allowed, because doing so would fork the history of the database file. The result might not be serializable. Before you can write, you have to first be looking at the most up-to-date copy of the database. RobR, who has been struggling for months with a program that might open the same SQLite file at the same time from two points in the program, and who has realized that the program is not well designed. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto: sqlite-users-boun...@sqlite.org] On Behalf Of Sreekumar TP Sent: Friday, February 10, 2012 8:52 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Database locked in multi process scenario In the real code, there is no sleep/wait or pause. It so happens that the write of the app2 is scheduled in between. What you are suggesting is that at any point of time only one process can have a transaction open in a database? Sreekumar On Feb 10, 2012 7:12 PM, Simon Slavin slav...@bigfraud.org wrote: On 10 Feb 2012, at 1:32pm, Sreekumar TP wrote: well, the 'wait' is a simulation of what
Re: [sqlite] Database locked in multi process scenario
Marc L. Allen mlal...@outsitenetworks.com wrote: I see. So, the implied commit doesn't occur until you finalize? Or reset. As a result, the subsequent update in step 5 was added to his non-finalized select? The update was attempted within the same transaction. Still.. what is the correct way to handle the explicit scenario? I mean, having one process do a BEGIN SELECT UPDATE and another do BEGIN UPDATE is perfectly reasonable, isn't it? How do you protect from a problem? Detect the error, rollback, and try again? That's one way. The other is for the first connection to start its transaction with BEGIN IMMEDIATE, thus marking itself as a writer from the start. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked in multi process scenario
On Fri, Feb 10, 2012 at 9:32 AM, Marc L. Allen mlal...@outsitenetworks.comwrote: I see. So, the implied commit doesn't occur until you finalize? As a result, the subsequent update in step 5 was added to his non-finalized select? Still.. what is the correct way to handle the explicit scenario? I mean, having one process do a BEGIN SELECT UPDATE and another do BEGIN UPDATE is perfectly reasonable, isn't it? How do you protect from a problem? Detect the error, rollback, and try again? One approach is to be prepared to rollback and try again. Or, if you know that your transaction is going to be reading first and later writing, you can start with BEGIN IMMEDIATE which goes ahead and starts as a write transaction, guaranteeing that no other process will write ahead of you so that when you get around to writing yourself, the write won't hit a BUSY. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Friday, February 10, 2012 9:28 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Database locked in multi process scenario On Fri, Feb 10, 2012 at 9:19 AM, Marc L. Allen mlal...@outsitenetworks.comwrote: So, you're assuming the OP actually started a transaction? Because, otherwise, isn't the SELECT in step 2 and the UPDATE in step 5 separate transactions? The OP said Step 3: The statement is not reset or finalized. That doesn't guarantee that the automatic read transaction that was started by the statement is still open, but it is pretty good hint. Remember, every statement runs within a transaction. Otherwise, the information coming out of the SELECT at the beginning might be incompatible with information that comes out at the end, if another connection modified the database while the select was running. It is not necessary to explicitly start a transaction with BEGIN in order to be in a transaction. If you don't manually do BEGIN, then BEGIN ... COMMIT is automatically inserted around each SQL statement you run. If there is a BEGIN in there somewhere, we're talking about: App1: BEGIN SELECT UPDATE .. App2BEGIN UPDATE ... Right? And you're saying that this causes a problem if App2 gets in between App1's SELECT and UPDATE? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Friday, February 10, 2012 9:13 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Database locked in multi process scenario On Fri, Feb 10, 2012 at 9:05 AM, Rob Richardson RDRichardson@rad- con.comwrote: Isn't it almost a requirement of a transaction that only one be open at a time in a database? If there could be more than one transaction, then transaction 1 might start, transaction 2 starts, transaction 1 fails, transaction 1 is rolled back, and what happens to transaction 2? One could imagine one transaction working an table 1 and a second working on table 2 which has no connection, but then someone comes along and adds a trigger to table 1 that updates table 2. Now we have two simultaneous independent transactions working on table 2. SQLite supports only SERIALIZABLE transaction semantics. That means the end result of the database is as if the various transactions had occurred in a strictly linear sequence. But SQLite does allow multiple simultaneous transactions to be in play, as long as no more than one of them is a write transaction. When a read transaction begins, it sees a snapshot of the database from the moment in time when the transaction started. In change that occur to the database file from other database connections are invisible to that transaction. The OPs problem is that he has a old read transaction open which is looking at an historical snapshot of the database, that does not contain the latest changes to the database. Then he tries to promote that read transaction to a write transaction. But that is not allowed, because doing so would fork the history of the database file. The result might not be serializable. Before you can write, you have to first be looking at the most up-to-date copy of the database. RobR, who has been struggling for months with a program that might open the same SQLite file at the same time from two points in the program, and who has realized that the program is not well designed. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto: sqlite-users-boun...@sqlite.org] On Behalf Of Sreekumar TP Sent: Friday, February 10, 2012 8:52 AM To: General
Re: [sqlite] Database locked in multi process scenario
Thanks so much for clarifying that. I was unaware of the BEGIN IMMEDIATE. Sorry.. new to sqlite, used to MySQL and MSSQL. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Friday, February 10, 2012 9:43 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Database locked in multi process scenario Marc L. Allen mlal...@outsitenetworks.com wrote: I see. So, the implied commit doesn't occur until you finalize? Or reset. As a result, the subsequent update in step 5 was added to his non-finalized select? The update was attempted within the same transaction. Still.. what is the correct way to handle the explicit scenario? I mean, having one process do a BEGIN SELECT UPDATE and another do BEGIN UPDATE is perfectly reasonable, isn't it? How do you protect from a problem? Detect the error, rollback, and try again? That's one way. The other is for the first connection to start its transaction with BEGIN IMMEDIATE, thus marking itself as a writer from the start. -- Igor Tandetnik ___ 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 locked in multi process scenario
Sreekumar TP sreekumar...@gmail.com wrote: How is this different from two threads each with a db connection in a single process? If each thread uses its own separate connection, it should be no different - you would observe the same issue. Moreover the journal mode is WAL. Hence the writer should be able to append changes to the WAL file as there are no other write transaction. Your problem is with a transaction stat starts as a reader, and later tries to become a writer. This is only possible if the reader is observing the most recent state of the database, that is, if there were no writes since it started. Consider: // initial setup create table t(count integer); insert into t values (0); /* 1 */ select count from t; /* 2 */ update t set count = count + 10; /* 1 */ update t set count = count + 1; // (!) /* 1 */ select count from t; // (!!) /* 1 */ and /* 2 */ mark operations performed by two separate transactions. Imagine that such a sequence were possible, and the update at (!) succeeded. What value should count have after this update? If it's 11, then a select at (!!) would effectively observe a change written by a different transaction, violating transaction isolation. If it's 1, then an observer in yet third connection could see the count go up, then down - which is surprising as the update statements only ever increment it. Neither outcome is particularly appealing, so the sequence is prohibited altogether. There are several ways in which transactions that start as readers and later promote themselves to writers may cause problems. It's best to avoid such situations: if you know that you may need to write eventually, start your transaction with BEGIN IMMEDIATE, then it would be marked as a writer from the outset. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked in multi process scenario
The last transaction should always be the final one. In a a multiprocess/threaded application how can one make assumptions on the order of updates? Sreekumar On Fri, Feb 10, 2012 at 8:16 PM, Igor Tandetnik itandet...@mvps.org wrote: Sreekumar TP sreekumar...@gmail.com wrote: How is this different from two threads each with a db connection in a single process? If each thread uses its own separate connection, it should be no different - you would observe the same issue. Moreover the journal mode is WAL. Hence the writer should be able to append changes to the WAL file as there are no other write transaction. Your problem is with a transaction stat starts as a reader, and later tries to become a writer. This is only possible if the reader is observing the most recent state of the database, that is, if there were no writes since it started. Consider: // initial setup create table t(count integer); insert into t values (0); /* 1 */ select count from t; /* 2 */ update t set count = count + 10; /* 1 */ update t set count = count + 1; // (!) /* 1 */ select count from t; // (!!) /* 1 */ and /* 2 */ mark operations performed by two separate transactions. Imagine that such a sequence were possible, and the update at (!) succeeded. What value should count have after this update? If it's 11, then a select at (!!) would effectively observe a change written by a different transaction, violating transaction isolation. If it's 1, then an observer in yet third connection could see the count go up, then down - which is surprising as the update statements only ever increment it. Neither outcome is particularly appealing, so the sequence is prohibited altogether. There are several ways in which transactions that start as readers and later promote themselves to writers may cause problems. It's best to avoid such situations: if you know that you may need to write eventually, start your transaction with BEGIN IMMEDIATE, then it would be marked as a writer from the outset. -- Igor Tandetnik ___ 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 locked in multi process scenario
One last question or series (I hope)... From my background, I'm used to SQL statements blocking until appropriate locks are acquired. From what I've seen, it looks like sqlite doesn't block, but returns BUSY, is that correct? If two processes start a BEGIN IMMEDIATE, will one return a BUSY or will it block and wait? If it returns BUSY, how does sqlite3_exec() handle it? (Btw.. I know there are books on sqlite, so if you want to point me to one that answers all these questions, or if I'm missing it from the online docs, just let me know. I just haven't found them.) Thanks -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Marc L. Allen Sent: Friday, February 10, 2012 9:45 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Database locked in multi process scenario Thanks so much for clarifying that. I was unaware of the BEGIN IMMEDIATE. Sorry.. new to sqlite, used to MySQL and MSSQL. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Friday, February 10, 2012 9:43 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Database locked in multi process scenario Marc L. Allen mlal...@outsitenetworks.com wrote: I see. So, the implied commit doesn't occur until you finalize? Or reset. As a result, the subsequent update in step 5 was added to his non-finalized select? The update was attempted within the same transaction. Still.. what is the correct way to handle the explicit scenario? I mean, having one process do a BEGIN SELECT UPDATE and another do BEGIN UPDATE is perfectly reasonable, isn't it? How do you protect from a problem? Detect the error, rollback, and try again? That's one way. The other is for the first connection to start its transaction with BEGIN IMMEDIATE, thus marking itself as a writer from the start. -- Igor Tandetnik ___ 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 locked in multi process scenario
I took traces of the lock/unlock pattern - After App1 SELECT -- fcntl -1212610880 7 SETLK WRLCK 124 1 0 0 WAL806F9D8: acquire EXCLUSIVE-READ-LOCK[1] cnt=1 ok fcntl -1212610880 7 SETLK UNLCK 124 1 0 0 WAL806F9D8: release EXCLUSIVE-READ-LOCK[1] cnt=1 fcntl -1212610880 7 SETLK RDLCK 124 1 0 0 WAL806F9D8: acquire SHARED-READ-LOCK[1] ok After App2 UPDATE - fcntl -1212344640 7 SETLK RDLCK 124 1 0 0 WAL806F570: acquire SHARED-READ-LOCK[1] ok fcntl -1212344640 7 SETLK WRLCK 120 1 0 0 WAL806F570: acquire EXCLUSIVE-WRITE-LOCK cnt=1 ok WAL806F570: frame write ok fcntl -1212344640 7 SETLK UNLCK 120 1 0 0 WAL806F570: release EXCLUSIVE-WRITE-LOCK cnt=1 fcntl -1212344640 7 SETLK UNLCK 124 1 0 0 WAL806F570: release SHARED-READ-LOCK[1] After App1 SELECT -- fcntl -1212610880 7 SETLK WRLCK 120 1 0 0 WAL806F9D8: acquire EXCLUSIVE-WRITE-LOCK cnt=1 ok fcntl -1212610880 7 SETLK UNLCK 120 1 0 0 WAL806F9D8: release EXCLUSIVE-WRITE-LOCK cnt=1 error @ 225, 1,5,database is locked After App2 UPDATE ( a second UPDATE ) - fcntl -1212344640 7 SETLK WRLCK 124 1 0 -1 fcntl-failure-reason: RDLCK 124 1 15295 WAL806F570: acquire EXCLUSIVE-READ-LOCK[1] cnt=1 failed fcntl -1212344640 7 SETLK WRLCK 125 1 0 0 WAL806F570: acquire EXCLUSIVE-READ-LOCK[2] cnt=1 ok fcntl -1212344640 7 SETLK UNLCK 125 1 0 0 WAL806F570: release EXCLUSIVE-READ-LOCK[2] cnt=1 fcntl -1212344640 7 SETLK RDLCK 125 1 0 0 WAL806F570: acquire SHARED-READ-LOCK[2] ok fcntl -1212344640 7 SETLK WRLCK 120 1 0 0 WAL806F570: acquire EXCLUSIVE-WRITE-LOCK cnt=1 ok fcntl -1212344640 7 SETLK UNLCK 120 1 0 0 WAL806F570: release EXCLUSIVE-WRITE-LOCK cnt=1 fcntl -1212344640 7 SETLK UNLCK 125 1 0 0 WAL806F570: release SHARED-READ-LOCK[2] Additional Info - You can also see that acquiring an EXCLUSIVE-READ-LOCK FAILED during the a second update by App2. The fcntl error translated means resource temporarily unavailable - Sreekumar On Fri, Feb 10, 2012 at 8:31 PM, Marc L. Allen mlal...@outsitenetworks.comwrote: One last question or series (I hope)... From my background, I'm used to SQL statements blocking until appropriate locks are acquired. From what I've seen, it looks like sqlite doesn't block, but returns BUSY, is that correct? If two processes start a BEGIN IMMEDIATE, will one return a BUSY or will it block and wait? If it returns BUSY, how does sqlite3_exec() handle it? (Btw.. I know there are books on sqlite, so if you want to point me to one that answers all these questions, or if I'm missing it from the online docs, just let me know. I just haven't found them.) Thanks -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Marc L. Allen Sent: Friday, February 10, 2012 9:45 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Database locked in multi process scenario Thanks so much for clarifying that. I was unaware of the BEGIN IMMEDIATE. Sorry.. new to sqlite, used to MySQL and MSSQL. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Friday, February 10, 2012 9:43 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Database locked in multi process scenario Marc L. Allen mlal...@outsitenetworks.com wrote: I see. So, the implied commit doesn't occur until you finalize? Or reset. As a result, the subsequent update in step 5 was added to his non-finalized select? The update was attempted within the same transaction. Still.. what is the correct way to handle the explicit scenario? I mean, having one process do a BEGIN SELECT UPDATE and another do BEGIN UPDATE is perfectly reasonable, isn't it? How do you protect from a problem? Detect the error, rollback, and try again? That's one way. The other is for the first connection to start its transaction with BEGIN IMMEDIATE, thus marking itself as a writer from the start. -- Igor Tandetnik ___ 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 locked in multi process scenario
On 10 Feb 2012, at 3:01pm, Marc L. Allen wrote: From my background, I'm used to SQL statements blocking until appropriate locks are acquired. From what I've seen, it looks like sqlite doesn't block, but returns BUSY, is that correct? You can set a timeout. SQLite tries and retries until the timeout expires, then it returns _BUSY. http://www.sqlite.org/c3ref/busy_timeout.html You can set the timeout to a few milliseconds or a few hours. Both will work. If two processes start a BEGIN IMMEDIATE, will one return a BUSY or will it block and wait? Dependent on whether the database becomes free before the amount of time you set in the timeout. If it returns BUSY, how does sqlite3_exec() handle it? It is the sqlite3_exec() routine which will itself return _BUSY. It's up to your application to handle that well. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked in multi process scenario
There is no recovery from this situation- If you try to rollback, you get the following error -cannot rollback savepoint, SQL statments in progress or if you dont use SAVEPOINT - cannot rollback, no transaction is active If you start the transaction with BEGIN IMMEDIATE in App1, the writer in App2 gets the following error database is locked Sreekumar On Fri, Feb 10, 2012 at 8:13 PM, Igor Tandetnik itandet...@mvps.org wrote: Marc L. Allen mlal...@outsitenetworks.com wrote: I see. So, the implied commit doesn't occur until you finalize? Or reset. As a result, the subsequent update in step 5 was added to his non-finalized select? The update was attempted within the same transaction. Still.. what is the correct way to handle the explicit scenario? I mean, having one process do a BEGIN SELECT UPDATE and another do BEGIN UPDATE is perfectly reasonable, isn't it? How do you protect from a problem? Detect the error, rollback, and try again? That's one way. The other is for the first connection to start its transaction with BEGIN IMMEDIATE, thus marking itself as a writer from the start. -- Igor Tandetnik ___ 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 locked in multi process scenario
On 10 Feb 2012, at 4:45pm, Sreekumar TP wrote: There is no recovery from this situation- If you try to rollback, you get the following error -cannot rollback savepoint, SQL statments in progress or if you dont use SAVEPOINT - cannot rollback, no transaction is active If you start the transaction with BEGIN IMMEDIATE in App1, the writer in App2 gets the following error database is locked Correct. The database really is locked, since the structure of your application tells SQLite that it is going to make a change soon. If you're not ready to make the change yet, don't lock the database yet. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked in multi process scenario
On 2/10/2012 9:57 AM, Sreekumar TP wrote: The last transaction should always be the final one. In a a multiprocess/threaded application how can one make assumptions on the order of updates? There are two updates in my example: update t set count = count + 1; update t set count = count + 10; Do you feel it unreasonable to assume that, after these two statements are executed successfully, in any order, the value of count should increase by 11? If two $100 deposits to your bank account are made by different parties at approximately the same time, I think you'd be pretty upset if the account balance didn't increase by precisely $200. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked in multi process scenario
On 2/10/2012 11:45 AM, Sreekumar TP wrote: There is no recovery from this situation- If you try to rollback, you get the following error -cannot rollback savepoint, SQL statments in progress or if you dont use SAVEPOINT - cannot rollback, no transaction is active If you start the transaction with BEGIN IMMEDIATE in App1, the writer in App2 gets the following error database is locked Just reset the statement that keeps the transaction open. If you have more than one write and you want to be able to roll back reliably if any of them fails, you should start an explicit transaction. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked in multi process scenario
Though the example of $ is very intuitive, I am not suggesting that we drop one of the transaction and block the database forever (as it is happening now). Instead, it could be serialized such that two $100 transactions are committed to the db. On Fri, Feb 10, 2012 at 10:33 PM, Igor Tandetnik itandet...@mvps.orgwrote: On 2/10/2012 9:57 AM, Sreekumar TP wrote: The last transaction should always be the final one. In a a multiprocess/threaded application how can one make assumptions on the order of updates? There are two updates in my example: update t set count = count + 1; update t set count = count + 10; Do you feel it unreasonable to assume that, after these two statements are executed successfully, in any order, the value of count should increase by 11? If two $100 deposits to your bank account are made by different parties at approximately the same time, I think you'd be pretty upset if the account balance didn't increase by precisely $200. -- Igor Tandetnik __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://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 locked in multi process scenario
On 2/10/2012 9:57 AM, Sreekumar TP wrote: The last transaction should always be the final one. In a a multiprocess/threaded application how can one make assumptions on the order of updates? SQL does not have any concept of 'last transaction' or 'final transaction' or 'order of transactions'. Either a transaction is done or it isn't. There is no order for changes to a database done within a transaction. Either they're all done, or none are done. There is no idea that one change is made first, then another, then another. Similarly, if you specify an order in your SELECT command, that's the order you'll get the rows in. If you don't, the rows may appear in a random order. If you don't specify an ORDER BY clause or perhaps even if you do and your ORDER BY is ambiguous, they could easily appear in a different order every time you do a SELECT. On 10 Feb 2012, at 5:19pm, Sreekumar TP wrote: Though the example of $ is very intuitive, I am not suggesting that we drop one of the transaction and block the database forever (as it is happening now). Instead, it could be serialized such that two $100 transactions are committed to the db. You BEGIN one transaction. In that transaction you make the two changes which balance each other out. You COMMIT the transaction and both changes are made. Or you ROLLBACK the transaction and nether change is made. That's how transactions work. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked in multi process scenario
Can this situation be handled in sqlite - by upgrading the lock to a writer lock ? Since both applications use the same WAL file for read and writes, it shouldnt be a problem , because all changes will be in linear sequence ? Sreekumar On Fri, Feb 10, 2012 at 10:49 PM, Sreekumar TP sreekumar...@gmail.comwrote: Though the example of $ is very intuitive, I am not suggesting that we drop one of the transaction and block the database forever (as it is happening now). Instead, it could be serialized such that two $100 transactions are committed to the db. On Fri, Feb 10, 2012 at 10:33 PM, Igor Tandetnik itandet...@mvps.orgwrote: On 2/10/2012 9:57 AM, Sreekumar TP wrote: The last transaction should always be the final one. In a a multiprocess/threaded application how can one make assumptions on the order of updates? There are two updates in my example: update t set count = count + 1; update t set count = count + 10; Do you feel it unreasonable to assume that, after these two statements are executed successfully, in any order, the value of count should increase by 11? If two $100 deposits to your bank account are made by different parties at approximately the same time, I think you'd be pretty upset if the account balance didn't increase by precisely $200. -- Igor Tandetnik __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://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 locked in multi process scenario
2012/2/10 Sreekumar TP sreekumar...@gmail.com: Though the example of $ is very intuitive, I am not suggesting that we drop one of the transaction and block the database forever (as it is happening now). Instead, it could be serialized such that two $100 transactions are committed to the db. A situation in which I read from the database first and then changes the data tells me that they are wrong questions. It is such a problem to insert SELECT into UPDATE or INSERT? -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked in multi process scenario
On 10 Feb 2012, at 5:29pm, Sreekumar TP wrote: Can this situation be handled in sqlite - by upgrading the lock to a writer lock ? Since both applications use the same WAL file for read and writes, it shouldnt be a problem , because all changes will be in linear sequence ? SQLite handles all of this perfectly. We don't understand why you have a problem. Why are you starting a SELECT command, then waiting to finalize it for a long time ? Just do your SELECT and complete it. When you're ready to make the changes in your your transaction, do that. There is no need for the SELECT and the changes to be part of the same transaction. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked in multi process scenario
On Fri, Feb 10, 2012 at 11:45 AM, Sreekumar TP sreekumar...@gmail.comwrote: There is no recovery from this situation- The recovery from your situation is to reset or finalize the initial query that is holding the transaction option. If you try to rollback, you get the following error -cannot rollback savepoint, SQL statments in progress or if you dont use SAVEPOINT - cannot rollback, no transaction is active If you start the transaction with BEGIN IMMEDIATE in App1, the writer in App2 gets the following error database is locked Sreekumar On Fri, Feb 10, 2012 at 8:13 PM, Igor Tandetnik itandet...@mvps.org wrote: Marc L. Allen mlal...@outsitenetworks.com wrote: I see. So, the implied commit doesn't occur until you finalize? Or reset. As a result, the subsequent update in step 5 was added to his non-finalized select? The update was attempted within the same transaction. Still.. what is the correct way to handle the explicit scenario? I mean, having one process do a BEGIN SELECT UPDATE and another do BEGIN UPDATE is perfectly reasonable, isn't it? How do you protect from a problem? Detect the error, rollback, and try again? That's one way. The other is for the first connection to start its transaction with BEGIN IMMEDIATE, thus marking itself as a writer from the start. -- Igor Tandetnik ___ 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 locked in multi process scenario
On 10 Feb 2012, at 5:32pm, Kit wrote: 2012/2/10 Sreekumar TP sreekumar...@gmail.com: Though the example of $ is very intuitive, I am not suggesting that we drop one of the transaction and block the database forever (as it is happening now). Instead, it could be serialized such that two $100 transactions are committed to the db. A situation in which I read from the database first and then changes the data tells me that they are wrong questions. It is such a problem to insert SELECT into UPDATE or INSERT? Why do you need to do a SELECT at all ? Can you present us with a simple example of your situation ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked in multi process scenario
2012/2/10 Simon Slavin slav...@bigfraud.org: On 10 Feb 2012, at 5:32pm, Kit wrote: A situation in which I read from the database first and then changes the data tells me that they are wrong questions. It is such a problem to insert SELECT into UPDATE or INSERT? Why do you need to do a SELECT at all ? Can you present us with a simple example of your situation ? Simon. Add value from Alpha to Bravo: Wrong: SELECT val AS val1 FROM t1 WHERE name='Alpha'; UPDATE t1 SET val=val+val1 WHERE name='Bravo'; Good: UPDATE t1 SET val=val+(SELECT val FROM t1 WHERE name='Alpha') WHERE name='Bravo'; No explicit transaction, no problem. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked in multi process scenario
On 10 Feb 2012, at 5:55pm, Kit wrote: 2012/2/10 Simon Slavin slav...@bigfraud.org: On 10 Feb 2012, at 5:32pm, Kit wrote: A situation in which I read from the database first and then changes the data tells me that they are wrong questions. It is such a problem to insert SELECT into UPDATE or INSERT? Why do you need to do a SELECT at all ? Can you present us with a simple example of your situation ? Simon. Add value from Alpha to Bravo: Wrong: SELECT val AS val1 FROM t1 WHERE name='Alpha'; UPDATE t1 SET val=val+val1 WHERE name='Bravo'; Good: UPDATE t1 SET val=val+(SELECT val FROM t1 WHERE name='Alpha') WHERE name='Bravo'; No explicit transaction, no problem. Ah, you were suggesting the solution, not asking the question. I misunderstood. Sorry. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked in multi process scenario
On 2/10/2012 12:29 PM, Sreekumar TP wrote: Can this situation be handled in sqlite - by upgrading the lock to a writer lock ? Since both applications use the same WAL file for read and writes, it shouldnt be a problem , because all changes will be in linear sequence ? Consider again: [1] BEGIN; [1] SELECT balance from Accounts; (1) [2] BEGIN; [2] SELECT balance from Accounts; (2) [2] UPDATE Accounts SET balance = balance + 100; [2] SELECT balance from Accounts; (3) [2] COMMIT; [1] UPDATE Accounts SET balance = balance + 100; [1] SELECT balance from Accounts; (4) [1] COMMIT; [3] SELECT balance from Accounts; (5) Statements are shown in the order they are submitted to SQLite. Numbers in square brackets indicate individual connections. Let's suppose SQLite does everything the way you want (it's not quite clear what it is you want exactly, so that's what I'm trying to establish). I assume you expect this sequence of statements to succeed. In this ideal world, what value of Accounts.balance should be observed at points (1), (2), (3), (4) and (5), in your opinion? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked in multi process scenario
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Friday, February 10, 2012 2:36 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Database locked in multi process scenario On 2/10/2012 12:29 PM, Sreekumar TP wrote: Can this situation be handled in sqlite - by upgrading the lock to a writer lock ? Since both applications use the same WAL file for read and writes, it shouldnt be a problem , because all changes will be in linear sequence ? Consider again: [1] BEGIN; [1] SELECT balance from Accounts; (1) [2] BEGIN; [2] SELECT balance from Accounts; (2) [2] UPDATE Accounts SET balance = balance + 100; [2] SELECT balance from Accounts; (3) [2] COMMIT; [1] UPDATE Accounts SET balance = balance + 100; [1] SELECT balance from Accounts; (4) [1] COMMIT; [3] SELECT balance from Accounts; (5) Statements are shown in the order they are submitted to SQLite. Numbers in square brackets indicate individual connections. Let's suppose SQLite does everything the way you want (it's not quite clear what it is you want exactly, so that's what I'm trying to establish). I assume you expect this sequence of statements to succeed. In this ideal world, what value of Accounts.balance should be observed at points (1), (2), (3), (4) and (5), in your opinion? Well.. in MSSQL that would work the way you think he expects. That is, if the initial balance is $100 (1) 100 (2) 100 (3) 200 (4) 300 (5) 300 MSSQL in its default serialization mode does not guarantee repeatable reads within a transaction. But, it provides locking hints to help enforce it when required. I'm guessing that sqlite does guarantee repeatable reads? Marc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked in multi process scenario
On 2/10/2012 2:57 PM, Marc L. Allen wrote: MSSQL in its default serialization mode does not guarantee repeatable reads within a transaction. But, it provides locking hints to help enforce it when required. I'm guessing that sqlite does guarantee repeatable reads? SQLite implements only one transaction isolation level - serializable (except in shared cache mode). In regular journal mode, this is achieved by holding locks. In WAL mode, this is effectively achieved via snapshot isolation (http://en.wikipedia.org/wiki/Snapshot_isolation) -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Database Locked
I'm using sqlite v3 as database backend for an very small web app (some of 20 concurrent users -really serial access users-).. in Ms. Win xp (my test/develop env.) it work perfectly...my web app can handle that load(and even more by My JMeter Tests)... But on my production environment (Linux CentOS)... with 3 concurrent access (CTRL+F5 on diferent Chrome Tabs/3 times)... my web app hang with message Sqlite Blocked.. I'm using exactly same versions on both environments(for Java, Sqlite JDBC, Resin, App. Pooling, etc..) I know that jdbc pooling is trivial on sqlite (just one user at time can update the DB) but in Win XP. it's running fine Why ? On My Test Page.. i'm doing some just SELECT statement... (no insert, no delete, no update, no ddl) Sorry for my Bad English ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Database Locked
I'm using sqlite v3 as database backend for an very small web app (some of 20 concurrent users -really serial access users-).. in Ms. Win xp (my test/develop env.) it work perfectly...my web app can handle that load(and even more by My JMeter Tests)... But on my production environment (Linux CentOS)... with 3 concurrent access (CTRL+F5 on diferent Chrome Tabs/3 times)... my web app hang with message Sqlite Blocked.. I'm using exactly same versions on both environments(for Java, Sqlite JDBC, Resin, App. Pooling, etc..) I know that jdbc pooling is trivial on sqlite (just one user at time can update the DB) but in Win XP. it's running fine Why ? On My Test Page.. i'm doing some just SELECT statement... (no insert, no delete, no update, no ddl) Sorry for my Bad English ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Issue sqlite database locked
Hi,
I have downloaded the sqlite from the link http://www.sqlite.org/download.html
version sqlite-3.6.23.so.gzhttp://www.sqlite.org/sqlite-3.6.23.so.gz (220.62
KiB). I am using fedora -9-i386. Have mounted a
Shared drive on my pc to the fedora image.I copied the extracted sqlite db and
saved it on the fedora usr/lib directory from where the code loads the db.
This is the piece of code that I am using to do the operations on sqlite db.
#include iostream
#include sqlite3.h
#include dlfcn.h
using namespace std;
typedef void* HINSTANCE;
typedef int (*FnPtr_sqlite3_open)(const char *filename, sqlite3 **ppDb );
typedef int (*FnPtr_sqlite3_extended_result_codes) (sqlite3*, int onoff);
typedef int (*FnPtr_sqlite3_prepare_v2)(sqlite3 *db, const char *zSql, int
nByte, sqlite3_stmt **ppStmt, const char **pzTail );
typedef int (*FnPtr_sqlite3_step)(sqlite3_stmt*);
typedef int (*FnPtr_sqlite3_finalize)(sqlite3_stmt *pStmt);
class TDBClass
{
public:
HINSTANCE LoadLibrary();
void LoadFunctions();
void VerifyLoadedFunction(void* aFnPtr);
public:
FnPtr_sqlite3_open sqlite3_open;
FnPtr_sqlite3_extended_result_codes sqlite3_extended_result_codes;
FnPtr_sqlite3_prepare_v2sqlite3_prepare_v2;
FnPtr_sqlite3_step sqlite3_step;
FnPtr_sqlite3_finalize sqlite3_finalize;
HINSTANCE sqLiteHndl;
};
HINSTANCE TDBClass::LoadLibrary()
{
sqLiteHndl = dlopen(/usr/lib/sqlite-3.6.23.so, RTLD_LAZY|RTLD_GLOBAL);
return sqLiteHndl;
}
void* GetProcAddress(HINSTANCE aHandle, const char* aSymbol)
{
return dlsym(aHandle, aSymbol);
}
void TDBClass::LoadFunctions()
{
sqlite3_open =
(FnPtr_sqlite3_open)GetProcAddress(sqLiteHndl,sqlite3_open);
if(sqlite3_open== NULL)
{
return;
}
sqlite3_prepare_v2 = (FnPtr_sqlite3_prepare_v2)
GetProcAddress(sqLiteHndl,sqlite3_prepare_v2);
sqlite3_step= (FnPtr_sqlite3_step)
GetProcAddress(sqLiteHndl,sqlite3_step);
sqlite3_extended_result_codes = (FnPtr_sqlite3_extended_result_codes)
GetProcAddress(sqLiteHndl, sqlite3_extended_result_codes );
}
int main()
{
cout !!!Hello World!!! endl; // prints !!!Hello World!!!
sqlite3* sqlhandle;
TDBClass tdbhandle;
HINSTANCE sqlitehandle = tdbhandle.LoadLibrary();
tdbhandle.LoadFunctions();
int err =
tdbhandle.sqlite3_open(/root/Sudha/epoc32/winscw/c/tswi/tscrtool/scr.db,sqlhandle);
err = tdbhandle.sqlite3_extended_result_codes(sqlhandle, 0);
sqlite3_stmt* stmtHandle = NULL;
const char* stmtTail = NULL;
const char* statement = CREATE TABLE SoftwareTypeNames(NameId INTEGER
PRIMARY KEY NOT NULL,SoftwareTypeId INTEGER NOT NULL,Locale INTEGER DEFAULT
0,Name TEXT NOT NULL);;
std::string stmt(statement);
tdbhandle.sqlite3_prepare_v2(sqlhandle, stmt.c_str(), stmt.size(),
stmtHandle, stmtTail);
int err1 = tdbhandle.sqlite3_step(stmtHandle);
return 0;
}
This step int err1 = tdbhandle.sqlite3_step(stmtHandle);
in the code returns 5 (sqlite_busy) every time I run the program.
(I don't have any multi threads in my program which can lock the db. )
Any help on this is appreciated.
Regards
sudha
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Issue sqlite database locked
On Mar 26, 2010, at 12:40 AM, sudha.bheema...@nokia.com
sudha.bheema...@nokia.com
wrote:
Hi,
I have downloaded the sqlite from the link
http://www.sqlite.org/download.html
version sqlite-3.6.23.so.gzhttp://www.sqlite.org/
sqlite-3.6.23.so.gz (220.62 KiB). I am using fedora -9-i386. Have
mounted a
Shared drive on my pc to the fedora image.I copied the extracted
sqlite db and saved it on the fedora usr/lib directory from where
the code loads the db.
Please see the second paragraph of http://www.sqlite.org/faq.html#q5
for a likely explanation of what is going wrong.
This is the piece of code that I am using to do the operations on
sqlite db.
#include iostream
#include sqlite3.h
#include dlfcn.h
using namespace std;
typedef void* HINSTANCE;
typedef int (*FnPtr_sqlite3_open)(const char *filename, sqlite3
**ppDb );
typedef int (*FnPtr_sqlite3_extended_result_codes) (sqlite3*, int
onoff);
typedef int (*FnPtr_sqlite3_prepare_v2)(sqlite3 *db, const char
*zSql, int nByte, sqlite3_stmt **ppStmt, const char **pzTail );
typedef int (*FnPtr_sqlite3_step)(sqlite3_stmt*);
typedef int (*FnPtr_sqlite3_finalize)(sqlite3_stmt *pStmt);
class TDBClass
{
public:
HINSTANCE LoadLibrary();
void LoadFunctions();
void VerifyLoadedFunction(void* aFnPtr);
public:
FnPtr_sqlite3_open sqlite3_open;
FnPtr_sqlite3_extended_result_codes
sqlite3_extended_result_codes;
FnPtr_sqlite3_prepare_v2sqlite3_prepare_v2;
FnPtr_sqlite3_step sqlite3_step;
FnPtr_sqlite3_finalize sqlite3_finalize;
HINSTANCE sqLiteHndl;
};
HINSTANCE TDBClass::LoadLibrary()
{
sqLiteHndl = dlopen(/usr/lib/sqlite-3.6.23.so, RTLD_LAZY|
RTLD_GLOBAL);
return sqLiteHndl;
}
void* GetProcAddress(HINSTANCE aHandle, const char* aSymbol)
{
return dlsym(aHandle, aSymbol);
}
void TDBClass::LoadFunctions()
{
sqlite3_open =
(FnPtr_sqlite3_open)GetProcAddress(sqLiteHndl,sqlite3_open);
if(sqlite3_open== NULL)
{
return;
}
sqlite3_prepare_v2 = (FnPtr_sqlite3_prepare_v2)
GetProcAddress(sqLiteHndl,sqlite3_prepare_v2);
sqlite3_step= (FnPtr_sqlite3_step)
GetProcAddress(sqLiteHndl,sqlite3_step);
sqlite3_extended_result_codes =
(FnPtr_sqlite3_extended_result_codes) GetProcAddress(sqLiteHndl,
sqlite3_extended_result_codes );
}
int main()
{
cout !!!Hello World!!! endl; // prints !!!Hello
World!!!
sqlite3* sqlhandle;
TDBClass tdbhandle;
HINSTANCE sqlitehandle = tdbhandle.LoadLibrary();
tdbhandle.LoadFunctions();
int err = tdbhandle.sqlite3_open(/root/Sudha/epoc32/winscw/c/
tswi/tscrtool/scr.db,sqlhandle);
err = tdbhandle.sqlite3_extended_result_codes(sqlhandle, 0);
sqlite3_stmt* stmtHandle = NULL;
const char* stmtTail = NULL;
const char* statement = CREATE TABLE
SoftwareTypeNames(NameId INTEGER PRIMARY KEY NOT NULL,SoftwareTypeId
INTEGER NOT NULL,Locale INTEGER DEFAULT 0,Name TEXT NOT NULL);;
std::string stmt(statement);
tdbhandle.sqlite3_prepare_v2(sqlhandle, stmt.c_str(),
stmt.size(), stmtHandle, stmtTail);
int err1 = tdbhandle.sqlite3_step(stmtHandle);
return 0;
}
This step int err1 = tdbhandle.sqlite3_step(stmtHandle);
in the code returns 5 (sqlite_busy) every time I run the program.
(I don't have any multi threads in my program which can lock the db. )
Any help on this is appreciated.
Regards
sudha
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
D. Richard Hipp
d...@hwaci.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 'database locked' error
Hello, I would like to pass cookies back and forth between a search script I have written that runs on the local system, and javascript pages displaying the forms and results in Firefox. I'll forego all the gory details here, it is working pretty well, but being able to pass cookies back and forth would make allow me to make it much more robust. FF (3+) uses an sqlite db to store it's cookies. I have tried to edit cookie values using sqlite3, but I keep getting a 'database locked' error, incessantly. (Does FF simply put a lock on this file at the beginning of a session and not release it til it is done?) I have read things that talk about using fcntl() to get around this, but it is quite over my head, though I am willing to invest, as I really want to accomplish this. If I could get some suggestions on how to handle this situation to my ends, I would much appreciate it. I could just simply copy the db file, alter the cookie values, and then copy it over the existing cookies db. This works. Perhaps a more kosher way would be to use fcntl() (or something) to get a mandatory exclusive lock beforehand. This is an area I am quite unfamiliar with, but willing to invest in learning if it seems feasible. I really would like to be able to edit the cookies outside of FF and I believe there is a way without just forcing it. Reading them is no problem, but I want to be able to write flags back to the javascript while it is running. Perhaps copying over the db file is not such a big deal, after all, this would (most likely) only be occurring while the user is using the search feature anyway. Any thoughts on this? But if I can do this in a more kosher manner, it would be better. Any input on this? Thanks much, Allasso -- View this message in context: http://www.nabble.com/%27database-locked%27-error-tp24953225p24953225.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Database Locked
I am writing a php script to back up mysql to sqlite(3) via PDO My process is : -- Create SQLite database connection Loop several tables Describe mysql table Drop sqlite table if exists, Create matching sqlite table query mysql table for recordset. insert recordset into sqlite table End Loop Kill SQLite connection This process works fine if the database is a new file otherwise I get an error : SQLSTATE[HY000]: General error: 6 database table is locked; What am I missing here - perhaps the tables are left open ? *Ben Marchbanks* www.magazooms.com http://www.magazooms.com/ Signature Email: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Phone: (864) 284.9918 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Database locked error, while deleting
Hi, I have a very big table with around 40,00, 000 rows. 4 columns. 220 MB. Now, I have two threads:- Thread1: Is deleting 7,00, 000 rows from the table. Thread2: Is doing SELECT on the same table. Now the problem is sometimes the sqlite3_prepare for the SELECT query is failing with SQLITE_BUSY error. My questions: - 1. What is the best way to handle this error, and continue working normally? 2. Is there any documentation in sqlite3.org, which discuses the locking mechanism for DELETEs? Exacly in what phase of DELETE sqlite creates the exclusive lock, so, the SELECT is failing? -- Sabyasachi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked error, while deleting
There can only be one prepare per table at a time. The first prepare has to be finalized before another one. Table is locked by the first prepare until finalized. On Tue, Jun 10, 2008 at 7:36 AM, Sabyasachi Ruj [EMAIL PROTECTED] wrote: Hi, I have a very big table with around 40,00, 000 rows. 4 columns. 220 MB. Now, I have two threads:- Thread1: Is deleting 7,00, 000 rows from the table. Thread2: Is doing SELECT on the same table. Now the problem is sometimes the sqlite3_prepare for the SELECT query is failing with SQLITE_BUSY error. My questions: - 1. What is the best way to handle this error, and continue working normally? 2. Is there any documentation in sqlite3.org, which discuses the locking mechanism for DELETEs? Exacly in what phase of DELETE sqlite creates the exclusive lock, so, the SELECT is failing? -- Sabyasachi ___ 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 locked error, while deleting
On Jun 10, 2008, at 7:50 AM, Alex Katebi wrote: There can only be one prepare per table at a time. The first prepare has to be finalized before another one. Table is locked by the first prepare until finalized. Actually, there is no limit on the number of prepared statements on a single table at a single time. The above is not correct. 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 locked error, while deleting
Sabyasachi Ruj [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Now, I have two threads:- Thread1: Is deleting 7,00, 000 rows from the table. Thread2: Is doing SELECT on the same table. Now the problem is sometimes the sqlite3_prepare for the SELECT query is failing with SQLITE_BUSY error. Are you sure it's the prepare that fails, and not step? Only the very first prepare after opening the connection may fail this way, because SQLite needs to read database schema at this point. 1. What is the best way to handle this error, and continue working normally? Retry until you manage to go through. Or, prepare all the necessary statements up front, before commencing any database work. Then prepare won't fail (of course, step still may). 2. Is there any documentation in sqlite3.org, which discuses the locking mechanism for DELETEs? Exacly in what phase of DELETE sqlite creates the exclusive lock, so, the SELECT is failing? http://sqlite.org/lockingv3.html EXCLUSIVE lock is obtained whenever SQLite needs to write to the database file. This may happen when the transaction is committed, or when the number of changes in a still-open transaction becomes so large that it no longer fits in a memory cache and has to be spilled to disk. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked error, while deleting
We are using two different connections to sqlite for these two different threads. So, we are not sharing the connection in SELECT and the DELETE thread. On Tue, Jun 10, 2008 at 5:28 PM, D. Richard Hipp [EMAIL PROTECTED] wrote: On Jun 10, 2008, at 7:36 AM, Sabyasachi Ruj wrote: Hi, I have a very big table with around 40,00, 000 rows. 4 columns. 220 MB. Now, I have two threads:- Thread1: Is deleting 7,00, 000 rows from the table. Thread2: Is doing SELECT on the same table. Now the problem is sometimes the sqlite3_prepare for the SELECT query is failing with SQLITE_BUSY error. My questions: - 1. What is the best way to handle this error, and continue working normally? 2. Is there any documentation in sqlite3.org, which discuses the locking mechanism for DELETEs? Exacly in what phase of DELETE sqlite creates the exclusive lock, so, the SELECT is failing? Are the two threads using the same database connection, or are they making separate and independent calls to sqlite3_open()? D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Sabyasachi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked error, while deleting
Our PRAGMA cache_size = 4000. Is there any recommended way to calculate the cache size needed? On Tue, Jun 10, 2008 at 5:29 PM, Igor Tandetnik [EMAIL PROTECTED] wrote: Sabyasachi Ruj [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED][EMAIL PROTECTED] Now, I have two threads:- Thread1: Is deleting 7,00, 000 rows from the table. Thread2: Is doing SELECT on the same table. Now the problem is sometimes the sqlite3_prepare for the SELECT query is failing with SQLITE_BUSY error. Are you sure it's the prepare that fails, and not step? Only the very first prepare after opening the connection may fail this way, because SQLite needs to read database schema at this point. 1. What is the best way to handle this error, and continue working normally? Retry until you manage to go through. Or, prepare all the necessary statements up front, before commencing any database work. Then prepare won't fail (of course, step still may). 2. Is there any documentation in sqlite3.org, which discuses the locking mechanism for DELETEs? Exacly in what phase of DELETE sqlite creates the exclusive lock, so, the SELECT is failing? http://sqlite.org/lockingv3.html EXCLUSIVE lock is obtained whenever SQLite needs to write to the database file. This may happen when the transaction is committed, or when the number of changes in a still-open transaction becomes so large that it no longer fits in a memory cache and has to be spilled to disk. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Sabyasachi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked error, while deleting
Richard, Just to go one step ahead, If there are independent connections, do you mean that there can exist multiple transactions at the same time? I think not since the first transaction (write) will exclusively lock the database and the second transaction will get a busy error. Correct me if I am wrong. Will the behaviour differ in case of shared cache enabled and disabled? -Shailesh. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp Sent: Tuesday, June 10, 2008 5:28 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Database locked error, while deleting On Jun 10, 2008, at 7:36 AM, Sabyasachi Ruj wrote: Hi, I have a very big table with around 40,00, 000 rows. 4 columns. 220 MB. Now, I have two threads:- Thread1: Is deleting 7,00, 000 rows from the table. Thread2: Is doing SELECT on the same table. Now the problem is sometimes the sqlite3_prepare for the SELECT query is failing with SQLITE_BUSY error. My questions: - 1. What is the best way to handle this error, and continue working normally? 2. Is there any documentation in sqlite3.org, which discuses the locking mechanism for DELETEs? Exacly in what phase of DELETE sqlite creates the exclusive lock, so, the SELECT is failing? Are the two threads using the same database connection, or are they making separate and independent calls to sqlite3_open()? 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 locked error, while deleting
On Jun 10, 2008, at 10:18 AM, Shailesh Birari wrote: Richard, Just to go one step ahead, If there are independent connections, do you mean that there can exist multiple transactions at the same time? I think not since the first transaction (write) will exclusively lock the database and the second transaction will get a busy error. Correct me if I am wrong. Will the behaviour differ in case of shared cache enabled and disabled? In shared cache mode, you can do PRAGMA read_uncommited=ON and then the reader will be able to see uncommitted data from the writer. And you won't ever get SQLITE_BUSY because the reader is unable to read the schema on account of a busy write transaction. An easier thing for you to do might be to use a single database connection. That way the reader will certainly never block waiting on the writer since they will be serialized. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Database locked version 3.3.17
Hi all, Think I may have hit some form of a bug in 3.3.17: At an insert statement I'm getting a rc=[5] msg=[database is locked] When I recompile the sqlite using version 3.3.13 and re-run the appliation. No problems. Sorry this is pretty vague.. I'll try digging in more and finding more details to reproduce a simple test case. Ken
Re: [sqlite] Database locked version 3.3.17
Resolved the issue. My issue on the app side. I think the newer version is much faster. So much so that it changed timing characteristics and reveald a logic bug in my code. Appologies, Ken Ken [EMAIL PROTECTED] wrote: Hi all, Think I may have hit some form of a bug in 3.3.17: At an insert statement I'm getting a rc=[5] msg=[database is locked] When I recompile the sqlite using version 3.3.13 and re-run the appliation. No problems. Sorry this is pretty vague.. I'll try digging in more and finding more details to reproduce a simple test case. Ken
[sqlite] database locked
Hello, I encounter the following difficulties with sqlite3: I have 4 different processes working on an sqlite3 database. When the sql timeout (set by sqlite3_busy_timeout) is set to 5 seconds and that all processes are running on a same windows machine, it seems OK (I am not 100% sure of that, need to do more tests). When the configuration is the following: 3 processes are running on a windows XP pro (service pack 2) machine (with NTFS filesystem) The 4th process and the database are located on another windows machine (same OS), NTFS filesystem. I cannot let the sql timeout set to 5 seconds because I get a lot of database locked errors; doing the same tests with a timeout set to 30 seconds really improves the results: I get really less database locked errors so, I have set it to 1 minute and it works better again ( I got only 2 database locked messages whereas the 4 processes had been running and working on the database (reading/writing and doing immediate transaction) for 18 hours. To give you an idea, with an sql timeout of 5 seconds, I got the first database locked after les than 30 minutes activity. With a timeout set to 30 seconds, I got the database locked problem after more than 3 hours. As I read in the sqlite3 documentation that when a process locks the database using a reserved lock (which is what I use as I do immediate transactions) it took only a few milliseconds, I am very surprise to find that I need a such long timeout. Does anyone have an explanation or has someone already had that problem? To try to understand what happens, i have added many traces in my code (each trace is written with the hour in the format hour:minute:second; My code looks like this: Trace1 sqlite3_exec(handle,begin immediate transaction something); trace2 ... Trace3 Sqlite3_exec(handle,end transaction something); Trace4 I have noticed that when a processus crashes dued to a database locked, in the log files of the other processes, at nearly the same hour, 20 seconds pass between trace1 and trace2 or between trace3 and trace4 whereas usually (I do many transactions like that) it doesn't even take a second. What can cause a processus to take 20 seconds to execute end transaction? Is the following scenario possible? A process writes trace3 in the log file then is put in a queue by the windows OS. Transaction is still active so, the database is locked and other processes (woken up by the windows OS) cannot do their begin transaction= they write trace1 in their log file and start to wait (does sqlite3 have its own process queue to handle that? I have the impression that it is not the processus that has been waiting for the longuest time that is given the lock). 20 seconds after, the windows OS finally wakes up our first process that is now able to perform its end transaction and write trace4 in the log file. Other processes can now perform their begin transaction and write trace2 in the log file. If that scenario is possible, then it should also happen when all 4 processes are running on the same machine... Any information is welcome. Thank you in advance.
[sqlite] Database Locked
Hi, I have opened a database, created tables and inserted data to it in a single transaction. Then I have closed the data base. Then I reopened the database and tried to update the table entries. But it throws an exception called Database Locked. Where can be the mistake? I am using wxSqlite3 wrapper class. All these are performed from a single program. Thanks, Lloyd. __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Database Locked
Lloyd, You need at some point to call the sqlite3_finalize function. I don't use wxSqlite, but if it's not handled by the destructor of the recordset then there should be a method, possibly called finalize or close, that needs to be called. I recommend consulting the documentation for wxSQLite since most of the wxWidgets components are pretty well documented. Clay Dowling Lloyd said: I have tracked down the problem and found th following. I have a select statement which stores the result set in a pointer variable. If the result set of select statement is empty, the program executes, otherwise it gives a Database Locked error. How can I solve this problem ? Thanks again, Lloyd. On Wed, 2006-11-15 at 14:46 +0530, Lloyd wrote: Hi, I have opened a database, created tables and inserted data to it in a single transaction. Then I have closed the data base. Then I reopened the database and tried to update the table entries. But it throws an exception called Database Locked. Where can be the mistake? I am using wxSqlite3 wrapper class. All these are performed from a single program. Thanks, Lloyd. __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] - __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] - -- Simple Content Management http://www.ceamus.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Database Locked
Thanks Clay Dowling, Yes there is a function for fanalize function. Now the problem is solved. On Tue, 2006-11-14 at 08:02 -0500, Clay Dowling wrote: Lloyd, You need at some point to call the sqlite3_finalize function. I don't use wxSqlite, but if it's not handled by the destructor of the recordset then there should be a method, possibly called finalize or close, that needs to be called. I recommend consulting the documentation for wxSQLite since most of the wxWidgets components are pretty well documented. Clay Dowling Lloyd said: I have tracked down the problem and found th following. I have a select statement which stores the result set in a pointer variable. If the result set of select statement is empty, the program executes, otherwise it gives a Database Locked error. How can I solve this problem ? Thanks again, Lloyd. On Wed, 2006-11-15 at 14:46 +0530, Lloyd wrote: Hi, I have opened a database, created tables and inserted data to it in a single transaction. Then I have closed the data base. Then I reopened the database and tried to update the table entries. But it throws an exception called Database Locked. Where can be the mistake? I am using wxSqlite3 wrapper class. All these are performed from a single program. Thanks, Lloyd. __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] - __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] - __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Database Locked
I have tracked down the problem and found th following. I have a select statement which stores the result set in a pointer variable. If the result set of select statement is empty, the program executes, otherwise it gives a Database Locked error. How can I solve this problem ? Thanks again, Lloyd. On Wed, 2006-11-15 at 14:46 +0530, Lloyd wrote: Hi, I have opened a database, created tables and inserted data to it in a single transaction. Then I have closed the data base. Then I reopened the database and tried to update the table entries. But it throws an exception called Database Locked. Where can be the mistake? I am using wxSqlite3 wrapper class. All these are performed from a single program. Thanks, Lloyd. __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] - __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Database Locked
You have to find what is locking the database and unlock it. Lloyd wrote: I have tracked down the problem and found th following. I have a select statement which stores the result set in a pointer variable. If the result set of select statement is empty, the program executes, otherwise it gives a Database Locked error. How can I solve this problem ? Thanks again, Lloyd. On Wed, 2006-11-15 at 14:46 +0530, Lloyd wrote: Hi, I have opened a database, created tables and inserted data to it in a single transaction. Then I have closed the data base. Then I reopened the database and tried to update the table entries. But it throws an exception called Database Locked. Where can be the mistake? I am using wxSqlite3 wrapper class. All these are performed from a single program. Thanks, Lloyd. __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] - __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] database locked
Hi all, I'm a software developer and I'm using sqlite3 for my application in c++. This is the problem I've found: two processes do queries (about 1 query per second) on one database; the 'select' queries don't have any problem, while 'update' queries find problems after 1 or 2 days that the processes are running. In fact, generally the 'update' queries begin to fail with the exit code 5 (database locked) with sqlite3_get_table(), while the 'select' queries are ok. If I stop and restart one of the two processes, this process does for some days all the queries in the right way and then begins again to give problems, while the other continues to return 'database locked' error. I don't understand the problem... Am I doing something wrong? Can anyone help me? Thanks for your attention Laura
Re: [sqlite] database locked
On 8/21/06, Laura Longo [EMAIL PROTECTED] wrote: Hi all, I'm a software developer and I'm using sqlite3 for my application in c++. This is the problem I've found: two processes do queries (about 1 query per second) on one database; the 'select' queries don't have any problem, while 'update' queries find problems after 1 or 2 days that the processes are running. In fact, generally the 'update' queries begin to fail with the exit code 5 (database locked) with sqlite3_get_table(), while the 'select' queries are ok. If I stop and restart one of the two processes, this process does for some days all the queries in the right way and then begins again to give problems, while the other continues to return 'database locked' error. I don't understand the problem... Am I doing something wrong? Can anyone help me? Are you checking for database locked return codes and retrying the operation (on updates)? -- SqliteImporter and SqliteReplicator: Command line utilities for Sqlite http://www.reddawn.net/~jsprenkl/Sqlite Cthulhu Bucks! http://www.cthulhubucks.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Database locked. Any idea ?
I could not port my code quickly to Cygwin but a quick investigation shows me that the lock (wrFlag) is never set back to 1. Which API is supposed to do this ? Right now, I have the following stack trace: sqlite3_step sqlite3VbdeExec Cp_OP_OpenRead Sqlite3BtreeCursor wrFlag = 0; Any idea how my table should be unlocked ? Mario Hebert Legerity [EMAIL PROTECTED] 06/20/2006 03:07 PM Please respond to sqlite-users@sqlite.org To sqlite-users@sqlite.org cc Subject Re: [sqlite] Database locked. Any idea ? [EMAIL PROTECTED] wrote: Anyone has an idea of what may be wrong ? Oh yeah, I am running of my own port using uCos and a memory database. You say you are using a :memory: database and this is happening? Can you provide a test program running under Linux? -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] Database locked. Any idea ?
[EMAIL PROTECTED] wrote: Anyone has an idea of what may be wrong ? Oh yeah, I am running of my own port using uCos and a memory database. You say you are using a :memory: database and this is happening? Can you provide a test program running under Linux? -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] Database locked. Any idea ?
Yes I am using a :memory: database created from 3 flash database that were previously attached to it. I am not quite sure of the amount of work that would be required for me to port it to linux. I will try later today to build it under cygwin and see if the same behavior occurs. Regards, Mario Hebert Legerity Anyone has an idea of what may be wrong ? Oh yeah, I am running of my own port using uCos and a memory database. You say you are using a :memory: database and this is happening? Can you provide a test program running under Linux? -- D. Richard Hipp [EMAIL PROTECTED]
RE: [sqlite] Database locked. Any idea ?
Hi, I don't know if I can help you with your problem, but I'm interested in what you are trying to do, are u using uCos on a Z-World based system? Kind regards, Lodewijk Duymaer van Twist
[sqlite] Database Locked Error... problem still persists
Hi,
Background Info -
1. Using Linux kernel v2.4.21 on both machines.
2. Scenario in which this problem occurs - when I rlogin/rsh to another
machine and then run the app
calls to sqlite3_open() work successfully
calls to sqlite3_exec() return 'database is locked' error message
3. This problem does not occur if I run the app on my local machine.
-
I've debugged this issue and found that the problem lies with fcntl()
returning a failure (i.e. -1) which causes sqlite3 functions to return
database is locked error.
The fcntl() calls which return -1 are of this type -
fcntl64(5, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, start=1073741824,
len=1}, 0xbfffe4f0)
I think its safe to assume that the use of F_SETLK64 causes the fcntl()
fn to fail.
Is there a solution to this without changing the NFS settings. Maybe if
I modify the fcntl() calls in sqlite source code so that instead of
F_SETLK64 I pass some other command. I've tried passing F_SETLKW64 but
that reproduces the problem again.
I also tried another option in os_unix.c file which sqlite uses for
DJGPP in which the fcntl() is re-defined as -
#define fcntl(A,B,C) 0
but this gives another error message related to OS not able support
large file formats.
Could you please suggest some other alternative(s) except for
reconfiguring NFS server - this is something we just can't do. Thanks
for your help.
Regards,
Ritesh
On Mon, 2006-01-30 at 19:31, [EMAIL PROTECTED] wrote:
Ritesh Kapoor [EMAIL PROTECTED] wrote:
Yes.
My machine has NFS and the machines I log onto also have NFS. But if
this is the problem then why dosen't it appear on my machine when I run
the app.
Perhaps you are using a local filesystem when you run on
your machine. Or perhaps NFS is configured properly on
your machine but not on the other machines.
Is there a workaround for this? without having to change the file system
from NFS.
Yes. Configure your NFS so that file locking works correctly.
--
D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] Database Locked Error
Regarding the configuration of NFS -
I have two machines with NFS on them.
- if i run the app on machine 1 it works properly
- now when i run the app on machine 2 it works properly
But if I login to machine 2 from machine 1 and then run the app I get
the 'database is locked' error message.
The sequence of statements are -
1 if((fp = fopen(fullFileName.c_str(),w+)))
2 {
3 fclose(fp);
4 }
5 else
6 {
7 printf(Error creating file);
8 return;
9 }
10 int res = sqlite3_open(fullFileName.c_str(), _db);
11 if( res != SQLITE_OK )
12 {
13 printf(Error opending DB);
14 sqlite3_close(_db);
15 return;
16 }
17
18 sqlite3_trace(getDB(),logSqlQuery,sqlLogList);
19 string query = create table XYZ;
20 res = sqlite3_exec(_db, query.c_str(), NULL , 0, errmsg);
21
22 if(res == 5)
23
After line #20 the value of 'res' is 5.
I've even tried to run the app after removing line #18 which contains
the sqlite3_trace() fn call. But that dosen't seem to be the cause.
I've gone through the mail archive of this list and saw that this
problem did appear for others as well but there the solution was to
check if a previously executing sql statement was still not finished
with its job and a another sql statement was being executed.
Over here the first sql statement's execution returns this error.
Another assumption that I made was that the host machine locks the file
after I open a sqlite connection (line #10). So I should wait for it to
release this lock. I ran the app using GDB and after line #10 I paused
the app for some time hoping that the lock would go away but the problem
persists even after pausing for 10, 20 and 30 minutes in seperate runs.
If NFS is the problem then what configurations do I need to look at and
change to make my app work properly?
What if the problem is with the host machine locking the file or some
other process running on my machine or the host machine which locks the
file? Is this possible?
If yes then is there a way to find out which process is doing this
through the entries in /proc directory?
Thanks,
Ritesh
On Mon, 2006-01-30 at 19:31, [EMAIL PROTECTED] wrote:
Ritesh Kapoor [EMAIL PROTECTED] wrote:
Yes.
My machine has NFS and the machines I log onto also have NFS. But if
this is the problem then why dosen't it appear on my machine when I run
the app.
Perhaps you are using a local filesystem when you run on
your machine. Or perhaps NFS is configured properly on
your machine but not on the other machines.
Is there a workaround for this? without having to change the file system
from NFS.
Yes. Configure your NFS so that file locking works correctly.
--
D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] Database Locked Error
Please ommit line #18 from the sequence of statements
and then reply.
Thanks
Ritesh
On Tue, 2006-01-31 at 16:42, Ritesh Kapoor wrote:
Regarding the configuration of NFS -
I have two machines with NFS on them.
- if i run the app on machine 1 it works properly
- now when i run the app on machine 2 it works properly
But if I login to machine 2 from machine 1 and then run the app I get
the 'database is locked' error message.
The sequence of statements are -
1 if((fp = fopen(fullFileName.c_str(),w+)))
2 {
3 fclose(fp);
4 }
5 else
6 {
7 printf(Error creating file);
8 return;
9 }
10 int res = sqlite3_open(fullFileName.c_str(), _db);
11 if( res != SQLITE_OK )
12 {
13 printf(Error opending DB);
14 sqlite3_close(_db);
15 return;
16 }
17
18 sqlite3_trace(getDB(),logSqlQuery,sqlLogList);
19 string query = create table XYZ;
20 res = sqlite3_exec(_db, query.c_str(), NULL , 0, errmsg);
21
22 if(res == 5)
23
After line #20 the value of 'res' is 5.
I've even tried to run the app after removing line #18 which contains
the sqlite3_trace() fn call. But that dosen't seem to be the cause.
I've gone through the mail archive of this list and saw that this
problem did appear for others as well but there the solution was to
check if a previously executing sql statement was still not finished
with its job and a another sql statement was being executed.
Over here the first sql statement's execution returns this error.
Another assumption that I made was that the host machine locks the file
after I open a sqlite connection (line #10). So I should wait for it to
release this lock. I ran the app using GDB and after line #10 I paused
the app for some time hoping that the lock would go away but the problem
persists even after pausing for 10, 20 and 30 minutes in seperate runs.
If NFS is the problem then what configurations do I need to look at and
change to make my app work properly?
What if the problem is with the host machine locking the file or some
other process running on my machine or the host machine which locks the
file? Is this possible?
If yes then is there a way to find out which process is doing this
through the entries in /proc directory?
Thanks,
Ritesh
On Mon, 2006-01-30 at 19:31, [EMAIL PROTECTED] wrote:
Ritesh Kapoor [EMAIL PROTECTED] wrote:
Yes.
My machine has NFS and the machines I log onto also have NFS. But if
this is the problem then why dosen't it appear on my machine when I run
the app.
Perhaps you are using a local filesystem when you run on
your machine. Or perhaps NFS is configured properly on
your machine but not on the other machines.
Is there a workaround for this? without having to change the file system
from NFS.
Yes. Configure your NFS so that file locking works correctly.
--
D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] Database Locked Error
On Tue, 31 Jan 2006, Ritesh Kapoor wrote:
Regarding the configuration of NFS -
I have two machines with NFS on them.
- if i run the app on machine 1 it works properly
- now when i run the app on machine 2 it works properly
But if I login to machine 2 from machine 1 and then run the app I get
the 'database is locked' error message.
I assume this is using an NFS file from machine 2 from machine 1?
The sequence of statements are -
1 if((fp = fopen(fullFileName.c_str(),w+)))
2 {
3 fclose(fp);
4 }
5 else
6 {
7 printf(Error creating file);
8 return;
9 }
This bit is unnecessary. SQLite will create a file if it does not exist.
10 int res = sqlite3_open(fullFileName.c_str(), _db);
11 if( res != SQLITE_OK )
12 {
13 printf(Error opending DB);
14 sqlite3_close(_db);
_db will not be a valid handle if sqlite3_open failed.
15 return;
16 }
17
18 sqlite3_trace(getDB(),logSqlQuery,sqlLogList);
getDB()? Does this return _db?
19 string query = create table XYZ;
20 res = sqlite3_exec(_db, query.c_str(), NULL , 0, errmsg);
This example won't work as create table XYZ isn't a valid complete SQL
statement.
21
22 if(res == 5)
23
After line #20 the value of 'res' is 5.
I've even tried to run the app after removing line #18 which contains
the sqlite3_trace() fn call. But that dosen't seem to be the cause.
I've gone through the mail archive of this list and saw that this
problem did appear for others as well but there the solution was to
check if a previously executing sql statement was still not finished
with its job and a another sql statement was being executed.
Over here the first sql statement's execution returns this error.
Could be that the incomplete statement is causing problems.
Another assumption that I made was that the host machine locks the file
after I open a sqlite connection (line #10). So I should wait for it to
release this lock. I ran the app using GDB and after line #10 I paused
the app for some time hoping that the lock would go away but the problem
persists even after pausing for 10, 20 and 30 minutes in seperate runs.
If NFS is the problem then what configurations do I need to look at and
change to make my app work properly?
Locking is handled by rpc.lockd(8). It should be started by default.
What if the problem is with the host machine locking the file or some
other process running on my machine or the host machine which locks the
file? Is this possible?
Possible. Check your on both machines if the file is in use.
If yes then is there a way to find out which process is doing this
through the entries in /proc directory?
If on Linux, try using fuser(1) to see if other processes have the file
open, and lsof(8) to see if the file has locks associated with it.
Thanks,
Ritesh
On Mon, 2006-01-30 at 19:31, [EMAIL PROTECTED] wrote:
Ritesh Kapoor [EMAIL PROTECTED] wrote:
Yes.
My machine has NFS and the machines I log onto also have NFS. But if
this is the problem then why dosen't it appear on my machine when I run
the app.
Perhaps you are using a local filesystem when you run on
your machine. Or perhaps NFS is configured properly on
your machine but not on the other machines.
Is there a workaround for this? without having to change the file system
from NFS.
Yes. Configure your NFS so that file locking works correctly.
--
D. Richard Hipp [EMAIL PROTECTED]
--
/\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
Re: [sqlite] Database Locked Error
I've tried to copy/paste and simplify the code.
Please assume that the sql query on line #19 is correct, its actually
longer so i truncated it. getDB() returns _db.
I checked the value of _db after the sqlite3_open() fn call its a valid
handle.
I'm trying to understand fuser and lsof commands and will get back to
you.
Thanks for you help. If you have any other suggestions then please do
let me know
Thanks
Ritesh
On Tue, 2006-01-31 at 18:02, Christian Smith wrote:
On Tue, 31 Jan 2006, Ritesh Kapoor wrote:
Regarding the configuration of NFS -
I have two machines with NFS on them.
- if i run the app on machine 1 it works properly
- now when i run the app on machine 2 it works properly
But if I login to machine 2 from machine 1 and then run the app I get
the 'database is locked' error message.
I assume this is using an NFS file from machine 2 from machine 1?
The sequence of statements are -
1 if((fp = fopen(fullFileName.c_str(),w+)))
2 {
3 fclose(fp);
4 }
5 else
6 {
7 printf(Error creating file);
8 return;
9 }
This bit is unnecessary. SQLite will create a file if it does not exist.
10 int res = sqlite3_open(fullFileName.c_str(), _db);
11 if( res != SQLITE_OK )
12 {
13 printf(Error opending DB);
14 sqlite3_close(_db);
_db will not be a valid handle if sqlite3_open failed.
15 return;
16 }
17
18 sqlite3_trace(getDB(),logSqlQuery,sqlLogList);
getDB()? Does this return _db?
19 string query = create table XYZ;
20 res = sqlite3_exec(_db, query.c_str(), NULL , 0, errmsg);
This example won't work as create table XYZ isn't a valid complete SQL
statement.
21
22 if(res == 5)
23
After line #20 the value of 'res' is 5.
I've even tried to run the app after removing line #18 which contains
the sqlite3_trace() fn call. But that dosen't seem to be the cause.
I've gone through the mail archive of this list and saw that this
problem did appear for others as well but there the solution was to
check if a previously executing sql statement was still not finished
with its job and a another sql statement was being executed.
Over here the first sql statement's execution returns this error.
Could be that the incomplete statement is causing problems.
Another assumption that I made was that the host machine locks the file
after I open a sqlite connection (line #10). So I should wait for it to
release this lock. I ran the app using GDB and after line #10 I paused
the app for some time hoping that the lock would go away but the problem
persists even after pausing for 10, 20 and 30 minutes in seperate runs.
If NFS is the problem then what configurations do I need to look at and
change to make my app work properly?
Locking is handled by rpc.lockd(8). It should be started by default.
What if the problem is with the host machine locking the file or some
other process running on my machine or the host machine which locks the
file? Is this possible?
Possible. Check your on both machines if the file is in use.
If yes then is there a way to find out which process is doing this
through the entries in /proc directory?
If on Linux, try using fuser(1) to see if other processes have the file
open, and lsof(8) to see if the file has locks associated with it.
Thanks,
Ritesh
On Mon, 2006-01-30 at 19:31, [EMAIL PROTECTED] wrote:
Ritesh Kapoor [EMAIL PROTECTED] wrote:
Yes.
My machine has NFS and the machines I log onto also have NFS. But if
this is the problem then why dosen't it appear on my machine when I run
the app.
Perhaps you are using a local filesystem when you run on
your machine. Or perhaps NFS is configured properly on
your machine but not on the other machines.
Is there a workaround for this? without having to change the file system
from NFS.
Yes. Configure your NFS so that file locking works correctly.
--
D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] Database Locked Error
Ritesh Kapoor [EMAIL PROTECTED] wrote: Can anyone suggest what the problem is with sqlite when running apps on different machines. This happens sometimes when NFS us misconfigured so that it does not support fcntl() file locks. The lock requests always fail, hence SQLite always returns database is locked. Are you using NFS? -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] Database Locked Error
Yes. My machine has NFS and the machines I log onto also have NFS. But if this is the problem then why dosen't it appear on my machine when I run the app. Is there a workaround for this? without having to change the file system from NFS. Thanks, Ritesh On Mon, 2006-01-30 at 18:12, [EMAIL PROTECTED] wrote: Ritesh Kapoor [EMAIL PROTECTED] wrote: Can anyone suggest what the problem is with sqlite when running apps on different machines. This happens sometimes when NFS us misconfigured so that it does not support fcntl() file locks. The lock requests always fail, hence SQLite always returns database is locked. Are you using NFS? -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] Database Locked Error
Ritesh Kapoor [EMAIL PROTECTED] wrote: Yes. My machine has NFS and the machines I log onto also have NFS. But if this is the problem then why dosen't it appear on my machine when I run the app. Perhaps you are using a local filesystem when you run on your machine. Or perhaps NFS is configured properly on your machine but not on the other machines. Is there a workaround for this? without having to change the file system from NFS. Yes. Configure your NFS so that file locking works correctly. -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] Database locked after crash
Can you post code? --- Jaap Krabbendam [EMAIL PROTECTED] wrote: Hi, I have been simulating a crash during a transaction. After BEGIN, at some point I do exit(-1) instead of COMMIT or ROLLBACK in order to simulate a crash. After that, I can see that a -journal file is present. If I restart my executable, it seems that the changes of the transaction are made undone (which is as expected). The journal file however is not removed. Furthermore, if I try to do the same operation again (BEGIN + some changes), I get an SQL_BUSY error code on the first record change (UPDATE/SET). I have the feeling that the OS still has a lock on the database. Any ideas on how to prevent this or on how to recover from this situation? I am using the following setup: -sqlite-3.2.1 -linux/i686/2.6.9-1.667smp -application using posix threads. Only one thread is accessing the database. Thanks, J.J. Krabbendam __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/
Re: [sqlite] Database locked after crash
On Tue, 17 May 2005, Jaap Krabbendam wrote:
I have been simulating a crash during a transaction. After BEGIN, at some
point I do exit(-1) instead of COMMIT or ROLLBACK in order to simulate a
crash.
After that, I can see that a -journal file is present. If I restart my
executable, it seems that the changes of the transaction are made undone
(which is as expected). The journal file however is not removed.
Furthermore, if I try to do the same operation again (BEGIN + some changes),
I get an SQL_BUSY error code on the first record change (UPDATE/SET).
run fuser on the db and see who has it open. this cannot happen unless:
- some other process holds the lock (eg. you are using fastcgi or mod_ruby
and some other process is locking the db)
- your db in on nfs and you setup is fubar. btw. i've never seen a
__correct__ nfs setup. when incorrect locks can get hung on the server
side.
- there is a kernel bug.
I have the feeling that the OS still has a lock on the database. Any ideas on
how to prevent this or on how to recover from this situation?
again - unless there is a kernel bug (which i doubt as we are using sqlite
on
many of our linux systems running 100,000's of transactions, even on nfs, with
zero issues in 3 years) the most likely explaination is that another process
does, in fact, hold the lock.
I am using the following setup:
-sqlite-3.2.1
-linux/i686/2.6.9-1.667smp
-application using posix threads. Only one thread is accessing the database.
threads and fcntl based locks do not work as you might expect. your process
will go into uninterruptable sleep on the call to fcntl if it blocks and this
stops all threads. what do you mean 'only one posix thread' since ruby
threads are green and not posix?? you mean you have another application using
posixthreads in addition to your rails app? if so that's certainly the
process holding the lock.
if your db is not on nfs this code will show you how to tell which process
holds the lock:
jib:~ cat a.rb
#
# http://raa.ruby-lang.org/project/posixlock/
# http://www.codeforpeople.com/lib/ruby/posixlock/
#
require 'posixlock'
path = ARGV.shift || __FILE__
File::chmod 0700, path
f = open path, 'r+'
if fork
ret = f.lockf File::F_LOCK, 0
pid = Process::pid
puts parent #{ pid } holds lock on #{ f.path }
sleep 2
else
sleep 1
ret = f.lockf File::F_TEST, 0
ppid = ret
pid = Process::pid
puts child #{ pid } cannot lock #{ f.path } because pid #{ ppid } holds
lock
exit
end
jib:~ ruby a.rb
parent 23833 holds lock on a.rb
child 23834 cannot lock a.rb because pid 23833 holds lock
so a simple script like
require 'posixlock'
path = open ARGV.shift 'r+'
ret = f.lockf File::F_TEST, 0
unless ret.zero?
puts process #{ ret } holds lock on #{ path }
else
puts lock on #{ path } available
end
man fcntl will explain all this further.
kind regards.
-a
--
===
| email :: ara [dot] t [dot] howard [at] noaa [dot] gov
| phone :: 303.497.6469
| renunciation is not getting rid of the things of this world, but accepting
| that they pass away. --aitken roshi
===
Re: [sqlite] Database locked
I am trying to import a csv file (ip-to-country to be especific) to a table on sqlite db. Version 2.8: COPY tablename FROM 'filename' USING DELIMITERS ','; You weren't a Clipper/XBase programmer at one time, were you? :-
Re: [sqlite] Database locked
Brass Tilde wrote: Version 2.8: COPY tablename FROM 'filename' USING DELIMITERS ','; You weren't a Clipper/XBase programmer at one time, were you? :- Never. The COPY command in version 2.8 was taken from PostgreSQL. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Database locked
Thank you!! Was just wondering, does it escape the double quotes around the data?? Thanx again Felipe Lopes Em 11 Aug 2004, [EMAIL PROTECTED] escreveu: Felipe Lopes wrote: I am trying to import a csv file (ip-to-country to be especific) to a table on sqlite db. Version 2.8: COPY FROM ' ' USING DELIMITERS ','; Version 3.0: .mode csv .import -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 -- _ Voce quer um iGMail protegido contra vírus e spams? Clique aqui: http://www.igmailseguro.ig.com.br Ofertas imperdíveis! Link: http://www.americanas.com.br/ig/
[sqlite] Database locked
Hi there! I am trying to import a csv file (ip-to-country to be especific) to a table on sqlite db...It has 5+ lines. Tried to use php and it takes like 10 minutes loading and returns that the database is locked...When I take a look at the table, it has only 9000 entries... I would like to know how can I import this csv file to sqlite, either w/ php or command line. If thru command line is the only way, some explanation on what should I get to have it working on my debian is welcome. Thanx in Advance Felipe Lopes _ Voce quer um iGMail protegido contra vírus e spams? Clique aqui: http://www.igmailseguro.ig.com.br Ofertas imperdíveis! Link: http://www.americanas.com.br/ig/
Re: [sqlite] Database locked
I am trying to import a csv file (ip-to-country to be especific) to a table on sqlite db...It has 5+ lines. Tried to use php and it takes like If you are using SQLite 2.8.x you can try importing the file with SQLite Database Browser (sqlitebrowser.sourceforge.net). Use the FILE-IMPORT-CVS TO TABLE menu item. Give it a few minutes to insert all data, it should work. Regards, Mauricio Tabuleiro
