As a general principle, database transactions should be held for as short a time
as possible. You should start your transaction, then do all of the operations
immediately that need to be mutually consistent, and then end the transaction
appropriately; ideally a transaction is only open for a fraction of a second in
typical cases.
If you are wanting to do something that involves waiting for users, say,
especially remote or web users, you should not be holding a transaction open
while waiting for a user; doing so is generally a design problem with your
application and you should change it so you use some other method for
longer-term consistency.
In a web context, web applications are supposed to be stateless, and you should
not have a database transaction shared between multiple web client requests.
The only common situation where its reasonable to have a transaction open for
more than a split second is if that involves a single database-bound operation,
such as a batch insert or a complicated report. Typical database activity does
not involve this.
On a tangent, if you know a database operation is only going to read, you should
be using a read-only transaction; commit/rollback is only meaningful if you
actually make a change. Barring that you did this, if you don't make a change,
probably a rollback is the correct way to end it, as in theory that's just
saying, I didn't intend to make any changes, and I want the db to ensure nothing
actually changed by accident.
-- Darren Duncan
On 2014-09-06, 7:22 PM, Richard Warburton wrote:
Hi,
Brief:
Should transactions be used for ensuring consistency between multiple
queries? And if so, after I've finished is there a reason why I should not
call commit?
Background:
I'm using SQLite for a web service. The database reference is passed to
Page objects, which handle their specific url path. Not all pages will
write data, but nearly all do multiple queries, which should be consistent
with eachother. I was thinking that to simplify each page object's code, I
would pass a transaction reference instead, and then call rollback if the
page object returns an error, commit otherwise.
However, given that the page will read many times more often than it
writes, I'm wondering if this is a bad idea.
Thoughts?
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
