Re: [sqlite] Begin immediate transaction -> SQLITE_BUSY (database is locked)

[Dan Kennedy]

On 10/18/2012 09:05 PM, Pavel Ivanov wrote:

On Thu, Oct 18, 2012 at 6:32 AM, Daniel Polski  wrote:

The SELECT statement, including the _prepare() stage and all the _step()s
until you've reached the last row, and then the _finalize(), is all one
process.  They're all part of the statement and you can assume that the
database is still locked until you do a _finalize().

If you are using the results of a SELECT to figure out a bunch of other
instructions like INSERT or DELETE you can do it two ways:

A) Quicky save the results of stepping into an array.  Once the SELECT is
finalized, look through the array and figure out what you want to do about
it.

B) Generate the INSERT/DELETE commands while stepping but keep them
somewhere, either as a data array or by accumulating the text of the SQL
commands in a string, rather than executing them immediately.  Once you're
finished stepping, execute the commands.  (You may choose to use _exec to
execute them all in one go.)




Thank you for the clarification, but I still misunderstand the documentation
some way.

In the documentation about WAL mode it says:
"Writers merely append new content to the end of the WAL file. Because
writers do nothing that would interfere with the actions of readers, writers
and readers can run at the same time. However, since there is only one WAL
file, there can only be one writer at a time."

Maybe the magic words I don't fully understand are what's written later:
"A checkpoint operation takes content from the WAL file and transfers it
back into the original database file. A checkpoint can run concurrently with
readers, however the checkpoint must stop when it reaches a page in the WAL
that is past the read mark of any current reader. The checkpoint has to stop
at that point because otherwise it might overwrite part of the database file
that the reader is actively using. The checkpoint remembers (in the
wal-index) how far it got and will resume transferring content from the WAL
to the database from where it left off on the next invocation."

I logically do understand that there can't be 2 writers updating the
database at the same time, but I don't understand why the second insert
statement in the example below won't work without finalizing the SELECT
query?


sqlite3* conn_1;
sqlite3* conn_2;
//(..opening db files and so on)

sqlite3_stmt* pVM_1;
sqlite3_stmt* pVM_2;

sqlite3_prepare(conn_1, "SELECT * FROM test_table_2;", -1,_1,);
//sets WAL end mark for pVM_1?
sqlite3_prepare(conn_2, "SELECT * FROM test_table_2;", -1,_2,);
//sets WAL end mark for pVM_2?

nRet = sqlite3_step(pVM_1); //stepping if<  WAL end mark set for pVM_1, set
new read mark?
nRet = sqlite3_step(pVM_2); //stepping if<  WAL end mark set for pVM_2, set
new read mark?

//statements below will add content to the end of the WAL file?
nRet = sqlite3_exec(conn_1, "BEGIN IMMEDIATE TRANSACTION;", 0, 0,);
nRet = sqlite3_exec(conn_1, "INSERT INTO test_table_1 VALUES(1, 1);", 0, 0,
);
nRet = sqlite3_exec(conn_1, "COMMIT;", 0, 0,);

nRet = sqlite3_step(pVM_1); //stepping if<  WAL end mark set for pVM_1, set
new read mark?
nRet = sqlite3_step(pVM_2); //stepping if<  WAL end mark set for pVM_2, set
new read mark?

//sqlite3_finalize(pVM_1);
//sqlite3_finalize(pVM_2);

//The execution below will fail with SQLITE_BUSY if the SELECT statement
pVM_2 isn't finalized
//(Why won't it append new data in the end of the WAL file just like the
successful insert above?)
nRet = sqlite3_exec(conn_2, "BEGIN IMMEDIATE TRANSACTION;", 0, 0,);
nRet = sqlite3_exec(conn_2, "INSERT INTO test_table_1 VALUES(1, 1);", 0, 0,
);
nRet = sqlite3_exec(conn_2, "COMMIT;", 0, 0,);


