MySQL5.6 InnoDB 트랜잭션과 잠금 테이블의 사용 예

14.14.2.1 InnoDB 트랜잭션과 잠금 테이블의 사용 예

예 14.11 차단하는 트랜잭션 식별

다른 트랜잭션이 트랜잭션을 차단하고 있는지를 식별 할 때 도움이 될 수 있습니다. INFORMATION_SCHEMA 테이블을 사용하면 다른 트랜잭션이 트랜잭션이 대기하고있는 모기장 어떤 자원이 요구되고 있는지를 찾을 수 있습니다.

3 명의 사용자가 동시에 실행하는 다음 시나리오가 있다고합니다. 각 사용자 (또는 세션)는 MySQL 스레드에 대응하고있는 트랜잭션을 다른 트랜잭션 후 실행합니다. 이러한 사용자가 다음 명령을 실행했지만, 아직 아무도 자신의 트랜잭션을 커밋하지 않은 때의 시스템 상태를 생각해보십시오.

User A:

 BEGIN;
 SELECT a FROM t FOR UPDATE;
 SELECT SLEEP (100);

User B:
```
 SELECT b FROM t FOR UPDATE;
```
User 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;

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`

위의 결과는 "waiting query"또는 "blocking query"에서 사용자를 식별 할 수 있습니다. 다음 것을 알 수 있습니다.

사용자 B (trx id 'A4' 스레드 6 )와 사용자 C (trx id 'A5' 스레드 7 )은 모두 사용자 A (trx id 'A3' 스레드 5 )를 대기하고 있습니다.
사용자 C는 사용자 A 외에 사용자 B를 대기하고 있습니다.

테이블 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`	`RUNNING`	`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 space	lock page	lock rec	lock data
`A3:1:3:2`	`A3`	`X`	`RECORD`	`test`.`t`	`PRIMARY`	`1`	`3`	`2`	`0x0200`
`A4:1:3:2`	`A4`	`X`	`RECORD`	`test`.`t`	`PRIMARY`	`1`	`3`	`2`	`0x0200`
`A5:1:3:2`	`A5`	`X`	`RECORD`	`test`.`t`	`PRIMARY`	`1`	`3`	`2`	`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`

예 14.12 정보 스키마 테이블의 트랜잭션 데이터의 더 복잡한 예제

경우에 따라서는 내부 InnoDB 잠금 정보를 MySQL로 유지되는 세션 레벨의 정보와 연관시킬 수 있습니다. 예를 들어, 특정 InnoDB 트랜잭션 ID에 대한 잠금을 보유하고 있기 때문에 다른 트랜잭션을 차단하고있을 가능성이있는 사용자의 해당 MySQL 세션 ID와 이름을 알고 싶은 것이 있습니다.

INFORMATION_SCHEMA 테이블에서 다음 출력은 어느 정도 부하가 높은 시스템에서 검색되었습니다.

다음 표에서 볼 수 있듯이 실행중인 트랜잭션이 여러 존재합니다.

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

트랜잭션 77F ( INSERT 를 실행중인)는 트랜잭션 77E , 77D 및 77B 가 커밋 될 때까지 대기하고 있습니다.
트랜잭션 77E (INSERT를 실행중인)는 트랜잭션 77D 및 77B 가 커밋 될 때까지 대기하고 있습니다.
트랜잭션 77D (INSERT를 실행중인)는 트랜잭션 77B 가 커밋 될 때까지 대기하고 있습니다.
트랜잭션 77B (INSERT를 실행중인)는 트랜잭션 77A 가 커밋 될 때까지 대기하고 있습니다.
트랜잭션 77A 는 실행 중이며, 현재 SELECT 를 실행하고 있습니다.
트랜잭션 E56 ( INSERT 를 실행중인)는 트랜잭션 E55 이 커밋 될 때까지 대기하고 있습니다.
트랜잭션 E55 ( INSERT 를 실행중인)는 트랜잭션 19C 가 커밋 될 때까지 대기하고 있습니다.
트랜잭션 19C 는 실행 중이며, 현재 INSERT 를 수행하고 있습니다.

두 테이블 INNODB_TRX.TRX_QUERY 과 PROCESSLIST.INFO 에 표시된 쿼리 사이에 불일치가 존재할 가능성이 있다는 점에 유의하십시오. 하나의 특정 스레드에 대한 스레드의 현재 트랜잭션 ID와 트랜잭션에서 실행되는 쿼리가 두 테이블에서 다를 수 있습니다. 설명은 섹션 14.14.2.3.1 "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` : 806	`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` : 806	`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` : 806	`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`	`RUNNING`	`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`	`RUNNING`	`2008-01-15 13:09:10`	`NULL`	`NULL`	`2900`	`130`	`insert into t2 values …`
`E15`	`RUNNING`	`2008-01-15 13:08:59`	`NULL`	`NULL`	`5395`	`61`	`insert into t2 values …`
`51D`	`RUNNING`	`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 space	lock page	lock rec	lock data
`77F` : 806	`77F`	`AUTO_INC`	`TABLE`	`test`.`t09`	`NULL`	`NULL`	`NULL`	`NULL`	`NULL`
`77E` : 806	`77E`	`AUTO_INC`	`TABLE`	`test`.`t09`	`NULL`	`NULL`	`NULL`	`NULL`	`NULL`
`77D` : 806	`77D`	`AUTO_INC`	`TABLE`	`test`.`t09`	`NULL`	`NULL`	`NULL`	`NULL`	`NULL`
`77B` : 806	`77B`	`AUTO_INC`	`TABLE`	`test`.`t09`	`NULL`	`NULL`	`NULL`	`NULL`	`NULL`
`77B` : 733 : 12 : 1	`77B`	`X`	`RECORD`	`test`.`t09`	`PRIMARY`	`733`	`12`	`1`	`supremum pseudo-record`
`77A` : 733 : 12 : 1	`77A`	`X`	`RECORD`	`test`.`t09`	`PRIMARY`	`733`	`12`	`1`	`supremum pseudo-record`
`E56` : 743 : 6 : 2	`E56`	`S`	`RECORD`	`test`.`t2`	`PRIMARY`	`743`	`6`	`2`	`0, 0`
`E55` : 743 : 6 : 2	`E55`	`X`	`RECORD`	`test`.`t2`	`PRIMARY`	`743`	`6`	`2`	`0, 0`
`E55` : 743 : 38 : 2	`E55`	`S`	`RECORD`	`test`.`t2`	`PRIMARY`	`743`	`38`	`2`	`1922, 1922`
`19C` : 743 : 38 : 2	`19C`	`X`	`RECORD`	`test`.`t2`	`PRIMARY`	`743`	`38`	`2`	`1922, 1922`