Prevent locked state (row lock + alter table)

From: Durumdara <durumdara(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Prevent locked state (row lock + alter table)
Date: 2018-10-05 12:55:01
Message-ID: CAEcMXhmN==FVtJBFUKPr8QMtQbfj-dKsbiJi-mvH+gYyHVcaPQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Dear Members!

We have a big database somewhere with more than 150 active connection.

Sometimes we experienced a "lock" situation, the client's and programs are
halted on a point.

We investigated a little, and we recognized that this problem seems to be
appears when we modify a table (alter) on high usage (morning-noon).

This table called "art".

For not modify by paralell users, we use row lock in transaction.

For example:

try
StartTrans;
try
set lock timeout to 30 sec
select * from art for update where id = ?
Modifications
Commit
catch error
Rollback
finally
set lock timeout to default

As I think this lock interferes with the alter table on high usage. On
10-20 live connection (night) it didn't happen.

The alter example:

alter table art add blabla int;

As we experienced the whole system stopped on queries (they wait).

From previously opened PGAdmin I can exec a Query to other table, so PG is
working.

Please help me a little:

Do you have any experince on same problem?

Which session (local) timeout parameter I need to set and limit to lower
for avoid these problem?

How can I detect the conflict (can I exec a query which show me, what
happens) when the problem is on?

Thank you for your any help!

dd

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ravi Krishna 2018-10-05 14:05:56 COPY from a remote machine in Datastage
Previous Message James Keener 2018-10-05 12:45:57 Re: Does postgreSQL community edition supports data distribution across nodes