Oracle的数据并发与一致性详解

转自：https://www.cnblogs.com/jpcflyer/p/9164100.html

           https://www.cnblogs.com/jpcflyer/p/9169357.html

1 Oracle数据并发与一致性概念

        在以前单用户的数据库环境中，我们根本就不需要关心数据一致性的问题，因为根本就不会有多个用户在同一时间修改同一数据。但在现在的多用户数据库环境中，必须允许同时发生多个事务，而且这些事务可能会访问同一数据，此外，还要保证这些事务的一致性。因此多用户数据库必须提供以下两个基本功能：

• 数据并发：即允许多用户同时访问同一数据
• 数据一致性：即每个用户看到的数据都是一致的

        为了描述事务并发运行时的一致性行为，研究人员定义了一种事务隔离模型，称之为serializability（序列化）。这种可串行化事务操作使得它看起来似乎没有其它用户在操作数据。虽然这种序列化机制在一般情况下是可用的，但在并发要求高的场景，它会严重影响系统的吞吐能力。一般情况下，需要在事务隔离级别与性能间作一个取舍。

       Oracle通过使用multiversion consistency model(多版本一致性模型)、以及各种锁和事务来维护数据一致性，下面介绍下相关概念。

1.1 Multiversion Read Consistency(多版本读一致性)

       多版本指的是同时存在数据的多个版本，意味着oracle有以下两个特性：

• read-consistent queries(读一致性查询)

       查询返回的数据是已提提交的，在某一时间点是一致的。（注意：oracle中是不会有脏读的，为什么呢？原因是第一条会话插入一条记录不提交，第二个会话再查询时发现这个事务没有commit，从而会找到这个事务的事务槽，事务槽中记录着该行未修改前的值存放在undo的位置，然后把该undo块加载到内存构造出CR块，查询会读取CR块中的值返回给客户。）

• nonblocking queries(非阻塞查询)

       数据的读和写不会相互阻塞。

1.2 Statement-Level Read Consistency(声明级读一致性)

        Oracle总是强制保证声明级的读一致性，确保查询返回的数据在同一时间点是已提交的（原因已在上面提及）。

1.3 Transaction-Leval Read Consistency(事务级读一致性)

        Oracle可以提供事务中所有查询的一致性，即事务中每个声明看到的数据都是某一点的数据，这个点指的是事务开始的点。在序列化事务中的查询只能看到自己本事务发生的修改。事务级读一致性产生了可重复读，且不会产生幻影读。

1.4 Read Consistency and Transaction Table(读一致性和事务表)

       Oracle使用了事务表来确定当数据库开始修改一个块时，是否有未提交的事务，这个事务表也称为interested transaction list(ITL)。事务表中描述了哪个事务有行锁、哪一行包含已提交或未提交的修改。

1.5 Locking Mechanisms(锁机制)

        一般来说，多用户数据库会使用多种数据锁的形式来解决数据的并发与一致性问题，本文后面会有锁的详细介绍。

1.6 ANSI/ISO Transaction Isotation Levals(ANSI/ISO事务隔离级别)

        ANSI和ISO都采纳的SQL标准中，定义了四个级别的事务隔离。这些不同级别对事务吞吐量有不同影响。这些隔离级别定义是为了预防两个并发事务会产生的一些现象，这些现象包括：

• 脏读：一个事务读取了另一个事务没有提交的数据
• 不可重复读：一个事务重复读取刚才已读过的数据，结果两次数据不一致，在此期间，其它事务对此数据已修改并提交
• 幻影读：一个事务重复读取满足查询条件的记录数，结果两次数据不一致，在此期间，其它事务插入了符合此查询条件的数据

        SQL标准中根据隔离级别允许发生的现象，定义了四种隔离级别：

        

        Oracle数据库提供了read committed(默认级别)和serializable两种隔离级别，同时还支持只读模式。

2 事务隔离级别

        上面已经提到ANSI的四种事务隔离级别，下面来详细介绍oracle数据库提供的三种事务隔离级别：read committed, serializable,read-only。

