Forum Stats

  • 3,770,163 Users
  • 2,253,079 Discussions
  • 7,875,350 Comments

Discussions

Locking table for more transactions

Wojtus-J
Wojtus-J Member Posts: 18
edited Jun 11, 2013 8:27AM in General Database Discussions

I am using Oracle 11g.

I am trying to realize scenario of concurrent loading into a table with index rebuild. I have few flows which are trying to realize this scenario:

1. load data from source,

2. transform the data,

3. turn off the index on DWH table,

4. load data into DWH,

5. rebuild index on DWH table.

I turn off and rebuild indexes for better performance, there are situations where one flow is rebuilding the index while the other tries to turn it off. What I need to do is to place some lock between points 2 and 3, which would be released after point 5.

Oracle built in LOCK TABLE mechanism is not sufficient, as the lock is released by the end of transaction, so any ALTER statement releases the lock.

The question is how to solve the problem?

Tagged:

Comments

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,790 Gold Crown
    Wojtus-J wrote:
    
    What I need to do is to place some lock between points 2 and 3, which would be released after point 5.
    Oracle built in LOCK TABLE mechanism is not sufficient, as the lock is released by the end of transaction, so any ALTER statement releases the lock.
    The question is how to solve the problem?
    

    Take a look at the dbms_lock package. This allows you to create "user-defined" locks that obey all the rules of Oracle's internal locking mechanism.

    You then need to create a lock that represents the action of isolating the DWH table for the drop/load/rebuild, and write code that has to take lock exclusively before the action and release it afterwards. There's an example on my blog - but not an exact match for what you want: http://jonathanlewis.wordpress.com/2008/05/12/synchronisation/

    Regards

    Jonathan Lewis

  • Wojtus-J
    Wojtus-J Member Posts: 18

    Thank you Jonathan. That was exactly what I was looking for - works perfectly.

This discussion has been closed.