Handle CONCURRENT Updates(Pessimistic and Optimistic Oracle Locking)

What is the difference between the Pessimistic and Optimistic Oracle Locking? 

How to handle Concurrent Updates on a TABLE?

Oracle allows you to choose the strategy for locking, either pessimistic or optimistic, depending on your needs.

PESSIMISTIC LOCKING:  

Pessimistic Locking is when you lock the record for your exclusive use until you have finished with it. It has much better integrity than optimistic locking but requires you to be careful with your application design to avoid Deadlocks

The developer must declare their intent to update the row set.  This is done with the SELECT xxx FOR UPDATE clause.

The only problem with this approach is The Locking and Deadlock.

Example- User A (Travel Agent) try to book last flight ticket to Delhi. He started the transaction but before clicking book the ticket, he got a phone call and talking. He got a lock on that data.

User B (Another travel agent) tries to book same ticket and wanted to make a lock to that row and he will get

ORACLE.JBO.AlreadyLockedException: JBO-26030: Failed to lock the record, another user holds the lock

The disadvantage of pessimistic locking is that a resource is locked from the time it is first accessed in a transaction until the transaction is finished, making it inaccessible to other transactions during that time.

OPTIMISTIC LOCKING:  

Optimistic Locking is a strategy where you read a record, take note of a version number (other methods to do this involve dates, timestamps or checksums/hashes) and check that the version hasn’t changed before you write the record back (You re-read data and only update it if it did not change since the initial fetch)

When you write the record back you filter the update on the version to make sure it’s Atomic. (i.e. hasn’t been updated between when you check the version and write the record to the disk) and update the version in one hit.
If the record is dirty (i.e. different version to yours) you abort the transaction and the user can re-start it.

EXAMPLE:

User A (Travel Agent) try to book last flight ticket to Delhi. He started the transaction but before clicking book the ticket, he got a phone call and talking. Here it will not try to acquire lock.

User B (Another travel agent) tries to book same ticket and he click commit. Ticket Booked. Changes will happen in Database. 

But now user A will try to book the Ticket and he will get 

ORA.JBO.RowInconsistentException.

Which one to use
Oracle recommends using Optimistic Locking for Web Applications.

Pessimistic locking, which is the default, should not be used for Web Applications as it creates pending transnational state in the database in the form of ROW-LEVEL locks.

Let’s understand this with the help of one more example and then will understand Practically:

Basically, above methodologies used to handle multi-user issues.

How does one handle the fact that 2 people want to update the same record at the same time?

1. Do Nothing

• User 1 Reads a Record
• User 2 Reads the same Record
• User 1 Updates that Record
• User 2 Updates the same Record

User 2 has now over-written the changes that User 1 made. They are completely gone, as if they never happened. This is called a 'Lost Update'.

2. Lock the record when it is Read. Pessimistic Locking

• User 1 reads a Record and locks it by putting an exclusive lock on the Record (FOR UPDATE clause)
• User 2 attempts to Read and lock the same Record, but must now wait behind User 1
• User 1 Updates the Record (And Commits)
• User 2 can now Read the record with the changes that User 1 has made
• User 2 Updates the Record complete with the changes from User 1

The lost update problem is solved. But the problem with this approach is Concurrency. User 1 is locking a record that they might not ever update. User 2 cannot even read the record because they want an exclusive lock when reading as well. This approach requires far too much exclusive locking, and the locks live far too long.

3. Use Optimistic Locking.

Optimistic locking does not use Exclusive Locks when Reading. Instead, a check is made during the update to make sure that the Record has not been changed since it was Read. This can be done by checking every field in the table.
i.e. UPDATE Table1 SET Col2 = x WHERE COL1=:OldCol1 AND COl2=:OldCol AND Col3=:OldCol3 AND...

There are, of course, several disadvantages to this. First, you must have already SELECTED every single column from the table.  Secondly, you must build and execute this massive statement.

Most people implement this, instead, through a single column, usually called TIMESTAMP. This column is used for no other purpose than implementing Optimistic Concurrency. It can be a Number or a Date. The idea is that it is given a value when the Row is Inserted.

Whenever the Record is Read, the Timestamp column is Read as well. When an Update is performed, the Timestamp column is checked. If it has the same value at UPDATE time as it did when it was read, then all is well, the UPDATE is performed and the timestamp is changed. If the Timestamp value is different at UPDATE time, then an error is returned to the User - they must re-read the record, re-make their changes, and try to Update the Record again.

• User 1 Reads the Record, including the Timestamp(T1)
• User 2 Reads the Record, including the Timestamp(T1)
• User 1 attempts to Update the record. If the Timestamp(T1) in hand matches the Timestamp in the Database(T1), then the Update is performed and the Timestamp is Updated(T2).
• User 2 attempts to Update the Record. The Timestamp in hand (T1) does not match the timestamp in the Database (T2), so an error is returned. User 2 must now re-read the Record, including the New Timestamp(T2) and User 1's changes, re-apply their changes and re-attempt the Update.

Optimistic Locking with CONCURRENCY In ORACLE

To Demonstrate this, consider an example- It’s the end of the year and Employees has done well. Company(Will use USER1) decides to give Employees a well-earned $300 pay rise. Meanwhile other Users(Will use USER2) are also using the Employee system, giving everybody a 5% annual salary.

USER1 Reads Salary of Employee (EMP_ID=202377) and gets Distracted