2.1 Read Committed事务隔离级别

        在此级别中，事务中查询到的数据都是在此查询前已经提交的。这种隔离级别避免了读取脏数据。然而数据库并不阻止其它事务修改一个所读取的数据，其它事务可能会在查询执行期间修改。因此 ，一个事务运行同样的查询两次，可能会遇到不可重复读和幻影读。

• read committed隔离级别中的读一致性

       每个查询都会提供一个一致性的结果集，其中不需要用户做什么（这里的查询也包含像update中where这样的隐式查询）。

• read committed隔离级别中的写冲突

       在一个read committed事务中，当事务要更改一行，而这行已经被另外一个未提交事务修改了（有时称之为blocking transaction），这里会发生写冲突。此时这个事务会等待blocking transaction结束，并有以下两个选项：

• 如果blocking transaction回滚，那么waiting transaction会修改之前被locked的行
• 如果blocking transaction提交然后释放锁，那么waiting transaction会在改变后的数据基础上，进行更新

2.2 Serializable事务隔离级别

 在序列化隔离级别中，事务可以看到的是事务开始时已经提交的或事务自己做的修改。此隔离级别适合下面的场景：

• 超大数据库并且事务很小，每个事务只更新几行
• 在两个并发事务修改相同行的几率相对比较低的场景
• 有较长的事务，但主要是只读事务的时候

       在序列化隔离级别中，读一致性从通常的语句级扩展成整个事务级。事务中读取的任何行，再次读时保证是相同的。序列化事务不会遇到脏读、不可重复读、幻影读的问题。

       Oracle允许序列化事务修改数据，不过如果有其它事务修改，那么这个事物必须在序列化事务开始之前就提交。当一个序列化事务企业修改一行，而该行被别的事务修改，且在序列化事务开始之后才提交，这时候会报ORA-08177:Cannot serialize access for this transaction。此时，应用可以采取以下动作：

• 提交事务
• 执行其它不同的语句，也许会回滚到之前的savepoint
• 回滚整个事务

2.3 Read-Only事务隔离级别

        只读隔离级别和序列化隔离级别很像，只是在只读事务中，不允许有修改操作，除非是用sys用户。因此只读事务不会有ORA-08177错误，只读事务在产生一个报告时很有效。

3 锁机制

        事务之间的并发控制实际是通过锁实现的，锁是用来预防事务之间访问相同数据时的破坏性交互（比如错误的更新数据等）的一种机制，在维护数据库并发性与一致性方面扮演了一个重要角色。

3.1 锁的基本概念

        一般来说，数据库有两种类型的锁：共享锁（share locks）和排他锁（exclusive locks）。一个资源（比如一行或一个表）上面只能有一个排他锁，但可以有多个共享锁。具体后文会有详细介绍。锁会影响到查询reader和writer的交互（reader是查询某资源，writer是修改某资源），下面总结了Oracle中reader和writer的锁定行为：

• 只有一个writer 在修改一行时，这一行才会被锁：当一个语句更新了一行，则事务只获取了这一行的锁，以实现数据库的最小争用。在正常情况下，数据库不会将行锁升级为块锁，甚至表锁。
• 某行的writer会阻塞并发的该行的其它writer：如果一个事务在修改某一行，那么行锁会阻止其它事务在这一时间内修改这一行
• reader永远不会堵塞writer：因为该行的reader不会对这行上锁（当然select .. for update除外，这是个特殊的select）
• writer永远不会堵塞reader：当一个writer正在修改一行时，数据库通过使用undo数据来对reader提供读一致性（上篇有过介绍了）

      注意：在一些非常特殊的场景中，reader可能会等待同一个块的写。

3.2 锁的使用

        数据库使用锁完成了下面的重要需求：

• 一致性（Consistency）：正在查看或修改的数据不能被其它session修改，直到用户修改完成
• 完整性（Integrity）：数据或结构必须按正确的顺序来反应对它们所做的所有修改

       锁是根据需要自动执行的，不需要用户做什么操作。看下面的一个简单案例

UPDATE employees
SET    email = ?, phone_number = ?
WHERE  employee_id = ?
AND    email = ?
AND    phone_number = ?

        在此update中，email和phone_number都是原始的值，这样更新，能够避免上篇提到的丢失更新的问题。下表显示了当两个会话在相同时间更新employees表中相同的行时的执行顺序：

