Thread: Locking concurrency: select for update vs update

Hello,

I have two tables phone_number and phone_number_type

When I start transaction and insert phone_number using FK from
phone_number_type. Then I can during another TX update row from
phone_number_type, but I can't execute select for update on it.

In db stats I see during inserInto AccessShareLock, during update
RowExclusieLock but during select for update AccessExclusieLock.

Why I can't execute 'select for update' but I can update???? We often
use 'select for update' to avoid update the same record in differents TX
but I don't understand why this block another tx from using this record
as FK


Best regards
Mirek

It means that second TX hangs/wait on this sql

code

FIRST TX

INSERT INTO phone_number( id_phone_number,id_phone_number_type)    VALUES (1,500);

SECOND TX

select * from phone_number_type  WHERE id_phone_number_type=500 for update //hangs/wait to TX with insert into ends

but this works fine
  UPDATE phone_number_type SET val=val+1 WHERE id_phone_number_type=500

On 7 June 2016 at 09:31, Streamsoft - Mirek Szajowski <m.szajowski@streamsoft.pl> wrote:

Hello,

I have two tables phone_number and phone_number_type

When I start transaction and insert phone_number using FK from phone_number_type. Then I can during another TX update row from phone_number_type, but I can't execute select for update on it.

In db stats I see during inserInto AccessShareLock, during update RowExclusieLock but during select for update AccessExclusieLock.

Why I can't execute 'select for update' but I can update???? We often use 'select for update' to avoid update the same record in differents TX but I don't understand why this block another tx from using this record as FK


Best regards
Mirek


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

What do you mean by " can't execute select for update on it"? Can you show an example code, and the error you get?

--

    regards Szymon Lipiński

Streamsoft - Mirek Szajowski <m.szajowski@streamsoft.pl> writes:
> Why I can't execute 'select for update' but I can update?

In recent PG versions, the lock held due to having inserted an FK
dependent row effectively only locks the key fields of the parent row.
UPDATE can tell whether you're trying to change the row's key fields,
and it will proceed if you aren't.  SELECT FOR UPDATE has to lock the
whole row (since it must assume you might be intending to change any
fields of the row); so it blocks until the FK lock goes away.

            regards, tom lane

Thanks

after your description I found select name from phone_number_type  WHERE id_phone_number_type=4 for NO KEY update (Postgresql 9.3 )

Streamsoft - Mirek Szajowski <m.szajowski@streamsoft.pl> writes:

Why I can't execute 'select for update' but I can update?

In recent PG versions, the lock held due to having inserted an FK
dependent row effectively only locks the key fields of the parent row.
UPDATE can tell whether you're trying to change the row's key fields,
and it will proceed if you aren't.  SELECT FOR UPDATE has to lock the
whole row (since it must assume you might be intending to change any
fields of the row); so it blocks until the FK lock goes away.
		regards, tom lane