[sqlite] Multiple connections in same process out of sync
Hi all, I'm having an issue when using multiple connections inside same process. Shared -mode is off in my case because b-tree locking slowed down performance when shared cache was on. The issue i'm having is that when one connection performs an insert, sometimes the other connection's page cache does not get flushed and the new rows do not come back. The other connection doesn't seem to be aware of the inserted row. Has anyone seen this issue? have any suggestions? Thanks, Yuriy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multiple connections to in-memory database
I have an asp.net application. I open a sqlite in-memory connection with SQLiteConnection conn = new SQLiteConnection ( Data Source=:memory: ); command. I read that by using cache=shared parameter, I can make that in-memory database reachable from other connections. 1.What is the way of applying cache=shared parameter in this type of connection? SQLiteConnection conn = new SQLiteConnection ( Data Source=:memory:?cache=shared ); is not working? 2.What is the way of creating a new connection for accessing the previously opened in-memory database? Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple connections to in-memory database
On Thu, Jul 5, 2012 at 7:46 AM, T Ü shocking_blue2...@yahoo.com wrote: I have an asp.net application. I open a sqlite in-memory connection with SQLiteConnection conn = new SQLiteConnection ( Data Source=:memory: ); command. I read that by using cache=shared parameter, I can make that in-memory database reachable from other connections. 1.What is the way of applying cache=shared parameter in this type of connection? SQLiteConnection conn = new SQLiteConnection ( Data Source=:memory:?cache=shared ); is not working? I think you should write SQLiteConnection(Data Source=file::memory:?cache=shared). But this possibility was introduced in SQLite 3.7.13. So your asp.net provider should be compiled with the latest version of SQLite, otherwise it won't work. 2.What is the way of creating a new connection for accessing the previously opened in-memory database? You should create new connection the same way as previously opened one, i.e. SQLiteConnection(Data Source=file::memory:?cache=shared). Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple connections to in-memory database
By trying I found out that SQLiteConnection(Data Source=:memory:;cache=shared); worked. In a single aspx.page at cs code, first I open an in-memory database connection SQLiteConnection conn = new SQLiteConnection ( Data Source=:memory:;cache=shared ); conn.Open(); than create table and insert some data then without closing that connection open another connection in the sameway, but when I try to select the rows of the table that I created in the previous table, I get no such table error. What am I doing wrong??? From: Pavel Ivanov paiva...@gmail.com To: T Ü shocking_blue2...@yahoo.com; General Discussion of SQLite Database sqlite-users@sqlite.org Sent: Thursday, July 5, 2012 3:21 PM Subject: Re: [sqlite] Multiple connections to in-memory database On Thu, Jul 5, 2012 at 7:46 AM, T Ü shocking_blue2...@yahoo.com wrote: I have an asp.net application. I open a sqlite in-memory connection with SQLiteConnection conn = new SQLiteConnection ( Data Source=:memory: ); command. I read that by using cache=shared parameter, I can make that in-memory database reachable from other connections. 1.What is the way of applying cache=shared parameter in this type of connection? SQLiteConnection conn = new SQLiteConnection ( Data Source=:memory:?cache=shared ); is not working? I think you should write SQLiteConnection(Data Source=file::memory:?cache=shared). But this possibility was introduced in SQLite 3.7.13. So your asp.net provider should be compiled with the latest version of SQLite, otherwise it won't work. 2.What is the way of creating a new connection for accessing the previously opened in-memory database? You should create new connection the same way as previously opened one, i.e. SQLiteConnection(Data Source=file::memory:?cache=shared). Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple connections to in-memory database
On Thu, Jul 5, 2012 at 8:37 AM, T Ü shocking_blue2...@yahoo.com wrote: By trying I found out that SQLiteConnection(Data Source=:memory:;cache=shared); worked. In a single aspx.page at cs code, first I open an in-memory database connection SQLiteConnection conn = new SQLiteConnection ( Data Source=:memory:;cache=shared ); conn.Open(); than create table and insert some data then without closing that connection open another connection in the sameway, but when I try to select the rows of the table that I created in the previous table, I get no such table error. What am I doing wrong??? Please execute SELECT sqlite_version() in your cs code and tell us the result of it. Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple connections to in-memory database
It returns 3.6.23.1 From: Pavel Ivanov paiva...@gmail.com To: T Ü shocking_blue2...@yahoo.com Cc: General Discussion of SQLite Database sqlite-users@sqlite.org Sent: Thursday, July 5, 2012 3:40 PM Subject: Re: [sqlite] Multiple connections to in-memory database On Thu, Jul 5, 2012 at 8:37 AM, T Ü shocking_blue2...@yahoo.com wrote: By trying I found out that SQLiteConnection(Data Source=:memory:;cache=shared); worked. In a single aspx.page at cs code, first I open an in-memory database connection SQLiteConnection conn = new SQLiteConnection ( Data Source=:memory:;cache=shared ); conn.Open(); than create table and insert some data then without closing that connection open another connection in the sameway, but when I try to select the rows of the table that I created in the previous table, I get no such table error. What am I doing wrong??? Please execute SELECT sqlite_version() in your cs code and tell us the result of it. Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple connections to in-memory database
So this feature shouldn't work for you. From my first message: But this possibility was introduced in SQLite 3.7.13. So your asp.net provider should be compiled with the latest version of SQLite, otherwise it won't work. Pavel On Thu, Jul 5, 2012 at 8:56 AM, T Ü shocking_blue2...@yahoo.com wrote: It returns 3.6.23.1 From: Pavel Ivanov paiva...@gmail.com To: T Ü shocking_blue2...@yahoo.com Cc: General Discussion of SQLite Database sqlite-users@sqlite.org Sent: Thursday, July 5, 2012 3:40 PM Subject: Re: [sqlite] Multiple connections to in-memory database On Thu, Jul 5, 2012 at 8:37 AM, T Ü shocking_blue2...@yahoo.com wrote: By trying I found out that SQLiteConnection(Data Source=:memory:;cache=shared); worked. In a single aspx.page at cs code, first I open an in-memory database connection SQLiteConnection conn = new SQLiteConnection ( Data Source=:memory:;cache=shared ); conn.Open(); than create table and insert some data then without closing that connection open another connection in the sameway, but when I try to select the rows of the table that I created in the previous table, I get no such table error. What am I doing wrong??? Please execute SELECT sqlite_version() in your cs code and tell us the result of it. Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple connections to in-memory database
Thank you so much Pavel. I will try with the new version. From: Pavel Ivanov paiva...@gmail.com To: T Ü shocking_blue2...@yahoo.com Cc: General Discussion of SQLite Database sqlite-users@sqlite.org Sent: Thursday, July 5, 2012 4:03 PM Subject: Re: [sqlite] Multiple connections to in-memory database So this feature shouldn't work for you. From my first message: But this possibility was introduced in SQLite 3.7.13. So your asp.net provider should be compiled with the latest version of SQLite, otherwise it won't work. Pavel On Thu, Jul 5, 2012 at 8:56 AM, T Ü shocking_blue2...@yahoo.com wrote: It returns 3.6.23.1 From: Pavel Ivanov paiva...@gmail.com To: T Ü shocking_blue2...@yahoo.com Cc: General Discussion of SQLite Database sqlite-users@sqlite.org Sent: Thursday, July 5, 2012 3:40 PM Subject: Re: [sqlite] Multiple connections to in-memory database On Thu, Jul 5, 2012 at 8:37 AM, T Ü shocking_blue2...@yahoo.com wrote: By trying I found out that SQLiteConnection(Data Source=:memory:;cache=shared); worked. In a single aspx.page at cs code, first I open an in-memory database connection SQLiteConnection conn = new SQLiteConnection ( Data Source=:memory:;cache=shared ); conn.Open(); than create table and insert some data then without closing that connection open another connection in the sameway, but when I try to select the rows of the table that I created in the previous table, I get no such table error. What am I doing wrong??? Please execute SELECT sqlite_version() in your cs code and tell us the result of it. Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple connections to in-memory database
On Thu, Jul 05, 2012 at 09:03:54AM -0400, Pavel Ivanov scratched on the wall: So this feature shouldn't work for you. From my first message: But this possibility was introduced in SQLite 3.7.13. So your asp.net provider should be compiled with the latest version of SQLite, otherwise it won't work. Also, not to state the obvious, but you can only share a :memory: database across connections that originate from the same process. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multiple connections to :memory: database
Is it possible to have multiple connections to a purely in-memory database and if so how can one go by doing that? Would it be safe if the connections were on separate threads? -Shaun ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple connections to :memory: database
Shaun Seckman (Firaxis) shaun.seck...@firaxis.com wrote: Is it possible to have multiple connections to a purely in-memory database No. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] multiple connections by multiple threads
Hello, I like to know if is recommended to do multiple connections from multiple threads in Sqlite or the best approach is create just one connection to multiple threads. Each thread has a hight concurrence level. I already try to open one connection per thread with that arguments: sqlite3_open_v2(db.toUtf8().data(), d-access, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE|SQLITE_OPEN_FULLMUTEX , 0) and with SQLITE_OPEN_NOMUTEX too. and with sqlite_busy_timeout to 5 seconds, but look likes that i still have deadlocks. Igor Veja quais são os assuntos do momento no Yahoo! +Buscados http://br.maisbuscados.yahoo.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multiple connections from different threads
Sorry for asking such a basic question, and it seems I know the answer but I would like a confirmation. If I am executing the same SQL statement from multiple database handles to the same database file, I still need to prepare a distinct sqlite3_stmt for each connection, even though the SQL statement is the same and the database is the same. It seems that the database handle is bound to the statement, and there is no way to specify which database you want to use after the statement has been prepared. Right? Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple connections from different threads
On Sat, Apr 11, 2009 at 8:58 PM, Vinnie thev...@yahoo.com wrote: Sorry for asking such a basic question, and it seems I know the answer but I would like a confirmation. If I am executing the same SQL statement from multiple database handles to the same database file, I still need to prepare a distinct sqlite3_stmt for each connection, even though the SQL statement is the same and the database is the same. It seems that the database handle is bound to the statement, and there is no way to specify which database you want to use after the statement has been prepared. I know nothing about threads (except what I wear), but you answered your question above -- the db is specified in the db handle. The statements are bound to each db handle. It doesn't matter whether the statements are the same or different, or meant for same or different db... the statements belong to a db handle, and the db handle specifies the db. Right? Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Carbon Model http://carbonmodel.org/ Open Source Geospatial Foundation http://www.osgeo.org/ Sent from Madison, WI, United States ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multiple connections to an in-memory database
Is it possible to open multiple connections to an in-memory database? I have an application that gets a db handle with sqlite3_open(:memory). If another thread in the application were to make that same call, would it get the same handle, another handle to the same in-memory database, or a handle to a new in-memory database? So far, I have been using a mutex in my application to share the single handle, since concurrent accesses will be rare. However, I would like to be able to support 2 transactions in parallel, which I don't think I can do with a single connection - I get a nested transaction error if I try (which makes sense). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple connections to an in-memory database
Fin Springs [EMAIL PROTECTED] wrote: Is it possible to open multiple connections to an in-memory database? No. I have an application that gets a db handle with sqlite3_open(:memory). If another thread in the application were to make that same call, would it get the same handle, another handle to the same in-memory database, or a handle to a new in-memory database? A handle to a new in-memory database. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multiple connections to an in-memory database
Is it possible to open multiple connections to an in-memory database? I have an application that gets a db handle with sqlite3_open(:memory). If another thread in the application were to make that same call, would it get the same handle, another handle to the same in-memory database, or a handle to a new in-memory database? So far, I have been using a mutex in my application to share the single handle, since concurrent accesses will be rare. However, I would like to be able to support 2 transactions in parallel, which I don't think I can do with a single connection - I get a nested transaction error if I try (which makes sense). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple connections to an in-memory database
Fin Springs wrote: Is it possible to open multiple connections to an in-memory database? I'm pretty sure the answer is no. I have an application that gets a db handle with sqlite3_open(:memory). If another thread in the application were to make that same call, would it get the same handle, another handle to the same in-memory database, or a handle to a new in-memory database? So far, I have been using a mutex in my application to share the single handle, since concurrent accesses will be rare. However, I would like to be able to support 2 transactions in parallel, which I don't think I can do with a single connection - I get a nested transaction error if I try (which makes sense). Why do you need two transactions in parallel? In general only one connection can have a transaction open on a database at any time. Locking is used to serialize transactions. Even with two connections, you can't have two active transactions. The second will stall waiting for the first to complete. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple connections to an in-memory database
On Apr 4, 2008, at 11:11 AM, Dennis Cote dennis.cote-at-. | sqlite| wrote: Why do you need two transactions in parallel? In general only one connection can have a transaction open on a database at any time. Locking is used to serialize transactions. Even with two connections, you can't have two active transactions. The second will stall waiting for the first to complete. You're right. I was thinking that using my own synchronization was suboptimal, rather than using SQLite's built-in synchronization that exists for transaction management. I was also thinking that I'd be able to process data coming in and execute the INSERTs in parallel transactions then only be blocked at the COMMIT, but of course I had forgotten about the coarse-grain locking that SQLite uses, so it wouldn't work the way I was thinking it would anyway. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multiple connections and triggers
Hi, I am in a situation where-in there are multiple connections to a single sqlite database. When one connection updates the database other connections would like to get the information about the change (asynchronously, for example when user wants he will use some refresh like command of the application). I tried it by doing the following. 1. Create a table create table updatelog (cid text) 2. The I create the trigger: create trigger updateaddresstrigger update on address begin insert into updatelog values (NEW.cid); end; The table address has a column cid. On refresh I query the updatelog to get the cid of all changed addresses. The problem here is when do I delete the data from updatelog, since if I don't do so then at every refresh, I will be updating the cid address which is already updated. What I want to make sure that a cid entry is removed only for the connection which has refreshed but is still available for connections that have not yet refreshed. I basically want to create trigger, and then associate the action from the application which connects to the database. Something like in my application I would do: sqlite3_open(); And run a command like this one create temp table updatalog (cid text) attach trigger updateaddresstrigger update on address (not a valid command but I just want to express what kind of solution I am looking for) begin insert into updatelog values (NEW.cid); end; The on refresh I would like to ` delete from updatelog Doing this, I can have every connection have its own updatelog table Prakash Reddy Bande Altair Engg. Inc, Troy, MI
Re: [sqlite] multiple connections
Joe Wilson uttered: --- D. Richard Hipp [EMAIL PROTECTED] wrote: In 3.5, cache can be shared between all threads, but shared cache is still disabled by default. You have to invoke sqlite3_enable_shared_cache() to turn it on. I put a comment in the documentation that we might turn shared cache on by default in future releases. But until I better understand the backwards compatibility issues, I think it is probably better to leave it off for now. There's no quicker way to see if there's a problem than enabling it in a release by default. ;-) As we saw when 3.3.0 was release with a non-backword compatible change in storing booleans... Christian -- /\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] multiple connections
--- Christian Smith [EMAIL PROTECTED] uttered: As we saw when 3.3.0 was release with a non-backword compatible change in storing booleans... Not quite the same. That was an intentional design decision they made at the time. As for bugs and unintended incompatibilities, you're always going to miss something. Few test code out of CVS. Most people only notice when the software is released. Some won't even bother to download and test the alpha, so their bug reports are delayed until the next stable release. Be a better Heartthrob. Get better relationship answers from someone who knows. Yahoo! Answers - Check it out. http://answers.yahoo.com/dir/?link=listsid=396545433 - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] multiple connections
Hi, Regarding only to select statements, is there a known limit of number of concurrent connections to Sqlite DB? What about concurrent connections to the same table in the sqlite DB? Regards, Aviad
Re: [sqlite] multiple connections
--- Aviad Harell [EMAIL PROTECTED] wrote: Regarding only to select statements, is there a known limit of number of concurrent connections to Sqlite DB? What about concurrent connections to the same table in the sqlite DB? In sqlite 3.4.2 and earlier, each connection - even to the same database - requires its own distinct file descriptor. So you limited by the number of file descriptors for your OS. In sqlite 3.5 they've changed the design to share a single file descriptor for all connections to the same database. Also, connections to the same database in 3.5+ will share the same database page cache resulting in less overall memory usage. Fussy? Opinionated? Impossible to please? Perfect. Join Yahoo!'s user panel and lay it on us. http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] multiple connections
On Sep 2, 2007, at 11:40 AM, Joe Wilson wrote: In sqlite 3.5 they've changed the design to share a single file descriptor for all connections to the same database. Also, connections to the same database in 3.5+ will share the same database page cache resulting in less overall memory usage. I didn't mean to mislead you, Joe. In 3.5, cache can be shared between all threads, but shared cache is still disabled by default. You have to invoke sqlite3_enable_shared_cache() to turn it on. I put a comment in the documentation that we might turn shared cache on by default in future releases. But until I better understand the backwards compatibility issues, I think it is probably better to leave it off for now. D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] multiple connections
Sharing cache is a great feature and thankyou for shedding the sweat necessary to implement it. I do have a suggestion for V3.5. How about changing the name to Sqnotsolite? D. Richard Hipp wrote: On Sep 2, 2007, at 11:40 AM, Joe Wilson wrote: In sqlite 3.5 they've changed the design to share a single file descriptor for all connections to the same database. Also, connections to the same database in 3.5+ will share the same database page cache resulting in less overall memory usage. I didn't mean to mislead you, Joe. In 3.5, cache can be shared between all threads, but shared cache is still disabled by default. You have to invoke sqlite3_enable_shared_cache() to turn it on. I put a comment in the documentation that we might turn shared cache on by default in future releases. But until I better understand the backwards compatibility issues, I think it is probably better to leave it off for now. D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] multiple connections
--- D. Richard Hipp [EMAIL PROTECTED] wrote: In 3.5, cache can be shared between all threads, but shared cache is still disabled by default. You have to invoke sqlite3_enable_shared_cache() to turn it on. I put a comment in the documentation that we might turn shared cache on by default in future releases. But until I better understand the backwards compatibility issues, I think it is probably better to leave it off for now. There's no quicker way to see if there's a problem than enabling it in a release by default. ;-) Building a website is a piece of cake. Yahoo! Small Business gives you all the tools to get online. http://smallbusiness.yahoo.com/webhosting - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Multiple connections - stale cache?
My final post on this issue...vxWorks has confirmed a bug in their fstat() implementation. I'm hoping for a quick resolution. Thanks for the help. Mark Just a follow-up for the group... Richard was able to determine that my platform is having trouble with an fstat() call to get the size of files on my file system. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Multiple connections - stale cache?
Just a follow-up for the group... Richard was able to determine that my platform is having trouble with an fstat() call to get the size of files on my file system. Specifically, the call is returning a size of 0 when it should be 11K. I am investigating the vxWorks platform and have found that fstat() behaves correctly when I step through my code via a debugger but not when I run it without the debugger. This behavior is very similar to other problems we were seeing in the past where code seemed to work while stepping through the debugger but not when running straight out. I have called in vxWorks support, and I'll keep the group posted of the results. Thanks so much! Mark - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Multiple connections - stale cache?
Hi- No, I am not confident that my file locking code is working. For vxWorks, I needed to alter the .lock functionality, as it wouldn't compile. Pretty much changes such as calling stat() instead of lstat(), etc. However, for this example, I do know that the two threads operate serially (ie, it is never the case that more than one SQL operation will be happening at a time). I have some more diagnostic information. I'm now showing the database connection and the SQL *about* to be executed. I have tried to trim the output to only show relative data, as well as changing the actual SQL table/column names for privacy. Here is the diagnostic output at the point the counter value was unexpected: REPEAT TEST 1 - DBC: 10944678 DB Counter: -61 SQL:BEGIN; DBC: 10944678 DB Counter: -61 SQL:DELETE FROM Table1 DBC: 10944678 DB Counter: -61 SQL:DELETE FROM Table2 DBC: 10944678 DB Counter: -61 SQL:DELETE FROM Table3 DBC: 10944678 DB Counter: -61 SQL:DELETE FROM Table4 DBC: 10944678 DB Counter: -61 SQL:DELETE FROM Table5 DBC: 10944678 DB Counter: -61 SQL:DELETE FROM Table6 DBC: 10944678 DB Counter: -61 SQL:DELETE FROM Table7 DBC: 10944678 DB Counter: -61 SQL:DELETE FROM Table8 DBC: 10944678 DB Counter: -61 SQL:COMMIT; DBC: 10944678 DB Counter: -68 SQL:INSERT INTO Table4(Col1, Col2, Col3) VALUES (1, 1, 32751) Here is some info around counter value -68 when it got to it during the first test (not during the repeat of the test): --- BEGIN TEST 1 - DBC: 10944678 DB Counter: -70 SQL:BEGIN; DBC: 10944678 DB Counter: -70 SQL:UPDATE Table1 SET Col1=1, Col2='Joe' DBC: 10944678 DB Counter: -70 SQL:UPDATE Table2 SET Col1=30, Col2=0 DBC: 10944678 DB Counter: -70 SQL:COMMIT; DBC: 10944678 DB Counter: -69 SQL:SELECT SessionNumber FROM MyTable DBC: 10944678 got sess num = 333000 DBC: 8650883 DB Counter: -69 SQL:SELECT SessionNumber, SessionState FROM MyTable DBC: 8650883 Got session number: 333000 DBC: 8650883 DB Counter: -69 SQL:BEGIN; DBC: 8650883 updating sess info, num = 35 DBC: 8650883 DB Counter: -69 SQL:UPDATE MyTable SET SessionNumber=?, SessionState=? DBC: 8650883 DB Counter: -69 SQL:UPDATE Table1 SET Col1=?, Col2=?, Col3=? DBC: 8650883 DB Counter: -69 SQL:DELETE FROM Table5 WHERE Id=(SELECT Id FROM Table8 WHERE Col4=?) DBC: 8650883 DB Counter: -69 SQL:SELECT Id FROM Table5 ORDER BY SequenceNumber DESC DBC: 8650883 DB Counter: -69 SQL:COMMIT; DBC: 8650883 DB Counter: -68 SQL:UPDATE Table8 SET Col1=? WHERE Col2=? AND Col3=? DBC: 8650883 DB Counter: -67 SQL:UPDATE Table8 SET Col1=? WHERE Col2=? AND Col3=? If you need the exact SQL statements without trimmed output, just let me know, and I can send it to you privately. Thanks for your help, Mark - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Multiple connections - stale cache?
Hi- We have a scenario where we have two different database connections to the same database. Each database connection is running on a separate thread, but in this situation, we are only using one connection at a time. We are finding that sometimes one database connection will do a select on the table and not get the correct value that the other database connection recently updated. The connections are obtained at the beginning of the program and not closed until the very end. Essentially, we run a series of tests that are successful, then we run the same tests again, this time failing. DBC1 - begin transaction DBC1 - sets value to 333000 DBC1 - commit transaction DBC1 - gets value out, is 333000 DBC2 - gets value out, is 333000 DBC2 - begin transaction DBC2 - sets value to 35 DBC2 - commit transaction DBC2 - gets value out, is 35 DBC2 - sets value to 350100 DBC2 - gets value out, is 350100 *Repeat test* DBC1 - begin transaction DBC1 - sets value to 333000 DBC1 - commit transaction DBC1 - gets value out, is 333000 DBC2 - gets value out, is 350100 I have noticed that if we make a call to sqlite3_enable_shared_cache just prior to each database connection being opened, the end result is that DBC2 will get the correct value at the beginning of the second test. Not really knowing the underlying details, it almosts looks like DBC2 doesn't realize the db was changed and uses what it last thought the db held for that value. Any thoughts on why we are seeing what we see? I could add the call to enable the shared cache, but I really want to understand why it doesn't work without it. Thanks, Mark
Re: [sqlite] Multiple connections - stale cache?
Mark Brown [EMAIL PROTECTED] wrote: Hi- We have a scenario where we have two different database connections to the same database. Each database connection is running on a separate thread, but in this situation, we are only using one connection at a time. We are finding that sometimes one database connection will do a select on the table and not get the correct value that the other database connection recently updated. See ticket #2458. http://www.sqlite.org/cvstrac/tktview?tn=2458 If you can get us a reproducible test case, that will be much appreciated. -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Multiple connections - stale cache?
Thanks for the quick reply. Unfortunately, we are developing code on the vxWorks platform, so I don't think sample code would be of use. We have seen the problem for some time now...at least from 3.3.12. The submitter of the ticket appears to have the exact same scenario as us. Hopefully he can submit same sample code that will help you track down the problem. Would there be any diagnostics (i.e. printfs) that I could enable that might be of use? See ticket #2458. http://www.sqlite.org/cvstrac/tktview?tn=2458 If you can get us a reproducible test case, that will be much appreciated. -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Multiple connections - stale cache?
Can you list all the compile flags you used to compile the sqlite3 library (including all -DOMIT_* defines)? --- Mark Brown [EMAIL PROTECTED] wrote: Thanks for the quick reply. Unfortunately, we are developing code on the vxWorks platform, so I don't think sample code would be of use. We have seen the problem for some time now...at least from 3.3.12. The submitter of the ticket appears to have the exact same scenario as us. Hopefully he can submit same sample code that will help you track down the problem. Would there be any diagnostics (i.e. printfs) that I could enable that might be of use? See ticket #2458. http://www.sqlite.org/cvstrac/tktview?tn=2458 If you can get us a reproducible test case, that will be much appreciated. Moody friends. Drama queens. Your life? Nope! - their life, your story. Play Sims Stories at Yahoo! Games. http://sims.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multiple connections - stale cache?
Mark Brown [EMAIL PROTECTED] wrote: Thanks for the quick reply. Unfortunately, we are developing code on the vxWorks platform, so I don't think sample code would be of use. We have seen the problem for some time now...at least from 3.3.12. The logic in SQLite that handles cache synchronization was reworked in version 3.3.14. If you have been seeing the problem since 3.3.12, that suggests an application problem or possible a bug in your OS interface layer, not in the core SQLite. The submitter of the ticket appears to have the exact same scenario as us. Hopefully he can submit same sample code that will help you track down the problem. As I pointed out in comments on the #2458 ticket, I can think of countless bugs in the application that can produce the same symptoms. There is no guarantee that this is an SQLite problem. And, in fact, until I have some evidence to the contrary, I'm working under the theory that this is an application bug not an SQLite bug. Would there be any diagnostics (i.e. printfs) that I could enable that might be of use? Bytes 24-27 of the database file contain a counter that is incremented every time the database file changes. A change in that counter is what triggers a cache flush. You might consider instrumenting your OS interface layer and making sure those bytes really are being read at the beginning of every transaction and written at the end of every transaction. -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Multiple connections - stale cache?
Hi Richard and Joe- It probably is some application error in our code. What puzzles me is that calling sqlite3_enable_shared_cache(1) appears to fix (or at least change) the behavior. Not sure if that triggers any ideas as to what our problem might be. Your suggestion of looking at the database counter is a good one. I can change our app to simply read those bytes and print out the value prior to each statement we execute. Is that what you were thinking? We currently have the following command line switches: -DOS_OTHER=1 -DNO_TCL -DSQLITE_DISABLE_LFS -DTHREADSAFE -DSQLITE_ENABLE_LOCKING_STYLE Essentially, we are on vxWorks operating system, but our build looks just like OS_UNIX. The THREADSAFE and SQLITE_ENABLE_LOCKING_STYLE are new options we added recently, but we have the problem without these as well. I did have to comment out most of the locking styles except the .lock style (which we are using) due to unavailability of certain headers and functionality on vxWorks. Thanks for your help, Mark - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Multiple connections - stale cache?
I have some odd results to report on the db counter. I put some diagnostic code in our database wrapper class to write out the db counter whenever a statement is executed. It will print out for every statement executed whether the statement is a SELECT or UPDATE or BEGIN TRANSACTION. Richard had mentioned reading out bytes 24-27. Assuming this is a zero-based index, I was seeing the 28th byte in the file changing by 1, so I think I'm looking at the right value. And, not knowing if the counter was little or big endian, I just printed out all 4 bytes. Here's the truncated results of what I saw (removing a lot of duplicated DB Counter print outs) - the interesting part is what happens to the counter number in the second execution of the test, as it starts shifting around. Does this information help? BEGIN TEST 1 - DB Counter 0:0:42:-100 DB Counter 0:0:42:-100 DB Counter 0:0:42:-99 DB Counter 0:0:42:-98 DB Counter 0:0:42:-98 DB Counter 0:0:42:-98 DB Counter 0:0:42:-98 DBC1: Re-initializing session number to 333000 DB Counter 0:0:42:-98 DB Counter 0:0:42:-98 DB Counter 0:0:42:-98 DB Counter 0:0:42:-98 DB Counter 0:0:42:-97 DBC1: got sess num = 333000 DB Counter 0:0:42:-97 DBC2: Got session number: 333000 DB Counter 0:0:42:-97 DBC2: about to update sess info, num = 35 DB Counter 0:0:42:-97 DB Counter 0:0:42:-97 DB Counter 0:0:42:-96 DB Counter 0:0:42:-95 DB Counter 0:0:42:-94 DB Counter 0:0:42:-93 DB Counter 0:0:42:-93 DB Counter 0:0:42:-93 DB Counter 0:0:42:-93 DB Counter 0:0:42:-93 DB Counter 0:0:42:-92 DB Counter 0:0:42:-91 DB Counter 0:0:42:-90 DB Counter 0:0:42:-90 DB Counter 0:0:42:-90 DB Counter 0:0:42:-90 DBC2: Got session number: 35 DB Counter 0:0:42:-90 DBC2: about to update sess info, num = 350100 DB Counter 0:0:42:-90 DB Counter 0:0:42:-90 DB Counter 0:0:42:-89 DB Counter 0:0:42:-89 DB Counter 0:0:42:-89 DBC2: Got session number: 350100 -- END TEST -- REPEAT TEST 1 - DB Counter 0:0:42:-89 DB Counter 0:0:42:-89 DB Counter 0:0:42:-89 DB Counter 0:0:42:-89 DB Counter 0:0:42:-96 -- What did my code do to cause this? I think we're doing a bunch of (delete from table) operations on several other tables in this database. DB Counter 0:0:42:-95 DB Counter 0:0:42:-95 DB Counter 0:0:42:-95 DB Counter 0:0:42:-95 DB Counter 0:0:42:-95 DBC1: Re-initializing session number to 333000 DB Counter 0:0:42:-95 DB Counter 0:0:42:-95 DB Counter 0:0:42:-95 DB Counter 0:0:42:-95 DB Counter 0:0:42:-94 DBC1: got sess num = 333000 DB Counter 0:0:42:-94 DBC2: Got session number: 350100 Wrong value DB Counter 0:0:42:-94 DB Counter 0:0:42:-88 --- counter went back to previous value DB Counter 0:0:42:-87 DB Counter 0:0:42:-86 DB Counter 0:0:42:-85 DB Counter 0:0:42:-85 DB Counter 0:0:42:-85 DB Counter 0:0:42:-85 DB Counter 0:0:42:-84 DB Counter 0:0:42:-83 DB Counter 0:0:42:-82 DB Counter 0:0:42:-82 DB Counter 0:0:42:-82 DB Counter 0:0:42:-82 DBC2: Got session number: 350100 Thanks for any help, Mark - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multiple connections - stale cache?
Mark Brown [EMAIL PROTECTED] wrote: REPEAT TEST 1 - DB Counter 0:0:42:-89 DB Counter 0:0:42:-89 DB Counter 0:0:42:-89 DB Counter 0:0:42:-89 DB Counter 0:0:42:-96 -- What did my code do to cause this? This is going to be the bug right here. I would very much like to know what your code is doing right here. Was it the same database connection that printed out the last two lines? Or different connections? What operations occurred in between these two last lines? Are you sure your file locking code is working? -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Multiple connections to the same database and CREATE TABLE command
Hi! In a single process, I open two connections (C1 and C2) to the same database (this is actually a corner case which could happen) and the following sequence of operations fail: 1- on C1 execute CREATE table actor (...) = Ok 2- on C1 execute SELECT * FROM actor = Ok 3- on C2 execute SELECT * FROM actor = error because table actor does not exist. Is there a way to make this sequence of operations work, or should I make sure one can never open two connections on the same DB file in the same process? Thanks, Vivien - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multiple connections to the same database and CREATE TABLE command
Vivien Malerba wrote: Hi! In a single process, I open two connections (C1 and C2) to the same database (this is actually a corner case which could happen) and the following sequence of operations fail: 1- on C1 execute CREATE table actor (...) = Ok 2- on C1 execute SELECT * FROM actor = Ok 3- on C2 execute SELECT * FROM actor = error because table actor does not exist. Is there a way to make this sequence of operations work What SQLite version/API are you using? ISTR this was fixed some time ago - you need to use sqlite3_prepare_v2() instead of sqlite3_prepare(). AIUI, pre the v2 API, connections cache a copy of the schema and aren't aware of changes made by other connections, so your case would result in a schema changed error. The caller would detect this and re-prepare and re-execute the statement. Since this was a common error case the _v2 API was created. It stores a copy of the statement's SQL and automatically re-prepares it if a schema change is encountered. I don't know offhand when this was added, but I assume it was ages ago because it works with Python's wrapper and that uses SQLite v3.3.4. import sqlite3 c1=sqlite3.Connection(c:\\fred) c2=sqlite3.Connection(c:\\fred) c1.execute(create table t(a,b,c)) sqlite3.Cursor object at 0x00D94560 c1.execute(select * from t) sqlite3.Cursor object at 0x00D94500 c1.execute(select * from t).fetchall() [] c2.execute(select * from t).fetchall() [] sqlite3.sqlite_version '3.3.4' Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multiple connections to the same database and CREATE TABLE command
Is C1 in transaction? If it is, commit will enable C2 'see' the new table. regards, Radzi. - Original Message - From: Vivien Malerba [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Friday, May 11, 2007 4:19 PM Subject: [sqlite] Multiple connections to the same database and CREATE TABLE command Hi! In a single process, I open two connections (C1 and C2) to the same database (this is actually a corner case which could happen) and the following sequence of operations fail: 1- on C1 execute CREATE table actor (...) = Ok 2- on C1 execute SELECT * FROM actor = Ok 3- on C2 execute SELECT * FROM actor = error because table actor does not exist. Is there a way to make this sequence of operations work, or should I make sure one can never open two connections on the same DB file in the same process? Thanks, Vivien - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multiple connections to the same database and CREATE TABLE command
Vivien Malerba [EMAIL PROTECTED] wrote: Hi! In a single process, I open two connections (C1 and C2) to the same database (this is actually a corner case which could happen) and the following sequence of operations fail: 1- on C1 execute CREATE table actor (...) = Ok 2- on C1 execute SELECT * FROM actor = Ok 3- on C2 execute SELECT * FROM actor = error because table actor does not exist. When C2 goes to parse the SQL in statement 3, it does not know that the database schema has changed because it has not attempted to access the database file. Thus it does not know that the new table exists. To fix this, you have to get C2 to access the database so that it will reread and reparse the schema and thus discover the new table. Perhaps something like this: 2.5- on C2 execute SELECT 1 FROM sqlite_master LIMIT 1 -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multiple connections to the same database and CREATE TABLE command
On 5/11/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Vivien Malerba [EMAIL PROTECTED] wrote:
Hi!
In a single process, I open two connections (C1 and C2) to the same
database (this is actually a corner case which could happen) and the
following sequence of operations fail:
1- on C1 execute CREATE table actor (...) = Ok
2- on C1 execute SELECT * FROM actor = Ok
3- on C2 execute SELECT * FROM actor = error because table actor
does not exist.
When C2 goes to parse the SQL in statement 3, it does not know
that the database schema has changed because it has not attempted
to access the database file. Thus it does not know that the new
table exists.
To fix this, you have to get C2 to access the database so that
it will reread and reparse the schema and thus discover the new
table. Perhaps something like this:
2.5- on C2 execute SELECT 1 FROM sqlite_master LIMIT 1
This seems to work, see test case (uncomment line 68, 69 to make it
work, remove the Test.db before each run).
Thanks!
Vivien
#include stdio.h
#include sqlite3.h
#include stdlib.h
static int
execute_sql (sqlite3 *db, const char *sql)
{
int status;
sqlite3_stmt *stmt = NULL;
int retval = 0;
printf (== %p SQL: %s\n, db, sql);
status = sqlite3_prepare_v2 (db, sql, -1, stmt, NULL);
if (status != SQLITE_OK)
printf ( ERROR preparing statement: %s\n, sqlite3_errmsg (db));
else {
status = sqlite3_step (stmt);
if ((status != SQLITE_OK) (status != SQLITE_DONE) (status != SQLITE_ROW))
printf ( ERROR executing statement: %s\n, sqlite3_errmsg (db));
else {
retval = 1;
printf ( Ok\n);
}
}
if (stmt)
sqlite3_finalize (stmt);
return retval;
}
int
main(int argc, char **argv){
char *dbname = Test.db;
sqlite3 *db1, *db2;
int rc;
rc = sqlite3_open(dbname, db1);
if (rc) {
printf (Can't open database: %s\n, sqlite3_errmsg(db1));
exit(1);
}
rc = sqlite3_open(dbname, db2);
if (rc) {
printf (Can't open database: %s\n, sqlite3_errmsg(db2));
exit(1);
}
printf (DB1: %p\nDB2: %p\n, db1, db2);
if (! execute_sql (db1, CREATE TABLE if not exists actor (\
actor_id INTEGER PRIMARY KEY, first_name VARCHAR(45) NOT NULL, last_name VARCHAR(45) NOT NULL, \
last_update TIMESTAMP NOT NULL);))
exit (1);
if (! execute_sql (db1, SELECT * FROM actor))
exit (1);
if (! execute_sql (db2, SELECT * FROM actor))
exit (1);
if (! execute_sql (db1, CREATE TABLE if not exists othertable (\
othertable_id INTEGER PRIMARY KEY, first_name VARCHAR(45) NOT NULL, last_name VARCHAR(45) NOT NULL, \
last_update TIMESTAMP NOT NULL);))
exit (1);
if (! execute_sql (db1, SELECT * FROM othertable))
exit (1);
/*if (! execute_sql (db2, SELECT 1 FROM sqlite_master LIMIT 1))
exit (1);*/
if (! execute_sql (db2, SELECT * FROM othertable))
exit (1);
sqlite3_close(db1);
sqlite3_close(db2);
return 0;
}
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