时间	会议1	会议2	说明
t0	SELECT employee_id, email, phone_number FROM hr.employees WHERE last_name = 'Himuro'; EMPLOYEE_ID EMAIL PHONE_NUMBER ----------- ------- ------------ 118 GHIMURO 515.127.4565
t1		SELECT employee_id, email, phone_number FROM hr.employees WHERE last_name = 'Himuro'; EMPLOYEE_ID EMAIL PHONE_NUMBER ----------- ------- ------------ 118 GHIMURO 515.127.4565
t2	UPDATE hr.employees SET phone_number='515.555.1234' WHERE employee_id=118 AND email='GHIMURO' AND phone_number='515.127.4565'; 1 row updated.		会话1获得了修改行的行锁
t3		UPDATE hr.employees SET phone_number='515.555.1235' WHERE employee_id=118 AND email='GHIMURO' AND phone_number='515.127.4565'; -- SQL*Plus does not show -- a row updated message or -- return the prompt.	会话2尝试获取相同行的行锁，但发生了阻塞
t4	COMMIT; Commit complete.		会话1提交
t5		0 rows updated.	因为where条件不匹配，所以会话2没有更新任何记录
t6	UPDATE hr.employees SET phone_number='515.555.1235' WHERE employee_id=118 AND email='GHIMURO' AND phone_number='515.555.1234'; 1 row updated.
t7		SELECT employee_id, email, phone_number FROM hr.employees WHERE last_name = 'Himuro'; EMPLOYEE_ID EMAIL PHONE_NUMBER ----------- ------- ------------ 118 GHIMURO 515.555.1234	oracle的读一致性使得会话2看不到未提交的t6的修改
t8		UPDATE hr.employees SET phone_number='515.555.1235' WHERE employee_id=118 AND email='GHIMURO' AND phone_number='515.555.1234'; -- SQL*Plus does not show -- a row updated message or -- return the prompt.
t9	ROLLBACK; Rollback complete.
t10		1 row updated.	会话2中发现一行匹配的记录
t11		COMMIT; Commit complete.

        Oracle数据库在执行sql的时候会自动获取所需要的锁，因此用户在应用设计的时候只需要定义恰当的事务级别，不需要显示锁定任何资源。（即使oracle提供了手动锁定数据的方法，用户不会用到。）

3.3 锁模式

        Oracle自动使用最低的限制级别去提供最高程度的并发。Oracle中有两种类型的锁：

• 排他锁（exclusive lock）：这种类型是阻止资源共享的，一个事务获取了一个排他锁，则这个事务锁定期间只有这个事务可以修改这个资源
• 共享锁（share lock）：这种类型是允许资源共享的，多个事务可以在同一资源上获取共享锁

        假设一个事务使用select ... for update(其它DML操作也一样)来选择表中的一行，则这个事务会获取该行的排他行锁，以及所在表的共享表锁。行锁允许其它事务修改除该行的其它行，而表锁阻止其它事务修改表结构。

3.4 锁转换和锁升级

        Oracle在需要的时候会自动执行锁转换，在转换过程中，会将低限制的锁转换为高限制的锁。举例来说，当一个事务执行select ... for update查出一行，之后更新该行，在这种场景下，Oracle会自动将 共享表 锁转换为 行独占表锁。其它的DML操作也会持有一样的锁。此时，获取的行锁已经在最高限制级别了，所以不会在执行锁转换了。

        锁转换和锁升级不一样，锁升级发生在当某个粒度级别持有很多锁时，数据库会将锁升级到更高的粒度级别。比如一个用户锁了一个表中的很多行，那么很多数据库会自动将锁升级为表锁，这样锁的数据就减少了，但限制程度也增加了。

        Oracle永远不会执行锁升级。锁升级极大的增加了死锁的可能性。想想为什么？因为事务1执行过程中需要升级锁，但因为被事务2锁住了相关资源而不能升级，而事务2此时也在等待事务1已锁住的资源，这样就死锁了。

3.5 锁持续时间

        当事务结束（执行commit或rollback）时，锁会被释放。注意，当执行rollback时，是先回滚到savepoint后，才释放锁。

