Using versions 3.8.5 and 3.7.7.1: Is there something in the SQLite C API that will tell what type of statement a prepared sqlite3_stmt is (e.g., insert, update, delete, select, etc.). Specifically, I?d like to know whether a statement that either has run or is about to be run (via sqlite3_step) is a read-only (select) or write (pretty much everything else) operation. I?m implementing a reader/writer lock mechanism around our access to the sqlite database, and as the consumer of the interface will need to specify the lock type when they construct a query object (save point objects would always be write, of course), I?d want to runtime sanity-check their choice after preparing the statement, and log or throw an error if they flagged a non-select statement as a reader. Perhaps it?s possible to interrogate the connection object after a statement runs to determine if the database was changed?
A possible approach might be to use sqlite3_update_hook or sqlite3_commit_hook to identify the statements that modify the db. Does sqlite3_commit_hook fire when a statement is implicitly committed (e.g., an update executed outside the context of a savepoint when in autocommit mode)? Another option might be to use two database connections, one read-only, the other normal, but the design of our current system makes this option a major redesign (this is legacy code I?ve inherited. If I had my druthers, I?d be using a dedicated sqlite connection per thread and running in multi-thread mode, but right now that?s too great a refactoring task). Or am I doing this wrong? Our multithreaded app produces a lot of SQLITE_BUSY results when concurrency increases; serializing our transactions, both read & write, globally solved this problem and increased throughput, but I?m interested in moving beyond a simple mutex to a read/write one. We?re running Serialized mode, but is there some other sqlite configuration that automatically does what I?m trying to accomplish: serialize all write operations and save points for all threads, allow selects to run concurrently? Thanks.
