8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | 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
- Full Reselect
- Selective Update
- Timestamp
- Update Counter
- Checksum
- ORA_ROWSCN (10g)
- Summary
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...