(19c)
select sid,type,lmode,request,ctime from v$lock where type in ('TX','TM');
select * from v$locked_object;
select * from dba_objects where object_id = 20391;
select
t1.sid,t1.serial#,t1.username,t1.osuser,t1.status,t1.machine,t1.program,t1.sql_id,t1.prev_sql_id
,t2.type,t2.lmode,t2.request,t2.ctime
,t3.process,t3.locked_mode
,t4.object_name
from v$session t1
inner join v$lock t2
on t1.sid = t2.sid
inner join v$locked_object t3
on t1.sid = t3.session_id
inner join dba_objects t4
on t3.object_id = t4.object_id
order by t1.sid,t1.serial#
;
http://d.hatena.ne.jp/sh2/20090618
(5.6)
select * from information_schema.INNODB_TRX order by trx_id\G
select * from information_schema.INNODB_LOCKS order by lock_id\G
select * from information_schema.INNODB_LOCK_WAITS \G
SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread,
TIMESTAMPDIFF(SECOND, r.trx_wait_started, CURRENT_TIMESTAMP) AS wait_time,
r.trx_query AS waiting_query,
l.lock_table AS waiting_table_lock,
b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread,
SUBSTRING(p.host, 1, INSTR(p.host, ':') - 1) AS blocking_host,
SUBSTRING(p.host, INSTR(p.host, ':') +1) AS blocking_port,
IF(p.command = "Sleep", p.time, 0) AS idle_in_trx,
b.trx_query AS blocking_query
FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS b ON b.trx_id = w.blocking_trx_id
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS r ON r.trx_id = w.requesting_trx_id
INNER JOIN INFORMATION_SCHEMA.INNODB_LOCKS AS l ON w.requested_lock_id = l.lock_id
LEFT JOIN INFORMATION_SCHEMA.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id
ORDER BY wait_time DESC\G
SELECT CONCAT('thread ', b.trx_mysql_thread_id, ' from ', p.host) AS who_blocks,
IF(p.command = "Sleep", p.time, 0) AS idle_in_trx,
MAX(TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW())) AS max_wait_time,
COUNT(*) AS num_waiters
FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS b ON b.trx_id = w.blocking_trx_id
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS r ON r.trx_id = w.requesting_trx_id
LEFT JOIN INFORMATION_SCHEMA.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id
GROUP BY who_blocks ORDER BY num_waiters DESC\G
https://qiita.com/hmatsu47/items/607d176e885f098262e8
(5.7)
select * from information_schema.INNODB_TRX order by trx_id\G
select * from information_schema.INNODB_LOCKS order by lock_id\G
select * from information_schema.INNODB_LOCK_WAITS \G
SELECT * FROM sys.innodb_lock_waits\G
(8.0)
select * from performance_schema.data_locks\G
select * from performance_schema.data_lock_waits\G
select * from performance_schema.metadata_locks\G
http://blog.ko-atrandom.com/?p=134
https://qiita.com/myzkyy/items/d456747f2c70a748587a
select l.pid, db.datname, c.relname, l.locktype, l.mode, l.granted
from pg_locks l
left join pg_class c on l.relation = c.oid
left join pg_database db on l.database = db.oid
order by l.pid;
※pg_locks.granted →ロックが保持されている場合は真、ロックが待ち状態の場合は偽
select
lock.locktype
, class.relname
, lock.pid
, lock.mode
from pg_locks lock
left outer join pg_stat_activity act
on lock.pid = act.pid
left outer join pg_class class
on lock.relation = class.oid
where not lock.granted
order by lock.pid;
--行ロック詳細確認
create extension pgrowlocks;
select * from tab1 as a, pgrowlocks('tab1') as p
where p.locked_row = a.ctid;
--ブロックしているプロセス確認(9.6)
select pg_blocking_pids(18758);
--ロック解除
SELECT pg_cancel_backend(18694);
SELECT * FROM sys.dm_tran_locks;
go
SELECT *
FROM sys.dm_tran_locks
WHERE resource_associated_entity_id
=(SELECT TOP 1 object_id FROM sys.partitions WHERE OBJECT_NAME(object_id)='tab1');
go
select s.session_id, s.last_request_end_time, ISNULL(r.status,s.status) as status,
s.database_id, r.blocking_session_id, r.wait_type, r.wait_time,
r.wait_resource, s.open_transaction_count
from sys.dm_exec_sessions s
left join sys.dm_exec_requests r
on r.session_id = s.session_id
where s.is_user_process = 1
;
select object_name(p.object_id) as [object_name], p.index_id,
i.name as index_name, partition_number
from sys.partitions p
inner join sys.indexes i
on i.object_id = p.object_id
and i.index_id = p.index_id
where p.hobt_id = 72057594054836224
;
(2019)
select * from sys.dm_db_page_info(8,1,157921,'LIMITED');
※8,1,157921は「wait_resource」を指定する
select object_name(t3.object_id) locked_table, t1.*, t2.*
from sys.dm_os_waiting_tasks t1
inner join sys.dm_tran_locks t2
on t1.session_id = t2.request_session_id
left join sys.partitions t3
on t3.partition_id = substring(t1.resource_description,charindex('associatedObjectId=',t1.resource_description,0)+19,100)
;
