MariaDB LOCK 조회 및 조치

LOCK 조회 쿼리

select
	process.DB as DB ,
	process.TIME wait_time ,
	(case
		when trx1.trx_state = "RUNNING" then "Lock_User"
		else "fail"
	end) STATE_L ,
	process1.USER USER_l ,
	trx1.trx_mysql_thread_id ID_l ,
	trx1.trx_query wait_query_l ,
	process1.TIME wait_time_l ,
	(case
		when trx.trx_state = "LOCK WAIT" then "Waiting_User"
		else "fail"
	end) as STATE_B ,
	TRIM(process.USER) user ,
	trx.trx_mysql_thread_id ID ,
	trx.trx_query wait_query
from
	information_schema.innodb_lock_waits lw
inner join information_schema.innodb_trx trx on
	lw.requesting_trx_id = trx.trx_id
inner join information_schema.processlist process on
	trx.trx_mysql_thread_id = process.id
inner join information_schema.innodb_trx trx1 on
	lw.blocking_trx_id = trx1.trx_id
inner join information_schema.processlist process1 on
	trx1.trx_mysql_thread_id = process1.id
where
	blocking_trx_id in (
	select
		trx_id
	from
		information_schema.innodb_trx
	where
		trx_state = "RUNNING")

LOCK 상세 확인

1. 실시간 lock 확인

• blocking 걸고 있는 transaction id 확인 ( blocking_trx_id) / 및 lock걸린 transaction 찾기 ( requesting_trx_id)

  select * from information_schema.innodb_lock_waits

• lock 걸린 table 확인하고 싶으면 lock_table 확인

  select * from information_schema.innodb_locks where lock_id = requesting_trx_id

• blocking 걸고 있는 transaction 확인 / lock 걸린 transaction 확인 및 SQL 확인

select from information_schema.innodb_trx where trx_id = blocking_trx_id
select from information_schema.innodb_trx where trx_id = requesting_trx_id

2. 자주 lock이 발생하는 경우, 오래 돌고 있는 트랜잭션이 있는 것은 아닌지 확인해야 한다.

• 트랜잭션이 blocking 유발 쿼리 수행 후 다른 쿼리를 수행중이거나, 혹은 다 끝나고 commit을 기다리는 Sleep상태 일 수 있기 때문에 트랜잭션의 쿼리 로그를 확인해야 한다.
• lock 자체는 innodb_lock_wait_timeout 으로 인해 종료될 수 있지만 원인이 되는 트랜잭션을 찾아야 한다.

1. information_schema.innodb_trx에서 RUNNING 중인 트랜잭션을 찾는다.

2. trx_tables_in_use 컬럼값을 확인하여 현재 lock 걸고 있는 테이블이 있는건 아닌지 확인한다.

4. performance_schema.events_statements_history 에서 blocking 트랜잭션의 수행 쿼리 로그를 확인하여, 어떤 쿼리가 blocking을 유발했는지 확인한다.

	SELECT thread_id,
	       EVENT_ID,
	       END_EVENT_ID,
	       EVENT_NAME,    
	   SQL_TEXT
	FROM events_statements_history
	WHERE thread_id = threads.threa_id
	
	-- 전체 쿼리
	SELECT ps.id 'PROCESS ID',
	             esh.event_name 'EVENT NAME',
	                            esh.sql_text 'SQL'
	FROM information_schema.innodb_trx trx
	JOIN information_schema.processlist ps ON trx.trx_mysql_thread_id = ps.id
	JOIN performance_schema.threads th ON th.processlist_id = trx.trx_mysql_thread_id
	JOIN performance_schema.events_statements_history esh ON esh.thread_id = th.thread_id
	WHERE trx.trx_started < CURRENT_TIME - INTERVAL 10 SECOND
	  AND ps.USER != ‘SYSTEM_USER’
	ORDER BY esh.EVENT_ID

5. 만약 performance_schema가 OFF되어 수집이 안된다면, 서버상에서 쿼리 history를 확인할 수 있는 방법은 없으므로, JDBM에 수집된 로그는 없는지 확인한다.

6. Maxguage모니터링 performace Analyze에서 Lock Tree 조회

7. information_schema.innodb_trx에서 trx_started 시간을 확인하여 Scouter상에서 해당 시간에 수행된 트랜잭션을 확인한다.

select trx_started from information_schema.innodb_trx where trx_state = 'RUNNING'