[sqlite] Issue with 2 connections getting out of sync, sample code included
Hi all, please help: very strange issue that should not be happening:
1. Have two connections to same database: _db1 and _db2.
2. Create table in _db1
3. Run count * from _db2 - returns 0
4. Insert 1 row using _db1 –OK
5. Run select * from _db2 - 0 rows return
Weird thing is that if I remove step 3, then step 5 returns a row.
I’m guessing there must be a bug in SQLite with cache sync, or I’m missing
something obvious.
Please help:
sqlite3* _db1;
sqlite3* _db2;
sqlite3_open(test.sqlite, _db1);
sqlite3_open(test.sqlite, _db2);
//create table
char* errorMessage;
sqlite3_exec(_db1, CREATE TABLE test (column1 INTEGER), NULL, NULL,
errorMessage);
//get count of items from db2 - should return 0
string countStatement = SELECT COUNT(*) FROM test;
sqlite3_stmt* stmt;
//prepare statement
if(sqlite3_prepare_v2(_db2, countStatement.c_str(), -1, stmt, NULL)
!= SQLITE_OK){
GHAssertTrue(false, @Should have created prepared statement:
%s, sqlite3_errmsg(_db1));
}
int rc = sqlite3_step(stmt);
if(rc == SQLITE_ROW){
//get the count
if(sqlite3_column_count(stmt) 0){
int count = sqlite3_column_int(stmt, 0);
GHAssertTrue(count == 0, @Count should be zero);
}else{
GHAssertTrue(false, @Should have returned a row);
}
}else{
GHAssertTrue(false, @Should have returned a row);
}
//insert one row into table using db1
sqlite3_exec(_db1, INSERT into test(column1) values (4), NULL,
NULL, errorMessage);
//read row using db2
//get count of items from db2 - should return 0
string selectStatement = SELECT * FROM test;
//prepare statement
if(sqlite3_prepare_v2(_db2, selectStatement.c_str(), -1, stmt, NULL)
!= SQLITE_OK){
GHAssertTrue(false, @Should have created prepared statement:
%s, sqlite3_errmsg(_db1));
}
rc = sqlite3_step(stmt);
if(rc == SQLITE_ROW){
//get the count
if(sqlite3_column_count(stmt) 0){
//good
}else{
//bad
GHAssertTrue(false, @Should have returned a row);
}
}else{
//bad
GHAssertTrue(false, @Should have returned a row);
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Issue with 2 connections getting out of sync, sample code included
This is on OSX and iOs 6. Sqlite versions tested were 3.7.13 3.7.17 Database file is stored on disk. Mac OSX journaled partion HFS+. Thanks, Yuriy On Jun 25, 2013, at 2:09 PM, Simon Slavin slav...@bigfraud.org wrote: On 25 Jun 2013, at 7:06pm, Yuriy Stelmakh yuriy...@gmail.com wrote: Hi all, please help: very strange issue that should not be happening: Please verify: Which operating system are you using ? And which version ? Is the database file stored on the hard disk of the computer running the program ? What format is the partition that the file is stored on ? Thanks. 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] Issue with 2 connections getting out of sync, sample code included
Thank you! that did the trick. Its interesting that even though you
can commit a transaction, the cache sync doesn't happen until you
finalize or reset all you statements. I wish this was documented
somewhere better!
Thanks,
Yuriy
On Jun 25, 2013, at 2:19 PM, Richard Hipp d...@sqlite.org wrote:
On Tue, Jun 25, 2013 at 2:06 PM, Yuriy Stelmakh yuriy...@gmail.com wrote:
Hi all, please help: very strange issue that should not be happening:
1. Have two connections to same database: _db1 and _db2.
2. Create table in _db1
3. Run count * from _db2 - returns 0
4. Insert 1 row using _db1 –OK
5. Run select * from _db2 - 0 rows return
Weird thing is that if I remove step 3, then step 5 returns a row.
I’m guessing there must be a bug in SQLite with cache sync, or I’m missing
something obvious.
Without looking at your code, I'm guessing that you are not finalizing the
statement in step 3. It is therefore holding the transaction open,
preventing it from being committed, and thus preventing step 5 from seeing
the change.
The fact that changes are not visible to outside connections until the
change is committed is an important and very powerful feature of the SQL
language, and of SQLite, not a bug.
Looking at your code now - yes, my suspicion seems to be confirmed. I've
marked the spot below where I think you need the sqlite3_finalize().
Please help:
sqlite3* _db1;
sqlite3* _db2;
sqlite3_open(test.sqlite, _db1);
sqlite3_open(test.sqlite, _db2);
//create table
char* errorMessage;
sqlite3_exec(_db1, CREATE TABLE test (column1 INTEGER), NULL,
NULL,
errorMessage);
//get count of items from db2 - should return 0
string countStatement = SELECT COUNT(*) FROM test;
sqlite3_stmt* stmt;
//prepare statement
if(sqlite3_prepare_v2(_db2, countStatement.c_str(), -1, stmt,
NULL)
!= SQLITE_OK){
GHAssertTrue(false, @Should have created prepared statement:
%s, sqlite3_errmsg(_db1));
}
int rc = sqlite3_step(stmt);
if(rc == SQLITE_ROW){
//get the count
if(sqlite3_column_count(stmt) 0){
int count = sqlite3_column_int(stmt, 0);
GHAssertTrue(count == 0, @Count should be zero);
}else{
GHAssertTrue(false, @Should have returned a row);
}
}else{
GHAssertTrue(false, @Should have returned a row);
}
Insert sqlite3_finalize(stmt); here.
//insert one row into table using db1
sqlite3_exec(_db1, INSERT into test(column1) values (4), NULL,
NULL, errorMessage);
//read row using db2
//get count of items from db2 - should return 0
string selectStatement = SELECT * FROM test;
//prepare statement
if(sqlite3_prepare_v2(_db2, selectStatement.c_str(), -1, stmt,
NULL)
!= SQLITE_OK){
GHAssertTrue(false, @Should have created prepared statement:
%s, sqlite3_errmsg(_db1));
}
rc = sqlite3_step(stmt);
if(rc == SQLITE_ROW){
//get the count
if(sqlite3_column_count(stmt) 0){
//good
}else{
//bad
GHAssertTrue(false, @Should have returned a row);
}
}else{
//bad
GHAssertTrue(false, @Should have returned a row);
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Issue with 2 connections getting out of sync, sample code included
In the sample code I did not indeed. But in the actual code that i'm using, the prepared statement used was part of bulk insert surrounded by begin transaction/ commit transaction. I was reseting the stmt before it was used inside of transaction and not after, so it caused a problem. this is what i had: begin transaction loop of reset step commit changed it to: begin transaction loop of step reset commit I assumed that commit would take care of sync, but because my statement was never finalized/reset after last use, it didn't it seems.. Thanks, Yuriy On Jun 25, 2013, at 6:39 PM, Richard Hipp d...@sqlite.org wrote: On Tue, Jun 25, 2013 at 6:32 PM, Yuriy Stelmakh yuriy...@gmail.com wrote: Thank you! that did the trick. Its interesting that even though you can commit a transaction, the cache sync doesn't happen until you finalize or reset all you statements. I wish this was documented somewhere better! No, you completely misunderstood what I said. You never issued an explicit COMMIT. And you had a read operation in progress (because you never finished the count(*)) which means that no autocommit would happen either. Hence, your transaction was never committing. This has nothing whatsoever to do with caches or syncing. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Issue with 2 connections getting out of sync, sample code included
i'm caching the statements for later use, so i assume just doing reset is also ok? it seemed to work well. Again thank you for help, wasted a few days on this. Thanks, Yuriy On Jun 25, 2013, at 6:49 PM, Simon Slavin slav...@bigfraud.org wrote: On 25 Jun 2013, at 11:45pm, Yuriy Stelmakh yuriy...@gmail.com wrote: I assumed that commit would take care of sync, but because my statement was never finalized/reset after last use, it didn't it seems.. You always need to _finalize(). Getting the data you asked for is not the end of the job. You have to _finalize() in order to let SQLite clear up after the statement (which does more than just unlock the file, it also does things about the memory your application uses). So get used to this: for every _prepare() do a _finalize(). As soon as practical. 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] 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
