如果应用允许最终用户生成主键/惟一列值,往往就会发生INSERT 阻塞。为避免这种情况,最容易的
做法是使用一个序列来生成主键/惟一列值。序列(sequence)设计为一种高度并发的方法,用在多用户环
境中生成惟一键。如果无法使用序列,那你可以使用以下技术,也就是使用手工锁来避免这个问题,这里
的手工锁通过内置的DBMS_LOCK 包来实现。
目的:防止两个(或更多)会话同时插入相同的值
会话1:
SQL> show user;
USER is "SYS"
SQL> grant execute on dbms_lock to scott;
Grant succeeded.
SQL> set echo on;
SQL> conn scott/scott
Connected.
SQL> create table demo ( x int primary key );
Table created.
SQL> create or replace trigger demo_bifer
2 before insert on demo
3 for each row
4 declare
5 l_lock_id number;
6 resource_busy exception;
7 pragma exception_init( resource_busy, -54 );
8 l_request number;
9 begin
10 l_lock_id :=
11 dbms_utility.get_hash_value( to_char( :new.x ), 0, 102400 );
12 l_request := ( dbms_lock.request
13 ( id => l_lock_id,
14 lockmode => dbms_lock.x_mode,
15 timeout => 0,
16 release_on_commit => TRUE ) );
17 if ( l_request not in (0,4) )
18 then
19 dbms_output.put_line( 'request = ' || l_request );
20 raise resource_busy;
21 end if;
22 end;
23 /
Trigger created.
SQL>
SQL> insert into demo(x) values (1);
1 row created.
会话2:插入相同值
SQL> conn scott/scott
Connected.
SQL> insert into demo(x) values (1);
insert into demo(x) values (1)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-06512: at "SCOTT.DEMO_BIFER", line 17
ORA-04088: error during execution of trigger 'SCOTT.DEMO_BIFER'
如果是会话1插入数据后COMMIT;会话2在执行插入相同值操作,是这个结果
SQL> conn scott/scott
Connected.
SQL> insert into scott.demo(x) values(1);
insert into scott.demo(x) values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C005992) violated
SQL>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15720542/viewspace-721839/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15720542/viewspace-721839/