第13章 事务和锁

1、第13章 事务和锁事务和锁是两个联系非常紧密的概念。事务很重要，可以保证多用户并发系统中的数据的完整性。在数据库中，存在多个用户同时对某一数据进行读写操作的情况，为了确保数据的并行性和一致性，可以使用事务。锁是Oracle数据库引擎用来同步多个用户，同时对同一个数据块访问的一种机制。锁可以消除多用户操作同一个资源产生的隐患。13.1 什么是事务事务（transaction）在数据库中主要用于保证数据的一致性，防止出现错误数据。在数据库中数据的完整性是一个广义概念，它包括数据的并行性和一致性。事务是单个的工作单元。如果某一事务成功，则在该事务中进行的所有数据修改均会提交，成为数据库中的永久组成部

2、分；如果事务遇到错误且必须取消或回滚，则所有的数据均会被清除。事务控制的必要性13.1.1 事务概述事务（transaction）是用户定义的一个数据库操作序列，是一个不可分割的整体。这些操作要么全做，要么全不做。事务是对数据库进行操作的最基本的逻辑单位，它可以是一组SQL语句或整个程序。通常情况下，一个应用程序里包含多个事务。此外，事务还是恢复和并发控制的基本单位。13.1.1 事务概述事务和程序不同，一条语句或者多条语句甚至一段程序都可能在一个事务中，而一段程序又可以包含多个事务。事务可以根据自己的需要把一段程序分成多个组，然后把每个组都当成一个单元，而这个单元就可以理解为一个事务。13.

3、1.1 事务概述事务在没有提交之前可以回滚，而且在提交前，当前用户可以查看已经修改的数据，但其他用户看不到该数据，一旦事务提交就不能再撤销修改了。Oracle数据库的事务基本控制语句有如下几种：set transaction，设置事务的属性。commit，提交事务。savepoint，设置保存点。rollback，回滚事务。rollback to savepoint，回滚至保存点。13.1.2 事务的ACID特性事务有4个特性，分别是原子性（Atomic）、一致性（Consistent）、隔离性（Isolated）、持久性（Durable）。13.1.2 事务的ACID特性1.原子性原子性是指

4、事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。13.1.2 事务的ACID特性2.一致性事务必须使数据库从一个一致性状态变换到另一个一致性状态。因此，当数据库中只包含了成功事务提交的结果时，就说数据库处于一致性状态。13.1.2 事务的ACID特性l3.隔离性l即使每个事务都能确保一致性和原子性，但如果有几个事务并发执行，如果在执行的过程中发生了事务间的交叉，也会导致数据库发生不一致的情况。事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。13.1.2 事务的ACID特性4.持久

5、性持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。13.1.3 事务类型在Oracle数据库中，操作事务可分为两种方式：显示操作方式和隐示管理方式。1.显示操作方式显式操作方式是对事务的提交或回滚，需要用户利用脚本来完成。相关脚本如图所示。13.1.3 事务类型2.隐示管理方式隐示管理方式没有明确的开始和结束标志，它同样由数据库自动开启。当一个程序正常结束或执行DDL语句时会自动提交，例如create table语句。而操作失败时就会自动回退，这些都是被动完成的，不需要开发者的参与。13.1.3 事务类型利用语句可以设置事务是

6、否自动提交，如图所示。 13.1.4 事务的状态对数据库进行操作的各种事务共有5种状态，如图所示。中止状态13.1.4 事务的状态提交状态和中止状态的事务统称为已决事务，处于活动状态、部分提交状态和失败状态的事务称为未决事务。（1）活动状态：事务在执行的状态叫活动状态。（2）部分提交状态：事务中随后一条语句被执行后的状态叫部分提交状态。（3）失败状态：事务不能正常执行的状态叫失败状态。导致失败状态发生的可能原因有硬件原因或逻辑错误，这样事务必须回滚，就进入了中止状态。（4）提交状态：事务在部分提交后，将往硬盘上写入数据，最后一条信息写入后的状态叫提交状态。进入提交状态的事务就成功完成了。（5）

7、中止状态：事务回滚，并且数据库已经回复到事务开始执行前的状态叫中止状态。13.1.5 事务控制语句Oracle中的一个重要概念就是没有“开始事务处理”的语句。用户不能显式地开始一个事务处理。事务处理会隐式地开始与第一条修改数据的语句，或者一些要求事务处理的场合。如果发出COMMIT或ROLLBACK，就会显式地结束一个事务。ROLLBACK TO SAVEPOINT命令不会结束事务！事务控制语句1COMMIT完整形式是COMMIT WORK，二者等价。COMMIT会结束你的事务，并使得已做的所有修改成为永久性的（持久保存）。ROLLBACK完整形式是ROLLBACK WORK，二者等价。ROL

8、LBACK会结束你的事务，并撤销正在进行的所有未提交的修改，把数据库块恢复到事务开始之前的状态。事务控制语句作为开发人员，用户应该使用用户应该使用commit或或rollback显显式终止用户的事务处理，否则用户正在使用的工具或式终止用户的事务处理，否则用户正在使用的工具或环境就将为用户选择其中的一种方式。环境就将为用户选择其中的一种方式。例如，如果用户没有进行提交或回滚就退出了用户的SQL*Plus会话，那么SQL*Plus就会认为用户希望提交用户工作。无论用户是否想要提交工作，系统都会为用户进行提交。无论事务处理的规模如何，提交都是非常快速的操作。 commit命令举例commit命令用于

9、提交事务，并将事务中对数据库的修改进行持久化，即将数据库修改为另外一种状态，而这种状态是可接受的、可靠的状态。while i1000 loop update people set salary = salary + 10*i where id = i; i := i+1;end loop;commit;对于开发者来说，最安全的方式是显式进行数据的提交或者回滚，以结束事务。但很多时候，许多开发者并未注意该问题，而是依靠开发工具来进行提交或回滚。 此时需要注意的是，如果用户未提交对数据库的修改，而关闭了回话，或者数据库连接在提交之前断开，那么针对该数据库的所有操作都将执行回滚操作。commit命令

10、举例另外，需要明确的概念是，在提交之前，数据库已经进行了实际更新，不过，并未得到数据库认可，因此提交动作只是一个获得认可的过程，其花费的数据库资源非常少。而且，提交一条数据与提交1000条数据所花费的数据库资源是相同的。因此，当实现大数据量的数据修改或者插入操作时，应当采取最后一次性提交的策略。while i1000 loop update people set salary = salary + 10*i where id = i; i := i+1; commit;end loop; 在该代码片段中，每次更新数据，都有一次提交动作，将造成数据库资源的浪费。 rollback命令举例roll

11、 back命令用于回滚用户操作。在某些时机，例如程序代码段中出现异常或错误，或者用户直接发出撤销命令，需要回滚操作。回滚操作将终止事务处理，并撤销用户在当前事务中进行的更改。begin while i1000 loop update people set salary = salary + 10*i where id = i; i := i+1; end loop;exception rollback;end;回滚操作，首先要读取回滚段信息，并利用这些信息将数据库中已发生的修改重新恢复。例如，对于使用了update操作的列，则需要将其恢复到原值，而使用了delete操作的行，则需要再次执行插入

12、操作。因此，回滚操作所需要的时间和花费的资源，依赖于在事务中所执行的数据库更改，并与之成正比。通常情况下，回滚操作是非常耗费时间和资源的，因此，回滚往往被用于处理异常，而不用作终端用户的可操作选项。一旦终端用户经常性使用回滚操作，那么将为数据库带来非常大的负担。应当保证终端用户在提交事务之前进行确认，来代替允许用户执行回滚，从而实现提交与回滚操作的平衡。事务控制语句2SAVEPOINTSAVEPOINT允许你在事务中创建一个标记点。一个事务中可以有多个SAVEPOINT。ROLLBACK TO 此语句和SAVEPOINT命令配合使用，可以把事务回滚到标记点，而不回滚在此标记点之前的任何工作。S

13、ET TRANSACTION此语句允许你设置不同的事务属性，比如事务的隔离级别以及事务是只读的还是可读写的。 事务的保存点用户可以使用savepoint语句在事务内设置保存点或标记。保存点可以定义在按条件取消某个事务的一部分后，该事务可以返回的一个位置。 事务的保存点一旦把事务回滚到某个保存点，Oracle会释放保存点之后持有的锁。这时之前等待被锁定资源的事务就可以继续了。有关保存点有如下注意点：当回滚到指定的保存点时，它以后的保存点会被删除，而指定的保存点会被保留。事务回滚时，只回滚保存点之后的操作。保存点之后的锁将被释放，之前的将会被保留。ROLLBACK TO B;（回滚最后一个INSE

14、RT操作）ROLLBACK TO A;（回滚后面的INSERT操作和UPDATE操作）ROLLBACK;（回滚全部操作） savepoint举例这里需要注意的是，回滚操作虽然回滚了数据库状态，但是并不会改变程序的运行轨迹，也就是说，程序继续执行rollback to insert_people的下一条语句。 update people set id = id -1;begin savepoint insert_people; update people set staus = CXL; insert into people (id, name, status) values (2, allen

15、, ACT);exception when others then rollback to insert_people;end; commit;13.1.6事务的隔离级别事务的隔离级别（transaction isolation level）一个事务对数据库的修改与并行的另外一个事务的隔离程度。所谓隔离，是指将事务所能看到的数据库状态与其他事务分隔开来。并发访问的常见问题：丢失更新幻像读取不可重复读取脏读并发访问的常见问题1. 丢失更新当两个或多个事务选择同一行数据，然后基于最初的选定值更新该行时，每个事务都不知道其他事务的存在，最后的更新将覆盖由其他事务所做的更新，这将导致数据丢失。2脏读取

16、脏读取意味着，可以读取来自外界其他动作对数据库的修改，而这种修改尚未提交，未提交的数据有可能回滚。也就是说，读取的数据并非真正有效的数据，这将直接破坏数据一致性。3不可重读不可重读意味着，如果用户在某一时刻读取了一条记录，那么，在下一时刻再次读取时，该记录已发生改变。其状态类似于“人不能两次踏入同一条河流”。不可重读并非不可接受的，相反，不可重读是一种正常的数据库状态。这与脏读取是有本质区别的。4幻像读取影像读取意味着，如果用户在某一时刻执行了一个查询，在下一时刻再次执行相同查询时，可能会有新的数据加入。但是，已经读取的数据是不会改变的，只是查询所获得结果集更大而已。事务的隔离级别SQL标准定

17、义的隔离级别READ UNCOMMITTED允许允许允许READ COMMITTED允许允许REPEATABLE READ允许SERIALIZABLEOracle支持 READ COMMITTED和 SERIALIZABLE两种事务隔离性级别，同时提供了非SQL-99标准的READ-ONLYRead only 属性1read only属性set transaction read only;insert into people(id, name, status) values (13, youyou, ACT);commit;只读事务的这种特性，可以提供一种稳定的状态，从而处理大量的数据查询工作

18、。例如，在生成复杂报表时，需要查询大量数据，而这些数据又是频繁变更的，在处理报表的过程中，可以利用只读事务来提供稳定的环境，以使生成的报表有意义。serializable隔离级别2serializable隔离级别serializable隔离级别是指串行化事务。串行化事务可以实现与只读事务实现相同的功能隔离其他事务对数据库状态的影响。但是串行化事务允许在其中执行任何DML操作，包括删除、修改、插入数据等。在默认情况下，一个事务可以识别其他事务针对数据库的修改，而这种修改仅限于已经提交到数据库的修改。使用了串行化事务，那么，其他事务已经提交的修改也将被隔离。这里所说的隔离，实际是对于查询操作来说，

