Re: Reg: Question about concurrency/locking

rkalyankumar(at)aol(dot)in writes:
> There will be one block/page/buffer allocated for table t1 and the
> values are inserted to that block. When a commit is issued after
> insert, the data is guranteed to be written to the datafile on the
> disk. Now when the couple of updates are done from 2 sessions opened by
> same user (user1), the page/buffer is found in the buffer/page frames
> in the memory & when the update from session 1 is done - an exclusive
> lock (update lock?) is held on the page - is this correct? Then when a
> second update from session 2 is issued how does the update goes without
> blocking, since all the four records are in the same physical block &
> hence in the page/buffer frame in the memory.

I think you are imagining that the page locks have something to do with
data accessibility, which they don't. In Postgres, an exclusive page
lock is only held long enough to physically make the bits change within
the page. Consistency and commit/rollback semantics are achieved using
MVCC, which stamps every tuple version with its insert and (eventually)
delete transaction IDs. Whether another transaction sees a tuple
version as good depends on whether it considers those transaction IDs
committed or not.

This costs space (since every update requires storing a new version of
the tuple) but it essentially eliminates locking issues of the kind you
are worried about.

The "Concurrency Control" chapter of the docs has more detail.

regards, tom lane