事务介绍详解程序员

Introduction to Transactions

A transaction is a logical, atomic unit of work that contains one or more SQL statements. A transaction groups SQL statements so that they are either all committed, which means they are applied to the database, or all rolled back, which means they are undone from the database. Oracle Database assigns every transaction a unique identifier called a transaction ID.

All Oracle transactions comply with the basic properties of a database transaction, known as ACID properties. ACID is an acronym for the following:

• Atomicity （原子性）

  All tasks of a transaction are performed or none of them are. There are no partial transactions. For example, if a transaction starts updating 100 rows, but the system fails after 20 updates, then the database rolls back the changes to these 20 rows.

• Consistency （一致性）

  The transaction takes the database from one consistent state to another consistent state. For example, in a banking transaction that debits a savings account and credits a checking account, a failure must not cause the database to credit only one account, which would lead to inconsistent data.

• Isolation （隔离性）

  The effect of a transaction is not visible to other transactions until the transaction is committed. For example, one user updating the hr.employees table does not see the uncommitted changes to employees made concurrently by another user. Thus, it appears to users as if transactions are executing serially.

• Durability （持久性）

  Changes made by committed transactions are permanent. After a transaction completes, the database ensures through its recovery mechanisms that changes from the transaction are not lost.

The use of transactions is one of the most important ways that a database management system differs from a file system.

–事务的使用是数据库管理系统和文件系统区别最大的一个方面

Sample Transaction: Account Debit and Credit （事务实例：转账）

To illustrate the concept of a transaction, consider a banking database. When a customer transfers money from a savings account to a checking account, the transaction must consist of three separate operations:

• Decrement the savings account

• Increment the checking account

• Record the transaction in the transaction journal

Oracle Database must allow for two situations. If all three SQL statements maintain the accounts in proper balance, then the effects of the transaction can be applied to the database. However, if a problem such as insufficient funds, invalid account number, or a hardware failure prevents one or two of the statements in the transaction from completing, then the database must roll back the entire transaction so that the balance of all accounts is correct.

Figure 10-1 illustrates a banking transaction. The first statement subtracts $500 from savings account 3209. The second statement adds $500 to checking account 3208. The third statement inserts a record of the transfer into the journal table. The final statement commits the transaction.

Structure of a Transaction

A database transaction consists of one or more statements. Specifically, a transaction consists of one of the following:

• One or more data manipulation language (DML) statements that together constitute an atomic change to the database

• One data definition language (DDL) statement

A transaction has a beginning and an end.

SQL> UPDATE hr.employees SET salary=salary;  
 
107 rows updated. 
  
SQL> SELECT XID AS "txn id", XIDUSN AS "undo seg", XIDSLOT AS "slot",  
  2  XIDSQN AS "seq", STATUS AS "txn status" 
  3  FROM V$TRANSACTION; 
  
txn id             undo seg       slot        seq txn status 
---------------- ---------- ---------- ---------- ---------------- 
0600060037000000          6          6         55 ACTIVE 

End of a Transaction

A transaction ends when any of the following actions occurs:

• A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause.

  In a commit, a user explicitly or implicitly requested that the changes in the transaction be made permanent. Changes made by the transaction are permanent and visible to other users only after a transaction commits. The transaction shown in Figure 10-1 ends with a commit.

• A user runs a DDL command such as CREATE, DROP, RENAME, or ALTER.

  The database issues an implicit COMMIT statement before and after every DDL statement. If the current transaction contains DML statements, then Oracle Database first commits the transaction and then runs and commits the DDL statement as a new, single-statement transaction.

• A user exits normally from most Oracle Database utilities and tools, causing the current transaction to be implicitly committed. The commit behavior when a user disconnects is application-dependent and configurable.

  Note:

  Applications should always explicitly commit or undo transactions before program termination.

• A client process terminates abnormally, causing the transaction to be implicitly rolled back using metadata stored in the transaction table and the undo segment.

After one transaction ends, the next executable SQL statement automatically starts the following transaction. The following example executes an UPDATE to start a transaction, ends the transaction with a ROLLBACK statement, and then executes an UPDATE to start a new transaction (note that the transaction IDs are different):

SQL> UPDATE hr.employees SET salary=salary;  
107 rows updated. 
  
SQL> SELECT XID, STATUS FROM V$TRANSACTION; 
  
XID              STATUS 
---------------- ---------------- 
0800090033000000 ACTIVE 
  
SQL> ROLLBACK; 
  
Rollback complete. 
  
SQL> SELECT XID FROM V$TRANSACTION; 
  
no rows selected 
  
SQL> UPDATE hr.employees SET last_name=last_name; 
  
107 rows updated. 
  
SQL> SELECT XID, STATUS FROM V$TRANSACTION; 
  
XID              STATUS 
---------------- ---------------- 
0900050033000000 ACTIVE 

Overview of Transaction Control

Transaction control is the management of changes made by DML statements and the grouping of DML statements into transactions. In general, application designers are concerned with transaction control so that work is accomplished in logical units and data is kept consistent.

Transaction control involves using the following statements, as described in “Transaction Control Statements”:

