Re: [sqlite] idea: sqlite3_begin() and sqlite3_end() ?

[John LeSueur]

Ned Batchelder wrote:
I went through this same issue in my application, and realized that
rollbacks throw off the whole scheme.  This proposal doesn't account for
rollbacks: how would they work?  If only the outermost "transaction" would
truly perform a rollback, then what would an inner one do? 

Consider this scenario:
1. begin()
2. do_db_work()
3.   begin()
4.   do_db_work()
5.   rollback()
6. do_db_work()
7. end()
What does line 5 do?  What does line 6 do?  What does line 7 do?
I decided for my own work that magic nested transactions are a bad idea.  To
properly handle errors and rollback the database, you need to know where the
edges of the transaction really are.  Fully-supported nested transactions
may be a good thing (I've never used them, so I don't know).  But pretending
that you have nested transactions when you don't is just waving your hands
over some very important issues.
--Ned.
http://nedbatchelder.com
 

Line 5 marks that the whole transaction that ends on 7 should be rolled 
back.  Sure, you lose some work,
but generally speaking(depending on your application's needs), Losing 
work is better than leaving your
database in an inconsistent state. So I agree that there's some 
difficult choices to be made, and that they're
better left to the application code than the library, but they are 
choices that can be made.

John