관련 무료 학습 권장사항: mysql 튜토리얼
이전에 우리는 MySQL 데이터베이스의 기본 데이터 구조 및 알고리즘과 MySQL 성능 최적화의 일부 내용에 대해 이야기했습니다. 그리고 이전 기사에서는 MySQL의 행 잠금 및 트랜잭션 격리 수준에 대해 설명했습니다. 이 문서에서는 잠금 유형과 잠금 원리에 중점을 둘 것입니다.
먼저 MySQL 잠금을 나눕니다.
테이블 수준 잠금은 MySQL 잠금 중 가장 세부적인 잠금입니다. 즉, 현재 작업이 행 잠금보다 리소스 오버헤드가 적고 교착 상태가 발생하지 않습니다. 하지만 잠금 충돌 가능성은 매우 높습니다. 대부분의 mysql 엔진에서 지원되는 MyISAM과 InnoDB는 모두 테이블 수준 잠금을 지원하지만 InnoDB는 기본적으로 행 수준 잠금을 사용합니다.
테이블 잠금은 MySQL 서버에 의해 구현됩니다. 일반적으로 ALTER TABLE 및 기타 작업과 같은 DDL 문을 실행할 때 전체 테이블이 잠깁니다. SQL 문을 실행할 때 잠길 테이블을 명시적으로 지정할 수도 있습니다. 테이블 잠금은 일회성 잠금 기술을 사용합니다. 즉, 세션 시작 시 잠금 명령을 사용하여 나중에 사용할 모든 테이블을 잠그면 해당 잠긴 테이블에만 액세스할 수 있습니다. 잠금 해제 테이블을 통해 모든 테이블 잠금이 최종적으로 해제될 때까지 다른 테이블에 액세스할 수 없습니다. 잠금 해제를 표시하기 위해 잠금 해제 테이블을 사용하는 것 외에도, 세션이 다른 테이블 잠금을 보유하고 있을 때 잠금 테이블 문을 실행하면 세션이 이전에 보유하고 있던 잠금이 해제되거나 세션이 다른 테이블 잠금을 보유할 때 시작을 실행하면 해제됩니다. 이전에 잠긴 잠금.공유 잠금 사용법:
LOCK TABLE table_name [ AS alias_name ] READ复制代码
독점 잠금 사용법:
LOCK TABLE table_name [AS alias_name][ LOW_PRIORITY ] WRITE复制代码
잠금 해제 사용법:
unlock tables;复制代码
행 수준 잠금은 데이터베이스 작업의 충돌을 크게 줄일 수 있습니다. 잠금 세분성은 가장 작지만 잠금 오버헤드도 가장 큽니다. 교착상태 상황이 발생할 수 있습니다. 행 수준 잠금은 사용 방법에 따라 공유 잠금과 전용 잠금으로 구분됩니다.
스토리지 엔진마다 행 잠금 구현이 다릅니다. 나중에 특별한 설명이 없으면 행 잠금은 구체적으로 InnoDB에서 구현하는 행 잠금을 나타냅니다. InnoDB의 잠금 원리를 이해하기 전에 저장 구조에 대한 어느 정도 이해가 필요합니다. InnoDB는 클러스터형 인덱스입니다. 즉, B+ 트리의 리프 노드는 기본 키 인덱스와 데이터 행을 모두 저장합니다. InnoDB 보조 인덱스의 리프 노드에는 기본 키 값이 저장되므로 보조 인덱스를 통해 데이터를 쿼리할 때는 클러스터형 인덱스에서 해당 기본 키를 가져와서 다시 쿼리해야 합니다. MySQL 인덱스에 대한 자세한 내용은 "MySQL 인덱스 기본 데이터 구조 및 알고리즘"을 참조하세요.
update user set age = 10 where id = 49; update user set age = 10 where name = 'Tom';复制代码
update user set age = 10 where id > 49;复制代码
읽기 잠금이라고도 하는 공유 잠금은 읽기 작업에 의해 생성되는 잠금입니다. 다른 사용자는 동시에 데이터를 읽을 수 있지만 모든 공유 잠금이 해제될 때까지 어떤 트랜잭션도 데이터를 수정할 수 없습니다(데이터에 대한 배타적 잠금 획득).
트랜잭션 T가 데이터 A에 공유 잠금을 추가하면 다른 트랜잭션은 A에 공유 잠금만 추가할 수 있고 배타적 잠금은 추가할 수 없습니다. 공유 잠금이 부여된 트랜잭션은 데이터를 읽을 수만 있고 데이터를 수정할 수는 없습니다.
SELECT ... LOCK IN SHARE MODE;SELECT ... LOCK IN SHARE MODE;
在查询语句后面增加LOCK IN SHARE MODE，Mysql会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。
排他锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。
SELECT ... FOR UPDATE;
在查询语句后面增加FOR UPDATE
LOCK IN SHARE MODE를 추가하면 MySQL에서 공유 잠금을 추가합니다. 다른 스레드는 쿼리 결과 집합의 모든 행에 배타적 잠금을 사용하지 않으며 공유 잠금을 성공적으로 적용할 수 있습니다. 그렇지 않으면 차단됩니다. 다른 스레드도 공유 잠금을 사용하는 테이블을 읽을 수 있으며 이러한 스레드는 동일한 버전의 데이터를 읽습니다. 배타적 잠금은 쓰기 잠금이라고도 합니다. 트랜잭션 T가 데이터 A에 배타적 잠금을 추가하면 다른 트랜잭션은 A에 더 이상 추가할 수 없습니다. 봉쇄의. 배타적 잠금이 부여된 트랜잭션은 데이터를 읽고 수정할 수 있습니다.
SELECT ... FOR UPDATE;쿼리 문 뒤에 FOR UPDATE를 추가하면 Mysql은 쿼리 결과 집합의 각 행에 배타적 잠금이 추가됩니다. 쿼리 결과 집합의 모든 행에 대해 다른 스레드가 배타적 잠금을 사용하지 않으면 배타적 잠금을 성공적으로 적용할 수 있으며, 그렇지 않으면 차단됩니다. 낙관적 잠금과 비관적 잠금데이터베이스의 잠금 메커니즘에 도입된 것처럼 데이터베이스 관리 시스템(DBMS)에서 동시성 제어 작업은 여러 트랜잭션이 동일한 데이터에 액세스할 때 트랜잭션의 격리가 파괴되지 않도록 하는 것입니다. 데이터베이스의 통일성과 데이터베이스의 통일성을 동시에 유지합니다. 비관적 잠금이든 낙관적 잠금이든 모두 사람들이 정의한 개념이고 일종의 생각이라고 볼 수 있습니다. 실제로 관계형 데이터베이스 시스템에는 낙관적 잠금(Optimistic Locking)과 비관적 잠금(Pessimistic Locking)이라는 개념이 존재할 뿐만 아니라, Memcache, Hibernate, tair 등도 비슷한 개념을 갖고 있다. 다양한 비즈니스 시나리오에서는 다양한 동시성 제어 방법을 선택해야 합니다. 그러므로 낙관적 동시성 제어와 비관적 동시성 제어를 좁은 의미의 DBMS 개념으로 이해하지 마시고, 데이터에서 제공하는 잠금 메커니즘(행 잠금, 테이블 잠금, 배타적 잠금, 공유 잠금)과 혼동하지 마십시오. 실제로 DBMS에서는 데이터베이스 자체에서 제공하는 잠금 메커니즘을 사용하여 비관적 잠금을 구현합니다. 비관적 잠금낙관적 동시성 제어(낙관적 잠금)와 비관적 동시성 제어(비관적 잠금)는 동시성 제어에 사용되는 주요 기술 수단입니다.
잠금에 실패하면 레코드가 잠금 해제되었음을 의미합니다. 수정 중인 경우 현재 쿼리가 기다리거나 예외가 발생할 수 있습니다. 구체적인 응답 방법은 실제 필요에 따라 개발자가 결정합니다.
🎜잠금에 성공하면 기록이 수정될 수 있으며 거래가 완료된 후 잠금이 해제됩니다. 🎜🎜레코드를 수정하거나 단독 잠금을 추가하는 다른 작업이 있는 경우 해당 작업은 우리가 잠금을 해제할 때까지 기다리거나 직접 예외를 발생시킵니다. 🎜🎜🎜비관적 잠금의 장점과 단점🎜🎜비관적 잠금은 실제로 "액세스하기 전에 먼저 잠금을 확보"하는 전략을 채택하여 데이터 처리의 보안을 보장합니다. 그러나 효율성 측면에서 추가 잠금 메커니즘으로 인해. , 추가 오버헤드가 발생하고 교착 상태 가능성이 높아집니다. 그리고 이는 동시성을 감소시킵니다. 한 항목이 데이터 행을 획득하면 다른 항목은 이 데이터 행에 대해 작업을 수행하기 전에 트랜잭션이 제출될 때까지 기다려야 합니다. 🎜관계형 데이터베이스 관리 시스템에서 낙관적 동시성 제어("낙관적 잠금", 낙관적 동시성 제어, 약어 "OCC"라고도 함)는 동시성 제어 방법입니다. 다중 사용자 동시 트랜잭션은 처리 중에 서로 영향을 미치지 않으며 각 트랜잭션은 잠금을 생성하지 않고도 영향을 받는 데이터 부분을 처리할 수 있다고 가정합니다. 데이터 업데이트를 커밋하기 전에 각 트랜잭션은 먼저 트랜잭션이 데이터를 읽은 후 다른 트랜잭션이 데이터를 수정했는지 여부를 확인합니다. 다른 트랜잭션에 업데이트가 있으면 제출 중인 트랜잭션이 롤백됩니다.
낙관적 잠금 비관적 잠금과 비교하여 낙관적 잠금은 일반적으로 데이터가 충돌을 일으키지 않는다고 가정하므로 업데이트를 위해 데이터를 제출할 때 데이터의 충돌이 공식적으로 감지됩니다. 충돌이 발견되면 오류 정보가 표시됩니다. 사용자에게 반환되며 사용자는 무엇을 할지 결정할 수 있습니다.
비관적 잠금과 비교하여 낙관적 잠금은 데이터베이스를 처리할 때 데이터베이스가 제공하는 잠금 메커니즘을 사용하지 않습니다. 낙관적 잠금을 구현하는 일반적인 방법은 데이터 버전을 기록하는 것입니다.
데이터 버전, 데이터에 추가된 버전 식별자입니다. 데이터를 읽을 때 버전 식별자의 값을 함께 읽습니다. 데이터가 업데이트될 때마다 버전 식별자도 동시에 업데이트됩니다. 업데이트를 제출할 때 데이터베이스 테이블의 해당 레코드의 현재 버전 정보를 처음 가져온 버전 식별 값과 비교합니다. 처음으로 업데이트하십시오. 그렇지 않으면 만료된 데이터로 간주됩니다.
낙관적 동시성 제어는 트랜잭션 간의 데이터 경합 가능성이 상대적으로 적다고 생각하므로 최대한 직접적으로 수행하고, 제출 시점까지 잠금을 설정하지 않으므로 잠금이 발생하지 않습니다. 모든 잠금 및 교착 상태가 발생합니다. 그러나 이렇게 간단하게 수행하면 여전히 예상치 못한 결과가 발생할 수 있습니다. 예를 들어 두 트랜잭션이 데이터베이스의 특정 행을 읽은 다음 수정 후 데이터베이스에 다시 쓰면 문제가 발생합니다.
테이블 잠금과 행 잠금은 잠금 범위가 다르기 때문에 서로 충돌합니다. 따라서 테이블 잠금을 추가하려면 먼저 테이블의 모든 레코드를 탐색하여 배타적 잠금이 추가되었는지 확인해야 합니다. 이 순회 확인 방법은 분명히 비효율적인 방법입니다. MySQL은 테이블 잠금과 행 잠금 간의 충돌을 감지하기 위해 의도 잠금을 도입합니다.
의도 잠금도 테이블 수준 잠금이며 읽기 의도 잠금(IS 잠금)과 쓰기 의도 잠금(IX 잠금)으로 나눌 수도 있습니다. 트랜잭션이 레코드에 읽기 잠금 또는 쓰기 잠금을 추가하려면 먼저 테이블에 의도 잠금을 추가해야 합니다. 이런 식으로 테이블에 잠긴 레코드가 있는지 판단하는 것은 매우 간단합니다. 테이블에 의도적인 잠금이 있는지 확인하면 됩니다.
의도 잠금은 서로 충돌하지 않으며 AUTO_INC 테이블 잠금과도 충돌하지 않습니다. 테이블 수준 읽기 잠금이나 테이블 수준 쓰기 잠금만 차단합니다. 또한 의도 잠금은 행 잠금과만 충돌하지 않습니다. 블록 테이블 수준 읽기 잠금 또는 테이블 수준 쓰기 잠금은 행 잠금과 충돌합니다.
의도 잠금은 InnoDB에 의해 자동으로 추가되며 사용자 개입이 필요하지 않습니다.
삽입, 업데이트 및 삭제의 경우 InnoDB는 관련 데이터에 자동으로 배타적 잠금(X)을 추가합니다.
일반 Select 문의 경우 InnoDB는 공유 잠금을 추가하여 트랜잭션을 표시할 수 없습니다. 다음 명령문 또는 배타적 잠금.
의도 공유 잠금(IS): 트랜잭션이 데이터 행에 공유 잠금을 추가할 준비를 하고 있음을 나타냅니다. 즉, 데이터 행에 공유 잠금을 추가하기 전에 IS가 테이블의 잠금을 먼저 획득해야 합니다
Intention Exclusive Lock(IX): 위와 유사하게 트랜잭션이 데이터 행에 배타적 잠금을 추가할 준비를 하고 있음을 의미합니다. 트랜잭션은 데이터 행에 배타적 잠금을 추가하기 전에 먼저 테이블의 IX 잠금을 획득해야 합니다.
레코드 잠금은 가장 간단한 행 잠금이며 이에 대해 말할 것도 없습니다. 위에서 설명한 InnoDB 잠금 원리의 잠금은 레코드 잠금으로, ID = 49 또는 이름 = 'Tom'인 레코드만 잠급니다.
SQL 문이 인덱스를 사용할 수 없는 경우 전체 테이블 스캔이 수행됩니다. 이때 MySQL은 전체 테이블의 모든 데이터 행에 레코드 잠금을 추가한 다음 MySQL 서버 계층에서 이를 필터링합니다. 그러나 MySQL Server 계층에서 필터링을 할 때 WHERE 조건을 만족하지 않는 것으로 확인되면 해당 레코드에 대한 잠금이 해제된다. 이렇게 하면 조건을 만족하는 레코드에 대해서만 잠금이 유지되지만, 각 레코드에 대한 잠금 작업은 생략할 수 없습니다.
그래서 업데이트 작업은 인덱스를 기반으로 수행되어야 합니다. 인덱스가 없으면 많은 잠금 리소스를 소비하고 데이터베이스의 오버헤드가 증가할 뿐만 아니라 데이터베이스의 동시성 성능도 크게 저하됩니다.
동일 조건 대신 범위 조건을 사용하여 데이터를 검색하고 공유 또는 배타적 잠금을 요청하면 InnoDB는 다음과 같은 키 값에 대한 조건을 충족하는 기존 데이터 레코드의 인덱스 항목을 잠급니다. 조건 범위 내에 있지만 존재하지 않는 경우 InnoDB는 "간격"도 잠급니다. 이 잠금 메커니즘을 소위 간극 잠금이라고 합니다.
间隙锁是锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。
间隙锁在 InnoDB 的唯一作用就是防止其它事务的插入操作，以此来达到防止幻读的发生，所以间隙锁不分什么共享锁与排他锁。
要禁止间隙锁，可以把隔离级别降为读已提交，或者开启参数 innodb_locks_unsafe_for_binlog
show variables like 'innodb_locks_unsafe_for_binlog';复制代码
innodb_locks_unsafe_for_binlog：默认
值为OFF，即启用间隙锁。因为此参数是只读模式，如果想要禁用间隙锁，需要修改 my.cnf（windows是my.ini） 重新启动才行。
# 在 my.cnf 里面的[mysqld]添加 [mysqld] innodb_locks_unsafe_for_binlog = 1复制代码
测试环境：
MySQL5.7，InnoDB，默认的隔离级别（RR）
示例表：
CREATE TABLE `my_gap` ( `id` int(1) NOT NULL AUTO_INCREMENT, `name` varchar(8) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `my_gap` VALUES ('1', '张三');INSERT INTO `my_gap` VALUES ('5', '李四');INSERT INTO `my_gap` VALUES ('7', '王五');INSERT INTO `my_gap` VALUES ('11', '赵六');复制代码
在进行测试之前，我们先看看 my_gap 表中存在的隐藏间隙：
/* 开启事务1 */BEGIN;/* 查询 id = 5 的数据并加记录锁 */SELECT * FROM `my_gap` WHERE `id` = 5 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30); # 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句/* 事务2插入一条 name = '杰伦' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (4, '杰伦'); # 正常执行/* 事务3插入一条 name = '学友' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (8, '学友'); # 正常执行/* 提交事务1，释放事务1的锁 */COMMIT;复制代码
上述案例，由于主键是唯一索引，而且只使用一个索引查询，并且只锁定了一条记录，所以只会对 id = 5 的数据加上记录锁（行锁），而不会产生间隙锁。
恢复初始化的4条记录，继续在 id 唯一索引列上做以下测试：
/* 开启事务1 */BEGIN;/* 查询 id 在 7 - 11 范围的数据并加记录锁 */SELECT * FROM `my_gap` WHERE `id` BETWEEN 5 AND 7 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30); # 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句/* 事务2插入一条 id = 3，name = '思聪3' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (3, '思聪3'); # 正常执行/* 事务3插入一条 id = 4，name = '思聪4' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (4, '思聪4'); # 正常执行/* 事务4插入一条 id = 6，name = '思聪6' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (6, '思聪6'); # 阻塞/* 事务5插入一条 id = 8， name = '思聪8' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (8, '思聪8'); # 阻塞/* 事务6插入一条 id = 9， name = '思聪9' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (9, '思聪9'); # 阻塞/* 事务7插入一条 id = 11， name = '思聪11' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (11, '思聪11'); # 阻塞/* 事务8插入一条 id = 12， name = '思聪12' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (12, '思聪12'); # 正常执行/* 提交事务1，释放事务1的锁 */COMMIT;复制代码
从上面可以看到，(5,7]、（7,11] 这两个区间，都不可插入数据，其它区间，都可以正常插入数据。所以可以得出结论：当我们给(5,7] 这个区间加锁的时候，会锁住(5,7]、(7,11] 这两个区间。
恢复初始化的4条记录，我们再来测试如果锁住不存在的数据时，会如何？
/* 开启事务1 */BEGIN;/* 查询 id = 3 这一条不存在的数据并加记录锁 */SELECT * FROM `my_gap` WHERE `id` = 3 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30); # 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句/* 事务2插入一条 id = 3，name = '小张' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (2, '小张'); # 阻塞/* 事务3插入一条 id = 4，name = '小白' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (4, '小白'); # 阻塞/* 事务4插入一条 id = 6，name = '小东' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (6, '小东'); # 正常执行/* 事务5插入一条 id = 8， name = '大罗' 的数据 */INSERT INTO `my_gap` (`id`, `name`) VALUES (8, '大罗'); # 正常执行/* 提交事务1，释放事务1的锁 */COMMIT;复制代码
从上面可以看出，指定查询某一条记录时，如果这条记录不存在，会产生间隙锁。
示例表：id 是主键，在 number 上，建立了一个普通索引。
# 注意：number 不是唯一值CREATE TABLE `my_gap1` ( `id` int(1) NOT NULL AUTO_INCREMENT, `number` int(1) NOT NULL COMMENT '数字', PRIMARY KEY (`id`), KEY `number` (`number`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;INSERT INTO `my_gap1` VALUES (1, 1);INSERT INTO `my_gap1` VALUES (5, 3);INSERT INTO `my_gap1` VALUES (7, 8);INSERT INTO `my_gap1` VALUES (11, 12);复制代码
在进行测试之前，我们先来看看 my_gap1 表中 number 索引存在的隐藏间隙：
我们执行以下的事务（事务1最后提交），分别执行下面的语句：
/* 开启事务1 */BEGIN;/* 查询 number = 3 的数据并加记录锁 */SELECT * FROM `my_gap1` WHERE `number` = 3 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30); # 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句/* 事务2插入一条 number = 0 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (0); # 正常执行/* 事务3插入一条 number = 1 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (1); # 被阻塞/* 事务4插入一条 number = 2 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (2); # 被阻塞/* 事务5插入一条 number = 4 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (4); # 被阻塞/* 事务6插入一条 number = 8 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (8); # 正常执行/* 事务7插入一条 number = 9 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (9); # 正常执行/* 事务8插入一条 number = 10 的数据 */INSERT INTO `my_gap1` (`number`) VALUES (10); # 正常执行/* 提交事务1 */COMMIT;复制代码
我们会发现有些语句可以正常执行，有些语句被阻塞来。查看表中的数据：
这里可以看到，number(1,8) 的间隙中，插入语句都被阻塞来，而不在这个范围内的语句，正常执行，这就是因为有间隙锁的原因。
我们再进行以下测试，这里将数据还原成初始化那样
/* 开启事务1 */BEGIN;/* 查询 number = 3 的数据并加记录锁 */SELECT * FROM `my_gap1` WHERE `number` = 3 FOR UPDATE;/* 延迟30秒执行，防止锁释放 */SELECT SLEEP(30);/* 事务1插入一条 id = 2， number = 1 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (2, 1); # 阻塞/* 事务2插入一条 id = 3， number = 2 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (3, 2); # 阻塞/* 事务3插入一条 id = 6， number = 8 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (6, 8); # 阻塞/* 事务4插入一条 id = 8， number = 8 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (8, 8); # 正常执行/* 事务5插入一条 id = 9， number = 9 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (9, 9); # 正常执行/* 事务6插入一条 id = 10， number = 12 的数据 */INSERT INTO `my_gap1` (`id`, `number`) VALUES (10, 12); # 正常执行/* 事务7修改 id = 11， number = 12 的数据 */UPDATE `my_gap1` SET `number` = 5 WHERE `id` = 11 AND `number` = 12; # 阻塞/* 提交事务1 */COMMIT;复制代码
查看表中的数据；
这里有一个奇怪的现象：
这是为什么？我们来看看下面的图：
从图中库看出，当 number 相同时，会根据主键 id 来排序
临键锁，是记录锁（行锁）与间隙锁的组合，它的锁范围，即包含索引记录，又包含索引区间。它指的是加在某条记录以及这条记录前面间隙上的锁。假设一个索引包含 15、18、20 ，30，49，50 这几个值，可能的 Next-key 锁如下：
(-∞, 15]，(15, 18]，(18, 20]，(20, 30]，(30, 49]，(49, 50]，(50, +∞)复制代码
通常我们都用这种左开右闭区间来表示 Next-key 锁，其中，圆括号表示不包含该记录，方括号表示包含该记录。前面四个都是 Next-key 锁，最后一个为间隙锁。和间隙锁一样，在 RC 隔离级别下没有 Next-key 锁，只有 RR 隔离级别才有。还是之前的例子，如果 id 不是主键，而是二级索引，且不是唯一索引，那么这个 SQL 在 RR 隔离级别下就会加如下的 Next-key 锁 (30, 49](49, 50)
此时如果插入一条 id = 31 的记录将会阻塞住。之所以要把 id = 49 前后的间隙都锁住，仍然是为了解决幻读问题，因为 id 是非唯一索引，所以 id = 49 可能会有多条记录，为了防止再插入一条 id = 49 的记录。
注意：临键锁的主要目的，也是为了避免幻读（Phantom Read）。如果把事务隔离级别降级为 RC，临键锁则也会失效。
插入意向锁是一种特殊的间隙锁（简称II GAP）表示插入的意向，只有在 INSERT 的时候才会有这个锁。注意，这个锁虽然也叫意向锁，但是和上面介绍的表级意向锁是两个完全不同的概念，不要搞混了。
插入意向锁和插入意向锁之间互不冲突，所以可以在同一个间隙中有多个事务同时插入不同索引的记录。譬如在例子中，id = 30 和 id = 49 之间如果有两个事务要同时分别插入 id = 32 和 id = 33 是没问题的，虽然两个事务都会在 id = 30 和 id = 50 之间加上插入意向锁，但是不会冲突。
插入意向锁只会和间隙锁或 Next-key 锁冲突，正如上面所说，间隙锁唯一的作用就是防止其他事务插入记录造成幻读，正是由于在执行 INSERT 语句时需要加插入意向锁，而插入意向锁和间隙锁冲突，从而阻止了插入操作的执行。
插入意向锁的作用：
AUTO_INC 锁又叫自增锁（一般简写成 AI 锁），是一种表锁，当表中有自增列（AUTO_INCREMENT）时出现。当插入表中有自增列时，数据库需要自动生成自增值，它会先为该表加 AUTO_INC 表锁，阻塞其他事务的插入操作，这样保证生成的自增值肯定是唯一的。AUTO_INC 锁具有如下特点：
使用AUTO_INCREMENT 函数实现自增操作，自增幅度通过 auto_increment_offset和auto_increment_increment这2个参数进行控制：
通过使用last_insert_id()函数可以获得最后一个插入的数字
select last_insert_id();复制代码
首先insert大致上可以分成三类：
如果存在自增字段，MySQL 会维护一个自增锁，和自增锁相关的一个参数为（5.1.22 版本后加入） innodb_autoinc_lock_mode ，可以设定 3 值：
MyISam引擎均为 traditonal，每次均会进行表锁。但是InnoDB引擎会视参数不同产生不同的锁，默认为 1：consecutive。
show variables like 'innodb_autoinc_lock_mode';复制代码
innodb_autoinc_lock_mode 为 0 时，也就是 traditional 级别。该自增锁时表锁级别，且必须等待当前 SQL 执行完毕后或者回滚才会释放，在高并发的情况下可想而知自增锁竞争时比较大的。
innodb_autoinc_lock_mode 为 1 时，也就是 consecutive 级别。这是如果是单一的 insert SQL，可以立即获得该锁，并立即释放，而不必等待当前SQL执行完成（除非在其它事务中已经有 session 获取了自增锁）。另外当SQL是一些批量 insert SQL 时，比如 insert into ... select ... ， load data , replace ... select ... 时，这时还是表级锁，可以理解为退化为必须等待当前 SQL 执行完才释放。可以认为，该值为 1 时相对比较轻量级的锁，也不会对复制产生影响，唯一的缺陷是产生自增值不一定是完全连续的。
innodb_autoinc_lock_mode 为 2 时，也就是 interleaved 级别。所有 insert 种类的 SQL 都可以立马获得锁并释放，这时的效率最高。但是会引入一个新的问题：当 binlog_format 为 statement 时，这是复制没法保证安全，因为批量的 insert，比如 insert ... select ... 语句在这个情况下，也可以立马获取到一大批的自增 id 值，不必锁整个表， slave 在回放这个 SQL 时必然会产生错乱。
如果你的二进制文件格式是mixed | row 那么这三个值中的任何一个对于你来说都是复制安全的。
由于现在mysql已经推荐把二进制的格式设置成row，所以在binlog_format不是statement的情况下最好是innodb_autoinc_lock_mode=2 这样可能知道更好的性能。
잠금 모드에는 읽기 의도 잠금, 쓰기 의도 잠금, 읽기 잠금, 쓰기 잠금 및 자동 증가 잠금(auto_inc)이 있습니다.
| IS | IX | S | X | AI | |
|---|---|---|---|---|---|
| IS | 호환 | 호환 | 호환 | 호환 | |
| IX | 호환 | 호환 가능 | 호환 가능 | ||
| S | 호환 | 호환 | |||
|
AI |
호환 | ||||
| 요약하자면 다음과 같습니다. | 의도 잠금은 서로 충돌하지 않습니다. |
S 잠금 S/IS 잠금과만 호환됩니다. | 테이블 잠금 과 | 행 잠금
Gap Lock gap lock
,다양한 유형의 자물쇠에 대한 호환성 매트릭스RECORD
GAP
NEXT-KEY| 기록 | 호환 가능 | 호환 가능 | ||
|---|---|---|---|---|
| GAP | 호환 가능 | 호환 |
호환 |
호환 |
| NEXT -KEY | 호환 가능 | Compatible |
위 내용은 오늘날 MySQL 잠금 유형 및 잠금 원리에 대한 심층적인 이해의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!
![[웹 프런트엔드] Node.js 빠른 시작](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
