Re: Locking concurrency: select for update vs update

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(dot)szajowski(at)streamsoft(dot)pl <mailto:m(dot)szajowski(at)streamsoft(dot)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(at)postgresql(dot)org
> <mailto:pgsql-performance(at)postgresql(dot)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

--

z poważaniem

*Mirek Szajowski*
Projektant-programista
Tel: 663 762 690
m(dot)szajowski(at)streamsoft(dot)pl <mailto:m(dot)szajowski(at)streamsoft(dot)pl>

*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 <http://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.