韩顺平oracle视频笔记四（事务）

一、事物例子说明

create table student(    uuid char(32) default sys_guid() not null primary key,    name varchar(32));insert into system.student (name) values ('huangbiao');insert into system.student (name) values ('liumei');

 备注：sys_guid()函数产生的数据只有32位。

 

SQL> select * from student;

 

UUID                                 NAME

------------------------------------ ------------

00D4A520B3EE46CC85333591A072031C     huangbiao

5C2634E72D5A405BA6342B1673178818     liumei

 

savepoint a1;

delete from student where name='huangbiao';

select * frSQL> savepoint a1;

 

Savepoint created.

 

SQL> delete from student where name='huangbiao';

 

1 row deleted.

 

SQL> select * from student;

 

UUID                                 NAME

------------------------------------ -----------

5C2634E72D5A405BA6342B1673178818     liumeiom emp;

 

 

SQL> savepoint a2;

 

Savepoint created.

 

SQL> delete from student where name='liumei';

 

1 row deleted.

 

SQL> select * from student;

 

no rows selected

 

 

SQL> rollback to a2;

 

Rollback complete.

 

SQL> select * from student;

 

UUID                                 NAME

------------------------------------ -------

5C2634E72D5A405BA6342B1673178818     liumei

 

 

SQL> rollback to a1;

 

Rollback complete.

 

SQL> select * from student;

UUID                                 NAME

------------------------------------ ------------

00D4A520B3EE46CC85333591A072031C     huangbiao

5C2634E72D5A405BA6342B1673178818     liumei

 

备注：这里不能使用commit命令，否则之前所用的savepoint都将全部不起作用了。

 

二、只读事务

只允许执行查询操作，不允许其它的DML操作事务，使用只读事务可以确保用户只能读取某时间点的数据，之后的数据将不会理睬。

例如机票代售点每天18点开始统计今天的销售情况，但是18点肯定还有人在操作数据库，因此这里使用只读事务之后就不会对统计造成影响。

 

1、打开PLSQL(1)工具输入：

set transaction read only;

 

SQL> select * from student;

 

UUID                             NAME

-------------------------------- ---------

E622460F590C43C8BF746A3F43622DE3 huangbiao

40C039E5536E45A9905383333B05E499 liumei

 

2、打开另一个PLSQL(2)工具

insert into system.student (name) values ('zhangsan');

 

3、使用PLSQL(1)查询数据

SQL> select * from student;

 

UUID                             NAME

-------------------------------- ---------

E622460F590C43C8BF746A3F43622DE3 huangbiao

40C039E5536E45A9905383333B05E499 liumei

 

结果：发现第一个查询的数量和第二个PLSQL查询的数量是不一致的，这就是设置只读事务的功能

设置只读事务之后，将不会显示当前时间点之后的所有操作数据

 

4、使用PLSQL(1)插入数据

SQL> insert into system.student (name) values ('lisi');

insert into system.student (name) values ('lisi')

                   *

ERROR at line 1:

ORA-01456: may not perform insert/delete/update operation inside a READ ONLY

transaction

 

三、java代码设置手动提交

 

import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Statement;public class TestTrans {public static void main(String[] args) {Connection conn = null;Statement stmt = null;try {DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());System.out.println("driver is ok");conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","system","admin");//不让oracle自动提交，而是手动提交数据conn.setAutoCommit(false);System.out.println("connection is ok");stmt = conn.createStatement();String sql1 = "update scott.emp set sal=sal-100 where ename='SMITH'";String sql2 = "update scott.emp set sal=sal-100 where ename='SCOTT'";stmt.execute(sql1);//这段代码是模拟操作出现异常的方法int a = 1/0;stmt.execute(sql2);//执行完上面两个SQL语句之后才commitconn.commit();} catch (SQLException e) {try {//如果出现异常将操作回滚，这样就能保证数据的一致性conn.rollback();} catch (SQLException e1) {// TODO Auto-generated catch blocke1.printStackTrace();}e.printStackTrace();}if(stmt!=null){try {stmt.close();} catch (SQLException e) {e.printStackTrace();}}if(conn!=null){try {conn.close();} catch (SQLException e) {e.printStackTrace();}}}}