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 节点。