Re: Isolation levels in Informix vs Oracle

DA Morgan said: > Obnoxio The Chav wrote: > >> DA Morgan said: >> >>>>>that as other jobs utilize resources it refines its estimate taking >>>>> them >>>>>into account. >>>> >>>>Yeah, that would never happen in the way I coded my progress >>>> monitoring. >>>>Gee, let me switch to Oracle right away, it does things that I have a >>>>library for. >>> >>>Yeah. Why would anyone want, built-in and included in a product, >>>something they could charge their employer thousands of dollars to >>>reinvent. I keep forgetting I bill by the hour. >> >> In what way does something in a library require reinvention? And please >> don't try and tell me Oracle aren't charging for their built-in >> functionality. If I had their sales, I could charge the same pennies per >> copy that they do. But they definitely charge for it. I just charge more >> for my library than they do, but they more than make it up in >> maintenance >> costs and license revenue for all the other neat stuff Oracle does. > > Last time I looked neither Oracle nor Informix was shareware so you got > me there: They all cost money. But we both know that wasn't the point I > was making. The point was how much money was going to be spent, above I thought I did, now I'm even more lost. Where did shareware come from? > and beyond the product cost to make up for what was missing. > > This is not some stupid us vs them flamewar starter: Not an attack on > Informix at all. Just an attack on the use of the phrase "red herring" > when none was present. Lets call it a day as there is no point in > pursuing this into something it was not intended to be. But there _was_ a red herring: savepoints have got nothing to do with DIRTY READS. Mark was trying to drag the discussion into a feature battle that was irrelevant to the issue at hand. By your defending this illogical approach, you lack internal consistency with your stated desire not to have a stupid flamewar. So which is it? -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche "I'm trying to see things your way, but I can't get my head up my ass" - JCH "Ogni uomo mi guarda come se fossi una testa di cazzo" - Marco I went to the airport to check in and they asked what I did because I looked like a terrorist. I said I was a comedian. They said, "Say something funny then." I told them I had just graduated from flying school. -- Ahmed Ahmed sending to informix-list

Obnoxio The Chav wrote: > Mark was trying to drag the discussion into a feature battle > that was irrelevant to the issue at hand. Au contraire. The discussion was around a justification for dirty read. The OP opined that dirty reads were useful for determining status of a very long running transaction that spanned multiple DMLS. I questioned if this was really a likely scenario, knowing a little about the propblems such long transactions can cause (in both Oracle and Informix), and, as an aside to this questioning, wondered aloud if Informix had save points. I genuinely wanted to know. The feature battles with Informix are long, long over.

Mark Townsend wrote: > Obnoxio The Chav wrote: > >> Mark was trying to drag the discussion into a feature battle >> that was irrelevant to the issue at hand. > > > Au contraire. The discussion was around a justification for dirty read. > The OP opined that dirty reads were useful for determining status of a > very long running transaction that spanned multiple DMLS. I questioned > if this was really a likely scenario, knowing a little about the > propblems such long transactions can cause (in both Oracle and > Informix), and, as an aside to this questioning, wondered aloud if > Informix had save points. I genuinely wanted to know. The feature > battles with Informix are long, long over. > An interesting topic in its own right and the differences between products are tricky and interaction with transactions is confusing (at least to me). To the best of my knowledge IDS does not support savepoints. What IDS does support is an auto-commit mode which can be suspended via "BEGIN WORK". By contrast Sybase/MS SQL Server supports nested transactions. The outermost transaction (BEGIN WORK (?) - I don't remember the exact syntax) is equivalent to what IDS has. Any subsequent nested transaction is the equivalent of an unnamed savepoint. DB2 for LUW supports nested save points but they are language wise orthogonal to transactions. (I RELEASE, but not COMMIT a save point and a COMMIT inside a savepoint will release all the save points up the stack and commit the transaction. DB2 today does not support "explicit transaction start" or "deep auto-commit" (auto-commit is a client side game of batching a commit to every request). In a way I find the Sybase/MS SQL Server approach appealing since it's recursive. IDS requires awareness of whether a stored procedure is under auto commit or not to do proper error-handling. In DB2 we had long debates whether we should allow COMMIT in procedures to begin with because it really results in convoluted programming due. (i.e. use save points for everything and reserve transaction control to the app) What does Oracle do? I do know that Oracle's situation is further complicated by the fact that DDL is auto-committed. Cheers Serge

"Mark Townsend" <markbtownsend@comcast.net> wrote > The feature battles with Informix are long, long over. what about performance battle :-)

