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