Recently I had a task, according to which, I needed to have a specific lock functionality. The specific was due to:
- Transaction that was updating table was distributed, so I had no controll over it,
- During the day thousands non blocking transactions must be supported simultaniously, lets call them general operations,
- Each general operation updated rows on specific branch ("LDN","NY","LA" ...),
- Once a day there is a master operation for each branch, which happen spanteniously, on different branches,
- During master operation no general operations on that branch can happen.
- When master operation start it must wait for current general operation on the provided branch completion, which were in the system before master operation arrived.
- Durring master processing on a specific branch, all other branches can be updated.
In order to archive this, I created Oracle DB specific table
create table BRANCH_LOCK(
BRANCH VARCHAR2(10),
FLAG VARCHAR2(1),
CONSTRAINT "PK_BRANCH_LOCK" PRIMARY KEY ("BRANCH")
)
The following functionality for different operations was supported:
For general operations:
1. In the same XA transaction each operation locks BRANCH_LOCK table in SHARE mode, 2. After locking it checks FLAG, on updated branch, 1. If flag is Y , that means that currently master operation is in progess, so Exception is thrown, and no further processing is done; 2. If flag is N than everything is OK, and general processing is done;
For master operation:
- When master operation comes I start separate transaction which:
- Lock BRANCH_LOCK table in EXCLUSIVE mode, which transaction can not acquire while there is SHARE mode LOCK on this table in a different transaction (This way, I guarantee that master operation would start after all current general operation finish, although it waits for transactions on all branches to finish, not only specified one),
- Sets flag for the branch to Y (This way, I guarantee that there would be no general transactions while master operation processing),
- In incoming transaction I change a Flag in the table to N , so after it commits BRANCH_LOG table will have appropriate value in FLAG column, and system would be able to process general operations again.
This has not gone in production yet, So I wonder is there a better solution for this, and are there any more drawbacks besides described one?
Some updates, which I did not mention:
- Master operation works on results of general operations, So it is vital, that no general operation get lost, durring the master processing, so this why current general operation must finish, before master operation start processing.
- Multiple general operations on the same branch happen every second, arround 3 000 operations per second,
- Only one master operation for the branch can happen, multiple master operations on different branches can be processed at the same time.