Re: DDLs -- was Re: simple question on DDL
My point wasn't to pick any particular detail
of any particular example . I was merely making
the point that whilst the concept of DDL without
commits seems to be straightforward, the requirement
for designing something that could analyse and handle
all the consequent errors that might be a non-trivial
problem.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )
UK___March
USA_(FL)_May
Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )
USA_(CA, TX)_August
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
-Original Message-
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: 24 January 2003 16:40
Take your first example :
insert into t1 values (1);
drop table t1;
-- how to deal with self-deadlock ?
insert into t1 values (2);
commit;
Why does Oracle HAVE to commit when the DROP TABLE is issued ?
What if the INSERT had been issued by another session ? Would
the DROP TABLE go through in this session ?
The self-deadlock could be handled as an Error -- a Transaction
error,
with a message like cannot drop table when transaction is active in
current session.
A duhveloper who has written a very long-winded .SQL file or
procedure
and tries to drop a Table when he has an active transaction should be
caught and errored -- he shouldn't be allowed to drop his own
table, he
has made a logical error.
Take the second example :
insert into t1 values (1);
drop table t2;
-- how to deal with lock by other user ?
insert into t1 values (2);
commit;
If the DROP TABLE may have to wait for another user who has a lock
on T2,
why should the INSERT INTO T1 be committed ? Our user hasn't
completed
his transaction yet.
It all depends on what you mean by a Transaction. The way Oracle
has
written DDLs, a Transaction ends and is committed when the next DDL
is issued. But that may not be a logical transaction in the real
world.
My point is not that the DROP TABLE should be roll-backable. The
DROP
TABLE itself must always commit it's own statement. --- it's own
statement.
That is to ensure that a third user does not see inconsistency when
running
a transaction accessing T1. But why should the DROP TABLE explicitly
commit the previous statement ? In your example, you have shown that
an inconsistency may arise when a DROP is issued on a table with an
outstanding transaction.
But take the case where :
INSERT INTO T10 values ('a');
-- now I want to, for the heck of it, or bec'ose I'm a bad programmer
...
DROP TABLE XYZ ;
-- oops, my INSERT INTO T10 should be rolled back
-- but the Drop XYZ is independent
ROLLBACK;
Here, unfortunately, the DROP TABLE XYZ had already committed the
INSERT
INTO T10.
I didn't want that !
It would have been better if this DROP TABLE XYZ was an Autonomous
Transaction.
But in the first example, the DROP TABLE T1 should return an error.
So, the Oracle Kernel must have some more complexity to see if
there's any
outstanding
transaction in the current session [it already checks for other
sessions
because they
hold TM locks on the Table !] are pending against the same table
being dropped.
Hemant
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jonathan Lewis
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
DDLs -- was Re: simple question on DDL
Take your first example :
insert into t1 values (1);
drop table t1;
-- how to deal with self-deadlock ?
insert into t1 values (2);
commit;
Why does Oracle HAVE to commit when the DROP TABLE is issued ?
What if the INSERT had been issued by another session ? Would
the DROP TABLE go through in this session ?
The self-deadlock could be handled as an Error -- a Transaction error,
with a message like cannot drop table when transaction is active in
current session.
A duhveloper who has written a very long-winded .SQL file or procedure
and tries to drop a Table when he has an active transaction should be
caught and errored -- he shouldn't be allowed to drop his own table, he
has made a logical error.
Take the second example :
insert into t1 values (1);
drop table t2;
-- how to deal with lock by other user ?
insert into t1 values (2);
commit;
If the DROP TABLE may have to wait for another user who has a lock on T2,
why should the INSERT INTO T1 be committed ? Our user hasn't completed
his transaction yet.
It all depends on what you mean by a Transaction. The way Oracle has
written DDLs, a Transaction ends and is committed when the next DDL
is issued. But that may not be a logical transaction in the real world.
My point is not that the DROP TABLE should be roll-backable. The DROP
TABLE itself must always commit it's own statement. --- it's own statement.
That is to ensure that a third user does not see inconsistency when running
a transaction accessing T1. But why should the DROP TABLE explicitly
commit the previous statement ? In your example, you have shown that
an inconsistency may arise when a DROP is issued on a table with an
outstanding transaction.
But take the case where :
INSERT INTO T10 values ('a');
-- now I want to, for the heck of it, or bec'ose I'm a bad programmer ...
DROP TABLE XYZ ;
-- oops, my INSERT INTO T10 should be rolled back
-- but the Drop XYZ is independent
ROLLBACK;
Here, unfortunately, the DROP TABLE XYZ had already committed the INSERT
INTO T10.
I didn't want that !
It would have been better if this DROP TABLE XYZ was an Autonomous Transaction.
But in the first example, the DROP TABLE T1 should return an error.
So, the Oracle Kernel must have some more complexity to see if there's any
outstanding
transaction in the current session [it already checks for other sessions
because they
hold TM locks on the Table !] are pending against the same table being dropped.
Hemant
At 09:50 AM 23-01-03 -0800, you wrote:
One question to ask is whether whether all DDL
use the same strategy. Similarly, if you have
multiple code paths for do a ddl call how much
more risk of error do you introduce to the kernel.
Finally how do you get a consistent error response
to the end user if the error condition of apparently
identical events can fail in extremely different ways.
Consider the complexities of finding a consistent
kernel level approach to:
insert into t1 values (1);
drop table t1;
-- how to deal with self-deadlock ?
insert into t1 values (2);
commit;
insert into t1 values (1);
drop table t2;
-- how to deal with lock by other user ?
insert into t1 values (2);
commit;
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )
Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )
England__January 21/23
USA_(CA, TX)_August
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
-Original Message-
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: 23 January 2003 16:31
Arup,
I see your point and agree that the DDL should be an autonomous tx.
Perhaps an enhancement request is in order? Since Oracle has the
autonomous
tx code, integrating into the kernel should be considered...perhaps
for
Oracle 38i?
In the absence of that change, I must disagree and say that ddl must
issue a commit. If you look at a combination of your example and
mine, the
problem of waiting transactions still exists. In order to create a
table,
space must be allocated. Again, if fet$ contains only 1 row for the
particular file where the table is to be created, there is a
potential for a
serious locking problem.
Dan
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jonathan Lewis
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Hemant K Chitale
My web site page is :
RE: DDLs -- was Re: simple question on DDL
Hemant,
My guess is that Oracle, at some point in time long ago, decided that DDL's
and DML's should not be mixed together. Because they could not (or did not
want to) deal with the issue, they decided to perform an implicit commit
before any DDL statement was issued. Case closed. This is the way it was
done.
Things have changed in the (more than??) 10 years that this code was
written. We are all smarter and have much more experience in what we would
like to see happen, rather than how it is currently coded. Could Oracle
change this code? Absolutely. Will they? Only if enough people ask for it
to be changed. Would I like to see it changed? No. I really don't see the
need. I think DDL and DML are two different things and should not be mixed
together. If they are mixed together, then developers need to learn how it
works and deal with it.
just my 2 cents.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Friday, January 24, 2003 10:30 AM
To: Multiple recipients of list ORACLE-L
Take your first example :
insert into t1 values (1);
drop table t1;
-- how to deal with self-deadlock ?
insert into t1 values (2);
commit;
Why does Oracle HAVE to commit when the DROP TABLE is issued ?
What if the INSERT had been issued by another session ? Would
the DROP TABLE go through in this session ?
The self-deadlock could be handled as an Error -- a Transaction error,
with a message like cannot drop table when transaction is active in
current session.
A duhveloper who has written a very long-winded .SQL file or procedure
and tries to drop a Table when he has an active transaction should be
caught and errored -- he shouldn't be allowed to drop his own table, he
has made a logical error.
Take the second example :
insert into t1 values (1);
drop table t2;
-- how to deal with lock by other user ?
insert into t1 values (2);
commit;
If the DROP TABLE may have to wait for another user who has a lock on T2,
why should the INSERT INTO T1 be committed ? Our user hasn't completed
his transaction yet.
It all depends on what you mean by a Transaction. The way Oracle has
written DDLs, a Transaction ends and is committed when the next DDL
is issued. But that may not be a logical transaction in the real world.
My point is not that the DROP TABLE should be roll-backable. The DROP
TABLE itself must always commit it's own statement. --- it's own statement.
That is to ensure that a third user does not see inconsistency when running
a transaction accessing T1. But why should the DROP TABLE explicitly
commit the previous statement ? In your example, you have shown that
an inconsistency may arise when a DROP is issued on a table with an
outstanding transaction.
But take the case where :
INSERT INTO T10 values ('a');
-- now I want to, for the heck of it, or bec'ose I'm a bad programmer ...
DROP TABLE XYZ ;
-- oops, my INSERT INTO T10 should be rolled back
-- but the Drop XYZ is independent
ROLLBACK;
Here, unfortunately, the DROP TABLE XYZ had already committed the INSERT
INTO T10.
I didn't want that !
It would have been better if this DROP TABLE XYZ was an Autonomous
Transaction.
But in the first example, the DROP TABLE T1 should return an error.
So, the Oracle Kernel must have some more complexity to see if there's any
outstanding
transaction in the current session [it already checks for other sessions
because they
hold TM locks on the Table !] are pending against the same table being
dropped.
Hemant
At 09:50 AM 23-01-03 -0800, you wrote:
One question to ask is whether whether all DDL
use the same strategy. Similarly, if you have
multiple code paths for do a ddl call how much
more risk of error do you introduce to the kernel.
Finally how do you get a consistent error response
to the end user if the error condition of apparently
identical events can fail in extremely different ways.
Consider the complexities of finding a consistent
kernel level approach to:
insert into t1 values (1);
drop table t1;
-- how to deal with self-deadlock ?
insert into t1 values (2);
commit;
insert into t1 values (1);
drop table t2;
-- how to deal with lock by other user ?
insert into t1 values (2);
commit;
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )
Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )
England__January 21/23
USA_(CA, TX)_August
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
-Original Message-
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: 23 January 2003 16:31
Arup,
I see your point and agree that the DDL should be an autonomous tx.
Perhaps an enhancement request is in order? Since Oracle has the
autonomous
tx code, integrating into the kernel should be considered...perhaps
for
Oracle 38i?
In the absence of that change, I must
RE: DDLs -- was Re: simple question on DDL
Tom - I think you've nailed it. Think of the design decisions that some of
Oracle's competitors made in the early days and how silly they seem in
retrospect. Anyone remember the row-locking vs. block-locking wars?
The other aspect that many people don't think of if they have never
worked in a vendor's development staff is inertia. You as a developer are
assigned a list of tasks which is usually larger than you can accomplish.
Marketing bases those tasks on 1) which features will get more customers or
2) what customers are howling about -- where is the ROI? Changing how the
kernel works is a high-risk business, so I think you are probably stuck with
the way it works, even if you feel it is inconsistent.
If Oracle did go back and fix stuff in this area, there may be customer
scripts that rely on this behavior, even if we feel it is inconsistent.
-Original Message-
Sent: Friday, January 24, 2003 11:35 AM
To: Multiple recipients of list ORACLE-L
Hemant,
My guess is that Oracle, at some point in time long ago, decided that DDL's
and DML's should not be mixed together. Because they could not (or did not
want to) deal with the issue, they decided to perform an implicit commit
before any DDL statement was issued. Case closed. This is the way it was
done.
Things have changed in the (more than??) 10 years that this code was
written. We are all smarter and have much more experience in what we would
like to see happen, rather than how it is currently coded. Could Oracle
change this code? Absolutely. Will they? Only if enough people ask for it
to be changed. Would I like to see it changed? No. I really don't see the
need. I think DDL and DML are two different things and should not be mixed
together. If they are mixed together, then developers need to learn how it
works and deal with it.
just my 2 cents.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Friday, January 24, 2003 10:30 AM
To: Multiple recipients of list ORACLE-L
Take your first example :
insert into t1 values (1);
drop table t1;
-- how to deal with self-deadlock ?
insert into t1 values (2);
commit;
Why does Oracle HAVE to commit when the DROP TABLE is issued ?
What if the INSERT had been issued by another session ? Would
the DROP TABLE go through in this session ?
The self-deadlock could be handled as an Error -- a Transaction error,
with a message like cannot drop table when transaction is active in
current session.
A duhveloper who has written a very long-winded .SQL file or procedure
and tries to drop a Table when he has an active transaction should be
caught and errored -- he shouldn't be allowed to drop his own table, he
has made a logical error.
Take the second example :
insert into t1 values (1);
drop table t2;
-- how to deal with lock by other user ?
insert into t1 values (2);
commit;
If the DROP TABLE may have to wait for another user who has a lock on T2,
why should the INSERT INTO T1 be committed ? Our user hasn't completed
his transaction yet.
It all depends on what you mean by a Transaction. The way Oracle has
written DDLs, a Transaction ends and is committed when the next DDL
is issued. But that may not be a logical transaction in the real world.
My point is not that the DROP TABLE should be roll-backable. The DROP
TABLE itself must always commit it's own statement. --- it's own statement.
That is to ensure that a third user does not see inconsistency when running
a transaction accessing T1. But why should the DROP TABLE explicitly
commit the previous statement ? In your example, you have shown that
an inconsistency may arise when a DROP is issued on a table with an
outstanding transaction.
But take the case where :
INSERT INTO T10 values ('a');
-- now I want to, for the heck of it, or bec'ose I'm a bad programmer ...
DROP TABLE XYZ ;
-- oops, my INSERT INTO T10 should be rolled back
-- but the Drop XYZ is independent
ROLLBACK;
Here, unfortunately, the DROP TABLE XYZ had already committed the INSERT
INTO T10.
I didn't want that !
It would have been better if this DROP TABLE XYZ was an Autonomous
Transaction.
But in the first example, the DROP TABLE T1 should return an error.
So, the Oracle Kernel must have some more complexity to see if there's any
outstanding
transaction in the current session [it already checks for other sessions
because they
hold TM locks on the Table !] are pending against the same table being
dropped.
Hemant
At 09:50 AM 23-01-03 -0800, you wrote:
One question to ask is whether whether all DDL
use the same strategy. Similarly, if you have
multiple code paths for do a ddl call how much
more risk of error do you introduce to the kernel.
Finally how do you get a consistent error response
to the end user if the error condition of apparently
identical events can fail in extremely different ways.
Consider the complexities of finding a consistent
kernel level approach to:
insert into t1 values (1);
drop table t1;
--
RE: DDLs -- was Re: simple question on DDL
I think DDL and DML are two different things and should not be mixed together. If they are mixed together, then developers need to learn how it works and deal with it. And there you have it. If you need DDL in your code, isolate it in an anonymous transaction, or just be sure that you are at a safe commit point. jared Mercadante, Thomas F [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/24/2003 09:34 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: DDLs -- was Re: simple question on DDL Hemant, My guess is that Oracle, at some point in time long ago, decided that DDL's and DML's should not be mixed together. Because they could not (or did not want to) deal with the issue, they decided to perform an implicit commit before any DDL statement was issued. Case closed. This is the way it was done. Things have changed in the (more than??) 10 years that this code was written. We are all smarter and have much more experience in what we would like to see happen, rather than how it is currently coded. Could Oracle change this code? Absolutely. Will they? Only if enough people ask for it to be changed. Would I like to see it changed? No. I really don't see the need. I think DDL and DML are two different things and should not be mixed together. If they are mixed together, then developers need to learn how it works and deal with it. just my 2 cents. Tom Mercadante Oracle Certified Professional -Original Message- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DDLs -- was Re: simple question on DDL
Agreed. There's a lot of code out there that was developed considering the
way Oracle handled DDL and DML specifically.
Although I would have liked Oracle to handle DDL as an Autonomous Transaction
and provide an error message for the scenario in the first example, I guess
it might be too late to change the kernel now.
As Arup said, hopefully Oracle 38i will allow us to mix DDL with DML
and yet have DDL as Autonomous Transactions.
When Oracle didn't have Sequences before Oracle6, people wrote code
to SELECT MAX(KEY)+1. There might still be a few developers and
few pieces of code doing precisely that instead of using a sequence [of
course, development which is RDBMS-independent might not use an
Oracle Sequence].
I was just expressing a wish that Oracle could have done DDL and DML
better.We are stuck with QWERTY and we are stuck with DDL
that does an Auto-Commit.
Hemant
At 10:49 AM 24-01-03 -0800, you wrote:
Tom - I think you've nailed it. Think of the design decisions that some of
Oracle's competitors made in the early days and how silly they seem in
retrospect. Anyone remember the row-locking vs. block-locking wars?
The other aspect that many people don't think of if they have never
worked in a vendor's development staff is inertia. You as a developer are
assigned a list of tasks which is usually larger than you can accomplish.
Marketing bases those tasks on 1) which features will get more customers or
2) what customers are howling about -- where is the ROI? Changing how the
kernel works is a high-risk business, so I think you are probably stuck with
the way it works, even if you feel it is inconsistent.
If Oracle did go back and fix stuff in this area, there may be customer
scripts that rely on this behavior, even if we feel it is inconsistent.
-Original Message-
Sent: Friday, January 24, 2003 11:35 AM
To: Multiple recipients of list ORACLE-L
Hemant,
My guess is that Oracle, at some point in time long ago, decided that DDL's
and DML's should not be mixed together. Because they could not (or did not
want to) deal with the issue, they decided to perform an implicit commit
before any DDL statement was issued. Case closed. This is the way it was
done.
Things have changed in the (more than??) 10 years that this code was
written. We are all smarter and have much more experience in what we would
like to see happen, rather than how it is currently coded. Could Oracle
change this code? Absolutely. Will they? Only if enough people ask for it
to be changed. Would I like to see it changed? No. I really don't see the
need. I think DDL and DML are two different things and should not be mixed
together. If they are mixed together, then developers need to learn how it
works and deal with it.
just my 2 cents.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Friday, January 24, 2003 10:30 AM
To: Multiple recipients of list ORACLE-L
Take your first example :
insert into t1 values (1);
drop table t1;
-- how to deal with self-deadlock ?
insert into t1 values (2);
commit;
Why does Oracle HAVE to commit when the DROP TABLE is issued ?
What if the INSERT had been issued by another session ? Would
the DROP TABLE go through in this session ?
The self-deadlock could be handled as an Error -- a Transaction error,
with a message like cannot drop table when transaction is active in
current session.
A duhveloper who has written a very long-winded .SQL file or procedure
and tries to drop a Table when he has an active transaction should be
caught and errored -- he shouldn't be allowed to drop his own table, he
has made a logical error.
Take the second example :
insert into t1 values (1);
drop table t2;
-- how to deal with lock by other user ?
insert into t1 values (2);
commit;
If the DROP TABLE may have to wait for another user who has a lock on T2,
why should the INSERT INTO T1 be committed ? Our user hasn't completed
his transaction yet.
It all depends on what you mean by a Transaction. The way Oracle has
written DDLs, a Transaction ends and is committed when the next DDL
is issued. But that may not be a logical transaction in the real world.
My point is not that the DROP TABLE should be roll-backable. The DROP
TABLE itself must always commit it's own statement. --- it's own statement.
That is to ensure that a third user does not see inconsistency when running
a transaction accessing T1. But why should the DROP TABLE explicitly
commit the previous statement ? In your example, you have shown that
an inconsistency may arise when a DROP is issued on a table with an
outstanding transaction.
But take the case where :
INSERT INTO T10 values ('a');
-- now I want to, for the heck of it, or bec'ose I'm a bad programmer ...
DROP TABLE XYZ ;
-- oops, my INSERT INTO T10 should be rolled back
-- but the Drop XYZ is independent
ROLLBACK;
Here, unfortunately, the DROP TABLE XYZ had already committed the INSERT
INTO T10.
I didn't want that !
It
