接上一篇。
③ 堤防DDL提交事务
DDL伪代码展示:
begin
commit;
DDL-statement
commit;
Exception
when others
then rollback;
end;
因此,DDL总会提交,即使提交不成功也会如此。DDL一开始就提交,一定要知道这一点。它首先提交,因此如果必须回滚,它不会回滚你的事务。如果执行了DDL,它会使你所执行的所有未执行的工作成为永久性的,即使DDL不成功也会如此。
测试如下:
-------session-A-----------
hr@ORCL> drop table t purge;
Table dropped.
hr@ORCL> create table t as select * from user_objects;
Table created.
hr@ORCL> select count(*) from t;
COUNT(*)
----------
42
hr@ORCL> delete from t;
42 rows deleted.
hr@ORCL> create index idx_t on t(object_id);
Index created.
-------session_B----------------
hr@ORCL> select count(*) from t;
COUNT(*)
----------
0
--发现t表记录为0,说明已经提交了。
hr@ORCL> insert into t select * from user_objects;
43 rows created.
hr@ORCL> create index idx_t on t(object_think);
create index idx_t on t(object_think)
*
ERROR at line 1:
ORA-00904: "OBJECT_THINK": invalid identifier
-------session_C----------------
hr@ORCL> select count(*) from t;
COUNT(*)
----------
43
在一个事务里,为了保证事务的原子性,DDL要慎重!!
④ 关注空格
这个错误其实很容易发生,虽然简单,但还是希望能引起共鸣!
测试:
hr@ORCL> create table t (n varchar2(10));
Table created.
hr@ORCL> insert into t values('ab ');
1 row created.
hr@ORCL> commit;
Commit complete.
hr@ORCL> select * from t where n='ab';
no rows selected
hr@ORCL> update t set n=trim(n);
1 row updated.
hr@ORCL> select * from t where n='ab';
N
----------
ab