A Locking Mechanism in Oracle 10g for Web Applications

Introduction

Locking issue in every concurrent environment is a significant issue to be decided at the very beginning of a system design. Obviously one of the most concurrency demanding architecture of today is web. Like many other concurrent applications, Oracle web applications may also be designed based on two locking scheme:

1. Pessimistic Locking
2. Optimistic Locking

You may refer to the best article I have ever seen about Oracle locking http://orafaq.com/papers/locking.pdf. In this article you will find the fundamentals of locking and a trigger based implementation of optimistic locking. What makes me annoying about that technique is the use of triggers. You may respond from any DBA or developer that triggers are really damn in real environments. In this post you will find a PL/SQL based implantation of optimistic locking using a feature ROWDEPENDENCIES introduced by Oracle 10g.

Comparison of two scheme

	Classical Optimistic Locking	Pessimistic Locking	Description
Ease of implementation	Harder	Easier	In optimistic locking developer needs to implement a way of performing locking but in pessimistic locking learning how to use FOR UPDATE is enough.
Cost of storage	More expensive	Cheaper	All tables to be protected via lock requires a version (change history) column which is usually a timestamp for each table row.
Code complexity	Usually more complex	Usually easier to read and shorter	For optimistic locking each DML operation requires a control. In pessimistic locking once go just as you are in an isolated environment.
Persistent physical database connection is required	No	Yes	In optimistic locking we simulate the physical connection as a logical one but in pessimistic locking we need to keep database connection opened. This is the fundamental reason making optimistic locking suitable for 3-tier architecture.
Mostly suitable for	3-tier architecture	2-tier architecture	Since in 3-tier architecture the thread in the application server side is always subject to change, you need to store database connection somewhere in the application server(session beans or so), which may be very costly. But for applications communicating directly with database have no such need. You may open a single connection and perform all your transactions over a single connection.
Death-lock/Starvation possibility	Lower	Higher	Since you lock the table rows in pessimistic locking. In case that application server and Oracle can’t agree on connections, the lock over some will exceed although the user close its session a while ago. Or due to in appropriate order of lock accusation, sessions may fall into death-lock more easily in pessimistic locking.
Trigger based implementation	Yes	Not required	As you all may know trigger base development is an old and proven to be dangerous in many cases. Ask a developer or DBA around you…
Addition of a column for version control	Yes	No	Optimistic lock is based on version controlling so you need somewhere to keep version of each row.

   
 

A brief introduction to ORA_ROWSCN

By 10g, Oracle introduce a new option ROWDEPENDECIES for tables (heap, index organized, etc.) and for clusters (B*Tree or Hash). This option allows developer to track any DML change in row level for table or a table of cluster. Lack of this option is told to be NOROWDEPENDENCIES. With this option you can track changes in only block level for any table of cluster. In other words with NOROWDEPENDENCIES option the change value you will see is an upper bound for any change within a block. To rephrase there can be no other changes performed at some time later than the block level change value. I now that it seems somewhat cumbersome. But let’s exemplify this:

NOROWDEPENDENCIES SAMPLE

SQL	Description
SQL> drop table emp purge;  SQL> create table emp norowdependencies as select * from employees;	Create EMP table with NOROWDEPENDENCIES option.
SQL> select distinct ora_rowscn from emp; ORA_ROWSCN ———- 3536806	Let’s check how many different version numbers exist within the EMP. ORA_ROWSCN pseudo column gives the version number of the row (indeed block in this case). That’s because all rows in table inserted by CTAS. So all SCN values are equal.
SQL> update emp set salary = 1.5*salary where employee_id=112; SQL> commit;	We update a single row.
SQL> select distinct ora_rowscn from emp; ORA_ROWSCN ———- 3536806 3536891	We recheck the number of distinct values and see that there are two distinct values. It is normal because we update the record 112.So it has to have a different value from other rows.
SQL> select count(*) from emp where ora_rowscn = 3536891;  90  SQL> select count(*) from emp where ora_rowscn = 3536806;  16	As we count the number of rows matching with these two different SCN values, we get a somewhat weird result. Although we only update a single row we see that there are 90 values sharing the same SCN with record 112. This is simply because the block in which record 112 resides contains 90 employee records. And SCN is kept in block level. Any DML will update block SCN.

   
 

ROWDEPENDENCIES SAMPLE

SQL	Description
SQL> drop table emp purge; SQL> create table emp rowdependencies as select * from employees;	Create EMP table with ROWDEPENDENCIES option this time.
SQL> select distinct ora_rowscn from emp; ORA_ROWSCN ———- 3539890	Let’s check how many different version numbers exist within the EMP. See that SCN values are equal because they are created by CTAS with in the same transaction.
SQL> update emp set salary =1.5* salary where employee_id=112; SQL> commit;	We update a single row.
SQL> select ora_rowscn, count(*) from emp group by ora_rowscn; ORA_ROWSCN COUNT(*) ———- ———- 3539924     1 3539890     105	As we count the number of rows matching with these two different SCN values, we see that update affects only the related row and change its version number. This is because ROWDEPENDENCIES option causes versioning to be kept in row level rather than block level.

   
 

