ALTER Transactions

  • paul s-306273 - Tuesday, July 24, 2018 1:36 AM

    Lynn Pettis - Monday, July 23, 2018 10:55 AM

    Sean Lange - Monday, July 23, 2018 7:42 AM

    paul s-306273 - Monday, July 23, 2018 2:53 AM

    Lynn Pettis - Thursday, July 19, 2018 3:34 PM

    paul s-306273 - Thursday, July 19, 2018 7:24 AM

    Sean Lange - Thursday, July 19, 2018 7:11 AM

    paul s-306273 - Thursday, July 19, 2018 2:18 AM

    Stewart "Arturius" Campbell - Wednesday, July 18, 2018 11:40 PM

    Nice, easy one today, thanks Steve

    Not easy for those with an Oracle background who may well get this wrong.

    I can barely spell Oracle. I have worked with it but never for more than a few weeks at a time. What is different about Oracle in this situation?

    Hi - there is an autocommit on DDL in Oracle.

    Does that override an explicit transaction?  That is what we have here.

    Hi Lynn, yes that will override the explicit transaction:

    A transaction ends when it is committed or rolled back, either explicitly with a COMMIT or ROLLBACK statement or implicitly when a DDL statement is issued.

    Well that seems really silly. The auto commit is only for ddl? Meaning that ddl ignores explicit transactions in oracle? Seems like that is an interesting choice from the designers.

    Use Autonomous Transactions to execute the DDL.

    However, as Tom Kyte said when talking about misused features in Oracle:    

  • "The third issue is the autonomous transaction which allows people to do things such as commit in a trigger because they can do that in SQL Server. 
  • SQL Server triggers work differently than Oracle triggers and, committing in a trigger is a horribly bad idea. I believe it’s horribly bad in SQL Server, but it’s doubly horribly bad in Oracle.

    Many people inappropriately use an autonomous transaction all over the place to get around what they think are limitations, but when I look at that code and I explain to them what’s going on they don’t even realise the transactional integrity issues that they’ve introduced into their application.

    You know if you commit in a trigger that means you commit that work. But they don’t realize it only commits the work that was done in the trigger, not the statement that caused the trigger to fire. Think about what happens then when the user rolls back? The stuff you did in your trigger doesn’t roll back with you, it’s non-transactional, you’ve already committed it and now the database is sort of left in this inconsistent state. "

  • Gosh that is bad and breaks ACID. At least in sql server the calling code would get an exception because there is no transaction if it was committed in a trigger. Not saying it handled gracefully but at least it doesn't commit part of the work.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • paul s-306273 - Tuesday, July 24, 2018 1:36 AM

    However, as Tom Kyte said when talking about misused features in Oracle:    

  • "The third issue is the autonomous transaction which allows people to do things such as commit in a trigger because they can do that in SQL Server. 
  • SQL Server triggers work differently than Oracle triggers and, committing in a trigger is a horribly bad idea. I believe it’s horribly bad in SQL Server, but it’s doubly horribly bad in Oracle.

    Many people inappropriately use an autonomous transaction all over the place to get around what they think are limitations, but when I look at that code and I explain to them what’s going on they don’t even realise the transactional integrity issues that they’ve introduced into their application.

    You know if you commit in a trigger that means you commit that work. But they don’t realize it only commits the work that was done in the trigger, not the statement that caused the trigger to fire. Think about what happens then when the user rolls back? The stuff you did in your trigger doesn’t roll back with you, it’s non-transactional, you’ve already committed it and now the database is sort of left in this inconsistent state. "

  • Autonomous transactions are a tool.  If used appropriately they are good, if not they are bad.  I used autonomous transactions to accomplish logging that needed to be retained regardless if the transaction in progress committed or rolled back.  Seems if you need to execute DDL in the middle of a transaction in Oracle that an autonomous transaction would be appropriate as well.  Just need to be sure that the DDL code is written to be re-runnable.

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply