On Saturday, 6 September, 2014, at 20:23, Richard Warburton
<rich...@skagerraksoftware.com> inquired:
>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?
You say "the database connection". Did you use the language imprecisely or are
you using only one database connection? One presumes that you may have
half-a-million pages and half-a-billion concurrent HTTP operations, in which
case you will have significant multi-leaving of operations. Or is your server
single threaded and can only answer one HTTP operation at a time?
The answer to this question is significant because transactions affect database
connections only -- you may have millions of cursors running millions of
queries in millions of threads all on the same connection, but the transaction
context belongs to the connection, not the cursor/statement. Isolation is at
the connection level, not the statement. If you do not have one connection per
HTTP operation (or a pool of connections where each connection is only used for
one HTTP operation at a time) then wrapping transactions around HTTP operations
will not have the result you intend.
Furthermore, making each HTTP operation use a separate transaction on separate
connections may have a significant effect on concurrency, unless you are using
WAL.
But yes, if you need database consistency within the processing of each HTTP
operation, then you need a connection pool and assign a separate connection per
HTTP operation with transactions around it (ie, start a transaction when you
get a connection from the pool and commit it when you return it, if it is not
already rolled back). You will probably also want to use WAL to increase
concurrency.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
