Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Stateless Locking Methods in Oracle

Oracle has excellent locking and transaction support in a client-server environment where connections to the database remain open at all times. Like all other RDBMS products locking issues can arise when Oracle is used in a stateless environment such as Web applications. In this article I shall discuss some of the many solutions to these problems.

The Problem

Most applications involve data being queried, manipulated by a user, then posted back to the database. In a stateful environment the constant connection means that rows can be locked during the manipulation to prevent data loss. In a stateless environment the database connections are closed between the retrieval of the data and the final update. During this time other users may be manipulting the data, leaving the data you are making your decisions on incorrect. Here is a simple example.

Two users are tasked with making changes to the salary of employees within the company using the following procedure.

CREATE OR REPLACE PROCEDURE UPDATE_SALARY (p_empno    IN  emp.empno%TYPE,
                                           p_new_sal  IN  emp.sal%TYPE) AS
  v_rowid    ROWID;
BEGIN
  UPDATE emp
  SET    sal   = p_new_sal
  WHERE  empno = p_empno;
  COMMIT;
END;
/

The first user is to add a £100 pay rise to the existing salary, the second is to add a £50 bonus to some employees. Both users happen to query the same record from the EMP table at approximately the same time using the following query.

SELECT sal
FROM   emp
WHERE  empno = 7876;

The resulting salary of £1100 is returned to both users. The first user then alters the row as follows.

BEGIN
  UPDATE_SALARY(7876, 1200);
END;
/

The second user then alters the row in a similar way.

BEGIN
  UPDATE_SALARY(7876, 1150);
END;
/

Both users have performed the task they were asked to do but one employee has lost out on a £100 pay rise.

Full Reselect

The Full Reselect is one solution to this problem. In this method the row is reselected and locked before the update to prevent changes being made. The old salary is used in the select to make sure the data hasn't been altered since it was initially retrieved.

CREATE OR REPLACE PROCEDURE UPDATE_SALARY (p_empno    IN  emp.empno%TYPE,
                                           p_old_sal  IN  emp.sal%TYPE,
                                           p_new_sal  IN  emp.sal%TYPE) AS
  v_rowid    ROWID;
BEGIN
  SELECT rowid
  INTO   v_rowid
  FROM   emp
  WHERE  empno = p_empno
  AND    sal   = p_old_sal
  FOR UPDATE;

  UPDATE emp
  SET    sal   = p_new_sal
  WHERE  rowid = v_rowid;
  COMMIT;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    ROLLBACK;
    RAISE_APPLICATION_ERROR(-20000, 'Data has changed since you retrieved it.');
END;
/

This can then be called as follows.

BEGIN
  UPDATE_SALARY(7876, 1100, 1200);
END;
/

Multiple calls will highlight that the data has changed and generate an error. The use of ROWID is for performance reasons. It's perfectly valid to update using the primary key value again.

Selective Update

The Selective Update is similar to the last example but it does not involve an explicit select to lock the row. Instead the update includes predicates to check the original values have not been changed. If no rows are updated the data must have been changed.

CREATE OR REPLACE PROCEDURE UPDATE_SALARY (p_empno    IN  emp.empno%TYPE,
                                           p_old_sal  IN  emp.sal%TYPE,
                                           p_new_sal  IN  emp.sal%TYPE) AS
BEGIN
  UPDATE emp
  SET    sal   = p_new_sal
  WHERE  empno = p_empno
  AND    sal   = p_old_sal;
  
  IF SQL%ROWCOUNT = 0 THEN
    RAISE_APPLICATION_ERROR(-20000, 'Data has changed since you retrieved it.');
  END IF;
  COMMIT;
END;
/

This is a more elegant solution but may involve many WHERE predicates if many columns can be updated by a particular web form.

Timestamp

An alternative to the above solutions is to use a timestamp to mark the last update to a row. The timestamp is returned when data is retrieved then used during the update.

ALTER TABLE emp ADD (last_update TIMESTAMP DEFAULT SYSTIMESTAMP);
UPDATE emp
SET    last_update = SYSTIMESTAMP;
COMMIT;

The column can then be used as follows.

CREATE OR REPLACE PROCEDURE UPDATE_SALARY (p_empno          IN  emp.empno%TYPE,
                                           p_old_timestamp  IN  emp.last_update%TYPE,
                                           p_new_sal        IN  emp.sal%TYPE) AS
