1. 事务的4个特性
· Atomicity(原子性)
All tasks of a transaction are performed ornone of them are. There are no partial transactions. For example, if atransaction 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 oneconsistent state to another consistent state. For example, in a bankingtransaction that debits a savings account and credits a checking account, afailure must not cause the database to credit only one account, which wouldlead to inconsistent data.
· Isolation(隔离性)
The effect of a transaction is not visible toother transactions until the transaction is committed. For example, one userupdating the hr.employees table does not see the uncommitted changes to employeesmade concurrently by another user. Thus, it appears to users as if transactionsare executing serially.
· Durability(持久性)
Changes made by committed transactions arepermanent. After a transaction completes, the database ensures through itsrecovery mechanisms that changes from the transaction are not lost.
2. 语句级原子特性
oracle支持语句级的原子特性:
· A SQL statement that does notsucceed causes the loss only of work it would have performed itself.
· The effect of the rollback is as ifthe statement had never been run.
3. 事务控制
· The COMMIT statement ends thecurrent transaction and makes all changes performed in the transactionpermanent. COMMIT also erases all savepoints in the transaction and releasestransaction locks.
· The ROLLBACK statement reverses thework done in the current transaction; it causes all data changes since the lastCOMMIT or ROLLBACK to be discarded. The ROLLBACK TOSAVEPOINT statement undoes the changes since the last savepoint but does notend the entire transaction.
· The SAVEPOINT statement identifies apoint in a transaction to which you can later roll back.
4. 自治事务
An autonomous transaction is anindependent transaction that can be called from another transaction, called themain transaction. You can suspend the calling transaction,perform SQL operations and commit or undo them in the autonomous transaction,and then resume the calling transaction.
自治事务的特点
- The autonomous transaction does not see uncommitted changes made by the main transaction and does not share locks or resources with the main transaction.
- Changes in an autonomous transaction are visible to other transactions upon commit of the autonomous transactions. Thus, users can access the updated information without having to wait for the main transaction to commit.
- Autonomous transactions can start other autonomous transactions. There are no limits, other than resource limits, on how many levels of autonomous transactions can be called.
5. 分布式事务
A distributeddatabase is a set of databases in a distributedsystem that can appear to applications as a single data source. A distributedtransaction is a transaction that includes one or more statements thatupdate data on two or more distinct nodes of a distributed database, using aschema object called a databaselink. A database link describes how one databaseinstance can log in to another database instance.
6. 设置事务的savepoint
设置一个savepoint,以便将事务回滚到这个点。
SAVEPOINT after_banda_sal;
ROLLBACK TO SAVEPOINT after_banda_sal;
7. oracle数据库事务隔离级别
Read CommittedIsolation Level是oracle默认的事务隔离级别。一个会话只能读取其他事务已提交的更新结果,否则,发生等待,但是其他会话可以修改这个事务中被读取的记录,而不必等待事务结束,显然,在这种隔离级别下,一个事务中的两个相同的读取操作,其结果可能不同。【可能会产生幻读或模糊读】
Session 1 | Session 2 | Explanation |
SQL> SELECT last_name, salary FROM employees WHERE last_name IN ('Banda','Greene','Hintz'); LAST_NAME SALARY ------------- ---------- Banda 6200 Greene 9500 |
Session 1 queries the salaries for Banda, Greene, and Hintz. No employee named Hintz is found. |
SQL> UPDATE employees SET salary = 7000 WHERE last_name = 'Banda'; |
Session 1 begins a transaction by updating the Banda salary. The default isolation level for transaction 1 is |
SQL> SET TRANSACTION ISOLATION LEVEL READ COMMITTED; |
Session 2 begins transaction 2 and sets the isolation level explicitly to |
SQL> SELECT last_name, salary FROM employees WHERE last_name IN ('Banda','Greene','Hintz'); LAST_NAME SALARY ------------- ---------- Banda 6200 Greene 9500 |
Transaction 2 queries the salaries for Banda, Greene, and Hintz. Oracle Database uses read consistency to show the salary for Banda before the uncommitted update made by transaction 1. |
SQL> UPDATE employees SET salary = 9900 WHERE last_name = 'Greene'; |
Transaction 2 updates the salary for Greene successfully because transaction 1 locked only the Banda row (see "Row Locks (TX)"). |
SQL> INSERT INTO employees (employee_id, last_name, email, hire_date, job_id) VALUES (210, 'Hintz', 'JHINTZ', SYSDATE, 'SH_CLERK'); |
Transaction 1 inserts a row for employee Hintz, but does not commit. |
SQL> SELECT last_name, salary FROM employees WHERE last_name IN ('Banda','Greene','Hintz'); LAST_NAME SALARY ------------- ---------- Banda 6200 Greene 9900 |
Transaction 2 queries the salaries for employees Banda, Greene, and Hintz. Transaction 2 sees its own update to the salary for Greene. Transaction 2 does not see the uncommitted update to the salary for Banda or the insertion for Hintz made by transaction 1. |
SQL> UPDATE employees SET salary = 6300 WHERE last_name = 'Banda'; -- prompt does not return |
Transaction 2 attempts to update the row for Banda, which is currently locked by transaction 1, creating a conflicting write. Transaction 2 waits until transaction 1 ends. |
SQL> COMMIT; |
Transaction 1 commits its work, ending the transaction. |
1 row updated. SQL> |
The lock on the Banda row is now released, so transaction 2 proceeds with its update to the salary for Banda. |
SQL> SELECT last_name, salary FROM employees WHERE last_name IN ('Banda','Greene','Hintz'); LAST_NAME SALARY ------------- ---------- Banda 6300 Greene 9900 Hintz |
Transaction 2 queries the salaries for employees Banda, Greene, and Hintz. The Hintz insert committed by transaction 1 is now visible to transaction 2. Transaction 2 sees its own update to the Banda salary. |
COMMIT; |
Transaction 2 commits its work, ending the transaction. |
SQL> SELECT last_name, salary FROM employees WHERE last_name IN ('Banda','Greene','Hintz'); LAST_NAME SALARY ------------- ---------- Banda 6300 Greene 9900 Hintz |
Session 1 queries the rows for Banda, Greene, and Hintz. The salary for Banda is 6300, which is the update made by transaction 2. The update of Banda's salary to 7000 made by transaction 1 is now "lost." |
Serializabletransactions do not experience dirty reads, fuzzy reads, or phantom reads.事务中的读取操作只能读取这个事务开始之前已经提交的数据结果。如果在读取时,其他事务正在对记录执行修改,则Oracle就会在回滚段或撤销段中去寻找对应的原来未经修改的记录(而且是在读取操作所在的事务开始之前存放于回滚段或撤销段的记录),这时读取操作也不会因为相应记录被更新而等待。
Read Consistency and Serialized AccessProblems in Serializable Transactions
Session 1 | Session 2 | Explanation |
SQL> SELECT last_name, salary FROM employees WHERE last_name IN ('Banda','Greene','Hintz'); LAST_NAME SALARY ------------- ---------- Banda 6200 Greene 9500 |
Session 1 queries the salaries for Banda, Greene, and Hintz. No employee named Hintz is found. |
SQL> UPDATE employees SET salary = 7000 WHERE last_name = 'Banda'; |
Session 1 begins transaction 1 by updating the Banda salary. The default isolation level for is |
SQL> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; |
Session 2 begins transaction 2 and sets it to the |
SQL> SELECT last_name, salary FROM employees WHERE last_name IN ('Banda','Greene','Hintz'); LAST_NAME SALARY ------------- ---------- Banda 6200 Greene 9500 |
Transaction 2 queries the salaries for Banda, Greene, and Hintz. Oracle Database uses read consistency to show the salary for Banda before the uncommitted update made by transaction 1. |
SQL> UPDATE employees SET salary = 9900 WHERE last_name = 'Greene'; |
Transaction 2 updates the Greene salary successfully because only the Banda row is locked. |
SQL> INSERT INTO employees (employee_id, last_name, email, hire_date, job_id) VALUES (210, 'Hintz', 'JHINTZ', SYSDATE, 'SH_CLERK'); |
Transaction 1 inserts a row for employee Hintz. |
SQL> COMMIT; |
Transaction 1 commits its work, ending the transaction. |
SQL> SELECT last_name, salary FROM employees WHERE last_name IN ('Banda','Greene','Hintz'); LAST_NAME SALARY ------------- ---------- Banda 7000 Greene 9500 Hintz |
SQL> SELECT last_name, salary FROM employees WHERE last_name IN ('Banda','Greene','Hintz'); LAST_NAME SALARY ------------- ---------- Banda 6200 Greene 9900 |
Session 1 queries the salaries for employees Banda, Greene, and Hintz and sees changes committed by transaction 1. Session 1 does not see the uncommitted Greene update made by transaction 2. Transaction 2 queries the salaries for employees Banda, Greene, and Hintz. Oracle Database read consistency ensures that the Hintz insert and Banda update committed by transaction 1 are not visible to transaction 2. Transaction 2 sees its own update to the Banda salary. |
COMMIT; |
Transaction 2 commits its work, ending the transaction. |
SQL> SELECT last_name, salary FROM employees WHERE last_name IN ('Banda','Greene','Hintz'); LAST_NAME SALARY ------------- ---------- Banda 7000 Greene 9900 Hintz |
SQL> SELECT last_name, salary FROM employees WHERE last_name IN ('Banda','Greene','Hintz'); LAST_NAME SALARY ------------- ---------- Banda 7000 Greene 9900 Hintz |
Both sessions query the salaries for Banda, Greene, and Hintz. Each session sees all committed changes made by transaction 1 and transaction 2. |
SQL> UPDATE employees SET salary = 7100 WHERE last_name = 'Hintz'; |
Session 1 begins transaction 3 by updating the Hintz salary. The default isolation level for transaction 3 is |
SQL> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; |
Session 2 begins transaction 4 and sets it to the |
SQL> UPDATE employees SET salary = 7200 WHERE last_name = 'Hintz'; -- prompt does not return |
Transaction 4 attempts to update the salary for Hintz, but is blocked because transaction 3 locked the Hintz row (see "Row Locks (TX)"). Transaction 4 queues behind transaction 3. |
SQL> COMMIT; |
Transaction 3 commits its update of the Hintz salary, ending the transaction. |
UPDATE employees SET salary = 7200 WHERE last_name = 'Hintz' * ERROR at line 1: ORA-08177: can't serialize access for this transaction |
The commit that ends transaction 3 causes the Hintz update in transaction 4 to fail with the |
SQL> ROLLBACK; |
Session 2 rolls back transaction 4, which ends the transaction. |
SQL> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; |
Session 2 begins transaction 5 and sets it to the |
SQL> SELECT last_name, salary FROM employees WHERE last_name IN ('Banda','Greene','Hintz'); LAST_NAME SALARY ------------- ---------- Banda 7100 Greene 9500 Hintz 7100 |
Transaction 5 queries the salaries for Banda, Greene, and Hintz. The Hintz salary update committed by transaction 3 is visible. |
SQL> UPDATE employees SET salary = 7200 WHERE last_name = 'Hintz'; 1 row updated. |
Transaction 5 updates the Hintz salary to a different value. Because the Hintz update made by transaction 3 committed before the start of transaction 5, the serialized access problem is avoided. Note: If a different transaction updated and committed the Hintz row after transaction transaction 5 began, then the serialized access problem would occur again. |
SQL> COMMIT; |
Session 2 commits the update without any problems, ending the transaction. |
Read-Only IsolationLevel
The read-only isolation level is similar to theserializable isolation level, but read-only transactions do not permit data tobe modified in the transaction unless the user is SYS
. Thus, read-only transactions are not susceptible to the ORA-08177
error. Read-only transactions are useful for generating reports in whichthe contents must be consistent with respect to the time when the transactionbegan.
Preventable Read Phenomena byIsolation Level
Isolation Level | Dirty Read | Nonrepeatable Read | Phantom Read |
Possible | Possible | Possible | |
Read committed | Not possible | Possible | Possible |
Repeatable read | Not possible | Not possible | Possible |
Serializable | Not possible | Not possible | Not possible |