This content has been marked as final. Show 4 replies
Oracle will lock only those rows which it can select at that point in time SCN.
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 ?
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)
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;
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.
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