Re: [sqlite] When incremental write is committed to the hard drive?
Pavel, Thank you so much for your help. Your knowledge is worth more than gold. You were absolutely right regarding not closed blob handle! (for prepare I use only sqlite3_prepare_v2) I found out that I had one blob handle opened in the unrelated table in the same database. This handle was never used for read or write yet it was holding up all the database incremental writes in the other tables. That was preventing all incremental writes to be committed to the hard drive! This was also locking the whole database preventing any updates by external programs. Very interesting… Thank you very much again, Best regards, Samuel - Original Message From: Pavel Ivanov To: General Discussion of SQLite Database Sent: Thu, February 4, 2010 3:54:33 PM Subject: Re: [sqlite] When incremental write is committed to the hard drive? > commit = 1; > pStmt != NULL > but > sql = NULL; //under debugger: pStmt->isPrepareV2 = 0; pStmt->zSql = NULL; So, I was right then that in case of not finished SELECT statement autocommit will still be 1. But as you correctly noticed above isPrepareV2 = 0 and it means that this statement wasn't prepared with sqlite3_prepare_v2 and sql text is never stored in this case. Do you prepare all your statements with sqlite3_prepare_v2? If yes then probably it's statement created by one of sqlite3_blob_open calls which wasn't matched with sqlite3_blob_close then. Check carefully that you close all blob handles. __ Be smarter than spam. See how smart SpamGuard is at giving junk email the boot with the All-new Yahoo! Mail. Click on Options in Mail and switch to New Mail today or register for free at http://mail.yahoo.ca ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] When incremental write is committed to the hard drive?
> commit = 1; > pStmt != NULL > but > sql = NULL; //under debugger: pStmt->isPrepareV2 = 0; pStmt->zSql = NULL; So, I was right then that in case of not finished SELECT statement autocommit will still be 1. But as you correctly noticed above isPrepareV2 = 0 and it means that this statement wasn't prepared with sqlite3_prepare_v2 and sql text is never stored in this case. Do you prepare all your statements with sqlite3_prepare_v2? If yes then probably it's statement created by one of sqlite3_blob_open calls which wasn't matched with sqlite3_blob_close then. Check carefully that you close all blob handles. Pavel On Thu, Feb 4, 2010 at 3:36 PM, a1rex wrote: > > Thank you very much for your advice! > >>to check that transaction >>wasn't committed yet you can connect to the database with external >>command while application is working and try to update or insert >>something. If it fails with message "The database file is locked" then >>application didn't commit transaction > > Yes, you are right. Update fails with message "The database file is locked". > > I inserted following statements: > > const char *sql; > int commit = sqlite3_get_autocommit(db); > > sqlite3_stmt *pStmt = sqlite3_next_stmt(db, NULL); > > if(pStmt != NULL) > { > sql = sqlite3_sql(pStmt); > } > > right after : > > sqlite3_blob_close(); > > And received: > commit = 1; > pStmt != NULL > but > sql = NULL; //under debugger: pStmt->isPrepareV2 = 0; pStmt->zSql = NULL; > > The results surprised me. I think that I do not have any > statement open yet sqlite3_next_stmt tells me that I have statement prepared > and pending. At the same time sqlite3_sql(pStmt) says that that statement is > a NULL statement. > > I just got more confused. > > Best regards, > Samuel > > > - Original Message > From: Pavel Ivanov > To: General Discussion of SQLite Database > Sent: Thu, February 4, 2010 2:07:12 PM > Subject: Re: [sqlite] When incremental write is committed to the hard drive? > >> 1) What else can prevent incremental data to be written to the hard drive? > > Besides all that I mentioned only explicit BEGIN statement can open > transaction and thus prevent anything after that from being written to > disk immediately until COMMIT is executed. What you can do now is > first of all use sqlite3_get_autocommit function > (http://www.sqlite.org/c3ref/get_autocommit.html) after closing blob > handle to check that transaction should be automatically committed. > But I'm not sure that it will return 0 if some SELECT statement is in > progress. To check that you can call sqlite3_next_stmt(db, NULL) > (http://www.sqlite.org/c3ref/next_stmt.html) to obtain pointer to the > statement that is still open (if you finalize all your statements then > this function should return NULL). If function returns some statement > you can use sqlite3_sql (http://www.sqlite.org/c3ref/sql.html) to see > what statement is at fault. > >> 2) Is there a way to force a write to the hard drive? > > Nothing but COMMIT statement (or auto-commit) can force new and > changed data to be written on disk. BTW, to check that transaction > wasn't committed yet you can connect to the database with external > command while application is working and try to update or insert > something. If it fails with message "The database file is locked" then > application didn't commit transaction. If update succeeds and you > still cannot see changes made by application then you have some > problems with file system, but I hope you have not. > > > __ > Looking for the perfect gift? Give the gift of Flickr! > > http://www.flickr.com/gift/ > ___ > 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] When incremental write is committed to the hard drive?
Thank you very much for your advice! >to check that transaction >wasn't committed yet you can connect to the database with external >command while application is working and try to update or insert >something. If it fails with message "The database file is locked" then >application didn't commit transaction Yes, you are right. Update fails with message "The database file is locked". I inserted following statements: const char *sql; int commit = sqlite3_get_autocommit(db); sqlite3_stmt *pStmt = sqlite3_next_stmt(db, NULL); if(pStmt != NULL) { sql = sqlite3_sql(pStmt); } right after : sqlite3_blob_close(); And received: commit = 1; pStmt != NULL but sql = NULL; //under debugger: pStmt->isPrepareV2 = 0; pStmt->zSql = NULL; The results surprised me. I think that I do not have any statement open yet sqlite3_next_stmt tells me that I have statement prepared and pending. At the same time sqlite3_sql(pStmt) says that that statement is a NULL statement. I just got more confused. Best regards, Samuel - Original Message From: Pavel Ivanov To: General Discussion of SQLite Database Sent: Thu, February 4, 2010 2:07:12 PM Subject: Re: [sqlite] When incremental write is committed to the hard drive? > 1) What else can prevent incremental data to be written to the hard drive? Besides all that I mentioned only explicit BEGIN statement can open transaction and thus prevent anything after that from being written to disk immediately until COMMIT is executed. What you can do now is first of all use sqlite3_get_autocommit function (http://www.sqlite.org/c3ref/get_autocommit.html) after closing blob handle to check that transaction should be automatically committed. But I'm not sure that it will return 0 if some SELECT statement is in progress. To check that you can call sqlite3_next_stmt(db, NULL) (http://www.sqlite.org/c3ref/next_stmt.html) to obtain pointer to the statement that is still open (if you finalize all your statements then this function should return NULL). If function returns some statement you can use sqlite3_sql (http://www.sqlite.org/c3ref/sql.html) to see what statement is at fault. > 2) Is there a way to force a write to the hard drive? Nothing but COMMIT statement (or auto-commit) can force new and changed data to be written on disk. BTW, to check that transaction wasn't committed yet you can connect to the database with external command while application is working and try to update or insert something. If it fails with message "The database file is locked" then application didn't commit transaction. If update succeeds and you still cannot see changes made by application then you have some problems with file system, but I hope you have not. __ Looking for the perfect gift? Give the gift of Flickr! http://www.flickr.com/gift/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] When incremental write is committed to the hard drive?
> 1) What else can prevent incremental data to be written to the hard drive? Besides all that I mentioned only explicit BEGIN statement can open transaction and thus prevent anything after that from being written to disk immediately until COMMIT is executed. What you can do now is first of all use sqlite3_get_autocommit function (http://www.sqlite.org/c3ref/get_autocommit.html) after closing blob handle to check that transaction should be automatically committed. But I'm not sure that it will return 0 if some SELECT statement is in progress. To check that you can call sqlite3_next_stmt(db, NULL) (http://www.sqlite.org/c3ref/next_stmt.html) to obtain pointer to the statement that is still open (if you finalize all your statements then this function should return NULL). If function returns some statement you can use sqlite3_sql (http://www.sqlite.org/c3ref/sql.html) to see what statement is at fault. > 2) Is there a way to force a write to the hard drive? Nothing but COMMIT statement (or auto-commit) can force new and changed data to be written on disk. BTW, to check that transaction wasn't committed yet you can connect to the database with external command while application is working and try to update or insert something. If it fails with message "The database file is locked" then application didn't commit transaction. If update succeeds and you still cannot see changes made by application then you have some problems with file system, but I hope you have not. Pavel On Thu, Feb 4, 2010 at 1:49 PM, a1rex wrote: > Pavel, > Thank you very much for your email. I greatly appreciate > your knowledge on the internal workings of Sqlite and your kindness to share > it. > >>All incremental writing is committed (and thus is written to disk) >>when blob handle is closed. And even when you close the handle >>transaction is committed only when there's no more blob handles or >>SELECT statements open at the moment on the same connection. > > I would never guess that SELECT dependency, never! > > I checked my code. But as far as I can tell I have all SELECT statements are > finalized > by sqlite3_finalize() and not held by sqlite3_reset(). I have only one blob > handle and I am opening and > closing it when I SELECT another record from the table. > > Incremental changes are done for sure, I can come back to the > modified record read it within program and new data is in. But when an > external > program reads the same database it does not see the changes till my program > exits. > > 1) What else can prevent incremental data to be written to the hard drive? > > 2) Is there a way to force a write to the hard drive? > > Thank you for reading. Any comment greatly appreciated! > > Regards, > Samuel > > > __ > Be smarter than spam. See how smart SpamGuard is at giving junk email the > boot with the All-new Yahoo! Mail. Click on Options in Mail and switch to > New Mail today or register for free at http://mail.yahoo.ca > ___ > 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] When incremental write is committed to the hard drive?
Pavel, Thank you very much for your email. I greatly appreciate your knowledge on the internal workings of Sqlite and your kindness to share it. >All incremental writing is committed (and thus is written to disk) >when blob handle is closed. And even when you close the handle >transaction is committed only when there's no more blob handles or >SELECT statements open at the moment on the same connection. I would never guess that SELECT dependency, never! I checked my code. But as far as I can tell I have all SELECT statements are finalized by sqlite3_finalize() and not held by sqlite3_reset(). I have only one blob handle and I am opening and closing it when I SELECT another record from the table. Incremental changes are done for sure, I can come back to the modified record read it within program and new data is in. But when an external program reads the same database it does not see the changes till my program exits. 1) What else can prevent incremental data to be written to the hard drive? 2) Is there a way to force a write to the hard drive? Thank you for reading. Any comment greatly appreciated! Regards, Samuel __ Be smarter than spam. See how smart SpamGuard is at giving junk email the boot with the All-new Yahoo! Mail. Click on Options in Mail and switch to New Mail today or register for free at http://mail.yahoo.ca ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] When incremental write is committed to the hard drive?
All incremental writing is committed (and thus is written to disk) when blob handle is closed. And even when you close the handle transaction is committed only when there's no more blob handles or SELECT statements open at the moment on the same connection. Pavel On Wed, Feb 3, 2010 at 7:41 PM, a1rex wrote: > > I use UPDATE for text columns in the table and any changes are committed to > the hard drive right away (well, > after about 120 ms). > > This can be verified by using external tool. I use SQLite Manager to 'see' > the changes. > > I use also sqlite3_blob_write(ppBlob,dataToWrite,dataCount,offsetInTheBlob); > for the BLOB columns. > > The changes to the blob are registered “somewhere” and > subsequent SELECT statement is aware of them but I do not see any physical > changes to the database data on the hard drive. > Also looking at the time stamps I do not see that ‘sqlite3_blob_write’ does > any physical i/o! > > Closing the blob also seems to do nothing. > > Nevertheless, the data seems to be written (flashed?) to the hard drive when > data base is closed. > > I wonder if ACID > properties of the data base are assured for incremental write and if yes when > physical > write really happens? > > Thank you for reading. Any comment greatly appreciated! > Regards, > Samuel > > > __ > Looking for the perfect gift? Give the gift of Flickr! > > http://www.flickr.com/gift/ > ___ > 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] When incremental write is committed to the hard drive?
I use UPDATE for text columns in the table and any changes are committed to the hard drive right away (well, after about 120 ms). This can be verified by using external tool. I use SQLite Manager to 'see' the changes. I use also sqlite3_blob_write(ppBlob,dataToWrite,dataCount,offsetInTheBlob); for the BLOB columns. The changes to the blob are registered “somewhere” and subsequent SELECT statement is aware of them but I do not see any physical changes to the database data on the hard drive. Also looking at the time stamps I do not see that ‘sqlite3_blob_write’ does any physical i/o! Closing the blob also seems to do nothing. Nevertheless, the data seems to be written (flashed?) to the hard drive when data base is closed. I wonder if ACID properties of the data base are assured for incremental write and if yes when physical write really happens? Thank you for reading. Any comment greatly appreciated! Regards, Samuel __ Looking for the perfect gift? Give the gift of Flickr! http://www.flickr.com/gift/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users