MySQL 학습은 InnoDB의 잠금 상황에 대해 이야기합니다.-MySQL 튜토리얼-php.cn

이 기사에서는 MySQL에 대해 설명하고 InnoDB의 잠금 상황을 소개합니다. 도움이 필요한 친구들이 모두 참고할 수 있기를 바랍니다.

MySQL 학습은 InnoDB의 잠금 상황에 대해 이야기합니다.

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.21    |
+-----------+
1 row in set (0.01 sec)

로그인 후 복사

먼저 잠금에 대한 기본 소개

다른 데이터베이스와 비교하여 MySQL의 잠금 메커니즘은 상대적으로 간단합니다. 가장 중요한 특징은 다양한 스토리지 엔진이 다양한 잠금 메커니즘을 지원한다는 것입니다. 예를 들어 MyISAM 및 MEMORY 스토리지 엔진은 테이블 수준 잠금을 사용합니다. InnoDB 스토리지 엔진은 행 수준 잠금(행 수준 잠금)과 테이블 수준 잠금을 모두 지원하지만 기본적으로 행 수준 잠금이 사용됩니다.

테이블 수준 잠금: 낮은 오버헤드, 빠른 잠금, 교착 상태 없음, 큰 잠금 세분성, 가장 높은 잠금 충돌 가능성 및 가장 낮은 동시성.

행 수준 잠금: 높은 오버헤드, 느린 잠금이 발생할 수 있습니다. 잠금 세분성은 가장 작고 잠금 충돌 가능성은 가장 낮으며 동시성은 가장 높습니다.

행 수준 잠금 유형:

레코드 잠금(레코드 잠금): 레코드 잠금 시(단일 레코드 잠금)

레코드 잠금은 InnoDB 저장소 테이블이 생성된 경우 인덱스 레코드만 잠급니다. 인덱스가 없으면 이 잠금은 아래와 같이 암시적 기본 키를 사용하여 잠급니다

MySQL 학습은 InnoDB의 잠금 상황에 대해 이야기합니다.

Gap Lock(gap lock): 레코드 자체를 제외하고 범위를 잠급니다(앞의 GAP만 잠급니다). data)

아래 그림에 표시된 잠금은 GAP 잠금으로, 간격(3, 8)인 인덱스 열 8번 앞의 공백에 다른 트랜잭션이 새 레코드를 삽입하는 것을 허용하지 않습니다. Gap Lock의 역할은 유령 레코드의 삽입을 방지하는 것뿐입니다

MySQL 학습은 InnoDB의 잠금 상황에 대해 이야기합니다.

Next-Key Lock: 레코드와 레코드 앞의 GAP을 동시에 잠그는 것, 즉 Next-Key 잠금 = 레코드 잠금 + 간격 잠금.

MySQL 학습은 InnoDB의 잠금 상황에 대해 이야기합니다.

두 번째, 잠금의 분류

공유 잠금 공유 잠금(S 잠금이라고 함, 행 잠금에 속함)

배타적 잠금(X 잠금이라고 함, 행 잠금에 속함)

의도 공유 잠금 공유 잠금(줄여서 IS 잠금, 테이블 잠금에 속함)

의도 배타적 잠금(줄여서 IX 잠금, 테이블 잠금에 속함)

AUTO-INC 잠금(테이블 잠금에 속함)

다음은 다음과 같습니다. 각각에 대한 자세한 소개 다양한 유형의 잠금에 대해 먼저 innodb 테이블을 구축합니다. SQL은 다음과 같습니다

create table tab_with_index(id int,name varchar(10)) engine=innodb;
alter table tab_with_index add index id(id);
insert into tab_with_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

로그인 후 복사

공유 잠금

공유 잠금은 여러 트랜잭션이 동일한 데이터에 대해 잠금을 공유할 수 있음을 의미하며 모두 가능합니다. 데이터베이스에 액세스할 수 있지만 읽을 수는 없습니다.

select * from tab_with_index 공유 모드의 잠금

select * from tab_with_index where id =1; 데이터를 쿼리할 수 있습니다.

update tab_with_index set name = 'aa' where id = 1 ;

