CODEWITHSUNDEEP
javaspringoracle-databasetransactionsisolation-level
Maverick Riz
Maverick Riz

Reputation: 2065

Spring transactions and inserting into database

I have a design question. Consider an application running in multiple cluster environment, lets say there are 3 cluster. The application listens to a directory, and processes all the new files exported there and sends it to a document management system. I want only one cluster handling a given file, so that i can avoid the race condition. For this purpose i created a lock table say DocumentLock, so that i can use database as the arbiter. The 3 columns are guid(primary key), file id(this is coming from the name of the file which is unique), lock time stamp. I am using Spring transactions, and Oracle as the database. I am planning to use Read_Committed isolation level.

So, suppose cluster A gets hold of file A, at the same time cluster B also gets hold of file A. Now cluster A, which uses Transaction-A will try to insert into the DocumentLock table, and lets say it is successful. So i will have 1 row in DocumentLock table, with unique id being the file A (file name). Now cluster B which uses Tx-B at the same time tries to insert a record into DocumentLock table, but it needs to wait until Tx-A is committed as i am using 'Read_Committed' isolation level.So TX-B will be in waiting state. Once Tx-A is committed, now Tx-B will try to insert same record(file A) and as it violates unique constraints it will throw an error.

Is my understanding of Read_committed correct?

I am trying to avoid multiple clusters inserting same file (unique record) in the table, so i am assuming that if both the transaction start inserting same file name at the same time, one of them needs to wait until the other one is committed ? So how does Oracle decides which Transaction(Tx-A or Tx-B) gets hold(lock) of the table? Also with Read_committed, is the whole table locked, or just the row ?

Also, if Cluster B get's hold of a different file, say file B it should insert into the DocumentLock without any issues, as cluster A is processing file A and cluster B needs to process file B. Appreciate your help

Upvotes: 0

Views: 660

Answers (1)

Harbeer Kadian
Harbeer Kadian

Reputation: 394

I am providing alternate way to solve this problem.

Make the isolation level as READ UNCOMMITTED. This will make sure any uncommitted updates to the database are also ready by other transaction. The benefit of this approach will be that any insertion to database from one cluster will be immediately accessible to all other clusters. This way one can put some application logic to figure out who should get the lock.

Lets take the cases.

Case 1: Cluster A and B are trying to get lock on File F

  • Cluster A wants to take hold of File F.

  • Cluster A searches in DB to see if any one has already got the lock.

  • Cluster A gets hold of the File F and inserts record into the table, but it has not committed the transaction

  • Cluster B wants to take hold of File F.

  • Cluster B searches in DB and finds the entry inserted by Cluster A
    even when that is not committed.

Case 2: Cluster A and B are trying to get lock on File F1 and F2

  • Cluster A wants to take hold of File F1.
  • Cluster A searches in DB to see if any one has already got the lock.
  • Cluster A gets hold of the File F1 and inserts record into the table, but it has not committed the transaction
  • Cluster B wants to take hold of File F2.
  • Cluster B searches in DB to see if any one has already got the lock.
  • Cluster B gets hold of the File F2 and inserts record into the table, but it has not committed the transaction.
  • Cluster A and Cluster B commits there transaction.

I am not pretty sure if the insertion of File f record is not committed by Cluster A, in that case will the database will throw uniqueness error in case Cluster B is also inserting record of File f. So this workaround to check all that in application logic can help.

Upvotes: 1

Related Questions