[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에있는 것과 다를 있습니다.

Designed by JB FACTORY