11 Replies Latest reply: May 17, 2008 9:34 AM by Aman.... RSS

    exclusive lock mode vs shared lock mode

    615452
      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
          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....
            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
              "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
                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....
                  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
                    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....
                      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
                        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....
                          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
                            thanks a ton Aman and Hans...Now i understood
                            • 11. Re: exclusive lock mode vs shared lock mode
                              Aman....
                              Your welcome:-)
                              Aman....