When INSERT is executed it doesn't add new row to the WAL file, it
have to add new row to some database page possibly changing some other
pages on the way too. These changed pages are written into WAL file.
Because of this fact when INSERT statement is executed it must be
executed on the latest version of the database pages. Otherwise it can
create an alternative database version that can't be merged with
version created by other INSERT statements. So when you execute INSERT
statement, or start IMMEDIATE transaction, or convert existing
read-only transaction into writing transaction SQLite have to make
sure that this transaction sees the latest version of the database.
But when you start read-only transaction SQLite ensures that all the
way through it sees a consistent database state, and that would be the
state of the database at the beginning of the transaction. So when you
start executing SELECT statement you lock conn_2 into the database
state that was at that moment. Then you do database changes on conn_1,
so database state is changed. Then you want to execute changes in
conn_2, but it can't do that because it's locked into database state
which is not latest one. And it can't change the visible database
state because SELECT statement is still in progress.


Exactly. To successfully upgrade a 

Re: [sqlite] Begin immediate transaction -> SQLITE_BUSY (database is locked)

[Pavel Ivanov]

On Thu, Oct 18, 2012 at 6:32 AM, Daniel Polski  wrote:
>> The SELECT statement, including the _prepare() stage and all the _step()s
>> until you've reached the last row, and then the _finalize(), is all one
>> process.  They're all part of the statement and you can assume that the
>> database is still locked until you do a _finalize().
>>
>> If you are using the results of a SELECT to figure out a bunch of other
>> instructions like INSERT or DELETE you can do it two ways:
>>
>> A) Quicky save the results of stepping into an array.  Once the SELECT is
>> finalized, look through the array and figure out what you want to do about
>> it.
>>
>> B) Generate the INSERT/DELETE commands while stepping but keep them
>> somewhere, either as a data array or by accumulating the text of the SQL
>> commands in a string, rather than executing them immediately.  Once you're
>> finished stepping, execute the commands.  (You may choose to use _exec to
>> execute them all in one go.)
>>
>>
>
> Thank you for the clarification, but I still misunderstand the documentation
> some way.
>
> In the documentation about WAL mode it says:
> "Writers merely append new content to the end of the WAL file. Because
> writers do nothing that would interfere with the actions of readers, writers
> and readers can run at the same time. However, since there is only one WAL
> file, there can only be one writer at a time."
>
> Maybe the magic words I don't fully understand are what's written later:
> "A checkpoint operation takes content from the WAL file and transfers it
> back into the original database file. A checkpoint can run concurrently with
> readers, however the checkpoint must stop when it reaches a page in the WAL
> that is past the read mark of any current reader. The checkpoint has to stop
> at that point because otherwise it might overwrite part of the database file
> that the reader is actively using. The checkpoint remembers (in the
> wal-index) how far it got and will resume transferring content from the WAL
> to the database from where it left off on the next invocation."
>
> I logically do understand that there can't be 2 writers updating the
> database at the same time, but I don't understand why the second insert
> statement in the example below won't work without finalizing the SELECT
> query?
>
>
> sqlite3* conn_1;
> sqlite3* conn_2;
> //(..opening db files and so on)
>
> sqlite3_stmt* pVM_1;
> sqlite3_stmt* pVM_2;
>
> sqlite3_prepare(conn_1, "SELECT * FROM test_table_2;", -1, _1, );
> //sets WAL end mark for pVM_1?
> sqlite3_prepare(conn_2, "SELECT * FROM test_table_2;", -1, _2, );
> //sets WAL end mark for pVM_2?
>
> nRet = sqlite3_step(pVM_1); //stepping if < WAL end mark set for pVM_1, set
> new read mark?
> nRet = sqlite3_step(pVM_2); //stepping if < WAL end mark set for pVM_2, set
> new read mark?
>
> //statements below will add content to the end of the WAL file?
> nRet = sqlite3_exec(conn_1, "BEGIN IMMEDIATE TRANSACTION;", 0, 0, );
> nRet = sqlite3_exec(conn_1, "INSERT INTO test_table_1 VALUES(1, 1);", 0, 0,
> );
> nRet = sqlite3_exec(conn_1, "COMMIT;", 0, 0, );
>
> nRet = sqlite3_step(pVM_1); //stepping if < WAL end mark set for pVM_1, set
> new read mark?
> nRet = sqlite3_step(pVM_2); //stepping if < WAL end mark set for pVM_2, set
> new read mark?
>
> //sqlite3_finalize(pVM_1);
> //sqlite3_finalize(pVM_2);
>
> //The execution below will fail with SQLITE_BUSY if the SELECT statement
> pVM_2 isn't finalized
> //(Why won't it append new data in the end of the WAL file just like the
> successful insert above?)
> nRet = sqlite3_exec(conn_2, "BEGIN IMMEDIATE TRANSACTION;", 0, 0, );
> nRet = sqlite3_exec(conn_2, "INSERT INTO test_table_1 VALUES(1, 1);", 0, 0,
> );
> nRet = sqlite3_exec(conn_2, "COMMIT;", 0, 0, );

