Archive for August, 2012

Iterative UPDATE with SKIP LOCKED option

August 28, 2012

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.

Dynamic SQL with FOR UPDATE clause

August 28, 2012

Many times we can hear that the Native Dynamic SQL can not be implemented with FOR UPDATE clause in a PL/SQL program.

In this article I will proof that we can associate a dynamic SQL statement with a weak ref cursor variable using FOR UPDATE clause.

Let us consider the following – simple- case study:
We would like to modify the salary column of an employees like table
(This means that the structure of the table is identical with HR’s employees table):
We increase the salary for those employees whose salary is less than a particular value.

Let’s see the code:

CREATE OR REPLACE PROCEDURE din_sql_upd
(p_tname VARCHAR2,p_sal NUMBER)
IS
TYPE c_emp IS REF CURSOR;
c  c_emp;
r EMPLOYEES%ROWTYPE;
v_updated_rows INTEGER:=0;
BEGIN
OPEN c FOR 'SELECT * FROM '||p_tname||' WHERE salary < '
|| p_sal ||' FOR UPDATE';
LOOP
FETCH c INTO r;
EXIT WHEN C%NOTFOUND;  
DBMS_OUTPUT.PUT_LINE(RPAD(R.LAST_NAME,20)||' Salary: '||r.salary);
IF r.salary < p_sal THEN
EXECUTE IMMEDIATE
'UPDATE '||p_tname ||' SET salary =salary*2 WHERE employee_ID=:r '
USING r.employee_id;
v_updated_rows:=v_updated_rows+1;
END IF;
END LOOP;
CLOSE c;
DBMS_OUTPUT.PUT_LINE(CHR(10)||v_updated_rows||' modifications made.');
END din_sql_upd;
/
EXECUTE din_sql_upd('employees',2500)


You can observe that the UPDATE statement does not contain
the WHERE CURRENT OF clause, because it is PROHIBITED!
This program demonstrates one of a kind workaround for solving this problem:
We used simply the primary key instead of WHERE CURRENT OF.