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 <>
> To:
> Subject: Re: [sqlite] Consistent reads
> Message-ID: <>
> 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

Reply via email to