Re: [sqlite] Consistent reads

[Simon Slavin]

On 14 Oct 2013, at 5:32pm, Jared Albers  wrote:

> 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?

That "other than" is, I think, incorrect.  Even a SELECT statement 
automatically gets wrapped in a transaction if you haven't declared one for it. 
 And that transaction can lock the database.  This is what stops a write from 
another process messing up a SELECT that needs to create its own index.

Simon.  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Consistent reads

[Jared Albers]

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

Re: [sqlite] Consistent reads

[Dan Kennedy]

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

[sqlite] Consistent reads

[Paul Harris]

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:
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"

cheers,
Paul
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users