[MySQL] InnoDB 트랜잭션 그리고 잠금 정보 모니터링

■ InnoDB 트랜잭션 그리고 잠금 정보 모니터링

3개의 InnoDB INFORMATION_SCHEMA 테이블을 사용하여 트랜잭션을 모니터링하고 잠재적인 잠금 문제를 진단할 수 있습니다.

• InnoDB 트랜잭션 사용 및 잠금 정보

• InnoDB 잠금(Lock) 그리고 잠금대기(Lock-wait) 정보

• InnoDB 트랜잭션 및 잠금 정보의 지속성 및 일관성

 

+ INNODB_TRX : 트랜잭션 상태 (예 : 실행 중인지 또는 잠금 대기 중인지 여부), 트랜잭션 시작 시점, 트랜잭션이 실행중인 특정 SQL 문을 포함하여 InnoDB 내에서 현재 실행중인 모든 트랜잭션에 대한 정보를 제공합니다.

 

+ INNODB_LOCKS : 다른 트랜잭션이 잠금을 해제하기를 기다리는 InnoDB의 각 트랜잭션 (INNODB_TRX.TRX_STATE는 LOCK WAIT)은 정확히 하나의 차단 잠금 요청에 의해 차단됩니다. 해당 차단 잠금 요청은 호환되지 않는 모드에서 다른 트랜잭션이 보유한 행 또는 테이블 잠금에 대한 것입니다. 트랜잭션을 차단하는 잠금은 항상 요청 된 잠금 모드 (읽기 대 쓰기, 공유 대 배타)와 호환되지 않는 모드로 유지됩니다. 차단 된 트랜잭션은 다른 트랜잭션이 커밋되거나 롤백되어 요청 된 잠금이 해제 될 때까지 진행할 수 없습니다. 차단 된 모든 트랜잭션에 대해 INNODB_LOCKS에는 트랜잭션이 요청하고 대기중인 각 잠금을 설명하는 하나의 행이 포함됩니다. INNODB_LOCKS는 또한 잠금을 보유한 트랜잭션의 상태 (INNODB_TRX.TRX_STATE가 RUNNING, LOCK WAIT, ROLLING BACK 또는 COMMITTING 임)에 관계없이 다른 트랜잭션을 차단하는 각 잠금에 대해 하나의 행을 포함합니다.

 

+ INNODB_LOCK_WAITS :이 테이블은 어떤 트랜잭션이 주어진 잠금을 기다리고 있는지 또는 주어진 트랜잭션이 어떤 잠금을 기다리고 있는지를 나타냅니다. 이 테이블에는 차단 된 각 트랜잭션에 대해 하나 이상의 행이 포함되어 요청한 잠금과 해당 요청을 차단하는 잠금을 나타냅니다. REQUESTED_LOCK_ID 값은 트랜잭션이 요청한 잠금을 의미하고 BLOCKING_LOCK_ID 값은 첫 번째 트랜잭션이 진행되는 것을 방지하는 잠금 (다른 트랜잭션이 보유)을 의미합니다. 차단 된 트랜잭션에 대해 INNODB_LOCK_WAITS의 모든 행은 REQUESTED_LOCK_ID에 대해 동일한 값을 갖고 BLOCKING_LOCK_ID에 대해 다른 값을 갖습니다.

 

 

■ InnoDB 트랜잭션 사용 및 잠금 정보

▶︎ 차단 트랜잭션 식별

어떤 트랜잭션이 다른 트랜잭션을 차단하는지 식별하는 것이 때때로 도움이 됩니다. InnoDB 트랜잭션 및 데이터 잠금에 대한 정보가 포함 된 테이블을 사용하면 어떤 트랜잭션이 다른 트랜잭션을 기다리고 있는지, 어떤 리소스가 요청되는지 확인할 수 있습니다.

 

세 개의 세션이 동시에 실행되고 있다고 가정합니다. 각 세션은 MySQL 스레드에 해당하며 트랜잭션을 차례로 실행합니다. 이 세션이 다음 명령문을 발행했지만 아직 트랜잭션을 커미트하지 않은 경우 시스템 상태를 고려해야 합니다.

+ Session A:
BEGIN;
SELECT a FROM t FOR UPDATE;
SELECT SLEEP(100);

+ Session B:
SELECT b FROM t FOR UPDATE;

+ Session C:
SELECT c FROM t FOR UPDATE;

 

