Обсуждение: Prevent locked state (row lock + alter table)
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.
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
Hello!
We have found some solutions here:
Best regards
dd
Durumdara <durumdara@gmail.com> ezt írta (időpont: 2018. okt. 5., P, 14:55):
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.