MySQL 잠금 메커니즘 재검토

기사 디렉터리

• Lock
• • 잠금 분류
  • 공유 잠금(읽기 잠금, 읽기 및 쓰기 상호 배제, 읽기와 읽기는 서로 영향을 주지 않음)
  • 독점 잠금(쓰기 잠금, 독점 잠금)
  • 공유 잠금과 배타 잠금의 차이점
• 테이블 잠금
• • 의도 잠금: 의도 공유 잠금 및 의도 배타 잠금
  • • 의도 잠금을 추가해야 하는 이유
  • 싱글 테이블 lock
  • 전역 테이블 잠금
• Row lock
• Row 잠금이 테이블 잠금으로 업그레이드되었습니다. (row 잠금은 실제로 인덱스를 잠그는 방식입니다. 인덱스를 사용하지 않고 테이블 전체를 스캔하면 테이블 전체가 잠깁니다.)
• 특정 행 레코드를 수정하거나 삭제하면 행 잠금이 잠기지 않습니다. 트랜잭션을 커밋할 때 행은 항상 행 잠금으로 잠깁니다.
• Record lock
• Gap Locks
• • 갭 잠금의 역할: 팬텀 읽기 방지
  • innodb가 자동으로 갭 잠금을 사용하는 조건
  • Gap 잠금 ​​
  • 다음 키 잠금 영역 [프로 키 잠금]

• 레코드 잠금, 갭 잠금 및 임시 잠금

관련 무료 학습 권장사항: mysql 비디오 튜토리얼

Lock

Lock은 컴퓨터가 동일한 공유 리소스에 액세스하는 여러 스레드를 조정하는 메커니즘입니다. 동일한 공유 리소스에 액세스하는 여러 스레드의 동시성 보안 문제.

잠금 분류

(1) 성능 측면에서 보면 낙관적 잠금과 비관적 잠금으로 구분됩니다.
MySQL은 버전 번호 제어를 사용하고 JVM은 CAS를 사용합니다

(2) 데이터베이스 작업 유형에서 , 읽기 잠금(공유 잠금), 쓰기 잠금(배타적 잠금)으로 나뉩니다. [읽기 잠금과 쓰기 잠금은 모두 비관적 잠금입니다.]

• 읽기 잠금(공유 잠금): 동일한 행 레코드에 대해 여러 읽기 작업이 가능합니다. 동시에 수행되지만 트랜잭션은 수행할 수 없습니다.
• 쓰기 잠금(배타적 잠금): 배타적 잠금을 획득한 트랜잭션은 데이터를 읽을 수도 있고 데이터를 수정할 수도 있습니다. 쓰기 잠금을 획득한 트랜잭션이 완료될 때까지 다른 트랜잭션이 쓰기 잠금이나 읽기 잠금을 획득하지 못하도록 차단합니다.

(3) 세분화하여 테이블 잠금과 행 잠금으로 구분됩니다.

참고:
(1) 읽기 잠금과 쓰기 잠금은 행 수준 잠금입니다. 즉, 트랜잭션 1이 쓰기 잠금을 획득합니다. 제품 A에 대한 트랜잭션 2의 제품 B에 대한 쓰기 잠금 획득은 서로를 차단하지 않습니다.
(2) SQL 문이 행 잠금을 사용하는 경우 SQL이 인덱스를 사용하지 않고 전체 테이블 스캔을 사용하면 행 수준 잠금이 테이블 잠금이 됩니다.

(3) 잠금은 커밋이나 롤백이 실행될 때만 해제되며, 모든 잠금이 동시에 해제됩니다.
(4) Innodb 엔진은 일반적인 수정 및 삭제 SQL 문을 실행할 때 수정 및 삭제된 행에 행 잠금을 추가합니다.

공유 잠금(읽기 잠금, 읽기 및 쓰기 상호 배제, 읽기와 읽기는 서로 영향을 주지 않음)

트랜잭션 A가 공유 잠금을 사용하여 특정(또는 일부) 레코드를 획득하면 트랜잭션 B는 이러한 레코드를 읽을 수 있습니다. 계속할 수 있습니다. 공유 잠금을 추가하지만 이러한 레코드를 수정할 수 없습니다(트랜잭션 C가 이러한 데이터를 수정하거나 삭제하면 잠금 대기 시간이 초과되거나 트랜잭션 A가 커밋될 때까지 차단 상태에 들어갑니다)

• 공유 잠금 및 해제 사용 방법 shared locks

# 加锁SELECT ... LOCK IN SHARE MODE# 释放锁commit;rollback;

• Function
  SELECT ... LOCK IN SHARE MODE는 읽은 레코드의 여러 행에 공유 잠금(공유 잠금)을 추가합니다. 다른 트랜잭션은 이러한 행을 쿼리할 수만 있지만 이러한 레코드를 수정할 수는 없습니다. 여러 트랜잭션이 동일한 행 레코드에 공유 잠금을 추가할 수 있으므로 공유 잠금을 획득하는 트랜잭션이 반드시 행 데이터를 수정할 수 없을 수도 있습니다

• 사용 시나리오: 다른 것을 방지하면서 결과 집합의 최신 버전을 읽습니다. 결과 세트
  예: 제품 재고에 대한 동시 작업

배타적 잠금(쓰기 잠금, 배타적 잠금)

select... 업데이트를 위해 행 레코드 읽기에 배타적 잠금을 추가하고, 일반 업데이트 문이 실행될 때 행 잠금이 추가되는 것처럼 잠금 트랜잭션은 행 레코드를 수정하고 다른 트랜잭션이 행을 수정하는 것을 방지합니다.

• 배타적 잠금 사용 방법

