Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Saturday, 2 April 2016

SQL Locks

Locks are the mechanisms used to prevent destructive interaction between users accessing same resource simultaneously. Locks provides high degree of data concurrency.

TYPES

Ø Row level locks
Ø Table level locks

ROW LEVEL LOCKS

In the row level lock a row is locked exclusively so that other cannot modify the row until the transaction holding the lock is committed or rolled back. This can be done by using select..for update clause.

Ex:
    SQL> select * from emp where sal > 3000 for update of comm.;

TABLE LEVEL LOCKS

A table level lock will protect table data thereby guaranteeing data integrity when data is being accessed concurrently by multiple users. A table lock can be held in several modes.

Ø Share lock
Ø Share update lock
Ø Exclusive lock

SHARE LOCK

A share lock locks the table allowing other users to only query but not insert, update or delete rows in a table. Multiple users can place share locks on the same resource at the same time.

Ex:
     SQL> lock table emp in share mode;

SHARE UPDATE LOCK

It locks rows that are to be updated in a table. It permits other users to concurrently query, insert, update or even lock other rows in the same table. It prevents the other users from updating the row that has been locked.

Ex:
     SQL> lock table emp in share update mode;         

EXCLUSIVE LOCK

Exclusive lock is the most restrictive of tables locks. When issued by any user, it allows the other user to only query. It is similar to share lock but only one user can place exclusive lock on a table at a time.

Ex:
     SQL> lock table emp in share exclusive mode;

NOWAIT

If one user locked the table without nowait then another user trying to lock the same table then he has to wait until the user who has initially locked the table issues a commit or rollback statement. This delay could be avoided by appending a nowait clause in the lock table command.

Ex:
     SQL> lock table emp in exclusive mode nowait.

DEADLOCK


A deadlock occurs when tow users have a lock each on separate object, and they want to acquire a lock on the each other’s object. When this happens, the first user has to wait for the second user to release the lock, but the second user will not release it until the lock on the first user’s object is freed. In such a case, oracle detects the deadlock automatically and solves the problem by aborting one of the two transactions.
Share this article :

5 comments:

  1. hi Dayakar,
    Greetings Mate,


    You make learning and reading addictive. All eyes fixed on you. Thank you being such a good and trust worthy guide.


    We have recently migrated from SS2008 (not R2 just SS2008) to SS2017. Now my queries have started running very slow. after a long research/google. I found that this could be a parallelism issue. So after giving below Hint, My queries have started running fine (as normal as it was with SS2008)
    SELECT
    FROM JOINS
    WHERE
    option (recompile, querytraceon 8649) --- the query hint

    Now I have more then 2000 procedures. I cannot update each and every procedure and give above query hint. Is there any way to enable this server level? I already have Max Degree of marallism SET to 8

    But nice Article Mate! Great Information! Keep up the good work!


    Cheers,
    Irene Hynes

    ReplyDelete
  2. Hello There,


    Grazie! Grazie! Grazie! Your blog is indeed quite interesting around SQL Locks I agree with you on lot of points!

    I have one hierarchy query for tables exchange _grp and node.
    Second hierarchy query for tables node and node side.

    Can we combine these two queries into one.

    It was cool to see your article pop up in my Google search for the process yesterday. Great Guide.
    Keep up the good work!

    Thanks a heaps,
    David

    ReplyDelete
  3. Greetings Mate,


    This is indeed great! But I think perhaps you are generally referring #topic which is getting unsustainable.


    I need some help from you experts on this stored procedure..

    There are 4-5 batch procedures that will call this stored procedure. The batch will call the stored procedure with 'I' parameter in the beginning of the batch process. The batch will call the stored procedure with 'O' parameter at the end of the batch process. The stored procedure should write the details into the auditing table.






    But great job man, do keep posted with the new updates.


    Shukran,

    ReplyDelete
  4. Hello There,

    Three cheers to you ! Hooray!!! I feel like I hit the jackpot on Oracle Appplications !

    I am trying to write a PLSQL block that firstly..

    grabs the table name from all_tables where owner='rob1' and rownum =1

    I then want to assign this table_name to a variable.

    I then want to count the number of rows of this table and print to the screen.

    I have pasted my code below. Are you able to reference a variable withing a sql statement as I have?
    DECLARE
    TABLE_HOLDER VARCHAR2(200);
    COUNT_OF_ROWS NUMBER;

    BEGIN

    SELECT TABLE_NAME INTO TABLE_HOLDER FROM ALL_TABLES
    WHERE OWNER ='ROB1'AND ROWNUM=1;
    DBMS_OUTPUT.PUT_LINE(TABLE_HOLDER);

    SELECT COUNT(*)INTO COUNT_OF_ROWS FROM TABLE_HOLDER;
    DBMS_OUTPUT.PUT_LINE(COUNT_OF_ROWS);
    END;

    It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
    Keep up the good work!

    Kind Regards,
    Preethi.

    ReplyDelete
  5. Please make use of Dynamic SQL for the select statement having the table name in the variable.

    Thanks,
    Uttam

    ReplyDelete