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
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 |