Insert Should Aquire Only Exclusive Rowlock. Why Does Insert Block Select With ( Updlock, Rowlock )?

Here is the situation i am stuck with, see the example first and below explained the problem:

-- 'SESSION A

create table foo (

id integer,

pid integer,

data varchar(10)

);

begin transaction

insert into foo values ( 1, 1, 'foo' )

insert into foo values ( 2, 1, 'bar' )

insert into foo values ( 3, 1, 'bozo' )

insert into foo values ( 4, 2, 'snafu' )

insert into foo values ( 5, 2, 'rimrom' )

insert into foo values ( 6, 2, 'blark' )

insert into foo values ( 7, 3, 'smeg' )

commit transaction

create index foo_id_idx on foo ( id )

create index foo_pid_idx on foo ( pid )

begin transaction

insert into foo values ( 9, 3, 'blamo' )

-- 'SESSION B

begin transaction

select id, data from foo with ( updlock, rowlock ) where id = 5;



-- Problem:

-- Uncommitted transaction in session A, with insert into table FOO, aquires lock on index foo_pid_idx which BLOCKS select with ( updlock, rowlock ) in session B.

-- Insert should aquire only exclusive rowlock. Why does insert block select with ( updlock, rowlock )?



Appreciate your help,

Rajesh.