Dennis Cote wrote:
Jef Driesen wrote:
I can give you the example of an application using sqlite as the on-disk
file format. As mentioned on the sqlite website [1], the traditional
File/Open operation does an sqlite3_open() and executes a BEGIN
TRANSACTION. File/Save does a COMMIT followed by another BEGIN
TRANSACTION. That would be the parent transaction.
Imagine now the application needs to execute a group of sql statements
that needs to be atomic. Some examples that come to my mind are
importing data, re-arranging existing data,... To guarantee the entire
operation is atomic, I want to group them in a child transaction. But
when this operation fails for some reason (because of invalid data, a
violated constraint,...), only the child transaction needs to
rollback. Because changes prior to this child transaction should
remain intact and the application can still continue because the
database remains in a clean state.
Without nested transactions, I have to make a compromise by:
(a) not using a parent transaction and loosing the File/Save feature.
(b) not using a child transaction and running into the risk of leaving
inconsistent data in the database after an error or having to throw away
all changes after an error.
[1] http://www.sqlite.org/whentouse.html
While this is another example of where a savepoint mechanism could be
useful, it is not necessary.
Another solution to your dilemma is given in the second paragraph of the
application file format description on that web page. You use the
database to store an undo log. You can then undo the changes made since
the beginning of your "pseudo transaction" in the event of a error
during a multiple statement change.
Since you are probably going to have an undo/redo mechanism anyway, this
adds little or no additional work. You simply mark all statements in a
"pseudo transaction" with the same transaction number in the undo log,
and if an error occurs you undo all the statements already logged with
that transaction number.
Of course there are also other ways of handling this such as swapping
files on open and save rather than using transactions, so that real
transactions can be used to update the active file atomically.
It is certainly possible to do what I described without nested
transactions. But those solutions are far more complicated and less
elegant than using nested transactions. For instance the undolog
approach needs three triggers (insert/delete/update) for every single
table, a method to maintain a "pseudo transaction level", executing the
items in the undolog and removing them afterwards,...
With nested transaction only two SQL statements are required: BEGIN and
COMMIT (or ROLLBACK in the case of a problem). Very simple and thus less
chance of making mistakes.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
