Oracle数据完整性和锁机制

本课内容属于Oracle高级课程范畴，内容略微偏向理论性，但是与数据库程序开发和管理、优化密切相关；另外本课的部分内容在前面章节已经涉及，请注意理论联系实际。

事务 

    事务(Transaction)从 通讯的角度看：是用户定义的数据库操作序列，这些操作要么全做、要么全不做，是不可分割的一个工作单元。事务控制语句称为TCL，一般包括Commit和Rollback。

    事务不是程序，事务和程序分属两个概念。在RDBMS中，一个事务可以有一条SQL语句、一组SQL语句或者整个程序；一个应用程序又通常包含多个事务。

    事务是恢复和并发控制的基本单元。

显式事务和隐式事务

begin

    insert into classes_2(bjbh,bjmc,bjms,bzr,ssxb,bjrs,bz)

    values ('888','测试班级','测试班级','肖丰斌','003','38','');

    commit/rollback;

   end ;

   insert into classes_2(bjbh,bjmc,bjms,bzr,ssxb,bjrs,bz)

    values ('888','测试班级','测试班级','肖丰斌','003','38','');

    commit/rollback;

 

事务的ACID特性和结束方式

事务的ACID特性和结束方式

破坏事务ACID特性的因素包括：

   1.多个事务并行运行时，不同事务的操作交叉执行

   2.事务在运行过程中被强行终止

事务的结束方式包括：

 

 

并行性和一致性

 

   并行性和一致性是针对多用户、多事务，而非单用户、单事务数据库环境的，其含义是在多用户、多事务环境下，针对同一张数据库表的数据存在同时更新（含Update和Insert、Delete）的情况。

  并行性意味着多用户能够同时访问数据；

  一致性意味着每个用户看到的数据是一致的。

   为保证数据的一致性，一般采用了事务隔离机制（事务隔离模型），又称为事务串行化，用来保证事务尽量按照串行的方式执行。

 

执行并行事务要防止三种情况：

1.脏读：事务读取了另外一个没有提交的事务的数据(脏数据)；

2.非重复读：事务重新读取了以前读取的数据，结果发现另外一个已经提交的事务已经修改了那些数据；

3. 幻影读：一个事务重新执行，返回满足条件的行集数据，结果发现另外一个已经提交的事务插入了满足条件的其他行的数据。

 

隔离层	未提交的读模式	提交的读模式	重复读模式	串行化模式
脏读	可能	不可能	不可能	不可能
非重复读	可能	可能	不可能	不可能
幻影读	可能	可能	可能	不可能

 

并行性适用的情况

前提条件是必须是多CPU的服务器上执行，此时并行性的好处才能显示出来，单CPU服务器上实验并行性反而会降低性能。 

 

•处理对大表(至少100万行记录以上)的大数据量查询

•处理连接非常大的表查询

•处理建立大索引、大容量数据装载、汇总计算

•处理Oracle对象间大量数据拷贝等作业

•处理在SMP(对称多处理器)或MPP(大规模并行处理)群和聚合(多机器同时访问同一组磁盘和主数据库)的机器上的查询

•处理存放在分布于不同磁盘的多个数据文件中的数据查询

•处理需要大量辅助内存的查询，如Group by、Order By等

 

语句级读一致性和事务级读一致性

 

 

 

 

 

 

Oracle锁

 

什么是数据库锁

    锁是用于防止在访问相同的资源(包括用户对象、系统对象、内存、Oralce数据字典中的共享数据结构，最常见的是数据库表Table对象)时 ，事务之间的有害性 交互（存、取）的一种机制。

    不同类型的锁，代表了当前用户是允许还是阻止其它用户对相同资源的同时存取，从而确保不破坏系统数据的完整性、一致性和并行性。

    加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。

注意：Oracle的锁机制是一种轻量级的锁定机制，不是通过构建锁列表来进行数据的锁定管理，而是直接将锁作为数据块的物理属性，存储在数据块首部。这个是通过ITL来实现的。在oracle数据库中，不存在真正意义上属于某个对象或数据的锁。

两种锁机制

