Re: [sqlite] Problem with SQLITE_BUSY

[Normand Mongeau]

Yep OK. I stand corrected.

BTW thanks for your help.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: October-30-13 12:16 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Problem with SQLITE_BUSY

The documentation for BEGIN TRANSACTION
(http://www.sqlite.org/lang_transaction.html) does:

An implicit transaction (a transaction that is started automatically, not a
transaction started by BEGIN) is committed automatically when the last
active statement finishes. A statement finishes when its prepared statement
is reset or finalized.

The explicit COMMIT command runs immediately, even if there are pending
SELECT statements. However, if there are pending write operations, the
COMMIT command will fail with an error code SQLITE_BUSY.

The ROLLBACK will fail with an error code SQLITE_BUSY if there are any
pending queries. Both read-only and read/write queries will cause a ROLLBACK
to fail. A ROLLBACK must fail if there are pending read operations (unlike
COMMIT which can succeed) because bad things will happen if the in-memory
image of the database is changed out from under an active query.

Igor Tandetnik

On 10/30/2013 12:09 PM, Normand Mongeau wrote:
> Then IMO the documentation for either sqlite3_prepare_xx or 
> SQLITE_BUSY should state this. It would have been helpful in my case, 
> I was pulling my hair looking at the transaction model which was sound.
>
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
> Sent: October-30-13 12:07 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Problem with SQLITE_BUSY
>
>
> On 30 Oct 2013, at 4:03pm, Normand Mongeau <nmong...@theobjects.com>
wrote:
>
>> Well finally found the problem: a forgotten sqlite3_finalize() call.
>>
>> Very disturbing, I'd expect leakage, not the results I was seeing.
>
> SQLite has to keep the state of your SELECT available until you tell 
> it you're done with that SELECT.  After all, you might look at the 
> results you have retrieved and decide to make some changes based on what
you see there.
> This means SQLite has to stop everything from changing the database.
> Therefore it locks it.
>
> 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] Problem with SQLITE_BUSY

[Igor Tandetnik]

The documentation for BEGIN TRANSACTION 
(http://www.sqlite.org/lang_transaction.html) does:


An implicit transaction (a transaction that is started automatically, 
not a transaction started by BEGIN) is committed automatically when the 
last active statement finishes. A statement finishes when its prepared 
statement is reset or finalized.


The explicit COMMIT command runs immediately, even if there are pending 
SELECT statements. However, if there are pending write operations, the 
COMMIT command will fail with an error code SQLITE_BUSY.


The ROLLBACK will fail with an error code SQLITE_BUSY if there are any 
pending queries. Both read-only and read/write queries will cause a 
ROLLBACK to fail. A ROLLBACK must fail if there are pending read 
operations (unlike COMMIT which can succeed) because bad things will 
happen if the in-memory image of the database is changed out from under 
an active query.


Igor Tandetnik

On 10/30/2013 12:09 PM, Normand Mongeau wrote:

Then IMO the documentation for either sqlite3_prepare_xx or SQLITE_BUSY
should state this. It would have been helpful in my case, I was pulling my
hair looking at the transaction model which was sound.



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: October-30-13 12:07 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Problem with SQLITE_BUSY


On 30 Oct 2013, at 4:03pm, Normand Mongeau <nmong...@theobjects.com> wrote:


Well finally found the problem: a forgotten sqlite3_finalize() call.

Very disturbing, I'd expect leakage, not the results I was seeing.


SQLite has to keep the state of your SELECT available until you tell it
you're done with that SELECT.  After all, you might look at the results you
have retrieved and decide to make some changes based on what you see there.
This means SQLite has to stop everything from changing the database.
Therefore it locks it.

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] Problem with SQLITE_BUSY

[Normand Mongeau]

Then IMO the documentation for either sqlite3_prepare_xx or SQLITE_BUSY
should state this. It would have been helpful in my case, I was pulling my
hair looking at the transaction model which was sound.



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: October-30-13 12:07 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Problem with SQLITE_BUSY


On 30 Oct 2013, at 4:03pm, Normand Mongeau <nmong...@theobjects.com> wrote:

