Prevent locked state (row lock + alter table)

Started by Durumdaraover 7 years ago2 messagesgeneral
Jump to latest
#1Durumdara
durumdara@gmail.com

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

#2Durumdara
durumdara@gmail.com
In reply to: Durumdara (#1)
Re: Prevent locked state (row lock + alter table)

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.