oracle锁和表分区

1、第三章第三章锁和表分区回顾q Oracle函数q Oracle伪列目标q 理解锁定的概念q 了解和使用表分区锁的概念 2-1q 锁是数据库用来控制共享资源并发访问的机制。q 锁用于保护正在被修改的数据q 直到提交或回滚了事务之后，其他用户才可以更新数据锁的概念 2-2修改表修改表拒绝访问Toy_IDNamePriceT001Barbie20T002GI Joe45锁定的优点q一致性一致性 - 一次只允许一个用户修改数据q完整性完整性 - 为所有用户提供正确的数据。如果一个用户进行了修改并保存，所做的修改将反映给所有用户q并行性并行性 允许多个用户访问同一数据Toy_IDNamePriceT00

2、1Barbie 20T002GI Joe 45修改表中的数据查看表中的数据允许访问表级锁行级锁锁的类型锁的类型行级锁 3-1Toy_IDNamePriceT001Barbie20T002GI Joe45更新 T002 行更新 T001 行行被锁定q对正在被修改的行进行锁定。其他用户可以访问除被锁定的行以外的行允许访问行级锁 3-2q行级锁是一种排他锁，防止其他事务修改此行q在使用以下语句时，Oracle会自动应用行级锁：qINSERTqUPDATEqDELETEqSELECT FOR UPDATEqSELECT FOR UPDATE语句允许用户一次锁定多条记录进行更新q使用COMMIT或ROL

3、LBACK语句释放锁行级锁 3-3q SELECT FOR UPDATE语法:SELECT FOR UPDATE OF columnsWAIT n | NOWAIT;SQL SELECT * FROM order_master WHERE vencode=V002 FOR UPDATE OF odate, del_date;SQL UPDATE order_master SET del_date=28-8月-05 WHERE vencode=V002;SQL COMMIT;SQL SELECT * FROM order_master WHERE vencode=V002 FOR UPDATE

4、WAIT 5;SQL SELECT * FROM order_master WHERE vencode=V002 FOR UPDATE NOWAIT;表级锁 3-1Toy_IDNamePriceT001Barbie20T002GI Joe45修改表中的行 更新表 拒绝访问锁定整个表，限制其他用户对表的访问。表级锁 3-2表级锁类型行共享行排他共享使用命令显示地锁定表，应用表级锁的语法是: LOCK TABLE table_name IN mode MODE;共享行排他排他表级锁 3-3q 共享(SHARE)：其他用户只能select，不能insert、update、delete，多个用户可以对

5、同一张表加共享锁。 排他(EXCLUSIVE)：其他用户只能select，不能insert、update、delete，不能加任何类型锁。 行共享(ROW SHARE)：其他用户可以select并加除排他锁以外的其他类型锁。 行排他(ROW EXCLUSIVE)：其他用户可以select并加除排他锁和共享锁以外的其他类型锁。 共享行排他(SHARE ROW EXCLUSIVE):其他用户只能select，不能insert、update、delete，不能加共享锁、共享行排他锁和排他锁。 死锁q 当两个事务相互等待对方释放资源时，就会形成死锁q Oracle会自动检测死锁，并通过结束其中的一个事

6、务来解决死锁q 右边是一个死锁的例子T1T2lock (D1)时间时间lock (D2)lock (D2)lock (D1)等待等待等待等待表分区q 允许用户将一个表分成多个分区q 用户可以执行查询，只访问表中的特定分区q 将不同的分区存储在不同的磁盘，提高访问性能和安全性q 可以独立地备份和恢复每个分区NameAddressStateCountryJohn Smith34th Rd.California USMicheal ClarkeLK Rd. New York USJack Jones9th RoadCalifornia USBob Simmons12th StreetNew York

