mysql의 innodb_autoinc_lock_mode에 대한 자세한 예

다음 에디터에서는 innodb_autoinc_lock_mode의 표현식 형식과 값 선택 참조 방법에 대해 간략하게 설명합니다. 에디터가 꽤 좋다고 생각해서 지금 공유해서 참고용으로 올려보겠습니다. 편집기를 따라 살펴보겠습니다

전제 조건, percona 버전 5.6, 트랜잭션 격리 수준은 RR

mysql> show create table test_autoinc_lock\G
*************************** 1. row ***************************
    Table: test_autoinc_lock
Create Table: CREATE TABLE `test_autoinc_lock` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `a` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)
mysql> select * from test_autoinc_lock;
+----+------+
| id | a  |
+----+------+
| 1 |  1 |
| 12 |  2 |
| 2 |  3 |
| 3 |  5 |
| 4 |  7 |
| 5 |  7 |
| 6 |  9 |
| 7 |  10 |
+----+------+
8 rows in set (0.00 sec)

조건 1 innodb_autoinc_lock_mode 0으로 설정

session1
 begin;delete from test_autoinc_lock where a>7;//这时未提交
session2
mysql> insert into test_autoinc_lock(a) values(100);//gap锁的存在，这时处于锁等待
session3
mysql> insert into test_autoinc_lock(a) values(2);//这时同样处于等待状态，理论上这个不是gap锁的锁定范围，那么它是在等什么呢
session4
mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
          trx_id: 2317
         trx_state: LOCK WAIT
        trx_started: 2016-10-31 19:28:05
   trx_requested_lock_id: 2317:20
     trx_wait_started: 2016-10-31 19:28:05
        trx_weight: 1
    trx_mysql_thread_id: 9
         trx_query: insert into test_autoinc_lock(a) values(2)
    trx_operation_state: setting auto-inc lock
     trx_tables_in_use: 1
     trx_tables_locked: 1
     trx_lock_structs: 1
   trx_lock_memory_bytes: 360
      trx_rows_locked: 0
     trx_rows_modified: 0
  trx_concurrency_tickets: 0
    trx_isolation_level: REPEATABLE READ
     trx_unique_checks: 1
  trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
     trx_is_read_only: 0
trx_autocommit_non_locking: 0

이때 session3은 auto-increment lock을 기다리고 있으며 설정 auto-inc lock 상태입니다

session2

ERROR 1205(HY000): 잠금 대기 시간 초과, 트랜잭션 다시 시작

이때 세션3 잠금 대기 시간 초과종료

session3

이때 session3을 보면 삽입이 완료된 것을 확인할 수 있습니다.

mysql> select * from test_autoinc_lock;
+----+------+
| id | a  |
+----+------+
| 1 |  1 |
| 12 |  2 |
| 13 |  2 |
| 2 |  3 |
| 3 |  5 |
| 4 |  7 |
| 5 |  7 |
| 6 |  9 |
| 7 |  10 |
+----+------+
9 rows in set (0.00 sec)//注意看这时的最大自增值是13，也就是之前自增最大值上+1，也就是说session2后来释放了预计生成的自增id，将13留给了session3，自增id值的申请完全是串行顺序的。

결론: innodb_autoinc_lock_mode가 0이면 공식적인 전통

수준입니다. -increasing lock은 테이블 잠금 수준이며 현재 SQL 실행이 완료되거나 롤백될 때까지 기다려야 합니다. 이 경우 높은 동시성 상황에서는 자체 증가 잠금에 대한 경쟁이 상대적으로 크다고 생각할 수 있습니다.

조건 2 innodb_autoinc_lock_mode가 1로 설정됨

session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)


mysql> delete from test_autoinc_lock where a>7;
Query OK, 2 rows affected (0.00 sec)
mysql> select * from test_autoinc_lock;
+----+------+
| id | a  |
+----+------+
| 1 |  1 |
| 12 |  2 |
| 13 |  2 |
| 2 |  3 |
| 3 |  5 |
| 4 |  7 |
| 5 |  7 |
| 6 |  9 |
| 7 |  10 |
+----+------+
9 rows in set (0.00 sec)//注意看这时的最大自增值是13


