源码-Oracle数据库管理-第十八章-事务和锁-Part 1（使用Oracle事务）

来源：互联网发布：单片机定时器流程图编辑：程序博客网时间：2024/06/15 12:25
毋庸多说，事务是个很重要的概念。必须拿下！
事务是一个逻辑的、原子的工作单元，可以将多个SQL语句作为一个整体提交给数据库。
如果事务单元中的任何一个SQL语句出现操作异常，可以对事务工作单元内所有的更改进行撤销。
事务可以确保数据的完整性和一致性，避免部分操作可能导致的数据紊乱。
事务具有ACID（Atomicity-原子性，Consistency-一致性，Isolation-隔离性，Durability-持久性）属性。
system_pd@ORCL> -- 查询事务信息system_pd@ORCL> SELECT XID     as "事务ID",  2         XIDUSN  as "UNDO",  3         XIDSLOT as "事务槽",  4         XIDSQN  as "seq",  5         STATUS  as "事务状态"  6    FROM v$transaction;未选定行system_pd@ORCL> --执行一条DML语句system_pd@ORCL> UPDATE scott.emp SET sal=1.21 WHERE empno=5093;更新了sal字段已更新 1 行。system_pd@ORCL> -- 再次查询事务信息system_pd@ORCL> SELECT XID     as "事务ID",  2         XIDUSN  as "UNDO",  3         XIDSLOT as "事务槽",  4         XIDSQN  as "seq",  5         STATUS  as "事务状态"  6    FROM v$transaction;事务ID                 UNDO     事务槽        seq 事务状态---------------- ---------- ---------- ---------- --------------------------------09001B00FC080000          9         27       2300 ACTIVEsystem_pd@ORCL> -- 再次查询事务信息system_pd@ORCL> SELECT XID     as "事务ID",  2         XIDUSN  as "UNDO",  3         XIDSLOT as "事务槽",  4         XIDSQN  as "seq",  5         STATUS  as "事务状态"  6    FROM v$transaction;事务ID                 UNDO     事务槽        seq 事务状态---------------- ---------- ---------- ---------- --------------------------------09001B00FC080000          9         27       2300 ACTIVEsystem_pd@ORCL> --执行一条DQL语句system_pd@ORCL> select * from scott.emp where empno=5093;     EMPNO ENAME                                    JOB                       MGR HIREDATE---------- ---------------------------------------- ------------------ ---------- --------------       SAL       COMM     DEPTNO---------- ---------- ----------      5093 双双                                     SR.CONT                  8006 03-8月 -15      1.21       2017         20system_pd@ORCL> -- 再次查询事务信息system_pd@ORCL> SELECT XID     as "事务ID",  2         XIDUSN  as "UNDO",  3         XIDSLOT as "事务槽",  4         XIDSQN  as "seq",  5         STATUS  as "事务状态"  6    FROM v$transaction;事务ID                 UNDO     事务槽        seq 事务状态---------------- ---------- ---------- ---------- --------------------------------09001B00FC080000          9         27       2300 ACTIVEsystem_pd@ORCL> --提交数据库system_pd@ORCL> COMMIT WORK;提交完成。system_pd@ORCL> -- 再次查询事务信息system_pd@ORCL> SELECT XID     as "事务ID",  2         XIDUSN  as "UNDO",  3         XIDSLOT as "事务槽",  4         XIDSQN  as "seq",  5         STATUS  as "事务状态"  6    FROM v$transaction;未选定行system_pd@ORCL> desc v$transaction 名称                                                  是否为空? 类型 ----------------------------------------------------- -------- ------------------------------------ ADDR                                                           RAW(8) XIDUSN                                                         NUMBER XIDSLOT                                                        NUMBER XIDSQN                                                         NUMBER UBAFIL                                                         NUMBER UBABLK                                                         NUMBER UBASQN                                                         NUMBER UBAREC                                                         NUMBER STATUS                                                         VARCHAR2(16) START_TIME                                                     VARCHAR2(20) START_SCNB                                                     NUMBER START_SCNW                                                     NUMBER START_UEXT                                                     NUMBER START_UBAFIL                                                   NUMBER START_UBABLK                                                   NUMBER START_UBASQN                                                   NUMBER START_UBAREC                                                   NUMBER SES_ADDR                                                       RAW(8) FLAG                                                           NUMBER SPACE                                                          VARCHAR2(3) RECURSIVE                                                      VARCHAR2(3) NOUNDO                                                         VARCHAR2(3) PTX                                                            VARCHAR2(3) NAME                                                           VARCHAR2(256) PRV_XIDUSN                                                     NUMBER PRV_XIDSLT                                                     NUMBER PRV_XIDSQN                                                     NUMBER PTX_XIDUSN                                                     NUMBER PTX_XIDSLT                                                     NUMBER PTX_XIDSQN                                                     NUMBER DSCN-B                                                         NUMBER DSCN-W                                                         NUMBER USED_UBLK                                                      NUMBER USED_UREC                                                      NUMBER LOG_IO                                                         NUMBER PHY_IO                                                         NUMBER CR_GET                                                         NUMBER CR_CHANGE                                                      NUMBER START_DATE                                                     DATE DSCN_BASE                                                      NUMBER DSCN_WRAP                                                      NUMBER START_SCN                                                      NUMBER DEPENDENT_SCN                                                  NUMBER XID                                                            RAW(8) PRV_XID                                                        RAW(8) PTX_XID                                                        RAW(8)system_pd@ORCL> --18.1.3 使用ROLLBACK回滚事务system_pd@ORCL> --回滚是一个物理操作（逆向执行在UNDO表空间中的写入的数据，并且释放所有的锁）system_pd@ORCL> --删除员工数据system_pd@ORCL> DELETE FROM emp WHERE empno=5093;DELETE FROM emp WHERE empno=5093            *第 1 行出现错误:ORA-00942: 表或视图不存在system_pd@ORCL> --18.1.3 使用ROLLBACK回滚事务system_pd@ORCL> --回滚是一个物理操作（逆向执行在UNDO表空间中的写入的数据，并且释放所有的锁）system_pd@ORCL> --删除员工数据system_pd@ORCL> DELETE FROM scott.emp WHERE empno=7369;删除了一条记录已删除 1 行。system_pd@ORCL> ROLLBACK  2  ;回退已完成。system_pd@ORCL> SELECT COUNT(*) FROM emp WHERE empno=7369;SELECT COUNT(*) FROM emp WHERE empno=7369                     *第 1 行出现错误:ORA-00942: 表或视图不存在system_pd@ORCL> SELECT COUNT(*) FROM scott.emp WHERE empno=7369;  COUNT(*)----------         1system_pd@ORCL> spool out
0 0