• The COMMIT statement ends the current transaction and makes all changes performed in the transaction permanent. COMMIT also erases all savepoints in the transaction and releases transaction locks.

• The ROLLBACK statement reverses the work done in the current transaction; it causes all data changes since the last COMMIT or ROLLBACK to be discarded. The ROLLBACK TO SAVEPOINT statement undoes the changes since the last savepoint but does not end the entire transaction.

• The SAVEPOINT statement identifies a point in a transaction to which you can later roll back.

The session in Table 10-1 illustrates the basic concepts of transaction control.

Table 10-1 Transaction Control

COMMIT; 

SET TRANSACTION NAME 'sal_update'; 

UPDATE employees 
    SET salary = 7000  
    WHERE last_name = 'Banda'; 

SAVEPOINT after_banda_sal; 

UPDATE employees 
    SET salary = 12000  
    WHERE last_name = 'Greene'; 

SAVEPOINT after_greene_sal; 

ROLLBACK TO SAVEPOINT 
    after_banda_sal; 

UPDATE employees 
    SET salary = 11000  
    WHERE last_name = 'Greene'; 

ROLLBACK; 

SET TRANSACTION NAME 'sal_update2'; 

UPDATE employees 
    SET salary = 7050  
    WHERE last_name = 'Banda'; 

UPDATE employees 
    SET salary = 10950  
    WHERE last_name = 'Greene'; 

COMMIT; 

Rollback to Savepoint

A rollback to a savepoint in an uncommitted transaction means undoing any changes made after the specified savepoint, but it does not mean a rollback of the transaction itself. When a transaction is rolled back to a savepoint, as when the ROLLBACK TO SAVEPOINT after_banda_sal is run in Table 10-1, the following occurs:

1. Oracle Database rolls back only the statements run after the savepoint.

   In Table 10-1, the ROLLBACK TO SAVEPOINT causes the UPDATE for Greene to be rolled back, but not the UPDATE for Banda.

2. Oracle Database preserves the savepoint specified in the ROLLBACK TO SAVEPOINT statement, but all subsequent savepoints are lost.

   In Table 10-1, the ROLLBACK TO SAVEPOINT causes the after_greene_sal savepoint to be lost.

3. Oracle Database releases all table and row locks acquired after the specified savepoint but retains all data locks acquired previous to the savepoint.

n the scenario shown in Table 10-2, session 1 rolls back to a savepoint created before it executed a DML statement. However, session 2 is still blocked because it is waiting for the session 1 transaction to complete.

Table 10-2 Rollback to Savepoint Example

UPDATE employees  
 SET salary = 7000  
 WHERE last_name =  
 'Banda'; 

SAVEPOINT  
 after_banda_sal; 

UPDATE employees  
 SET salary = 12000  
 WHERE last_name =   
 'Greene'; 

UPDATE employees  
 SET salary = 14000  
 WHERE last_name =  
 'Greene'; 

ROLLBACK 
 TO SAVEPOINT  
 after_banda_sal; 

UPDATE employees  
 SET salary = 11000  
 WHERE last_name = 
 'Greene'; 

COMMIT; 

Committing Transactions

A commit ends the current transaction and makes permanent all changes performed in the transaction. In Table 10-1, a second transaction begins with sal_update2 and ends with an explicit COMMIT statement. The changes that resulted from the two UPDATE statements are now made permanent.

When a transaction commits, the following actions occur:

• A system change number (SCN) is generated for the COMMIT.

  The internal transaction table for the associated undo tablespace records that the transaction has committed. The corresponding unique SCN of the transaction is assigned and recorded in the transaction table. See “Serializable Isolation Level”.

• The log writer (LGWR) process writes remaining redo log entries in the redo log buffers to the online redo log and writes the transaction SCN to the online redo log. This atomic event constitutes the commit of the transaction.

• Oracle Database releases locks held on rows and tables.

  Users who were enqueued waiting on locks held by the uncommitted transaction are allowed to proceed with their work.

• Oracle Database deletes savepoints.

  In Table 10-1, no savepoints existed in the sal_update transaction so no savepoints were erased.

• Oracle Database performs a commit cleanout.

  If modified blocks containing data from the committed transaction are still in the SGA, and if no other session is modifying them, then the database removes lock-related transaction information from the blocks. Ideally, the COMMIT cleans out the blocks so that a subsequent SELECT does not have to perform this task.

  Note:

  Because a block cleanout generates redo, a query may generate redo and thus cause blocks to be written during the next
  checkpoint.

• Oracle Database marks the transaction complete.

After a transaction commits, users can view the changes.

Typically, a commit is a fast operation, regardless of the transaction size. The speed of a commit does not increase with the size of the data modified in the transaction. The lengthiest part of the commit is the physical disk I/O performed by LGWR. However, the amount of time spent by LGWR is reduced because it has been incrementally writing the contents of the redo log buffer in the background.

The default behavior is for LGWR to write redo to the online redo log synchronously and for transactions to wait for the buffered redo to be on disk before returning a commit to the user. However, for lower transaction commit latency, application developers can specify that redo be written asynchronously so that transactions need not wait for the redo to be on disk and can return from the COMMIT call immediately.