> Well finally found the problem: a forgotten sqlite3_finalize() call.
> 
> Very disturbing, I'd expect leakage, not the results I was seeing.

SQLite has to keep the state of your SELECT available until you tell it
you're done with that SELECT.  After all, you might look at the results you
have retrieved and decide to make some changes based on what you see there.
This means SQLite has to stop everything from changing the database.
Therefore it locks it.

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] Problem with SQLITE_BUSY

[Simon Slavin]

On 30 Oct 2013, at 4:03pm, Normand Mongeau  wrote:

> Well finally found the problem: a forgotten sqlite3_finalize() call.
> 
> Very disturbing, I'd expect leakage, not the results I was seeing.

SQLite has to keep the state of your SELECT available until you tell it you're 
done with that SELECT.  After all, you might look at the results you have 
retrieved and decide to make some changes based on what you see there.  This 
means SQLite has to stop everything from changing the database.  Therefore it 
locks it.

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

Re: [sqlite] Problem with SQLITE_BUSY

[Normand Mongeau]

Well finally found the problem: a forgotten sqlite3_finalize() call.

Very disturbing, I'd expect leakage, not the results I was seeing.

Normand


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Normand Mongeau
Sent: October-30-13 9:51 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Problem with SQLITE_BUSY

Using the straight C API of SQLite.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: October-30-13 9:39 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Problem with SQLITE_BUSY


On 30 Oct 2013, at 1:00pm, Normand Mongeau <nmong...@theobjects.com> wrote:

> Could this be related to this: in A, the database connection is 
> created in the main program, but is passed down to a dll that loads 
> another dll that uses the connection to do the writes. Maybe the dll 
> should open its own connection?

Are you addressing SQLite though its own C API, or are you using a library
which says it does SQLite things for you ?  If you're using a library, which
one ?

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] Problem with SQLITE_BUSY

[Normand Mongeau]

Using the straight C API of SQLite.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: October-30-13 9:39 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Problem with SQLITE_BUSY


On 30 Oct 2013, at 1:00pm, Normand Mongeau <nmong...@theobjects.com> wrote:

> Could this be related to this: in A, the database connection is 
> created in the main program, but is passed down to a dll that loads 
> another dll that uses the connection to do the writes. Maybe the dll 
> should open its own connection?

Are you addressing SQLite though its own C API, or are you using a library
which says it does SQLite things for you ?  If you're using a library, which
one ?

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] Problem with SQLITE_BUSY

[Simon Slavin]

On 30 Oct 2013, at 1:00pm, Normand Mongeau  wrote:

> Could this be related to this: in A, the database connection is created in
> the main program, but is passed down to a dll that loads another dll that
> uses the connection to do the writes. Maybe the dll should open its own
> connection?

Are you addressing SQLite though its own C API, or are you using a library 
which says it does SQLite things for you ?  If you're using a library, which 
one ?

Simon.

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

Re: [sqlite] Problem with SQLITE_BUSY

[Normand Mongeau]

Actually, I inspected carefully the code and to avoid any deadlocks and make
the intentions very explicit, every time we're about to write we do:

Begin immediate transaction
Our updates
Commit transaction

Anytime we read we do

Begin transaction
read
rollback transaction (there was a mix of commit and rollback, but I made it
uniform by using rollback only)

I still can't find the hole in our code. More info: after the scenario is
done, any read in C (thus a begin/rollback) doesn't affect A, but _any_
write transaction (thus begin immediate/commit) results in SQLITE_BUSY.

Could this be related to this: in A, the database connection is created in
the main program, but is passed down to a dll that loads another dll that
uses the connection to do the writes. Maybe the dll should open its own
connection?

Normand



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: October-29-13 6:41 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Problem with SQLITE_BUSY

You haven't explained what the second connection in A is doing. My educated
guess is, the two connections enter into a deadlock. This is possible when
at least one connection starts as a reader and later attempts to write (the
other could be a straight writer). The scenario goes like this: the reader
acquires a SHARED lock and starts reading. 
Meanwhile, the writer is ready to write, so it gets a PENDING lock and waits
for readers to clear. But instead, the reader tries to start writing by
acquiring a RESERVED lock - which it cannot do for as long as the other
writer is active.

