Search code examples
oracleconcurrencytransactionsejbeclipselink

Many paralllel transactions vs transaction isolation level in Oracle DB

My app retrieves requests using Weblogic Server, EJB, EclipseLink as a ORM. The business logic looks as follows. Select all records from table A where A.col = 'ABC' (ABS is a value from request) If none of them satisfy some condition, create new one.

Now suppose that 10 parallel requests have been sent with the same payload. I have a default isolation level in my Oracle DB (READ_COMMITED). In this case, many transactions are performed in parallel:

Req1 start T1    
Req2 start T2   
T1 select rows  
T2 select rows  
T1 insert new one (no rows with col = 'ABC')
T1 COMMIT
T2 insert new one (no rows with col = 'ABC')
T2 COMMIT

As a result, 1-10 rows are created instead of 1.

Oracle doesn't have REPEATABLE_READS isolation level. SERIALIZABLE has a negative impact to throughput.

Solution

  • PESSIMISTIC_WRITE lock mode is the solution. PESSIMISTIC_WRITE acquire exclusive lock on selected row using FOR UPDATE (Oracle). JPA takes LockModeType as a one of method argument, e.g. in find method. Of course, consistency at the expense of throughput.