Avoiding Lost Updates with ORA_ROWSCN

Applications today use “optimistic locking” to avoid lost updates. Tom Kyte thought of using ORA_ROWSCN for optimistic locking, then decided it didn’t work (see ORA_ROWSCN for optimistic locking). I now think it does!

If you don’t understand what an SCN is, and how it helps with read consistency, this post may go over your head. Sorry I can’t take the time to try explaining all that here…

Test Data and Use Case

By default, Oracle keeps one ORA_ROWSCN per block. However, we can use ROWDEPENDENCIES when creating the table to make room for one SCN per row.

I’m going to create the venerable EMP table with ROWDEPENDENCIES:

CREATE TABLE EMP (
    EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(2)
) rowdependencies;

INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
commit;

select distinct ora_rowscn from emp;

ORA_ROWSCN
13848205

 

In honor of Oracle’s 40-year birthday, SMITH is going to submit a batch job to give everyone a 10% raise, including himself. At the same time, MILLER is doing a reorganization: he assigns SMITH to department 30, using an OLTP application that updates both department number and salary.

Lost Updates

Here is what happens when an application neglects “optimistic locking”:

BATCH	MILLER
– A1: SMITH submits a batch job for 10% raises update emp set sal = sal * 1.1;
	– A2: MILLER queries SMITH and sees committed data select empno, sal, deptno from emp where ename = ‘SMITH’; EMPNO SAL DEPTNO 7369 800 20
	– A3: MILLER reassigns SMITH: update waits for batch job to commit update emp set sal = 800, deptno = 30 where empno = 7369;
– A4: the batch job commits commit;	-The application sees “1 rows updated.”
	– A5: MILLER commits commit;
– A6: SMITH discovers he was the only one who didn’t get a raise! select empno, sal, deptno from emp where ename = ‘SMITH’; EMPNO SAL DEPTNO 7369 800 30

 

You see what happened here? Oracle told MILLER that SMITH’s salary was 800, because that was the latest committed value. The batch update had not committed, so the new salary was invisible to any session but the batch session itself. Since the OLTP application updated the salary as well, it replaced the new salary with the old one.

For details on lost updates, see the documentation on Data Concurrency and Consistency.

Optimistic Locking using column values

To avoid lost updates, the application has to check during its update that someone else has not changed the data in the meantime.

BATCH	MILLER
– B1: SMITH submits a batch job for 10% raises update emp set sal = sal * 1.1;
	– B2: MILLER queries SMITH and sees committed data select empno, sal, deptno from emp where ename = ‘SMITH’; EMPNO SAL DEPTNO 7369 800 20
	– B3: MILLER reassigns SMITH: update waits for batch job to commit update emp set sal = 800, deptno = 30 where empno = 7369 and sal = 800 and deptno = 20;
– B4: the batch job commits commit;	-The application sees “0 rows updated.” and explains to MILLER that someone else already changed the data.
– B5: SMITH got his raise! select empno, sal, deptno from emp where ename = ‘SMITH’; EMPNO SAL DEPTNO 7369 880 20

 

You may be wondering why Step B3 didn’t find the row it wanted to update. That is because of “restartability”:

• Oracle did a “consistent get” to get the latest committed values, where SAL was 800
• but then it did a “current get” to apply MILLER’s change to the current version of the data. This is when it blocked, waiting for the batch job to commit and release the lock on that row.
• When the “current get” finally happened, Oracle noticed that the SAL value had changed, so it restarted the update: it did another consistent get, at which time it found no rows that matched the WHERE clause – so it did not do the update.

For a detailed explanation of restartability, see these posts by Tom Kyte:

http://asktom.oracle.com/Misc/something-different-part-i-of-iii.html

http://asktom.oracle.com/Misc/part-ii-seeing-restart.html

http://asktom.oracle.com/Misc/part-iii-why-is-restart-important-to.html

Why not use ORA_ROWSCN?

Tom discovered that putting ORA_ROWSCN in the WHERE clause did not trigger a restart. In our scenario:

BATCH	MILLER
– C1: SMITH submits a batch job for 10% raises update emp set sal = sal * 1.1;
	– C2: MILLER queries SMITH and sees committed data select empno, sal, deptno, ora_rowscn from emp where ename = ‘SMITH’; EMPNO SAL DEPTNO ORA_ROWSCN 7369 800 20 13857633
	– C3: MILLER reassigns SMITH: update waits for batch job to commit update emp set sal = 800, deptno = 30 where empno = 7369 and ora_rowscn = 13857633;
– C4: the batch job commits commit;	-The application sees “1 rows updated.”

 

No reason to go any further: the update did not see that the batch job had changed the ORA_ROWSCN.

Making ORA_ROWSCN work!

Here is what I stumbled on: in the WHERE clause, referring to ORA_ROWSCN will not trigger a restart, but referring to SAL, which changed, will trigger a restart – and once the restart is triggered, then the new “consistent get” will see the new value of ORA_ROWSCN. We don’t need the old value of SAL, we just need to mention it. For example:

BATCH	MILLER
– D1: SMITH submits a batch job for 10% raises update emp set sal = sal * 1.1;
	– D2: MILLER queries SMITH and sees committed data select empno, sal, deptno, ora_rowscn from emp where ename = ‘SMITH’; EMPNO SAL DEPTNO ORA_ROWSCN 7369 800 20 13865770
	– D3: MILLER reassigns SMITH: update waits for batch job to commit update emp set sal = 800, deptno = 30 where empno = 7369 and ora_rowscn = 13865770 and coalesce(‘*’,sal||deptno) is not null;
– D4: the batch job commits commit;	-The application sees “0 rows updated.” and explains to MILLER that someone else already changed the data.

 

I decided to use COALESCE to refer to the column values because it uses short-circuit evaluation. Once it determines that ‘*’ is not null, it doesn’t bother evaluating the concatenation of the columns. Also, concatenation implicitly converts every datatype to VARCHAR2 so I can list strings, dates, timestamps, numbers and intervals without worry.

Conclusion

ORA_ROWSCN can indeed be used as a “version number” that can be checked to avoid lost updates. The UPDATE statement just has to list the appropriate columns in the WHERE clause, but it doesn’t have to check them against values from a prior SELECT statement. The only real value that needs to be checked is ORA_ROWSCN.

There are some limitations and caveats here:

1. If your table has not been created with ROWDEPENDENCIES, ORA_ROWSCN will change every time any row in the block has changed. The table has to be “recreated” with ROWDEPENDENCIES for this to work.
2. You cannot use this method with Index Organized Tables! If you create such a table with ROWDEPENDENCIES, then SELECT ORA_ROWSCN… will return NULL.
3. You might wonder whether a “delayed block cleanout” will cause the ORA_ROWSCN to change without the data having changed. I don’t see how that could be a problem, since the SELECT done to get the data (and the ORA_ROWSCN) will do the cleanout as part of its processing.