Once this deadlock happens, the only way to make progress is for one of the
two connections to roll back its transaction. One can avoid this scenario by
starting the transaction with BEGIN IMMEDIATE (or BEGIN
EXCLUSIVE) - this marks the transaction as a writer right away, by obtaining
a RESERVED (or EXCLUSIVE) lock from the start.

Igor Tandetnik

On 10/29/2013 6:26 PM, Normand Mongeau wrote:
> Hi,
>
>
>
> I have a situation where I always run into an SQLITE_BUSY error. It's 
> quite involved, here's the high picture:
>
>
>
> -3 processes (A, B and C) each have a connection to the same db. 
> Everybody has a busy handler set for 5 seconds.
>
>
>
> -1 of these processes (A) opens more than one connection, as it loads 
> a DLL that opens its own connection.
>
>
>
> -A needs to check for data at regular intervals, so every second it 
> does a begin transaction, read then commit transaction.
>
>
>
> -C deletes data within a transaction. Then it triggers about 400 
> transactions in B (it basically sends data to B via a TCP/IP layer, B 
> receives the data and writes in the DB).
>
>
>
> -A then sees that data has arrived, and does its own processing, 
> eventually recording some more data (always within a begin/write/commit).
>
>
>
> -After A is done, it goes back into its "check for data" at the same 
> regular interval.
>
>
>
> So far so good. I see the A's begin/read/commit succeed.
>
>
>
> As soon as I trigger another transaction in C (any transaction), A 
> gets the SQLITE_BUSY error, even though the transaction goes through 
> normally in C (i.e. begin transaction, write, commit transaction).
>
>
>
> Any ideas as to what's wrong in the above scenario? How do I avoid the 
> SQLITE_BUSY error? Is it possible to recover from that error? 
> (Apparently not. I close the B and C processes (proper shutdown) and A 
> still gets the error).
>
>
>
> Thanks,
>
>
>
> Normand
>


___
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] Problem with SQLITE_BUSY

[Simon Slavin]

On 30 Oct 2013, at 2:32am, Normand Mongeau  wrote:

> Odd thing is that although I do have a 10 second timeout as soon as C goes
> into a begin transaction A receives the SQLITE_BUSY error, in other words I
> don't see any 10 second delay.
> 
> I'll try increasing the timeout.

No need.  You've already run the test I was interested in by setting your high 
10 second timeout.  What you report suggests that Igor is right: what you are 
seeing is genuine deadlock, not just a temporary clash.

I assume you have already looked through your code to see if you can see any 
obvious deadlock possibilities.

Are you doing SQLite API calls from C, or are you using a library or framework 
to access your database ?

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

Re: [sqlite] Problem with SQLITE_BUSY

[Igor Tandetnik]

On 10/29/2013 10:32 PM, Normand Mongeau wrote:

Hmm really?

Odd thing is that although I do have a 10 second timeout as soon as C goes
into a begin transaction A receives the SQLITE_BUSY error, in other words I
don't see any 10 second delay.


This, too, is consistent with my diagnosis. When SQLite detects the 
deadlock situation I described, it starts returning SQLITE_BUSY right 
away, without waiting for a busy timeout or calling a busy handler. 
SQLite knows that in this situation it's pointless to wait - no progress 
will be made. Only a rollback helps.

--
Igor Tandetnik

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

Re: [sqlite] Problem with SQLITE_BUSY

[Normand Mongeau]

Hmm really?

Odd thing is that although I do have a 10 second timeout as soon as C goes
into a begin transaction A receives the SQLITE_BUSY error, in other words I
don't see any 10 second delay.

I'll try increasing the timeout.



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: October-29-13 8:48 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Problem with SQLITE_BUSY


On 29 Oct 2013, at 10:26pm, Normand Mongeau <nmong...@theobjects.com> wrote:

> I have a situation where I always run into an SQLITE_BUSY error. 