session2
mysql> insert into test_autoinc_lock(a) values(100);//同样gap锁的存在，这时处于锁等待
session3
mysql> insert into test_autoinc_lock(a) values(5);
Query OK, 1 row affected (0.00 sec)


mysql> select * from test_autoinc_lock;
+----+------+
| id | a  |
+----+------+
| 1 |  1 |
| 12 |  2 |
| 13 |  2 |
| 2 |  3 |
| 3 |  5 |
| 15 |  5 |
| 4 |  7 |
| 5 |  7 |
| 6 |  9 |
| 7 |  10 |
+----+------+
10 rows in set (0.00 sec)//session3直接完成了，并且注意观察插入的自增id值是15，也就是跳过了预计分配给session2的14，可以看到自增id值立马就分配给了session3，而不必等session2执行完成

결론: innodb_autoinc_lock_mode 는 공식 연속

레벨인 1레벨이다. 이때 단일 insert SQL이라면 완료를 기다리지 않고 즉시 잠금을 획득하고 해제할 수 있다. 현재 SQL 실행. (다른 트랜잭션에서 자동 증가 잠금을 획득한 세션이 이미 있는 경우는 제외) 또한 SQL이 삽입...선택..., 데이터 로드, 교체 ..선택...과 같은 일부 일괄 삽입 SQL인 경우에도 여전히 테이블 수준 잠금이므로 퇴화되는 것으로 이해될 수 있습니다. 현재 SQL이 실행될 때까지 기다려야 합니다.

값이 1이면 상대적으로 가벼운 잠금이고 복제에 영향을 미치지 않는다고 생각할 수 있습니다. 유일한 결함은 생성된 자체 증가 값이 반드시 완전히 연속적이지는 않다는 것입니다(그러나 개인적으로 생각합니다. 이는 종종 별로 중요하지 않으며 자동 증가된 id 값을 기준으로 행 수를 계산할 필요가 없습니다.)

조건 3 innodb_autoinc_lock_mode가 2로 설정됨

첫 번째 결론: innodb_autoinc_lock_mode를 2로 설정하면 모든 삽입 유형의 SQL이 즉시 잠금을 획득하고 해제할 수 있으므로 가장 효율적입니다. 그러나 새로운 문제가 발생합니다. binlog_format이 문인 경우, insert..select.. 문과 같은 일괄 삽입도 이 경우 즉시 실행될 수 있기 때문에 이때 복제는 안전성을 보장할 수 없습니다. 자체 증가하는 ID 값을 많이 얻은 후에는 전체 테이블을 잠글 필요가 없습니다. 슬레이브는 SQL을 재생할 때 필연적으로 혼란을 야기합니다. 복사가 안전하지 않은지 테스트해 보겠습니다.

master session1
mysql> show variables like '%binlog_for%';
+---------------+-----------+
| Variable_name | Value   |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
mysql> insert into test_autoinc_lock(a) select * from test_auto;
Query OK, 8388608 rows affected, 1 warning (29.85 sec)
Records: 8388608 Duplicates: 0 Warnings: 1


master session2（注意session2在session1执行完成之前执行）
mysql> insert into test_autoinc_lock(a) values(2);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test_autoinc_lock where a=2;
+---------+------+
| id   | a  |
+---------+------+
| 1376236 |  2 |
+---------+------+
1 row in set (0.00 sec)


slave session1（这时可看到1376236主键冲突）
mysql> show slave status\G
*************************** 1. row ***************************
        Slave_IO_State: Waiting for master to send event
         Master_Host: 10.9.73.139
         Master_User: ucloudbackup
         Master_Port: 3306
        Connect_Retry: 60
       Master_Log_File: mysql-bin.000006
     Read_Master_Log_Pos: 75823243
        Relay_Log_File: mysql-relay.000002
        Relay_Log_Pos: 541
    Relay_Master_Log_File: mysql-bin.000006
       Slave_IO_Running: Yes
      Slave_SQL_Running: No
       Replicate_Do_DB: 
     Replicate_Ignore_DB: 
      Replicate_Do_Table: 
    Replicate_Ignore_Table: 
   Replicate_Wild_Do_Table: 
 Replicate_Wild_Ignore_Table: 
          Last_Errno: 1062
          Last_Error: Error 'Duplicate entry '1376236' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into test_autoinc_lock(a) select * from test_auto'
         Skip_Counter: 0
     Exec_Master_Log_Pos: 75822971

