On 2014/06/21 22:36, Simon Slavin wrote:
Hello gang.
I recently did some work with another system which supported transactions (a journaling file system) then
came back to SQLite and saw it with a different eye. In my programming for SQLite so far either each
database change has been handled by a different PHP process which opened a different connection, or a
long-running application does sqlite3_open() once when it starts up and _close() when it quite. But the
programming style I've been using recently does the equivalent of allowing any number of concurrent
"BEGIN"s and handing back a handle for each one. You can execute any number of commands (SELECT or
write) for each BEGIN, and then close the transaction with an "END" or "ROLLBACK".
SQLite, of course, does not hand back a handle for each "BEGIN". A BEGIN
inside a transaction is, correctly, an error. But I find myself wanting to handle my
transactions more ... erm ... modularly. So if I was to change my programming style
accordingly ...
(A) What should I be using as transaction handles ? Do I do a different
sqlite3_open() for each transaction or is there some other level ?
(B) Which journaling mode supports this use best ? Any other advice about
PRAGMAs ?
(C) In an application where any number of writing transactions can be running
at one time, what is going to be my expected 'blocking' operation ? Would it
be a second transaction executing a database change _step() ? Or does it
happen when I close a transaction ?
(D) Do I expect to see a big time penalty for changing to this style of working
?
Hi Simon,
I much prefer the modular approach outside of initializations too. I am not sure which DB you've been using - my experience is
limited to PostGres, MySQL, MSSQL, SQLite and some lesser proprietary things, most of which offers transactions that can be handled
in a more modular way.
I do however not think any of them offers the Object or Pointer-per-Transaction approach, if indeed any of them do, I would be
surprised, but I could be wrong. As for simply handling a transaction with several starting points or multiple transactional steps,
this is very possible on almost all as long as it sticks to a connection and employs a locking mechanism - even SQLite offers these
in the form of Save-Point+Release Mechanisms for which you can roll-back to any last savepoint and redo a bit of a transaction, or
go back earlier or all the way up to the first one to completely undo a chain of transactions. I find this very helpful when you
cannot predict the chances of success easily and saves you from losing a whole lot of time-consuming re-do's, or when you update
across different DB's and need to be able to keep them all in step.
It works a treat in WAL mode, but I can't speak about the rest since I only really use WAL mode on anything that goes near a
transaction chain (and if an implementation requires a different mode, I try to change the implementation) :)
As for having two (or more) concurrent connections to the same database having more than one concurrent transactions open which are
all transacting at the same time with the same Tables in the same DB... I cannot fathom by what method any engine would be able to
achieve this, or I am not understanding you. Maybe it's a sort of fake juggling of engine slices or perhaps a multiple WAL kind of
setup, or INNO-like row-level locking (though even that doesn't guarantee transactions won't touch the same rows), or maybe some
other way - but at some point, one of them will HAVE to finish before another does, regardless of the user/programmer's wishes. As I
said, I might be misunderstanding you here....
On the last point. Time Penalty? Why? Sure it takes a bit of extra effort here and there but nothing much more than normal, with
one caveat - unlike proper planning and code design (etc. etc.) promising reduced future time-consumption at the cost of some
initial time input, this transaction approach promises to save time on DB use/effort, but not much in terms of future programming
ease - though one can argue for the advantages of becoming fluent in any discipline.
Have you incurred quite a time-penalty doing the other work (which you described above) in this manner, other than initial learning
curve?
(Again, I may have completely misunderstood, and if so, kindly enlighten us a
bit more!)
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