When INSERT is executed it doesn't add new row to the WAL file, it
have to add new row to some database page possibly changing some other
pages on the way too. These changed pages are written into WAL file.
Because of this fact when INSERT statement is executed it must be
executed on the latest version of the database pages. Otherwise it can
create an alternative database version that can't be merged with
version created by other INSERT statements. So when you execute INSERT
statement, or start IMMEDIATE transaction, or convert existing
read-only transaction into writing transaction SQLite have to make
sure that this transaction sees the latest version of the database.
But when you start read-only transaction SQLite ensures that all the
way through it sees a consistent database state, and that would be the
state of the database at the beginning of the transaction. So when you
start executing SELECT statement you lock conn_2 into the database
state that was at that moment. Then you do database changes on conn_1,
so database state is changed. Then you want to execute changes in
conn_2, but it can't do that because it's locked into database state
which is not latest one. And it 

Re: [sqlite] Begin immediate transaction -> SQLITE_BUSY (database is locked)

[Simon Slavin]

On 18 Oct 2012, at 2:32pm, Daniel Polski  wrote:

> I logically do understand that there can't be 2 writers updating the database 
> at the same time, but I don't understand why the second insert statement in 
> the example below won't work without finalizing the SELECT query?

The fact that changes are initially made to a journal file (maybe a WAL file) 
and only moved to the normal database file later is a red herring.  Similarly, 
where in the file changes are made (whether they modify existing pages or 
create new ones) is a red herring.  You should consider all the files that make 
up a database to be one consistent whole.

A SELECT needs to keep the database locked against changes.  Because the 
results of the SELECT must reflect the data as it was when the command was 
executed.  So until the SELECT is finalized (or has produced an error), no 
changes can be made to the data because that might make the results of the 
SELECT inconsistent.

So you can run multiple SELECTs at one time, since there's no way for one 
SELECT to invalidate the data returned by another.  But as soon as someone 
tries a command that would change the file, it has to be blocked, because all 
SELECTs under way must be allowed to finish before anything is allowed to make 
changes.

So once again, consider all the steps involved in a statement: _prepare(), 
_step(), and _finalize() to be part of the same process, and once you've 
started it, finish it as quickly as possible.  Earlier versions didn't even 
split this up into three statements: they provided just _exec() which does all 
three.  It might be best to continue thinking of SQLite in this way.

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

Re: [sqlite] Begin immediate transaction -> SQLITE_BUSY (database is locked)

[Daniel Polski]

The SELECT statement, including the _prepare() stage and all the _step()s until 
you've reached the last row, and then the _finalize(), is all one process.  
They're all part of the statement and you can assume that the database is still 
locked until you do a _finalize().

If you are using the results of a SELECT to figure out a bunch of other 
instructions like INSERT or DELETE you can do it two ways:

A) Quicky save the results of stepping into an array.  Once the SELECT is 
finalized, look through the array and figure out what you want to do about it.

B) Generate the INSERT/DELETE commands while stepping but keep them somewhere, 
either as a data array or by accumulating the text of the SQL commands in a 
string, rather than executing them immediately.  Once you're finished stepping, 
execute the commands.  (You may choose to use _exec to execute them all in one 
go.)




