Re: [sqlite] Re: SQLite and nested transactions
[EMAIL PROTECTED] wrote: Gerry, I took a look at this and I don't see how it works. Sorry. I was just thinking about inserts. Gerry - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: SQLite and nested transactions
I followed every one of these threads and it is really good stuff.
I've done some research to realize I need to do more.
I see that most of you are in the client/server world where you can ask
the client various questions about the query.
In my case, I am a standalone server embedded in a device that has
communication in one direction only. Application to device.
The device can only return status but the device can only return status
when the application asks for it.
The device must support nested transactions.
I gave the following example:
BEGIN parent;
insert into t values ('a');
BEGIN child;
insert into t values ('b');
insert into t values ('c');
insert into t values ('d');
ROLLBACK child;
insert into t values ('e');
COMMIT parent;
Someone asked why I just didn't do the following:
BEGIN parent;
insert into t values ('a');
insert into t values ('e');
COMMIT parent;
The reason for this is that the rollback was caused by a system error
and not a program decision.
For example:
My device may have enough disk space to store records 'b' and 'c' but
not 'd'. So this transaction must be rolled back.
Also, since I have enough space to store 'b' and 'c' I definitely have
enough space to store 'e' since 'b' and 'c' were rolled back.
This is a requirement for my device and may not be a real world
situation for any other system.
Ray
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: SQLite and nested transactions
At 9:48 AM -0600 4/12/07, Dennis Cote wrote:
Yes I did assume no coupling because you didn't suggest any. If
there is coupling this is just another case of the second example.
While I didn't explicitly suggest coupling before, I was making my
arguments on the general case where actions against a database may
possibly be coupled, and my argument was towards solutions that work
for the general case. Sorry if I didn't communicate before that I
was speaking to the general case.
So put all the sub-steps in a subroutine and call it.
In the general case, I don't control what the sub-steps are, but I am
being a proxy for someone else, and I don't know in advance what they
would ask for. Also, as users may want data returned to them between
the sub-steps, their use for which could include determining what
sub-steps are, I can't just generate a subroutine at runtime to
execute, as then they wouldn't get anything back from their
intermediate queries on time. That said, I recognize that in some
situations it is possible for the stored procedure to embed all the
decision making logic necessary from the application, but this isn't
always true, as eg some user may be involved in intermediate steps.
I think that a SQLite pager-based mechanism for tracking child
transactions is quite a bit less complicated and more reliable than
using your workaround, since no details have to be remembered but
for the pages that changed.
That is not true and you know it.
On the contrary, I believe what I said.
You are just pushing the complexity back to Richard. He will have to
implement the changes to the parser, code generation, pager layers,
and test suite, as well as address the backwards compatibility
issues.
I don't see this as a problem. While it is true that a lot of
complexity can be layered on top of the DBMS rather than being
internal to the DBMS, I see child transactions as something that is
best implemented inside the DBMS.
Speaking in a very loose analogy, I see the complexity as SQLite is
now compared to with child transaction support to be like replacing:
foo();
foo();
With:
for ... {
foo();
}
That is, I see it as the difference between explicitly doing
something twice, and doing it once but inside a loop.
So as one can refactor code to use loops rather than explicit
repeating, I don't see the end result here being much larger or more
difficult to maintain. That is, we aren't just adding code, but also
taking away some that has become redundant, is how I conceptualize it.
So SQLite with child transactions is only trivially less lite than
it is now, which is still lite.
If it is a trivial as you suggest, then you should have already
prepared a patch. :-)
I wasn't saying that the patch itself was trivial (though I'm saying
it should be a simpler than the patches for many other requested
features), but rather that the measures of how "lite" SQLite is would
change a trivially small amount between before and after.
In fact, I propose moving rollbackable child transaction support to
the top of the todo list, rather than it being in the middle, given
that its presence can make a lot of other todo or wishlist items
much easier to implement, I believe.
And if it will make a difference, I will even make a monetary
donation (as I can afford to) in order to sponsor its development
(though I would like to think that the benefits are compelling on
their own).
You will have to discuss this with Richard Hipp.
Yes, of course. And I already did do that a few minutes after the list post.
How will nested transactions make creating a your wrapper easier?
Please be specific.
Well, to help people better understand this, I should start but
outlining my own connected work.
I am writing a free and open source RDBMS of my own, whose main
innovations relative to the general DBMS field are in the query
engine, namely the public face (programmatic API and query language)
that application developers and their users interact with. My RDBMS
has its own query language and feature set which overlaps with but
isn't the same as that of existing SQL DBMSs.
My RDBMS is structured as a framework with separate public interface
and backend implementation layers (called "Interface" and "Engine),
such that the backend is a swappable plugin-style component. The
"interface" or wrappers thereof handle parsing user queries into an
standardized AST format, which is what an "Engine" takes as input and
the engine implements the AST-defined query however it wants. The
native language and AST of my RDBMS define rigorous semantics which
users should be able to expect, and which an Engine is supposed to
comply with.
Note that a single query in my language is a full-blown routine
definition (which in the trivial case just contains a single
statement), so what it does and what format of data it can process
for input or output is arbitrarily complex.
Re: [sqlite] Re: SQLite and nested transactions
Gerry,
I took a look at this and I don't see how it works.
I believe I would have to do the following:
Make TEMP copies of all of the tables that are being modified.
Upon COMMIT:
Delete the old tables
Rename the temp tables to the old tables
COMMIT
I don't see an easy way to do this in SQLite.
Keep in mind I am a newbie to database access.
Ray
Gerry Snyder <[EMAIL PROTECTED]> wrote:
> [EMAIL PROTECTED] wrote:
> > Here is an excerpt from my requirements doc:
> >
> >
> > If a transaction is aborted all database state is reset
> ("rolled-back") to its value at the time the transaction was opened.
> > Nested transactions abort or commit relative to their
> parent transaction. In the case of an aborted transaction,
> the database state is rolled back to the point where
> the transaction was started. (This is true whether
> or not the transaction is nested.) In the case of
> a commit, the nested transaction’s changes become
> part of its parent transaction, as if the nested
> transaction boundaries had never been established.
>
> If I had to implement this with the current SQLite,
> I would start a new TEMP table at each BEGIN, and
> destroy the current TEMP table at a ROLLBACK or
> add its contents to its parent table (either
> the previous TEMP table or the "real" table at a
> COMMIT.
>
> I think that does everything you need, and would be
> easy to program.
>
> HTH,
>
> Gerry
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: SQLite and nested transactions
Thanks. I'll look into this path and see if it fits.
Anybody here live in the Irvine, Ca area.
After the discussions on this subject I have come to the conclusion that we
need somebody to implement our embedded database.
Ray
Gerry Snyder <[EMAIL PROTECTED]> wrote:
> [EMAIL PROTECTED] wrote:
> > Here is an excerpt from my requirements doc:
> >
> >
> > If a transaction is aborted all database state is reset
> ("rolled-back") to its value at the time the transaction was opened.
> > Nested transactions abort or commit relative to their
> parent transaction. In the case of an aborted transaction,
> the database state is rolled back to the point where
> the transaction was started. (This is true whether
> or not the transaction is nested.) In the case of
> a commit, the nested transaction’s changes become
> part of its parent transaction, as if the nested
> transaction boundaries had never been established.
>
> If I had to implement this with the current SQLite,
> I would start a new TEMP table at each BEGIN, and
> destroy the current TEMP table at a ROLLBACK or
> add its contents to its parent table (either
> the previous TEMP table or the "real" table at a
> COMMIT.
>
> I think that does everything you need, and would be
> easy to program.
>
> HTH,
>
> Gerry
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: SQLite and nested transactions
Autonomous transactions: ie begin begin autonomous txn1 commit; commit txn1 This transaction has no impact on the outer txn. I believe it can commit either in or out of the parent transaction as well Quite a bit more complicated and really requires the concept of a transaction ID. Maybe this is specific to the DB vendor (ORA$$) I totally agree, in more than 20 years of commercial db development and DBA work, I've only encounterd the use of savepoints 1 time. Dennis Cote <[EMAIL PROTECTED]> wrote: Ken wrote: > Correct me if I'm wrong on this concept: > Adding nested transactions really means adding the ability to demark > internally a transaction ID. So that later that transaction can be rolled > back. > > Consider > begin Main; > step a > savepoint loc1 > step 1 > savepoint loc2 > step 2 > rollback loc2 <- Rolls back step2 > step 2a > savepoint loc3 > step 3 > commit ; > >(result: step a, step 1, step2a and step3 ) > > I think the concept of a savepoint is simpler than a truely nested > transaction. As one doesn't actually need to start a new transaction just > mark a position where a savepoint rollback would stop. Savepoints then are > not really nested transactions but just markers that indicate when to stop > rolling back within the journal file. > Ken, As far as I understand it the two concepts are fundamentally the same. Savepoints can be implemented using simply nested transactions. The savepoint syntax is what is used by the SQ:1999 and later standards. > > But savepoints are usefull in special situations. > Yes they are, but those situations are really quite rare in the real world. > >Instead of Nested Transactions, What about the concept of an autonomous > transaction? > > I don't know what you mean by autonomous transactions as opposed to normal SQL transactions. Can you explain the difference? Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: SQLite and nested transactions
Darren Duncan wrote: At 3:33 PM -0600 4/11/07, Dennis Cote wrote: You have lost me here. If this transaction is considered successful without executing the middle step (which is the same as executing it and then undoing that execution) then that step does not belong in this transaction. Instead of begin step 1 savepoint after_1 optional step 2 if error rollback to savepoint after_1 step 3 commit You can do begin step 1 step 3 commit begin optional step 2 if error rollback else commit Your example assumes no coupling between the different steps, which is true some times, but in the general case there can be coupling. That is, the effect that step 3 actually has can be different depending on whether step 2 is rolled back or not, either because step 3 is operating on a different database state, or because step 3 contains conditionals that cause different statements to execute depending on database state that could have been changed by step 2. So in the general case, step 2 must always be run after step 1 and before step 3. Darren, Yes I did assume no coupling because you didn't suggest any. If there is coupling this is just another case of the second example. This *is* an example of an application where a nested transaction or a savepoint could be useful. However there is a fairly simple workaround that gets the same result without a nested transaction. Instead of: begin step 1 begin nested temp step 2 var = query current state rollback nested step 3 using var commit You could do: begin step 1 temp step 2 var = query current state rollback begin step 1 step 3 using var commit The cost of this approach is repeating the work done in step 1. While that workaround may be an acceptable solution for some situations, I see that as overly complicated and difficult in the general case. For one thing, it requires the application to keep track of all the details of what step 1 was, and step 1 could be arbitrarily complex. So put all the sub-steps in a subroutine and call it. Moreover, step 1 could have been expensive, involving a large amount of data which may have been input from somewhere and can't be retrieved again nor stored in RAM; the only copy of it is in the database. You can always manufacture an example where nested transactions are useful. My point is that these situations are rare in real world applications. Or even ignoring the last point there is still the complexity, especially if one used bind variables that were since freed up for other tasks, since you aren't just keeping a log of SQL strings to re-run. Again subroutines solve this issue quite handily. I think that a SQLite pager-based mechanism for tracking child transactions is quite a bit less complicated and more reliable than using your workaround, since no details have to be remembered but for the pages that changed. That is not true and you know it. You are just pushing the complexity back to Richard. He will have to implement the changes to the parser, code generation, pager layers, and test suite, as well as address the backwards compatibility issues. How much more complicated is the nested transaction solution if *you* have to implement it? Now going off on a tangent ... To address the oft-raised comment that some people make that any proposed additions or changes to SQLite be opposed in general on the principal that "it is supposed to be lite", I say this: It is perfectly in keeping with SQLite's philosophy for us to add lots of power to it if the cost of adding that power is low. Low cost meaning that the codebase doesn't need to increase much if any, the resource efficiency of running SQLite isn't impacted much, complexity doesn't raise the bug density appreciably, and particularly, it isn't more difficult for users to use. Some features, like proper child transactions as I described, are a situation where users gain a lot of power at very little cost. Having proper child transactions means it is a lot easier for users and developers, particularly SQLite extension or wrapper writers such as myself, to add powerful features to SQLite using programs while SQLite itself is hardly more complex. Users are saved a lot of work, and SQLite developers gain next to none. By contrast, say, supporting named users in the database and concurrent database writes and stuff like that is indeed way too complicated for SQLite to have, and I still support SQLite never adding support for it. So SQLite with child transactions is only trivially less lite than it is now, which is still lite. If it is a trivial as you suggest, then you should have already prepared a patch. :-) How will nested transactions make creating a your wrapper easier? Please be specific. In fact, I propose moving rollbackable child transaction support to the top
Re: [sqlite] Re: SQLite and nested transactions
Ken wrote: Correct me if I'm wrong on this concept: Adding nested transactions really means adding the ability to demark internally a transaction ID. So that later that transaction can be rolled back. Consider begin Main; step a savepoint loc1 step 1 savepoint loc2 step 2 rollback loc2 <- Rolls back step2 step 2a savepoint loc3 step 3 commit ; (result: step a, step 1, step2a and step3 ) I think the concept of a savepoint is simpler than a truely nested transaction. As one doesn't actually need to start a new transaction just mark a position where a savepoint rollback would stop. Savepoints then are not really nested transactions but just markers that indicate when to stop rolling back within the journal file. Ken, As far as I understand it the two concepts are fundamentally the same. Savepoints can be implemented using simply nested transactions. The savepoint syntax is what is used by the SQ:1999 and later standards. But savepoints are usefull in special situations. Yes they are, but those situations are really quite rare in the real world. Instead of Nested Transactions, What about the concept of an autonomous transaction? I don't know what you mean by autonomous transactions as opposed to normal SQL transactions. Can you explain the difference? Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: SQLite and nested transactions
Ramon Ribó wrote: I cannot agree here. Just imagine that the user decision is based on the imported data. Of course, you can read the data of the file, store in temporal structures on memory, ask the user and then, enter the data into the database. But the advantage of using sqlite as storage mechanism, as drh pointed out, is to use it as file format, an avoid creating intermediate data structures. Ramon, In order to insert any data into an sqlite database you have to have it in memory in some intermediate data structure. All I have suggested is querying the user before you do the insert instead of after, so that you don' t need to undo the insert if the user does not want that data inserted. My opinion is that it is completely possible to live without nested transactions, in fact we all do now and survive, but they are very convenient to solve a full range of problems. You are right that a different range of problems can be easily solved by just maintaining a counter of transactions. I fully agree. I just think its important to point out that the range of problems that require nested transactions is not nearly as large as people assume when they first start thinking about the issue. Written in an abstract form, you base your reasoning on the fact that you know, before beginning the first transaction, all the future steps that you are going to perform and the order of performing them. This is not always true, as some decisions can be based on external input, like an interative user, data coming from an external source, other types of events. Also, the decision is often taken based on the data itself, so it is more convenient to put the data in the database, operate with it and decide after operation. That is not what I'm saying. There is no requirement to know the all the steps that will be included in the transaction or the order of performing them before the transaction begins. The point of a transaction is that all the actions are done or none are. The individual actions can be conditional, or done in different orders from one transaction to another. What you don't want to do is include any action that is not necessary to the successful completion of the transaction. This way if an error occurs at some point, the transaction can be rolled back to undo all the changes in the same way the database handles an incomplete transaction due to a catastrophic failure such as a power failure. If the transaction only includes necessary statements, then an error in any statement means the transaction is incomplete, and therefore should be completely undone. If you include unnecessary actions in your transaction, you are not using transactions correctly, and you will run into problems. Finally, I am not advocating to include nested transactions into sqlite or not, as this is a problem to be solved by the people who is developing the library. But it is important to include all the facts in the discussion. Again, I agree fully. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: SQLite and nested transactions
Dennis Cote <[EMAIL PROTECTED]> wrote: Ramon Ribó wrote:
>
>
> Imagine one application that can import data from a file. You want
> that, in case of computer crash, either all the data of the file is
> imported or none. At the same time, you want the user to manually
> accept or reject every section of the file.
>
> This example can be modelled in a very natural way with a
> transaction covering the full file import and a nested transaction
> covering every section.
>
>
Ramon,
I don't see that where nested transactions are needed for this example.
You seem to be suggesting a loop reading each file section and writing
it into the database in a nested transaction and then rolling back a
nested transaction if the user says they want to skip that section.
begin
for each section in file {
read section
begin nested
insert section
if promp_user(section) == keep
commit nested
else
rollback nested
}
commit
The same thing can be done far more efficiently by prompting the user
first and only inserting the sections the user wants to keep.
begin
for each section in file {
read section
if promp_user(section) == keep
insert section
}
commit
If the program completes all users selected sections are inserted into
the database atomically. If the program crashes the entire file will be
deleted when the incomplete transaction is rolled back. Similarly if an
I/O error occur when reading the file or a disk full condition happens
when inserting a section, those and any other errors would cause the
transaction to be rolled back so that none of the file sections are
inserted. I want to insert all of the user selected sections or none of
them.
Nested transaction only create more work and make the application more
complicated.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Denis,
Correct me if I'm wrong on this concept:
Adding nested transactions really means adding the ability to demark
internally a transaction ID. So that later that transaction can be rolled back.
Consider
begin Main;
step a
savepoint loc1
step 1
savepoint loc2
step 2
rollback loc2 <- Rolls back step2
step 2a
savepoint loc3
step 3
commit ;
(result: step a, step 1, step2a and step3 )
I think the concept of a savepoint is simpler than a truely nested
transaction. As one doesn't actually need to start a new transaction just mark
a position where a savepoint rollback would stop. Savepoints then are not
really nested transactions but just markers that indicate when to stop rolling
back within the journal file.
The examples given thus far are not very compelling for savepoints. But
savepoints are usefull in special situations.
Instead of Nested Transactions, What about the concept of an autonomous
transaction?
Regards,
Ken
Re: [sqlite] Re: SQLite and nested transactions
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 Jef, 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. Dennis Cote . - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: SQLite and nested transactions
Dennis,
I cannot agree here. Just imagine that the user decision is based on the
imported data.
Of course, you can read the data of the file, store in temporal structures
on memory, ask
the user and then, enter the data into the database. But the advantage of
using sqlite as
storage mechanism, as drh pointed out, is to use it as file format, an
avoid creating
intermediate data structures.
My opinion is that it is completely possible to live without nested
transactions, in
fact we all do now and survive, but they are very convenient to solve a
full range of
problems. You are right that a different range of problems can be easily
solved by just
maintaining a counter of transactions.
Written in an abstract form, you base your reasoning on the fact that
you know, before
beginning the first transaction, all the future steps that you are going
to perform and
the order of performing them. This is not always true, as some decisions
can be based on
external input, like an interative user, data coming from an external
source, other types
of events. Also, the decision is often taken based on the data itself, so
it is more
convenient to put the data in the database, operate with it and decide
after operation.
Finally, I am not advocating to include nested transactions into sqlite
or not, as this
is a problem to be solved by the people who is developing the library. But
it is important
to include all the facts in the discussion.
Best regards,
--
Compass Ing. y Sistemas Dr. Ramon Ribo
http://www.compassis.com[EMAIL PROTECTED]
c/ Tuset, 8 7-2 tel. +34 93 218 19 89
08006 Barcelona, Spain fax. +34 93 396 97 46
En Thu, 12 Apr 2007 00:37:21 +0200, Dennis Cote <[EMAIL PROTECTED]>
escribió:
Ramon Ribó wrote:
Imagine one application that can import data from a file. You want
that, in case of computer crash, either all the data of the file is
imported or none. At the same time, you want the user to manually
accept or reject every section of the file.
This example can be modelled in a very natural way with a transaction
covering the full file import and a nested transaction covering every
section.
Ramon,
I don't see that where nested transactions are needed for this example.
You seem to be suggesting a loop reading each file section and writing
it into the database in a nested transaction and then rolling back a
nested transaction if the user says they want to skip that section.
begin
for each section in file {
read section
begin nested
insert section
if promp_user(section) == keep
commit nested else
rollback nested
}
commit
The same thing can be done far more efficiently by prompting the user
first and only inserting the sections the user wants to keep.
begin
for each section in file {
read section
if promp_user(section) == keep
insert section
}
commit
If the program completes all users selected sections are inserted into
the database atomically. If the program crashes the entire file will be
deleted when the incomplete transaction is rolled back. Similarly if an
I/O error occur when reading the file or a disk full condition happens
when inserting a section, those and any other errors would cause the
transaction to be rolled back so that none of the file sections are
inserted. I want to insert all of the user selected sections or none of
them.
Nested transaction only create more work and make the application more
complicated.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
--
Compass Ing. y Sistemas Dr. Ramon Ribo
http://www.compassis.com[EMAIL PROTECTED]
c/ Tuset, 8 7-2 tel. +34 93 218 19 89
08006 Barcelona, Spain fax. +34 93 396 97 46
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: SQLite and nested transactions
[EMAIL PROTECTED] wrote:
Here is an excerpt from my requirements doc:
If a transaction is aborted all database state is reset
("rolled-back") to its value at the time the transaction was opened.
Nested transactions abort or commit relative to their
parent transaction. In the case of an aborted transaction,
the database state is rolled back to the point where
the transaction was started. (This is true whether
or not the transaction is nested.) In the case of
a commit, the nested transaction’s changes become
part of its parent transaction, as if the nested
transaction boundaries had never been established.
If I had to implement this with the current SQLite,
I would start a new TEMP table at each BEGIN, and
destroy the current TEMP table at a ROLLBACK or
add its contents to its parent table (either
the previous TEMP table or the "real" table at a
COMMIT.
I think that does everything you need, and would be
easy to program.
HTH,
Gerry
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: SQLite and nested transactions
At 3:33 PM -0600 4/11/07, Dennis Cote wrote: You have lost me here. If this transaction is considered successful without executing the middle step (which is the same as executing it and then undoing that execution) then that step does not belong in this transaction. Instead of begin step 1 savepoint after_1 optional step 2 if error rollback to savepoint after_1 step 3 commit You can do begin step 1 step 3 commit begin optional step 2 if error rollback else commit Your example assumes no coupling between the different steps, which is true some times, but in the general case there can be coupling. That is, the effect that step 3 actually has can be different depending on whether step 2 is rolled back or not, either because step 3 is operating on a different database state, or because step 3 contains conditionals that cause different statements to execute depending on database state that could have been changed by step 2. So in the general case, step 2 must always be run after step 1 and before step 3. This *is* an example of an application where a nested transaction or a savepoint could be useful. However there is a fairly simple workaround that gets the same result without a nested transaction. Instead of: begin step 1 begin nested temp step 2 var = query current state rollback nested step 3 using var commit You could do: begin step 1 temp step 2 var = query current state rollback begin step 1 step 3 using var commit The cost of this approach is repeating the work done in step 1. While that workaround may be an acceptable solution for some situations, I see that as overly complicated and difficult in the general case. For one thing, it requires the application to keep track of all the details of what step 1 was, and step 1 could be arbitrarily complex. Moreover, step 1 could have been expensive, involving a large amount of data which may have been input from somewhere and can't be retrieved again nor stored in RAM; the only copy of it is in the database. Or even ignoring the last point there is still the complexity, especially if one used bind variables that were since freed up for other tasks, since you aren't just keeping a log of SQL strings to re-run. I think that a SQLite pager-based mechanism for tracking child transactions is quite a bit less complicated and more reliable than using your workaround, since no details have to be remembered but for the pages that changed. Now going off on a tangent ... To address the oft-raised comment that some people make that any proposed additions or changes to SQLite be opposed in general on the principal that "it is supposed to be lite", I say this: It is perfectly in keeping with SQLite's philosophy for us to add lots of power to it if the cost of adding that power is low. Low cost meaning that the codebase doesn't need to increase much if any, the resource efficiency of running SQLite isn't impacted much, complexity doesn't raise the bug density appreciably, and particularly, it isn't more difficult for users to use. Some features, like proper child transactions as I described, are a situation where users gain a lot of power at very little cost. Having proper child transactions means it is a lot easier for users and developers, particularly SQLite extension or wrapper writers such as myself, to add powerful features to SQLite using programs while SQLite itself is hardly more complex. Users are saved a lot of work, and SQLite developers gain next to none. By contrast, say, supporting named users in the database and concurrent database writes and stuff like that is indeed way too complicated for SQLite to have, and I still support SQLite never adding support for it. So SQLite with child transactions is only trivially less lite than it is now, which is still lite. In fact, I propose moving rollbackable child transaction support to the top of the todo list, rather than it being in the middle, given that its presence can make a lot of other todo or wishlist items much easier to implement, I believe. And if it will make a difference, I will even make a monetary donation (as I can afford to) in order to sponsor its development (though I would like to think that the benefits are compelling on their own). -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: SQLite and nested transactions
Ramon Ribó wrote:
Imagine one application that can import data from a file. You want
that, in case of computer crash, either all the data of the file is
imported or none. At the same time, you want the user to manually
accept or reject every section of the file.
This example can be modelled in a very natural way with a
transaction covering the full file import and a nested transaction
covering every section.
Ramon,
I don't see that where nested transactions are needed for this example.
You seem to be suggesting a loop reading each file section and writing
it into the database in a nested transaction and then rolling back a
nested transaction if the user says they want to skip that section.
begin
for each section in file {
read section
begin nested
insert section
if promp_user(section) == keep
commit nested
else
rollback nested
}
commit
The same thing can be done far more efficiently by prompting the user
first and only inserting the sections the user wants to keep.
begin
for each section in file {
read section
if promp_user(section) == keep
insert section
}
commit
If the program completes all users selected sections are inserted into
the database atomically. If the program crashes the entire file will be
deleted when the incomplete transaction is rolled back. Similarly if an
I/O error occur when reading the file or a disk full condition happens
when inserting a section, those and any other errors would cause the
transaction to be rolled back so that none of the file sections are
inserted. I want to insert all of the user selected sections or none of
them.
Nested transaction only create more work and make the application more
complicated.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: SQLite and nested transactions
I forgot to mention that the stream data contains a BEGIN TRANSACTION and END
TRANSACTION marker.
Ray
[EMAIL PROTECTED] wrote:
> In my case, I am a slave device that must accept a stream of commands from an
> external device. I'm not sure if I can make intelligent decisions about
> choosing what I commit to the database.
> Ray
>
> Darren Duncan <[EMAIL PROTECTED]> wrote:
> > At 12:49 PM -0600 4/11/07, Dennis Cote wrote:
> > >[EMAIL PROTECTED] wrote:
> > >>It appears that my requirements are to be able to do the following:
> > >>
> > >>BEGIN parent;
> > >>insert into t values ('a');
> > >>BEGIN child;
> > >>insert into t values ('b');
> > >>insert into t values ('c');
> > >>ROLLBACK child; // child aborts
> > >>insert into t values ('d');
> > >>COMMIT parent;
> > >>
> > >>As a result of this sequence, the table should have two new rows
> > >>with values 'a' and 'd', but not 'b' and 'c'.
> > >
> > >Can you explain why your application is rolling back the child transaction?
> > >
> > >If the above is really how your application works (and I don't think
> > >it is), then the exact same result can always be achieved with the
> > >simpler sequence:
> > >
> > >BEGIN;
> > >insert into t values ('a');
> > >insert into t values ('d');
> > >COMMIT;
> > >
> > >You don't need to bother inserting b and c if you are going to undo
> > >those insertions with a static rollback.
> >
> > While it is true in some cases that an application can be written to
> > know in advance whether certain SQL statements need to be run or not,
> > there are other cases where it can only easily know after having
> > tried it.
> >
> > One type of situation that stands out the most to me is if you have
> > state constraints defined (in some way) on the database for which it
> > is only really feasible to calculate the constraint definition after
> > DML has occurred, because you want SQLite to do the calculation
> > itself on the post-DML-state and it is technically simpler that way;
> > if the constraint fails, we would want to be able to just rollback
> > the DML that caused the state to break, but not the other valid stuff
> > before that, since we could have more stuff after this attempt that
> > needs to be atomic with stuff before the attempt.
> >
> > Well, the key thing in that example, and many situations, is that the
> > child transaction is doing something that we may or may not want to
> > rollback and we won't know until after it is tried.
> >
> > This said, I can also see situations where there is an unconditional
> > child rollback, and that is where we want SQLite itself to perform a
> > querying or calculating task using a temporarily changed database
> > state as input. We want the result of the query, but not the changed
> > state that went into its input. But there is other changed state
> > before that which does need to go into the input, and it needs to
> > persist, and be atomic with other state changes done after the
> > calculation.
> >
> > Now, you could say make the application do that work, but the fact is
> > that certain kinds of data processing are more efficiently done by a
> > DBMS itself.
> >
> > All this said, I look forward to rhurst's specific problem being
> > explained, so it is easier for us to see whether child transaction
> > support would help *that* problem.
> >
> > -- Darren Duncan
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
> >
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: SQLite and nested transactions
[EMAIL PROTECTED] wrote: I'm not sure if I can make intelligent decisions about choosing what I commit to the database. Things don't look too bright for you or your users then. ;-) I couldn't resist. :-) Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: SQLite and nested transactions
Darren Duncan wrote: While it is true in some cases that an application can be written to know in advance whether certain SQL statements need to be run or not, there are other cases where it can only easily know after having tried it. One type of situation that stands out the most to me is if you have state constraints defined (in some way) on the database for which it is only really feasible to calculate the constraint definition after DML has occurred, because you want SQLite to do the calculation itself on the post-DML-state and it is technically simpler that way; if the constraint fails, we would want to be able to just rollback the DML that caused the state to break, but not the other valid stuff before that, since we could have more stuff after this attempt that needs to be atomic with stuff before the attempt. Well, the key thing in that example, and many situations, is that the child transaction is doing something that we may or may not want to rollback and we won't know until after it is tried. Darren, You have lost me here. If this transaction is considered successful without executing the middle step (which is the same as executing it and then undoing that execution) then that step does not belong in this transaction. Instead of begin step 1 savepoint after_1 optional step 2 if error rollback to savepoint after_1 step 3 commit You can do begin step 1 step 3 commit begin optional step 2 if error rollback else commit This said, I can also see situations where there is an unconditional child rollback, and that is where we want SQLite itself to perform a querying or calculating task using a temporarily changed database state as input. We want the result of the query, but not the changed state that went into its input. But there is other changed state before that which does need to go into the input, and it needs to persist, and be atomic with other state changes done after the calculation. Now, you could say make the application do that work, but the fact is that certain kinds of data processing are more efficiently done by a DBMS itself. This *is* an example of an application where a nested transaction or a savepoint could be useful. However there is a fairly simple workaround that gets the same result without a nested transaction. Instead of: begin step 1 savepoint after_1 temp step 2 var = query current state rollback to savepoint after_1 step 3 using var commit or begin step 1 begin nested temp step 2 var = query current state rollback nested step 3 using var commit You could do: begin step 1 temp step 2 var = query current state rollback begin step 1 step 3 using var commit And in a multiple access system you could use a variation using chained transactions to ensure that no other process changes the database state between the two transactions. begin step 1 temp step 2 var = query current state rollback and chain step 1 step 3 using var commit The cost of this approach is repeating the work done in step 1. Another approach that could be used in some, but definitely not all, cases is to use additional SQL statements to undo or invert the effects of step 2. begin step 1 temp step 2 var = query current state inverse step 2 step 3 using var commit All this said, I look forward to rhurst's specific problem being explained, so it is easier for us to see whether child transaction support would help *that* problem. Likewise. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: SQLite and nested transactions
In my case, I am a slave device that must accept a stream of commands from an
external device. I'm not sure if I can make intelligent decisions about
choosing what I commit to the database.
Ray
Darren Duncan <[EMAIL PROTECTED]> wrote:
> At 12:49 PM -0600 4/11/07, Dennis Cote wrote:
> >[EMAIL PROTECTED] wrote:
> >>It appears that my requirements are to be able to do the following:
> >>
> >>BEGIN parent;
> >>insert into t values ('a');
> >>BEGIN child;
> >>insert into t values ('b');
> >>insert into t values ('c');
> >>ROLLBACK child; // child aborts
> >>insert into t values ('d');
> >>COMMIT parent;
> >>
> >>As a result of this sequence, the table should have two new rows
> >>with values 'a' and 'd', but not 'b' and 'c'.
> >
> >Can you explain why your application is rolling back the child transaction?
> >
> >If the above is really how your application works (and I don't think
> >it is), then the exact same result can always be achieved with the
> >simpler sequence:
> >
> >BEGIN;
> >insert into t values ('a');
> >insert into t values ('d');
> >COMMIT;
> >
> >You don't need to bother inserting b and c if you are going to undo
> >those insertions with a static rollback.
>
> While it is true in some cases that an application can be written to
> know in advance whether certain SQL statements need to be run or not,
> there are other cases where it can only easily know after having
> tried it.
>
> One type of situation that stands out the most to me is if you have
> state constraints defined (in some way) on the database for which it
> is only really feasible to calculate the constraint definition after
> DML has occurred, because you want SQLite to do the calculation
> itself on the post-DML-state and it is technically simpler that way;
> if the constraint fails, we would want to be able to just rollback
> the DML that caused the state to break, but not the other valid stuff
> before that, since we could have more stuff after this attempt that
> needs to be atomic with stuff before the attempt.
>
> Well, the key thing in that example, and many situations, is that the
> child transaction is doing something that we may or may not want to
> rollback and we won't know until after it is tried.
>
> This said, I can also see situations where there is an unconditional
> child rollback, and that is where we want SQLite itself to perform a
> querying or calculating task using a temporarily changed database
> state as input. We want the result of the query, but not the changed
> state that went into its input. But there is other changed state
> before that which does need to go into the input, and it needs to
> persist, and be atomic with other state changes done after the
> calculation.
>
> Now, you could say make the application do that work, but the fact is
> that certain kinds of data processing are more efficiently done by a
> DBMS itself.
>
> All this said, I look forward to rhurst's specific problem being
> explained, so it is easier for us to see whether child transaction
> support would help *that* problem.
>
> -- Darren Duncan
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: SQLite and nested transactions
At 12:49 PM -0600 4/11/07, Dennis Cote wrote:
[EMAIL PROTECTED] wrote:
It appears that my requirements are to be able to do the following:
BEGIN parent;
insert into t values ('a');
BEGIN child;
insert into t values ('b');
insert into t values ('c');
ROLLBACK child; // child aborts
insert into t values ('d');
COMMIT parent;
As a result of this sequence, the table should have two new rows
with values 'a' and 'd', but not 'b' and 'c'.
Can you explain why your application is rolling back the child transaction?
If the above is really how your application works (and I don't think
it is), then the exact same result can always be achieved with the
simpler sequence:
BEGIN;
insert into t values ('a');
insert into t values ('d');
COMMIT;
You don't need to bother inserting b and c if you are going to undo
those insertions with a static rollback.
While it is true in some cases that an application can be written to
know in advance whether certain SQL statements need to be run or not,
there are other cases where it can only easily know after having
tried it.
One type of situation that stands out the most to me is if you have
state constraints defined (in some way) on the database for which it
is only really feasible to calculate the constraint definition after
DML has occurred, because you want SQLite to do the calculation
itself on the post-DML-state and it is technically simpler that way;
if the constraint fails, we would want to be able to just rollback
the DML that caused the state to break, but not the other valid stuff
before that, since we could have more stuff after this attempt that
needs to be atomic with stuff before the attempt.
Well, the key thing in that example, and many situations, is that the
child transaction is doing something that we may or may not want to
rollback and we won't know until after it is tried.
This said, I can also see situations where there is an unconditional
child rollback, and that is where we want SQLite itself to perform a
querying or calculating task using a temporarily changed database
state as input. We want the result of the query, but not the changed
state that went into its input. But there is other changed state
before that which does need to go into the input, and it needs to
persist, and be atomic with other state changes done after the
calculation.
Now, you could say make the application do that work, but the fact is
that certain kinds of data processing are more efficiently done by a
DBMS itself.
All this said, I look forward to rhurst's specific problem being
explained, so it is easier for us to see whether child transaction
support would help *that* problem.
-- Darren Duncan
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: SQLite and nested transactions
[EMAIL PROTECTED] wrote:
It appears that my requirements are to be able to do the following:
BEGIN parent;
insert into t values ('a');
BEGIN child;
insert into t values ('b');
insert into t values ('c');
ROLLBACK child; // child aborts
insert into t values ('d');
COMMIT parent;
As a result of this sequence, the table should have two new rows with values
'a' and ‘d', but not 'b' and ‘c’.
I expanded Igor's example because a single transaction failure performs a
rollback. ( Think that is true).
Anyway, the above result is what is required and I cannot do this with the all
or none concept.
Ray,
Can you explain why your application is rolling back the child transaction?
If the above is really how your application works (and I don't think it
is), then the exact same result can always be achieved with the simpler
sequence:
BEGIN;
insert into t values ('a');
insert into t values ('d');
COMMIT;
You don't need to bother inserting b and c if you are going to undo
those insertions with a static rollback.
I see that other databases have the concept of SavePoint.
I believe this will work for me but I am not sure.
BEGIN
insert into t values ('a');
savepoint = SetSavePoint();
insert into t values ('b');
insert into t values ('c');
RestoreSavePoint(savepoint);
insert into t values ('d');
Commit
As a result of this sequence, the table should have two new rows with values
'a' and ‘d', but not 'b' and ‘c’.
The difference here is that there is only one Begin and Commit.
There can be any number of savepoints. This seems to perform the same function.
The standard SQL syntax for this sequence would be:
BEGIN;
insert into t values ('a');
SAVEPOINT after_a;
insert into t values ('b');
insert into t values ('c');
ROLLBACK TO SAVEPOINT after_a;
insert into t values ('d');
COMMIT;
This still begs the question "why are you rolling back to the savepoint"?
These samples are very much out of context in that they don't show how
or why the application is issuing this sequence of SQL statements.
Without that context information its nearly impossible decide if it must
be done this way or not.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
