Hi Jeff, On 2015-08-21 07:30 AM, Jeff M wrote: > Sometimes my iOS app creates an unreasonable number of prepared statements > (perhaps 1,000, an app bug that I'm fixing). These prepared statements are > later finalized just prior to doing sqlite3_close(), which sometimes returns > SQL_BUSY. The docs say SQL_BUSY will be returned if I haven't finalized all > prepared statements, but I believe I have done so. My iOS app has only one > connection to the DB and I'm doing all this work on the main thread. > > 1. The docs don't say what to do in the case of SQL_BUSY. Does that mean > I've certainly failed to finalize one or more prepared statements, or does > SQLite just need more time (in which case can I loop on sqlite3_close() until > I get SQLITE_OK)?
SQL_BUSY does not mean anything bad except that you are trying to do some work on a query (read: prepared statement) while another is still not done with its duties. These duties may in your case simply mean that the "closing" of a previous prepared statement is still under way, so yes, it just needs a moment. You can wait a moment and try again. I will mention (as Simon is likely to point out soon!) that the good news is: SQLite will do this waiting-and-retrying for you if you simply set a suitable time-out, perhaps in the order of a minute or more, using the pragma: http://www.sqlite.org/pragma.html#pragma_busy_timeout or, if you prefer using the C-interface: http://www.sqlite.org/c3ref/busy_timeout.html The idea with quite a long time-out is that in the unlikely event a busy signal is reached after that much waiting, you can be certain of a lock race condition or something else being wrong and probably go into recovery/safety mode. Don't make it too long since an impatient user will just kill your process. > 2. Does SQLite keep a record of prepared statements? If so, is there a way > I can ask SQLite to close them all, or are there any tricks to debugging > which statements have not been finalized? Closing the connection will release the resources (such as lingering prepared statements), but there it is best to control it yourself. For a C-interface to find them, see here: https://www.sqlite.org/c3ref/next_stmt.html Hope that helps! Ryan