첫 번째 배치 삽입이 실행되기 전, 두 번째 단순 삽입이 메인 라이브러리의 binlog를 분석해 보면 문제의 원인을 찾는 것은 어렵지 않습니다. 이때 Auto-increment ID 값이 1376236인 Lock을 획득하게 되는데, 이때 메인 라이브러리에서는 쓰기에는 문제가 없으나, Statement 기반의 복제이기 때문에 슬레이브 라이브러리에 반영되는 경우가 발생한다. 필연적으로 핵심 충돌이 발생합니다.

SET INSERT_ID=1376236/*!*/;
#161031 21:44:31 server id 168380811 end_log_pos 75822940 CRC32 0x65797f1c   Query  thread_id=20  exec_time=0   error_code=0
use `test`/*!*/;
SET TIMESTAMP=1477921471/*!*/;
insert into test_autoinc_lock(a) values(2)
/*!*/;
# at 75822940
#161031 21:44:31 server id 168380811 end_log_pos 75822971 CRC32 0xbb91449d   Xid = 274
COMMIT/*!*/;
# at 75822971
#161031 21:44:26 server id 168380811 end_log_pos 75823050 CRC32 0xa297b57b   Query  thread_id=57  exec_time=30  error_code=0
SET TIMESTAMP=1477921466/*!*/;
BEGIN
/*!*/;
# at 75823050
# at 75823082
#161031 21:44:26 server id 168380811 end_log_pos 75823082 CRC32 0xa5aa31a1   Intvar
SET INSERT_ID=1/*!*/;
#161031 21:44:26 server id 168380811 end_log_pos 75823212 CRC32 0x470282ba   Query  thread_id=57  exec_time=30  error_code=0
SET TIMESTAMP=1477921466/*!*/;
insert into test_autoinc_lock(a) select * from test_auto

요약:

1 innodb 행을 복사할 때 innodb_autoinc_lock_mode를 2로 설정할 수 있습니다. 모든 insert 상황에서 테이블의 최대 동시성을 확보할 수 있다

2 innodb 문 복사 시 innodb_autoinc_lock_mode를 1로 설정하여 단순 insert문의 최대 동시성을 확보하면서 복제의 안전성을 확보할 수 있다

3 myisam 엔진의 경우 어떤 self-increasing ID lock이 table-level lock이더라도 innodb_autoinc_lock_mode 매개변수 설정이 무효하다(테스트 생략)

4 실제로 질문자가 언급한 내용 innodb 엔진 하에서 자체적으로 증가하는 ID 값 기본키로 사용될 경우 uuid나 커스텀 기본키와 비교하여 삽입 속도를 언급할 수 있다. 왜냐하면 innodb는 기본키 클러스터 인덱스이고 실제 기본키이기 때문이다. 키 값은 기본 키 순서대로 접근해야 하므로 자동으로 ID 증가 자체가 오름차순으로 이루어지므로 데이터 삽입 시 하위 레이어에서는 추가적인 정렬 작업을 할 필요가 없으며, 인덱스 페이지 분할 횟수를 줄여서 삽입 속도를 크게 높입니다(다른 솔루션에서도 기본 키가 완전히 자동 증가되는 것을 보장하지 않는 한)

[관련 권장 사항]

1. >Mysql 무료 동영상 튜토리얼

2.

MySQL에 새 사용자 권한 추가 예시에 대한 자세한 설명

비밀번호 변경 예시에 대한 자세한 설명 및 MySQL의 액세스 제한

4.

정규식을 사용하여 데이터베이스 내용 교체

예제에 대한 자세한 설명 mysql에 이미지를 저장하는 PHP

위 내용은 mysql의 innodb_autoinc_lock_mode에 대한 자세한 예의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!