Postgresql / PPAS/Lock
-
[PostgreSQL] LOCK TABLE table_name IN APostgresql / PPAS/Lock 2015. 1. 28. 16:42
기본적인 사용법은 다음과 같다.BEGIN WORK;LOCK TABLE table_name IN ACCESS EXCLUSIVE MODE;SELECT * FROM table_name WHERE id=10;Update table_name SET field1=test WHERE id=10;COMMIT WORK;ACCESS SHAREACCESS EXCLUSIVE 락 모드와 충돌이 난다. SELECT 와 ANALYZE 명령어가 사용될때 이 잠금 모드가 사용된다. 일반적으로 오직 읽기를 수행하거나, 갱신이 없는 명령어를 수행할때는 이 모드를 획득한다고 보면 된다.ROW SHAREEXCLUSIVE, ACCESS EXCLUSIVE 모드와 충돌이 난다. SELECT FOR UPDATE 명령시에 타겟이 되는 테이블에 이 잠..
-
PostgreSQL ShareLock DeadlocksPostgresql / PPAS/Lock 2014. 11. 26. 14:23
PostgreSQL ShareLock DeadlocksHome → Postgresql → PostgreSQL ShareLock Deadlocks We recently encountered the following error which warranted further investigation:ERROR: deadlock detected DETAIL: Process 4312 waits for ShareLock on transaction 1426407; blocked by process 2583. Process 2583 waits for ShareLock on transaction 1426408; blocked by process 4312. HINT: See server log for query details..
-
[PPAS / Postgresql] pg_locks 테이블을 참고하여 Transaction 사용하는 테이블 확인Postgresql / PPAS/Lock 2014. 11. 18. 17:30
blocking_statement 필드와 blocking_pid 를 확인하여어떤 테이블이 트랜잭션을 쓰고있는지 확인.focus=# select relation::regclass from pg_locks where pid=2782; relation -----------------------focus.pk_tb_samplefocus.ix_tb_sample_01 focus.tb_sample DUALfocus.ix_tb_sample_02focus.ix_tb_sample_03focus.ix_tb_sample_04
-
[PPAS / Postgresql] SQL 경합 현상 분석 쿼리Postgresql / PPAS/Lock 2014. 11. 18. 17:26
- SQL 경합 현상 분석 쿼리 SELECT bl.pid AS blocked_pid, a.usename AS blocked_user, ka.current_query AS blocking_statement,TO_CHAR(NOW() - ka.query_start, 'HH24:MI:SS') AS blocking_duration,kl.pid AS blocking_pid, ka.usename AS blocking_user,a.current_query AS blocked_statement,TO_CHAR(NOW() - a.query_start, 'HH24:MI:SS') AS blocked_durationFROM pg_catalog.pg_locks blJOIN pg_catalog.pg_stat_activity a ON..