|
Getting your Trinity Audio player ready...
|
 |
Incredibly enough, Redshift suffers from Lock and Block problems in the same way as any other transactional bank.
Like any cloud post, at the time of this publication, Redshift doesn't have an interface that monitors Lock and Block, it monitors active connections, querys running but not lock and block.
The query for monitoring Redshift is as follows:
select a.txn_owner, a.txn_db, a.xid, a.pid, a.txn_start, a.lock_mode, a.relation as table_id, nvl (trim (c.” name”), d.relname) as tablename, a.granted, b.pid as blocking_pid, datediff (s, a.txn_start, getdate ()) /86400||' days '||datediff (s, a.txn_start, getdate ()) %86400/3600||' hrs '||datediff (s, a.txn_start, getdate () date ()) %3600/60||' mins '||datediff (s, a.txn_start, getdate ()) %60||' secs' as txn_duration
from svv_transactions to
left join (select pid, relation, granted from pg_locks group by 1,2,3) b
on a.relation=b.relation and a.granted='f' and b.granted='t'
left join (select * from stv_tbl_perm where slice=0) c
on a.relation=c.id
left join pg_class and on a.relation=d.oid
where a.relation is not null;
and to kill the process
select pg_terminate_backend (PID);
the result should come as “1"
You can find more information on the link below:
