- 浏览: 1125179 次
文章分类
最新评论
-
lyqf365:
mono死了?mono的开发团队从novel分离出来建立了一个 ...
由mono死了所想到的 -
kittychina:
不错!
2012数据库架构规划 -
诸焕辉:
[color=blue][/color]你很不错的一篇文档
RBAC权限设计实例 -
yangxing1002:
沙舟狼客 写道User_Java 写道票是死的。权当商品了,放 ...
对于12306,我的完整技术方案 -
Mybeautiful:
如果只是分表就能解决的问题,博主又极大的藐视了下 铁路的售票系 ...
铁路的售票系统来说明分库分表对架构的影响
Oracle 数据隔离级别(Transaction Isolation Levels) 说明
From 11gR2:
http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/consist.htm#CNCPT621
一. ANSI/ISO Transaction Isolation Levels(ANSI/ISO标准的隔离级别)
The SQL standard, which has been adoptedby both ANSI and ISO/IEC, defines four levels of transaction isolation. Theselevels have differing degrees of impact on transaction processing throughput.
Theseisolation levels are defined in terms of phenomena that must be preventedbetween concurrently executing transactions. The preventable phenomena are:
--isolation levels 的一些表现
(1)Dirtyreads(脏读)
Atransaction reads data that has been written by another transaction that hasnot been committed yet.
--一个事务读取另一个事务已经修改但为提交的数据
(2)Nonrepeatable (fuzzy) reads(模糊读)
Atransaction rereads data it has previously read and finds that anothercommitted transaction has modified or deleted the data. For example, a userqueries a row and then later queries the same row, onlyto discover that the data has changed.
--当事务重读之前的数据时,发现这个事务被另一个事务修改,如modified 或者deleted,并且已经提交。
(3)Phantomreads
Atransaction reruns aqueryreturning a set of rows that satisfies a search condition and finds thatanother committed transaction has inserted additional rows that satisfy thecondition.
--同一查询在同一事务中多次进行,由于其他提交事务所做的插入操作,每次返回不同的结果集,此时发生幻像读。
Forexample, a transaction queries the number of employees. Five minutes later itperforms the same query, but now the number has increased by one becauseanother user inserted a record for a new hire. More data satisfies the querycriteria than before, but unlike in a fuzzy read thepreviously read data is unchanged.
TheSQL standard defines four levels of isolation in terms of the phenomena that atransaction running at a particular isolation level is permitted to experience.Table9-1shows the levels.
Table 9-1 Preventable ReadPhenomena by Isolation Level
OracleDatabase offers the read committed (default) andserializable isolation levels. Also, the database offers a read-only mode.
隔离级别与并发性是互为矛盾的:隔离程度越高,数据库的并发性越差;隔离程度越低,数据库的并发性越好。
ANSI/ISO标准定义了一些数据库操作的隔离级别:
(1)未提交读(read uncommitted)
(2)提交读(read committed)
(3)重复读(repeatable read)
(4)序列化(Serializable)
二. Oracle Database Transaction Isolation Levels(Oracle 标准的隔离级别)
Table9-1summarizes the ANSI standard for transaction isolation levels. Thestandard is defined in terms of the phenomena that are either permitted orprevented for each isolation level. Oracle Databaseprovides the transaction isolation levels:
(1)ReadCommitted Isolation Level
(2)SerializableIsolation Level
2.1 Read CommittedIsolation Level
In the read committedisolation level, which is the default, everyquery executed by a transaction sees only data committed before the query—notthe transaction—began. This level of isolation is appropriate for databaseenvironments in which few transactions are likely to conflict.
A query in a read committed transaction avoids reading datathat commits while the query is in progress. For example, if a query ishalfway through a scan of a million-row table, and if a different transactioncommits an update to row 950,000, then the query does not see this change whenit reads row 950,000. However, because the database does not prevent othertransactions from modifying data read by a query, other transactions may changedata between query executions. Thus, a transaction that runs the same querytwice may experience fuzzy reads and phantoms.
2.1.1 ReadConsistency in the Read Committed Isolation Level
A consistent result set is provided forevery query, guaranteeing data consistency, with no action by the user. Animplicitquery, such as a query implied by a WHERE clause in an UPDATE statement, isguaranteed a consistent set of results. However, each statement in an implicitquery does not see the changes made by the DML statement itself, but sees thedata as it existed before changes were made.
Ifa SELECT list contains a PL/SQL function, then the database appliesstatement-level read consistency at the statement level for SQL run within thePL/SQL function code, rather than at the parent SQL level. For example, afunction could access a table whose data is changed and committed by anotheruser. For each execution of the SELECT in the function, a new read-consistentsnapshot is established.
See Also:
"Subqueriesand Implicit Queries"
2.1.2 Conflicting Writes in Read CommittedTransactions
Ina read committed transaction, a conflicting write occurs when the transaction attempts tochange a row updated by an uncommitted concurrent transaction, sometimes calleda blocking transaction. The read committed transaction waits for the blockingtransaction to end and release its row lock. The options are as follows:
(1)If the blocking transaction rolls back, then the waiting transactionproceeds to change the previously locked row as if the other transaction neverexisted.
(2)If the blocking transaction commits and releases its locks, then thewaiting transaction proceeds with its intended update to the newly changed row.
Table9-2shows how transaction 1, which can be either serializable or readcommitted, interacts with read committed transaction 2.Table9-2shows a classic situation known as a lost update (see"Useof Locks"). The update made by transaction 1 is not in the table eventhough transaction 1 committed it. Devising a strategy to handle lost updatesis an important part of application development.
eg: Conflicting Writes and Lost Updates in a READCOMMITTED Transaction
session 1:
SQL> SELECT last_name, salary FROMemployees WHERE last_name IN ('Banda','Greene','Hintz');
LAST_NAME SALARY
------------- ----------
Banda 6200
Greene 9500
-- Session 1 queries the salaries forBanda, Greene, and Hintz. No employee named Hintz is found.
SQL> UPDATE employees SET salary = 7000WHERE last_name = 'Banda';
--Session 1 begins a transaction by updatingthe Banda salary. The default isolation level for transaction 1 is READCOMMITTED.
Session 2:
SQL> SET TRANSACTION ISOLATION LEVELREAD COMMITTED;
--Session 2 begins transaction 2 and setsthe isolation level explicitly to READ COMMITTED.
SQL> SELECT last_name, salary FROMemployees WHERE last_name IN ('Banda','Greene','Hintz');
LAST_NAME SALARY
------------- ----------
Banda 6200
Greene 9500
--Transaction 2 queries the salaries forBanda, Greene, and Hintz. Oracle Database uses read consistency to show thesalary for Banda before the uncommitted update made by transaction 1.
SQL> UPDATE employees SET salary =9900WHERE last_name = 'Greene';
--Transaction 2 updates the salary forGreene successfully because transaction 1 locked only the Banda row (see"RowLocks (TX)").
Session 1:
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 employeeHintz, but does not commit.
Session 2:
SQL> SELECT last_name, salary FROMemployees WHERE last_name IN ('Banda','Greene','Hintz');
LAST_NAME SALARY
------------- ----------
Banda6200
Greene 9900
-- Transaction 2 queries the salaries foremployees Banda, Greene, and Hintz.
Transaction 2 sees its own update to thesalary for Greene. Transaction 2 does not see the uncommitted update to thesalary for Banda or the insertion for Hintz made by transaction 1.
SQL> UPDATE employees SET salary =6300WHERE last_name = 'Banda';
-- prompt does not return
-- Transaction 2 attempts to update the rowfor Banda, which is currently locked by transaction 1, creating a conflictingwrite. Transaction 2 waits until transaction 1 ends.
Session 1:
SQL> COMMIT;
--Transaction 1 commits its work, endingthe transaction.
session 2:
1 row updated.
SQL>
--The lock on the Banda row is nowreleased, so transaction 2 proceeds with its update to the salary for Banda.
SQL> SELECT last_name, salary FROMemployees WHERE last_name IN ('Banda','Greene','Hintz');
LAST_NAME SALARY
------------- ----------
Banda 6300
Greene 9900
Hintz
--Transaction 2 queries the salaries foremployees Banda, Greene, and Hintz. The Hintz insert committed by transaction 1is now visible to transaction 2. Transaction 2 sees its own update to the Bandasalary.
SQL>COMMIT;
--Transaction 2 commits its work, endingthe transaction.
session 1:
SQL> SELECT last_name, salary FROMemployees 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 bytransaction 2. The update of Banda's salary to 7000 made by transaction 1 isnow "lost."
2.2 Serializable Isolation Level
In the serialization isolation level, atransaction sees only changes committed at the time the transaction—not thequery—began and changes made by the transaction itself. A serializabletransaction operates in an environment that makes it appear as if no otherusers were modifying data in the database.
Serializable isolation issuitable for environments:
(1)With large databases and short transactions that update only a fewrows
(2)Where the chance that two concurrent transactions will modify thesame rows is relatively low
(3)Where relatively long-running transactions are primarily read only
Inserializable isolation, the read consistency normally obtained at the statementlevel extends to the entire transaction. Any row read by the transaction isassured to be the same when reread. Any query isguaranteed to return the same results for the duration of the transaction,so changes made by other transactions are not visible to the query regardlessof how long it has been running. Serializabletransactions do not experience dirty reads, fuzzy reads, or phantom reads.
Oracle Database permits a serializable transaction to modifya row only if changes to the row made by other transactions were alreadycommitted when the serializable transaction began. The databasegenerates an error when a serializable transaction tries to update or deletedata changed by a different transaction that committed after the serializabletransaction began:
ORA-08177:Cannot serialize access for this transaction
When a serializable transaction fails withtheORA-08177error, an application can take several actions, including the following:
(1)Commit the work executed to that point
(2)Execute additional (but different) statements, perhaps after rollingback to asavepointestablished earlier in the transaction
(3)Roll back the entire transaction
Table9-3shows how a serializable transaction interacts with other transactions.If the serializable transaction does not try to change a row committed byanother transaction after the serializable transaction began, then a serializedaccess problem is avoided.
Eg: Read Consistency andSerialized Access Problems in Serializable Transactions
Session 1:
SQL> SELECT last_name, salary FROMemployees 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 = 7000WHERE last_name = 'Banda';
--Session 1 begins transaction 1 byupdating the Banda salary. The default isolation level for is READ COMMITTED.
Session 2:
SQL> SET TRANSACTION ISOLATION LEVELSERIALIZABLE;
--Session 2 begins transaction 2 and setsit to the SERIALIZABLE isolation level.
SQL> SELECT last_name, salary FROMemployees WHERE last_name IN ('Banda','Greene','Hintz');
LAST_NAME SALARY
------------- ----------
Banda 6200
Greene 9500
--Transaction 2 queries the salaries forBanda, Greene, and Hintz. Oracle Database uses read consistency to show thesalary for Banda before the uncommitted update made by transaction 1.
SQL> UPDATE employees SET salary =9900WHERE last_name = 'Greene';
--Transaction 2 updates the Greene salarysuccessfully because only the Banda row is locked.
Session 1:
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 employeeHintz.
SQL> COMMIT;
-- Transaction 1 commits its work, endingthe transaction.
SQL> SELECT last_name, salary FROMemployees WHERE last_name IN ('Banda','Greene','Hintz');
LAST_NAME SALARY
------------- ----------
Banda 7000
Greene 9500
Hintz
Session 2:
SQL> SELECT last_name, salary FROMemployees WHERE last_name IN ('Banda','Greene','Hintz');
LAST_NAME SALARY
------------- ----------
Banda 6200
Greene 9900
-- Session 1 queries the salaries foremployees Banda, Greene, and Hintz and sees changes committed by transaction 1.Session 1 does not see the uncommitted Greene update made by transaction 2.
Transaction2 queries the salaries for employees Banda, Greene, and Hintz. Oracle Databaseread consistency ensures that the Hintz insert and Banda update committed bytransaction 1 are not visible to transaction 2. Transaction 2 sees its ownupdate to the Banda salary.
SQL> COMMIT;
--Transaction 2 commits its work, endingthe transaction.
SQL> SELECT last_name, salary FROMemployees WHERE last_name IN ('Banda','Greene','Hintz');
LAST_NAME SALARY
------------- ----------
Banda 7000
Greene 9900
Hintz
Session 1:
SQL> SELECT last_name, salary FROMemployees WHERE last_name IN ('Banda','Greene','Hintz');
LAST_NAME SALARY
------------- ----------
Banda 7000
Greene 9900
Hintz
-- Both sessions query the salaries forBanda, Greene, and Hintz. Each session sees all committed changes made bytransaction 1 and transaction 2.
SQL> UPDATE employees SET salary = 7100WHERE last_name = 'Hintz';
--Session 1 begins transaction 3 byupdating the Hintz salary. The default isolation level for transaction 3 isREAD COMMITTED.
Session 2:
SQL> SET TRANSACTION ISOLATION LEVELSERIALIZABLE;
-- Session 2 begins transaction 4 and sets it to the SERIALIZABLEisolation level.
SQL> UPDATE employees SET salary = 7200WHERE last_name = 'Hintz';
-- prompt does not return
-- Transaction 4 attempts to update thesalary for Hintz, but is blocked because transaction 3 locked the Hintz row(see"RowLocks (TX)"). Transaction 4 queues behind transaction 3.
Session 1:
SQL> COMMIT;
--Transaction 3 commits its update of theHintz salary, ending the transaction.
Session 2:
UPDATE employees SET salary = 7200WHERElast_name = 'Hintz'
*
ERROR at line 1:
ORA-08177: can'tserialize access for this transaction
-- The commit that ends transaction 3 causes the Hintz update intransaction 4 to fail with theORA-08177error.The problem error occurs because transaction 3 committed the Hintz update aftertransaction 4 began.
SQL> ROLLBACK;
--Session 2 rolls back transaction 4, whichends the transaction.
SQL> SET TRANSACTION ISOLATION LEVELSERIALIZABLE;
--Session 2 begins transaction 5 and setsit to the SERIALIZABLE isolation level.
SQL> SELECT last_name, salary FROMemployees WHERE last_name IN ('Banda','Greene','Hintz');
LAST_NAME SALARY
------------- ----------
Banda 7100
Greene 9500
Hintz 7100
--Transaction 5 queries the salaries forBanda, Greene, and Hintz. The Hintz salary update committed by transaction 3 isvisible.
SQL> UPDATE employees SET salary =7200WHERE last_name = 'Hintz';
1 row updated.
--Transaction 5 updates the Hintz salary toa 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 updatedand committed the Hintz row after transaction transaction 5 began, then theserialized access problem would occur again.
SQL> COMMIT;
--Session 2 commits the update without anyproblems, ending the transaction.
See Also:
"Overviewof Transaction Control"
2.3 Read-OnlyIsolation Level
The read-only isolation level is similarto the serializable isolation level, but read-onlytransactions do not permit data to be modified in the transaction unless theuser is SYS. Thus, read-only transactions are not susceptible to theORA-08177error. Read-only transactions are useful forgenerating reports in which the contents must be consistent with respect to thetime when the transaction began.
Oracle Database achieves read consistency by reconstructingdata as needed from the undo segments. Because undo segments are used ina circular fashion, the database can overwrite undo data. Long-running reportsrun the risk that undo data required for read consistency may have been reusedby a different transaction, raising a snapshot too old error. Setting an undo retention period, which is the minimumamount of time that the database attempts to retain old undo data beforeoverwriting it, appropriately avoids this problem.
三. Setthe Isolation Level
Applicationdesigners, application developers, and database administrators can chooseappropriate isolation levels for different transactions, depending on theapplication and workload. Youcan set the isolation level of a transaction by using one of these statementsat the beginning of a transaction:
--在事务开始之前设置,否则会报错误:
ORA-01453: SETTRANSACTION must be first statement of transaction
SQL>SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SQL>SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SQL>SET TRANSACTION READ ONLY;
Tosave the networking and processing cost of beginning each transaction with aSET TRANSACTION statement, you can use the ALTER SESSIONstatement to set the transaction isolation level for all subsequent transactions:
SQL>ALTER SESSION SET ISOLATION_LEVELSERIALIZABLE;
SQL>ALTER SESSION SET ISOLATION_LEVELREAD COMMITTED;
-------------------------------------------------------------------------------------------------------
相关推荐
数据库隔离级别有四种,应用《高性能mysql》一书中的说明: 然后说说修改事务隔离级别的方法: 1.全局修改,修改mysql.ini配置文件,在最后加上 代码如下: #可选参数有:READ-UNCOMMITTED, READ-COMMITTED, ...
RANSACTION ISOLATION LEVEL
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED 说明:如果指定GLOBAL,那么定义的隔离级将适用于所有的SQL用户;如果指定SESSION,则...
不再维护-请分叉或寻求其他解决方案transaction_isolation 以与数据库无关的方式在ActiveRecord中设置事务隔离级别。 只要您使用的是新适配器mysql2,pg或sqlite3,就可以与MySQL,PostgreSQL和SQLite一起使用。 ...
Oracle Purchasing 11i XML Transaction Delivery Setup Guide
Oracle Services for Microsoft Transaction Server Developer's Guide
你可以在命令行用–transaction-isolation选项,或在选项文件里,为所有连接设置默认隔离级别。 例如,你可以在my.inf文件的[mysqld]节里类似如下设置该选项: transaction-isolation = {READ-UNCOMMITTED | READ-...
oracle-事务oracle-transaction信息管理与信息系统.doc
oracle-事务oracle-transaction信息管理与信息系统学士学位论文.doc
ORACLE-事务ORACLE-TRANSACTION-信息管理与信息系统本科毕业设计.doc
** (详细)事务特性和隔离级别 ...隔离性(Isolation):并发的数据库事务操作场景下,各事务间相互隔离,互不影响。 持久性(Durability):事务一旦提交后,事务执行结果永久保留在数据库中。 二.事务隔离级别 2.1 RE
Oracle Exchange XML Transaction Delivery Guide for Marketplace Exchange Release 6.2.3
Oracle自治事务的介绍(Autonomous_Transactions)PRAGMA_AUTONOMOUS_TRANSACTION
数据库隔离级别有四种,应用《高性能mysql》一书中的说明:然后说说修改事务隔离级别的方法:1.全局修改,修改mysql.ini配置文件,在最后加上#可选参数有:READ-UNCOMMITTED,READ-COMMITTED,REPEATABLE-READ,...
在原理探讨的基础上,介绍了常用的存储参数分析方法,并对所涉及的存储优化、HWM的优化和Freelist竞争优化作了说明。 缩略语: ASSM:auto segement space management HWM:high water mark DBA:data block...
Oracle 10g 第 2 版 ODAC 和 Oracle Developer Tools for Visual Studio .NET 下载文件 ODTwithODAC1020221.exe 235 MB(247,296,458 字节) ODAC 安装说明 Oracle Developer Tools For Visual ...
oracle数据库事务的概念和相关概念,以及事务级别的划分。分别介绍了jdbc事务和jta事务。
Oracle Healthcare Transaction Base Implementation Guide Release 11i Part No. B13734-01