Say I have employee table with 100 records . Out of 100 records, 60 records having
middle_name as null
Say in two separate sessions(say Session_A, Session_B), I execute below query at same time
update employee middle_name ='test_middle_name' where middle_name is null
My question is is there a possibility Session_A can work on X records where Session_B work on Y records where X+Y=60 on is it always single session will get lock on all 60 records and there will be nothing to process for another session ?
I understand both process will try to get the exclusive row level lock on applicable rows but process will try to acquire those lock row by row or in one go.
My understanding is It is possible that Session_A can work on X records where Session_B work on Y records where X+Y=60. Reason is each process will get some CPU cycles(say in round robin fashion) and pick the records based on available records whose middle_name is null