勿传网上！严禁谋利！ Oracle学习笔记

常彦博

十七、事务

17.1 transaction

交易，事务；（一笔）交易，（一项）事务。

client端建立连接 connect jsd1304/jsd1304(sqlplus/sql developer/jdbc) DDL操作： create table (column 列 datatype 数据类型 constraint约束)； alter table 对列、约束等的操作 drop table cascade constraint purge; DML操作：insert、update、delete TCL操作：commit、rollback，数据才真正入库 table:account column id,balance余额 row：A B update account set balance = balance-1000 where id='A'; update account set balance = balance+1000 where id='B'; 交易：包含2个update，一笔交易应看作一个原子操作：要做一起作，要么都不作 帐平，不是单条DML，而是一组DML+commin、rollback（transaction） 17.2定义 事务是由一组DML语句和commit/rollback(TCL)组成，是改变数据库数据的最小逻辑单元。 1）如果是commit，表示数据入库；如果是rollback，表示取消所有的DML操作。 2）事务的结束：commin/rollback，DDL语句自动提交，DML一定要有显式commit。用程序传数据入库也要传一个事务！ 3）事务的开始：上一个事务的结束就是下一个事务的开始。 ? 注意事项：我们传送的是一个事务，而不是简单的DML语句了！数据库中也都是一个个事务。切记！！ 17.3事务的特性：ACID 1）原子性（atomicity）：一个事务或者完全发生，或者完全不发生。由DML和TCL共同完成的！！ 2）一致性（consistency）：事务把数据库从一个一致状态转变到另一个状态。 3）隔离性（isolation）：在事务提交之前，其他事务觉察不到事务的影响。 4）持久性（durability）：一旦事务提交，它是永久的。

17.4事务的隔离级别

数据库应用程序中最常用的隔离级别。

Read committed：一个事务只可以读取在事务开始之前提交的数据和本事务正在修改的数据。

17.5数据库开发的关键挑战

在开发多用户、数据库驱动的应用程序中，关键性的挑战之一是要使并行的访问量达到

35

勿传网上！严禁谋利！ Oracle学习笔记

最大化，同时还要保证每一个用户（会话）可以以一致的方式读取并修改数据。

1）锁（lock）机制：用来管理对一个共享资源的并行访问 2）多版本一致读：

①非阻塞查询：写不阻塞读，读不阻塞写 ②一致读查询：在某一时刻查询产生一致结果

常彦博

17.6锁的概念

1）排他锁（X锁）：如果一个对象上加了X锁，在这个锁被采用后，直到commit或rollback释放它之前，该对象上不能施加任何其他类型的锁。

2）共享锁（S锁）：如果一个对象被加上了S锁，该对象上可以加其他类型的S锁，但是，在该锁释放之前，该对象不能被加任何其他类型的X锁。

17.7 Oracle的锁机制

为确保并发用户能正确使用与管理共享资源，如表中的记录，Oracle引进锁机制。 1）对表的数据进行写操作时，系统会自动加两类，共3种锁。 ①DML锁：用于保护数据的完整性，会加以下两种锁（对DML操作而言的，用排队机制wait）。

TX锁：即事务锁（行级锁），类型为X锁；操作哪行记录就加排他锁 TM锁：即意向锁（表级锁），属于一种S锁；操作哪个表就加共享锁

? 注意事项：

? 不提交，锁不会被释放！就会把别人堵塞住！

? 不提交，锁不会被释放！别人也看不到你对数据的操作。 ? select语句是读操作，没有上锁。 ②DDL锁：用于保护数据库对象的结构（例如表、索引的结构定义）（正在写操作

时，不可修改表的结果，否则直接报错error）。 X类型的DDL锁：这些锁定防止其他的会话，自己获得DDL锁定或TM（DML）

锁定。这意味着可以在DDL其间查询一个表，但不可以以任何方式进行修改。

? 注意事项：出现wait和no wait（即DDL报错）都是因为不能再加X锁导致的。

17.8事务不提交的后果

1）其他事务看不见它的操作结果。 2）表和行上的锁不释放，会阻塞其他事务的操作。 3）它所操作的数据可以恢复到之前的状态。

4）占用的回滚端资源不释放，rollback segment/undo segment会滚段（公共空间）。

17.9回滚事务rollback 1）数据的改变就像从未发生过一样

