锁的概念
- 锁是数据库用来控制共享资源并发访问的机制——在多用户环境下,多个用户可同时访问相同的数据。Oracle 提供锁以确保在多用户环境下数据的完整性和一致性。
- 锁通常是有Oracle系统自动管理的,但用户也可 以间接的控制其中的部分锁。以间接的控制其中的部分锁。
- 事务的开始和结束决定了Oracle对于锁的持有和释放。
根据保护对象不同常见锁:
- DML锁:用于保护数据完整性
- DDL锁:用于保护数据库对象的结构
- 内部锁:保护内部数据库结构
- 。。。。。
按照锁的粒度分类:
- 行级锁:对于处理少量记录的事务使用—只有一种排他锁(X锁)也叫事务锁(TX锁)或行级锁。
- 表级锁:对于处理一个表中的大量记录的事务使用—共享锁(S锁) 、排他锁(X锁) 、行共享锁(RS锁) 、行排他锁(RX锁) 、共享行排 他锁(SRX锁)
- 数据库级锁:对于处理多个表中的大量记录的事务使用,一般由数据库 管理员通过命令将数据库设置成限制型模式的来锁定整个数据库。
锁的粒度和系统开销成反比,粒度越大并发度越小,系统开销 就越少,相反,粒度越小并发度越大,系统开销越大。
行级锁
对正在被修改的行进行锁定。其他用户可以修改除被锁定的行以外的行。
行级锁是一种排他锁,防止其他事务修改此行。
在使用以下语句时,Oracle会自动应用行级锁:
- INSERT
- UPDATE
- DELETE
- SELECT … FOR UPDATE
SELECT … FOR UPDATE语句允许用户一次锁定多条记录进行更新。
使用COMMIT或ROLLBACK语句释放锁。
SELECT … FOR UPDATE [OF columns] [WAIT n | NOWAIT];
实例:
在第一个会话,输入如下SQL语句:
SELECT * FROM scott.emp where empno=7934 FOR UPDATE OF sal;
启动第二个会话,输入如下SQL语句:
SELECT * FROM scott.emp where empno=7934 FOR UPDATE OF sal;
在第一个会话的输入如下SQL语句:
UPDATE scott.emp SET sal = sal+500 WHERE empno= 7934; COMMIT;
设置行级锁等待时间(秒数)
在第一个会话的输入如下SQL语句:
SELECT * FROM scott.emp where empno=7934 FOR UPDATE OF sal ;
启动第二个会话,输入如下SQL语句:
SELECT * FROM scott.emp where empno=7934 FOR UPDATE OF sal wait 5;
表级锁
锁定整个表,限制其他用户对表的访问。
使用命令显式地锁定表,应用表级锁的语法是:
lock table<table_name> in <lock_mode> mode [nowait];
表级锁的类型:
- 共享锁(SHARE,S锁)
- 排他锁(EXCLUSIVE,X锁)
- 行级共享锁(ROW SHARE,RS锁)
- 行级排他锁(ROW EXCLUSIVE,RX锁)
- 共享行级排他锁(SHARE ROW EXCLUSIVE,SRX锁):表示对表施加了 S锁,而对要进行更新的行施加了RX锁
RS锁是限制最少的锁、而X锁是限制最多的锁。
锁的相容性:
表级锁应用案例
- 第一个窗口以SCOTT身份登录,用S锁锁定emp表
LOCK TABLE emp IN SHARE MODE;
系统显示表已锁定
- 第二个窗口仍使用SCOTT身份登录,用S锁锁定emp表
LOCK TABLE emp IN SHARE MODE;
系统显示表已锁定,共享锁特点
- 第二个窗口提交
COMMIT;
释放第二个窗口的共享锁
- 第二个窗口修改数据
UPDATE emp SET sal =1000;
会话阻塞,第一个会话锁定表,只允许查询,不允许修改
- 第一个窗口提交
COMMIT;
释放第一个会话的共享锁
同时第二个会话获得了相应的锁
- 第二个窗口回滚事务
ROLLBACK;
死锁
当两个事务相互等待对方释放资源时,就会形成死锁。
Oracle会自动检测死锁 ,并通过结束其中的一 个事务来解决死锁。
表类型
Oracle数据库中表的类型有很多种:
- 默认表类型(又称堆表)。
- 外部表:建立在数据库之外的文件中。
- 索引组织表:基于主键对记录进行排序的表 。
- 临时表:在会话或事务中临时使用的表。
- 对象表:使用面向对象概念中的对象创建的表。
- 分区表:表数据量达到几十到几百GB,甚至TB级,为了提高读取和查询速度,可以将一个表中数据分成较小的 片段分别存储,分别查询。
- 簇表:相互关联的一组表,共享相同的数据段,提高了 连接查询的效率 。
表分区技术
问题:当同一表中纪录数非常大时,给数据的存取带来很 大的麻烦。
Oracle向用户提供了表分区技术,以改善大型应用系统的性能。
表分区技术:允许用户把一个表中的所有行分为几个部分,并将各部分存储在不同的位置,被分区的表称为分区表, 并将各部分存储在不同的位置,被分区的表称为分区表, 分成的每一部分称为一个分区。
表分区的好处:
- 增强可用性
- 减少关闭时间
- 维护轻松
- 均衡I/O
- 改善性能
- 分区对用户透明
分区的类型
范围分区
以表中的一个列或一组列的值的范围分区。
范围分区的语法:
PARTITION BY RANGE (column_name)
( PARTITION part1 VALUES LESS THAN(range1),
PARTITION part2 VALUES LESS THAN(range2), ...
[PARTITION partN VALUES LESS THAN(MAXVALUE)]
);
范围分区示例
create table drawlist(
dt_draw date not null
)
partition by range(dt_draw)(
partition p1 values less than(to_date('1/1/2013','dd-mm-yyyy')),
partition p2 values less than(to_date('1/1/2014','dd-mm-yyyy')),
partition p3 values less than(maxvalue)
);
select * from drawlist partition (p1);
select * from drawlist partition (p2);
select * from drawlist partition (p3);
散列分区
- 允许用户对不具有逻辑范围的数据进行分区
- 通过在分区键上执行HASH函数决定存储的分区
- 将数据平均地分布到不同的分区
散列分区语法:
PARTITION BY HASH (column_name)
PARTITIONS number_of_partitions;
或 PARTITION BY HASH (column_name)
( PARTITION part1 [TABLESPACE tbs1],
PARTITION part2 [TABLESPACE tbs2], ...
PARTITION partN [TABLESPACE tbsN]);
散列分区示例
-------方法1
create table hash_table(
hash_no number(10) not null
)
partition by hash(hash_no)(
partition p1,
partition p2,
partition p3,
partition p4
);
-------方法2
create table hash_table(
hash_no number(10) not null
)
partition by hash(hash_no) partitions 4;
列表分区
允许用户将不相关的数据组织在一起。
列表分区的语法:
PARTITION BY LIST (column_name)
(
PARTITION part1 VALUES (values_list1),
PARTITION part2 VALUES (values_list2), ...
PARTITION partN VALUES (DEFAULT)
);
列表分区示例:
create table area(
code number(10) not null,
name varchar2(10) not null
)
partition by list(code)(
partition p1 values(102200,102202,102203),
partition p2 values(164300,164302,164303)
);
复合分区
范围分区与散列分区或列表分区的组合。
复合分区的语法:
PARTITION BY RANGE (column_name1)
SUBPARTITION BY HASH (column_name2)
SUBPARTITIONS number_of_partitions (
PARTITION part1 VALUE LESS THAN(range1),
PARTITION part2 VALUE LESS THAN(range2), ...
PARTITION partN VALUE LESS THAN(MAXVALUE)
);
复合分区实例:
CREATE TABLE SALESFH (
PRODUCT_ID VARCHAR2 (5),
SALES_DATE DATE NOT NULL,
SALES_COST NUMBER (10)
)
PARTITION BY RANGE (SALES_DATE) ------在表的 Sales_Date 列中创建范围分区
SUBPARTITION BY HASH (PRODUCT_ID) ------在表的 Product_ID 列创建散列子分区
SUBPARTITIONS 5 ------在每个范围分区中创建5个散列子分区
(
------创建的四个范围分区的名称
PARTITION S1 VALUES LESS THAN (TO_DATE(‘01/4月/2001', 'DD/MON/YYYY')),
PARTITION S2 VALUES LESS THAN (TO_DATE(‘01/7月/2001', 'DD/MON/YYYY')),
PARTITION S3 VALUES LESS THAN (TO_DATE(‘01/9月/2001', 'DD/MON/YYYY')),
PARTITION S4 VALUES LESS THAN (MAXVALUE)
);
操纵已分区的表
在已分区的表中插入数据与操作普通表完全相同,Oracle 会自动将数据保存到对应的分区。
查询、修改和删除分区表时可以显式指定要操作的分区。
INSERT INTO T_SALES VALUES (‘P001’,’ 02-3月-10 ’, 2000);
INSERT INTO T_SALES VALUES ('P002', ‘15-3月-10 ’, 2508);
INSERT INTO T_SALES VALUES ('P003', ‘23-5月-10 ’, 780);
INSERT INTO T_SALES VALUES ('P004', ‘16-6月-10 ’, 1080);
SELECT * FROM T_SALES PARTITION (P3);
DELETE FROM T_SALES PARTITION (P2);
分区维护操作
分区维护操作修改已分区表的分区。
分区维护的类型:
- 计划事件 - 定期删除最旧的分区
- 非计划事件 - 解决应用程序或系统问题
分区维护操作有:
- 添加分区
- 添加分区
- 删除分区
- 截断分区
- 合并分区
- 拆分分区
维护分区
- 添加分区 – 在最后一个分区之后添加新分区
ALTER TABLE t_sales ADD PARTITION P4 VALUES LESS THAN (4000);
- 删除分区 – 删除一个指定的分区,分区的数据也随之删除
ALTER TABLE t_sales DROP PARTITION P4;
- 截断分区 – 删除指定分区中的所有记录
ALTER TABLE t_sales TRUNCATE PARTITION P3;
- 合并分区 - 将范围分区或复合分区的两个相邻分区连接起来
ALTER TABLE t_sales MERGE PARTITIONS P1, P2 INTO PARTITION P12;
- 拆分分区 - 将一个大分区中的记录拆分到两个分区中
ALTER TABLE t_sales SPLIT PARTITION P12 AT (1500) INTO (PARTITION P1, PARTITION P2);
本章总结
- 锁用于保护多用户环境下被修改的数据
- 锁分为两种级别,即行级锁和表级锁
- 表分区允许将一个表划分成几部分,以改善大型应用系统的性能
- 分区方法包括范围分区、散列分区、复合分区和列表分区
- 分区维护操作包括添加、删除、截断、合并和拆分分区
2018.8.27