共享锁（Share Lock）：即S锁，是通过对数据存取的高并行性来实现的。加了共享锁的数据库对象可以被其它事务读取，但是不能被其它事务修改。

独占锁（Exclusive Lock）：即X锁，又称排它锁，是用来防止同时共享相同资源的锁。加了独占锁的数据库对象不能被其它事务读取和修改。

 

•锁在事务保持期间是被保持的，用来防止包括脏读、丢失更新和破坏性DLL等交互行为。对一个事务中SQL语句所做的修改只有在该事务提交或回滚后才能被其它事务所使用。

•Commit或Rollback执行后，事务所使用的锁被释放。

死锁

 

锁的类型

1.数据锁(DML锁)。

用来保证并行访问数据的完整性。能够防止同步冲突的DML和DDL操作的破坏性 交互。是Oracle中主要的锁，又包括表级锁（TM锁）和行级锁（TX锁、也称为事务锁）。

(1).TM锁

  

（2）TM锁的兼容性

 

1.数据锁(DML锁) 。

(1).DML锁工作机制

•当Oracle 执行DML语句时，系统自动在所要操作的表上申请TM类型的锁。当TM锁获得后，系统再自动申请TX类型的锁，并将实际锁定的数据行的锁标志位进行置位。 这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志，而只需检查TM锁模式的相容性即可，大大提高了系统的效率。TM锁包括了SS、SX、S、X 等多种模式，在数据库中用0－6来表示。不同的SQL操作产生不同类型的TM锁。

执行DML语句修改数据：

先申请TM类型的锁，系统再自动申请TX类型的锁。

当一个事务开始时，必须申请一个TX锁，这种锁保护资源是回滚段、回滚段数据块，因此这个这个申请意味着：用户进程必须先申请到回滚段资源后才能开始一个事务，才能
申请到回滚段资源后，用户事务就可以开始修改数据了，事务信息可在v$transaction中查到，在修改数据表的记录时，

需要遵守如下操作顺序：
首先获得这个表的TM锁，这个锁用于保护事务执行过程中其他用户不能修改表结构；
事务修改某个数据块记录时，首先需要在改数据块块头的ITL表中申请一个空闲表项，并在其中记录事务号，实际就是在记录这个事物要使用的回滚段地址；
事务修改该数据块的某条记录时，会设置该记录头部的ITL索引指向上一步申请到的表项，然后再修改记录内容，修改前先在回滚段对记录修改前的状态做一个拷贝，然后才能修改数据记录，这个拷贝用于以后的回滚、恢复和一致性读。当其他用户并发修改这条记录时，会根据记录头的ITL索引读取ITL表项内容，查看这个事务是否已经提交(即是否处于活动状态), 如果没有提交，则这个用户的TX锁会等待前一个用户的TX锁的释放。

如下图所示：

(2).TX锁

  TX锁是Transaction eXclusive Lock行级排它锁，对一条记录加上TX锁后，其他用户不能修改、删除该记录。

•在数据行上只有X锁（排他锁）。在 Oracle数据库中，当一个事务首次发起一个DML语句时就获得一个TX锁，该锁保持到事务被提交或回滚。当两个或多个会话在表的同一条记录上执行DML语句时，第一个会话在该条记录上加锁，其他的会话处于等待状态。当第一个会话提交后，TX锁被释放，其他会话才可以加锁。

•当Oracle数据库发生TX锁等待时，如果不及时处理常常会引起Oracle数据库挂起，或导致死锁的发生，产生ORA-60的错误。这些现象都会对实际应用产生极大的危害，如长时间未响应、大量事务失败等。

(3)关于锁的语句：

可以根据v$lock视图的lmode和request mode判断谁是owner、waiter和converter

select * from v$lock;
owner:lomode>0,request=0
waiter:lmode=0，request>0
converter:lmode>0,request>0 

lomode：表示锁的模式；

request：表示是请求其它模式的锁；

BLOCK：是否阻塞了其它的事务；

解锁语句：

1.找出堵塞了当前会话的会话（sid）select * from  v$lock;

2.再查询堵塞锁的sid和serial#：select * from v$locked_object a,v$session b where a.SESSION_ID=b.SID；

