越来越多的接触并发、吞吐量、锁、一致性等等问题,很多时候也经常听到一些说系统性能太低,加并发提高性能,这往往是泛泛之谈。
把握不清楚问题的症结、问题源,并发很有可能不仅对性能无益,反而适得其反,因为不管是在应用程序级别还是数据库级别,并发和事务控制是一个
复杂的体系。并发也分应用程序级别和数据库级别,比如Java等编程语言的多线程,这里不详细深究应用程序级别的并发,
所以,为了深刻理解并发和一致性、Oracle的事务控制和锁,我将用一个系列专题来研究Oracle的并发问题。
把握不清楚问题的症结、问题源,并发很有可能不仅对性能无益,反而适得其反,因为不管是在应用程序级别还是数据库级别,并发和事务控制是一个
复杂的体系。并发也分应用程序级别和数据库级别,比如Java等编程语言的多线程,这里不详细深究应用程序级别的并发,
所以,为了深刻理解并发和一致性、Oracle的事务控制和锁,我将用一个系列专题来研究Oracle的并发问题。
首先确认一个概念,在Oracle数据库中,经常听到事务的概念,那么什么是Oracle的Transaction即事务呢?这个问题我在这里不讨论了,但是要研究
oracle的并发,你必须首先明确这个概念。
oracle的并发,你必须首先明确这个概念。
其次是concurrency和consistency的概念,以下解释摘自联机文档:
Data concurrency means that many users can access data at the same time.
Data consistency means that each user sees a consistent view of the data,
including visible changes made by the user’s own transactions and transactions of
other users.
在oracle中控制并发性和连续性的机制(同时考虑性能),也就是事务隔离级别,ANSI/ISO 的SQL92标准
定义了4种事务隔离级别以及三种相关现象,如下(摘自联机文档):
Data concurrency means that many users can access data at the same time.
Data consistency means that each user sees a consistent view of the data,
including visible changes made by the user’s own transactions and transactions of
other users.
在oracle中控制并发性和连续性的机制(同时考虑性能),也就是事务隔离级别,ANSI/ISO 的SQL92标准
定义了4种事务隔离级别以及三种相关现象,如下(摘自联机文档):
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
四种隔离级别:Read uncommitted未提交读、Read committed 提交读、Repeatable read 重复读、Serializable序列化。
三种现象:Dirty Read脏读、Nonrepeatable Read非重复读、Phantom Read幻象。
Dirty reads: A transaction reads data that has been written by another transaction
that has not been committed yet.
Nonrepeatable (fuzzy) reads: A transaction rereads data it has previously read and
finds that another committed transaction has modified or deleted the data.
Phantom reads (or phantoms): A transaction re-runs a query returning a set of
rows that satisfies a search condition and finds that another committed transaction
has inserted additional rows that satisfy the condition.
简单来讲,未提交读也就是一个事务能够读到其他事物未提交的数据,非重复读即一个事务多次进行多次读,能够读到其他
事务中修改和删除的数据,而幻象即一个事务进行多次读,能够读到其他事务insert的数据。
而在Oracle中,提供了read committed、Serializable和Read only三种事务隔离级别。
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
四种隔离级别:Read uncommitted未提交读、Read committed 提交读、Repeatable read 重复读、Serializable序列化。
三种现象:Dirty Read脏读、Nonrepeatable Read非重复读、Phantom Read幻象。
Dirty reads: A transaction reads data that has been written by another transaction
that has not been committed yet.
Nonrepeatable (fuzzy) reads: A transaction rereads data it has previously read and
finds that another committed transaction has modified or deleted the data.
Phantom reads (or phantoms): A transaction re-runs a query returning a set of
rows that satisfies a search condition and finds that another committed transaction
has inserted additional rows that satisfy the condition.
简单来讲,未提交读也就是一个事务能够读到其他事物未提交的数据,非重复读即一个事务多次进行多次读,能够读到其他
事务中修改和删除的数据,而幻象即一个事务进行多次读,能够读到其他事务insert的数据。
而在Oracle中,提供了read committed、Serializable和Read only三种事务隔离级别。
修改数据库隔离级别的实践中可能较少,而Oracle默认的隔离级别是read commited,Oracle不同于很多其他的DBMS,读不阻塞写,
serializable在read only的一致性基础上可以有insert、update和delete,所以,我们主要关注read committed和serializable的区别。
serializable在read only的一致性基础上可以有insert、update和delete,所以,我们主要关注read committed和serializable的区别。
首先,在读一致性上,read commited是Statement-Level Read Consistency,也就是在事务中,任何的select查询的结果集是查询在select命令执行
时的scn的提交的结果集,而在serializable下是Transaction-Level Read Consistency,也就是在一个事务中,所有的查询返回相同的结果集,即使在事务期间
其他事务已经修改并提交了查询的数据。
时的scn的提交的结果集,而在serializable下是Transaction-Level Read Consistency,也就是在一个事务中,所有的查询返回相同的结果集,即使在事务期间
其他事务已经修改并提交了查询的数据。
其次,就是在事务级别,以下摘自联机文档:
Both read committed and serializable transactions use row-level locking, and both will
wait if they try to change a row updated by an uncommitted concurrent transaction.
The second transaction that tries to update a given row waits for the other transaction
to commit or undo and release its lock. If that other transaction rolls back, the waiting
transaction, regardless of its isolation mode, can proceed to change the previously
locked row as if the other transaction had not existed.
However, if the other blocking transaction commits and releases its locks, a read
committed transaction proceeds with its intended update. A serializable transaction,
however, fails with the error Cannot serialize access error, because the other
transaction has committed a change that was made since the serializable transaction
began.
Both read committed and serializable transactions use row-level locking, and both will
wait if they try to change a row updated by an uncommitted concurrent transaction.
The second transaction that tries to update a given row waits for the other transaction
to commit or undo and release its lock. If that other transaction rolls back, the waiting
transaction, regardless of its isolation mode, can proceed to change the previously
locked row as if the other transaction had not existed.
However, if the other blocking transaction commits and releases its locks, a read
committed transaction proceeds with its intended update. A serializable transaction,
however, fails with the error Cannot serialize access error, because the other
transaction has committed a change that was made since the serializable transaction
began.
事务1先于事务2开始,并保持未提交状态。事务2想要修改正被事务1修改的行。事务2等待。如果事务1回滚,则
事务2(不论是read committed还是serializable)进行它想要的修改。如果事务1提交,则当事务2是read committed时,
进行它要的修改;而当事务2是serializable方式时,失败并报错ORA-08177: Cannot serialize access for this transaction
事务2(不论是read committed还是serializable)进行它想要的修改。如果事务1提交,则当事务2是read committed时,
进行它要的修改;而当事务2是serializable方式时,失败并报错ORA-08177: Cannot serialize access for this transaction
另外,在serialiazable方式下,由于一个事务开始前为了防止ORA-08177,必须确定它要修改的数据没有其他事务可能更改,这里有这个判断,所以在oracle的数据块中保留着
这一部分控制信息,表明数据块中的行的提交和未提交的信息,大小是在create table或者alter table的INITRANS参数,在某些情况下,频繁的对块中的行进行事务操作,导致
oracle缺少足够的控制信息来判断该块上的行的最近访问情况,从而出现错误,避免的方法就是增打INITRANS参数。见联机文档:
Serializable isolation permits concurrent transactions to make only those database
changes they could have made if the transactions had been scheduled to run one after
another. Specifically, Oracle permits a serializable transaction to modify a data row
only if it can determine that prior changes to the row were made by transactions that
had committed when the serializable transaction began.
To make this determination efficiently, Oracle uses control information stored in the
data block that indicates which rows in the block contain committed and uncommitted
changes. In a sense, the block contains a recent history of transactions that affected
each row in the block. The amount of history that is retained is controlled by the
INITRANS parameter of CREATE TABLE and ALTER TABLE.
Under some circumstances, Oracle can have insufficient history information to
determine whether a row has been updated by a too recent transaction. This can occur
when many transactions concurrently modify the same data block, or do so in a very
short period. You can avoid this situation by setting higher values of INITRANS for
tables that will experience many transactions updating the same blocks. Doing so
enables Oracle to allocate sufficient storage in each block to record the history of recent
transactions that accessed the block.
这一部分控制信息,表明数据块中的行的提交和未提交的信息,大小是在create table或者alter table的INITRANS参数,在某些情况下,频繁的对块中的行进行事务操作,导致
oracle缺少足够的控制信息来判断该块上的行的最近访问情况,从而出现错误,避免的方法就是增打INITRANS参数。见联机文档:
Serializable isolation permits concurrent transactions to make only those database
changes they could have made if the transactions had been scheduled to run one after
another. Specifically, Oracle permits a serializable transaction to modify a data row
only if it can determine that prior changes to the row were made by transactions that
had committed when the serializable transaction began.
To make this determination efficiently, Oracle uses control information stored in the
data block that indicates which rows in the block contain committed and uncommitted
changes. In a sense, the block contains a recent history of transactions that affected
each row in the block. The amount of history that is retained is controlled by the
INITRANS parameter of CREATE TABLE and ALTER TABLE.
Under some circumstances, Oracle can have insufficient history information to
determine whether a row has been updated by a too recent transaction. This can occur
when many transactions concurrently modify the same data block, or do so in a very
short period. You can avoid this situation by setting higher values of INITRANS for
tables that will experience many transactions updating the same blocks. Doing so
enables Oracle to allocate sufficient storage in each block to record the history of recent
transactions that accessed the block.
所有,很显然,上面的隔离级别的现象现在很容易理解,read committed保证没有未提交读,但会有非重复读和幻象,而serializable没有未提交读、
没有非重复读和幻象。
没有非重复读和幻象。
事务隔离级别的选择:
read committed有非重复读和幻象,但是不会出现ORA-08177,而serializable则相反。
简单来讲read committed有高的并发、吞吐量和低的一致性(非重复读和幻象),而serialiazable则有更好的一致性。在代码级别,read committed不需要额外的
代码在事务中检查ORA-08177,而serializable则需要检查ORA-08177以避免死锁发生。由于serializable没有非重复度和幻象,所以在有DML事务的系统中(如报表)能得到更加精准的查询.
如下联机文档:
A report-writing application that generates
summary data and stores it in the database might use serializable mode because it
provides the consistency that a READ ONLY transaction provides, but also allows
INSERT, UPDATE, and DELETE.
read committed有非重复读和幻象,但是不会出现ORA-08177,而serializable则相反。
简单来讲read committed有高的并发、吞吐量和低的一致性(非重复读和幻象),而serialiazable则有更好的一致性。在代码级别,read committed不需要额外的
代码在事务中检查ORA-08177,而serializable则需要检查ORA-08177以避免死锁发生。由于serializable没有非重复度和幻象,所以在有DML事务的系统中(如报表)能得到更加精准的查询.
如下联机文档:
A report-writing application that generates
summary data and stores it in the database might use serializable mode because it
provides the consistency that a READ ONLY transaction provides, but also allows
INSERT, UPDATE, and DELETE.
查看事务隔离级别的sql语句:
SELECT s.sid, s.serial#,
CASE BITAND(t.flag, POWER(2, 28))
WHEN 0 THEN 'READ COMMITTED'
ELSE 'SERIALIZABLE'
END AS isolation_level
FROM v$transaction t
JOIN v$session s ON t.addr = s.taddr AND s.sid = sys_context('USERENV', 'SID');
修改事务隔离级别的命令:
事务级别:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION READ ONLY;
session级别:
ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE;
ALTER SESSION SET ISOLATION_LEVEL READ COMMITTED;
转载于:https://blog.51cto.com/hunter8888/1332363