Re: [sqlite] Problem with SQLITE_BUSY
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 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
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 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
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 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
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
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 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
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 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
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
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
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
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
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 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
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
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
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
Re: [sqlite] problem with SQLITE_BUSY
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 To: General Discussion of SQLite Database 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 > 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
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 > 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
I use prepare statements, and I am sure I finalize all of them. From: Igor Tandetnik 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
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
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" To: General Discussion of SQLite Database 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 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
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 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