Thank you for the clarification, but I still misunderstand the 
documentation some way.


In the documentation about WAL mode it says:
"Writers merely append new content to the end of the WAL file. Because 
writers do nothing that would interfere with the actions of readers, 
writers and readers can run at the same time. However, since there is 
only one WAL file, there can only be one writer at a time."


Maybe the magic words I don't fully understand are what's written later:
"A checkpoint operation takes content from the WAL file and transfers it 
back into the original database file. A checkpoint can run concurrently 
with readers, however the checkpoint must stop when it reaches a page in 
the WAL that is past the read mark of any current reader. The checkpoint 
has to stop at that point because otherwise it might overwrite part of 
the database file that the reader is actively using. The checkpoint 
remembers (in the wal-index) how far it got and will resume transferring 
content from the WAL to the database from where it left off on the next 
invocation."


I logically do understand that there can't be 2 writers updating the 
database at the same time, but I don't understand why the second insert 
statement in the example below won't work without finalizing the SELECT 
query?



sqlite3* conn_1;
sqlite3* conn_2;
//(..opening db files and so on)

sqlite3_stmt* pVM_1;
sqlite3_stmt* pVM_2;

sqlite3_prepare(conn_1, "SELECT * FROM test_table_2;", -1, _1, 
); //sets WAL end mark for pVM_1?
sqlite3_prepare(conn_2, "SELECT * FROM test_table_2;", -1, _2, 
); //sets WAL end mark for pVM_2?


nRet = sqlite3_step(pVM_1); //stepping if < WAL end mark set for pVM_1, 
set new read mark?
nRet = sqlite3_step(pVM_2); //stepping if < WAL end mark set for pVM_2, 
set new read mark?


//statements below will add content to the end of the WAL file?
nRet = sqlite3_exec(conn_1, "BEGIN IMMEDIATE TRANSACTION;", 0, 0, );
nRet = sqlite3_exec(conn_1, "INSERT INTO test_table_1 VALUES(1, 1);", 0, 
0, );

nRet = sqlite3_exec(conn_1, "COMMIT;", 0, 0, );

nRet = sqlite3_step(pVM_1); //stepping if < WAL end mark set for pVM_1, 
set new read mark?
nRet = sqlite3_step(pVM_2); //stepping if < WAL end mark set for pVM_2, 
set new read mark?


//sqlite3_finalize(pVM_1);
//sqlite3_finalize(pVM_2);

//The execution below will fail with SQLITE_BUSY if the SELECT statement 
pVM_2 isn't finalized
//(Why won't it append new data in the end of the WAL file just like the 
successful insert above?)

nRet = sqlite3_exec(conn_2, "BEGIN IMMEDIATE TRANSACTION;", 0, 0, );
nRet = sqlite3_exec(conn_2, "INSERT INTO test_table_1 VALUES(1, 1);", 0, 
0, );

nRet = sqlite3_exec(conn_2, "COMMIT;", 0, 0, );


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

Re: [sqlite] Begin immediate transaction -> SQLITE_BUSY (database is locked)

[Simon Slavin]

On 18 Oct 2012, at 10:55am, Daniel Polski  wrote:

> What if I create the SELECT sqlite3_stmt and want to step through the data to 
> evalute if an insert is needed?
> If I find a matching row and create another sqlite3_stmt (INSERT) it will 
> convert the SELECT statement to a write transaction? ...Which would make all 
> other threads unable continue without restarting "from the top"? (No need to 
> continue search through their SELECT statements if they cannot begin an 
> INSERT transaction anyway).
> 
> From what I understand you mean this is what happens (I don't think I 
> understood correctly though):
> 
> 1. Connection A creates a read statement for table2, table3.
> 2. Connection B creates a read statement for table2, table3.
> 3. Connection A steps through the data and creates a write statement to 
> begin->insert->commit data to table1, success. (This also changes Connection 
> A's read in step 1 to a write statement.)
> 4. Connection B steps through the data and tries to begin, fails since it 
> can't convert the read at step 2 to a write transaction since Connection A's 
> statement in step 1 now is a write transaction?
> 
> Why does it try to convert the read transaction (table2, table3) to a write 
> transaction when another prepared statment inserts data to an unrelated table 
> (table1)?

The SELECT statement, including the _prepare() stage and all the _step()s until 
you've reached the last row, and then the _finalize(), is all one process.  
They're all part of the statement and you can assume that the database is still 
locked until you do a _finalize().

If you are using the results of a SELECT to figure out a bunch of other 
instructions like INSERT or DELETE you can do it two ways:

A) Quicky save the results of stepping into an array.  Once the SELECT is 
finalized, look through the array and figure out what you want to do about it.

