采用防御式编程技术来构建真正可移植的数据库应用。
场景:为每一行生成一个唯一的主键,
scott@ORCL>create table id_table
2 (id_name varchar2(30) primary key,
3 id_value number);
表已创建。
scott@ORCL>insert into id_table values('MY_KEY',0);
已创建 1 行。
scott@ORCL>commit;
提交完成。
为了得到一个新的键,必须执行以下代码:
scott@ORCL>update id_table
2 set id_value=id_value+1
3 where id_name='MY_KEY';
已更新 1 行。
scott@ORCL>select id_value
2 from id_table
3 where id_name='MY_KEY';
ID_VALUE
----------
1
问题有:
1.一次只能有一个用户处理事务行,需要更新这一行来递增计数器,这会导致程序必须串行完成这个操作。
2.在oracle中,倘若隔离级别为serializable,除第一个用户外,视图并发完成此操作的其他用户都会接到这样一个错误:ORA-08117:can't serialize access for this transaction (无法串行访问这个事务)
scott@ORCL>set transaction isolation level serializable;
set transaction isolation level serializable
*
第 1 行出现错误:
ORA-01453: SET TRANSACTION 必须是事务处理的第一个语句
scott@ORCL>commit;
提交完成。
scott@ORCL>set transaction isolation level serializable;
事务处理集。
scott@ORCL>update id_table
2 set id_value=id_value+1
3 where id_name='MY_KEY';
已更新 1 行。
scott@ORCL>select id_value
2 from id_table
3 where id_name='MY_KEY';
ID_VALUE
----------
2
下面,再到另一个sql*plus会话中完成同样的操作,并发的请求唯一的ID:
scott@ORCL>set transaction isolation level serializable;
事务处理集。
scott@ORCL>update id_table
2 set id_value=id_value+1
3 where id_name='MY_KEY';
此时它会阻塞,因为一次只有一个事务可以更新这一行。这展示了第一个问题,即这个会话会阻塞,并等待该行提交。
由于我们使用的是oracle,而且隔离级别是serializable,提交第一个会话的事务时会观察到以下行为:
scott@ORCL>commit;
提交完成。
第二个会话会立即显示以下错误:
scott@ORCL>update id_table
2 set id_value=id_value+1
3 where id_name='MY_KEY';
update id_table
*
第 1 行出现错误:
ORA-08177: 无法连续访问此事务处理
对于这个问题,正确解法:
scott@ORCL>create table t
2 (pk number primary key,
3 name varchar2(50));
表已创建。
scott@ORCL>create sequence t_seq;
序列已创建。
scott@ORCL>create trigger t_trigger before insert on t for each row
2 begin
3 select t_seq.nextval into :new.pk from dual;
4 end;
5 /
触发器已创建
scott@ORCL>insert into t(name) values('yin');
已创建 1 行。
scott@ORCL>insert into t(name) values('xian');
已创建 1 行。
scott@ORCL>select * from t;
PK NAME
---------- --------------------------------------------------
1 yin
2 xian
其效果是为所插入的每一行自动地(而且透明地)指定一个唯一键。
还有一个更优的方法,完全没有触发器的开销(首选方法):
scott@ORCL>insert into t values(t_seq.nextval,'wangwang');
已创建 1 行。
scott@ORCL>insert into t values(t_seq.nextval,'qqq');
已创建 1 行。
scott@ORCL>select * from t;
PK NAME
---------- --------------------------------------------------
1 yin
2 xian
4 wangwang
6 qqq