sqlite3_stmt_readonly(stmt)? This hits INSERT/UPDATE/DELETE, but not BEGIN/COMMIT/ROLLBACK.
Or sqlite3_sql(stmt) if you want to do it heuristically by inspecting the statement. I think a "BEGIN READONLY" would be a sensible transaction type. Having a wrapper API force the developer to select read-only or immediate for the transaction type up front has some benefits. My experience is that most developers don't write their code in a way which allows them to correctly handle SQLITE_BUSY after the transaction starts, and once they've written it it can be hard to refactor things to allow the overall transaction to be correctly restarted. -scott On Fri, May 8, 2015 at 10:01 AM, Stephen Broberg <SBroberg at carbonite.com> wrote: > 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. > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