3.6 死锁

        Oracle会自动检测死锁，并通过回滚其中一个语句来解决死锁。当然，可以在等待一段时间后，重试被回滚的语句。下面描述了一个死锁的场景：

时间	会话1	会话2	说明
t0	SQL> UPDATE employees SET salary = salary*1.1 WHERE employee_id = 100; 1 row updated.	SQL> UPDATE employees SET salary = salary*1.1 WHERE employee_id = 200; 1 row updated.
t1	SQL> UPDATE employees SET salary = salary*1.1 WHERE employee_id = 200; -- prompt does not return	SQL> UPDATE employees salary = salary*1.1 WHERE employee_id = 100; -- prompt does not return	发生了死锁
t2	UPDATE employees * ERROR at line 1: ORA-00060: deadlock detected while waiting for resource SQL>		事务1发生死锁信息并回滚
t3	SQL> COMMIT; Commit complete.
t4		1 row updated. SQL>
t5		SQL> COMMIT; Commit complete.

 

4 自动锁

       Oracle的锁可以分为以下几个类别：

• DML锁：保护数据，比如表锁是锁定整个表，而行锁是锁定指定行
• DDL锁：保护对象的结构，比如表和视图的定义
• 系统锁：保护内部的数据库结构，比如数据文件，latch, mutexes以及内部锁

4.1 DML锁

        DML锁可以防止多个互相冲突的DDL,DML操作对数据的破坏性。DML语句自动获取两种类型的锁：Row locks(TX)和Table locks(TM)。（这个简写是oracle EM的locks monitor中使用的缩略语）。

1. 行锁（Row locks或TX）

      事务通过insert, update, delete, merge或select ... for update修改的任何行都会加上行锁，行锁只有排他模式，行锁会持续到事务结束（commit或rollback）。

      注意：如果一个事务因为数据库异常关闭，会先进行块级恢复以使行可用，然后再进行整个事务的恢复。

      如果一个事务获取了一行的行锁，那么也同样获取了所在表的表锁，表锁的作用是阻止冲突性地DDL操作。下面描述了更新表中的一行数据，即自动锁了行，又自动锁了表：

        

        下表解释了Oracle通过锁实现并发性。三个会话同时查询相同的行，会话1和会话2分别更新了不同行，但没有提交，而会话3没有做任何更新。每个会话都能看到它自己做的未提交更新，但不能看到其它会话的未提交更新。

Time	Session 1	Session 2	Session 3
t0	SELECT employee_id, salary FROM employees WHERE employee_id IN ( 100, 101 ); EMPLOYEE_ID SALARY ----------- ------ 100 512 101 600	SELECT employee_id, salary FROM employees WHERE employee_id IN ( 100, 101 ); EMPLOYEE_ID SALARY ----------- ------ 100 512 101 600	SELECT employee_id, salary FROM employees WHERE employee_id IN ( 100, 101 ); EMPLOYEE_ID SALARY ----------- ------ 100 512 101 600
t1	UPDATE hr.employees SET salary=salary+100 WHERE employee_id=100;
t2	SELECT employee_id, salary FROM employees WHERE employee_id IN ( 100, 101 ); EMPLOYEE_ID SALARY ----------- ------ 100 612 101 600	SELECT employee_id, salary FROM employees WHERE employee_id IN ( 100, 101 ); EMPLOYEE_ID SALARY ----------- ------ 100 512 101 600	SELECT employee_id, salary FROM employees WHERE employee_id IN ( 100, 101 ); EMPLOYEE_ID SALARY ----------- ------ 100 512 101 600
t3		UPDATE hr.employees SET salary=salary+100 WHERE employee_id=101;
t4	SELECT employee_id, salary FROM employees WHERE employee_id IN ( 100, 101 ); EMPLOYEE_ID SALARY ----------- ------ 100 612 101 600	SELECT employee_id, salaryFROM employees WHERE employee_id IN ( 100, 101 ); EMPLOYEE_ID SALARY ----------- ------ 100 512 101 700	SELECT employee_id, salary FROM employees WHERE employee_id IN ( 100, 101 ); EMPLOYEE_ID SALARY ----------- ------ 100 512 101 600

 

