Post reply

ALTER Transactions

  • July 24, 2018 at 8:27 am

    #1998789

    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 post 16 (of 17 total)

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