SQLite Gotcha

Working with SQLite on the iPhone, I ran across a strange (to me, anyway) interaction between prepared statements and transactions. This interaction (which I’m not sure it would be fair to call a “bug”) was causing DB writes to take effect only while my app was running, without being committed to disk. This, of course, was not the desired behaviour, but fortunately it could be corrected.

Symptoms

I observed this issue in the context of INSERT statements. The statements appeared to execute correctly: they ran without error, and when the app read data back from the DB, the INSERTs seemed to have taken effect. However:

• When the app was stopped and restarted, the INSERTed rows vanished
• At no time were the INSERTed rows visible in the sqlite3 command-line client

Transactions

In the course of investigating this problem, I found that I couldn’t begin new transactions; SQLite claimed that a transaction was already in progress. This surprised me, since I had only run a few SELECT statements at that point. I knew that statements outside of BEGIN/COMMIT blocks were assigned to implicit transactions, but couldn’t see how those transactions could be incomplete.

Prepare/Step

I was using sqlite3_prepare_v2 and sqlite3_step to run my queries; in particular, I was getting the row count for a table with the following code:

- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section
{
	NSInteger rv = 0;
	if (q_row_count && (sqlite3_reset(q_row_count) == SQLITE_OK) && (sqlite3_step(q_row_count) == SQLITE_ROW))
	{
		rv = sqlite3_column_int(q_row_count, 0);
	}
	return rv;
}

This seemed reasonable to me; I knew that the q_row_count query would return only one row, because I’d prepared it with code along these lines:

sqlite3_prepare_v2(database, "Select Count(*) From FooTable", -1, &q_row_count, NULL);

However, it appears that the implicit transaction that begins when sqlite3_step is run on a prepared statement does not end when the last row is fetched, but rather when sqlite3_step returns SQLITE_DONE. Since the code wasn’t stepping the prepared statement to completion it was leaving the transaction open, and this transaction was interfering with the final commits of my INSERT statement.

To resolve the problem, I made changes along the lines indicated below:

- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section
{
	NSInteger rv = 0;
	if (q_row_count && (sqlite3_reset(q_row_count) == SQLITE_OK) && (sqlite3_step(q_row_count) == SQLITE_ROW))
	{
		rv = sqlite3_column_int(q_row_count, 0);

		// The next line isn't optional; it ends the implicit transaction
		sqlite3_step(q_row_count);
	}
	return rv;
}