Re: [GENERAL] Re: have trouble understanding xmin and xmax withupdate operations from two different sessions - Mailing list pgsql-general

From Jeff Janes
Subject Re: [GENERAL] Re: have trouble understanding xmin and xmax withupdate operations from two different sessions
Date
Msg-id CAMkU=1wP1jeHk1jQCvEhsnRC=5FwbQZ_ALkGumv4k3K8hHmR9A@mail.gmail.com
Whole thread Raw
In response to [GENERAL] Re: have trouble understanding xmin and xmax with update operationsfrom two different sessions  (rajan <vgmonnet@gmail.com>)
Responses [GENERAL] Re: have trouble understanding xmin and xmax with update operationsfrom two different sessions
List pgsql-general
On Sat, Jul 1, 2017 at 8:55 PM, rajan <vgmonnet@gmail.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

pgsql-general by date:

Previous
From: Jerry Regan
Date:
Subject: [GENERAL] Using 'WITH SELECT' Results Do Not Match 'SELECT FROM 'Results
Next
From: twoflower
Date:
Subject: Re: [GENERAL] Text search dictionary vs. the C locale