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.
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:
https://leopard.in.ua/2016/09/20/safe-and-unsafe-operations-postgresql#.W-7bFEeYWHs
https://dba.stackexchange.com/questions/132851/database-frozen-on-alter-table
Best regards
dd
Durumdara <durumdara@gmail.com> ezt írta (időpont: 2018. okt. 5., P, 14:55):
Show quoted text
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.