7、 USJim Taylor53rd RoadNew York US更新表只访问 P1P1 分区P2 分区表分区的类型 9-1分区方法分区方法范围分区散列分区列表分区复合分区表分区的类型 9-2q 范围分区q 以表中的一个列或一组列的值的范围分区q 范围分区的语法：PARTITION BY RANGE (column_name)( PARTITION part1 VALUE LESS THAN(range1), PARTITION part2 VALUE LESS THAN(range2), . PARTITION partN VALUE LESS THAN(MAXVALUE);表分区的类型 9

8、-3SQL CREATE TABLE Sales( Product_ID varchar2 (5), Sales_Cost number (10)PARTITION BY RANGE (Sales_Cost)( PARTITION P1 VALUES LESS THAN (1000), PARTITION P2 VALUES LESS THAN (2000), PARTITION P3 VALUES LESS THAN (3000);根据 Sales_Cost 创建分区分区的名称包含销售成本低于1000 的所有产品的值 SQL CREATE TABLE SALES2 (PRODUCT_ID V

9、ARCHAR2(5),SALES_DATE DATE NOT NULL,SALES_COST NUMBER(10)PARTITION BY RANGE (SALES_DATE)( PARTITION P1 VALUES LESS THAN (DATE 2003-01-01), PARTITION P2 VALUES LESS THAN (DATE 2004-01-01), PARTITION P3 VALUES LESS THAN (MAXVALUE);q 范围分区示例表分区的类型 9-4q 散列分区q允许用户对不具有逻辑范围的数据进行分区 q通过在分区键上执行HASH函数决定存储的分区q将数

10、据平均地分布到不同的分区q 散列分区语法PARTITION BY HASH (column_name)PARTITIONS number_of_partitions;或PARTITION BY HASH (column_name)( PARTITION part1, PARTITION part2, . PARTITION partN);表分区的类型 9-5q 散列分区示例SQL CREATE TABLE Employee( Employee_ID varchar2 (5), Employee_Name varchar2(20), Department varchar2 (10)PARTITI

11、ON BY HASH (Department)( Partition D1, Partition D2, Partition D3); 在表 Employee上创建分区键 Department分区的名称创建 3 个分区SQL CREATE TABLE EMPLOYEE( EMP_ID NUMBER(4), EMP_NAME VARCHAR2(14), EMP_ADDRESS VARCHAR2(15), DEPARTMENT VARCHAR2(10)PARTITION BY HASH (DEPARTMENT)PARTITIONS 4; 表分区的类型 9-6q 列表分区q允许用户将不相关的数据组织

12、在一起q 列表分区的语法：PARTITION BY LIST (column_name)( PARTITION part1 VALUES (values_list1), PARTITION part2 VALUES (values_list2), . PARTITION partN VALUES (DEFAULT);表分区的类型 9-7SQL CREATE TABLE Employee( Emp_ID number (4), Emp_Name varchar2 (14), Emp_Address varchar2 (15)PARTITION BY LIST (Emp_Address)( Par

13、tition north values (芝加哥), Partition west values (旧金山, 洛杉矶), Partition south values (亚特兰大, 达拉斯, 休斯顿), Partition east values (纽约, 波斯顿);包含住在芝加哥的职员的记录根据职员住址在表上创建的列表分区分区的名称q 列表分区示例表分区的类型 9-8q复合分区q范围分区与散列分区的组合q复合分区的语法：PARTITION BY RANGE (column_name1)SUBPARTITION BY HASH (column_name2)SUBPARTITIONS numbe

14、r_of_partitions( PARTITION part1 VALUE LESS THAN(range1), PARTITION part2 VALUE LESS THAN(range2), . PARTITION partN VALUE LESS THAN(MAXVALUE);表分区的类型 9-9SQL CREATE TABLE SALES( PRODUCT_ID VARCHAR2 (5), SALES_DATE DATE NOT NULL, SALES_COST NUMBER (10)PARTITION BY RANGE (SALES_DATE)SUBPARTITION BY HAS

15、H (PRODUCT_ID)SUBPARTITIONS 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);创建的四个范围分区的名称在表的 Sales_Date 列中创建范围

16、分区在表的 Product_ID 列创建散列子分区在每个范围分区中创建 5 个散列子分区q 复合分区示例操纵已分区的表 q在已分区的表中插入数据与操作普通表完全相同，Oracle会自动将数据保存到对应的分区q查询、修改和删除分区表时可以显式指定要操作的分区 INSERT INTO SALES3 VALUES (P001, 02-3月-2001, 2000);INSERT INTO SALES3 VALUES (P002, 10-5月-2001, 2508);INSERT INTO SALES3 VALUES (P003, 05-7月-2001, 780);INSERT INTO SALES3

17、VALUES (P004, 12-9月-2001, 1080);SELECT * FROM SALES3 PARTITION (P3);DELETE FROM SALES3 PARTITION (P2);分区维护操作 q分区维护操作修改已分区表的分区。q分区维护的类型：q计划事件 定期删除最旧的分区q非计划事件 解决应用程序或系统问题q分区维护操作有：q添加分区q删除分区q截断分区q合并分区q拆分分区维护分区 2-1q 添加分区 在最后一个分区之后添加新分区SQL ALTER TABLE SALES ADD PARTITION P4 VALUES LESS THAN (4000);q 删除分区 删除一个指定的分区，分区的数据也随之删除SQL ALTER TABLE SALES DROP PARTITION P4;q 截断分区 删除指定分区中的所有记录 SQL ALTER TABLE SALES TRUNCATE PARTITION P3;维护分区 2-2q 合并分区 - 将范围分区或复合分区的两个相邻分区连接起来SQL ALTER TABLE SALES