Iterative UPDATE with SKIP LOCKED option


Many times occur that we can not execute an UPDATE or DELETE statement, because a couple of rows are locked by an other session(s).
We can generate – and handle – exceptions, but this solution sometimes not enough.
Using the SKIP LOCKED clause – officially introduced in Oracle 11g- we can try to solve the problem incrementally.
Let’s suppose that other sessions sooner or later will finish their transactions. During their transactions we can modify the “free” (not locked) rows with this option.
Let’s see a simple case study:
Our goal is to increase all employees’s salary with 20% ,
however other session can prevent us from doing this modification.
First we create a table that is copy of employees owned by HR.
We modify the structure of the new table adding a column which will indicate whether the modification is done or not.
Initially the value of this column should ‘N’:
To check the timestamp of the modification we change the data type of hire_date from DATE to TIMESTAMP:

DROP TABLE emp4;
CREATE TABLE EMP4 AS SELECT * FROM EMPLOYEES;
ALTER TABLE EMP4 ADD MODIFIED CHAR(1) DEFAULT 'N';
ALTER TABLE EMP4 MODIFY hire_date TIMESTAMP;


We have two sessions:
In the first session we issue periodically the following statement:

UPDATE emp4 
set last_name=UPPER(LAST_NAME) WHERE SALARY >&SAL;


where Sal substitution variable should be 10000, 14000
For 10000 the result:
15 rows updated
This session has not issued the COMMIT statement!

In the second session execute the following PL/SQL program:

DECLARE
CURSOR c_emp is
SELECT * FROM  EMP4
WHERE modified ='N' FOR UPDATE SKIP LOCKED ;
r  emp4%ROWTYPE;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO r;
EXIT WHEN C_EMP%NOTFOUND;
r.hire_date:=SYSTIMESTAMP;
UPDATE EMP4 SET salary=salary*1.2, 
                 modified ='Y', hire_DATE=r.hire_date
WHERE CURRENT OF c_emp;
DBMS_OUTPUT.PUT_LINE
(r.employee_id||': '||r.last_name||' modified');
END LOOP;
DBMS_OUTPUT.PUT_LINE(CHR(10)||c_emp%ROWCOUNT||' modifications were');
CLOSE c_emp;
END;
/

The result :
92 modifications were
Issue a COMMIT in the first session and repeat the UPDATE with 14000
The result:
3 rows updated
Run again the PL/SQL program:
The result:
12 modifications were.
When the first session issues a COMMIT, after the second session finish it’s modification
The result:
1 row updated
It is not guaranteed that this method always successful.
We can use it, if the likelihood of the fact that other transactions will be finished on time, great enough.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: