Обсуждение: Problem with row-level lock
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
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
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
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