BEGIN
  UPDATE emp
  SET    sal         = p_new_sal,
         last_update = SYSTIMESTAMP
  WHERE  empno       = p_empno
  AND    last_update = p_old_timestamp;
  
  IF SQL%ROWCOUNT = 0 THEN
    RAISE_APPLICATION_ERROR(-20000, 'Data has changed since you retrieved it.');
  END IF;
  COMMIT;
END;
/

Remember, the accuracy of this method is only as good as the granularity of the timestamp. In exceptional circumstances data could be lost if both users perform an update during the same timestamp period.

Update Counter

Using an update counter is similar to using a timestamp but with no granularity issue. The update counter is returned when data is retrieved then used during the update.

ALTER TABLE emp ADD (last_update NUMBER(10) DEFAULT 0);
UPDATE emp
SET    last_update = 0;
COMMIT;

The column can then be used as follows.

CREATE OR REPLACE PROCEDURE UPDATE_SALARY (p_empno            IN  emp.empno%TYPE,
                                           p_old_last_update  IN  emp.last_update%TYPE,
                                           p_new_sal          IN  emp.sal%TYPE) AS
BEGIN
  UPDATE emp
  SET    sal         = p_new_sal,
         last_update = last_update + 1
  WHERE  empno       = p_empno
  AND    last_update = p_old_last_update;
  
  IF SQL%ROWCOUNT = 0 THEN
    RAISE_APPLICATION_ERROR(-20000, 'Data has changed since you retrieved it.');
  END IF;
  COMMIT;
END;
/

Checksum

An alternative approach is to calculate a checksum from the contents of the row, using the OWA_OPT_LOCK package, when selecting the data. The checksum can then be recalculted and checked before performing any updates. First we get the original checksum.

SET SERVEROUTPUT ON
DECLARE
  v_rowid  ROWID;
BEGIN
  SELECT rowid
  INTO   v_rowid
  FROM   emp
  WHERE  empno = 7876;
  DBMS_OUTPUT.PUT_LINE(owa_opt_lock.checksum('SCOTT','EMP', v_rowid));
END;
/

When performing the update we check that the checksum has not changed. If it has we know the data has changed so we cannot proceed.

CREATE OR REPLACE PROCEDURE UPDATE_SALARY (p_empno         IN  emp.empno%TYPE,
                                           p_old_checksum  IN  NUMBER,
                                           p_new_sal       IN  emp.sal%TYPE) AS
  v_rowid         ROWID;
  v_new_checksum  NUMBER;
BEGIN
  SELECT rowid
  INTO   v_rowid
  FROM   emp
  WHERE  empno = p_empno
  FOR UPDATE;

  v_new_checksum := owa_opt_lock.checksum('SCOTT','EMP', v_rowid);
  
  IF v_new_checksum = p_old_checksum THEN
    UPDATE emp
    SET    sal         = p_new_sal
    WHERE  rowid       = v_rowid;
    COMMIT;
  ELSE
    ROLLBACK;
    RAISE_APPLICATION_ERROR(-20000, 'Data has changed since you retrieved it.');
  END IF; 
END;
/

This negates the need for any extra columns but increases the amount of code necessary since the CHECKSUM function cannot be used as part of the SQL statement.

ORA_ROWSCN (10g)

Oracle 10g includes a new pseudocolumn called ORA_ROWSCN, that gives the SCN associated with the last row modification. By default, the SCN returned is actually block-specific, in that it reflects the SCN of the last changed row in the block, but if the base table is created with ROWDEPENDENCIES the SCN returned by ORA_ROWSCN is row-specific.

Using this method, the ORA_ROWSCN would be returned along with the the data from the initial query. It is then passed along with the modified data in order to check for changes to the SCN since the initial query.

CREATE OR REPLACE PROCEDURE UPDATE_SALARY (p_empno    IN  emp.empno%TYPE,
                                           p_scn      IN  NUMBER,
                                           p_new_sal  IN  emp.sal%TYPE) AS
BEGIN
  UPDATE emp
  SET    sal         = p_new_sal
  WHERE  empno       = p_empno
  AND    ORA_ROWSCN  = p_scn;
  
  IF SQL%ROWCOUNT = 0 THEN
    RAISE_APPLICATION_ERROR(-20000, 'Data has changed since you retrieved it.');
  END IF;
  COMMIT;
END;
/

Summary

There are a vast number of possible solutions to this problem and it is up to you as a DBA/developer to decide which one gives you the required security and fits in with your programming model. It's worth taking matters into your own hands rather than rely on specific technologies to deal with locking issues since this years best practice programming model may not be used next year.

Hope this helps. Regards Tim...

Back to the Top.