Oracle开发与优化 之 数据完整性和锁机制

Oracle数据完整性和锁机制——《12年资深DBA教你Oracle开发与优化——性能优化部分》
目录：
Oracle数据完整性和锁机制
索引及优化之表分析
表分析、约束及表间关系
Oracle体系结构1
Oracle体系结构2
海量数据库及分区1
海量数据库及分区2
海量数据库及分区3
海量数据库及分区4
高级SQL优化(一) 
高级SQL优化(二) 
高级SQL优化(三) 常用优化工具
PPT和源码下载：   http://sishuok.com/forum/posts/list/6365.html
配套视频课程
   Oracle性能优化 http://sishuok.com/product/601
   海量数据库和高级SQL优化 http://sishuok.com/product/602


本课内容属于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对象)时 ，事务之间的有害性 交互（存、取）的一种机制。
    不同类型的锁，代表了当前用户是允许还是阻止其它用户对相同资源的同时存取，从而确保不破坏系统数据的完整性、一致性和并行性。
    加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。
两种锁机制
共享锁（Share Lock）：即S锁，是通过对数据存取的高并行性来实现的。加了共享锁的数据库对象可以被其它事务读取，但是不能被其它事务修改。
独占锁（Exclusive Lock）：即X锁，又称排它锁，是用来防止同时共享相同资源的锁。加了独占锁的数据库对象不能被其它事务读取和修改。

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


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

1.数据锁(DML锁) 。
(2).TX锁及DML锁工作机制
  TX锁是Transaction eXclusive Lock行级排它锁，对一条记录加上TX锁后，其他用户不能修改、删除该记录。
•当Oracle 执行DML语句时，系统自动在所要操作的表上申请TM类型的锁。当TM锁获得后，系统再自动申请TX类型的锁，并将实际锁定的数据行的锁标志位进行置位。 这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志，而只需检查TM锁模式的相容性即可，大大提高了系统的效率。TM锁包括了SS、SX、S、X 等多种模式，在数据库中用0－6来表示。不同的SQL操作产生不同类型的TM锁。
1.数据锁(DML锁)
(2).TX锁及DML锁工作机制
•在数据行上只有X锁（排他锁）。在 Oracle数据库中，当一个事务首次发起一个DML语句时就获得一个TX锁，该锁保持到事务被提交或回滚。当两个或多个会话在表的同一条记录上执行 DML语句时，第一个会话在该条记录上加锁，其他的会话处于等待状态。当第一个会话提交后，TX锁被释放，其他会话才可以加锁。
•当Oracle数据库发生TX锁等待时，如果不及时处理常常会引起Oracle数据库挂起，或导致死锁的发生，产生ORA-60的错误。这些现象都会对实际应用产生极大的危害，如长时间未响应、大量事务失败等。

2.字典锁(DDL锁)
  当 DDL命令发出时，Oracle会自动在被处理的对象上添加DDL锁定，从而防止对象被其他用户所修改。当DDL命令结束以后，则释放DDL锁定。DDL锁定不能显式的被请求，只有当对象结构被修改或者被引用时，才会在对象上添加DDL锁定。比如创建或者编译 存储过程时会对引用的对象添加DDL锁定。在创建视图时，也会对引用的表添加DDL锁定等。
在执行DDL命令之前，Oracle会自动添加一个隐式提交命令，然后执行具体的DDL命令，在DDL命令执行结束之后，还会自动添加一个隐式提交命令。实际上，Oracle在执行DDL命令时，都会将其转换为对数据字典表的DML操作。比如我们发出创建表的DDL命令时，Oracle会
2.字典锁(DDL锁)
将表的名称插入数据字典表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.查找锁




3.Kill 操作系统进程
Orakill 实例名 操作系统进程ID
Orakill oralearn 2444
其中oralearn是数据库sid,244是第二步查出spid
  

数据完整性
常用的数据完整性约束规则包括：
1.NOT NULL
2.唯一关键字
3.主关键字
4.外键
5.检查项Check

由于本部分内容再前面的章节中已经穿插讲解，本处不再赘述


要点及习题
习题
1.什么是事务，请解释什么是显式事务和隐式事务。
2.事务具有哪四个特性？并行性 事务主要使用的情况是什么，请举出四种情况。
3.事务级读一致性包括那三种类型，并列表说明其相同点和不同点。
4.什么是数据库锁，包括那两种大的类型？TM锁又包括那些类型？
5.将表级锁和行级锁结合起来，举例解释数据锁的工作机制。
6.什么是死锁？死锁解决的步骤是什么？
7.为什么数据库设计不推荐大量使用外键来确保数据完整性？