Optimistic Locking with concurrency in Oracle

As database administrators are required to scale their databases to handle the challenges of Web-based
Access, B2B and e-commerce, faster hardware and more resources may only be a part of the solution.
Poor locking strategies can cripple even the most well resource-backed applications. Optimistic Locking
Is often over-looked by grouping TS and developers but may prove special useful for high transaction
Load databases such as those accessible by web-based clients. The following article attributes es the use
The Optimistic Locking with an Oracle database, and presents some battle-tested solutions for introducing
The approach into any Oracle-based development project.
The problems of pessimistic locking.
Most Oracle developers are already familiar with pessimistic locking-the technique by which the data
Be updated is locked in advance. This is achieved using the familiar select... For update syntax.
Once the data to be updated has been locked, the application can make the required changes, and then
Commit or rollback-during which the lock is automatically dropped.
If anyone else attempts to acquire a lock of the same data during this process, they will be forced to wait
Until the first transaction has completed.
This approach is called pessimistic because it assumes that another transaction might change the data
Between the read and the update. In order to prevent that change from happening-and the data
Inconsistency that wocould result-the read Statement locks the data to prevent any other transaction from
Changing it.
Whilst very simple and safe, this approach has two major problems ....
· The lockout-an application user selects a record for update, and then leaves for lunch
Finishing or aborting the transaction. All other users that need to update that record are forced to wait
Until the user returns and completes the transaction, or until the DBA kills the offending transaction
And releases the lock.
· The deadlock-users A and B are both updating the database at the same time. User A locks a record
And then attempt to acquire a lock held by user B-who is waiting to obtain a lock held by user.
Both transactions go into an infinite wait state-the so-called deadly embrace or deadlock.
If you think these scenarios are rare then just ask anyone who has administered a large RDB database. RDB
Only offers pessimistic locking, and even with well-designed applications, lockouts and deadlocks can be
Frequent.

Detailed lock tree hierarchies-through which developers are required to obtain locks in a pre-defined
Order can resolve failed of these problems, but this requires the database has ECT to identify all
Possible transactions a new system will be required to make and ensure that they are catered for in
Lock tree, as well as ensuring that all members of the development team are disciplined in following
Tree. Even if the database has ect correctly identifies all the transactions and the developers faithfully
Follow the lock tree, future releases of the software may add new transactions that cannot be easily
Integrated into the lock tree. Furthermore, Gui interfaces such as those used by web-based applications
Often offer users a variety of methods of navigating to the same result. mapping this user-driven
Complexity into a rigid lock tree can prove difficult.
Modern servers are capable of supporting hundreds if not thousands of simultaneous transactions which
Only increases the potential problems posed by pessimistic locking. The Oracle Parallel Server, which
Can dramatically improve throughput for very large databases by leveraging the benefits of clustered
Hardware systems can also be crippled by excessive locking as the distributed lock manager tries to coordinate
Each pessimistic lock amongst all of the instances in the database.
Introducing Optimistic Locking.
Optimistic Locking offers an elegant solution to the problems outlined above. Optimistic Locking does
Not lock records when they are read, and proceeds on the assumption that the data being updated has not
Changed since the read. Since no locks are taken out during the read, it doesn' t matter if the user goes
Lunch after starting a transaction, and deadlocks are all but eliminated since users shoshould never have
Wait on each other's locks.
The Oracle database uses Optimistic Locking by default. Any command that begins with Update... Set
That is not preceded by a select... For update is an example of Optimistic Locking.
However, whilst this is Optimistic Locking at work, it does not provide concurrency control. Concurrency
Control is the mechanic that ensures that the data being written back to the database is consistent
What was read from the database in the first place-that is no other transaction has updated the data
After it was read.
To demonstrate this, consider an example based on the familiar Scott/tiger Demonstration:
It's the end of the year and Scott has done well. King, his manager, decides to give him a well-earned
$300 pay rise. Meanwhile human resources are also using the employee system, giving everybody a 5%
Annual salary adjustment ....

Sal from e-mapreduce where e-mapreduce = 7788;
Sal
----------
3000
Human Resources read Scott's salary data, apply a 5% increase and commit the change:
SQL & gt; select Sal from EMP where empno = 7788;
Sal
----------
3000
SQL> Update EMP set sal = 3150 where empno = 7788;
1 row updated.
SQL> commit;
Commit complete.
King computes Scott's new salary based on his obsolete data and updates the record:
SQL> Update EMP set sal = 3300 where empno = 7788;
1 row updated.
SQL> commit;
Commit complete.
The change made by human resources has been wiped out:
SQL & gt; select Sal from EMP where empno = 7788;
Sal
----------
3300
Since the locking mechanism is optimistic, no locks are taken out during the database read. When King
Gets distracted and HR make their 5% adjustment, King has no way of knowing that his data has become
Obsolete. unaware of what human resources has done, King calculates Scott's new salary on his original

