This discussion is archived
14 Replies Latest reply: Feb 25, 2013 11:04 PM by 987429 RSS

Shared lock vs exclusive lock

987429 Newbie
Currently Being Moderated
Hello
I have a question.
If we have two scott sessions. I am updating a table EMP in session 1. It means it is exclusively locked.It cannot be used by session 2. Then can we use select command on table EMP in session 2.?? This command shoul not work according to me. But it is working.



Reply me.
Thanks in anticipation.
  • 1. Re: Shared lock vs exclusive lock
    sb92075 Guru
    Currently Being Moderated
    984426 wrote:
    Hello
    I have a question.
    If we have two scott sessions. I am updating a table EMP in session 1. It means it is exclusively locked.It cannot be used by session 2. Then can we use select command on table EMP in session 2.?? This command shoul not work according to me. But it is working.
    with Oracle reader do not block writers & writers do not block readers
  • 2. Re: Shared lock vs exclusive lock
    Girish Sharma Guru
    Currently Being Moderated
    I think you are bit confused with shared and exclusive lock. Please read below link where Aman and Hans replied in a good manner :

    exclusive lock mode vs shared lock mode

    Regards
    Girish Sharma
  • 3. Re: Shared lock vs exclusive lock
    987429 Newbie
    Currently Being Moderated
    But in shared / exclusive lock, we have a property that we can't acquire shared lock on data if it is exclusively locked and vice versa. that means readers block writers and vice versa.
    E.g. if T1 is updating a row then how T2 can read that row? If T2 is reading then that is inconsistent data as it will pick the non-updated value of that row until T1 commits.

    Please explain.
    I am having doubts in this topic.
    Thanks
  • 4. Re: Shared lock vs exclusive lock
    sb92075 Guru
    Currently Being Moderated
    984426 wrote:
    But in shared / exclusive lock, we have a property that we can't acquire shared lock on data if it is exclusively locked and vice versa. that means readers block writers and vice versa.
    E.g. if T1 is updating a row then how T2 can read that row? If T2 is reading then that is inconsistent data as it will pick the non-updated value of that row until T1 commits.

    Please explain.
    I am having doubts in this topic.
    Thanks
    you do NOT understand how Oracle works.

    you need to Read The Fine Manual

    http://docs.oracle.com/cd/E11882_01/server.112/e25789/consist.htm#CNCPT221

    http://www.oracle.com/pls/db112/search?remark=quick_search&word=read+consistency&partno=
  • 5. Re: Shared lock vs exclusive lock
    Aman.... Oracle ACE
    Currently Being Moderated
    984426 wrote:
    But in shared / exclusive lock, we have a property that we can't acquire shared lock on data if it is exclusively locked and vice versa. that means readers block writers and vice versa.
    E.g. if T1 is updating a row then how T2 can read that row? If T2 is reading then that is inconsistent data as it will pick the non-updated value of that row until T1 commits.

    Please explain.
    I am having doubts in this topic.
    Thanks
    You need to check back again the basic concepts from the Concepts guide. Your understanding about the Oracle's working is completely wrong.

    In Oracle, the Transaction Isolation Level is set to Read Committed . This means any kind of inconsistent data is not possible to be read by you and for that data , Oracle would create for you a consistent image using the Undo Blocks in which the old image is going to be stored till the time that transaction is not over( and even after that too for some time with conditions apply) . So if T1 is updating a row, T2 can't lock the same row as it would be locked exclusively and the S1 (another select) would be seeing an old and consistent image of that change only as long as it's not committed. What you said doesn't and won't work ever in Oracle.

    Read the first link that's given to you already.

    Aman....
  • 6. Re: Shared lock vs exclusive lock
    987429 Newbie
    Currently Being Moderated
    Thanks for sending me the link. Very helpful. Now I got your point - readers do not block writers and vice versa. Means while we are reading a table or row, that is not locked in Oracle. It is locked at the time of updation .
    Can you tell me the benefits of Two phase locking protocol over Shared/Exclusive lock with example? And how two phase locking works in oracle?

    Thanks in anticipation.
  • 7. Re: Shared lock vs exclusive lock
    987429 Newbie
    Currently Being Moderated
    Actually i was considering the old image of data as inconsistent data. But that is the consistent data.

    Kindly check the following table. Is it correct?
    T1,T2 operating on same data

    T1(Session 1)     T2(Session 2)     Result

    Select query     Select query     Both will work
    Select query     Update query     Both will work
    Update query     Select query     Both will work and T2 picks the old value
    Update query     Update query     T1 will work and T2 will not work until T1 commits
  • 8. Re: Shared lock vs exclusive lock
    Aman.... Oracle ACE
    Currently Being Moderated
    984426 wrote:
    Actually i was considering the old image of data as inconsistent data. But that is the consistent data.

    Kindly check the following table. Is it correct?
    T1,T2 operating on same data
    That's not possible. The row level locking is going to stop your transaction from one session and would make it hanged.
    T1(Session 1)     T2(Session 2)     Result

    Select query     Select query     Both will work
    Select query     Update query     Both will work
    Update query     Select query     Both will work and T2 picks the old value
    Update query     Update query     T1 will work and T2 will not work until T1 commits
    If the update is going to be happening from both the sessions, it won't work and you would get the error ora-0060.

    Aman....
  • 9. Re: Shared lock vs exclusive lock
    987429 Newbie
    Currently Being Moderated
    Thanks.
    Can you tell me the benefits of Two phase locking protocol over Shared/Exclusive lock with example? And how two phase locking works in oracle?
  • 10. Re: Shared lock vs exclusive lock
    987429 Newbie
    Currently Being Moderated
    Kindly check the following problem of PL/SQL

    declare
    choice number;
    a number:=10;
    b number:=30;
    result number;
    begin
    dbms_output.put_line('enter your choice 1 for addition and 2 for subtraction');
    choice=&choice;
    if choice=1 then
    result:=a+b;
    dbms_output.put_line(result);
    else
    result:=a-b;
    dbms_output.put_line(result);
    end if;
    end;

    Output:_

    Enter value of choice: 1
    Enter your choice 1 for addition and 2 for subtraction.
    40


    Question:= I want to print the message first and then user should be asked to enter the value. Kindly tell me the correct code.
    Thanks..
  • 11. Re: Shared lock vs exclusive lock
    Aman.... Oracle ACE
    Currently Being Moderated
    984426 wrote:
    Thanks.
    Can you tell me the benefits of Two phase locking protocol over Shared/Exclusive lock with example? And how two phase locking works in oracle?
    I am not sure that I have heard Two-Phase locking in Oracle.

    Aman....
  • 12. Distributed database system
    987429 Newbie
    Currently Being Moderated
    Hello.
    Kindly explain Distributed database system and its working with example.



    Thanks in anticipation
  • 13. Re: Shared lock vs exclusive lock
    987429 Newbie
    Currently Being Moderated
    Some questions are still pending
  • 14. Questions regarding Decision support system
    987429 Newbie
    Currently Being Moderated
    Can you tell me any decision support system which is currently being used by some organization?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points