这篇文章主要讲解了“数据库中各种带锁游标加锁的时机分析”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“数据库中各种带锁游标加锁的时机分析”吧！
我建立了一个表并生成一行数据：
create table plch_one_row (id number);
insert into plch_one_row values (1);
commit;然后我建立一个过程来检查我的表里这行数据是否被锁住。我用的方法是在一个带有自治事务的过程里试图对这行进行加锁。
CREATE OR REPLACE PROCEDURE plch_check_lock
AS
PRAGMA AUTONOMOUS_TRANSACTION;
resource_busy EXCEPTION;
PRAGMA EXCEPTION_INIT (resource_busy, -54);
l_id plch_one_row.id%TYPE;
BEGIN
SELECT id
INTO l_id
FROM plch_one_row
FOR UPDATE NOWAIT;
DBMS_OUTPUT.put_line ('Not locked');
COMMIT;
EXCEPTION
WHEN resource_busy
THEN
DBMS_OUTPUT.put_line ('Locked');
END;
/下列的选项中，哪些可以用来代替下面这个块中的注释，从而执行之后会显示"Not locked"? 你可以假定在执行之前表上没有锁。
BEGIN
plch_check_lock;
END;
/(A)
begin
for rec in (select 1/0 from plch_one_row for update) loop
null;
end loop;
exception
when zero_divide then
null;
end;SQL> BEGIN
2 begin
3 for rec in (select 1 / 0 from plch_one_row for update) loop
4 null;
5 end loop;
6 exception
7 when zero_divide then
8 null;
9 end;
10 plch_check_lock;
11 END;
12 /
Not locked
PL/SQL procedure successfully completed
SQL>(B)
declare
cursor cur is select 1/0 from plch_one_row for update;
begin
for rec in cur loop
null;
end loop;
exception
when zero_divide then
null;
end;SQL> BEGIN
2 declare
3 cursor cur is
4 select 1 / 0 from plch_one_row for update;
5 begin
6 for rec in cur loop
7 null;
8 end loop;
9 exception
10 when zero_divide then
11 null;
12 end;
13 plch_check_lock;
14 END;
15 /
Locked
PL/SQL procedure successfully completed
SQL>(C)
declare
cursor cur is select 1/0 from plch_one_row for update;
begin
savepoint before_loop;
for rec in cur loop
null;
end loop;
exception
when zero_divide then
rollback to before_loop;
end;SQL> BEGIN
2 declare
3 cursor cur is
4 select 1 / 0 from plch_one_row for update;
5 begin
6 savepoint before_loop;
7 for rec in cur loop
8 null;
9 end loop;
10 exception
11 when zero_divide then
12 rollback to before_loop;
13 end;
14 plch_check_lock;
15 END;
16 /
Not locked
PL/SQL procedure successfully completed
SQL>(D)
begin
savepoint before_loop;
for rec in (select 1/0 from plch_one_row for update) loop
null;
end loop;
exception
when zero_divide then
rollback to before_loop;
end;SQL> BEGIN
2 begin
3 savepoint before_loop;
4 for rec in (select 1 / 0 from plch_one_row for update) loop
5 null;
6 end loop;
7 exception
8 when zero_divide then
9 rollback to before_loop;
10 end;
11 plch_check_lock;
12 END;
13 /
Not locked
PL/SQL procedure successfully completed
SQL>答案ACD
(A)正确：如果用隐性游标循环，发生异常时锁会被释放
(B)不正确，如果用显性游标循环，发生异常时锁不会被释放
(C)正确：异常被捕获，显式回滚到SAVE POINT, 因而锁被释放。
(D)正确：同A, 异常处理里的回滚相当于什么也没做。
感谢各位的阅读，以上就是“数据库中各种带锁游标加锁的时机分析”的内容了，经过本文的学习后，相信大家对数据库中各种带锁游标加锁的时机分析这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！
