Oracle 第3章 锁、表分区

 

Oracle 第2章 Oracle查询、数据类型、函数
Oracle 第4章 同义词、序列、视图、索引

 

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
);

8、复合分区

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

复合分区语法：
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),
);

9、列表分区

列表分区允许用户明确地控制行到分区的映射，列表分区允许按自然方式
对无序和不相关的数据集进行分组和组织，语法如下：
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('江苏', '上海')
);

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

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

11、分区维护

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

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

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

部分分区维护操作如下：

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

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

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

    11.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);

12、总结

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

 

Oracle 第2章 Oracle查询、数据类型、函数
Oracle 第4章 同义词、序列、视图、索引