RE: commit for triggers
A two-phase commit is simply a way to make sure that commits happen in a distributed transaction the same way that they do in a local transaction. The absolute rule is: Everything commits or Nothing does. In-between, with some parts committed and some not, is NOT tolerable. So in your transaction, the change to the audit log is NOT committed if any part of the transaction fails. Everything from the beginning of a transaction up to a commit or rollback command is part of the transaction. All DDL commands are transactions unto themselves, so they end the prior transaction (which is committed, if you have autocommit turned on, or rolled back otherwise) and the command following a DDL command starts a new transaction. Triggers execute within the same transaction as the command that triggered them, and may not include a commit or rollback. So any DML in a trigger is only committed if the entire transaction is committed. There is only one exception to this behavior. You can declare a stored procedure as an Autonomous Transaction, which means that you are starting a new transaction that is independant of the current transaction. This means that the new transaction can commit or rollback without affecting or being affected by the current transaction, and can fail without causing the current transaction to fail or succeed, even if the current transaction fails. This is very useful and powerful, but use it with caution, because you are no longer protected by the normal transaction safeguards. -Original Message- Sent: Friday, January 23, 2004 9:15 AM To: Multiple recipients of list ORACLE-L Hi All, I have a before update trigger for a local table. I know Oracle does not commit the inserting audit entry into the audit log table until the user commits the changes on the audited table. Can I assume Oracle issues one commit for both changes? When commit fails, both changes will be rolled back. However, Oracle uses two-phase commit if a trigger updates remote tables in a distributed database. What happens if Oracle commits the change in audit log table and my change subsequently fails? _ Learn how to choose, serve, and enjoy wine at Wine @ MSN. http://wine.msn.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Flack 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: commit for triggers
John, Thanks for your very detail explanation. From: John Flack [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: commit for triggers Date: Fri, 23 Jan 2004 09:09:34 -0800 A two-phase commit is simply a way to make sure that commits happen in a distributed transaction the same way that they do in a local transaction. The absolute rule is: Everything commits or Nothing does. In-between, with some parts committed and some not, is NOT tolerable. So in your transaction, the change to the audit log is NOT committed if any part of the transaction fails. Everything from the beginning of a transaction up to a commit or rollback command is part of the transaction. All DDL commands are transactions unto themselves, so they end the prior transaction (which is committed, if you have autocommit turned on, or rolled back otherwise) and the command following a DDL command starts a new transaction. Triggers execute within the same transaction as the command that triggered them, and may not include a commit or rollback. So any DML in a trigger is only committed if the entire transaction is committed. There is only one exception to this behavior. You can declare a stored procedure as an Autonomous Transaction, which means that you are starting a new transaction that is independant of the current transaction. This means that the new transaction can commit or rollback without affecting or being affected by the current transaction, and can fail without causing the current transaction to fail or succeed, even if the current transaction fails. This is very useful and powerful, but use it with caution, because you are no longer protected by the normal transaction safeguards. -Original Message- Sent: Friday, January 23, 2004 9:15 AM To: Multiple recipients of list ORACLE-L Hi All, I have a before update trigger for a local table. I know Oracle does not commit the inserting audit entry into the audit log table until the user commits the changes on the audited table. Can I assume Oracle issues one commit for both changes? When commit fails, both changes will be rolled back. However, Oracle uses two-phase commit if a trigger updates remote tables in a distributed database. What happens if Oracle commits the change in audit log table and my change subsequently fails? _ Learn how to choose, serve, and enjoy wine at Wine @ MSN. http://wine.msn.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Flack 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). _ Check out the new MSN 9 Dial-up fast reliable Internet access with prime features! http://join.msn.com/?pgmarket=en-uspage=dialup/homeST=1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd 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: commit for triggers
John, I agree w/ everything you said, except for the autocommit functionality. Autocommit setting has no impact on whether DDL will commit or rollback any in progress transaction. DDL always commits an in-progress transaction. The short example below speaks for itself. (8.1.7.4 on Solaris 2.8) SQL show autocommit autocommit OFF SQL desc a Name Null?Type - COL1 NUMBER COL2 NUMBER SQL select * from a where col1=-12345; no rows selected SQL insert into a values(-12345,-12345); 1 row created. SQL create table xxx(a number); Table created. SQL select * from a where col1=-12345; COL1 COL2 -- -- -12345 -12345 1 row selected. Mark J. Bobak Oracle DBA ProQuest Company Ann Arbor, MI Imagination was given to man to compensate him for what he is not, and a sense of humor was provided to console him for what he is. --Unknown -Original Message- Sent: Friday, January 23, 2004 12:10 PM To: Multiple recipients of list ORACLE-L A two-phase commit is simply a way to make sure that commits happen in a distributed transaction the same way that they do in a local transaction. The absolute rule is: Everything commits or Nothing does. In-between, with some parts committed and some not, is NOT tolerable. So in your transaction, the change to the audit log is NOT committed if any part of the transaction fails. Everything from the beginning of a transaction up to a commit or rollback command is part of the transaction. All DDL commands are transactions unto themselves, so they end the prior transaction (which is committed, if you have autocommit turned on, or rolled back otherwise) and the command following a DDL command starts a new transaction. Triggers execute within the same transaction as the command that triggered them, and may not include a commit or rollback. So any DML in a trigger is only committed if the entire transaction is committed. There is only one exception to this behavior. You can declare a stored procedure as an Autonomous Transaction, which means that you are starting a new transaction that is independant of the current transaction. This means that the new transaction can commit or rollback without affecting or being affected by the current transaction, and can fail without causing the current transaction to fail or succeed, even if the current transaction fails. This is very useful and powerful, but use it with caution, because you are no longer protected by the normal transaction safeguards. -Original Message- Sent: Friday, January 23, 2004 9:15 AM To: Multiple recipients of list ORACLE-L Hi All, I have a before update trigger for a local table. I know Oracle does not commit the inserting audit entry into the audit log table until the user commits the changes on the audited table. Can I assume Oracle issues one commit for both changes? When commit fails, both changes will be rolled back. However, Oracle uses two-phase commit if a trigger updates remote tables in a distributed database. What happens if Oracle commits the change in audit log table and my change subsequently fails? _ Learn how to choose, serve, and enjoy wine at Wine @ MSN. http://wine.msn.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Flack 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bobak, Mark INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California
Re: commit for triggers
Perhaps I got it wrong, but, John - are you saying that the entries are part of the rollback, i.e. if the transaction that caused the audit trail entries to be created is rolled back, the audit trail enries are rolled back as well? The auditing entry is NOT part of the transaction, it's created via an autonomous one and it stays in the audit trail table, regardless of what happens to the transaction. It will take a very simple test to prove this. Make sure that audit_trail is set to DB. create table atest1 (col1 number, col2 number, col3 number, col4 number); insert into atest1 values (1,1,1,1); audit update on atest1 by access; update atest1 set col1 = 2; Do NOT commit. From another session as user SYS, select action_name, obj_name, ses_actions, returncode from dba_audit_trail; ACTION_NAME OBJ_NAME SES_ACTIONS RETURNCODE --- --- --- --- UPDATE ATEST1 0 The entry is there even if the transaction is not committed. Now rollback the update and check the audit trail; it will be there. If the auditing option were BY SESSION, instead of action, the ACTION_NAME would have been SESSION REC and the column SES_ACTIONS would've been --S-. Hope this helps. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, January 23, 2004 3:24 PM John, I agree w/ everything you said, except for the autocommit functionality. Autocommit setting has no impact on whether DDL will commit or rollback any in progress transaction. DDL always commits an in-progress transaction. The short example below speaks for itself. (8.1.7.4 on Solaris 2.8) SQL show autocommit autocommit OFF SQL desc a Name Null?Type - COL1 NUMBER COL2 NUMBER SQL select * from a where col1=-12345; no rows selected SQL insert into a values(-12345,-12345); 1 row created. SQL create table xxx(a number); Table created. SQL select * from a where col1=-12345; COL1 COL2 -- -- -12345 -12345 1 row selected. Mark J. Bobak Oracle DBA ProQuest Company Ann Arbor, MI Imagination was given to man to compensate him for what he is not, and a sense of humor was provided to console him for what he is. --Unknown -Original Message- Sent: Friday, January 23, 2004 12:10 PM To: Multiple recipients of list ORACLE-L A two-phase commit is simply a way to make sure that commits happen in a distributed transaction the same way that they do in a local transaction. The absolute rule is: Everything commits or Nothing does. In-between, with some parts committed and some not, is NOT tolerable. So in your transaction, the change to the audit log is NOT committed if any part of the transaction fails. Everything from the beginning of a transaction up to a commit or rollback command is part of the transaction. All DDL commands are transactions unto themselves, so they end the prior transaction (which is committed, if you have autocommit turned on, or rolled back otherwise) and the command following a DDL command starts a new transaction. Triggers execute within the same transaction as the command that triggered them, and may not include a commit or rollback. So any DML in a trigger is only committed if the entire transaction is committed. There is only one exception to this behavior. You can declare a stored procedure as an Autonomous Transaction, which means that you are starting a new transaction that is independant of the current transaction. This means that the new transaction can commit or rollback without affecting or being affected by the current transaction, and can fail without causing the current transaction to fail or succeed, even if the current transaction fails. This is very useful and powerful, but use it with caution, because you are no longer protected by the normal transaction safeguards. -Original Message- Sent: Friday, January 23, 2004 9:15 AM To: Multiple recipients of list ORACLE-L Hi All, I have a before update trigger for a local table. I know Oracle does not commit the inserting audit entry into the audit log table until the user commits the changes on the audited table. Can I assume Oracle issues one commit for both changes? When commit fails, both changes will be rolled back. However, Oracle uses two-phase commit if a trigger updates remote tables in a distributed database. What happens if Oracle commits the change in audit log table and my change subsequently fails? _ Learn how to choose, serve, and enjoy wine at Wine @ MSN.
RE: commit for triggers
Mark - Thanks for the correction. When I looked at what I said about the transaction before a DDL command a second time, I myself wondered if I'd gotten it right. If you've tested it, and the transaction is always committed, I'll take your word for it. Arup - I don't normally use Oracle's built-in auditing of DML, I write my own audits with triggers, and it works as I said. If you've tested this, I'll take your word for it. That said, if it DOES work the way you say, I personally think it works the wrong way. If I update a table, and then roll back the update, I don't want an audit table record of the update, unless it CLEARLY notes the fact that the update was rolled back. I'm much more interested in the fact that Jack changed the table, than in the fact that Manny started to change it, but then changed his mind. -Original Message- Sent: Friday, January 23, 2004 11:09 PM To: Multiple recipients of list ORACLE-L Perhaps I got it wrong, but, John - are you saying that the entries are part of the rollback, i.e. if the transaction that caused the audit trail entries to be created is rolled back, the audit trail enries are rolled back as well? The auditing entry is NOT part of the transaction, it's created via an autonomous one and it stays in the audit trail table, regardless of what happens to the transaction. It will take a very simple test to prove this. Make sure that audit_trail is set to DB. create table atest1 (col1 number, col2 number, col3 number, col4 number); insert into atest1 values (1,1,1,1); audit update on atest1 by access; update atest1 set col1 = 2; Do NOT commit. From another session as user SYS, select action_name, obj_name, ses_actions, returncode from dba_audit_trail; ACTION_NAME OBJ_NAME SES_ACTIONS RETURNCODE --- --- --- --- UPDATE ATEST1 0 The entry is there even if the transaction is not committed. Now rollback the update and check the audit trail; it will be there. If the auditing option were BY SESSION, instead of action, the ACTION_NAME would have been SESSION REC and the column SES_ACTIONS would've been --S-. Hope this helps. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, January 23, 2004 3:24 PM John, I agree w/ everything you said, except for the autocommit functionality. Autocommit setting has no impact on whether DDL will commit or rollback any in progress transaction. DDL always commits an in-progress transaction. The short example below speaks for itself. (8.1.7.4 on Solaris 2.8) SQL show autocommit autocommit OFF SQL desc a Name Null?Type - COL1 NUMBER COL2 NUMBER SQL select * from a where col1=-12345; no rows selected SQL insert into a values(-12345,-12345); 1 row created. SQL create table xxx(a number); Table created. SQL select * from a where col1=-12345; COL1 COL2 -- -- -12345 -12345 1 row selected. Mark J. Bobak Oracle DBA ProQuest Company Ann Arbor, MI Imagination was given to man to compensate him for what he is not, and a sense of humor was provided to console him for what he is. --Unknown -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Flack 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).
