`
xiewanzhi
  • 浏览: 160602 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

第三章 锁、表分区

阅读更多

Oracle 第3章 锁、表分区



1、技术目标 

  • 理解锁定的概念
  • 使用表分区


2、锁定的概念 

  • 多个用户可同时访问相同数据,锁是数据库用来控制共享资源并发访问的机制
  • Oracle提供的锁可以确保多用户环境下数据的完整性、一致性
  • 锁能用于保护正在被修改的数据
  • 在提交或回滚事务之前,Oracle会锁定正在被修改的数据,直到提交或回滚了事务之后,锁会自动释放,其他用户才能更新数据 例如:库存中某商品只剩1件,某用户正在在线订购该商品,与该操作相关的数据,也就是这件商品的记录可以锁定,以防止其他用户"同时购买"该商品而修改该记录

大多数情况下,锁不需要开发者干预 ,Oracle会自动完成锁定,比如修改数据时,Oracle提供了锁定操作,以便于需要显示锁定数据时使用。锁定的特点如下:

  • 一致性:一次只允许一个用户修改数据,以保证数据的统一
  • 完整性:提供正确的数据,某用户所修改的数据会反映给所有其他用户
  • 并发性:允许多用户同时访问同一数据,如,某用户正在修改商品库存时,其他用户可以同时查看库存信息,某用户正在更新数据时,其他用户就不能同时删除该数据


3、锁的类型 

锁有两种类型:行级锁 (用于特定行)、表级锁 (用于整个表)

    3.1)行级锁

    对正在被修改的行进行锁定。其他用户可以访问其余的行,如图

    

    行级锁是一种排他锁 ,可防止其他事务修改行,但不会阻止读取,
    在使用insert、update、delete以及select  ... for update等
    语句时,Oracle会自动应用行级锁,select  ... for update 
    语句可每次选择多行记录进行更新,这些记录会被锁定,直到
    回滚或提交该事务后锁才会释放,其他用户才可编辑这些记录
    
    select ... for update语句的完整语法如下:
    select ... for update [of 列名集合] [wait 秒数 | nowait]; 
    of子句指定需要锁定的列
    wait子句指定等待其他用户释放锁的时间(秒),防止无限期等待
    
    使用: 锁定vencode为V002的记录中oDate和delDate两列,然后再修改
    SELECT * FROM orderMaster WHERE vencode=’V002’
            FOR UPDATE OF oDate, delDate;
    UPDATE orderMaster SET delDate=’18-8月-08’ WHERE 
    vencode=’V002’;
    COMMIT;--事务提交后释放锁定 
    
    使用"for update wait"子句的优点有:

  • 防止无期限等待被锁定的行
  • 可在应用程序中对锁定的等待时间进行设置

    
    3.2)表级锁

    表级锁用于保护表数据,使用"lock table "语句显示锁定表。在事务处理中,
    表级锁用来限制对表的添加、更新和删除等操作,具体语法如下:
    lock table 表名 in 锁定模式 mode [nowait];
    nowait关键字可防止无限期等待其他用户释放锁
    锁定模式有如下内容:

  • 行共享(row share, rs):允许其他用户访问和锁定表,禁止排他锁定整个表
  • 行排他(row exclusive, rx):在行共享模式基础上,禁止其他用户在表上使用共享锁
  • 共享(share, s):共享锁将锁定表,只允许其他用户查询表中的行,不允许添加、更新或删除行,多个用户可同时在同一表中设置共享锁(允许资源共享) 例如,每天的结帐操作时需更新日销售额表,可在更新该表示设置共享锁以确保数据一致性
  • 共享行排他(share row exclusive, srx):比共享锁更多的限制,防止其他事务在表上使用共享锁、共享行排他锁以及排他锁
  • 排他(exclusive, x):对表执行的最大限制,其他用户只能查询该表的记录,该锁防止其他事务对表做任何更新或在表上设置任何类型的锁

    使用: 以共享模式锁定orderMaster表
    lock table orderMaster in share mode;
    注意:执行commit或rollback命令可释放锁定 
    
    某用户对表锁定时未使用nowait子句,如该表已被另外的用户
    锁定,那么他将无限期等待,直到锁定该表的用户使用commit
    或rollback语句释放锁
    
4、死锁

当两个事务相互等待对方完成任务时,会出现死锁。比如用户A锁定了对象
X,用户B锁定了对象Y,用户A再锁定Y,用户B再锁定X,两位用户需要等
待对方释放锁,此时两个用户处于僵持状态,无法继续处理业务,这种情况
Oracle会自动检测死锁,通过终止两个事务之一来解决问题,如图




死锁在Oracle中极少出现,一般不用考虑此问题,可以通过人为制造环境来
产生死锁

5、表分区 

Oracle可管理包含海量数据的表,如,公司的订单表可能会增加到百万行,
大小超过2GB,随着表的增大,数据管理随之变得困难,要查找某条记录
需要搜索整个表,会消耗大量的系统资源和时间。Oracle提供的表分区技
术可改善系统性能

表分区允许用户把一个表中的行分为几个部分,不同的部分还可存储在不
同的位置。被分区的表称为分区表,划分出的每一个部分成为一个分区

表分区有许多优势 :

  • 可改善表的查询性能,在对表进行分区后,用户执行SQL查询时可以只访问表中的特定分区
  • 表更容易管理,因为分区表的数据存储在多个部分中,按分区加载和删除数据比在表中加载和删除更容易
  • 便于备份和恢复,可独立备份和恢复每个分区
  • 提高数据安全性,将不同的分区分布在不同的磁盘,可减小所有分区数据同时损坏的可能


应用程序不用知道表已分区,在更新和查询分区表时和普通表的操作一
样,但Oracle优化程序知道表已被分区

注意:要分区的表不能具有LONG和LONG ROW数据类型的列 

Oracle提供4种分区方法:

  • 范围分区
  • 散列分区
  • 复合分区
  • 列表分区


6、范围分区

范围分区根据表的某列或多列的值范围,决定将数据存储在哪个分区上,
比如,可根据序号分区,根据记录的创建日期分区等

创建分区的语法,在create table语句中增加partition子句可创建分区表,
按范围分区的语法为:
create table 表名
(
    ......
)
partition by range (column_name)
(
    partition part1 value less than(range1) [tablespace tbs1],
    partition part2 value less than(range2) [tablespace tbs2],
    ...
    partition partN value less than(rangeN) [tablespace tbsN],
)
语法说明:
column_name 为创建范围分区的列,其列值称为分区键
part1 ... partN 为分区名
range1 ... MAXVALUE 为分区的边界值
tbs1 ... tbsN 为分区所在的表空间,tablespace是可选项

范围分区注意事项:

  • 每个分区的边界值必须小于下一个分区的边界值
  • 每个分区中,只需指定其范围的最大值
  • 所有行的分区键都要小于( < )该分区的边界值
  • 最后一个分区中,MAXVALUE关键字代表边界的最大值,Oracle使用这个分区来存储前面几个分区中不能存储的数据,范围的最小值由Oracle隐含定义


使用1: 购物商场根据销售成本(salesCost)对Sales表中的数据进行分区,
每个分区有一个分区界限用以限制分区范围,按逻辑范围进行分区,
create table Sales
(
    productId varchar2(5),
    salesDate date not null,
    salesCost number(10)
)
partition by range(salesCost)
(
    partition P1 values less than (1000),
    partition P2 values less than (2000),
    partition P3 values less than (3000),
);
说明: 
创建Sales表时创建了3个分区,P1分区包含销售成本低于1000的所有
产品,P2分区包含销售成本低于2000但高于或等于1000的所有产品

使用2: 分区列为date数据类型的情况,必须使用年份为4字符格式掩码
的to_date()函数指定分区边界,
create table sales2
(
    productId varchar2(5),
    salesDate date not null,
    salesCost number(10)
)
partition by range(salesDate)
(
    partition P1 values less than (to_date('2006-01-01', 'YYYY-MM-DD')),
    partition P2 values less than (to_date('2007-01-01', 'YYYY-MM-DD')),
    partition P3 values less than (MAXVALUE),
);
说明: 根据销售日期将表分为3个分区,第一个分区存储2006年以前
的数据,第二个分区存储2006年度的数据,第三个分区存储2007年
以后的数据

7、散列分区

散列分区通过在分区键值上执行一个散列函数 来决定数据的物理位置,
在范围分区中分区键的连续值通常存储在相同的分区中,而散列分区
会把记录平均分布到不同的分区,减少磁盘I/O争用的可能性,

散列分区需要用户指定表所需的分区数目以及存储分区的物理位置,
将散列算法应用于分区键后,散列分区会将数据分布到适当的分区,
语法如下:
partition by hash (column_name)
partitions number_of_partitions [store in (tablespace_list)];
或者
partition by hash (column_name)
(
    partition part1 [tablespace tbs1],
    partition part2 [tablespace tbs2],
    ...
    partition partN [tablespace tbsN],
)
语法说明: 
column_name 为作为基础创建散列分区的列
number_of_partitions 为分区数量,使用这种方式会自动生成分区名
tablespace_list 为分区使用的表空间,如果分区数量超过表空间数量,
        分区会以循环的方式分配到表空间中
part1 ... partN 为分区名

使用1: 创建Employee表,设置4个散列分区,department列为分区键
create table employee
(
    empId number(4),
    empName varchar2(14),
    empAddress varchar2(15),
    department varchar2(10)
)
partition by hash(department) partitions 4;

使用2: 创建表MyEmp并设置2个散列分区
create table MyEmp
(
    eId number(4),
    eName varchar2(20)
)
partition by hash(empId)
(
    partition part1,
    partition part2
);

7、复合分区

复合分区是范围分区和散列分区的结合,创建复合分区时,先按范围对
数据进行分区,然后在这些分区内创建散列分区。复合分区即具有范围
分区便于管理的特定,有具有散列分区在数据放置和并行操作方面的优势

复合分区语法:
partition by range (column_name1)
subpartition by hash(column_name2)
subpartition number_of_partitions [store in (tablespace_list)]
(
    partition part1 value less than(range1),
    partition part2 value less than(range2),
    ...
    partition partN value less than(MAXVALUE),
);
语法说明: 
column_name1
column_name2
number_of_partitions
part1 ... partN 为分区名
range 为范围分区的边界值

使用: 创建表Sales,设置复合分区,先根据salesDate列创建4个范
围分区,再根据productId创建子分区,子分区采用散列分区,共创
建5个子分区,总共创建20个子分区
create table Sales
(
    productId varchar2(5),
    salesDate date not null,
    salesCost number(10)
)
partition by range(salesDate)
subpartition by hash(productId)
subpartitions 5
(
    partition P1 values less than(date '2008-03-01'),
    partition P2 values less than(date '2008-06-01'),
    partition P3 values less than(date '2008-09-01'),
    partition P4 values less than(MAXVALUE),
);

8、列表分区

列表分区允许用户明确地控制行到分区的映射,列表分区允许按自然方式
对无序和不相关的数据集进行分组和组织,语法如下:
partition by list (column_name)
(
    partition part1 values (values_list1),
    partition part2 values (values_list2),
    ...
    partition partN values (DEFAULT)
);
语法说明: 
column_name 为创建列表分区的基础列
part1 ... partN 为分区名
values_list 为对应分区的分区键值列表
DEFAULT 关键字为允许存储前面的分区不能存储的记录

使用: 根据职员住址对Employee表进行分区,north分区只包含地址
为"辽宁"的记录,如果用户输入的值是"广东",则Oracle会拒绝该值,
因为没有创建可以包含该值的分区
create Employee
(
    empId number(4),
    empName varchar2(14),
    empAddress varchar2(15),
    department varchar2(10)
)
partition by list(empAddress)
(
    partition north values('辽宁'),
    partition west values('西藏', '青海'),
    partition south values('福建', '海南'),
    partition east values('江苏', '上海')
);

9、按分区查询、删除记录 
使用1: 查询表分区的记录
select * from 表名 partition (分区名);

使用2: 删除表分区的记录
delete from 表名 partition (分区名);

10、分区维护

分区维护是指修改分区表的分区,比如可以向现有表添加新分区,将
分区移动到其他表空间中等等

某些分区维护操作时计划事件,如在历史数据库中,数据库管理员定
期从数据库中删除最旧的分区并添加一组新的分区,该删除和添加操作
将定期执行

其他分区维护操作时费计划事件,用于解决应用程序或系统问题,例如
意料之外的事务处理活动可能会迫使DBA拆分分区以便重新平衡I/O负载

部分分区维护操作如下:

  • 添加分区
  • 删除分区
  • 截断分区
  • 合并分区
  • 拆分分区

    10.1)添加分区 
    alert table 表名 add partition 新分区名 values less than (边界值);
    该语句用于在最后一个分区后添加新分区,如果要在表的开始或中间
    位置添加分区,或者最高分区的分区边界是MAXVALUE,则应使用
    split partition语句
    
    10.2)删除分区 
    alter table 表名 drop partition 分区名;
    删除分区会连分区中的数据一起删除
    
    10.3)截断分区 
    alter table 表名 truncate partition 分区名;
    截断分区会删除表分区中的所有记录

    10.4)合并分区 
    alter table 表名
    merge partitions 分区名1, 分区名2
    into 新分区名;
    可将范围分区或复合分区表的两相邻分区连接起来,合并后的
    分区将继承两个分区的较高上界

    10.5)拆分分区 
    alter table 表名
    split partition 分区名 at (边界值)
    into (partition 新分区名1, partition 新分区名2);
    可在表的开头或中间添加分区,拆分分区允许用户将一个分区
    拆分为两个分区,当分区过大时可对分区进行拆分
    
    使用:将Sales表的P3分区拆分为P31和P32两个分区,原P3分区
    存储2007年以后的数据,拆分为两个分区,一个存放2007年度数
    据,另一个存放2008年以后的数据
    alter table Sales split partition P3 at (date '2008-01-01')
    into (partition P31, partition P32);

11、总结

  • 锁用于保护多用户环境下被修改的数据
  • 锁分为两种级别,即行级锁和表级锁
  • 表分区允许将一个表划分成几部分,以改善大型应用系统的性能
  • 分区方法包括范围分区、散列分区、复合分区和列表分区
  • 分区维护操作包括添加、删除、截断、合并和拆分分区

分享到:
评论

相关推荐

    Oracle第三章锁和分区表.ppt

    Oracle第三章锁和分区表.ppt

    老二牛车第三章理论课锁和表分区.pdf

    ### Oracle数据库应用:锁和表分区 #### 一、锁的概念及作用 **锁**是数据库管理系统为了控制并发访问而采用的一种机制。在多用户环境中,为了保证数据的一致性和完整性,Oracle数据库通过锁机制来保护正在被修改...

    北大青鸟Oracle教程集

    第三章 锁和表分区; 第四章 数据库对象; 第五章 Oracle 中的 OOP 概念; 第六章 PL/SQL 简介; 第七章 异常和游标管理; 第八章 子程序和程序包; 第九章 数据库触发器和内置程序包; 第十章 集合和成员函数。

    Oracle幻灯片第三章

    在"Oracle幻灯片第三章"中,我们重点探讨的是Oracle的锁机制以及表分区这两项核心概念。 首先,让我们深入理解Oracle的锁机制。在多用户环境下,为了保证数据的一致性和完整性,Oracle数据库采用了多种类型的锁来...

    mysql数据库应用与管理视频教程详细完整版

    内容包括dba视频教程中的mysql数据库应用与管理,第一章:配置mysql服务器 第二章:获取元数据 第三章:存储引擎 第四章:隔离级别与锁 第五章:使用分区 第六章:导出与导入数据 第七章:备份与恢复 第八章:使用...

    精品云计算第三版课程配套PPT课件含习题(47页)第2章 Google云计算第三版原理与应用(二).pptx

    《云计算(第三版)》是一本深入探讨云计算理论与实践的教材,配套的PPT课件涵盖了多个重要的云计算技术和平台,特别关注了Google云计算的原理与应用。在第二章中,重点讲述了Google云计算的核心组件和服务,包括...

    MYSQL培训经典教程共14章

    第3章:数据库设计与关系模型 本章深入讲解数据库设计的原则,包括范式理论、实体关系模型和数据库规范化。通过实例,你会了解到如何有效地设计数据库,避免数据冗余和异常。 第4章:查询与联接 在这一部分,将详细...

    章鱼星球安卓精简线刷包.img

    同时,“img”后缀表明这是一个镜像文件,包含了完整的系统结构,包括系统分区、应用程序、库文件等。 为了使用这个线刷包,你需要准备以下工具和步骤: 1. **准备工作**:确保你的设备是章鱼星球,并且已经解锁...

    Oracle数据库详解

    `第3章锁和表分区.sql` 可能介绍了Oracle的并发控制机制——锁,以及如何通过表分区来优化大数据量的处理。锁确保了数据的一致性,防止并发操作导致的数据不一致。表分区则是将大表分成更小、更易管理的部分,可以...

    第三章uCOSII程序设计示例ppt课件.ppt

    【标题】: "第三章uCOSII程序设计示例ppt课件.ppt" 【描述】: "第三章uCOSII程序设计示例ppt课件.ppt" 【标签】: "第三章uCOSII程序设计示例p" 【部分内容】: 介绍了基于SmartARM2200平台的μC/OS-II程序设计,...

    S2-2-MySchool数据库设计优化(PPT+源码)【第五章】

    1. **数据库设计原则**:遵循第三范式(3NF),确保数据无冗余,减少更新异常和插入异常。MySchool数据库应确保学生、教师、课程等表的设计符合规范。 2. **索引优化**:索引能够显著提高查询速度。理解B树、哈希...

    王珊数据库系统概论(第五版)第1到11章PPT

    规范化理论中的第一范式(1NF)、第二范式(2NF)、第三范式(3NF)以及BCNF(Boyce-Codd范式)是此阶段的核心内容。物理设计则涉及到数据库的物理存储结构,如文件组织方式、索引结构等。掌握这些内容,对于数据库...

    数据库原理讲义6-12章

    重点可能是规范化理论,如第一范式(1NF)、第二范式(2NF)、第三范式(3NF)和BCNF,以及反规范化以优化性能。 第五章:数据库管理系统 DBMS是数据库系统的核心,负责管理数据的存储和访问。这一章可能详细讨论了...

    高性能MySQL(第3版).part2

    第3章服务器性能剖析67 3.1性能优化简介67 3.1.1通过性能剖析进行优化69 3.1.2理解性能剖析71 3.2对应用程序进行性能剖析72 3.2.1测量PHP应用程序74 3.3剖析MySQL查询77 3.3.1剖析服务器负载77 3.3.2剖析单...

    第3章 管理数据库 SQL 2008完全学习手册 视频教程.zip

    在本套"第3章 管理数据库 SQL 2008完全学习手册 视频教程"中,我们将深入探讨SQL Server 2008这一强大的关系型数据库管理系统。SQL Server 2008是Microsoft公司推出的一款企业级数据库解决方案,它提供了广泛的功能...

    数据库概论(第三版)

    3. **第3章:关系数据库** - 关系数据库的基本原理,如关系的规范化理论(1NF, 2NF, 3NF, BCNF等)。 - SQL语言的使用,包括数据查询、插入、更新和删除操作。 - 关系代数和关系演算,作为查询语言的理论基础。 ...

    Oracle编程技术文档

    7. **锁和表分区**:`第3章锁和表分区.sql`可能涉及到数据库的并发控制和性能优化。锁用于管理多个用户对数据的并发访问,而表分区是大型数据库中提高查询性能和管理效率的策略。 8. **其他章节**:`第8章.sql`和`...

    数据库上课课件1~6章

    规范化理论,如第一范式(1NF)、第二范式(2NF)、第三范式(3NF)和BCNF(博科斯范式),是确保数据库高效、无冗余的重要工具。此外,还会讲解反规范化和数据库性能优化。 第五章:数据库安全性与完整性 在这一章...

Global site tag (gtag.js) - Google Analytics