Source: http://yangtingkun.itpub.net/post/468/462908
看到yangtingkun提到用VPD实现读锁,感觉很有意思,尝试了下--
-- create test table
create table test_read_lock(name varchar2(10));
-- create policy function
create or replace function read_lock_policy(OBJECT_SCHEMA IN VARCHAR2, OBJECT_NAME IN VARCHAR2)
return varchar2
as
l_obj_id pls_integer;
l_lock_return pls_integer;
begin
select object_id into l_obj_id
from user_objects
where object_name = 'TEST_READ_LOCK';
l_lock_return := dbms_lock.request(l_obj_id, 6, 60, true);
return l_lock_return || ' in (0, 4)';
end;
-- Add policy
exec dbms_rls.drop_policy(user, 'TEST_READ_LOCK', 'my_test_policy');
exec dbms_rls.ADD_POLICY(user, 'TEST_READ_LOCK', 'my_test_policy', user, 'READ_LOCK_POLICY');
-- Test
-- Session 1
select * from TEST_READ_LOCK;
-- Session 2
select * from TEST_READ_LOCK;
hang until sesion 1 commit or rollback