(And now inconsistent) data producing a result of $3300 which he commits back to the database wiping
Out the change made by HR. This is what is known as a "buried Update". instead of the $3450 a year
Scott was supposed to be making he now makes $3300-and later quits to find a better paid job designing
Databases.
Once again you may believe that this scenario is rare-but anyone who thinks the credit card Gods smiled
On them by not charging for that purchase several months ago have in fact probably witnessed a case
Transaction concurrency failure. Similarly, anyone who has checked in for a flight and been issued
Same boarding pass as someone else is probably the victim of a badly designed optimistic lock rather
An unscrupulous airline engaging in passenger seating foul play.
Whereas most of these transaction concurrency failures result in rather minor problems such as a free
CD from that on-line store or a double booked seat, both of which can easily be rectified by a polite
Conversation with the relevant person, when you consider safety critical systems the risks of concurrency
Failure using this approach become unacceptable. What wocould be the consequences of an optimistic lock
Failure resulting in the same organ being issued to two critically ill patients, or two aircraft being
Cannot ucted to land on the same runway at the same time?
Ideally what we need is a method of allowing Optimistic Locking whilst ensuring concurrency, and
Although the technique is well formed ented and understood, it is rarely used in business applications.
Ensuring data concurrency.
The method to ensure data consistency with the use of Optimistic Locking is quite simple. At It's most
Basic it involves reading a key value along with the record, and then writing that same key value back to
Database when the record is updated. The database then modifies the key value on a successful write
Ensure that any other current transactions that hold the same key value will not be able to modify
Record. shocould any other transaction attempt to update the record with the old (and now obsolete) Key
Value, the database will recognize the key value as obsolete and reject the update.
In practice there are a number of different way of achieving this, but the most common is the use of
Modification time-stamp. Let us consider again our employee example from earlier:
King wocould read Scott's salary data from the database with a time-stamp, and then get distracted.
Human Resources wocould read the same record and add 5% to Scott's salary. HR wowould then write back
The record with the same time-stamp that both they and King have read. The database wocould compare
Time-stamps, see that they match, and accept the update. The database now updates the time-stamp
Reflect the fact that the record has changed.

King wowould then return to his task of giving Scott his well-earned $300 pay rise and attempt to update
Record with the original and now obsolete time-stamp. The database wocould compare the time-stamps and
See that they no longer match. The database wocould reject the update with a concurrency failure and
Well-written application wocould inform King that the record he is trying to update has been changed and
Must be re-read before any updates can be made.
King re-reads the record and applies the $300 pay rise to Scott's adjusted salary giving a final and correct
Figure of $3450
Note that it is the database that updates the time-stamp-not the application. The time-stamp in this
Context is merely a key that demonstrates to the database that the data being written is consistent with that
Already in the record.
Suggestions for implementation.
The Oracle database, with it's Row-level triggers and sophisticated PL/SQL programming language
Provides everything we need to implement concurrency controls for database tables ts wishing to use
Optimistic Locking. Indeed, there are doubtless always ways of achieving concurrency control with
Optimistic lock, but in this article I present one method that has been refined over the development cycles
Of several large projects. The reader is invited to take this suggestion and modify it to their own needs.
However, before describing the solution I wocould like to cover some of the lessons that have helped
Shape it's evolution:
· The concurrency shocould always be enforced at the trigger level. I have several attempts
Enforce concurrency using a PL/SQL interface, and although it can work, is easily defeated by
Casual user with access to a SQL prompt and a rudimentary understanding of SQL.
· The concurrency key used to guarantee consistency shocould never be called "timestamp"-even if it is
A date/time-stamp. Calling the concurrency key "datestamp" or "timestamp" is inviting
Development Team to misinterpret the meaning of the data held in the column. I have seen
Concurrency key time-stamps appearing on management reports with all manner of imagined
Meanings.
· Using a date/time-stamp as the concurrency key is further discouraged as it only offers a one-second
Resolution for the lock. although today it may seem unlikely that two transactions cocould read
Same record, make different changes and both write back in the same second, the rapid pace
Development in the information sector and the increasing use of B2B systems mean that
Likelihood of this scenario will only increase over time.

