Sharing Knowledge - sqlite3_reset()

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Sharing Knowledge - sqlite3_reset()

Sean Heber
This is perhaps well known in the community, but I just spent a day  
banging my head against the wall and figured I'd send out a note so  
that others who have similar problems may come across it and help  
them out.  :-)

It is critically important that sqlite3_reset() be called right after  
you are done with sqlite3_step().

I ran into this problem because I was trying to be clever and had my  
database functions structured like this (fake code):

bool save_data( string a, int b, void* d1, void* d2 ) {
   sqlite3_reset( prepared_insert_query );
   sqlite3_bind_text( prepared_insert_query, 1, a, a.len,  
SQLITE_TRANSIENT );
   sqlite3_bind_int( prepared_insert_query, 2, b );
   sqlite3_bind_blob( prepared_insert_query, 3, d1, len(d1),  
SQLITE_TRANSIENT );
   sqlite3_bind_blob( prepared_insert_query, 4, d2, len(d2),  
SQLITE_TRANSIENT );
   etc...
   return SQLITE_DONE  == sqlite3_step( prepared_insert_query );
}

I liked that structure because it seemed clean.  I could just return  
with the call to step and be all right.  The problem is that  
apparently there are some situations where table locking happens and  
until you call sqlite3_reset(), your table might still be locked.  If  
this function had been all I was doing, it probably would have worked  
fine.  However I had other database queries happening between the  
inserts on the same table.  Some selects, etc.  They were structured  
the same way with the reset of their prepared statement at the top of  
the function.

This problem manifested itself is strange ways.  I was having  
corrupted data at times, other times it seemed to only insert 15 or  
20 records and then it'd start failing, etc.  All sorts of odd  
behavior and problems.  (Some manifestations may be platform  
dependent: I am using MacOS X.)  I must have tried just about  
everything.  I switched data types, changed the order of fields,  
removed the blobs, etc. and I'd still end up with inconsistent results.

All I had to do was move the code in all of my database functions  
around so that sqlite3_reset() happened at the end instead.  Like this:

bool save_data( string a, int b, void* d1, void* d2 ) {
   sqlite3_bind_text( prepared_insert_query, 1, a, a.len,  
SQLITE_TRANSIENT );
   sqlite3_bind_int( prepared_insert_query, 2, b );
   sqlite3_bind_blob( prepared_insert_query, 3, d1, len(d1),  
SQLITE_TRANSIENT );
   sqlite3_bind_blob( prepared_insert_query, 4, d2, len(d2),  
SQLITE_TRANSIENT );
   etc...
   bool r = SQLITE_DONE  == sqlite3_step( prepared_insert_query );
   sqlite3_reset( prepared_insert_query );
   return r;
}

This solved everything and suddenly it worked consistently.  This  
single change seemed to be the final answer to all of my problems and  
it is both satisfying and annoying that it was so simple.  :-)

Just a friendly neighborhood FYI!

l8r
Sean