3.kill堵塞会话：alter system kill session '197,9480'; 197是session_id, 9480是serial#

则被堵塞的会话就能顺利进行了。

2.字典锁(DDL锁)

  当 DDL命令发出时，Oracle会自动在被处理的对象上添加DDL锁定，从而防止对象被其他用户所修改。当DDL命令结束以后，则释放DDL锁定。DDL锁定不能显式的被请求，只有当对象结构被修改或者被引用时，才会在对象上添加DDL锁定。比如创建或者编译 存储过程时会对引用的对象添加DDL锁定。在创建视图时，也会对引用的表添加DDL锁定等。

在执行DDL命令之前，Oracle会自动添加一个隐式提交命令，然后执行具体的DDL命令，在DDL命令执行结束之后，还会自动添加一个隐式提交命令。实际上，Oracle在执行DDL命令时，都会将其转换为对数据字典表的DML操作。比如我们发出创建表的DDL命令时，Oracle会将表的名称插入数据字典表tab$里，同 时将表里的列名以及列的类型插入col$表里等。因此，在DDL命令中需要添加隐式的提交命令，从而提交那些对数据字典表的DML操作。即使DDL命令失 败，它也会发出提交命令。DDL锁包括三种类型：

 

•排他的DDL锁定(Exclusive DDL Lock)  
大部分的DDL操作都会在被操作的对象上添加排他的DDL锁定，从而防止在DDL命令执行期间，对象被其他用户所修改。当对象上添加了排他的DDL锁定以后，该对象上不能再添加任何其他的DDL锁定。如果是对表进行DDL命令，则其他进程也不能修改表里的数据。

2.字典锁(DDL锁)

•共享的DDL锁定(Shared DDL Lock )  
用来保护被DDL的对象不被其他用户进程所更新，但是允许其他进程在对象上添加共享的DDL锁定。如果是对表进行DDL命令，则其他进程可以同时修改表里 的数据。比如我们发出create view命令创建视图时，在视图的所引用的表(这种表也叫基表)上添加的就是共享的DDL命令。也就是说，在创建视图时，其他用户不能修改 基表的结构，但 是可以更新基表里的数据。

3.内部锁

  内部锁保护内部数据库结构，如数据文件，对用户是不可见的。

 

2.字典锁(DDL锁)

 

•可打破的解析锁定(Breakable Parsed Lock)

   在shared pool里缓存的SQL游标或者PL/SQL程序代码都会获得引用对象上的解析锁定。如果我们发出DDL命令修改了某个对象的结构时，该对象相关的、位于 shared pool里的解析锁定就被打破，从而导致引用了该对象的SQL游标或者PL/SQL程序代码全都失效。下次再次执行相同的SQL语句时，需要重新解析，这 也就是所谓的SQL语句的reload了。可打破的解析锁定不会阻止其他的DDL锁定，如果发生与解析锁定相冲突的DDL锁定，则解析锁定也会被打破

死锁的解决

1.查找锁

 

 

 

 另：

四、死锁的解决方法
     一般情况下，只要将产生死锁的语句提交就可以了，但是在实际的执行过程中。用户可
能不知道产生死锁的语句是哪一句。可以将程序关闭并重新启动就可以了。
　经常在Oracle的使用过程中碰到这个问题，所以也总结了一点解决方法。

1）查找死锁的进程：

sqlplus "/as sysdba" (sys/change_on_install)
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS 
FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;

2）kill掉这个死锁的进程：

　　alter system kill session ‘sid,serial#’; （其中sid=l.session_id）

3）如果还不能解决：

select pro.spid from v$session ses,v$process pro where ses.sid=XX and ses.paddr=pro.addr;

　　其中sid用死锁的sid替换: exit
ps -ef|grep spid

　　其中spid是这个进程的进程号，kill掉这个Oracle进程

3.Kill 操作系统进程

Orakill 实例名 操作系统进程ID

Orakill oralearn 2444

其中oralearn是数据库sid,244是第二步查出spid

  

 

数据完整性

常用的数据完整性约束规则包括：

1.NOT NULL

2.唯一关键字

3.主关键字

4.外键

5.检查项Check