B) Generate the INSERT/DELETE commands while stepping but keep them somewhere, 
either as a data array or by accumulating the text of the SQL commands in a 
string, rather than executing them immediately.  Once you're finished stepping, 
execute the commands.  (You may choose to use _exec to execute them all in one 
go.)

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

Re: [sqlite] Begin immediate transaction -> SQLITE_BUSY (database is locked)

[Daniel Polski]

Pavel Ivanov skrev 2012-10-17 16:08:

The problem is you are starting read-only transaction by executing
SELECT and then try to convert this transaction into writing one by
executing BEGIN IMMEDIATE. If in such situation SQLITE_BUSY is
returned you have to finish the transaction and start it again. In
your code solution is easy: finalize SELECT statement before executing
BEGIN IMMEDIATE.


What if I create the SELECT sqlite3_stmt and want to step through the 
data to evalute if an insert is needed?
If I find a matching row and create another sqlite3_stmt (INSERT) it 
will convert the SELECT statement to a write transaction? ...Which would 
make all other threads unable continue without restarting "from the 
top"? (No need to continue search through their SELECT statements if 
they cannot begin an INSERT transaction anyway).


From what I understand you mean this is what happens (I don't think I 
understood correctly though):


1. Connection A creates a read statement for table2, table3.
2. Connection B creates a read statement for table2, table3.
3. Connection A steps through the data and creates a write statement to 
begin->insert->commit data to table1, success. (This also changes 
Connection A's read in step 1 to a write statement.)
4. Connection B steps through the data and tries to begin, fails since 
it can't convert the read at step 2 to a write transaction since 
Connection A's statement in step 1 now is a write transaction?


Why does it try to convert the read transaction (table2, table3) to a 
write transaction when another prepared statment inserts data to an 
unrelated table (table1)?


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

Re: [sqlite] Begin immediate transaction -> SQLITE_BUSY (database is locked)

[Pavel Ivanov]

The problem is you are starting read-only transaction by executing
SELECT and then try to convert this transaction into writing one by
executing BEGIN IMMEDIATE. If in such situation SQLITE_BUSY is
returned you have to finish the transaction and start it again. In
your code solution is easy: finalize SELECT statement before executing
BEGIN IMMEDIATE.

Pavel

On Wed, Oct 17, 2012 at 6:12 AM, Daniel Polski  wrote:
> Hello again,
> Attached is a test application which replicates the problem.
>
> I expected the transactions to block each other exactly like they do in the
> beginning (one connection successfully begins and the other receives
> SQLITE_BUSY), but I didn't expect the blocked connection to never get
> unlocked in the end. What's holding the lock on the database so that the
> "begin" can't proceed?
>
> Sample console output:
>
> sqlite3_libversion: 3.7.13
> sqlite3_sourceid: 2012-06-11 02:05:22
> f5b5a13f7394dc143aa136f1d4faba6839eaa6dc
> sqlite3_libversion_number: 3007013
> sqlite3_threadsafe: 1
> Creating thread 0
> Creating thread 1
>
> conn addrstatusquery
> ----
> 0x6a6278SuccessPRAGMA journal_mode=wal;
> 0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
> 0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) VALUES (
> 1, 2);
> 0x6a6278SuccessCOMMIT;
> 0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
> 0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) VALUES (
> 1, 2);
> 0x6a6278SuccessCOMMIT;
> 0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
> 0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) VALUES (
> 1, 2);
> 0x6bd678SuccessPRAGMA journal_mode=wal;
> 0x6bd678FailedBEGIN IMMEDIATE TRANSACTION; (return code: 5)
> 0x6a6278SuccessCOMMIT;
> 0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
> 0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) VALUES (
> 1, 2);
> 0x6a6278SuccessCOMMIT;
> 0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
> 0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) VALUES (
> 1, 2);
> 0x6a6278SuccessCOMMIT;
> 0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
> 0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) VALUES (
> 1, 2);
> 0x6a6278SuccessCOMMIT;
> 0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
> 0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) VALUES (
> 1, 2);
> 0x6a6278SuccessCOMMIT;
> 0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
> 0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) VALUES (
> 1, 2);
> 0x6a6278SuccessCOMMIT;
> 0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
> 0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) VALUES (
> 1, 2);
> 0x6a6278SuccessCOMMIT;
> 0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
> 0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) VALUES (
> 1, 2);
> 0x6a6278SuccessCOMMIT;
> 0x6bd678FailedBEGIN IMMEDIATE TRANSACTION; (return code: 5)
> 0x6bd678FailedBEGIN IMMEDIATE TRANSACTION; (return code: 5)
> (... And so on)
>
>
> Thank you in advance,
> Daniel
>
> ___
> 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] Begin immediate transaction -> SQLITE_BUSY (database is locked)

