SQLite optimization using transactions

I created an iOS app that, when user taps on a button, a thread will be created and the thread will execute SELECT statements for 50 times. I read about an optimization technique from http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html#transactions It wrote, (emphasis mine)

Unless already in a transaction, each SQL statement has a new transaction started for it. This is very expensive, since it requires reopening, writing to, and closing the journal file for each statement. This can be avoided by wrapping sequences of SQL statements with BEGIN TRANSACTION; and END TRANSACTION; statements. This speedup is also obtained for statements which don't alter the database.

So, I tried adding the BEGIN TRANSACTION; at the beginning of t开发者_JAVA技巧he thread and END TRANSACTION; and the end of the thread, and I found that it reduced the running time by half. However, since it's in threads, sometimes a situation like this happens:

BEGIN TRANSACTION from thread 1
BEGIN TRANSACTION from thread 2
END TRANSACTION from thread 2
END TRANSACTION from thread 1

The second BEGIN and the second END statements will fail. Therefore, I want to move the BEGIN/END statements out of threads i.e. call the BEGIN only once at the start of the application and END at the end of the application. But I'm not sure if it will use up memory? i.e. open the transaction thoughout the application's life time without committing it. The database is read-only because it's bundled with the app.

Thank you for your help.

If it's a read-only database, try opening it with

sqlite3_open_v2("path/to/database", &connection,
                SQLITE_OPEN_READONLY | SQLITE_OPEN_FULLMUTEX, 0);
sqlite3_exec(connection,
             "PRAGMA locking_mode = EXCLUSIVE; PRAGMA journal_mode = OFF;",
             0, 0, &errmsg);

then sharing the connection object among all your threads. That ought to get you all the performance benefits of leaving a transaction open, without having to leave a transaction open. (You might not need the second command. I'd like to think SQLite was clever enough to recognize that it didn't need locking or journaling for a read-only connection, but I don't trust it to be.)