Serge Rielau wrote: > Mark Townsend wrote: > >> Obnoxio The Chav wrote: >> >>> Mark was trying to drag the discussion into a feature battle >>> that was irrelevant to the issue at hand. >> >> >> >> Au contraire. The discussion was around a justification for dirty >> read. The OP opined that dirty reads were useful for determining >> status of a very long running transaction that spanned multiple >> DMLS. I questioned if this was really a likely scenario, knowing a >> little about the propblems such long transactions can cause (in both >> Oracle and Informix), and, as an aside to this questioning, wondered >> aloud if Informix had save points. I genuinely wanted to know. The >> feature battles with Informix are long, long over. >> > An interesting topic in its own right and the differences between > products are tricky and interaction with transactions is confusing (at > least to me). > To the best of my knowledge IDS does not support savepoints. > What IDS does support is an auto-commit mode which can be suspended via > "BEGIN WORK". > By contrast Sybase/MS SQL Server supports nested transactions. The > outermost transaction (BEGIN WORK (?) - I don't remember the exact > syntax) is equivalent to what IDS has. > Any subsequent nested transaction is the equivalent of an unnamed > savepoint. > DB2 for LUW supports nested save points but they are language wise > orthogonal to transactions. (I RELEASE, but not COMMIT a save point and > a COMMIT inside a savepoint will release all the save points up the > stack and commit the transaction. > DB2 today does not support "explicit transaction start" or "deep > auto-commit" (auto-commit is a client side game of batching a commit to > every request). > In a way I find the Sybase/MS SQL Server approach appealing since it's > recursive. IDS requires awareness of whether a stored procedure is under > auto commit or not to do proper error-handling. > In DB2 we had long debates whether we should allow COMMIT in procedures > to begin with because it really results in convoluted programming due. > (i.e. use save points for everything and reserve transaction control to > the app) > > What does Oracle do? I do know that Oracle's situation is further > complicated by the fact that DDL is auto-committed. > > Cheers > Serge Here's a simple demo of SAVEPOINT CREATE TABLE t1 (testcol NUMBER); -- an anonymous block in which i is not allowed to become zero DECLARE i INTEGER := 3; BEGIN INSERT INTO t1 (testcol) VALUES (10/i); SAVEPOINT A; i := i-1; INSERT INTO t1 (testcol) VALUES (10/i); i := i-1; INSERT INTO t1 (testcol) VALUES (10/i);/* i := i-1; INSERT INTO t1 (testcol) VALUES (10/i); i := i-1; INSERT INTO t1 (testcol) VALUES (10/i);*/ COMMIT; EXCEPTION WHEN ZERO_DIVIDE THEN ROLLBACK TO SAVEPOINT A; COMMIT; END testblock; / SQL> SELECT * FROM t1; TESTCOL ---------- 3.33333333 5 10 TRUNCATE TABLE t1; -- an anonymous block in which i is not allowed to become zero DECLARE i INTEGER := 3; BEGIN INSERT INTO t1 (testcol) VALUES (10/i); SAVEPOINT A; i := i-1; INSERT INTO t1 (testcol) VALUES (10/i); i := i-1; INSERT INTO t1 (testcol) VALUES (10/i); i := i-1; INSERT INTO t1 (testcol) VALUES (10/i); i := i-1; INSERT INTO t1 (testcol) VALUES (10/i); COMMIT; EXCEPTION WHEN ZERO_DIVIDE THEN ROLLBACK TO SAVEPOINT A; COMMIT; END testblock; / SQL> SELECT * FROM t1; TESTCOL ---------- 3.33333333 The savepoint allows the exception handler to return the procedure to a specific point in the transaction. Oracle does not autocommit except for DDL and it is not so much an autocommit as it is the fact that DDL is dynamically rewritten as an anonymous block before processing in the following form: BEGIN COMMIT; -- DDL statement here COMMIT; END; / This is not much of a problem as in Oracle there is never a need to perform DDL within an application session unlike some products such as SQL Server. HTH -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace 'x' with 'u' to respond)

rkusenet wrote: > "Mark Townsend" <markbtownsend@comcast.net> wrote > > >>The feature battles with Informix are long, long over. > > > what about performance battle :-) Seems IBM is waging it with DB2: Not Informix. -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace 'x' with 'u' to respond)

