《Oracle数据库应用与开发》第03章[锁和分区表]理论课
锁和表分区Oracle数据库应用与开发第03章回顾 SQL 是数据库语言，Oracle 使用该语言在数据 库中存储和检索信息 Oracle 支持各种数据类型，如 VARCHAR2、 NUMBER、LONG、RAW 和 DATE 等 数据操纵语言用于查询和修改表中的数据 事务控制语言管理事务的一致性 SQL 操作符包括算术、比较、逻辑、集合和连接 操作符 SQL 函数可以大致分为单行函数、聚合函数和分 析函数目标 理解锁定的概念 了解和使用表分区锁的概念 2-1 锁是数据库用来控制共享资源并发访问的机制。 锁用于保护正在被修改的数据 直到提交或回滚了事务之后，其他用户才可以更 新数据锁的概念 2-2Toy_IDNamePriceT001Barbie20T002GI Joe45修改表修改表拒绝访问锁定的优点 一致性 - 一次只允许一个用户修改数据 完整性 - 为所有用户提供正确的数据。如果一个用户进 行了修改并保存，所做的修改将反映给所有用户Toy_IDNamePriceT001Barbie 20T002GI Joe 45q并行性 允许多个用户访问同一数据修改表中 的数据查看表中 的数据允许访问表级锁行级锁锁的类型锁的类型行级锁 3-1Toy_IDNamePriceT001Barbie20T002GI Joe45更新 T002 行更新 T001 行行被锁定q对正在被修改的行进行锁定。其他用户可以访问 除被锁定的行以外的行允许访问行级锁 3-2 行级锁是一种排他锁，防止其他事务修改此行 在使用以下语句时，Oracle会自动应用行级锁： INSERT UPDATE DELETE SELECT FOR UPDATE SELECT FOR UPDATE语句允许用户一次锁 定多条记录进行更新 使用COMMIT或ROLLBACK语句释放锁行级锁 3-3 SELECT FOR UPDATE语法:SELECT FOR UPDATE OF columns WAIT n | NOWAIT;SQL> SELECT * FROM order_master WHERE vencode=V002FOR UPDATE OF odate, del_date; SQL> UPDATE order_master SET del_date=28-8月-05WHERE vencode=V002; SQL> COMMIT;SQL> SELECT * FROM order_master WHERE vencode=V002FOR UPDATE WAIT 5;SQL> SELECT * FROM order_master WHERE vencode=V002FOR UPDATE NOWAIT;表级锁 3-1Toy_IDNamePriceT001Barbie20T002GI Joe45修改表中的行 更新表 拒绝访问锁定整个表，限制其他用户对表的访问。表级锁 3-2使用命令显示地锁定表，应用表级锁的语法是:LOCK TABLE table_name IN mode MODE;表级锁类型行共享行排他共享共享行排他排他表级锁 3-3 行共享 (ROW SHARE) 禁止排他锁定表 行排他(ROW EXCLUSIVE) 禁止使用排他锁 和共享锁 共享锁(SHARE) 锁定表，仅允许其他用户查询表中的行 禁止其他用户插入、更新和删除行 多个用户可以同时在同一个表上应用此锁 共享 行排他(SHARE ROW EXCLUSIVE) 比 共享锁更多的限制，禁止使用共享锁及更高的锁 排他(EXCLUSIVE) 限制最强的表锁，仅允许 其他用户查询该表的行。禁止修改和锁定表死锁 当两个事务相互等待对 方释放资源时，就会形 成死锁 Oracle会自动检测死锁 ，并通过结束其中的一 个事务来解决死锁 右边是一个死锁的例子T1T2lock (D1)时间lock (D2)lock (D2)lock (D1)等待等待表分区 允许用户将一个表分成多个分区 用户可以执行查询，只访问表中的特定分区 将不同的分区存储在不同的磁盘，提高访问性能 和安全性 可以独立地备份和恢复每个分区NameAddressStateCountryJohn Smith34th Rd.California USMicheal ClarkeLK Rd. New York USJack Jones9th RoadCalifornia USBob Simmons12th StreetNew York USJim Taylor53rd RoadNew York US更新表只访问 P1P1 分区P2 分区表分区的类型 9-1分区方法范围分区散列分区列表分区复合分区表分区的类型 9-2 范围分区 以表中的一个列或一组列的值的范围分区 范围分区的语法：PARTITION BY RANGE (column_name) (PARTITION part1 VALUES LESS THAN(range1),PARTITION part2 VALUES LESS THAN(range2),.PARTITION partN VALUES LESS THAN(MAXVALUE) );表分区的类型 9-3 范围分区示例SQL> 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 VARCHAR2(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) );表分区的类型 9-4散列分区 允许用户对不具有逻辑范围的数据进行分区 通过在分区键上执行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);表分区的类型 9-5 散列分区示例SQL> CREATE TABLE Employee (Employee_ID varchar2 (5),Employee_Name varchar2(20),Department varchar2 (10) ) PARTITION 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-6列表分区1. 允许用户将不相关的数据组织在一起 列表分区的语法：PARTITION BY LIST (column_name) (PARTITION part1 VALUES (values_list1),PARTITION part2 VALUES (values_list2),.PARTITION partN VALUES (DEFAULT) );表分区的类型 9-7 列表分区示例SQL> CREATE TABLE Employee (Emp_ID number (4),Emp_Name varchar2 (14),Emp_Address varchar2 (15) ) PARTITION BY LIST (Emp_Address) (Partition north values (芝加哥'),Partition west values (旧金山, 洛杉矶'),Partition south values (亚特兰大, 达拉斯, 休斯顿'),Partition east values (纽约, 波斯顿') );包含住在芝加哥的职员的记录根据职员住址在表 上创建的列表分区分区的名称表分区的类型 9-8 复合分区 范围分区与散列分区或列表分区的组合 复合分区的语法：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) );表分区的类型 9-9 复合分区示例SQL> CREATE TABLE SALES (PRODUCT_ID VARCHAR2 (5),SALES_DATE DATE NOT NULL,SALES_COST NUMBER (10) ) PARTITION BY RANGE (SALES_DATE) SUBPARTITION BY HASH (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 列中创建范围分区在表的 Product_ID 列创建散列子分区在每个范围分区中 创建 5 个散列子分区操纵已分区的表 在已分区的表中插入数据与操作普通表完全相同 ，Oracle会自动将数据保存到对应的分区 查询、修改和删除分区表时可以显式指定要操作 的分区 INSERT INTO SALES3 VALUES (P001, 02-3月-2001', 2000);