Set your timeout to something very large (a million milliseconds ?) and see
if the problem just turns into unexpected delays instead of errors.  You can
set timeout using either of these:

easy:

http://www.sqlite.org/pragma.html#pragma_busy_timeout

difficult:

http://www.sqlite.org/c3ref/busy_timeout.html

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] Problem with SQLITE_BUSY

[Normand Mongeau]

Actually I was mistaken (my apologies), A doesn't open a second connection,
I thought it did but under this scenario it doesn't.

So it simplifies my problem, yet I don't see what's wrong.

Normand

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: October-29-13 6:41 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Problem with SQLITE_BUSY

You haven't explained what the second connection in A is doing. My educated
guess is, the two connections enter into a deadlock. This is possible when
at least one connection starts as a reader and later attempts to write (the
other could be a straight writer). The scenario goes like this: the reader
acquires a SHARED lock and starts reading. 
Meanwhile, the writer is ready to write, so it gets a PENDING lock and waits
for readers to clear. But instead, the reader tries to start writing by
acquiring a RESERVED lock - which it cannot do for as long as the other
writer is active.

Once this deadlock happens, the only way to make progress is for one of the
two connections to roll back its transaction. One can avoid this scenario by
starting the transaction with BEGIN IMMEDIATE (or BEGIN
EXCLUSIVE) - this marks the transaction as a writer right away, by obtaining
a RESERVED (or EXCLUSIVE) lock from the start.

Igor Tandetnik

On 10/29/2013 6:26 PM, Normand Mongeau wrote:
> Hi,
>
>
>
> I have a situation where I always run into an SQLITE_BUSY error. It's 
> quite involved, here's the high picture:
>
>
>
> -3 processes (A, B and C) each have a connection to the same db. 
> Everybody has a busy handler set for 5 seconds.
>
>
>
> -1 of these processes (A) opens more than one connection, as it loads 
> a DLL that opens its own connection.
>
>
>
> -A needs to check for data at regular intervals, so every second it 
> does a begin transaction, read then commit transaction.
>
>
>
> -C deletes data within a transaction. Then it triggers about 400 
> transactions in B (it basically sends data to B via a TCP/IP layer, B 
> receives the data and writes in the DB).
>
>
>
> -A then sees that data has arrived, and does its own processing, 
> eventually recording some more data (always within a begin/write/commit).
>
>
>
> -After A is done, it goes back into its "check for data" at the same 
> regular interval.
>
>
>
> So far so good. I see the A's begin/read/commit succeed.
>
>
>
> As soon as I trigger another transaction in C (any transaction), A 
> gets the SQLITE_BUSY error, even though the transaction goes through 
> normally in C (i.e. begin transaction, write, commit transaction).
>
>
>
> Any ideas as to what's wrong in the above scenario? How do I avoid the 
> SQLITE_BUSY error? Is it possible to recover from that error? 
> (Apparently not. I close the B and C processes (proper shutdown) and A 
> still gets the error).
>
>
>
> Thanks,
>
>
>
> Normand
>


___
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] Problem with SQLITE_BUSY

[Simon Slavin]

On 29 Oct 2013, at 10:26pm, Normand Mongeau  wrote:

> I have a situation where I always run into an SQLITE_BUSY error. 

Set your timeout to something very large (a million milliseconds ?) and see if 
the problem just turns into unexpected delays instead of errors.  You can set 
timeout using either of these:

easy:

http://www.sqlite.org/pragma.html#pragma_busy_timeout

difficult:

http://www.sqlite.org/c3ref/busy_timeout.html

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

Re: [sqlite] Problem with SQLITE_BUSY

[Igor Tandetnik]

You haven't explained what the second connection in A is doing. My 
educated guess is, the two connections enter into a deadlock. This is 
possible when at least one connection starts as a reader and later 
attempts to write (the other could be a straight writer). The scenario 
goes like this: the reader acquires a SHARED lock and starts reading. 
Meanwhile, the writer is ready to write, so it gets a PENDING lock and 
waits for readers to clear. But instead, the reader tries to start 
writing by acquiring a RESERVED lock - which it cannot do for as long as 
the other writer is active.


