4 Replies Latest reply: Apr 17, 2013 9:30 AM by 987705 RSS

    for update causing problem

    987705
      Session#1
      select * from emp where salary <= 3000 for update;

      This returned me 3 records:

      emp_id dept_id salary
      ==================
      2 12 1000
      3 12 2000
      4 12 3000

      Session#2
      select * from emp where salary <= 3000 for update;

      This session is now waiting for release by session#1.

      Then I did:
      Session#1
      update emp set salary = 2000 where emp_id = 5;

      Then:
      commit;

      Session#2:
      The waiting query was finished and returned me:
      emp_id dept_id salary
      ==================
      2 12 1000
      3 12 2000
      4 12 3000

      The record emp_id = 5 was not returned though it is having salary <= 3000.
      The Session#2 not showing the records which it should show.

      Any thoughts ?
        • 1. Re: for update causing problem
          DK2010
          I believe it not any issue, That is what ACID property in RDBMS
          You have to run the Query again on the session#2 for Desired result.
          • 2. Re: for update causing problem
            Harvey
            Session#1
            select * from emp where salary <= 3000 for update;
            
            This returned me 3 records:
            
            emp_id dept_id salary
            ================== 
            2 12 1000
            3 12 2000
            4 12 3000
            
            Session#2
            select * from emp where salary <= 3000 for update;
            
            This session is now waiting for release by session#1.
            
            Then I did:
            Session#1
            update emp set salary = 2000 where emp_id = 5;
            
            Then:
            commit;
            
            Session#2:
            The waiting query was finished and returned me:
            emp_id dept_id salary
            ================== 
            2 12 1000
            3 12 2000
            4 12 3000 
            
            The record emp_id = 5 was not returned though it is having salary <= 3000.
            The Session#2 not showing the records which it should show.
            
            Any thoughts ?
            Oracle will lock only those rows which it can select at that point in time SCN.

            Step 1:
            When you issued select * from emp where salary <= 3000 for update; session locked the three rows for update this is exclusive row lock at a particular point in time (SCN suppose 1003)

            Step 2:
            Then you issued the second command from session#2, (select * from emp where salary <= 3000 for update;) At another SCN suppose 1007, because session#1 have locks already on the rows and session#2 is not running SQL with nowait hence the session#2 will wait till session#1 releases lock with either rollback or commit;

            Step 3:
            Now in session#1 you issue (update emp set salary = 2000 where emp_id = 5;) and (commit) , by committing you are releasing the lock on three rows, suppose this is SCN 1015. Note SCN is increasing as time is passing.

            Step 4:
            Session#2, the locks on the particular rows are gone and session#2 will return the results from SCN 1007. This data is read from undo log for the read consistency. This session will select the data which was available at SCN 1007


            In case you update the locked rows in session#1 in steps 3 like (update emp set salary = 4000 where emp_id = 2;) and (Commit), then session#2 will not show this row. But if on first session you do (update emp set salary = 100 where emp_id = 2;) and commint in steps 3 then this new row will be included.

            Edited by: Harvey on Apr 17, 2013 9:41 AM
            • 3. Re: for update causing problem
              987705
              It was helpful.
              Thanks.
              • 4. Re: for update causing problem
                987705
                Thanks for detail reply. Its clear now.