Analysis of the timing of locking various locked cursors in the database

This article mainly explains "the timing analysis of various locked cursors in the database". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. let's study and learn the timing analysis of all kinds of locked cursors in the database.

I created a table and generated a row of data:

Create table plch_one_row (id number); insert into plch_one_row values (1); commit

Then I set up a process to check whether the row of data in my table is locked. The method I use is to try to lock the line in a process with autonomous transactions.

CREATE OR REPLACE PROCEDURE plch_check_lockAS 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;/

Which of the following options can be used to replace the / * code * / comment in the following block so that "Not locked" is displayed after execution? You can assume that there is no lock on the table before execution.

BEGIN / * code * / plch_check_lock;END;/

(A)

Begin for rec in (select 1 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 from plch_one_row for update 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 lockedPL/SQL procedure successfully completedSQL >

(B)

Declare cursor cur is select 1 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 / LockedPL/SQL procedure successfully completedSQL >

(C)

Declare cursor cur is select 1 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 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 lockedPL/SQL procedure successfully completedSQL >

(D)

Begin savepoint before_loop; for rec in (select 1 for rec in 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 from plch_one_row for update 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 lockedPL/SQL procedure successfully completedSQL >

Answer ACD

(a) correct: if you use a hidden cursor loop, the lock will be released when an exception occurs

(B) incorrect, if an explicit cursor loop is used, the lock will not be released when an exception occurs

(C) correct: the exception is caught and explicitly rolled back to SAVE POINT, so the lock is released.

(d) correct: like A, a rollback in exception handling is equivalent to doing nothing.

Thank you for your reading. the above is the content of "the timing analysis of various locked cursors in the database". After the study of this article, I believe you have a deeper understanding of the timing analysis of various locked cursors in the database, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!