Systems Engineering and RDBMS

Using ORA_ROWSCN to implement optimistic concurrency

Posted by decipherinfosys on April 16, 2007

There are two ways that an n-tier application accesses the Oracle database:

1)    Stateless: Connections are held for a very brief duration of time.  Most of the web applications fall under this category, and
2)    Stateful: These hold the connections for a longer period of time.

Most of the applications use #1 nowadays so that the end user is consuming resources only when they are active in the database.  Using pessimistic locking requires a stateful connection to the database akin to the client/server connection.  In Oracle, one uses “select for update wait nnn” when using the pessimistic concurrency model.  In places where optimistic concurrency model (generally UI code) is required for locking mechanism, one can either chose to implement it using a number column for versioning or via using ORA_ROWSCN pseudo column (in Oracle 10g only).

In version 10g, using this pseudo column one does not need to lock a row with the select for update wait nnn option but one just needs to select ORA_ROWSCN which provides the SCN value of each individual row in a table and while modifying we need to make sure that ORA_ROWSCN for given row is same as when we read that row.  If no data found exception is raised at the time of the write operation, it means that someone else did the update prior to us doing it.  The application in that case needs to re-try or give a message to the user.  As you know, using optimistic concurrency, the first update wins.

One essential point to keep in mind is that by default, the tables will not get created with track row SCN’s option (the ora_rowscn will be the same for all rows within the same block), and also there is no ALTER TABLE command available to enable for row SCN tracking (atleast till 10g R2), so you need to create/re-create table(s) by using ROWDEPENDENCIES keyword in the CREATE TABLE command to use ORA_ROWSCN in place of select for update wait nnn.

Here is an example.

SQL> — SESSION – 1 @11-Apr-2007:13:58:09

SQL> — Create a table with ROWDEPENDENCIES option
SQL> — To keep data creation process simple we kept table definition same as SCOTT.EMP
SQL> CREATE TABLE EMP_Decipher_RowSCN_Demo
2         (empno number(6) constraint emp_decipher_rowscn_demo primary key,
3   ename varchar2(10),
4   job varchar2(9),
5   mgr number(4),
6   hiredate date,
7   sal number(7,2),
8   comm number(7,2),
9   deptno number(2))
10  ROWDEPENDENCIES
11  /

Table created.

SQL> — Copy some data from emp table
SQL> INSERT INTO EMP_Decipher_RowSCN_Demo SELECT * FROM emp WHERE empno < 10000
2  /

1313 rows created.

SQL> COMMIT
2  /

Commit complete.

SQL> col ORA_ROWSCN for 9999999999999
SQL> — select data from EMP_Decipher_RowSCN_Demo
SQL> SELECT empno, sal, ora_rowscn, to_char(sysdate,’DD-MM-YY:HH24:MI:SS’) date_time
2    FROM EMP_Decipher_RowSCN_Demo
3   WHERE empno >= 7900
4  /

EMPNO        SAL     ORA_ROWSCN DATE_TIME
———- ———- ————– —————————————————-
7900        950   267371584952 11-04-07:13:58:09
7902       3000   267371584952 11-04-07:13:58:09
7934       1300   267371584952 11-04-07:13:58:09

SQL>

SQL> — SESSION – 2 @11-Apr-2007:13:58:26
SQL> col ORA_ROWSCN for 9999999999999
SQL>
SQL> SELECT empno, sal, ora_rowscn, to_char(sysdate,’DD-MM-YY:HH24:MI:SS’) date_time
2    FROM EMP_Decipher_RowSCN_Demo
3   WHERE empno = 7900
4  /

EMPNO        SAL     ORA_ROWSCN DATE_TIME
———- ———- ————– —————————————————-
7900        950   267371584952 11-04-07:13:58:26

SQL> — Update EMP_Decipher_RowSCN_Demo data using ora_rowscn to make sure data is not modified by o
ther session
SQL> — If data is alrady modified ny other session, no records will get updated
SQL> UPDATE EMP_Decipher_RowSCN_Demo
2     SET sal = sal + 1000
3   WHERE empno = 7900
4     AND ora_rowscn = 267371584952
5  /

1 row updated.

SQL> COMMIT
2  /

Commit complete.

SQL> SELECT to_char(sysdate,’DD-MM-YY:HH24:MI:SS’) date_time
2    FROM dual
3  /

DATE_TIME
—————————————————————————
11-04-07:13:58:26

SQL>

SQL> — SESSION – 1 again @11-Apr-2007:13:58:29
SQL> SELECT to_char(sysdate,’DD-MM-YY:HH24:MI:SS’) date_time
2    FROM dual
3  /

DATE_TIME
—————————————————————————
11-04-07:13:58:29

SQL> — Update EMP_Decipher_RowSCN_Demo data using ora_rowscn to make sure data is not modified by other session
SQL> — If data is alrady modified ny other session, no records will get updated
SQL> UPDATE EMP_Decipher_RowSCN_Demo
2     SET sal = sal + 1000
3   WHERE empno = 7900
4     AND ora_rowscn = 267371584952
5  /

0 rows updated.
SQL> 0 rows updated.
SQL>
SQL> — As record has already changed after we read it, so now ORA_ROWSCN for the row
SQL> — will be different than the one we read, and hence no records get updated

SQL>
SQL> — Below is SQL to displays new ora_rowscn assigned to empno 7900
SQL> SELECT empno, sal, ora_rowscn, to_char(sysdate,’DD-MM-YY:HH24:MI:SS’) date_time
2    FROM EMP_Decipher_RowSCN_Demo
3   WHERE empno >= 7900
4  /

EMPNO        SAL     ORA_ROWSCN DATE_TIME
———- ———- ————– —————————————————-
7900       1950   267371587616 11-04-07:13:59:22
7902       3000   267371584952 11-04-07:13:59:22
7934       1300   267371584952 11-04-07:13:59:22

About these ads

Sorry, the comment form is closed at this time.

 
Follow

Get every new post delivered to your Inbox.

Join 80 other followers

%d bloggers like this: