Databases
StackOverflow2013
Postsdbo
POOracle DB lock table use
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOracle DB lock table use
    primarykey
    data
    text
    <p>Recently I had a task, according to which, I needed to have a specific lock functionality. The specific was due to:</p> <blockquote> <ol> <li>Transaction that was updating table was distributed, so I had no controll over it, </li> <li>During the day thousands non blocking transactions must be supported simultaniously, lets call them 'general' operations,</li> <li>Each 'general' operation updated rows on specific 'branch' ("LDN","NY","LA" ...),</li> <li>Once a day there is a 'master' operation for each branch, which happen spanteniously, on different branches, <ol> <li>During 'master' operation no 'general' operations on that branch can happen.</li> <li>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.</li> <li>Durring 'master' processing on a specific branch, all other branches can be updated.</li> </ol></li> </ol> </blockquote> <p>In order to archive this, I created Oracle DB specific table</p> <pre><code>create table BRANCH_LOCK( BRANCH VARCHAR2(10), FLAG VARCHAR2(1), CONSTRAINT "PK_BRANCH_LOCK" PRIMARY KEY ("BRANCH") ) </code></pre> <p>The following functionality for different operations was supported:</p> <p>For 'general' operations:</p> <blockquote> <pre><code>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; </code></pre> </blockquote> <p>For 'master' operation:</p> <blockquote> <ol> <li>When 'master' operation comes I start separate transaction which: <ol> <li>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),</li> <li>Sets flag for the branch to 'Y' (This way, I guarantee that there would be no 'general' transactions while 'master' operation processing),</li> </ol></li> <li>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.</li> </ol> </blockquote> <p>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?</p> <p>Some updates, which I did not mention:</p> <ol> <li>'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.</li> <li>Multiple 'general' operations on the same branch happen every second, arround 3'000 operations per second,</li> <li>Only one 'master' operation for the branch can happen, multiple 'master' operations on different branches can be processed at the same time.</li> </ol>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmavarazy
    1. USmavarazy
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POOracle DB lock table use
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POOracle DB lock table use
      1. USRené Nyffenegger
      1. VTFavorite
    3. VO
      singulars
      1. POOracle DB lock table use
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI'm not clear on your point (1). What does it being distributed have to do with whether you can control it? Also, is this referring to the general or master operation?
      singulars
      1. POOracle DB lock table use
      1. USDave Costa
    2. COYou imply, but don't state outright, that it is OK for multiple general operations on the same branch to work at once. Is that correct?
      singulars
      1. POOracle DB lock table use
      1. USDave Costa
    3. CORegarding comments. Regarding point 1 all the operations general and master are distributed, so I don't know when transaction starts, and when will it be commited, or rolledback, over this points in time I have no control. Regarding general operations, there must be 3'000 oper per sec on the same branch supported, so yes, there are multiple operations at the same time for one branch, happening simultaniously.
      singulars
      1. POOracle DB lock table use
      1. USmavarazy
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload