Re: [sqlite] Memory DB - Query does not return all records after Update
Thank You for the help everyone. The problem was in my query. I have a trigger that updates the a last_modified field anytime there is an update on a given record. My query has a where clause that says ... last_modified current_timestamp. So if there were any updates that occur during the same 1s interval as the query, they were dropped. The issue was that I did not understand/relalize that the precision on the timestamps were 1s. Once again, thank you for all the help, Mike -- View this message in context: http://sqlite.1065341.n5.nabble.com/Memory-DB-Query-does-not-return-all-records-after-Update-tp67267p67717.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory DB - Query does not return all records after Update
Oh, nice catch. Well, after I made the change I am still seeing the same issue. I added some print statements that will maybe help. It sounds like you might think it is because I am not closing my statement is the reason I am getting this issue? I was thinking if it were only that then no matter how long I wait, my next query would return only 9. But this happens only if it is an immediate query, if I wait .5s then I get all 10 results. It feels as if SQLite is acquiring the write lock, doing some work, then releasing the lock. But it seems like SQLite still is doing some writing/protecting of sort in the back ground, and this work is taking longer than my next statement to come around and execute. This is my Query [Thread ID] Bind Results - calls to get data from sqlite3_column_count, sqlite3_column_int64, etc Next - Row, - [SQLITE return code from sqlite3_step()] -- [980] P Query Begin [980] Acquire Mutex [980] Prepare [980] Execute [980] Bind Results Next - Row, 100 [980 ] Bind Results Next - Row, 100 [980] Bind Results Next - Row, 100 [980] Bind Results Next - Row, 100 [980] Bind Results Next - Row, 100 [980] Bind Results Next - Row, 100 [980] Bind Results Next - Row, 100 [980] Bind Results Next - Row, 100 [980] Bind Results Next - Row, 100 [980] Bind Results [980] Release [980] Release Mutex Result set has 10 entries Then the Update [10096] Acquire Mutex [10096] Begin Transaction [10096] Prepare [10096] Bind Blob [10096] Execute [10096] Release [10096] End Transaction [10096] Release Mutex Immediate Query after the Update [980] Acquire Mutex [980] Prepare [980] Execute [980] Bind Results Next - Row, 100 [980] Bind Results Next - Row, 100 [980] Bind Results Next - Row, 100 [980] Bind Results Next - Row, 100 [980] Bind Results Next - Row, 100 [980] Bind Results Next - Row, 100 [980] Bind Results Next - Row, 100 [980 ] Bind Results Next - Row, 100 [980] Bind Results [980] Release [980] Release Mutex Result set has 9 entries -- View this message in context: http://sqlite.1065341.n5.nabble.com/Memory-DB-Query-does-not-return-all-records-after-Update-tp67267p67333.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory DB - Query does not return all records after Update
On 2/27/2013 1:10 PM, mike.akers wrote: Well, after I made the change I am still seeing the same issue. I added some print statements that will maybe help. What does your select statement look like? What does your update statement look like? Any chance you can show a complete sample that reproduces the problem? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory DB - Query does not return all records after Update
Yes I believe so. For my UPDATES, currently (things have moved around quite a bit for me trying to solve this) sqlite3_exec(this-hDBC_, BEGIN EXCLUSIVE TRANSACTION;, NULL, NULL, 0); sqlite3_prepare_v2(this-hDBC_, updateStatement, -1, this-hStmt_, 0); sqlite3_bind_blob(this-hStmt_, 1, blob, blobSize, SQLITE_STATIC); sqlite3_step(this-hStmt_); sqlite3_exec(this-hDBC_, END TRANSACTION;, NULL, NULL, 0); sqlite3_finalize(this-hStmt_); -- View this message in context: http://sqlite.1065341.n5.nabble.com/Memory-DB-Query-does-not-return-all-records-after-Update-tp67267p67292.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory DB - Query does not return all records after Update
COT_EVENT_TABLE = CREATE TABLE MAIN_TABLE( pk char(41) primary key,\ item1 real, \ item2 char(64), \ item3 timestamp, \ item4 integer,\ item5 TIMESTAMP NOT NULL DEFAULT current_timestamp, \ Blob char(1024)); Lets assume I have inserted 10 records. pk = 1 - 10 sqlite3_exec(this-hDBC_, BEGIN EXCLUSIVE TRANSACTION;, NULL, NULL, 0); sqlite3_prepare_v2(this-hDBC_, SELECT * FROM MAIN_TABLE, -1, this-hStmt_, 0); sqlite3_step(this-hStmt_); sqlite3_exec(this-hDBC_, END TRANSACTION;, NULL, NULL, 0); sqlite3_finalize(this-hStmt_); Expect 10 Results -- Returns 10 Results This Select statement is on a loop of roughly .5s ... Then on an external event, another thread will aquire my mutex and attempt an update sqlite3_exec(this-hDBC_, BEGIN EXCLUSIVE TRANSACTION;, NULL, NULL, 0); sqlite3_prepare_v2(this-hDBC_, UPDATE EVENT_TABLE SET item1 = value1, item2 = value2, blob = ?, WHERE pk = 5, -1, this-hStmt_, 0); sqlite3_bind_blob(this-hStmt_, 1, blob, blobSize, SQLITE_STATIC); sqlite3_step(this-hStmt_); sqlite3_exec(this-hDBC_, END TRANSACTION;, NULL, NULL, 0); sqlite3_finalize(this-hStmt_); Expect SQLITE_OK -- Returns SQLITE_OK Now, if a select statments timer is up immediately after this update, meaning the next thing the thread does it the first set of statements. Then sqlite3_exec returns SQLITE_OK and there are 9 results (pk = 5 is missing). I expect either 10 results, or for sqlite3_exec to return a SQLITE_LOCKED, BUSY, or something other than _OK. Additional Info SQLITE_VERSION 3.7.14.1 To open my connection: sqlite3_config(SQLITE_CONFIG_URI); sqlite3_enable_shared_cache(true); sqlite3_open(file::memory:?cache=shared, hDBC_); sqlite3_extended_result_codes(hDBC_, true); I did not change SQLITE_THREADSAFE so I assuming it is still the default 1 -- View this message in context: http://sqlite.1065341.n5.nabble.com/Memory-DB-Query-does-not-return-all-records-after-Update-tp67267p67320.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory DB - Query does not return all records after Update
On 2/26/2013 9:27 AM, mike.akers wrote: Yes I believe so. For my UPDATES, currently (things have moved around quite a bit for me trying to solve this) sqlite3_exec(this-hDBC_, BEGIN EXCLUSIVE TRANSACTION;, NULL, NULL, 0); sqlite3_prepare_v2(this-hDBC_, updateStatement, -1, this-hStmt_, 0); sqlite3_bind_blob(this-hStmt_, 1, blob, blobSize, SQLITE_STATIC); sqlite3_step(this-hStmt_); sqlite3_exec(this-hDBC_, END TRANSACTION;, NULL, NULL, 0); sqlite3_finalize(this-hStmt_); The last two lines should go the other way round. Finalize (or at least reset) the statement first, then commit the transaction. The statement is considered active after step until it's reset or finalized. Your END TRANSACTION statement actually fails, leaving the transaction open. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Memory DB - Query does not return all records after Update
I am using an in-memory database with a singleton containing a single connection. I have multiple threads (3) using this same connection, but I have ensured that only 1 thread uses the connection at a time. If I do an UPDATE on a record then immediately preform a SELECT, the query will return all rows that match the WHERE clause except for the record that was being updated. The sqlite3_prepare_v2 and sqlite3_step both return SQLITE_OK and do not return a SQLITE_LOCKED or SQLITE_BUSY so I would assume that my write was completed. I would like to be able to either 1. read the old data (as the quieres happen frequently a couple old data queries are fine) I have tried read_uncommited with no noticable difference. or 2. Have the database return LOCKED or BUSY if the write has not completed so that the query will return all of the results. Example, If I insert 10 records and loop a query I receive 10, 10, 10, 10, results But, if I change one of the records during the loop I get 10, 10, update record, 9, 10, Any advice on what I am missing? Thanks in advance, Mike -- View this message in context: http://sqlite.1065341.n5.nabble.com/Memory-DB-Query-does-not-return-all-records-after-Update-tp67267.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory DB - Query does not return all records after Update
Are you finalizing the UPDATE statement? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of mike.akers Sent: Monday, February 25, 2013 4:48 PM To: sqlite-users@sqlite.org Subject: [sqlite] Memory DB - Query does not return all records after Update I am using an in-memory database with a singleton containing a single connection. I have multiple threads (3) using this same connection, but I have ensured that only 1 thread uses the connection at a time. If I do an UPDATE on a record then immediately preform a SELECT, the query will return all rows that match the WHERE clause except for the record that was being updated. The sqlite3_prepare_v2 and sqlite3_step both return SQLITE_OK and do not return a SQLITE_LOCKED or SQLITE_BUSY so I would assume that my write was completed. I would like to be able to either 1. read the old data (as the quieres happen frequently a couple old data queries are fine) I have tried read_uncommited with no noticable difference. or 2. Have the database return LOCKED or BUSY if the write has not completed so that the query will return all of the results. Example, If I insert 10 records and loop a query I receive 10, 10, 10, 10, results But, if I change one of the records during the loop I get 10, 10, update record, 9, 10, Any advice on what I am missing? Thanks in advance, Mike -- View this message in context: http://sqlite.1065341.n5.nabble.com/Memory-DB-Query-does-not-return-all-records-after-Update-tp67267.html Sent from the SQLite mailing list archive at Nabble.com. ___ 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] Memory DB - Query does not return all records after Update
Example, If I insert 10 records and loop a query I receive 10, 10, 10, 10, results But, if I change one of the records during the loop I get 10, 10, update record, 9, 10, This sounds gibberish. Could you please post your database schema and exact queries you issue in the exact order? And tell us how their results are different from what you expect. Pavel On Mon, Feb 25, 2013 at 1:47 PM, mike.akers mike.aker...@gmail.com wrote: I am using an in-memory database with a singleton containing a single connection. I have multiple threads (3) using this same connection, but I have ensured that only 1 thread uses the connection at a time. If I do an UPDATE on a record then immediately preform a SELECT, the query will return all rows that match the WHERE clause except for the record that was being updated. The sqlite3_prepare_v2 and sqlite3_step both return SQLITE_OK and do not return a SQLITE_LOCKED or SQLITE_BUSY so I would assume that my write was completed. I would like to be able to either 1. read the old data (as the quieres happen frequently a couple old data queries are fine) I have tried read_uncommited with no noticable difference. or 2. Have the database return LOCKED or BUSY if the write has not completed so that the query will return all of the results. Example, If I insert 10 records and loop a query I receive 10, 10, 10, 10, results But, if I change one of the records during the loop I get 10, 10, update record, 9, 10, Any advice on what I am missing? Thanks in advance, Mike -- View this message in context: http://sqlite.1065341.n5.nabble.com/Memory-DB-Query-does-not-return-all-records-after-Update-tp67267.html Sent from the SQLite mailing list archive at Nabble.com. ___ 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
