Do SELECT statements automatically start a transaction in order to obtain the SHARED lock? In other words, is the SHARED lock started as a result of the SELECT statement or as a result of the transaction? I was reading the "SQL as Understood by SQLite" documentation and it says the following:
"No changes can be made to the database except within a transaction. Any command that changes the database (basically, any SQL command other than SELECT) will automatically start a transaction if one is not already in effect." Does this excerpt from the documentation mean to say that SELECT statements don't use transactions in addition to a SELECT statement not modifying the database? > Date: Fri, 11 Oct 2013 15:15:55 +0700 > From: Dan Kennedy <danielk1...@gmail.com> > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Consistent reads > Message-ID: <5257b3bb.8000...@gmail.com> > Content-Type: text/plain; charset=ISO-8859-1; format=flowed > > On 10/11/2013 02:29 PM, Paul Harris wrote: >> Hi again, >> >> I am wondering if there is any performance benefit in *not* wrapping >> multiple SELECT calls in a BEGIN/END transaction? >> >> As I understand it, with wrapping: >> BEGIN -- does nothing (yet) >> SELECT1 -- creates a SHARE-ONLY lock on the DB >> SELECT2 -- nothing extra >> SELECT3 -- nothing extra >> END -- cancels SHARE lock >> >> Or, without wrapping, does it create and drop the SHARE LOCK each time? >> Does that add overhead? ie: > > Yes. Adds the overhead of obtaining and releasing the SHARED lock > each transaction. > >> SELECT1 -- creates a SHARE-ONLY lock on the DB, selects, then drops lock >> SELECT2 -- creates a SHARE-ONLY lock on the DB, selects, then drops lock >> SELECT3 -- creates a SHARE-ONLY lock on the DB, selects, then drops lock >> >> >> Also, silly question, but if I want to find a row and then read a blob >> using the sqlite3_blob_open API, I assume I must use transactions to get a >> consistent read, right? >> ie, without a transaction, >> >> SELECT rowid FROM table WHERE condition; >> ** another client could make a change to a db here ** >> sqlite3_blob_open( rowid ) >> ** blob may now be for the "wrong row" > > Transaction is required, yes. Otherwise some other connection may > delete the row in question between the SELECT and sqlite3_blob_open() > calls. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users