이 시나리오에서는 다음 쿼리를 사용하여 대기중인 트랜잭션과이를 차단하는 트랜잭션을 확인합니다.

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM       information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_trx_id;

 

또는 더 간단하게 sys 스키마 innodb_lock_waits 뷰를 사용합니다.

SELECT
  waiting_trx_id,
  waiting_pid,
  waiting_query,
  blocking_trx_id,
  blocking_pid,
  blocking_query
FROM sys.innodb_lock_waits;

 

차단 쿼리에 대해 NULL 값이보고되는 경우 발급 세션이 유휴 상태가 된 후 차단 쿼리 식별을 참조합니다.

waiting trx id	waiting thread	waiting query	blocking trx id	blocking thread	blocking query
A4	6	SELECT b FROM t FOR UPDATE	A3	5	SELECT SLEEP(100)
A5	7	SELECT c FROM t FOR UPDATE	A3	5	SELECT SLEEP(100)
A5	7	SELECT c FROM t FOR UPDATE	A4	6	SELECT b FROM t FOR UPDATE

 

앞의 표에서 "쿼리 대기 중"또는 "쿼리 차단" 컬럼으로 세션을 식별할 수 있습니다. 아래와 같이 식별할 수 있습니다.

+ 세션 B (trx ID A4, 스레드 6)와 세션 C (trx ID 와 마찬가지로, 스레드 7)는 모두 세션 A (trx ID A3, 스레드 5)를 기다리고 있습니다.

+ 세션 C는 세션 B와 세션 A를 기다리고 있습니다.

INNODB_TRX, INNODB_LOCKS 및 INNODB_LOCK_WAITS 테이블에서 기본 데이터를 볼 수 있습니다.

 

다음 표는 INFORMATION_SCHEMA.INNODB_TRX의 일부 샘플 콘텐츠를 보여줍니다.

trx id	trx state	trx started	trx requested lock id	trx wait started	trx weight	trx mysql thread id	trx query
A3	RUN­NING	2008-01-15 16:44:54	NULL	NULL	2	5	SELECT SLEEP(100)
A4	LOCK WAIT	2008-01-15 16:45:09	A4:1:3:2	2008-01-15 16:45:09	2	6	SELECT b FROM t FOR UPDATE
A5	LOCK WAIT	2008-01-15 16:45:14	A5:1:3:2	2008-01-15 16:45:14	2	7	SELECT c FROM t FOR UPDATE

 

 

다음 표는 INFORMATION_SCHEMA.INNODB_LOCKS의 일부 샘플 콘텐츠를 보여줍니다.

lock id	lock trx id	lock mode	lock type	lock table	lock index	lock data
A3:1:3:2	A3	X	RECORD	test.t	PRIMARY	0x0200
A4:1:3:2	A4	X	RECORD	test.t	PRIMARY	0x0200
A5:1:3:2	A5	X	RECORD	test.t	PRIMARY	0x0200

 

 

다음 표는 INFORMATION_SCHEMA.INNODB_LOCK_WAITS의 일부 샘플 콘텐츠를 보여줍니다.

requesting trx id	requested lock id	blocking trx id	blocking lock id
A4	A4:1:3:2	A3	A3:1:3:2
A5	A5:1:3:2	A3	A3:1:3:2
A5	A5:1:3:2	A4	A4:1:3:2

 

▶︎ 실행 세션이 유휴 상태가 된 후 차단 쿼리 식별

차단 트랜잭션을 식별할 때 쿼리를 실행한 세션이 유휴 상태가 되면 차단 쿼리에 대해 NULL값이 보고됩니다. 이 경우 다음 단계를 사용하여 차단 쿼리를 확인합니다.

1. 차단 트랜잭션의 프로세스 목록 ID를 식별합니다. sys.innodb_lock_waits 테이블에서 차단 트랜잭션의 프로세스 목록 ID는 blocking_pid 값입니다.

2. blocking_pid를 사용하여 MySQL 성능 스키마 스레드 테이블을 쿼리하여 차단 트랜잭션의 THREAD_ID를 확인합니다. 예를 들어, blocking_pid가 6이면 다음 쿼리를 실행합니다.

SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = 6;

 

3. THREAD_ID를 사용하여 성능 스키마 events_statements_current 테이블을 쿼리하여 스레드에서 실행한 마지막 쿼리를 확인합니다. 예를 들어 THREAD_ID가 28이면 다음 쿼리를 실행합니다.

SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current WHERE THREAD_ID = 28\G

 

