Re: Locking concurrency: select for update vs update - Mailing list pgsql-performance

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