SQL> SELECT SALARY From EMPLOYEES Where EMP_ID=202377;

SALARY

--------------

3000

USER2 Reads Salary of Employee (EMP_ID=202377), make update on Salary and Commit the change.

SQL> SELECT SALARY From EMPLOYEES Where EMP_ID=202377;

SALARY

--------------

3000

SQL> UPDATE EMPLOYEES SET SALARY=3150 Where EMP_ID=202377;

1 row updated.

SQL> COMMIT;

Commit complete.

USER1 computes New salary based on his obsolete data and updates the record:

SQL> UPDATE EMPLOYEES SET SALARY=3300 Where EMP_ID=202377;

1 row updated.

SQL> COMMIT;

Commit complete.

The change made by USER2 has been wiped out:

SQL> SELECT SALARY From EMPLOYEES Where EMP_ID=202377;

SALARY

--------------

3300

Since the locking mechanism is Optimistic, No Locks are taken out during the Database Read. When USER1 gets distracted and USER2 make an Update, USER1 has no way of knowing that his data has become obsolete.

Unaware of what USER1 has done, USER2 calculates New salary on his original (and now inconsistent) data producing a result which he commits back to the database wiping out the change made by USER1. This is what is known as a “Buried Update”.

Ideally what we need is a method of allowing optimistic locking whilst ensuring concurrency.

Ensuring Data Concurrency.

The method to ensure data consistency with the use of optimistic locking is quite simple. At its most basic it involves reading a key value along with the record, and then writing that same key value back to the database when the record is updated. The database then modifies the key value on a successful write to ensure that any other current transactions that hold the same key value will not be able to modify the record.

Should 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 a modification Time-Stamp. Let us consider again our Employee example from earlier:

USER1 would read Employee (EMP_ID=202377) salary data from the database with a Time-Stamp, and then get distracted.

USER2 would read the same record and Update Salary. USER2 would then write back the record with the same Time-Stamp that both they and USER1 have read. The database would compare the Time-Stamps, see that they match, and accept the Update. The Database now updates the Time-Stamp to reflect the fact that the record has changed.

USER1 would then return to his task of increasing Salary for EMPLOYEE (202377) and attempt to Update the record with the original and now Obsolete Time-Stamp. The database would compare the Time-Stamps and see that they no longer match. The database would reject the Update with a Concurrency Failure and the well-written application would inform USER1 that the record he is trying to Update has been changed and must be re-read before any other Updates can be made.

USER1 re-reads the record with Time-Stamp and make the changes he wants.

IMPLEMENTATION

The Oracle database, with its Row-Level Triggers and sophisticated PL/SQL programming language provides everything we need to implement Concurrency controls for Database.

·       Using an integer for the concurrency key makes interactive SQL much simpler, and if combined with the GET_TIME function within the DBMS_UTILITY Package offers lock resolutions to 100th second

·       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 - to do so is to risk inconsistent data in your database.

First, we will add our Concurrency key to the Table (In our case EMPLOYEES) 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 EMPLOYEES Add TCN Integer;

Table altered

SQL> UPDATE EMPLOYEES Set TCN = DBMS_UTILITY.GET_TIME;

5 rows updated.

SQL> Alter Table EMPLOYEES 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 across both triggers.

CREATE OR REPLACE Trigger EMP_PREINSERT

BEFORE INSERT ON EMPLOYEES

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;

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 the transaction is rejected.

CREATE OR REPLACE Trigger EMP_PREUPDATE

BEFORE UPDATE ON EMPLOYEES

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 USER1 and USER2 to see what happens:

USER1 Reads Salary of Employee (EMP_ID=202377) and gets Distracted

SQL> SELECT SALARY, TCN From EMPLOYEES Where EMP_ID=202377;

    SALARY        TCN

---------- --------------------

     3000    69057359

USER2 Reads Salary of Employee (EMP_ID=202377), make update on Salary and Commit the change.

SQL> SELECT SALARY, TCN From EMPLOYEES Where EMP_ID=202377;

    SALARY        TCN

---------- --------------------

     3000    69057359

SQL> UPDATE EMPLOYEES SET SALARY=3150, TCN=69057360 Where EMP_ID=202377;

1 row updated.

SQL> COMMIT;

Commit complete.

USER1 computes New salary based on his obsolete data and updates the record:

SQL> UPDATE EMPLOYEES SET SALARY=3300, TCN=69057360 Where EMP_ID=202377;

UPDATE EMPLOYEES SET SALARY=3300, TCN=69057360 Where EMP_ID=202377;

       *

ERROR at line 1:

ORA-20000: Concurrency Failure

ORA-06512: at "MYUSER. EMP_PREUPDATE ", line 4

ORA-04088: error during execution of trigger 'MYUSER. EMP_PREUPDATE '

Now, USER1 re-reads the Data of Employee (202377) and find a change

SQL> SELECT SALARY,TCN From EMPLOYEES Where EMP_ID=202377;

    SALARY        TCN

---------- --------------------

     3150    70267087

SQL> UPDATE EMPLOYEES SET SALARY=3450, TCN=70267088 Where EMP_ID=202377;

1 row updated.

SQL> COMMIT;

Commit complete.

After USER1 successfully applies his change - pre-update trigger in the database changes the TCN number again to ensure that USER2 nor anyone else can update the database with an obsolete TCN.

Source: http://www.orafaq.co