On Sat, Jul 1, 2017 at 8:55 PM, rajan <vgmonnet(at)gmail(dot)com> wrote:
> Thanks, Jeff. That helps understanding it 50%.
>
> *Session 2* fails to UPDATE the record which is in *(0,2)* and this tuple
> is
> marked for deletion. It means that *(0,2) never exists* when Session 2 is
> trying to perform the update.
>
That it never exists is an appearance presented to the user. The database
system works hard to maintain that illusion but the database system itself
sees through the illusion. It blocks on (0,2) waiting for session 1 to
commit, and then once that happens session 2 goes and finds the new version
of that row ((0,4) in this case) and locks it. If you use pageinspect, you
can see that (0,2) has left a pointer behind pointing to (0,4) to make it
easy to find.
Cheers,
Jeff