DA Morgan wrote: > Serge Rielau wrote: >> An interesting topic in its own right and the differences between >> products are tricky and interaction with transactions is confusing (at >> least to me). >> To the best of my knowledge IDS does not support savepoints. >> What IDS does support is an auto-commit mode which can be suspended >> via "BEGIN WORK". >> By contrast Sybase/MS SQL Server supports nested transactions. The >> outermost transaction (BEGIN WORK (?) - I don't remember the exact >> syntax) is equivalent to what IDS has. >> Any subsequent nested transaction is the equivalent of an unnamed >> savepoint. >> DB2 for LUW supports nested save points but they are language wise >> orthogonal to transactions. (I RELEASE, but not COMMIT a save point >> and a COMMIT inside a savepoint will release all the save points up >> the stack and commit the transaction. >> DB2 today does not support "explicit transaction start" or "deep >> auto-commit" (auto-commit is a client side game of batching a commit >> to every request). >> In a way I find the Sybase/MS SQL Server approach appealing since it's >> recursive. IDS requires awareness of whether a stored procedure is >> under auto commit or not to do proper error-handling. >> In DB2 we had long debates whether we should allow COMMIT in >> procedures to begin with because it really results in convoluted >> programming due. >> (i.e. use save points for everything and reserve transaction control >> to the app) >> >> What does Oracle do? I do know that Oracle's situation is further >> complicated by the fact that DDL is auto-committed. >> >> Cheers >> Serge > > > Here's a simple demo of SAVEPOINT > > CREATE TABLE t1 (> testcol NUMBER); > > -- an anonymous block in which i is not allowed to become zero > DECLARE > i INTEGER := 3; > BEGIN > INSERT INTO t1 (testcol) VALUES (10/i);> > SAVEPOINT A; > > i := i-1; > INSERT INTO t1 (testcol) VALUES (10/i);> > i := i-1; > INSERT INTO t1 (testcol) VALUES (10/i);> /* > i := i-1; > INSERT INTO t1 (testcol) VALUES (10/i);> > i := i-1; > INSERT INTO t1 (testcol) VALUES (10/i);> */ > COMMIT; > EXCEPTION > WHEN ZERO_DIVIDE THEN > ROLLBACK TO SAVEPOINT A; > COMMIT; > END testblock; > / > > SQL> SELECT * FROM t1; > > TESTCOL > ---------- > 3.33333333 > 5 > 10 > > TRUNCATE TABLE t1; > > -- an anonymous block in which i is not allowed to become zero > DECLARE > i INTEGER := 3; > BEGIN > INSERT INTO t1 (testcol) VALUES (10/i);> > SAVEPOINT A; > > i := i-1; > INSERT INTO t1 (testcol) VALUES (10/i);> > i := i-1; > INSERT INTO t1 (testcol) VALUES (10/i);> > i := i-1; > INSERT INTO t1 (testcol) VALUES (10/i);> > i := i-1; > INSERT INTO t1 (testcol) VALUES (10/i);> > COMMIT; > EXCEPTION > WHEN ZERO_DIVIDE THEN > ROLLBACK TO SAVEPOINT A; > COMMIT; > END testblock; > / > > SQL> SELECT * FROM t1; > > TESTCOL > ---------- > 3.33333333 > > The savepoint allows the exception handler to return the procedure > to a specific point in the transaction. > > Oracle does not autocommit except for DDL and it is not so much > an autocommit as it is the fact that DDL is dynamically rewritten > as an anonymous block before processing in the following form: > > BEGIN > COMMIT; > -- DDL statement here > COMMIT; > END; > / > > This is not much of a problem as in Oracle there is never a need > to perform DDL within an application session unlike some products > such as SQL Server. > > HTH What about nesting? Can you declare svaepoint B inside of svaepoint A. Can I rollback to an arbitrary point. What happens if I simply say COMMIT without releasing/commiting the save point? I ask because these are questions that differentiate to MS SQL Server. Cheers Serge

Serge Rielau wrote: > DA Morgan wrote: > >> Serge Rielau wrote: >> >>> An interesting topic in its own right and the differences between >>> products are tricky and interaction with transactions is confusing >>> (at least to me). >>> To the best of my knowledge IDS does not support savepoints. >>> What IDS does support is an auto-commit mode which can be suspended >>> via "BEGIN WORK". >>> By contrast Sybase/MS SQL Server supports nested transactions. The >>> outermost transaction (BEGIN WORK (?) - I don't remember the exact >>> syntax) is equivalent to what IDS has. >>> Any subsequent nested transaction is the equivalent of an unnamed >>> savepoint. >>> DB2 for LUW supports nested save points but they are language wise >>> orthogonal to transactions. (I RELEASE, but not COMMIT a save point >>> and a COMMIT inside a savepoint will release all the save points up >>> the stack and commit the transaction. >>> DB2 today does not support "explicit transaction start" or "deep >>> auto-commit" (auto-commit is a client side game of batching a commit >>> to every request). >>> In a way I find the Sybase/MS SQL Server approach appealing since >>> it's recursive. IDS requires awareness of whether a stored procedure >>> is under auto commit or not to do proper error-handling. >>> In DB2 we had long debates whether we should allow COMMIT in >>> procedures to begin with because it really results in convoluted >>> programming due. >>> (i.e. use save points for everything and reserve transaction control >>> to the app) >>> >>> What does Oracle do? I do know that Oracle's situation is further >>> complicated by the fact that DDL is auto-committed. >>> >>> Cheers >>> Serge >> >> >> >> Here's a simple demo of SAVEPOINT >> >> CREATE TABLE t1 (>> testcol NUMBER); >> >> -- an anonymous block in which i is not allowed to become zero >> DECLARE >> i INTEGER := 3; >> BEGIN >> INSERT INTO t1 (testcol) VALUES (10/i);>> >> SAVEPOINT A; >> >> i := i-1; >> INSERT INTO t1 (testcol) VALUES (10/i);>> >> i := i-1; >> INSERT INTO t1 (testcol) VALUES (10/i);>> /* >> i := i-1; >> INSERT INTO t1 (testcol) VALUES (10/i);>> >> i := i-1; >> INSERT INTO t1 (testcol) VALUES (10/i);>> */ >> COMMIT; >> EXCEPTION >> WHEN ZERO_DIVIDE THEN >> ROLLBACK TO SAVEPOINT A; >> COMMIT; >> END testblock; >> / >> >> SQL> SELECT * FROM t1; >> >> TESTCOL >> ---------- >> 3.33333333 >> 5 >> 10 >> >> TRUNCATE TABLE t1; >> >> -- an anonymous block in which i is not allowed to become zero >> DECLARE >> i INTEGER := 3; >> BEGIN >> INSERT INTO t1 (testcol) VALUES (10/i);>> >> SAVEPOINT A; >> >> i := i-1; >> INSERT INTO t1 (testcol) VALUES (10/i);>> >> i := i-1; >> INSERT INTO t1 (testcol) VALUES (10/i);>> >> i := i-1; >> INSERT INTO t1 (testcol) VALUES (10/i);>> >> i := i-1; >> INSERT INTO t1 (testcol) VALUES (10/i);>> >> COMMIT; >> EXCEPTION >> WHEN ZERO_DIVIDE THEN >> ROLLBACK TO SAVEPOINT A; >> COMMIT; >> END testblock; >> / >> >> SQL> SELECT * FROM t1; >> >> TESTCOL >> ---------- >> 3.33333333 >> >> The savepoint allows the exception handler to return the procedure >> to a specific point in the transaction. >> >> Oracle does not autocommit except for DDL and it is not so much >> an autocommit as it is the fact that DDL is dynamically rewritten >> as an anonymous block before processing in the following form: >> >> BEGIN >> COMMIT; >> -- DDL statement here >> COMMIT; >> END; >> / >> >> This is not much of a problem as in Oracle there is never a need >> to perform DDL within an application session unlike some products >> such as SQL Server. >> >> HTH > > What about nesting? Can you declare svaepoint B inside of svaepoint A. > Can I rollback to an arbitrary point. > What happens if I simply say COMMIT without releasing/commiting the save > point? > I ask because these are questions that differentiate to MS SQL Server. > > Cheers > Serge Absolutely. One can have as many savepoints as they wish either within a single block or nested within multiple blocks. And yes you can arbitrarily roll back to any savepoint. DECLARE x VARCHAR2(1) := 'C'; BEGIN INSERT INTO t1 (testcol) VALUES (1); SAVEPOINT A; INSERT INTO t1 (testcol) VALUES (2); SAVEPOINT B; INSERT INTO t1 (testcol) VALUES (3); SAVEPOINT C; INSERT INTO t1 (testcol) VALUES (4); SAVEPOINT D; EXECUTE IMMEDIATE 'ROLLBACK TO SAVEPOINT ' || x; COMMIT; END; / Changing the value of the variable x will take you where-ever you wish. A commit completes the transaction and the savepoints evaporate. -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace 'x' with 'u' to respond)

DA Morgan wrote: > A commit completes the transaction and the savepoints evaporate. Thanks, that makes Oracle and DB2 compatible and SQL Server the odd one out. Cheers Serge

Serge Rielau wrote: > DA Morgan wrote: > >> A commit completes the transaction and the savepoints evaporate. > > Thanks, that makes Oracle and DB2 compatible and SQL Server the odd one > out. > > Cheers > Serge Well .... expect that Oracle has nested (autonomous) transactions as well. Really, really useful for audit type operations