# 加排他锁select ... for update //排他锁 # 释放锁commit;rollback;

공유 잠금과 배타적 잠금의 차이점

(1) 한 거래가 배타적 잠금을 획득하면 다른 거래는 더 이상 배타적 잠금을 획득할 수 없습니다.
여러 트랜잭션이 동일한 데이터 행에 공유 잠금을 추가할 수 있습니다.
(2) 지정된 행에 공유 잠금을 추가하는 트랜잭션은 반드시 행 데이터를 수정하지 못할 수도 있습니다. 다른 트랜잭션도 배타적 잠금을 추가하는 트랜잭션에 공유 잠금 또는 배타적 잠금을 추가할 수 있기 때문입니다. 지정된 행에 배타적 잠금을 사용하여 데이터 행을 수정할 수 있습니다

테이블 잠금

테이블 수준 잠금은 주로 MyISAM, MEMORY, CSV와 같은 일부 비트랜잭션 스토리지 엔진에서 사용됩니다.
테이블 잠금은 일반적으로 데이터 마이그레이션 중에 사용됩니다.

의도 잠금: 의도 공유 잠금 및 의도 배타 잠금

행에 공유 잠금을 추가하는 전제는 행의 데이터가 있는 테이블이 먼저 의도 공유 잠금을 획득한다는 것입니다.
행에 배타적 잠금을 추가하기 위한 전제 조건은 해당 행의 데이터가 있는 테이블이 먼저 의도한 배타적 잠금을 획득한다는 것입니다.
참고: 의도 공유 잠금 및 의도 배타적 잠금은 테이블 잠금이며 수동으로 생성할 수 없습니다.

의도 잠금을 추가해야 하는 이유

意向锁是为了告知mysql该表已经存在数据被加锁，而不需要逐行扫描是否加锁，提搞加锁的效率。

单个表锁定

lock tables saas.goods read,saas.account write;　　// 给saas库中的goods表加读锁,account表加写锁unlock tables;   //解锁

全局表锁定

FLUSH TABLES WITH READ LOCK; 　　// 所有库所有表都被锁定只读unlock tables;         //解锁

注意： 在客户端和数据库断开连接时，都会隐式的执行unlock tables。如果要让表锁定生效就必须一直保持连接。

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
  行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；

行锁

• 行锁是锁一行或者多行记录
• MySQL的行锁是基于索引，行锁是加在索引上，而不是加在行记录上的。
  
  如上图所示，数据库中有1个主键索引和1个普通索引，图中的sql语句是基于普通索引查询，命中4条记录，此时一把行锁就锁定两条记录，而其他事务修改这两条记录中的任意一条，都会一直阻塞【获取锁的事务没有执行commit之前】，下图就是上图没有执行commit语句时的情况。
  

行锁升级为表锁（行锁实际是给索引加锁，如果没用索引而全表扫描，则会给全表加锁）

上图中where条件中，虽然template_name建立普通索引，但使用or关键字，导致template_name的索引失效，从而进行了全表扫描，锁定了整张表。

修改、删除某一行记录，且未提交事务时，该行会一直被行锁锁定

窗口1中删除某一行，但没有提交。窗口2中更新该行会一直处于阻塞中。

记录锁

• 行锁：行锁是命中索引，一把锁锁的是一张表的一条记录或多条记录
• 记录锁：记录锁是在行锁的衍生锁，记录锁锁的是表中的某一条记录，记录锁出现的条件必须是：精确命中索引，且索引是唯一索引（比如主键id、唯一索引列）。

间隙锁（Gap Locks）

经典参考文章

间隙锁的作用：防止幻读

间隙锁的目的是为了防止幻读，其主要通过两个方面实现这个目的：
（1）防止止间隙内有新数据被插入
（2）防止范围内已存在的数据被更新

innodb自动使用间隙锁的条件

（1）数据隔离级别必须为可重复读
（2）检索条件必须使用索引（没有使用索引的话，mysql会全表扫描，那样会锁定整张表所有的记录，包括不存在的记录，此时其他事务不能修改不能删除不能添加）

间隙锁锁定的区域

根据检索条件向左寻找最靠近检索条件的记录值A，作为左区间，向右寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为（A，B）。下图中，where number=5的话，那么间隙锁的区间范围为[4,11]；

session 1:start  transaction ;触发间隙锁的方式1：select  * from news where number=4 for update ;触发间隙锁的方式2：update news set number=3 where number=4; session 2:start  transaction ;insert into news value(2,4);#（阻塞）insert into news value(2,2);#（阻塞）insert into news value(4,4);#（阻塞）insert into news value(4,5);#（阻塞）insert into news value(7,5);#（执行成功）insert into news value(9,5);#（执行成功）insert into news value(11,5);#（执行成功）

next-key锁【临键锁】

next-key锁其实包含了记录锁和间隙锁，即锁定一个范围，并且锁定记录本身。InnoDB默认加锁方式是next-key 锁。

select * from news where number=4 for update ;

next-key锁锁定的范围为间隙锁+记录锁，即区间（2，4），（4，5）加间隙锁，同时number=4的记录加记录锁，即next-key锁的锁定的范围为（2,4],(4,5]。

记录锁、间隙锁、临间锁的区别

update news  set number=0 where id>15

sql默认加的是next-key锁。根据上图，next-key锁的区间为（-∞,1],（1,5]，（5,9]，（9，11]，(11,+∞），上面id>15，实际上next-key锁是加在[11，+∞）这个范围内，而不是（15，+∞）这个范围内。注意：需要使用锁的字段必须加索引，因为锁是加在索引上的，没有索引则加的表锁。

相关免费学习推荐：mysql数据库(视频)

위 내용은 MySQL 잠금 메커니즘 재검토의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!