Once this deadlock happens, the only way to make progress is for one of 
the two connections to roll back its transaction. One can avoid this 
scenario by starting the transaction with BEGIN IMMEDIATE (or BEGIN 
EXCLUSIVE) - this marks the transaction as a writer right away, by 
obtaining a RESERVED (or EXCLUSIVE) lock from the start.


Igor Tandetnik

On 10/29/2013 6:26 PM, Normand Mongeau wrote:

Hi,



I have a situation where I always run into an SQLITE_BUSY error. It's quite
involved, here's the high picture:



-3 processes (A, B and C) each have a connection to the same db. Everybody
has a busy handler set for 5 seconds.



-1 of these processes (A) opens more than one connection, as it loads a DLL
that opens its own connection.



-A needs to check for data at regular intervals, so every second it does a
begin transaction, read then commit transaction.



-C deletes data within a transaction. Then it triggers about 400
transactions in B (it basically sends data to B via a TCP/IP layer, B
receives the data and writes in the DB).



-A then sees that data has arrived, and does its own processing, eventually
recording some more data (always within a begin/write/commit).



-After A is done, it goes back into its "check for data" at the same regular
interval.



So far so good. I see the A's begin/read/commit succeed.



As soon as I trigger another transaction in C (any transaction), A gets the
SQLITE_BUSY error, even though the transaction goes through normally in C
(i.e. begin transaction, write, commit transaction).



Any ideas as to what's wrong in the above scenario? How do I avoid the
SQLITE_BUSY error? Is it possible to recover from that error? (Apparently
not. I close the B and C processes (proper shutdown) and A still gets the
error).



Thanks,



Normand




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

[sqlite] Problem with SQLITE_BUSY

[Normand Mongeau]

Hi,

 

I have a situation where I always run into an SQLITE_BUSY error. It's quite
involved, here's the high picture:

 

-3 processes (A, B and C) each have a connection to the same db. Everybody
has a busy handler set for 5 seconds.

 

-1 of these processes (A) opens more than one connection, as it loads a DLL
that opens its own connection.

 

-A needs to check for data at regular intervals, so every second it does a
begin transaction, read then commit transaction.

 

-C deletes data within a transaction. Then it triggers about 400
transactions in B (it basically sends data to B via a TCP/IP layer, B
receives the data and writes in the DB).

 

-A then sees that data has arrived, and does its own processing, eventually
recording some more data (always within a begin/write/commit).

 

-After A is done, it goes back into its "check for data" at the same regular
interval.

 

So far so good. I see the A's begin/read/commit succeed.

 

As soon as I trigger another transaction in C (any transaction), A gets the
SQLITE_BUSY error, even though the transaction goes through normally in C
(i.e. begin transaction, write, commit transaction).

 

Any ideas as to what's wrong in the above scenario? How do I avoid the
SQLITE_BUSY error? Is it possible to recover from that error? (Apparently
not. I close the B and C processes (proper shutdown) and A still gets the
error).

 

Thanks,

 

Normand

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

Re: [sqlite] problem with SQLITE_BUSY

[Wenton Thomas]

I used sqlite version 3.5.9.
The interface sqlite3_next_stmt()  is not offerd.

Any  other solution to check whether all statements really are finalized?





From: Dan <danielk1...@gmail.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Saturday, July 4, 2009 10:03:47 PM
Subject: Re: [sqlite] problem  with SQLITE_BUSY


On Jul 4, 2009, at 8:12 PM, Wenton Thomas wrote:

> I use prepare statements, and I  am sure I  finalize all  of  them.

You may be mistaken. sqlite3_next_stmt() will return 0 if all
statements really are finalized.

   assert( sqlite3_next_stmt(db, 0)==0 );

Dan.

