Oracle DB lock table use

Recently I had a task, according to which, I needed to have a specific lock functionality. The specific was due to:

1. Transaction that was updating table was distributed, so I had no controll over it,
2. During the day thousands non blocking transactions must be supported simultaniously, lets call them 'general' operations,
3. Each 'general' operation updated rows on specific 'branch' ("LDN","NY","LA" ...),
4. Once a day there is a 'master' operation for each branch, which happen spanteniously, on different branches,
   1. During 'master' operation no 'general' operations on that branch can happen.
   2. 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.
   3. 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:

1. When 'master' operation comes I start separate transaction which:
   1. 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),
   2. Sets flag for the branch to 'Y' (This w开发者_开发技巧ay, I guarantee that there would be no 'general' transactions while 'master' operation processing),
2. 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:

1. '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.
2. Multiple 'general' operations on the same branch happen every second, arround 3'000 operations per second,
3. Only one 'master' operation for the branch can happen, multiple 'master' operations on different branches can be processed at the same time.

---

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.

---

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.

---

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.