19、也就是串行化事务的处理过程中，无法查看到其他事务的修改。set transaction isolation level serializable;Read commited隔离级别3read commited隔离级别read commited隔离级别是事务的默认隔离级别，即只能读取其他事务已经提交的修改。对于尚未提交的修改，只有实现该修改的事务本身可以进行读取。事务的隔离级别设定set transaction语句可以用来设置事务处理的各种属性，例如，它的隔离层次，它是只读的还是可以进行读写的；是否需要使用特定的回滚段等。 set transaction isolation level seri

20、alizable set transaction isolation level read committed事务的隔离级别当使用了该命令之后，无论是否出现改变，数据库都会被冻结，可以完全隔离其他事务处理的影响。与此命令相似的命令是：但两个命令存在区别：第一，read only会确保用户无法执行修改数据的DML操作，例如，insert、update或delete。如果执行了这样的操作，则系统就会发出错误消息。第二，用read only可以有效地将其数据库视图冻结到某个时间点。也就是说，无论数据库中的其它会话如何工作，数据库在用户的前面都会是使用set transaction语句时的样子。 se

21、t transaction isolation level serializable set transaction read only事务的隔离级别此模式是设置隔离层次时Oracle的默认操作模式，很少使用。如果用户在会话前面使用alter session命令，将用户会话的事务处理的默认隔离层次从read committed改变为serializable，那么就可能会用到这个命令。与此命令相当的命令是： set transaction isolation level read committed set transaction read write13.2 锁锁是在事务访问相同资源的时候，防

22、止事务之间的有害性交互的机制，这些资源包括用户系统对象、内存和数据字典中的共享数据结构。Oracle通过不同类型的锁，来设置用户允许或阻止其他用户对相同资源的同时访问，从而实现数据的完整性、并行性与一致性。在Oracle数据库中，多个事务并发执行的正确性由数据库管理系统的并发控制机制提供支持。13.2.1 并发访问的常见问题数据库系统的并发控制是以亊务为单位进行的，而亊务中用到的数据或资源，可以使用内部锁定的机制来限制事务对所需共同资源的存取操作从而确保数据的并行性和一致性。1. 丢失信息2. 未确认的相关性3. 不一致的分析4. 幻像读13.2.2 锁概述锁是对数据进行并发控制的机制，是Or

23、acle数据库引擎用来同步多个用户。当对一个数据源加锁后，此数据源就有了一定的访问机制。锁 可防止多个会话同时更改同一数据 自动在指定语句尽可能低的级别获取 不会升级 事务处理事务处理 1 事务处理事务处理 2 SQL UPDATE employees 2 SET salary=salary*1.1 3 WHERE employee_id=100;SQL UPDATE employees 2 SET salary=salary+100 3 WHERE employee_id=100;锁的概念锁是防止访问同一资源的事务之间进行相互破坏的机制。锁用于保护将要被或正在被修改的数据，修改表修改表拒绝访

24、问锁的优点一致性一致性 ： 一次只允许一个用户修改数据完整性完整性 ： 为所有用户提供正确的数据。如果一个用户进行了修改并保存，所做的修改将反映给所有用户q并行性并行性 允许多个用户访问同一数据修改表中的数据查看表中的数据允许访问锁定机制 高级数据并发处理： 执行插入、更新和删除时使用行级锁定 查询不需要任何锁 自动队列管理 在事务处理结束（进行 COMMIT 或 ROLLBACK 操作）之前一直持有锁 事务处理事务处理 1 事务处理事务处理 2 SQL UPDATE employees 2 SET salary=salary*1.1 3 WHERE employee_id=101;SQL U

25、PDATE employees 2 SET salary=salary+100 3 WHERE employee_id=100;数据并发处理 时间时间： 09:00:00 事务处理事务处理 1 UPDATE hr.employees SET salary=salary+100 WHERE employee_id=100; 事务处理事务处理 2 UPDATE hr.employees SET salary=salary+100 WHERE employee_id=101; 事务处理事务处理 3 UPDATE hr.employees SET salary=salary+100 WHERE emp

