Oracle cursor for updating every row in same table stop being so accommodating

lock_row function: /*--------------------------------------* * Function to lock a row in Oracle * *---------------------------------------*/ create or replace function lock_row(p_id in number) return boolean is resource_busy exception; pragma exception_init(resource_busy, -54); v_empno emp.empno%type; begin select empno into v_empno from emp where empno = p_id for update nowait; return true; exception when resource_busy then return false; end; /*-------------------------------------------------* * PL/SQL code that checks for concurrency conflict * *--------------------------------------------------*/ begin if lock_row(7900) then dbms_output.put_line('Row locked successfully'); -- Row lock is successful -- Proceed with the transaction else dbms_output.put_line('Unable to lock row'); -- Row lock attempt failed -- Do not proceed with the transaction end if; end; begin 2 if lock_row(7900) then 3 dbms_output.put_line('Row locked successfully'); 4 -- Row lock is successful 5 -- Proceed with the transaction 6 else 7 dbms_output.put_line('Unable to lock row'); 8 -- Row lock attempt failed 9 -- Do not proceed with the transaction 10 end if; 11 end; 12 / Row locked for update PL/SQL procedure successfully completed.begin 2 if lock_row(7900) then 3 dbms_output.put_line('Row locked successfully'); 4 -- Row lock is successful 5 -- Proceed with the transaction 6 else 7 dbms_output.put_line('Unable to lock row'); 8 -- Row lock attempt failed 9 -- Do not proceed with the transaction 10 end if; 11 end; 12 / Unable to lock row This article describes how to lock a row in Oracle, lists important features associated with row locking in Oracle, and demonstrates a sample implementation of acquiring a row lock before initiating a transaction to manage concurrency conflict.Here is a video showing a similar overview of using cursors in TSQL. -Steve Stedman Need help with SQL Server Performance Tuning, contact Steve at Stedman Solutions, LLC for help, or take a look at the performance tuning page at the Steve blog.

PL/SQL also conforms to the current ANSI/ISO SQL standard.

The set of rows the cursor holds is called the These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed.

They are also created when a SELECT statement that returns just one row is executed.

We’ll create a function that attempts a row lock NOWAIT and returns a BOOLEAN value to indicate success or failure.

The calling program can accordingly proceed with or abandon the transaction.

Leave a Reply