Thread: Problem with row-level lock

Problem with row-level lock

From
Job
Date:
Hi guys,

i am struggling with a problem due, sometime, to a double concurrent update on the same row of a table.

Since the client application cannot be control, i need to manage - in order to avoid this situation - the lock of a
recorddue to two updates at the same time. 
Is there way to force serializing, i don't know how, the updates on the same row in order to avoid this locks?

Thank you, cheers!

FC



Re: Problem with row-level lock

From
Adrian Klaver
Date:
On 6/19/19 9:06 AM, Job wrote:
> Hi guys,
> 
> i am struggling with a problem due, sometime, to a double concurrent update on the same row of a table.

What is the error message you get?

> 
> Since the client application cannot be control, i need to manage - in order to avoid this situation - the lock of a
recorddue to two updates at the same time.
 
> Is there way to force serializing, i don't know how, the updates on the same row in order to avoid this locks?
> 
> Thank you, cheers!
> 
> FC
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com



R: Problem with row-level lock

From
Job
Date:
Dear Adrian,

thank you for the reply!

>> i am struggling with a problem due, sometime, to a double concurrent update on the same row of a table.

>What is the error message you get?

We have no error, but the two queries, in "Lock wait", keep stucked for some minutes before completed.
Are there any parameters useful to "serialize" in some way two different Update queries - for the same field in the
samerow - arriving concurrently? 

I was looking at the lock_timeout parameter: could it be what i am looking for?

Thank you!
Franco


Re: R: Problem with row-level lock

From
Adrian Klaver
Date:
On 6/21/19 1:40 AM, Job wrote:
> Dear Adrian,
> 
> thank you for the reply!
> 
>>> i am struggling with a problem due, sometime, to a double concurrent update on the same row of a table.
> 
>> What is the error message you get?
> 
> We have no error, but the two queries, in "Lock wait", keep stucked for some minutes before completed.
> Are there any parameters useful to "serialize" in some way two different Update queries - for the same field in the
samerow - arriving concurrently?
 
> 
> I was looking at the lock_timeout parameter: could it be what i am looking for?

If I understood your OP you do not have control of what the client, 
correct? If that is the case it makes what you want to achieve more 
difficult. To understand what can be done you will want to take a look at:

The locking choices in Postgres.
https://www.postgresql.org/docs/11/mvcc.html

The lock timeouts available(though I don't think they apply to your case)
https://www.postgresql.org/docs/11/runtime-config-locks.html

How to change the transaction modes
https://www.postgresql.org/docs/11/sql-set-transaction.html

> 
> Thank you!
> Franco
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com