Solve concurrent access problem in cluster based on Oracle database lock mechanism

1. Demand

The application scenario is this:

Use Oracle data to save the to-do task, using the status field to differentiate whether the task has been executed. When multiple worker threads perform a task concurrently, the value of the status field is modified after the execution succeeds or fails.

Suppose the database table structure looks like this.

tableTask(    id      varchar2(32),    name    varchar2(32),    flag    varchar2(1),    worker  varchar2(32));

Flag desirable values include: 0-Todo, 1-Done, 1-failed to retry.

Problems to avoid:
When multiple workers are working at the same time, avoid a situation where a task is executed multiple times, and the status of the task is incorrectly changed.

2, Analysis 2.1, rely on the Java language mechanism

The Java language locking mechanism can resolve concurrency problems, but only in single-machine situations.

In a tomcat (or other Application server) cluster environment, the locking mechanism in Java code does not solve this problem.
The various synchronization and locking mechanisms at the Java language level are internal to the JVM and cannot handle cross-JVM scenarios.

As the semaphore of the lock, it must be stored in a separate place from the JVM. Can be a database, which can be redis.

2.2. Support provided by quartz

In a production environment, in order to avoid a single point of failure, Quartz requires that the cluster provide HA (high availability, highly available) support. The quartz cluster relies on persisting task information into the database.

There are two alternative ideas:

1. The number of workers that can set a single node is 1. The first guarantee is that there will be no concurrency problems within a single node. To ensure that only one instance of the same job in the cluster is running, you need to examine the documentation provided by Quartz.

2, use @DisallowConcurrentExecution or Statefulljob on the task class. It may be possible to achieve results and need experimentation.

Using quartz to manage concurrency problems, the avoidance strategy is adopted and the computational resources are not fully utilized.

The quartz cluster environment relies on JDBC storage, on the one hand, it needs to share information among nodes through the database, on the other hand, the database-based rowset lock solves the concurrency problem.

Quartz itself is too large, not to read the document carefully, or even read the source code, there is no way to guess its behavior, as an enterprise-class Timer, it is very useful. Solve concurrency problems, or find a more refreshing way to do it.

2.3, through the database lock implementation

Before considering a solution to the problem, review the transaction isolation level of the database and the locking mechanism of the Oracle database.

2.3.1, transaction ISOLATION LEVEL

The transaction isolation level is for the current session.

The SQL 92 standard defines 4 levels of transaction isolation.

1, read uncommited: Can read to other sessions not submitted to.
This is the lowest of the 4 levels. Other sessions are modified directly on the data, and the current session is read to the real-time modification state of other sessions.
When session B modifies the data, is read by the current session, and session B is rolled back, the current session reads "Error" data. This is called "dirty reading".

2, read commited: The current session can read to other sessions have been submitted to the data.
This isolation level avoids "dirty reads".
If this condition occurs:

1) 当前会话读取数据；2) 会话B 修改并提交了当前会话数据；3) 当前会话再次提交，读到了会话B修改后的数据。

For the current session, read the data two times, read the same, which is called "non-repeatable read."

This is the default transaction isolation level for Oracle.

3. Repeatable READ: The current session does not see data modifications that have been committed by other sessions, but you can see the newly inserted data from other sessions.

The problem with non-repeatable reads is that the same query condition is repeated in the same session, and the number of records in the query gets different. This is called "Phantom reading".

4, Serializable: Other sessions on the data changes are not visible.

It is important to note that not other sessions cannot modify the data, but instead modify the current session to be invisible.

Oracle supports 3 levels of transaction isolation.

Oracle supports read commited, repeatable read, in addition, read only is supported.
Read only is the most thorough deceiving.

The level of transaction isolation can help us understand the problem, but it is not a way to solve the problem.

Solve the problem, rely on the database lock mechanism.

Locking mechanism of 2.3.2 and Oracle database

What we need is a DML lock, and the purpose of DML locks is to ensure data integrity in concurrency. In Oracle, DML locks include table-level and row-level locks.

Select ... for update to obtain row-level locks.
Update execution also automatically obtains row-level locks.

In this way, we can have two methods to achieve the purpose of concurrency control:

Method One:

Get row-level locks with select ... for update, lock several tasks, and each data is a task.

The task is then executed, the task is completed, the status is updated, the transaction is committed, and the lock is released.

Quartz itself is using this mechanism to solve concurrency problems in clusters.

Related code files include:

接口定义：org.quartz.impl.jdbcjobstore.Semaphore。模板方法：org.quartz.impl.jdbcjobstore.DBSemaphore。实现类：org.quartz.impl.jdbcjobstore.StdRowLockSemaphore。应用：org.quartz.impl.jdbcjobstore.JobStoreSupport。

Key methods include: Obainlock,releselock and Executeinlock.

Method Two:

Follow these steps:

1. Execute the following SQL statement to preempt the task.

update Task t set t.worker = ‘worker-1‘ where t.worker is null 

Improvements such as the above SQL can be made to preempt only the specified number of tasks, and redundant tasks are left to other workers.

2, one by one to perform the task has been preempted.

You can use the following SQL to query the task information that has been preempted successfully.

select * from Task t where t.worker = ‘worker-1‘ and t.flag < 1

3. After execution, change the status of the task.

set1whereid = ‘someid‘

If the task fails to execute, put it back.

update Task t set t.flag = -1 , t.worker = nullwhere id = ‘some id‘

The main point of this approach is in the first step. The first step is where the concurrency problem occurs.

Oracle's UPDATE statement automatically obtains row-level locks. We can do the following experiments to verify:

1）打开两个PL／SQL 窗口，模拟两个会话，每个窗口都将执行update语句，更新相同的行。比如：第一个窗口执行 update Task set flag＝1 where flag＝0 and id＝‘1’，第二个窗口执行 update Task set flag＝2 where flag＝0 and id＝‘1’。2）先执行第一个窗口的update语句，不提交。3）再执行第二窗口的 update 语句，发现“在等待”。4）提交或回滚第一个窗口的事务后，发现第二窗口停止等待，执行了语句，等待提交。两条语句是串行执行的。

Oracle's own lock mechanism for update is sufficient to support our work.

Method Three:

Increase the version or timestamp field, using optimistic locking.

With method Two, this method is troublesome.

Solve concurrent access problem in cluster based on Oracle database lock mechanism