2）插入的数据没有了，更新前和删除前的数据都恢复出来。

3）锁被释放。

17.10保留点savepoint

用savepoint在当前事务里创建一个保留点，用rollback to savepoint命令将事务回滚到标记点。 SQL>insert??; savepoint created. SQL>insert??; 36

SQL>update??; SQL>savepoint update_done; rollback complete. SQL>rollback to update_done; 勿传网上！严禁谋利！ Oracle学习笔记

常彦博

十八、数据库对象：视图view

18.1带子查询的create table

create table tabname[column(,column?)] as subquery;

1）根据子查询语句创建表并插入数据（根据已有的表创建新表）。

2）表结构由子查询的select语句决定，create table指定的列的数量要跟select语句指定的列的数量一致。

3）create table 定义列只能定义列名、缺省值、完整性约束，不能定义数据类型。 4）约束不能被复制过来，但非空约束不需要定义可以直接复制过来。

eg1:20机器上的业务信息 create table service_20 as select * from service where unix_host='192.168.0.20'; eg2：创建一张表account_90，表结构与account一致，没有数据 create table account_90 as select * from account where 1 = 2 ; ? 注意事项：where 1=2;是通用的，所以记录不符合条件；若子查询的返回记录数为0，新建的表就只有结构。1 = 2是永假式，任何表都不会返回记录。where 1=1;所有记录符合条件 18.2带子查询的insert insert into new_tab(colname1,colname2,?colnamen) select colname1,colname2,?colnamen from old_tab where condition; 1）根据子查询语句向表中插入数据。 2）insert指定的列的数量要跟select语句指定的列的数量一致。 3）一次可以插入多条记录，不能用values子句。 ? 注意事项：若插入多个列，则需要把所有的非空列都选出，否则报错，因为非空约束直接复制过来了。 eg：account_90表中包含所有的90后客户 insert into account_90_chang select * from account where to_char(birthdate,'yyyy') between 1990 and 1999; 18.3定义缺省值：default

?colname date default sysdate,?

1）缺省值的数据类型必须匹配列的数据类型。

2）有效的缺省值为文字值，表达式、sql函数：sysdate、user等。 3）无效的缺省值为另一个列的列名或伪列。 4）default可以用于insert语句、update语句。

? 注意事项：insert语句、update语句都可写非关联自查询。 eg1：案例1 drop table test purge; create table test( c1 number default 1,c2 number);

37

勿传网上！严禁谋利！ Oracle学习笔记

insert into test (c2) values (2); insert into test values (default,3); eg2：案例2 insert into test values (4,4); update test set c1 = default where c1=4;把c1列等于4的都换成默认值 常彦博

18.4 视图view

1）视图在数据库中不存储数据值，即不占空间。 2）只在系统表中存储对视图的定义。 3）视图实际就是一条select语句。 4）类似windows中的快捷方式。

? 注意事项：对象类型都按大写存储的，所以查找也写大写。 eg1：创建view create of replace view test_v1 as select * from test where c1=2 eg2：查找view test_v1是如何定义的，找对应的select语句 select view_name,text from user_views where view_name = 'TEST_V1'; eg3：查找view test_v1当前的状态，若为invalid则源表出问题了 select object_name,object_type,status from user_objects where object_name = 'TEST_V1'; eg4：当视图无效时 alter view test_v1 compile; //当视图无效时，尝试先编译视图，若不能编译，则源表不存在，要创建表。 create table test(c1 number,c2 number);//此时系统不会自动编译。 select * from test_v1;//当从视图中查询时，系统会做alter view test_v1 compile即编译操作，此时视图就有效了。 18.5视图的应用场景 1）简化操作，屏蔽了复杂的SQL语句，直接对视图操作。 2）控制权限，只允许查询一张表中的部分数据。解决办法：对其创建视图，授予用户读视图的权限，而非读表的权限。 eg：允许授权account表部分数据给jsd1304 create or replace view account_1304 as select * from account where 条件表达式 grant select on account_1304 to jsd1304; 3）通过视图将多张表union all成一张逻辑表，作为单独一个数据库对象，实现表的超集。

eg：数据库中有分区表

table heap table堆表，我们当前用的这些无序的一般表 table partition table分区表 create table haidian create table haidian1 create table xicheng create table changping ??

38