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                     800ALLEN                SALESMAN                 1600WARD                 SALESMAN                 1250JONES                MANAGER                  2975MARTIN               SALESMAN                 1250BLAKE                MANAGER                  2850CLARK                MANAGER                  2450SCOTT                ANALYST                  3000KING                 PRESIDENT                5000TURNER               SALESMAN                 1500ADAMS                CLERK                    1100JAMES                CLERK                     950FORD                 ANALYST                  3000MILLER               CLERK                    130014 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 selectedscott@TEST>rollback to savepoint u3;Rollback complete.scott@TEST>select * from t1;A1                   A2                         A3-------------------- ------------------ ----------SMITH                CLERK                    1100ALLEN                SALESMAN                 1600WARD                 SALESMAN                 1250JONES                MANAGER                  2975MARTIN               SALESMAN                 1250BLAKE                MANAGER                  2850CLARK                MANAGER                  2450SCOTT                ANALYST                  3000KING                 PRESIDENT                5000TURNER               SALESMAN                 1500ADAMS                CLERK                    1100JAMES                CLERK                     950FORD                 ANALYST                  3000MILLER               CLERK                    130014 rows selected.scott@TEST>rollback to savepoint u2;Rollback complete.scott@TEST>select * from t1;A1                   A2                         A3-------------------- ------------------ ----------SMITH                CLERK                    1000ALLEN                SALESMAN                 1600WARD                 SALESMAN                 1250JONES                MANAGER                  2975MARTIN               SALESMAN                 1250BLAKE                MANAGER                  2850CLARK                MANAGER                  2450SCOTT                ANALYST                  3000KING                 PRESIDENT                5000TURNER               SALESMAN                 1500ADAMS                CLERK                    1100JAMES                CLERK                     950FORD                 ANALYST                  3000MILLER               CLERK                    130014 rows selected.scott@TEST>rollback to savepoint u1;Rollback complete.scott@TEST>select * from t1;A1                   A2                         A3-------------------- ------------------ ----------SMITH                CLERK                     900ALLEN                SALESMAN                 1600WARD                 SALESMAN                 1250JONES                MANAGER                  2975MARTIN               SALESMAN                 1250BLAKE                MANAGER                  2850CLARK                MANAGER                  2450SCOTT                ANALYST                  3000KING                 PRESIDENT                5000TURNER               SALESMAN                 1500ADAMS                CLERK                    1100JAMES                CLERK                     950FORD                 ANALYST                  3000MILLER               CLERK                    130014 rows selected.

 

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