Question

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.

Answer 1

Anton, why exactly do you need to manually lock the table[s]? Normally there is no need to do such. When the master operation you describe starts, it get s a read consistent view of the data that is committed at the start of that master transaction. The general transactions can continue to work and the master transaction you describe will not see the changes, until it starts a new transaction.

Answer 2

Rather than build my own lock table, I think I d try to use Oracle s locking package DBMS_LOCK.

It s more efficient than using DML to do locks, and is what Oracle uses internally to perform enqueue locking.

Answer 3

What are the table volumes.

I d consider having the master operation start like

CREATE OR REPLACE PROCEDURE do_master (in_branch IN VARCHAR2) IS
BEGIN
  SELECT ...
  BULK COLLECT INTO
  FROM ...
  WHERE branch = in_branch
  FOR UPDATE OF branch;
  ...
END do_master;

That would use standard Oracle locking to ensure that the do_master waits until outstanding general transactions locking those branches complete, then the locks are taken by do_master stopping any other general updates until it commits. Then those general updates resume.

But if the volumes are big, then that SELECT...FOR UPDATE could be very large. That s when I d consider a DBMS_LOCK solution.

友情链接