事务的概念和事务的控制

transaction 事务的概念

　　开始：第一个dml语句

　　结束：commit或者rollback

　　未完成的事务可以撤销

　　未完成的事务，其它会话看不到结果，只能看到已经提交的结果

　　维护事务需要锁和回退段的参与

提交事务 commit

　　1.手工直接提交 commit

　　2.自动提交

　　　　ddl，dcl语句

　　　　exit 退出sqlplus

　　3.提交后

　　　　事务结束

　　　　释放锁和回退

　　　　其它用户可以看到结果，修改过的结果

撤销事务 rollback

　　1.手工直接撤销rollback

　　2.自动撤销

　　　　网络或数据库崩溃

　　　　强制退出sqlplus

　　3.撤销后

　　　　事务结束

　　　　修改前的数据恢复了

　　　　释放锁和回退

　　　　其它用户可以看到结果，未修改的结果

事务的控制

scott@TEST>select * from t1;

A1                   A2                         A3
-------------------- ------------------ ----------
SMITH                CLERK                     800
ALLEN                SALESMAN                 1600
WARD                 SALESMAN                 1250
JONES                MANAGER                  2975
MARTIN               SALESMAN                 1250
BLAKE                MANAGER                  2850
CLARK                MANAGER                  2450
SCOTT                ANALYST                  3000
KING                 PRESIDENT                5000
TURNER               SALESMAN                 1500
ADAMS                CLERK                    1100
JAMES                CLERK                     950
FORD                 ANALYST                  3000
MILLER               CLERK                    1300

14 rows selected.

scott@TEST>update t1 set a3=900 where a1='SMITH';

1 row updated.

scott@TEST>savepoint u1;

Savepoint created.

scott@TEST>update t1 set a3=1000 where a1='SMITH';

1 row updated.

scott@TEST>savepoint u2;

Savepoint created.

scott@TEST>update t1 set a3=1100 where a1='SMITH';

1 row updated.

scott@TEST>savepoint u3;

Savepoint created.

scott@TEST>delete t1;

14 rows deleted.

scott@TEST>select * from t1;

no rows selected

scott@TEST>rollback to savepoint u3;

Rollback complete.

scott@TEST>select * from t1;

A1                   A2                         A3
-------------------- ------------------ ----------
SMITH                CLERK                    1100
ALLEN                SALESMAN                 1600
WARD                 SALESMAN                 1250
JONES                MANAGER                  2975
MARTIN               SALESMAN                 1250
BLAKE                MANAGER                  2850
CLARK                MANAGER                  2450
SCOTT                ANALYST                  3000
KING                 PRESIDENT                5000
TURNER               SALESMAN                 1500
ADAMS                CLERK                    1100
JAMES                CLERK                     950
FORD                 ANALYST                  3000
MILLER               CLERK                    1300

14 rows selected.

scott@TEST>rollback to savepoint u2;

Rollback complete.

scott@TEST>select * from t1;

A1                   A2                         A3
-------------------- ------------------ ----------
SMITH                CLERK                    1000
ALLEN                SALESMAN                 1600
WARD                 SALESMAN                 1250
JONES                MANAGER                  2975
MARTIN               SALESMAN                 1250
BLAKE                MANAGER                  2850
CLARK                MANAGER                  2450
SCOTT                ANALYST                  3000
KING                 PRESIDENT                5000
TURNER               SALESMAN                 1500
ADAMS                CLERK                    1100
JAMES                CLERK                     950
FORD                 ANALYST                  3000
MILLER               CLERK                    1300

14 rows selected.

scott@TEST>rollback to savepoint u1;

Rollback complete.

scott@TEST>select * from t1;

A1                   A2                         A3
-------------------- ------------------ ----------
SMITH                CLERK                     900
ALLEN                SALESMAN                 1600
WARD                 SALESMAN                 1250
JONES                MANAGER                  2975
MARTIN               SALESMAN                 1250
BLAKE                MANAGER                  2850
CLARK                MANAGER                  2450
SCOTT                ANALYST                  3000
KING                 PRESIDENT                5000
TURNER               SALESMAN                 1500
ADAMS                CLERK                    1100
JAMES                CLERK                     950
FORD                 ANALYST                  3000
MILLER               CLERK                    1300

14 rows selected.

 

注意：如果保存了四个回退点 比如 1 - 2 - 3 - 4，如果直接回退到节点2，那么保存的3 和 4 节点就会被删除，不能回退到3 和4 只能回退到1 节点。