[sqlite] Best way to terminate a dead-transaction: commit or rollback?

[Olivier Mascia]

> Le 15 janv. 2016 ? 19:13, James K. Lowden  a 
> ?crit :
> 
>> Let a transaction (started with BEGIN TRANSACTION) which did only
>> reads. Is it any better to end it by COMMIT TRANSACTION or ROLLBACK
>> TRANSACTION, or is it completely insignificant?
> 
> Why not do the right thing and remove the begin & commit transaction
> statements?
> 
> An explicit transaction implies a unit of work, two or more changes to
> the database that must cohere for the database be consistent.
> Read-only operations (by which I suppose you mean SELECT) don't modify
> the database and don't belong in a transaction.


James, I appreciate and thank you for your answer, though I completely disagree.

a) There is always a transaction: either implicit or explicit.

b) I have many situations where running multiple statements within a single 
transaction, even though these statements are read-only (selects) is 
specifically wanted. When using WAL such readers do not block writers and write 
transactions committed after the read transaction began, are not seen by that 
read transaction. Allowing it to have a stable view on the whole database.  
Which is *very* important for some kind of work which can not easily expressed 
in a single select statement, however complex it is.

--
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om


-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 842 bytes
Desc: Message signed with OpenPGP using GPGMail
URL: 

[sqlite] Best way to terminate a dead-transaction: commit or rollback?

[Dominique Devienne]

On Fri, Jan 15, 2016 at 7:25 PM, Keith Medcalf  wrote:

> While it is true that without further action each statement is carried out
> in a separate transaction, it may very well be that the OP wants to have
> what is called Repeatable-Read.  The only way to obtain Repeatable-Read
> between statements is to put the SELECTs in the *same* transaction,
> otherwise they will see different views of the database if there was an
> intervening update.


Did you consider the new http://sqlite.org/c3ref/snapshot_get.html when you
wrote the above?

Richard, what's the use case(s) for the new experimental snapshot APIs?
Thanks, --DD

[sqlite] Best way to terminate a dead-transaction: commit or rollback?

[James K. Lowden]

On Fri, 15 Jan 2016 20:39:15 +0100
Olivier Mascia  wrote:

> write transactions committed after the read transaction began, are
> not seen by that read transaction. Allowing it to have a stable view
> on the whole database.  

As Keith correctly surmised, you need repeatable read.  Never having
had the need, I never explored that aspect of transactions.  (I've
changed isolation levels on other products.  I don't remember the
circumstances well enough to know whether a transaction would have
accomplished the same end.)  

Live & learn.  Thanks for the explanation.  

--jkl

[sqlite] Best way to terminate a dead-transaction: commit or rollback?

[James K. Lowden]

On Thu, 14 Jan 2016 16:54:04 +0100
Olivier Mascia  wrote:

> Let a transaction (started with BEGIN TRANSACTION) which did only
> reads. Is it any better to end it by COMMIT TRANSACTION or ROLLBACK
> TRANSACTION, or is it completely insignificant?

Why not do the right thing and remove the begin & commit transaction
statements?  

An explicit transaction implies a unit of work, two or more changes to
the database that must cohere for the database be consistent.
Read-only operations (by which I suppose you mean SELECT) don't modify
the database and don't belong in a transaction.  

--jkl

[sqlite] Best way to terminate a dead-transaction: commit or rollback?

[Keith Medcalf]

On Friday, 15 January, 2016 11:14, James K. Lowden ,

> On Thu, 14 Jan 2016 16:54:04 +0100 Olivier Mascia  wrote:

> > Let a transaction (started with BEGIN TRANSACTION) which did only
> > reads. Is it any better to end it by COMMIT TRANSACTION or ROLLBACK
> > TRANSACTION, or is it completely insignificant?

> Why not do the right thing and remove the begin & commit transaction
> statements?

> An explicit transaction implies a unit of work, two or more changes to
> the database that must cohere for the database be consistent.
> Read-only operations (by which I suppose you mean SELECT) don't modify
> the database and don't belong in a transaction.

While it is true that without further action each statement is carried out in a 
separate transaction, it may very well be that the OP wants to have what is 
called Repeatable-Read.  The only way to obtain Repeatable-Read between 
statements is to put the SELECTs in the *same* transaction, otherwise they will 
see different views of the database if there was an intervening update.  Once 
you put multiple SELECTs in the same transaction, they will ALL see the same 
database, unchanging, notwithstanding concurrent updates.

Of course, in SQLite the READ transaction will block all writers -- thus 
achieving stability.  If WAL is used then updates can run concurrently and the 
view of the database inside the BEGIN ... COMMIT containing only SELECTs will 
not see the updates committed in a different transaction until the after the 
commit.

[sqlite] Best way to terminate a dead-transaction: commit or rollback?

[R Smith]

On 2016/01/14 5:54 PM, Olivier Mascia wrote:
> Hello,
>
> Let a transaction (started with BEGIN TRANSACTION) which did only reads.
> Is it any better to end it by COMMIT TRANSACTION or ROLLBACK TRANSACTION, or 
> is it completely insignificant?
>

Those two statements do very different things, but in the case of *only 
reads* the effect is similar.  The differences inside SQLite (if both 
are not reduced to a no-op) will be very small to insignificant, but the 
differences to the programmer and code legibility is significant.

What if the day comes that you or another programmer decide to, even for 
test purposes, add a little update line to one such transaction/script? 
Either you might forgot that you were rolling back all the time and then 
run a program that now won't actually work, costing you a lot of time 
debugging, or you might know the rollback strategy is in place, and then 
have to change it, which now causes you time and effort, not to mention 
adding more untested code to your system with possible debugging time 
and errors creeping in.

Best thing to do in the code is stick to what is the sensible intent. If 
your intent isn't specifically to "undo" any changes, then don't use 
rollback.

If your system really needs to squeeze every ounce of speed out of a 
transaction, please test both methods for your specific transaction, 
note the faster one (if any), use it and document it very well for 
future. (It might work different for your specific transaction/script 
than for one of ours). I am however willing to bet the difference is 
insignificant and suggest always use the normal intended way - i.e. 
COMMIT, not ROLLBACK.

Cheers,
Ryan

[sqlite] Best way to terminate a dead-transaction: commit or rollback?

[Olivier Mascia]

Hello,

Let a transaction (started with BEGIN TRANSACTION) which did only reads.
Is it any better to end it by COMMIT TRANSACTION or ROLLBACK TRANSACTION, or is 
it completely insignificant?

-- 
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om