4. 스레드에 의해 실행된 마지막 쿼리가 잠금이 유지되는 이유를 확인하기에 충분한 정보가 아닌 경우 성능 스키마 events_statements_history 테이블을 쿼리하여 스레드가 실행한 마지막 10개 문을 볼 수 있습니다.

SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_history

WHERE THREAD_ID = 28 ORDER BY EVENT_ID;

 

 

▶︎ InnoDB 트랜잭션과 MySQL 세션의 상관 관계

때때로 내부 InnoDB 잠금 정보를 MySQL에서 유지 관리하는 세션 수준 정보와 연관시키는 것이 유용합니다. 예를 들어, 주어진 InnoDB 트랜잭션 ID에 대해 해당 MySQL 세션ID 및 잠금을 보유하고있어 다른 트랜잭션을 차단할 수 있는 세션 이름을 알고 싶을 수 있습니다.

 

INFORMATION_SCHEMA 테이블의 다음 출력은 다소 부하가 있는 시스템에서 가져옵니다. 아래와 같은 여러 트랜잭션이 실행 중입니다.

다음 INNODB_LOCKS 및 INNODB_LOCK_WAITS 테이블은 다음을 보여줍니다.

+ Transaction 77F (executing an INSERT) is waiting for transactions 77E, 77D, and 77B to commit.

+ Transaction 77E (executing an INSERT) is waiting for transactions 77D and 77B to commit.

+ Transaction 77D (executing an INSERT) is waiting for transaction 77B to commit.

+ Transaction 77B (executing an INSERT) is waiting for transaction 77A to commit.

+ Transaction 77A is running, currently executing SELECT.

+ Transaction E56 (executing an INSERT) is waiting for transaction E55 to commit.

+ Transaction E55 (executing an INSERT) is waiting for transaction 19C to commit.

+ Transaction 19C is running, currently executing an INSERT.

 

참고사항.

INFORMATION_SCHEMA PROCESSLIST 및 INNODB_TRX 테이블에 표시된 쿼리간에 불일치가 있을 수 있습니다. 다음 표는 과중한 워크로드를 실행하는 시스템에 대한 INFORMATION_SCHEMA.PROCESSLIST의 내용을 보여줍니다.

 

다음표는 부하가 높은 워크로드를 실행하는 시스템에 대한 INFORMATION_SCHEMA.PROCESSLIST의 내용을 보여줍니다.

ID	USER	HOST	DB	COMMAND	TIME	STATE	INFO
384	root	localhost	test	Query	10	update	INSERT INTO t2 VALUES …
257	root	localhost	test	Query	3	update	INSERT INTO t2 VALUES …
130	root	localhost	test	Query	0	update	INSERT INTO t2 VALUES …
61	root	localhost	test	Query	1	update	INSERT INTO t2 VALUES …
8	root	localhost	test	Query	1	update	INSERT INTO t2 VALUES …
4	root	localhost	test	Query	0	preparing	SELECT * FROM PROCESSLIST
2	root	localhost	test	Sleep	566		NULL

 

 

다음 표는 부하가 높은 워크로드를 실행하는 시스템에 대한 INFORMATION_SCHEMA.INNODB_TRX의 내용을 보여줍니다.

trx id	trx state	trx started	trx requested lock id	trx wait started	trx weight	trx mysql thread id	trx query
77F	LOCK WAIT	2008-01-15 13:10:16	77F	2008-01-15 13:10:16	1	876	INSERT INTO t09 (D, B, C) VALUES …
77E	LOCK WAIT	2008-01-15 13:10:16	77E	2008-01-15 13:10:16	1	875	INSERT INTO t09 (D, B, C) VALUES …
77D	LOCK WAIT	2008-01-15 13:10:16	77D	2008-01-15 13:10:16	1	874	INSERT INTO t09 (D, B, C) VALUES …
77B	LOCK WAIT	2008-01-15 13:10:16	77B:733:12:1	2008-01-15 13:10:16	4	873	INSERT INTO t09 (D, B, C) VALUES …
77A	RUN­NING	2008-01-15 13:10:16	NULL	NULL	4	872	SELECT b, c FROM t09 WHERE …
E56	LOCK WAIT	2008-01-15 13:10:06	E56:743:6:2	2008-01-15 13:10:06	5	384	INSERT INTO t2 VALUES …
E55	LOCK WAIT	2008-01-15 13:10:06	E55:743:38:2	2008-01-15 13:10:13	965	257	INSERT INTO t2 VALUES …
19C	RUN­NING	2008-01-15 13:09:10	NULL	NULL	2900	130	INSERT INTO t2 VALUES …
E15	RUN­NING	2008-01-15 13:08:59	NULL	NULL	5395	61	INSERT INTO t2 VALUES …
51D	RUN­NING	2008-01-15 13:08:47	NULL	NULL	9807	8	INSERT INTO t2 VALUES …

 

 