참고: 여기의 수정 문은 차단되며 트랜잭션 A가 제출될 때까지 작업이 성공할 수 없습니다.

배타적 잠금

배타적 잠금은 다른 잠금과 공존할 수 없습니다. 트랜잭션이 데이터 행에 대한 독점 잠금을 획득하면 다른 트랜잭션은 해당 행에 대한 잠금을 획득할 수 없습니다. 현재 독점 잠금을 획득한 트랜잭션만 데이터를 수정할 수 있습니다. . (삭제, 업데이트, 생성은 기본적으로 배타적 잠금입니다)

트랜잭션 A:

select * from tab_with_index where id =1 for update

select * from tab_with_index where id =1; /결과를 얻을 수 있습니다

select * from tab_with_index where id =1 for update; // 차단됨

select * from tab_with_index where id = 1 lock for share mode

참고: 트랜잭션 B two 각 SQL 문은 차단됩니다. 즉, 트랜잭션 A가 제출될 때까지 작업은 공유 잠금이나 배타적 잠금을 얻을 수 없습니다.

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

의도 공유 잠금: 트랜잭션이 데이터 행에 공유 잠금을 추가할 준비를 하고 있음을 나타냅니다. 즉, 데이터 행은 추가하기 전에 먼저 테이블의 IS 잠금을 획득해야 함을 의미합니다. 공유 자물쇠.

의도적인 배타적 잠금: 트랜잭션이 데이터 행에 배타적 잠금을 추가할 준비를 하고 있음을 나타냅니다. 즉, 데이터 행에 배타적 잠금을 추가하기 전에 먼저 테이블의 IX 잠금을 획득해야 합니다.

IS 잠금 및 IX 잠금은 나중에 테이블 수준 S 잠금 및 X 잠금을 추가할 때 테이블의 레코드가 잠겨 있는지 확인하기 위해 순회를 사용하지 않도록 하기 위한 목적으로만 제안됩니다. 테이블에 잠긴 레코드가 있습니까? 즉, IS 잠금은 IX 잠금과 호환되고 IX 잠금은 IX 잠금과 호환됩니다. "MySQL 실행 방법"

자동 증가 잠금

자동 증가 열에 대한 특수 테이블 수준 잠금입니다.
show variables like 'innodb_autoinc_lock_mode'; 
-- 默认值1，代表连续，事务未提交则ID永久丢失
로그인 후 복사
三，InnoDB锁

1、事务及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有4属性，通常称为事务的ACID属性。

原子性（Actomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

2、并发事务带来的问题

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多用户的并发操作，但与此同时，会带来一下问题：

脏读：一个事务正在对一条记录做修改，在这个事务并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读”

不可重复读：一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了！这种现象叫做“不可重复读”。

幻读：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”

上述出现的问题都是数据库读一致性的问题，可以通过事务的隔离机制来进行保证。

数据库的事务隔离越严格，并发副作用就越小，但付出的代价也就越大，因为事务隔离本质上就是使事务在一定程度上串行化，需要根据具体的业务需求来决定使用哪种隔离级别

脏读不可重复读幻读

read uncommitted √ √ √

read committed
√ √

repeatable read

√

serializable

可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：
mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 18702 |
| Innodb_row_lock_time_avg      | 18702 |
| Innodb_row_lock_time_max      | 18702 |
| Innodb_row_lock_waits         | 1     |
+-------------------------------+-------+
--如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高
로그인 후 복사
3、InnoDB的行锁模式及加锁方法

共享锁（S） ：又称读锁(lock in share mode)。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。 排他锁（X） ：又称写锁(for update)。允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。

mysql InnoDB引擎默认的修改数据语句：update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务中是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

4、InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

1、在不通过索引条件查询的时候，innodb使用的是表锁而不是行锁
create table tab_no_index(id int,name varchar(10)) engine=innodb;
insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');
로그인 후 복사
session1 session2

set autocommit=0 select * from tab_no_index where id = 1; set autocommit=0 select * from tab_no_index where id =2

select * from tab_no_index where id = 1 for update

select * from tab_no_index where id = 2 for update;

