锁如何在提交后继续存在,或者如何将对锁定表的更改传播到另一个会话而不提交和丢失锁

下面是一个使用 AUTONOMOUS_TRANSACTION

drop table EX_EMPLOYEE
/
drop table EX_EMPLOYEE1
/
create table EX_EMPLOYEE
(id number(4) null)
/
create table EX_EMPLOYEE1
(id number(4) null)
/
insert into EX_EMPLOYEE (id) values(1);
/
insert into EX_EMPLOYEE1 (id) values(1);
/
commit
/
create or replace procedure P_TEST
as
  begin
  DELETE FROM EX_EMPLOYEE WHERE ID =1;
  P_TEST1();

end;
/

create or replace procedure P_TEST1
as
  PRAGMA AUTONOMOUS_TRANSACTION;
begin
    DELETE FROM EX_EMPLOYEE1 WHERE ID =1;
commit;
end;
/

然后执行程序

begin
P_TEST();
end;
/

select * from ex_employee where id =1;
select * from ex_employee1 where id =1;

您还可以从数据库中检查表是否仍处于锁定状态:

SELECT * FROM V$LOCKED_OBJECT L
INNER JOIN USER_OBJECTS  U ON(L.OBJECT_ID=U.OBJECT_ID);

不要忘记在测试之后提交或回滚以释放锁。