1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle table lock - multiple transaction runs parallely

Discussion in 'SQL PL/SQL' started by stsivaraj, Sep 1, 2015.

  1. stsivaraj

    stsivaraj Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    chennai
    Hi,
    I have a table Devices and am trying to insert 1000 records at a time using a single transaction. Suppose If I have 5 transaction and each transaction has 1000 records to insert. In this case, will all transaction will insert parallel or wait for each other to complete. My doubt is for each transaction acquire the table lock or row lock. Will it insert parallel mode or wait one transaction release other transaction.
     
  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    There is no locking behavior that you need to be concerned with for INSERT operations no matter the parallelization.

    Locking behavior in Oracle is designed to ensure data consistency. This only applies to existing rows and so is required for UPDATE and DELETE operations to ensure things like User A cannot modify a row that is currently being updated by User B.

    Tables locks in Oracle generally only occur during DDL operations, when the structure of the table itself is being modified.
     
  3. stsivaraj

    stsivaraj Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    chennai
    Thanks for your reply