Thread: Reg: Question about concurrency/locking

Hi,

I have a doubt referring to following scenario:

create table t1 (id integer primary key,desc varchar(10));

I insert 4 values as below:

id                 val
---------------------------------------
1                   one
2                   two
3                   three
4                   four

Note that the auto commit transaction for the session is off. I did 
these inserts from user1 (session1). Now I opened another session 
(session 2) of same user user1. I issued a update statement from 
session 1 & from session 2 respectively as below:

Session 1: update t1 set val='ONE' where id=1;
Session 2: update t1 set val='TWO' where id=2;

Note that I haven's issued commit on either sessions. Both sessions 
have auto commit off.

Now considering code internals for the above:

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. The second update also 
need an exclusive lock before changing the record. Since the page is 
already locked by session 1 in exclusive (update?) mode how does 
session 2 succeed with getting it's own exclusive lock while session 1 
is already holding one and haven't issued a commit/rollback yet?

Kindly clarify this & please provide me pointers into the wonderful 
postgresql code, where it has been handled.

Thanks in advance.



Regards
Kalyankumar Ramaseshan

________________________________________________________________________
You are invited to Get a Free AOL Email ID. - http://webmail.aol.in

rkalyankumar@aol.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