26、loyee_id=102; . . 事务处理事务处理 x UPDATE hr.employees SET salary=salary+100 WHERE employee_id=xxx; 13.2.3 锁分类事务对数据库的操作可以概括为读和写，当两个事务对同一个数据项进行操作时，可能的情况包括：读-读、写-读、读-写、写-写。除了读-读，其它都可能导致数据的不一致，因此要通过不同模式的锁来避免数据不一致的发生。13.2.3 锁分类锁是实现并发访问的重要手段，数据库将自动启用和取消锁，不需要人的参与。按照锁的权限来分，Oracle数据库中存在两种类型的锁，分别是排他锁（X锁）和共享锁（S锁）。O

27、racle数据库中的锁按照所用对象的不同分为一下几种类型。DML锁：用来保护数据的完整性，称为数据锁。DDL锁：可以保护模式中对象的结构，表、索引的结构定义。内部闩锁：保护数据库的内部结构，完成自动调用。表级锁TM行级锁TXDML锁可以在两个不同级别上获取数据锁：为指定行和为整个表。数据锁行锁锁住选中行表锁锁定整个表DML 锁定 每个 DML 事务处理必须获取两类锁定： 针对正在更新的一行或多行的 EXCLUSIVE 行锁定 针对包含这些行的表的 ROW EXCLUSIVE 表级锁定 事务处理事务处理 2 事务处理事务处理 1 SQL UPDATE employees 2 SET salary

28、=salary*1.1 3 WHERE employee_id= 106;1 row updated.SQL UPDATE employees 2 SET salary=salary*1.1 3 WHERE employee_id= 107;1 row updated.行级锁更新 T002 行更新 T001 行行被锁定允许访问表级锁 1修改表中的行 更新表 拒绝访问限制其他用户对表的访问。表级锁 2使用命令显示地锁定表，应用表级锁的语法是: LOCK TABLE table_name IN mode MODENOWAIT ;表级锁类型行共享行排他共享共享行排他排他表级锁 3行共享 (ROW S

29、HARE) 禁止排他锁定表行排他(ROW EXCLUSIVE) 禁止使用排他锁和共享锁共享锁(SHARE)锁定表，仅允许其他用户查询表中的行禁止其他用户插入、更新和删除行多个用户可以同时在同一个表上应用此锁共享行排他(SHARE ROW EXCLUSIVE) 比共享锁更多的限制，禁止使用共享锁及更高的锁排他(EXCLUSIVE) 限制最强的表锁，仅允许其他用户查询该表的行。禁止修改和锁定表Oracle数据库广州大学华软软件学院 软件工程系 锁模式锁模式说明说明共享锁(Share, S)S锁称为共享锁，某个事务使用S锁锁定了表，则允许其他事务使用S锁锁定该表，不允许对该表进行任何更新。排他锁(E

30、xclusive,X)X锁又称写锁，某个事务对表加了X锁，则不允许其他事务锁定该表。行级共享锁(Row Share, RS)如果事务更新表，使用RS锁定相应的行，则表中的其他行可以让其他事务锁定。行级排他锁(Row Exclusive,RX)如果事务更新表，使用RX锁定相应的行，则不允许其他事务再锁定该表。共享行级排他锁(Share Row Exclusive, SRX)如果某个事务对表加SRX锁，则表示对该表加RS所，而对要进行更新的行加RX锁。当Oracle执行SELECTFOR UPDATE、INSERT、UPDATE、DELETE等DML语句时，系统自动在所要操作的表上申请表级RS锁（

31、SELECTFOR UPDATE）或RX锁（INSERT、UPDATE、DELETE），当表级锁获得后，系统再自动申请TX锁，并将实际锁定的数据行的锁标志位置位（指向该TX锁）；也可以通过LOCK TABLE语句来指定获得某种类型的TM锁。下表总结了Oracle中各SQL语句产生TM锁的情况：Oracle中各SQL语句产生TM锁的情况：13.2.4 锁等待和死锁在某些情况下，由于占用的资源不能及时释放，而造成锁等待。锁等待会严重影响数据库性能和日常工作。例如，当一个会话修改表A的记录时，它会对该记录加锁，而此时如果另一个会话也来修改此记录，那么第二个会话因得不到排他锁而一直等待，此时会出现执行