[Daniel Polski]

Hello again,
Attached is a test application which replicates the problem.

I expected the transactions to block each other exactly like they do in 
the beginning (one connection successfully begins and the other receives 
SQLITE_BUSY), but I didn't expect the blocked connection to never get 
unlocked in the end. What's holding the lock on the database so that the 
"begin" can't proceed?


Sample console output:

sqlite3_libversion: 3.7.13
sqlite3_sourceid: 2012-06-11 02:05:22 
f5b5a13f7394dc143aa136f1d4faba6839eaa6dc

sqlite3_libversion_number: 3007013
sqlite3_threadsafe: 1
Creating thread 0
Creating thread 1

conn addrstatusquery
----
0x6a6278SuccessPRAGMA journal_mode=wal;
0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) 
VALUES ( 1, 2);

0x6a6278SuccessCOMMIT;
0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) 
VALUES ( 1, 2);

0x6a6278SuccessCOMMIT;
0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) 
VALUES ( 1, 2);

0x6bd678SuccessPRAGMA journal_mode=wal;
0x6bd678FailedBEGIN IMMEDIATE TRANSACTION; (return code: 5)
0x6a6278SuccessCOMMIT;
0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) 
VALUES ( 1, 2);

0x6a6278SuccessCOMMIT;
0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) 
VALUES ( 1, 2);

0x6a6278SuccessCOMMIT;
0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) 
VALUES ( 1, 2);

0x6a6278SuccessCOMMIT;
0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) 
VALUES ( 1, 2);

0x6a6278SuccessCOMMIT;
0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) 
VALUES ( 1, 2);

0x6a6278SuccessCOMMIT;
0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) 
VALUES ( 1, 2);

0x6a6278SuccessCOMMIT;
0x6a6278SuccessBEGIN IMMEDIATE TRANSACTION;
0x6a6278SuccessINSERT INTO test_table_1 (test1, test2) 
VALUES ( 1, 2);

0x6a6278SuccessCOMMIT;
0x6bd678FailedBEGIN IMMEDIATE TRANSACTION; (return code: 5)
0x6bd678FailedBEGIN IMMEDIATE TRANSACTION; (return code: 5)
(... And so on)

Thank you in advance,
Daniel
#include 
#include 
#include 
#include 
#include 

#include "sqlite3.h"

#define DATABASE_PATH "/tmp/test.db"
#define THREAD_COUNT2

struct thread_data
{
int thread_id;
};

struct thread_data thread_data_array[THREAD_COUNT];