session1只给一行加了排他锁，但是session2在请求其他行的排他锁的时候，会出现锁等待。原因是在没有索引的情况下，innodb只能使用表锁。

2、创建带索引的表进行条件查询，innodb使用的是行锁
create table tab_with_index(id int,name varchar(10)) engine=innodb;
alter table tab_with_index add index id(id);
insert into tab_with_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');
로그인 후 복사
로그인 후 복사
session1 session2

set autocommit=0 select * from tab_with_indexwhere id = 1; set autocommit=0 select * from tab_with_indexwhere id =2

select * from tab_with_indexwhere id = 1 for update

select * from tab_with_indexwhere id = 2 for update;

3、由于mysql的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是依然无法访问到具体的数据（这里是表锁）
alter table tab_with_index drop index id;
insert into tab_with_index  values(1,'4');
로그인 후 복사
session1 session2

set autocommit=0 set autocommit=0

select * from tab_with_index where id = 1 and name='1' for update

select * from tab_with_index where id = 1 and name='4' for update 虽然session2访问的是和session1不同的记录，但是锁的是具体表，所以需要等待锁

요약

이 기사에서는 InnoDB 테이블에 대해 주로 다음 내용을 논의합니다. (1) InnoDB의 행 잠금은 인덱스를 기반으로 합니다. 데이터가 인덱스를 통해 액세스되지 않으면 InnoDB는 테이블 잠금을 사용합니다. (2) 서로 다른 격리 수준에서는 InnoDB의 잠금 메커니즘과 일관된 읽기 전략이 다릅니다.

InnoDB의 잠금 특성을 이해한 후 사용자는 다음을 포함한 설계 및 SQL 조정을 통해 잠금 충돌 및 교착 상태를 줄일 수 있습니다.

인덱스를 신중하게 설계하고 인덱스를 사용하여 데이터에 액세스하십시오. 잠금이 더 정확하므로 잠금 충돌 가능성이 줄어듭니다.

합리적인 트랜잭션 크기를 선택하면 소규모 트랜잭션에 대한 잠금 충돌 가능성이 줄어듭니다.

레코드 세트를 명시적으로 잠글 때는 충분한 수준을 요청하는 것이 가장 좋습니다. 한 번에 잠금. 예를 들어, 데이터를 수정하려면 먼저 공유 잠금을 적용한 다음 수정 시 독점 잠금을 요청하는 대신 직접 독점 잠금을 적용하는 것이 가장 좋습니다. 이로 인해 다른 프로그램이 액세스할 때 쉽게 교착 상태가 발생할 수 있습니다. 테이블 그룹에서는 동일한 것에 동의하도록 노력해야 합니다. 각 테이블에 대해 순차적으로 액세스하십시오. 테이블의 경우 가능한 한 고정된 순서로 테이블의 행에 액세스하십시오. 이렇게 하면 교착 상태가 발생할 가능성이 크게 줄어들 수 있습니다.

동시 삽입에 대한 갭 잠금의 영향을 피하기 위해 동일한 조건을 사용하여 실제 요구 사항을 초과하는 잠금 수준을 적용하지 마십시오.

일부 특정 트랜잭션의 경우 테이블 잠금을 사용하여 처리 속도를 높이거나 교착 상태 가능성을 줄일 수 있습니다.

【관련 추천:
mysql 비디오 튜토리얼
】

	脏读	不可重复读	幻读
read uncommitted	√	√	√
read committed		√	√
repeatable read			√
serializable

session1	session2
set autocommit=0 select * from tab_no_index where id = 1;	set autocommit=0 select * from tab_no_index where id =2
select * from tab_no_index where id = 1 for update
	select * from tab_no_index where id = 2 for update;

session1	session2
set autocommit=0 select * from tab_with_indexwhere id = 1;	set autocommit=0 select * from tab_with_indexwhere id =2
select * from tab_with_indexwhere id = 1 for update
	select * from tab_with_indexwhere id = 2 for update;

session1	session2
set autocommit=0	set autocommit=0
select * from tab_with_index where id = 1 and name='1' for update
	select * from tab_with_index where id = 1 and name='4' for update 虽然session2访问的是和session1不同的记录，但是锁的是具体表，所以需要等待锁