다음 표는 부하가 높은 워크로드를 실행하는 시스템에 대한 INFORMATION_SCHEMA.INNODB_LOCK_WAITS의 내용을 보여줍니다.

requesting trx id	requested lock id	blocking trx id	blocking lock id
77F	77F:806	77E	77E:806
77F	77F:806	77D	77D:806
77F	77F:806	77B	77B:806
77E	77E:806	77D	77D:806
77E	77E:806	77B	77B:806
77D	77D:806	77B	77B:806
77B	77B:733:12:1	77A	77A:733:12:1
E56	E56:743:6:2	E55	E55:743:6:2
E55	E55:743:38:2	19C	19C:743:38:2

 

다음 표는 과중한 워크로드를 실행하는 시스템에 대한 INFORMATION_SCHEMA.INNODB_LOCKS의 내용을 보여줍니다.

lock id	lock trx id	lock mode	lock type	lock table	lock index	lock data
77F:806	77F	AUTO_INC	TABLE	test.t09	NULL	NULL
77E:806	77E	AUTO_INC	TABLE	test.t09	NULL	NULL
77D:806	77D	AUTO_INC	TABLE	test.t09	NULL	NULL
77B:806	77B	AUTO_INC	TABLE	test.t09	NULL	NULL
77B:733:12:1	77B	X	RECORD	test.t09	PRIMARY	supremum pseudo-record
77A:733:12:1	77A	X	RECORD	test.t09	PRIMARY	supremum pseudo-record
E56:743:6:2	E56	S	RECORD	test.t2	PRIMARY	0, 0
E55:743:6:2	E55	X	RECORD	test.t2	PRIMARY	0, 0
E55:743:38:2	E55	S	RECORD	test.t2	PRIMARY	1922, 1922
19C:743:38:2	19C	X	RECORD	test.t2	PRIMARY	1922, 1922

 

■ InnoDB 잠금(Lock) 그리고 잠금대기(Lock-wait) 정보

트랜잭션이 테이블의 행을 업데이트하거나 SELECT FOR UPDATE를 사용하여 잠글때, InnoDB는 해당 행에 대한 잠금 목록 또는 대기열을 설정합니다. 마찬가지로 InnoDB는 테이블 수준 잠금을 위해 테이블에 잠금 목록을 유지합니다. 두 번째 트랜잭션이 호환되지 않는 모드에서 이전 트랜잭션에 의해 이미 잠긴 테이블을 잠 그거나 행을 업데이트하려는 경우 InnoDB는 해당 큐에 해당 행에 대한 잠금 요청을 추가합니다. 트랜잭션이 잠금을 획득하려면 해당 행 또는 테이블의 잠금 대기열에 이전에 입력 된 모든 호환되지 않는 잠금 요청을 제거해야합니다 (이러한 잠금을 보유하거나 요청하는 트랜잭션이 커밋 또는 롤백 할 때 발생 함).

 

트랜잭션에는 다른 행이나 테이블에 대한 잠금 요청이 얼마든지있을 수 있습니다. 주어진 시간에 트랜잭션은 다른 트랜잭션이 보유한 잠금을 요청할 수 있으며,이 경우 다른 트랜잭션에 의해 차단됩니다. 요청 트랜잭션은 차단 잠금을 보유한 트랜잭션이 커밋 또는 롤백 될 때까지 기다려야합니다. 트랜잭션이 잠금을 기다리고 있지 않으면 RUNNING 상태입니다. 트랜잭션이 잠금을 대기중인 경우 LOCK WAIT 상태입니다. (INFORMATION_SCHEMA INNODB_TRX 테이블은 트랜잭션 상태 값을 나타냅니다.)

 

INNODB_LOCKS 테이블은 각 LOCK WAIT 트랜잭션에 대해 하나 이상의 행을 보유하여 진행을 방해하는 잠금 요청을 나타냅니다. 이 테이블에는 주어진 행 또는 테이블에 대해 보류중인 잠금 큐의 각 잠금을 설명하는 하나의 행도 있습니다. INNODB_LOCK_WAITS 테이블은 트랜잭션이 이미 보유한 잠금이 다른 트랜잭션에서 요청한 잠금을 차단하고 있는지 보여줍니다.

 

 

