This discussion is archived
11 Replies Latest reply: May 17, 2008 7:34 AM by Aman.... RSS

exclusive lock mode vs shared lock mode

615452 Newbie
Currently Being Moderated
Hi friends,
I have gone through the oracle documentation...i did not understand properly about these lock mode...

Exclusive lock mode prevents the associates resource from being shared. This lock mode is obtained to modify data. The first transaction to lock a resource exclusively is the only transaction that can alter the resource until the exclusive lock is released.

Share lock mode allows the associated resource to be shared, depending on the operations involved. Multiple users reading data can share the data, holding share locks to prevent concurrent access by a writer (who needs an exclusive lock). Several transactions can acquire share locks on the same resource.


can anybody explain me with simle example

thanks in advance
  • 1. Re: exclusive lock mode vs shared lock mode
    608334 Newbie
    Currently Being Moderated
    Hi

    The exclusive lock is a lock when one user updating the row.. other user not bat to

    ex: scott is updating empname in emp table for empno=10001,
    the exclusive lock ia generated .. on the row .. that other user can not modify/update the row .. can select the row.

    --Girish                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
  • 2. Re: exclusive lock mode vs shared lock mode
    Aman.... Oracle ACE
    Currently Being Moderated
    Deepa,
    I shall try to answer.
    Exclusive as the word means, "just for you".For example answer me when you update a row in a table, is some one else able to modify the same row?If the answer is No than we proceed.So no one is able to modifythe row other than you means that row is "exclusively " locked for you.Only you own it!
    Shared-well its like as the words says,"shared by all".So you have a table having 10 rows.5 are locked by you by some DML.They can't be locked by some one else,but what about rest of 5 they can be right! Now both of you users have locked one common aka shared resourse and that is the table.So in order to preserve ,maximum concurrency oracle puts your table in the shared lock so that mutiple people can shareably lock but till the time the DML is going on , no one is able to drop,alter or in other words take an exclusive lock over it.
    Hope that helps
    Aman....
  • 3. Re: exclusive lock mode vs shared lock mode
    615452 Newbie
    Currently Being Moderated
    "exclusively " locked for you.Only you own it!
    Ok fine...I mean here if i can login through 2 sessions using same user name...then I think exclusive mode is session dependent ??? (not user dependent)
    So in order to
    preserve ,maximum concurrency oracle puts your table
    in the shared lock so that mutiple people can
    shareably lock but till the time the DML is going on
    Multiple people can sharebly lock means......

    I did not understand properly this setence Aman....

    thanks for your patience........
  • 4. Re: exclusive lock mode vs shared lock mode
    608334 Newbie
    Currently Being Moderated
    Illustrate a SHARE lock on a primary key table when going to perform inserts into a foreign key table: BEGIN WORK;

    LOCK TABLE films IN SHARE MODE;
    SELECT id FROM films
    WHERE name = 'Star Wars: Episode I - The Phantom Menace';
    Do ROLLBACK if record was not returned
    INSERT INTO films_user_comments VALUES
    (_id_, 'GREAT! I was waiting for it for so long!');
    COMMIT WORK;
  • 5. Re: exclusive lock mode vs shared lock mode
    Aman.... Oracle ACE
    Currently Being Moderated
    Deepa,
    Yes as two Scott "sessions" who can access the same EMP table, you will see a exclusive lock happening for a particular row eg like if you update reord of Scott user and try to lock the same row from the other session,it will hang means you will be in queue to access the same row.Yes locks are acquired by sessions as one user can have multiple sessions.
    Pick up the same example, how many people are simultaneously accessing one table,2 sessions of Scott right? Means EMP table is shareably locked by 2 sessions.
    HTH
    Aman....
  • 6. Re: exclusive lock mode vs shared lock mode
    615452 Newbie
    Currently Being Moderated
    thanks aman....

    in what scenarios...share locked mode is used..

    I mean...exclusive lock will be automatically held, when one transaction is updating one row in a table...until unless it is completed, no other user can update this row....

    sorry for any mis understandings....
  • 7. Re: exclusive lock mode vs shared lock mode
    Aman.... Oracle ACE
    Currently Being Moderated
    Deepa,
    In Oracle locking is automatic. Oracle itself will put the table into the particular lock mode depending upon the operation involved. Share mode , as I explained is to make sure that locking is not going to stop or limit the concurrency.In oracle , the minimum locking level is the row level.Oracel neither goes for page level ( table lock) nor it escalates the locks from row level to page level as in Oracle, there is no such thing called "lock Manager" whic is there in other databases.
    So in the answer for your question,both the lock types will be automatically held when the transaction is started.

    Aman....
  • 8. Re: exclusive lock mode vs shared lock mode
    hans forbrich Oracle ACE Director
    Currently Being Moderated
    thanks aman....

    in what scenarios...share locked mode is used..
    A way of letting other sessions know that a session is using the object, so there may be some (self-imposed) restrictions on how others can.should use it.

    Some examples,

    - if you are using a view, you would be annoyed should that view be dropped while you are using it. So the simple act of using the view should raise a flag (eg: shared lock) indicating the view is in use and should not be altered.

    - if you are reading from a table, you might be unhappy if the table structure changed because someone dropped a column. A "Please don't mess with the structure, but go ahead and do row updates (or other reads)" lock will maintain your level of happiness.
  • 9. Re: exclusive lock mode vs shared lock mode
    Aman.... Oracle ACE
    Currently Being Moderated
    Deepa,
    In addition to the excellent example given by Hans, you may find this ppt helpful, result of googling.
    https://www.indiana.edu/~dbateam/resources/tips/oracle_locking.ppt
    Aman....
  • 10. Re: exclusive lock mode vs shared lock mode
    615452 Newbie
    Currently Being Moderated
    thanks a ton Aman and Hans...Now i understood
  • 11. Re: exclusive lock mode vs shared lock mode
    Aman.... Oracle ACE
    Currently Being Moderated
    Your welcome:-)
    Aman....