이 글에서는 MySQL 문 잠금을 구현하는 방법을 공유하겠습니다. MySQL 잠금 분석은 항상 어려운 주제였습니다. 업무를 진행하다 보면 동료들이 이런 질문을 자주 하곤 합니다. 오늘 우리는 모든 사람에게 도움이 되기를 바라며 이 문제에 대해 간략하게 이야기하겠습니다.
다음 SQL 문에 어떤 잠금이 추가되어 있는지 살펴보세요
SLQ1：select * from t1 where id = 10;
SQL2：delete from t1 where id = 10;
로그인 후 복사
(1) id가 기본 키입니까? (2) 현재 시스템의 격리 수준은 무엇입니까? id 열이 기본 키가 아닌데 id 열에 인덱스가 있나요? (4) id 열에 보조 인덱스가 있으면 이 인덱스는 보조 인덱스인가요? (5) 두 SQL의 실행 계획은 무엇인가요? ? 인덱스 스캔 또는 전체 테이블 스캔
실제 실행 계획은 MySQL의 출력을 기반으로 해야 합니다
조합 1: id 열은 기본 키, RC 격리 수준
조합 2: id 열은 보조 고유 인덱스 , RC 격리 수준
조합 3: id 열은 보조 비고유 인덱스, RC 격리 수준
조합 4: id 열에는 인덱스가 없음, RC 격리 수준
조합 5: id 열은 기본 키, RR 격리 level
조합 6: id 열은 보조 고유 인덱스, RR 격리 수준
조합 7: id 열은 보조 비고유 인덱스, RR 격리 수준
조합 8: id 열에 인덱스가 없음, RR 격리 수준
직렬화 가능 격리 수준
RR RC 격리 수준에서 SQL1: 선택 사항 중 어느 것도 잠기지 않으며 스냅샷 읽기는 사용됩니다. 다음은 SQL2의 잠금에 대해서만 설명합니다. 삭제 작업
Percona
조합 1 : ID 기본 키 + rc keypercona
---TRANSACTION 1286310, ACTIVE 9 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 341, OS thread handle 0x7f4d540d0700, query id 4510972 localhost root cleaning up
TABLE LOCK table `test`.`t1` trx id 1286310 lock mode IX
RECORD LOCKS space id 29 page no 3 n bits 80 index `PRIMARY` of table `test`.`t1` trx id 1286310 lock_mode X locks rec but not gap
로그인 후 복사
mysql
---TRANSACTION 5936, ACTIVE 171 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 0x7f5677201700, query id 364 localhost root
TABLE LOCK table `test`.`t1` trx id 5936 lock mode IX
RECORD LOCKS space id 6 page no 3 n bits 80 index `PRIMARY` of table `test`.`t1` trx id 5936 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 000000001730; asc 0;;
2: len 7; hex 26000001550110; asc & U ;;
3: len 1; hex 61; asc a;;
로그인 후 복사
콤비네이션 2 : ID 고유 인덱스 + rc
고유 인덱스의 업데이트에는 2 개의 x 잠금이 필요합니다. 고유 인덱스 id=10 레코드에 해당하고, 클러스터형 인덱스 이름에 해당하는 레코드='d'
Percona
---TRANSACTION 1286327, ACTIVE 3 sec
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 344, OS thread handle 0x7f4d5404e700, query id 4510986 localhost root cleaning up
TABLE LOCK table `test`.`t2` trx id 1286327 lock mode IX
RECORD LOCKS space id 30 page no 4 n bits 80 index `id` of table `test`.`t2` trx id 1286327 lock_mode X locks rec but not gap
RECORD LOCKS space id 30 page no 3 n bits 80 index `PRIMARY` of table `test`.`t2` trx id 1286327 lock_mode X locks rec but not gap
로그인 후 복사
MySQL
---TRANSACTION 5938, ACTIVE 3 sec
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 0x7f5677201700, query id 374 localhost root
TABLE LOCK table `test`.`t2` trx id 5938 lock mode IX
RECORD LOCKS space id 7 page no 4 n bits 80 index `id` of table `test`.`t2` trx id 5938 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 8000000a; asc ;;
1: len 1; hex 64; asc d;;
RECORD LOCKS space id 7 page no 3 n bits 80 index `PRIMARY` of table `test`.`t2` trx id 5938 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 1; hex 64; asc d;;
1: len 6; hex 000000001732; asc 2;;
2: len 7; hex 27000001560110; asc ' V ;;
3: len 4; hex 8000000a; asc ;;
로그인 후 복사
조합 3의 레코드 : id non-unique index + RC
ID가 일반 인덱스로 나열되면 해당 SQL 쿼리 조건이 모두 충족됩니다. 레코드가 동시에 잠기며 기본 키 인덱스의 이러한 레코드도 잠깁니다
Percona
---TRANSACTION 1286339, ACTIVE 9 sec
3 lock struct(s), heap size 360, 4 row lock(s), undo log entries 2
MySQL thread id 347, OS thread handle 0x7f4b67fff700, query id 4511015 localhost root cleaning up
TABLE LOCK table `test`.`t3` trx id 1286339 lock mode IX
RECORD LOCKS space id 31 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 1286339 lock_mode X locks rec but not gap
RECORD LOCKS space id 31 page no 3 n bits 80 index `PRIMARY` of table `test`.`t3` trx id 1286339 lock_mode X locks rec but not gap
로그인 후 복사
MySQL
---TRANSACTION 5940, ACTIVE 3 sec
3 lock struct(s), heap size 360, 4 row lock(s), undo log entries 2
MySQL thread id 2, OS thread handle 0x7f5677201700, query id 378 localhost root
TABLE LOCK table `test`.`t3` trx id 5940 lock mode IX
RECORD LOCKS space id 8 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 5940 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 8000000a; asc ;;
1: len 1; hex 62; asc b;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 8000000a; asc ;;
1: len 1; hex 64; asc d;;
RECORD LOCKS space id 8 page no 3 n bits 80 index `PRIMARY` of table `test`.`t3` trx id 5940 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 1; hex 62; asc b;;
1: len 6; hex 000000001734; asc 4;;
2: len 7; hex 28000001570110; asc ( W ;;
3: len 4; hex 8000000a; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 1; hex 64; asc d;;
1: len 6; hex 000000001734; asc 4;;
2: len 7; hex 28000001570132; asc ( W 2;;
3: len 4; hex 8000000a; asc ;;
로그인 후 복사
조합 4: id 인덱스 없음 + RC
Percona
---TRANSACTION 1286373, ACTIVE 5 sec
2 lock struct(s), heap size 360, 2 row lock(s), undo log entries 2
MySQL thread id 348, OS thread handle 0x7f4d54193700, query id 4511037 localhost root cleaning up
TABLE LOCK table `test`.`t4` trx id 1286373 lock mode IX
RECORD LOCKS space id 33 page no 3 n bits 80 index `PRIMARY` of table `test`.`t4` trx id 1286373 lock_mode X locks rec but not gap
로그인 후 복사
MySQL
---TRANSACTION 5946, ACTIVE 2 sec
2 lock struct(s), heap size 360, 2 row lock(s), undo log entries 2
MySQL thread id 2, OS thread handle 0x7f5677201700, query id 382 localhost root
TABLE LOCK table `test`.`t4` trx id 5946 lock mode IX
RECORD LOCKS space id 9 page no 3 n bits 80 index `PRIMARY` of table `test`.`t4` trx id 5946 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 1; hex 62; asc b;;
1: len 6; hex 00000000173a; asc :;;
2: len 7; hex 2b0000015a0110; asc + Z ;;
3: len 4; hex 8000000a; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 1; hex 64; asc d;;
1: len 6; hex 00000000173a; asc :;;
2: len 7; hex 2b0000015a012c; asc + Z ,;;
3: len 4; hex 8000000a; asc ;;
로그인 후 복사
조합 5: id 기본 키 + RR
참조 조합 1
조합 6: id 고유 인덱스 + RR
참조 조합 2
조합 7: id 비고유 인덱스 + RR
Percona
---TRANSACTION 1592633, ACTIVE 24 sec
4 lock struct(s), heap size 1184, 5 row lock(s), undo log entries 2
MySQL thread id 794, OS thread handle 0x7f4d5404e700, query id 7801799 localhost root cleaning up
Trx read view will not see trx with id >= 1592634, sees < 1592634
TABLE LOCK table `test`.`t3` trx id 1592633 lock mode IX
RECORD LOCKS space id 31 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 1592633 lock_mode X
RECORD LOCKS space id 31 page no 3 n bits 80 index `PRIMARY` of table `test`.`t3` trx id 1592633 lock_mode X locks rec but not gap
RECORD LOCKS space id 31 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 1592633 lock_mode X locks gap before rec
로그인 후 복사
MySQL
---TRANSACTION 5985, ACTIVE 7 sec
4 lock struct(s), heap size 1184, 5 row lock(s), undo log entries 2
MySQL thread id 12, OS thread handle 0x7f56770fd700, query id 500 localhost root
TABLE LOCK table `test`.`t3` trx id 5985 lock mode IX
RECORD LOCKS space id 8 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 5985 lock_mode X
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 8000000a; asc ;;
1: len 1; hex 64; asc d;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 8000000a; asc ;;
1: len 1; hex 62; asc b;;
RECORD LOCKS space id 8 page no 3 n bits 80 index `PRIMARY` of table `test`.`t3` trx id 5985 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 1; hex 64; asc d;;
1: len 6; hex 000000001761; asc a;;
2: len 7; hex 3f0000016d0132; asc ? m 2;;
3: len 4; hex 8000000a; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 1; hex 62; asc b;;
1: len 6; hex 000000001761; asc a;;
2: len 7; hex 3f0000016d0110; asc ? m ;;
3: len 4; hex 8000000a; asc ;;
RECORD LOCKS space id 8 page no 4 n bits 80 index `idx_key` of table `test`.`t3` trx id 5985 lock_mode X locks gap before rec
Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000b; asc ;;
1: len 1; hex 66; asc f;;
로그인 후 복사
조합 8: ID 없음 인덱스 +RR
Percon a
---TRANSACTION 1592639, ACTIVE 4 sec
2 lock struct(s), heap size 360, 7 row lock(s), undo log entries 2
MySQL thread id 794, OS thread handle 0x7f4d5404e700, query id 7801804 localhost root cleaning up
TABLE LOCK table `test`.`t4` trx id 1592639 lock mode IX
RECORD LOCKS space id 33 page no 3 n bits 80 index `PRIMARY` of table `test`.`t4` trx id 1592639 lock_mode X
로그인 후 복사
MySQL
---TRANSACTION 6000, ACTIVE 3 sec
2 lock struct(s), heap size 360, 7 row lock(s), undo log entries 2
MySQL thread id 12, OS thread handle 0x7f56770fd700, query id 546 localhost root
TABLE LOCK table `test`.`t4` trx id 6000 lock mode IX
RECORD LOCKS space id 9 page no 3 n bits 80 index `PRIMARY` of table `test`.`t4` trx id 6000 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 1; hex 61; asc a;;
1: len 6; hex 000000001722; asc ";;
2: len 7; hex 9e0000014e0110; asc N ;;
3: len 4; hex 8000000f; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 1; hex 62; asc b;;
1: len 6; hex 000000001770; asc p;;
2: len 7; hex 47000001730110; asc G s ;;
3: len 4; hex 8000000a; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 1; hex 63; asc c;;
1: len 6; hex 000000001722; asc ";;
2: len 7; hex 9e0000014e0122; asc N ";;
3: len 4; hex 80000006; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 1; hex 64; asc d;;
1: len 6; hex 000000001770; asc p;;
2: len 7; hex 4700000173012c; asc G s ,;;
3: len 4; hex 8000000a; asc ;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 1; hex 66; asc f;;
1: len 6; hex 000000001722; asc ";;
2: len 7; hex 9e0000014e0134; asc N 4;;
3: len 4; hex 8000000b; asc ;;
Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 2; hex 7a7a; asc zz;;
1: len 6; hex 000000001722; asc ";;
2: len 7; hex 9e0000014e013d; asc N =;;
3: len 4; hex 80000002; asc ;;
로그인 후 복사
구성 9: 직렬화 가능
앞서 언급한 단순 SQL의 경우 마지막 경우는 직렬화 가능 격리 수준입니다. SQL2: delete from t1 where id = 10;의 경우 직렬화 가능 격리 수준은 반복 읽기 격리 수준과 정확히 동일하므로 도입되지 않습니다.
직렬화 가능 격리 수준은 SQL1에 영향을 미칩니다. select * from t1 where id = 10; 이 SQL은 RC 및 RR 격리 수준 아래에 있으며 잠금이 없는 스냅샷 읽기입니다. 그러나 직렬화 가능 격리 수준에서 SQL1은 읽기 잠금을 추가합니다. 즉, 스냅샷 읽기가 더 이상 존재하지 않으며 MVCC 동시성 제어가 잠금 기반 CC로 다운그레이드됩니다.
MySQL/InnoDB에서는 소위 잠금 없는 읽기가 모든 상황에 적용되는 것은 아니지만 격리 수준과 관련이 있습니다. 읽기가 잠겨 있지 않으면 직렬화 가능 격리 수준이 더 이상 유효하지 않습니다.
관련 권장 사항:
Mysql 높은 동시성 잠금 트랜잭션 처리
PHP_PHP 튜토리얼에서 파일을 잠그는 방법
MySQL 트랜잭션 및 잠금 메커니즘
위 내용은 MySQL 문 잠금을 구현하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!
![[웹 프런트엔드] Node.js 빠른 시작](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