■ InnoDB 트랜잭션 및 잠금 정보의 지속성 및 일관성

트랜잭션 및 잠금 테이블(INNODB_TRX, INNODB_LOCKS 및 INNODB_LOCK_WAITS)에 의해 확인된 데이터는 빠르게 변화하는 데이터를 체크해볼 수 있음을 나타냅니다. 이것은 응용 프로그램에서 시작된 업데이트가 발생할 때만 데이터가 변경되는 사용자 테이블과 다릅니다. 기본 데이터는 내부 시스템 관리 데이터이며 매우 빠르게 변경될 수 있습니다.

 

성능상의 이유로 그리고 트랜잭션과 잠금 테이블 사이의 잘못된 조인 가능성을 최소화하기 위해 InnoDB는 테이블에 대한 SELECT가 실행될 때마다 필요한 트랜잭션 및 잠금 정보를 중간 버퍼에 수집합니다. 이 버퍼는 버퍼를 마지막으로 읽은 후 0.1초 이상 경과한 경우에만 새로 고쳐집니다. 세개의 테이블을 채우는데 필요한 데이터는 원자적으로 일관되게 가져와, 이 전역 내부 버퍼에 저장되어 특정 시점에 스냅샷을 형성합니다. 여러 테이블 액세스가 0.1초 이내에 발생하면 (MySQL이 이러한 테이블 간의 조인을 처리 할 때 거의 확실하게 수행되는 것처럼) 쿼리를 충족하기 위해 동일한 스냅샷이 사용됩니다.

 

이러한 테이블중 하나를 단일 쿼리로 조인하면 세 테이블의 데이터가 동일한 스냅샷에서 제공되므로 올바른 결과가 반환됩니다. 이러한 테이블의 모든 쿼리에 대해 버퍼가 새로 고쳐지지 않기 때문에 이러한 테이블에 대해 10분의 1초 내에 별도의 쿼리를 실행하면 쿼리마다 결과가 동일합니다. 반면, 10분의 1초 이상 간격을두고 실행된 동일하거나 다른 테이블의 두개의 개별 쿼리는 데이터가 다른 스냅샷에서 가져 오기 때문에 다른 결과를 볼 수 있습니다.

 

InnoDB는 트랜잭션 및 잠금 데이터가 수집되는 동안 일시적으로 중단되어야 하기 때문에 이러한 테이블을 너무 자주 쿼리하면 다른 사용자가 보는 것처럼 성능에 부정적인 영향을 미칠 수 있습니다.

 

이러한 테이블에는 민감한 정보 (최소 INNODB_LOCKS.LOCK_DATA 및 INNODB_TRX.TRX_QUERY)가 포함되어 있으므로 보안상의 이유로 PROCESS 권한이있는 사용자만 해당 테이블에서 선택할 수 있습니다.

 

앞에서 설명한 것처럼 트랜잭션 및 잠금 테이블 (INNODB_TRX, INNODB_LOCKS 및 INNODB_LOCK_WAITS)의 데이터는 자동으로 가져 와서 특정 시점의 스냅샷을 제공하는 중간 버퍼에 저장됩니다. 동일한 스냅샷에서 쿼리 할 때 세 테이블 모두의 데이터가 일관됩니다. 그러나 기본 데이터가 너무 빠르게 변경되어 유사하게 빠르게 변화하는 다른 데이터를 유사한 방식으로 확인할 수 있지만 일치하지 않을 수 있습니다. 따라서 InnoDB 트랜잭션의 데이터와 PROCESSLIST 테이블의 데이터와 잠금 테이블을 비교할 때주의해야 합니다. PROCESSLIST 테이블의 데이터는 잠금 및 트랜잭션에 대한 데이터와 동일한 스냅 샷에서 가져 오지 않습니다. 단일 SELECT (예 : INNODB_TRX 및 PROCESSLIST 조인)를 실행하더라도 해당 테이블의 내용은 일반적으로 일관성이 없습니다. INNODB_TRX는 PROCESSLIST에없는 행을 참조하거나 INNODB_TRX.TRX_QUERY에 표시된 트랜잭션의 현재 실행중인 SQL 쿼리가 PROCESSLIST.INFO에있는 것과 다를 수 있습니다.