Re: Locking concurrency: select for update vs update

[Streamsoft - Mirek Szajowski <m.szajowski@xxxxxxxxxxxxx>]

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

W dniu 2016-06-07 o 09:35, Szymon Lipiński pisze:

On 7 June 2016 at 09:31, Streamsoft - Mirek Szajowski <m.szajowski@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
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

--

z poważaniem

Mirek Szajowski
Projektant-programista
Tel: 663 762 690
m.szajowski@xxxxxxxxxxxxx

Streamsoft
65-140 Zielona Góra, ul.Kossaka 10
NIP: 929-010-00-96, REGON: 970033184
Tel: +48 68 45 66 900, Fax: +48 68 45 66 933
www.streamsoft.pl

Uwaga: Treść niniejszej wiadomości może być poufna i objęta zakazem jej ujawniania. Jeśli czytelnik lub odbiorca niniejszej wiadomości nie jest jej zamierzonym adresatem, pracownikiem lub pośrednikiem upoważnionym do jej przekazania adresatowi, niniejszym informujemy że wszelkie rozprowadzanie, dystrybucja lub powielanie niniejszej wiadomości jest zabronione. Odbiorca lub czytelnik korespondencji, który otrzymał ja omyłkowo, proszony jest o zawiadomienie nadawcy i usuniecie tego materiału z komputera. Dziękujemy. Streamsoft.

Note: The information contained in this message may be privileged and confidential and protected from disclosure. If the reader or receiver of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you received this in error, please contact the sender and delete the material from any computer. Thank you. Streamsoft.