Transactions & Autonomous Transactions

                                                Transactions &  Autonomous Transactions

 参考网站:http://www.java2s.com/Tutorial/Oracle/0680__Transaction/StartingandEndingaTransaction.htm

                http://docs.oracle.com/cd/B10501_01/appdev.920/a96624/06_ora.htm#1990

     Starting and Ending a Transaction
    As mentioned, transactions are logical units of work you use to split up your database activities.
A transaction has both a beginning and an end.
A transaction begins when one of the following events occurs:
    You connect to the database and perform the first DML statement.
    A previous transaction ends and you enter another DML statement.
A transaction ends when one of the following events occurs:
   You perform a COMMIT or a ROLLBACK statement.
   You perform a DDL statement, such as a CREATE TABLE statement, in which case a COMMIT is automatically performed.
   You perform a DCL statement, such as a GRANT statement, in which case a COMMIT is automatically performed.
   You disconnect from the database.
   If you exit SQL*Plus normally by entering the EXIT command, a COMMIT is automatically performed for you.
   If SQL*Plus terminates abnormally, a ROLLBACK is automatically performed.
   You perform a DML statement that fails, in which case a ROLLBACK is automatically performed for that individual DML statement.

ACID Transaction Properties,
Transaction Locking and Transaction Isolation Levels
Database theory has a more rigorous definition of a transaction and states that a transaction has four fundamental properties, known as ACID properties:
    Atomicity Transactions are committed or rolled back as a group, and are atomic, meaning that all SQL statements contained in a transaction are considered to be a single indivisible unit.
   Consistency Transactions ensure that the database state remains consistent, meaning that the database starts at one consistent state and ends in another consistent state when the transaction finishes.
   Isolation Separate transactions should appear to run without interfering with each other.
   Durability Once a transaction has been committed, the database changes are preserved, even if the machine on which the database software runs later crashes.
A transaction cannot get a lock on a row while another transaction already holds the lock on that row.
The easiest way to understand default locking is: readers don't block readers, writers don't block readers, and writers only block writers when they attempt to modify the same row.
The transaction isolation level is the degree to which the changes made by one transaction are separated from other transactions running concurrently.
   Phantom reads(幻读)
T1 reads a set of rows returned by a specified WHERE clause.
T2 then inserts a new row, which also happens to satisfy the WHERE clause of the query previously used by T1.
T1 then reads the rows again using the same query, but now sees the additional row just inserted by T2.
This new row is known as a 'phantom' because to T1 this row seems to have magically appeared.
   Nonrepeatable reads(不可重复读)
T1 reads a row, and T2 updates the same row just read by T1.
T1 then reads the same row again and discovers that the row it read earlier is now different.
This is known as a 'nonrepeatable' read, because the row originally read by T1 has been changed.
    Dirty reads(脏读)
T1 updates a row, but doesn't commit the update.
T2 reads the updated row.
T1 then performs a rollback, undoing the previous update.
Now the row just read by T2 is no longer valid (it's 'dirty') because the update made by T1 wasn't committed when the row was read by T2.
To deal with these potential problems, databases implement various levels of transaction isolation to prevent concurrent transactions from interfering with each other.
The SQL standard defines the following transaction isolation levels, shown in order of increasing isolation:
     READ UNCOMMITTED Phantom reads, nonrepeatable reads, and dirty reads are permitted.
     READ COMMITTED Phantom reads and nonrepeatable reads are permitted, but dirty reads are not.
     REPEATABLE READ Phantom reads are permitted, but nonrepeatable and dirty reads are not.
     SERIALIZABLE Phantom reads, nonrepeatable reads, and dirty reads are not permitted.
The Oracle database supports the READ COMMITTED and SERIALIZABLE transaction isolation levels.
It doesn't support READ UNCOMMITTED or REPEATABLE READ levels.
The default transaction isolation level defined by the SQL standard is SERIALIZABLE,
but the default used by the Oracle database is READ COMMITTED, which is usually acceptable for nearly all applications.
Although you can use SERIALIZABLE with the Oracle database, it may increase the time your SQL statements take to complete, so you should only use SERILIZABLE if you absolutely have to.

                                             Doing Independent Units of Work with Autonomous Transactions

转载至(http://docs.oracle.com/cd/B10501_01/appdev.920/a96624/06_ora.htm#1990)
     自治事务是一个独立的事务并从另一个事务中开始的,这个事务就是主事务,当运行自治事务的时候,允许主事务暂时停滞,当自治事务commit或者roll back,主事务恢复。
    英文:An autonomous transaction is an independent transaction started by another transaction, the main transaction. Autonomous transactions let you suspend the main transaction, do SQL operations, commit or roll back those operations, then resume the main transaction.
自治事务的优点:
Advantages of Autonomous Transactions
     Once started, an autonomous transaction is fully independent. It shares no locks, resources, or commit-dependencies with the main transaction. So, you can log events, increment retry counters, and so on, even if the main transaction rolls back.
More important, autonomous transactions help you build modular, reusable software components. For example, stored procedures can start and finish autonomous transactions on their own. A calling application need not know about a procedure's autonomous operations, and the procedure need not know about the application's transaction context. That makes autonomous transactions less error-prone than regular transactions and easier to use.
Furthermore, autonomous transactions have all the functionality of regular transactions. They allow parallel queries, distributed processing, and all the transaction control statements including SET TRANSACTION.

   自治事务和事务的区别:
  Although an autonomous transaction is started by another transaction, it is not a nested transaction for the following reasons:

  • It does not share transactional resources (such as locks) with the main transaction.
  • It does not depend on the main transaction. For example, if the main transaction rolls back, nested transactions roll back, but autonomous transactions do not.
  • Its committed changes are visible to other transactions immediately. (A nested transaction's committed changes are not visible to other transactions until the main transaction commits.)
  • Exceptions raised in an autonomous transaction cause a transaction-level rollback, not a statement-level rollback.

 (网址:http://docs.oracle.com/cd/B10501_01/appdev.920/a96624/06_ora.htm#1990
    

I believe that we are who we choose to be. Nobody‘s going to come and save you, you‘ve got to save yourself. 我相信我们成为怎样的人是我们自己的选择。没有人会来拯救你,你必须要自己拯救自己。
原文地址:https://www.cnblogs.com/caroline/p/TRANSACTION.html