TP3 锁和表分区

第三章锁和表分区2回顾SQL是数据库语言，Oracle使用该语言在数据库中存储和检索信息Oracle支持各种数据类型，如VARCHAR2、NUMBER、LONG、RAW和DATE等数据操纵语言用于查询和修改表中的数据事务控制语言管理事务的一致性SQL操作符包括算术、比较、逻辑、集合和连接操作符SQL函数可以大致分为单行函数、聚合函数和分析函数3目标理解锁定的概念了解和使用表分区4锁的概念2-1锁是数据库用来控制共享资源并发访问的机制。锁用于保护正在被修改的数据直到提交或回滚了事务之后，其他用户才可以更新数据5锁的概念2-2修改表修改表拒绝访问Toy_IDNamePriceT001Barbie20T002GIJoe456锁定的优点一致性-一次只允许一个用户修改数据完整性-为所有用户提供正确的数据。如果一个用户进行了修改并保存，所做的修改将反映给所有用户并行性－允许多个用户访问同一数据Toy_IDNamePriceT001Barbie20T002GIJoe45修改表中的数据查看表中的数据允许访问7表级锁行级锁锁的类型锁的类型8行级锁3-1Toy_IDNamePriceT001Barbie20T002GIJoe45更新T002行更新T001行行被锁定对正在被修改的行进行锁定。其他用户可以访问除被锁定的行以外的行允许访问9行级锁3-2行级锁是一种排他锁，防止其他事务修改此行在使用以下语句时，Oracle会自动应用行级锁：INSERTUPDATEDELETESELECT…FORUPDATESELECT…FORUPDATE语句允许用户一次锁定多条记录进行更新使用COMMIT或ROLLBACK语句释放锁10行级锁3-3SELECT…FORUPDATE语法:SELECT…FORUPDATE[OFcolumns][WAITn|NOWAIT];SQLSELECT*FROMorder_masterWHEREvencode=’V002’FORUPDATEOFodate,del_date;SQLUPDATEorder_masterSETdel_date=’28-8月-05’WHEREvencode=’V002’;SQLCOMMIT;SQLSELECT*FROMorder_masterWHEREvencode=’V002’FORUPDATEWAIT5;SQLSELECT*FROMorder_masterWHEREvencode=’V002’FORUPDATENOWAIT;11表级锁3-1Toy_IDNamePriceT001Barbie20T002GIJoe45修改表中的行更新表拒绝访问锁定整个表，限制其他用户对表的访问。12表级锁3-2表级锁类型行共享行排他共享使用命令显示地锁定表，应用表级锁的语法是:LOCKTABLEtable_nameINmodeMODE;共享行排他排他13表级锁3-3行共享(ROWSHARE)–禁止排他锁定表行排他(ROWEXCLUSIVE)–禁止使用排他锁和共享锁共享锁(SHARE)锁定表，仅允许其他用户查询表中的行禁止其他用户插入、更新和删除行多个用户可以同时在同一个表上应用此锁共享行排他(SHAREROWEXCLUSIVE)–比共享锁更多的限制，禁止使用共享锁及更高的锁排他(EXCLUSIVE)–限制最强的表锁，仅允许其他用户查询该表的行。禁止修改和锁定表14死锁当两个事务相互等待对方释放资源时，就会形成死锁Oracle会自动检测死锁，并通过结束其中的一个事务来解决死锁右边是一个死锁的例子T1T2lock(D1)时间lock(D2)lock(D2)lock(D1)等待等待……15表分区允许用户将一个表分成多个分区用户可以执行查询，只访问表中的特定分区将不同的分区存储在不同的磁盘，提高访问性能和安全性可以独立地备份和恢复每个分区NameAddressStateCountryJohnSmith34thRd.CaliforniaUSMichealClarkeLKRd.NewYorkUSJackJones9thRoadCaliforniaUSBobSimmons12thStreetNewYorkUSJimTaylor53rdRoadNewYorkUS更新表只访问P1P1分区P2分区16表分区的类型9-1分区方法范围分区散列分区列表分区复合分区17表分区的类型9-2范围分区以表中的一个列或一组列的值的范围分区范围分区的语法：PARTITIONBYRANGE(column_name)(PARTITIONpart1VALUELESSTHAN(range1),PARTITIONpart2VALUELESSTHAN(range2),...[PARTITIONpartNVALUELESSTHAN(MAXVALUE)]);18表分区的类型9-3SQLCREATETABLESales(Product_IDvarchar2(5),Sales_Costnumber(10))PARTITIONBYRANGE(Sales_Cost)(PARTITIONP1VALUESLESSTHAN(1000),PARTITIONP2VALUESLESSTHAN(2000),PARTITIONP3VALUESLESSTHAN(3000));根据Sales_Cost创建分区分区的名称包含销售成本低于1000的所有产品的值SQLCREATETABLESALES2(PRODUCT_IDVARCHAR2(5),SALES_DATEDATENOTNULL,SALES_COSTNUMBER(10))PARTITIONBYRANGE(SALES_DATE)(PARTITIONP1VALUESLESSTHAN(DATE‘2003-01-01’),PARTITIONP2VALUESLESSTHAN(DATE‘2004-01-01’),PARTITIONP3VALUESLESSTHAN(MAXVALUE));范围分区示例19表分区的类型9-4散列分区允许用户对不具有逻辑范围的数据进行分区通过在分区键上执行HASH函数决定存储的分区将数据平均地分布到不同的分区散列分区语法PARTITIONBYHASH(column_name)PARTITIONSnumber_of_partitions;或PARTITIONBYHASH(column_name)(PARTITIONpart1[TABLESPACEtbs1],PARTITIONpart2[TABLESPACEtbs2],...PARTITIONpartN[TABLESPACEtbsN]);20表分区的类型9-5散列分区示例SQLCREATETABLEEmployee(Employee_IDvarchar2(5),Employee_Namevarchar2(20),Departmentvarchar2(10))PARTITIONBYHASH(Department)(PartitionD1,PartitionD2,PartitionD3);在表Employee上创建分区键Department分区的名称创建3个分区SQLCREATETABLEEMPLOYEE(EMP_IDNUMBER(4),EMP_NAMEVARCHAR2(14),EMP_ADDRESSVARCHAR2(15),DEPARTMENTVARCHAR2(10))PARTITIONBYHASH(DEPARTMENT)PARTITIONS4;21表分区的类型9-6列表分区允许用户将不相关的数据组织在一起列表分区的语法：PARTITIONBYLIST(column_name)(PARTITIONpart1VALUES(values_list1),PARTITIONpart2VALUES(values_list2),...PARTITIONpartNVALUES(DEFAULT));22表分区的类型9-7SQLCREATETABLEEmployee(Emp_IDnumber(4),Emp_Namevarchar2(14),Emp_Addressvarchar2(15))PARTITIONBYLIST(Emp_Address)(Partitionnorthvalues(‘芝加哥'),Partitionwestvalues(‘旧金山’,‘洛杉矶'),Partitionsouthvalues(‘亚特兰大’,‘达拉斯’,‘休斯顿'),Partitioneastvalues(‘纽约’,‘波斯顿'));包含住在芝加哥的职员的记录根据职员住址在表上创建的列表分区分区的名称列表分区示例23表分区的类型9-8复合分区范围分区与散列分区或列表分区的组合复合分区的语法：PARTITIONBYRANGE(column_name1)SUBPARTITIONBYHASH(column_name2)SUBPARTITIONSnumber_of_partitions(PARTITIONpart1VALUELESSTHAN(range1),PARTITIONpart2VALUELESSTHAN(range2),...PARTITIONpartNVALUELESSTHAN(MAXVALUE));24表分区的类型9-9SQLCREATETABLESALES(PRODUCT_IDVARCHAR2(5),SALES_DATEDATENOTNULL,SALES_COSTNUMBER(10))PARTITIONBYRANGE(SALES_DATE)SUBPARTITIONBYHASH(PRODUCT_ID)SUBPARTITIONS5(PARTITIONS1VALUESLESSTHAN(TO_DATE(‘01/4月/2001','DD/MON/YYYY')),PARTITIONS2VALUESLESSTHAN(TO_DATE(‘01/7月/2001','DD/MON/YYYY')),PARTITIONS3VALUESLESSTHAN(TO_DATE(‘01/9月/2001','DD/MON/YYYY')),PARTITIONS4VALUESLESSTHAN(MAXVALUE));创建的四个范围分区的名称在表的Sales_Date列中创建范围分区在表的Product_ID列创建散列子分区在每个范围分区中创建5个散列子分区复合分区示例25操纵已分区的表在已分区的表中插入数据与操作普通表完全相同，Oracle会自动将数据保存到对应的分区查询、修改和删除分区表时可以显式指定要操作的分区INSERTINTOSALES3VALUES(‘P001’,’02-3月-2001',2000);INSERTINTOSALES3VALUES(‘P002’,’10-5月-2001',2508);INSERTINTOSALES3VALUES(‘P003’,’05-7月-2001',780);INSERTINTOSALES3VALUES(‘P004’,’12-9月-2001',1080);SELECT*FROMSALES3PARTITION(P3);DELETEFROMSALES3PARTITION(P2);26分区维护操作分区维护操作修改已分区表的分区。分区维护的类型：计划事件－定期删除最旧的分区非计划事件－解决应用程序或系统问题分区维护操作有：添加分区删除分区截断分区合并分区拆分分区27维护分区2-1添加分区–在最后一个分区之后添加新分区SQLALTERTABLESALESADDPARTITIONP4VALUESLESSTHAN(4000);删除分区–删除一个指定的分区，分区的数据也随之删除SQLALTERTABLESALESDROPPARTITIONP4;截断分区–删除指定分区中的所有记录SQLALTERTABLESALESTRUNCATEPARTITIONP3;28维护分区2