32、SQL时，数据库长时间没有响应的现象。直到第一个会话把事务提交，释放锁，第二个会话才能对数据进行操作。锁定冲突 UPDATE employees SET salary=salary+100 WHERE employee_id=100; 1 row updated. 9:00:00 UPDATE employees SET salary=salary+100 WHERE employee_id=101; 1 row updated. UPDATE employees SET COMMISION_PCT=2 WHERE employee_id=101; 会话因锁定冲突而等待入队。会话因锁定冲突而等

33、待入队。 9:00:05 SELECT sum(salary) FROM employees; SUM(SALARY) - 692634 会话仍在等待！会话仍在等待！ 16:30:00 在最后在最后 7.5 个小时里进行了许多个小时里进行了许多选择、插入、更新和删除操作，选择、插入、更新和删除操作，但未提交，也未回退！但未提交，也未回退！ 1 row updated. 会话继续进行。会话继续进行。 16:30:01 commit; 事务处理事务处理 1 事务处理事务处理 2 时间时间 锁定冲突的可能原因 未提交更改 长时间运行事务处理 不必要的高锁定级别 检测锁定冲突 在“Performanc

34、e（性能）”页上选择“Blocking Sessions（阻塞会话）”。 单击“Session ID（会话 ID）”链接，查看关于锁定会话的信息，包括实际 SQL 语句。 解决锁定冲突 为了解决锁定冲突，您应该： 提交或回退持有锁的会话 终止持有锁的会话（在紧急情况下） 使用 SQL 解决锁定冲突 可以使用 SQL 语句来确定阻塞会话并终止会话。 结果：结果： 12SQL alter system kill session 144,8982 immediate;SQL select sid, serial#, usernamefrom v$session where sid in(select

35、 blocking_session from v$session)死锁当两个事务相互等待对方释放资源时，就会形成死锁Oracle会自动检测死锁，并通过结束其中的一个事务来解决死锁右边是一个死锁的例子T1T2lock (D1)时间时间lock (D2)lock (D2)lock (D1)等待等待等待等待死锁 事务处理事务处理 2 事务处理事务处理 1 UPDATE employeesSET salary = salary x 1.1WHERE employee_id = 1000; UPDATE employeesSET salary = salary x 1.1WHERE employee_i

36、d = 2000; ORA-00060:Deadlock detected while waiting for resource UPDATE employeesSET manager = 1342WHERE employee_id = 2000; UPDATE employeesSET manager = 1342WHERE employee_id = 1000; 9:009:159:16 13.2.5 查询锁 Oracle将当前锁的信息存储在数据字典的动态性能视图V$LOCK和V$LOCKED_OBJECT中。其中，V$LOCK视图列出当前系统持有的，或者正在申请的所有锁的情况。封锁机制的

37、监控v$lock视图列出当前系统持有的或正在申请的所有锁的情况，其主要字段说明如下：v$locked_object视图列出当前系统中哪些对象正被锁定查看锁等待的进程查看当前的用户会话和对应的锁信息Select s.sid,s.serial#,s.username,s.status,l.id1,l.lmode,l.request from v$session s,v$lock l where s.sid=l.sid and s.username is not null动态性能试图v$session查看造成锁等待的锁信息Select l.id1,l.lmode,l.request from v$s

38、ession s,v$lock l where s.lockwait=l.kaddr删除无效的会话进程Alter system kill session sid,serial#;关于锁冲突的解决方案通过SQL语句检测死锁：Select oracle_username,os_user_name,session_id,locked_mode from v$locked_object;如果有结果显示，表示有数据锁存在。查询到该锁对应的表：Select owner| | object,type from v$access where sid=?是从上面查询得到的的session_id查询该锁的sql语句：Select user_name,sid,sql_text from v$open_cursor where sid=?获得sid,serial#号：select s.sid,s.serial# from v$sess