void execSQL(sqlite3* db, std::string query){
bool keep_trying = true;
while(keep_trying){
char* szError=0;
int return_code = sqlite3_exec(db, query.c_str(), 0, 0, 
);
if(return_code == SQLITE_OK){
std::cout<

Re: [sqlite] Begin immediate transaction -> SQLITE_BUSY (database is locked)

[Daniel Polski]

One of the other threads is writing at the same time.  SQLite only allows a
single writer at a time to a single database file.  Others have to wait in
line.

Your solution is that when you get an SQLITE_BUSY, delay for a short while
and then try again.  Keep trying until you break through.


Note what I do when the application detects that the database is locked:

SQL Error: SQLITE_BUSY[5]: database is locked
0x10d4f8waiting 1 sec <-
0x10d4f8begin immediate transaction
SQL Error: SQLITE_BUSY[5]: database is locked

This loop continue even when all other threads are done writing and 
waiting in an idle state.


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

Re: [sqlite] Begin immediate transaction -> SQLITE_BUSY (database is locked)

[Richard Hipp]

On Tue, Oct 16, 2012 at 4:33 AM, Daniel Polski wrote:

>
> Hello,
> I have a hard time finding the cause of a bug in my application. I believe
> that it's me doing something wrong and not sqlite since I can't reproduce
> the error in a simple example. I have 4 threads mostly reading data from a
> database, and all threads open "own" database connections. I've compiled
> with the 3.7.13 amalgamation C source file and I'm using journal_mode = wal
> and sqlite_threadsafe returns 1.
>
> Pseudo code showing my applications logic:
>
> while(keep_running){
> sqlite3_exec ( "SELECT a, b, c FROM table1, table2, table3, table4;");
> sqlite3_exec ( "SELECT d, e, f FROM table1, table2;");
> if( sqlite3_exec ( "BEGIN IMMEDIATE TRANSACTION;") == SQLITE_OK){
> sqlite3_exec ( "INSERT INTO table3 VALUES('1'));");
> sqlite3_exec ( "INSERT INTO table4 VALUES('1'));");
> sqlite3_exec ( "COMMIT;");
> }else{
> //failed to start transaction
> }
> }
>
> What confuses me is that the "BEGIN IMMEDIATE TRANSACTION" sometimes fails
> with a SQLITE_BUSY[5] (database is locked) after some iterations in the
> loop even though all previous actions so far has returned SQLITE_OK. All
> subsequent calls I do to try to begin the transaction again also fails. Any
> ideas how that can happen?
>

One of the other threads is writing at the same time.  SQLite only allows a
single writer at a time to a single database file.  Others have to wait in
line.

Your solution is that when you get an SQLITE_BUSY, delay for a short while
and then try again.  Keep trying until you break through.




>
> I've tried to print debug info from my threads to figure out what's
> locking the database, but to my eyes they seem to do what and I didn't
> expect the database file to get locked from this usage:
>
> threadaction
> 0x10edf8select
> 0x10edf8select
> 0x10edf8begin immediate transaction
> 0x10edf8insert
> 0x10edf8insert
> 0x10edf8commit
> (switched to another thread)
> 0x10d4f8select
> 0x10d4f8select
> 0x10d4f8begin immediate transaction
> 0x10d4f8insert
> 0x10d4f8insert
> 0x10d4f8commit
> 0x10d4f8select
> 0x10d4f8select
> 0x10d4f8begin immediate transaction
> SQL Error: SQLITE_BUSY[5]: database is locked
> 0x10d4f8waiting 1 sec
> 0x10d4f8begin immediate transaction
> SQL Error: SQLITE_BUSY[5]: database is locked
> ...
>
> Do you have any idea what can cause my problem? Do you have any
> suggestions about how I can dig deeper and debug better to find the real
> cause?
>
> Thank you in advance,
> Daniel
>
> __**_
> 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] Begin immediate transaction -> SQLITE_BUSY (database is locked)

[Daniel Polski]

Nothing obvious springs to mind but I do see that you are not checking the 
values returned by most of your sqlite_exec() calls.  Perhaps you could write a 
little routine that does the sqlite_exec() and then asserts that the value 
returned is SQLITE_OK.  It may be that it's actually one of the other calls 
which is getting the first indication of error.

Apart from that, and the fact that you don't need the semicolons everywhere, I 
don't see anything bad about your code.


Thanks for your reply. The example I wrote is just an example what the 
code does and the output from when the threads try to access the 
database and when the error occur (I've wrapped the database access in 
c++ try/catch in the real app). Is it possible to find out the reason 
for the lock some way? Any internal debug I can enable to help me figure 
out what's going on?


What I'll do now is to strip down my large application more and more 
until I hopefully find the cause of the problem, but I'm interested in 
knowing about good ways to debug sqlite processing anyway.


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

Re: [sqlite] Begin immediate transaction -> SQLITE_BUSY (database is locked)

[Simon Slavin]

On 16 Oct 2012, at 9:33am, Daniel Polski  wrote:

> What confuses me is that the "BEGIN IMMEDIATE TRANSACTION" sometimes fails 
> with a SQLITE_BUSY[5] (database is locked) after some iterations in the loop 
> even though all previous actions so far has returned SQLITE_OK. All 
> subsequent calls I do to try to begin the transaction again also fails. Any 
> ideas how that can happen?

Nothing obvious springs to mind but I do see that you are not checking the 
values returned by most of your sqlite_exec() calls.  Perhaps you could write a 
little routine that does the sqlite_exec() and then asserts that the value 
returned is SQLITE_OK.  It may be that it's actually one of the other calls 
which is getting the first indication of error.

Apart from that, and the fact that you don't need the semicolons everywhere, I 
don't see anything bad about your code.

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

[sqlite] Begin immediate transaction -> SQLITE_BUSY (database is locked)

[Daniel Polski]

Hello,
I have a hard time finding the cause of a bug in my application. I 
believe that it's me doing something wrong and not sqlite since I can't 
reproduce the error in a simple example. I have 4 threads mostly reading 
data from a database, and all threads open "own" database connections. 
I've compiled with the 3.7.13 amalgamation C source file and I'm using 
journal_mode = wal and sqlite_threadsafe returns 1.


Pseudo code showing my applications logic:

while(keep_running){
sqlite3_exec ( "SELECT a, b, c FROM table1, table2, table3, table4;");
sqlite3_exec ( "SELECT d, e, f FROM table1, table2;");
if( sqlite3_exec ( "BEGIN IMMEDIATE TRANSACTION;") == SQLITE_OK){
sqlite3_exec ( "INSERT INTO table3 VALUES('1'));");
sqlite3_exec ( "INSERT INTO table4 VALUES('1'));");
sqlite3_exec ( "COMMIT;");
}else{
//failed to start transaction
}
}

What confuses me is that the "BEGIN IMMEDIATE TRANSACTION" sometimes 
fails with a SQLITE_BUSY[5] (database is locked) after some iterations 
in the loop even though all previous actions so far has returned 
SQLITE_OK. All subsequent calls I do to try to begin the transaction 
again also fails. Any ideas how that can happen?


I've tried to print debug info from my threads to figure out what's 
locking the database, but to my eyes they seem to do what and I didn't 
expect the database file to get locked from this usage:


threadaction
0x10edf8select
0x10edf8select
0x10edf8begin immediate transaction
0x10edf8insert
0x10edf8insert
0x10edf8commit
(switched to another thread)
0x10d4f8select
0x10d4f8select
0x10d4f8begin immediate transaction
0x10d4f8insert
0x10d4f8insert
0x10d4f8commit
0x10d4f8select
0x10d4f8select
0x10d4f8begin immediate transaction
SQL Error: SQLITE_BUSY[5]: database is locked
0x10d4f8waiting 1 sec
0x10d4f8begin immediate transaction
SQL Error: SQLITE_BUSY[5]: database is locked
...

Do you have any idea what can cause my problem? Do you have any 
suggestions about how I can dig deeper and debug better to find the real 
cause?


Thank you in advance,
Daniel

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