How to concurrency will be handled in this case?

Question

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:-

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

Answer 1

The processes can theoretically work concurrently on different records, row locks are obtained as needed not in advance. However, in this case they are bound to run into each other sooner or later. If both have locked at least one record when that happens they will deadlock (both wait for the other to release a lock that it needs) and the database will kill one. Alternatively one will get the locks first and the other will wait until it commits; then it will update the records again.

In other words you can never end up with a scenario where A has updated some records and B some records. A will either update all or fail (due to deadlock/timeout) and B will also either update all or fail.

EDIT: if you do want to share load in this way you can get that effect using a select ... for update skip locked. Then locked records are simply ignored and the sessions can split the work, often quite useful in polling scenarios. Or use a parallel hint for the update and make a single session run in parallel.