oracle数据库事务及隔离级别的一些概念

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 thehr.employees table does not see the uncommitted changes toemployeesmade 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.     分布式事务

Adistributeddatabase is a set of databases in a distributedsystem that can appear to applications as a single data source. Adistributedtransaction 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 adatabaselink. 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     Committed Isolation Level（default） 
• Serializable Isolation Level
• Read-Only Isolation Level

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 isREAD COMMITTED.

SQL> SET TRANSACTION ISOLATION

LEVEL READ COMMITTED;

 

Session 2 begins transaction 2 and sets the isolation level  explicitly toREAD COMMITTED.

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."

 

SerializableIsolation Level

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 isREAD COMMITTED.

SQL> SET TRANSACTION ISOLATION

LEVEL SERIALIZABLE;

 

Session  2 begins transaction 2 and sets it to theSERIALIZABLE isolation  level.

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 Bandabefore  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 arenot 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 isREAD COMMITTED.

SQL> SET TRANSACTION ISOLATION

LEVEL SERIALIZABLE;

 

Session  2 begins transaction 4 and sets it to theSERIALIZABLE isolation  level.

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 theORA-08177 error. The  problem error occurs because transaction 3 committed the Hintz update after transaction 4 began.

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 theSERIALIZABLE isolation  level.

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 committedbefore 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

Theread-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 isSYS. Thus, read-only transactions are not susceptible to theORA-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

Read uncommitted

Possible

Possible

Possible

Read committed

Not possible

Possible

Possible

Repeatable read

Not possible

Not possible

Possible

Serializable

Not possible

Not possible

Not possible