Re: [sqlite] When incremental write is committed to the hard drive?

[a1rex]

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 <paiva...@gmail.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
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?

[Pavel Ivanov]

> 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 <a1rex2...@yahoo.com> 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 <paiva...@gmail.com>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> 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?

[a1rex]

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 <paiva...@gmail.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
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?

[Pavel Ivanov]

> 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?

[a1rex]

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?

[Pavel Ivanov]

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?

[a1rex]

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