Home » SQL & PL/SQL » SQL & PL/SQL » Difference between share & Exclusive Locks (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
Difference between share & Exclusive Locks [message #610298] Wed, 19 March 2014 04:18 Go to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member

Hi

Could anybody please explain the difference between Shared & Exclusive locks with some
examples .I had gone trough the some of the documents. Still have some confusion

Please help me

Thanks
Sai Pradyumn

Report message to a moderator

Re: Difference between share & Exclusive Locks [message #610304 is a reply to message #610298] Wed, 19 March 2014 05:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Database Concepts
Chapter 9 Data Concurrency and Consistency

Report message to a moderator

Re: Difference between share & Exclusive Locks [message #610306 is a reply to message #610304] Wed, 19 March 2014 05:27 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member

Hi Michel

I had gone through it . Its very very confusing to me.
Could you please explain with some examples

Thanks
Sai Pradyumn

Report message to a moderator

Re: Difference between share & Exclusive Locks [message #610308 is a reply to message #610306] Wed, 19 March 2014 05:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What don't you understand? Explain it in details and either you will understand and we will correct you in your misunderstanding.
So first: what is for you a shared lock? What is for you an exclusive lock?

Note that the I pointed you to explain this in details WITH examples.

Report message to a moderator

Re: Difference between share & Exclusive Locks [message #610311 is a reply to message #610308] Wed, 19 March 2014 06:11 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member
Hi Michel ,

Thanks for your reply

I tried the following steps 


Sessio1  : LOCK TABLE scott.emp IN  share MODE nowait; 

With out Commit /roll back  

Session2 : update    scott.emp   set empno =  1234   where empno  =  7369  

Its not at all executing  .


The same thing happening for EXCLUSIVE mode also 


Sessio1  : LOCK TABLE scott.emp IN  EXCLUSIVE  MODE nowait; 

With out Commit /roll back  

Session2 : update    scott.emp   set empno =  1234   where empno  =  7369  

Its not at all executing  .


I am not getting the difference between these two .If I execute either commit/rollback in Seeion1 for these scenarios , Session2 statement was executing successfully

Please let me know if I am wrong

Thanks
Sai Pradyumn

Report message to a moderator

Re: Difference between share & Exclusive Locks [message #610344 is a reply to message #610311] Wed, 19 March 2014 08:36 Go to previous message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Case 1: the second second tries to get an exclusive lock on a row as there is a shared lock on the whole table it can't get it as exclusive is incompatible with shared.

Case 2: This is worst, the first session has an exclusive lock on the whole table so no other session can get any kind of lock.

To have a quick view of compatibility between the locks, read the following: Summary of Table Locks

Report message to a moderator

Previous Topic: I Object to that Cursor record!
Next Topic: string to number conversion
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Apr 23 16:30:02 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.