2. 表锁（Table locks或TM）

      当事务通过insert, update, delete, merge, select ... for update修改数据时，或者直接用Lock table语句时，会对表加上TM锁。表锁有以下几种模式：

• Row share lock(RS, 行共享表锁)

      也叫subshare table lock(SS)，表明事务在这个表上锁住了一行，并且打算更新它们，RS锁是最小约束的锁，当然也是并发最高的锁

• Row Exclusive Table lock(RX，行排他表锁)

      也叫subexclusive table lock(SX)，表明表中有事务更新了行，或者执行select ... for update。一个SX锁允许其它事务同时查询、插入、更新、删除或者锁行。因此多个事务可以同时获取在同一个表上获取SS和SX锁。

• Share Table lock(S，共享表锁)

      一个事务持有了S锁，允许其它事务查询这个表（除了select ... for udpate），但更新操作只允许单个事务持有该锁时才可以。因为多个事务可能会并发持有这个锁，所以持有了这个锁并不代表这个事务就可以修改表。

• Share Row Exclusive Table lock(SRX， 共享行排他表锁)

      也叫share-subexclusive table lock(SSX)，比S锁的限制更多，一次只能有一个事务获取SSX锁，允许其它事务查询（除了select ... for update），不允许其它事务更新。

• Exclusive Table lock(X, 排他表锁)

      是最大限制的锁，禁止其它事务执行任何DML语句或设置任何类型的锁到这个表。

4.2 DDL锁

        当事务执行DDL操作需要DDL锁时，Oracle会自动获取，不需要显示获取。举例来说，如果用户正在创建一个存储过程，那么Oracle会自动获取这个存储过程所有引用到的schema objects上的DDL。这些DDL锁是为了防止在存储过程还没编译完成时，相关的对象就被删除或者修改结构了。

• Exclusive DDL locks(排他DDL锁)

      排他DDL锁，阻止其它会话获取DDL或DML锁。举例来说，DROP TABLE命令在执行是不允许ALTER TABLE的，反之亦然。

• Share DDL locks(共享DDL锁)

        共享DDL锁用于阻止与它冲突的DDL操作，但允许相似的DDL操作并发执行。举例来说，当执行CREATE PROCEDURE时，会获取到所有引用表的的共享DDL锁，其它事务也可以在相应的表上并发的CREATE PROCEDURE获取共享DDL锁，但不允许在被引用的表上获取DDL锁。

4.3 系统锁

        Oracle也提供了多种类型的系统锁来保护数据库和内存的内部结构，由于用户无法控制它们何时出现和持续多久，所以这些机制对用户来说几乎是不可见的。

• Latch(闩锁)

        Latch是低级别的串行化机制，它协调多个用户访问这些共享的数据结构、对象、文件。当多个进程读取一个共享内存资源时使用Latch保护其不受损坏。具体来说，Latch在以下情况保护数据结构：多个会话的并发修改、读取一个正在被别人修改的资源、当访问的时候，内存被释放。V$LATCH事务包含了每个latch的使用状态的详细信息，包括latch被请求和被等待的次数。

• Mutexes(互斥器)

        Mutex是Mutual exclusion object（互相排斥对象）的缩写，是低级别的锁，防止内存中的对象被并行访问时被换出或被损坏。Mutex和Latch很相似，但是一个Latch基本上保护一级object，而mutex保护单个object。

• Internal Locks(内部锁)

        内部锁是高级别的锁，比latch和mutex要复杂的多，用于各种场景。Oracle有以下几种类型的内部锁：字典缓存锁、文件和日志管理锁、表空间和undo段锁。这里就不详细介绍了。

5 手动锁

        Oracle除了保用自动锁外，还可以使用手动锁覆盖默认的锁机制。举例来说，事务中包含如下语句时会覆盖Oracle的默认锁：SET TRANSACTION ISOLATION LEVEL, LOCK TABLE, SELECT ... FOR UPDATE。

6 用户自定义锁

        可以通过DBMS_LOCK包调用锁管理服务，包含功能有请求指定类型的锁、给锁一个唯一名字、修改锁类型、锁释放等等。