Mar
19
postgres LOCK TABLE waiting 状态导致无法插入更新DB的解决
当执行一条时间比较长的Sql文时,会引起DB挂起。
①查询Pg的状态方法:
# ps axwf | grep 'postgres'
20152 ? S 34:54 /usr/local/pgsql/bin/postgres
20154 ? Ss 54:20 \_ postgres: writer process
20155 ? Ss 41:50 \_ postgres: wal writer process
20156 ? Ss 192:31 \_ postgres: autovacuum launcher process
20157 ? Ss 465:52 \_ postgres: stats collector process
8493 ? Ss 0:00 \_ postgres: postgres WildCard ::1(42985) SELECT
8495 pts/3 S+ 0:00 | \_ grep postgres
②table lock waiting, select waiting产生测试方法,postgres select 执行延迟:
select pg_sleep(50),(select id from zhoz_table) as zhoz_id;
③解决方法:
lock部分mode变换
LOCK TABLE %s IN ACCESS EXCLUSIVE MODE
↓
LOCK TABLE %s IN ROW EXCLUSIVE MODE
①查询Pg的状态方法:
# ps axwf | grep 'postgres'
20152 ? S 34:54 /usr/local/pgsql/bin/postgres
20154 ? Ss 54:20 \_ postgres: writer process
20155 ? Ss 41:50 \_ postgres: wal writer process
20156 ? Ss 192:31 \_ postgres: autovacuum launcher process
20157 ? Ss 465:52 \_ postgres: stats collector process
8493 ? Ss 0:00 \_ postgres: postgres WildCard ::1(42985) SELECT
8495 pts/3 S+ 0:00 | \_ grep postgres
②table lock waiting, select waiting产生测试方法,postgres select 执行延迟:
select pg_sleep(50),(select id from zhoz_table) as zhoz_id;
③解决方法:
lock部分mode变换
LOCK TABLE %s IN ACCESS EXCLUSIVE MODE
↓
LOCK TABLE %s IN ROW EXCLUSIVE MODE
作者:@Everyday NetLog
地址:http://log.zhoz.com/read.php?772
版权所有。转载时必须以链接形式注明作者和原始出处及本声明!