Using an integer for the concurrency key makes interactive SQL much simpler, and if combined
The get_time function within the dbms_utility Package offers lock resolutions to 100th
Second.
· Use an offset when writing back the key. For example, if the key value read is N, then the application
Shocould write back n + 1. This is for two reasons. Firstly, if the transaction making the update only had
To specify the same key as was read, then not specifying the key at all wowould have the same effect, and
So wocould require the update trigger to include code to check that the key was being updated.
Secondly, even if the trigger did include code to ensure the key was being updated, too third-party
Transaction processors will attempt to out-smart the application developer and screen out updates that
They consider redundant. Third-party transaction processors are becoming more commonplace
The advent of B2B communications and applications that interact with heterogeneous data-sources.
When your well-behaved application dutifully attempts to write back exactly the same key value as it
Read, a third-party transaction processor may determine that update to be redundant and omit it from
The update command that finally reaches the database, resulting in a de-facto concurrency failure.
This is especially true of several of the current leading application servers on the market.
· When a concurrency failure is encountered abort the transaction immediately using PL/SQL's
Raise_application_error procedure. Do not attempt to handle the error any other way-
Do so is to risk inconsistent data in your database.
Worked example.
In this section we present a fully tested worked example that draws upon all of the lessons presented in
The previous section and which is again based on the familiar Scott/tiger demonstration. This example
Will work in all versions of oracle from 7.1 onwards:
First we will add our concurrency key to the EMP table as an integer column called TCN (transaction
Control number). We then set the initial value of the TCN for each row and mark the column as not-null.
SQL> ALTER TABLE EMP add TCN integer;
Table altered.
SQL> Update EMP set TCN = dbms_utility.get_time;
14 rows updated.
SQL> ALTER TABLE EMP modify TCN not null;
Table altered.

We can now add the pre-insert and pre-update triggers that will enforce concurrency with our optimistic
Locking strategy. The pre-insert trigger simply sets the initial value of the TCN. Note that the initial value
Can be any arbitrary integer value, the only purpose at this stage is to comply with the not-Null
Requirement, but using the result of the get_time function provides consistency extends SS both triggers.
The real work is done by the Pre-update trigger that checks the value of the updated TCN against the one
Held in the database. If the two tcns do not match (they are in fact offset by one) the transaction is
Rejected.
Create or replace trigger emp_bitrg
Before insert on EMP for each row
Begin
/* Note-additional pre-insert code may go here */
/* Set the initial transaction control number */
: New. TCN: = dbms_utility.get_time;
End;
Create or replace trigger emp_butrg
Before update on EMP for each row
Begin
/* Test for concurrency failure */
If (: New. TCN! =: Old. TCN + 1) then
Raise_application_error (-20000, 'concurrency failure ');
End if;
/* Note-additional pre-update code may go here */
/* Update the transaction control number */
: New. TCN: = dbms_utility.get_time;
End;
Now let's re-run our example with King and human resources to see what happens:

King reads Scott's salary data and gets distracted ....
SQL> select Sal, TCN from EMP where empno = 7788;
Sal TCN
--------------------
3000 761824625
Human Resources read Scott's salary data, apply a 5% increase and commit the change:
SQL> select Sal, TCN from EMP where empno = 7788;
Sal TCN
--------------------
3000 761824625
SQL> Update EMP set sal = 3150, TCN = 761824626 where empno = 7788;
1 row updated.
SQL> commit;
Commit complete.
King computes Scott's new salary based on his obsolete data and attempts to update the record:
SQL> Update EMP set sal = 3300, TCN = 761824626 where empno = 7788;
Update EMP set sal = 3300, TCN = 761824626 where empno = 7788
*
Error at line 1:
ORA-20000: concurrency failure
ORA-06512: At "Scott. emp_butrg", line 4
ORA-04088: error during execution of trigger 'Scott. emp_butrg'
King re-reads Scott's salary data and discovers the change:
SQL> select Sal, TCN from EMP where empno = 7788;
Sal TCN
--------------------
3150 761828854
King adjusts his calculations to induplicate ate the change:
SQL> Update EMP set sal = 3450, TCN = 761828855 where empno = 7788;
1 row updated.

After King successfully applies his $300 pay rise the pre-update trigger in the database changes the TCN
Number again to ensure that human resources nor anyone else can update the database with an obsolete
TCN.
Limitations and suggestions for future enhancements.
The solutions and example shown above demonstrate how to integrate Optimistic Locking
Concurrency Control into an oracle Development Project. However the database has ect needs
Understand that this is only part of the solution.
More challenging than the technology is overcoming resistance from seasoned Development
Specified sionals who have been using the trusted select... For update for all of their oracle careers.
These individuals may need to be convinced of the benefits of using optimistic and on large development
Projects their support will be crucial.
Also note that the solution as shown is still not fool-proof. Any transaction that updates a record and sets
The TCN to TCN + 1 will succeed. Indeed, if the update being made is a relative Update (e.g. Set x = x *
1.05) then defeating the concurrency control in this manner is acceptable. What is not acceptable is
Defeating the concurrency control to perform an absolute Update (e.g. Set x = 42) if the validity of that
Update wocould be undermined by X changing since the new absolute value was calculated.
The use of the dbms_utility.get_time function to set and update the TCN has proven reliable on
Several large projects, but others may decide on a different approach such as a straightforward unique
Sequence Number.
As noted earlier, incluthird party transaction processors and application servers are already implementing
Optimistic Locking with concurrency controls, and it is probably only a matter of time before Oracle Corp.
Integrates some form of concurrency control into the core of the database product.