>
>
>
>
>
> 
> From: Igor Tandetnik <itandet...@mvps.org>
> To: sqlite-users@sqlite.org
> Sent: Saturday, July 4, 2009 8:44:52 PM
> Subject: Re: [sqlite] problem  with SQLITE_BUSY
>
> Wenton Thomas wrote:
>> Now in my system I used sqlite  to manage  2  database file A.db and
>> B.db,  and each has a connection handle cA, cB. My operation perform
>> like this:
>>
>>
>> sqlite3_exec( select records from cA)
>> sqlite3_exec("begin transaction");
>> insert all records  into cB;
>> sqlite3_exec("commit transaction");
>>
>> All  return value is normal.,but when  I  execute
>> rc = sqlite3_close(),
>> return value rc always be SQLITE_BUSY.
>
> How precisely do you implement "insert all records" part? Do you use
> prepared statements, by any chance? If so, you need to finalize all  
> such
> statements before you can close the connection.
>
> 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] problem with SQLITE_BUSY

[Dan]

On Jul 4, 2009, at 8:12 PM, Wenton Thomas wrote:

> I use prepare statements, and I  am sure I  finalize all  of  them.

You may be mistaken. sqlite3_next_stmt() will return 0 if all
statements really are finalized.

   assert( sqlite3_next_stmt(db, 0)==0 );

Dan.

>
>
>
>
>
> 
> From: Igor Tandetnik <itandet...@mvps.org>
> To: sqlite-users@sqlite.org
> Sent: Saturday, July 4, 2009 8:44:52 PM
> Subject: Re: [sqlite] problem  with SQLITE_BUSY
>
> Wenton Thomas wrote:
>> Now in my system I used sqlite  to manage  2  database file A.db and
>> B.db,  and each has a connection handle cA, cB. My operation perform
>> like this:
>>
>>
>> sqlite3_exec( select records from cA)
>> sqlite3_exec("begin transaction");
>> insert all records  into cB;
>> sqlite3_exec("commit transaction");
>>
>> All  return value is normal.,but when  I  execute
>> rc = sqlite3_close(),
>> return value rc always be SQLITE_BUSY.
>
> How precisely do you implement "insert all records" part? Do you use
> prepared statements, by any chance? If so, you need to finalize all  
> such
> statements before you can close the connection.
>
> 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] problem with SQLITE_BUSY

[Wenton Thomas]

I use prepare statements, and I  am sure I  finalize all  of  them.





From: Igor Tandetnik <itandet...@mvps.org>
To: sqlite-users@sqlite.org
Sent: Saturday, July 4, 2009 8:44:52 PM
Subject: Re: [sqlite] problem  with SQLITE_BUSY

Wenton Thomas wrote:
> Now in my system I used sqlite  to manage  2  database file A.db and
> B.db,  and each has a connection handle cA, cB. My operation perform
> like this:
>
>
> sqlite3_exec( select records from cA)
> sqlite3_exec("begin transaction");
> insert all records  into cB;
> sqlite3_exec("commit transaction");
>
> All  return value is normal.,but when  I  execute
> rc = sqlite3_close(),
> return value rc always be SQLITE_BUSY.

How precisely do you implement "insert all records" part? Do you use 
prepared statements, by any chance? If so, you need to finalize all such 
statements before you can close the connection.

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] problem with SQLITE_BUSY

[Igor Tandetnik]

Wenton Thomas wrote:
> Now in my system I used sqlite  to manage  2  database file A.db and
> B.db,  and each has a connection handle cA, cB. My operation perform
> like this:
>
>
> sqlite3_exec( select records from cA)
> sqlite3_exec("begin transaction");
> insert all records  into cB;
> sqlite3_exec("commit transaction");
>
> All  return value is normal.,but when  I  execute
> rc = sqlite3_close(),
> return value rc always be SQLITE_BUSY.

How precisely do you implement "insert all records" part? Do you use 
prepared statements, by any chance? If so, you need to finalize all such 
statements before you can close the connection.

Igor Tandetnik 



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

Re: [sqlite] problem with SQLITE_BUSY

[Wenton Thomas]

The two database file belongs to different modules.
A module  gets  records  from another  through   interfaces, not  accesses  
other module's database file  directly.

The following  statements  

sqlite3_exec( select records from cA)
sqlite3_exec("begin transaction");
insert all records  into cB;
sqlite3_exec("commit transaction");


are  actually 
get_record_func();  
sqlite3_exec("begin transaction");
insert all records  into cB;
sqlite3_exec("commit transaction");

where   get_record_func() is  a interface of  module which has database file 
A.db.
The functon  get_record_func() execute  
"sqlite3_exec( select records from cA)".



From: "freshie2004-sql...@yahoo.com.au" <freshie2004-sql...@yahoo.com.au>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Saturday, July 4, 2009 5:57:41 PM
Subject: Re: [sqlite] problem  with SQLITE_BUSY

What about using only one connection and the ATTACH statement:

http://www.sqlite.org/lang_attach.html

Also, see the select-stmt form of the INSERT statement:

http://www.sqlite.org/lang_insert.html

Something like...

sqlite3_open database B

ATTACH DATABASE A.db AS dbA

BEGIN


INSERT INTO main.mytable(col1,...colN) SELECT col1,...colN FROM dbA.myothertable

COMMIT

DETACH dbA


sqlite3_close B.db

Cheers!




From: Wenton Thomas <thomas.wen...@yahoo.com>
To: sqlite-users@sqlite.org
Sent: Saturday, 4 July, 2009 7:31:55 PM
Subject: [sqlite] problem  with SQLITE_BUSY

Now in my system I used sqlite  to manage  2  database file A.db and B.db,  and 
each has a connection handle cA, cB.
My operation perform like this:


sqlite3_exec( select records from cA)
sqlite3_exec("begin transaction");
insert all records  into cB;
sqlite3_exec("commit transaction");

All  return value is normal.,but when  I  execute
rc = sqlite3_close(), 
return value rc always be SQLITE_BUSY.

Could anyone help me?

Does the  two database connection disturb each other?
I means, if  there exist a  reading lock on cA, can I write cB?


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



  

Access Yahoo!7 Mail on your mobile. Anytime. Anywhere.
Show me how: http://au.mobile.yahoo.com/mail
___
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] problem with SQLITE_BUSY

[freshie2004-sqlite]

What about using only one connection and the ATTACH statement:

http://www.sqlite.org/lang_attach.html

Also, see the select-stmt form of the INSERT statement:

http://www.sqlite.org/lang_insert.html

Something like...

sqlite3_open database B

ATTACH DATABASE A.db AS dbA

BEGIN


INSERT INTO main.mytable(col1,...colN) SELECT col1,...colN FROM dbA.myothertable

COMMIT

DETACH dbA


sqlite3_close B.db

Cheers!




From: Wenton Thomas <thomas.wen...@yahoo.com>
To: sqlite-users@sqlite.org
Sent: Saturday, 4 July, 2009 7:31:55 PM
Subject: [sqlite] problem  with SQLITE_BUSY

Now in my system I used sqlite  to manage  2  database file A.db and B.db,  and 
each has a connection handle cA, cB.
My operation perform like this:


sqlite3_exec( select records from cA)
sqlite3_exec("begin transaction");
insert all records  into cB;
sqlite3_exec("commit transaction");

All  return value is normal.,but when  I  execute
rc = sqlite3_close(), 
return value rc always be SQLITE_BUSY.

Could anyone help me?

Does the  two database connection disturb each other?
I means, if  there exist a  reading lock on cA, can I write cB?


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



  

Access Yahoo!7 Mail on your mobile. Anytime. Anywhere.
Show me how: http://au.mobile.yahoo.com/mail
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] problem with SQLITE_BUSY

[Wenton Thomas]

Now in my system I used sqlite  to manage  2  database file A.db and B.db,  and 
each has a connection handle cA, cB.
My operation perform like this:


sqlite3_exec( select records from cA)
sqlite3_exec("begin transaction");
insert all records  into cB;
sqlite3_exec("commit transaction");

All  return value is normal.,but when  I  execute 
rc = sqlite3_close(), 
return value rc always be SQLITE_BUSY.

Could anyone help me?

Does the  two database connection disturb each other?
I means, if  there exist a  reading lock on cA, can I write cB?


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