Java synchronization Vs Oracle locking

The choice between using java synchronized block or oracle row locking mechanism, while accessing database row can be determined by understanding the following scenario.

Consider the following example. There are 1000 people who apply for job in XYZ Company. The details of the applicants are stored in database table, EMP_DETAILS. The table contains various columns one of which is STATUS. The STATUS column has the default value as ‘not-verified’. The STATUS column is used to verify whether the applicant details, applying for job, are verified or not.

There are 5 employees in company XYZ, who use a web based application, to access these details from database. The query that is used to perform the database search is SELECT * FROM EMP_DETAILS WHERE STATUS=’not-verified’. Once the details are verified by these employees, the status is updated as ‘verified’.

If 5 employees are using the web application simultaneously, it is possible that more than one employee to access the same record from database i.e, more than one employee is viewing the details of the same applicant, since the query is SELECT * FROM EMP_DETAILS WHERE STATUS=’not-verified’. If both the employees make some changes to the record, it can cause data inconsistency.

Java provide a mechanism to handle this scenario by putting the code in synchronized block, and change the status of the STATUS as ‘locked’. This synchronized block should be common code that is used by all the thread in a web application.

synchronized (this){
//Fetch a record from database.
//SELECT * FROM EMP_DETAILS WHERE STATUS=’not-verified’
//change the STATUS as ‘locked’ so that it is not visible to other threads.
}

The synchronized block ensures that only one thread can enter the block at any given point of time. The other thread cannot enter the block until the first thread exits the block.
The thread that first enters into synchronized block will fetch a record from database, and change its STATUS as ‘locked’ so that it is not visible to other threads, since the query to fetch the record is SELECT * FROM EMP_DETAILS WHERE STATUS=’not-verified’.

This concept works fine if the code is deployed in non-clustered environment, where there is only one instance of servlets, containing the synchronized block. In clustered environment, there are multiple instances of web applications accessing the database simultaneously. Each cluster will have identical servlets and the synchronized block.

While accessing web application in clustered environment, the request from different users can end up on different clusters. As a result the synchronized block on different clusters can execute simultaneously, and it is possible for different web user to access the same record at any given point of time.

Synchronization ensures that out of all the threads in a cluster, only one thread can enter the synchronized block and access a database record. But this does not prevent the thread in other cluster to access the same database record, since synchronization of thread manages the threads in the same cluster. These threads are not synchronized with the thread running on different clusters.

This situation can be overcome by using SELECT FOR UPDATE query. SELECT FOR UPDATE query acquires exclusive row locks on the row(s) to be modified by the statement. Other transactions cannot view, update or delete the locked rows until the locking transaction either commits or rolls back.

synchronized (this) {
//Fetch a record from database.
//SELECT * FROM EMP_DETAILS WHERE STATUS=’not-verified’ FOR UPDATE.
//change the STATUS as ‘locked’ so that it is not visible to other threads.
//Commit / Rollback transaction to release the Lock.
}

By using SELECT FOR UPDATE, the row that is fetched from database is locked by oracle, irrespective of clustered or non-clustered environment. Once the STATUS is set to ‘locked’ for the given row, the lock can be released by committing (rollback) the transaction.

Note: Make sure that auto commit is set to false for this approach to work correctly.