Some properties of ORA_ROWSCN

Disappearing ORA_ROWSCN with TM Lock

SQL	Description
SQL> select ora_rowscn,         employee_id,         last_name from emp     where employee_id=112 for update;       ORA_ROWSCN EMPLOYEE_ID LAST_NAME ———- ———– ————————- 3540566     112         Urman	We select ORA_ROWSCN value of record 112 and see that its 3540566. By the way notice that we get a TM lock over table by locking the record.
SQL> select ora_rowscn,         employee_id,         last_name from emp     where employee_id=112;       ORA_ROWSCN EMPLOYEE_ID LAST_NAME ———- ———– ————————-             112         Urman	As we reselect the same row we see that ORA_ROWSCN value is NULL. Is it a bug? No, that’s because the current state of the row is fuzzy for the modifying session. Value of ORA_ROWSCN will be defined after a commit or rollback.
SQL>–Session 2 SQL> select ora_rowscn,         employee_id,         last_name from emp where employee_id=112;       ORA_ROWSCN EMPLOYEE_ID LAST_NAME ———- ———– ————————- 3540566     112         Urman	This time without committing or roll-backing the Session 1, we select the same row in another Session 2. And see that value is still 3540566. This is because of the fact that Oracle uses READ COMMITED isolation level by default.

   
 

SCN_TO_TIMESTAMP Function

A good function SCN_TO_TIMESTAMP is used to convert a SCN number to TIMESTAMP as you will guess. This timestamp defines the approximate time of the last transaction commit. This also shows that SCN number is not simply a constantly increasing number. It also carries information about transaction commit time.

SQL> select scn_to_timestamp (ora_rowscn), employee_id, last_name from EMP where employee_id=112;

SCN_TO_TIMESTAMP (ORA_ROWSCN)	EMPLOYEE_ID	LAST_NAME
28/07/2007 17:27:19, 000000000	112	Urman

 

SQL> update EMP set salary=1.2*salary where employee_id=112;

SQL> commit;

SQL> select scn_to_timestamp (ora_rowscn), employee_id, last_name from EMP where employee_id=112;

SCN_TO_TIMESTAMP (ORA_ROWSCN)	EMPLOYEE_ID	LAST_NAME
28/07/2007 17:29:31, 000000000	112	Urman

ORA_ROWSCN in Index Organized Tables (IOT)

SQL> create table EMP (    employee_id,

first_name,

last_name,salary,

constraint PK_EMP primary key (employee_id))

organization index rowdependencies as select employee_id, first_name, last_name, salary from employees;

SQL> select ora_rowscn, e.* from EMP e where rownum<=5;

ORA_ROWSCN	EMPLOYEE_ID	FIRST_NAME	LAST_NAME	SALARY
	100	Stephen	King	24000
	101	Neena	Kochhar	17000
	102	Lex	De Haan	17000
	103	Alexander	Hunold	9000
	104	Bruce	Ernst	16858,13

 

SQL> drop table EMP purge;

SQL> create table EMP (    employee_id,

first_name,

last_name,

salary,

constraint PK_EMP_ primary key (employee_id))

organization index as select employee_id, first_name, last_name, salary from employees;

SQL> select ora_rowscn, e.* from EMP e where rownum<=5;

ORA_ROWSCN	EMPLOYEE_ID	FIRST_NAME	LAST_NAME	SALARY
3541098	100	Stephen	King	24000
3541098	101	Neena	Kochhar	17000
3541098	102	Lex	De Haan	17000
3541098	103	Alexander	Hunold	9000
3541098	104	Bruce	Ernst	16858,13

 

You see the problem? For Index Organized Tables, ORA_ROWSCN is defined only when NOROWDEPENDENCIES option is used. Row level change tracking still requires addition of user defined columns.

OPTIMISTIC LOCKING SCHEME

After summarizing ROWDEPENDENCIES concept we can explain then optimistic locking scheme.

Description

The basis for locking mechanism described in this post is based on taking advantage of both optimistic and pessimistic locking. By using this mechanism we will be easily implement a locking mechanism without using an extra explicit column or trigger. We solve all problems in PL/SQL.

What do we need by locking?

By a locking mechanism we desired two things:

1. Has the record I desired to update changed by the last time I have seen it?

1. How can I ensure that it won’t change till I complete my update?

Here is the pseudo-code:

1. Begin
2. Lock the desired row in shared mode with NOWAIT option.
3. Compare the SCN value in hand
   1. If SCNs are matched the perform the update
   2. If not raise an exception indicating that the row is modified by some other session.
4. End

Lock the desired row in shared mode

As one talk about lock in shared mode, the very first thing comes into mind is the use of FOR UPDATE clause and you are right. Now lock your table’s desired row in shared mode.

SELECT ORA_ROWSCN INTO
<local parameter>
FROM
<table_name>
WHERE
<primary_key>=: x
FOR UPDATE NOWAIT

Now you are sure that nobody can modify this row till your session commits/rollbacks either by you or PMON (u know what that means. Right?)

Check whether record is modified or not

IF
<local parameter> = <last scn value you have seen>
THEN

    Update your row

ELSE

RAISE record_modified_excp;

END IF;

Update your row

Next step is to update your row. Since you are the only session that can modify the record since you previously lock it:

UPDATE
<table_name>
SET
<column_name> =: new_value
WHERE
<primary_key>=: x

Can you show me on some example?

Time Slot	Session 1	Session 2
T1	SQL> select ora_rowscn,first_name,last_name from emp where employee_id=112;   ORA_ROWSCN FIRST_NAME LAST_NAME ———- ——————– ————————- 3512833 Jose Manuel Urman	SQL> select ora_rowscn,first_name,last_name from emp where employee_id=112;       ORA_ROWSCN FIRST_NAME LAST_NAME ———- ——————– ————————- 3512833 Jose Manuel Urman
T2	SQL> begin 2 update_salary(112, 8000, 3512833); 3 commit; 4 exception 5 when others then 6 rollback; 7 raise; 8 end; 9 /
T3	SQL> select ora_rowscn,first_name,last_name,salary from emp where employee_id=112;       ORA_ROWSCN FIRST_NAME LAST_NAME SALARY ———- ——————– ————————- ———- 3513114 Jose Manuel Urman 8000	SQL> begin 2 update_salary(112, 8000, 3512833); 3 commit; 4 exception 5 when others then 6 rollback; 7 raise; 8 end; 9 / begin * ORA-20000: Record is modified ORA-06512: at line 7
T4		SQL> select ora_rowscn,first_name,last_name,salary from emp where employee_id=112;       ORA_ROWSCN FIRST_NAME LAST_NAME SALARY ———- ——————– ————————- ———- 3513114 Jose Manuel Urman 8000

   
 

Do we fail is table has created with NOROWDEPENDENCIES option?

Let’s see what will happen as we create our EMP table with NOROWDEPENDENCIES option. Session 1 is trying to update the salary of employee 113 to 8000$ and Session 2 is trying to update the salary of employee 114 to 120000$.

Time Slot	Session 1	Session 2
T1	SQL> select ora_rowscn,first_name,last_name,salary from emp where employee_id in (113,114);       ORA_ROWSCN FIRST_NAME LAST_NAME SALARY ———- ——————– ————————- ———- 3513479 Luis Popp 6900 3513479 Den Raphaely 11000	SQL> select ora_rowscn,first_name,last_name,salary from emp where employee_id in (113,114);       ORA_ROWSCN FIRST_NAME LAST_NAME SALARY ———- ——————– ————————- ———- 3513479 Luis Popp 6900 3513479 Den Raphaely 11000
T2	SQL> begin 2 update_salary(113, 8000, 3513479); 3 commit; 4 exception 5 when others then 6 rollback; 7 raise; 8 end; 9 /
T3		SQL> begin 2 update_salary(114, 120000, 3513479); 3 commit; 4 exception 5 when others then 6 rollback; 7 raise; 8 end; 9 / begin * ORA-20000: Record is modified ORA-06512: at line 7

   
 

Although two sessions request mutual exclusion over two different rows, session 2 fails to do so. Why? And is it erroneous? First of all, it doesn’t cause lost updates; we still acquire exclusion over the row. But more over this time we not only acquire exclusion in row level but also in block level. In other words, by updating the salary of employee 113, session 1 changes all SCN values of records within in the same block with employee 113(including record 114). As a result this causes a perception by session 2 as the record for employee 114 is updated by some other session. Isn’t that funny?

In a high concurrent environment, some hot blocks may cause this problem. Any change within the block will invalidate all records. This means an annoying sense over system users. You will display a “refresh your record” alert to your user although nothing changes about its record. The only change is at block level.

Conclusion

I have seen many applications just ignoring locking its locking mechanism. Ignoring locking schema will result in

• Information corruption(Not data corruption)
• Annoying users.

Either use pessimistic or optimistic locking; it is your architectural decision. But never be in a position answering the question “What is your locking mechanism for application X?” as

• “I don’t know”
• “We use no mechanism. We just update and allow lost updates”

Source Code Used in Examples

http://husnu.sensoy.googlepages.com/source.sql