mysql 잠금 메커니즘의 원리에 대한 자세한 설명(2)

Lock은 컴퓨터가 리소스에 동시에 액세스하기 위해 여러 프로세스나 스레드를 조정하는 메커니즘입니다. 데이터베이스에서는 컴퓨팅 리소스(예: CPU, RAM, I/O 등)에 대한 전통적인 경쟁 외에도 데이터도 많은 사용자가 공유하는 리소스입니다. 데이터에 대한 동시 접근의 일관성과 효율성을 어떻게 보장하는가는 모든 데이터베이스가 해결해야 할 문제이다. 잠금 충돌 역시 데이터베이스에 대한 동시 접근 성능에 영향을 미치는 중요한 요소이다. 이러한 관점에서 잠금은 데이터베이스에 특히 중요하고 복잡합니다. 이 장에서는 MySQL 잠금 메커니즘의 특성, 일반적인 잠금 문제, MySQL 잠금 문제를 해결하기 위한 몇 가지 방법이나 제안에 중점을 둡니다.
Mysql은 행 잠금, 테이블 잠금, 읽기 잠금, 쓰기 잠금 등과 같은 많은 잠금 메커니즘을 사용하며 모두 작업 전에 잠깁니다. 이러한 잠금을 집합적으로 비관적 잠금이라고 합니다.

InnoDB 잠금

InnoDB와 MyISAM의 가장 큰 차이점은 두 가지입니다.

첫째, 트랜잭션(TRANSACTION)을 지원합니다.

둘째, 행 수준 잠금을 사용합니다. 행 수준 잠금과 테이블 수준 잠금에는 많은 차이점이 있습니다. 또한 트랜잭션의 도입으로 인해 몇 가지 새로운 문제도 발생합니다.

1. 트랜잭션(Transaction) 및 해당 ACID 속성
트랜잭션은 일련의 SQL 문으로 구성된 논리적 처리 단위이며 일반적으로 트랜잭션의 ACID 속성이라고 합니다.

1. 원자성: 트랜잭션은 원자적 연산 단위이며, 데이터에 대한 모든 수정 사항은 실행되거나 전혀 실행되지 않습니다.

2. 일관성: 거래가 시작되고 완료될 때 데이터는 일관성을 유지해야 합니다. 이는 트랜잭션이 끝날 때 무결성을 유지하기 위해 모든 관련 데이터 규칙이 트랜잭션 수정에 적용되어야 하며 모든 내부 데이터 구조(예: B-트리 인덱스 또는 이중 연결 목록)도 정확해야 함을 의미합니다.

3. 격리: 데이터베이스 시스템은 외부 동시 작업의 영향을 받지 않는 "독립적인" 환경에서 트랜잭션이 실행되도록 특정 격리 메커니즘을 제공합니다. 이는 트랜잭션 중 중간 상태가 외부 세계에 표시되지 않으며 그 반대의 경우도 마찬가지임을 의미합니다.

4. 내구성: 트랜잭션이 완료된 후 데이터 수정은 영구적이며 시스템 오류가 발생하더라도 유지될 수 있습니다.

2. 동시 트랜잭션으로 인한 문제
직렬 처리에 비해 동시 트랜잭션 처리는 데이터베이스 리소스의 활용도를 크게 높이고 데이터베이스 시스템의 트랜잭션 처리량을 향상시켜 더 많은 사용자를 지원할 수 있습니다. 그러나 동시 트랜잭션 처리에는 주로 다음과 같은 상황을 포함하여 몇 가지 문제가 발생합니다.

1. 업데이트 손실: 두 개 이상의 트랜잭션이 동일한 행을 선택한 다음 원래 선택한 값을 기반으로 행을 업데이트하면 각 트랜잭션이 다른 트랜잭션의 존재를 인식하지 못하기 때문에 손실이 발생합니다. 업데이트 문제 - 마지막 업데이트. 다른 트랜잭션으로 이루어진 업데이트를 덮어씁니다. 예를 들어, 두 명의 편집자가 동일한 문서의 전자 사본을 만듭니다. 각 편집자는 독립적으로 사본을 변경한 후 변경된 사본을 저장하고 원본 문서를 덮어씁니다. 자신의 변경 사항 사본을 마지막으로 저장한 편집자가 다른 편집자의 변경 사항을 덮어씁니다. 다른 편집자가 트랜잭션을 완료하고 커밋할 때까지 한 편집자가 동일한 파일에 액세스할 수 없는 경우 이 문제를 피할 수 있습니다.

2. 더티 읽기(Dirty Reads): 트랜잭션이 제출되기 전에 이 레코드의 데이터가 일치하지 않는 상태입니다. 다른 트랜잭션도 동일한 레코드를 읽습니다. 트랜잭션은 이러한 "더러운" 데이터를 읽고 이에 따라 추가 처리를 수행하며, 이로 인해 커밋되지 않은 데이터 종속성이 생성됩니다. 이런 현상을 '더티 리딩(dirty reading)'이라고 부르기도 합니다.

3. 반복 불가능한 읽기: 일부 데이터를 읽을 때 트랜잭션이 변경되었거나 일부 기록이 삭제되었습니다! 이러한 현상을 "반복 불가능한 읽기"라고 합니다.

4. 팬텀 읽기(Phantom Reads): 한 트랜잭션이 이전에 검색한 데이터를 동일한 쿼리 조건에 따라 다시 읽지만, 다른 트랜잭션이 쿼리 조건을 만족하는 새로운 데이터를 삽입했음을 발견하는 현상을 "팬텀 읽기"라고 합니다.

3. 트랜잭션 격리 수준
동시 트랜잭션 처리로 인해 발생하는 문제 중 "업데이트 손실"은 일반적으로 완전히 피해야 합니다. 그러나 업데이트 손실 방지는 데이터베이스 트랜잭션 컨트롤러만으로는 해결할 수 없습니다. 따라서 업데이트할 데이터에 필요한 잠금을 애플리케이션에서 추가해야 합니다. 따라서 업데이트 손실 방지는 애플리케이션의 책임입니다.

"더티 읽기", "반복 불가능 읽기" 및 "팬텀 읽기"는 실제로 데이터베이스 읽기 일관성 문제이며, 이는 특정 트랜잭션 격리 메커니즘을 제공하는 데이터베이스에 의해 해결되어야 합니다. 데이터베이스가 트랜잭션 격리를 구현하는 방식은 기본적으로 다음 두 가지 유형으로 나눌 수 있습니다.

1. 하나는 다른 트랜잭션이 데이터를 수정하지 못하도록 데이터를 읽기 전에 잠그는 것입니다.

2 다른 하나는 잠금을 추가하지 않고 특정 메커니즘을 통해 데이터 요청 시점의 일관된 데이터 스냅샷(Snapshot)을 생성하고 이 스냅샷을 사용하여 일정 수준의 일관성(문 수준 또는 트랜잭션 수준)을 제공하는 것입니다. ) 읽다. 사용자의 관점에서 볼 때, 데이터베이스는 동일한 데이터의 여러 버전을 제공할 수 있는 것처럼 보입니다. 따라서 이 기술을 데이터 다중 버전 동시성 제어(MVCC 또는 줄여서 MCC)라고 하며, 흔히 다중 버전 데이터베이스라고도 합니다.

MVCC 동시성 제어에서 읽기 작업은 스냅샷 읽기와 현재 읽기의 두 가지 범주로 나눌 수 있습니다. 스냅샷 읽기는 잠금 없이 기록의 표시 버전(기록 버전일 수 있음)을 읽습니다. 현재 읽기는 최신 버전의 레코드를 읽고, 현재 읽기에서 반환된 레코드는 다른 트랜잭션이 이 레코드를 동시에 수정하지 못하도록 잠깁니다.
MVCC 동시성 제어를 지원하는 시스템에서 어떤 읽기 작업이 스냅샷 읽기인가요? 현재 어떤 작업을 읽고 있나요? MySQL InnoDB를 예로 들어 보겠습니다.

Snapshot 읽기: 간단한 선택 작업으로, 스냅샷 읽기이며 잠금이 필요하지 않습니다. (물론 예외도 있습니다)

select * from table where ?;

현재 읽기: 특수 읽기 작업, 삽입/업데이트/삭제 작업은 현재 읽기에 속하며 잠금이 필요합니다.
다음 진술은 모두 현재 읽기, 최신 버전의 기록을 읽는 것입니다. 또한, 읽은 후에는 다른 동시 트랜잭션이 현재 레코드를 수정하고 읽은 레코드를 잠그지 못하도록 하는 것도 필요합니다. 이 중 읽기 레코드에 S 잠금(공유 잠금)을 추가하는 첫 번째 문을 제외하고 다른 모든 작업에는 X 잠금(배타적 잠금)이 추가됩니다.

데이터베이스의 트랜잭션 격리가 엄격할수록 동시성 부작용은 작아지지만 지불하는 비용은 더 커집니다. 트랜잭션 격리는 본질적으로 트랜잭션을 어느 정도 "직렬화"하기 때문입니다. "동시성"과 모순됩니다. 동시에, 애플리케이션마다 읽기 일관성 및 트랜잭션 격리에 대한 요구 사항이 다릅니다. 예를 들어, 많은 애플리케이션은 "반복 불가능한 읽기" 및 "팬텀 읽기"에 민감하지 않으며 동시에 데이터에 액세스하는 기능에 더 관심을 가질 수 있습니다.

"격리"와 "동시성" 사이의 모순을 해결하기 위해 ISO/ANSI SQL92는 4가지 트랜잭션 격리 수준을 정의합니다. 각 수준은 서로 다른 격리 수준을 가지며 서로 다른 부작용을 허용합니다. 자신의 비즈니스 논리 요구 사항에 따라 다양한 격리 수준을 선택하여 "격리"와 "동시성" 간의 모순을 균형있게 조정하세요. 다음 표는 이러한 4가지 격리 수준의 특성을 잘 요약한 것입니다.

InonoD 행 잠금 경합 가져오기

#🎜🎜 #당신 InnoDB_row_lock 상태 변수를 확인하여 시스템의 행 잠금 경합을 분석할 수 있습니다.

mysql> show status like 'innodb_row_lock%';

잠금 경합이 InnoDB_row_lock_waits와 같이 심각한 것으로 확인된 경우 InnoDB_row_lock_time_avg의 값은 상대적으로 높습니다. InnoDB 모니터를 설정하여 잠금 충돌이 발생하는 테이블과 데이터 행을 추가로 관찰하고 잠금 경합의 원인을 분석할 수도 있습니다.

InnoDB의 행 잠금 모드 및 잠금 방법

InnoDB는 다음 두 가지 유형의 행 잠금을 구현합니다.

공유 잠금: 읽기 잠금이라고도 합니다. 하나의 트랜잭션이 행을 읽을 수 있도록 허용하여 다른 트랜잭션이 동일한 데이터 세트에 대해 배타적 잠금을 획득하는 것을 방지합니다. 트랜잭션 T가 데이터 개체 A에 S 잠금을 추가하는 경우 트랜잭션 T는 A를 읽을 수 있지만 A를 수정할 수는 없습니다. 다른 트랜잭션은 A에 S 잠금만 추가할 수 있지만 T가 A에 대한 S 잠금을 해제할 때까지 X 잠금을 추가할 수는 없습니다. 이는 다른 트랜잭션이 A를 읽을 수 있지만 T가 A에 대한 S 잠금을 해제할 때까지 A를 수정할 수 없음을 보장합니다.

독점 잠금(Ｘ): 쓰기 잠금이라고도 합니다. 배타적 잠금을 획득하는 트랜잭션을 허용하여 데이터를 업데이트하고 다른 트랜잭션이 동일한 데이터 세트에 대해 공유 읽기 잠금 및 배타적 쓰기 잠금을 획득하지 못하도록 합니다. 트랜잭션 T가 데이터 개체 A에 X 잠금을 추가하면 트랜잭션 T는 A를 읽거나 수정할 수 있습니다. T가 A에 대한 잠금을 해제할 때까지 다른 트랜잭션은 A에 더 이상 잠금을 추가할 수 없습니다.

공유 잠금에 대해 잘 이해하고 계실 것입니다. 즉, 여러 트랜잭션은 데이터를 읽을 수만 있고 데이터를 변경할 수는 없습니다.

对于排他锁大家的理解可能就有些差别，我当初就犯了一个错误，以为排他锁锁住一行数据后，其他事务就不能读取和修改该行数据，其实不是这样的。排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁。mysql InnoDB引擎默认的修改数据语句：update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。

意向共享锁（IS）：事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。意向排他锁（IX）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

InnoDB行锁模式兼容性列表: 

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就请求的锁授予该事务；反之，如果两者两者不兼容，该事务就要等待锁释放。 
意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁。 
事务可以通过以下语句显式给记录集加共享锁或排他锁：

1、共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。

2、排他锁（X）：SELECT * FROM table_name WHERE ... FOR UPDATE。

用SELECT ... IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT… FOR UPDATE方式获得排他锁。

InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！ 
在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。下面通过一些实际例子来加以说明。

（1）在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。

mysql> create table tab_no_index(id int,name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.15 sec)
mysql> insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

在上面的例子中，看起来session_1只给一行加了排他锁，但session_2在请求其他行的排他锁时，却出现了锁等待！原因就是在没有索引的情况下，InnoDB只能使用表锁。当我们给其增加一个索引后，InnoDB就只锁定了符合条件的行，如下例所示： 
创建tab_with_index表，id字段有普通索引：

mysql> create table tab_with_index(id int,name varchar(10)) engine=innodb;
mysql> alter table tab_with_index add index id(id);

（2）由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。 
在下面的例子中，表tab_with_index的id字段有索引，name字段没有索引：

mysql> alter table tab_with_index drop index name;
1
Query OK, 4 rows affected (0.22 sec) Records: 4 Duplicates: 0 
Warnings: 0
mysql> insert into tab_with_index  values(1,'4');
1
Query OK, 1 row affected (0.00 sec)
mysql> select * from tab_with_index where id = 1;

InnoDB存储引擎使用相同索引键的阻塞例子 ：

（3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。 
在下面的例子中，表tab_with_index的id字段有主键索引，name字段有普通索引：

mysql> alter table tab_with_index add index name(name);
1Query OK, 5 rows affected (0.23 sec) Records: 5 Duplicates: 0 
Warnings: 0

InnoDB存储引擎的表使用不同索引的阻塞例子 ：

（4）即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决 定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突 时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。 
比如，在tab_with_index表里的name字段有索引，但是name字段是varchar类型的，检索值的数据类型与索引字段不同，虽然MySQL能够进行数据类型转换，但却不会使用索引，从而导致InnoDB使用表锁。通过用explain检查两条SQL的执行计划，我们可以清楚地看到了这一点。

mysql> explain select * from tab_with_index where name = 1 \G
mysql> explain select * from tab_with_index where name = '1' \G

间隙锁（Next-Key锁）

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的 索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁 （Next-Key锁）。 
举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：

Select * from  emp where empid > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使 用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需 要。有关其恢复和复制对锁机制的影响，以及不同隔离级别下InnoDB使用间隙锁的情况，在后续的章节中会做进一步介绍。

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！下面这个例子假设emp表中只有101条记录，其empid的值分别是1,2,……,100,101。 
InnoDB存储引擎的间隙锁阻塞例子 

小结

本文重点介绍了MySQL中MyISAM表级锁和InnoDB行级锁的实现特点，并讨论了两种存储引擎经常遇到的锁问题和解决办法。

MyISAM 테이블 잠금의 경우 다음 사항이 주로 논의됩니다.
(1) 공유 읽기 잠금(S)은 호환되지만 공유 읽기 잠금(S)과 배타적 쓰기 잠금(X)은 상호 배타적입니다. 읽기와 쓰기가 연속적으로 수행됨을 의미하는 배타적 쓰기 잠금(X)도 있습니다.
(2) 특정 조건에서 MyISAM은 쿼리와 삽입이 동시에 실행되도록 허용합니다. 이를 사용하여 애플리케이션의 동일한 테이블에 대한 쿼리 및 삽입에 대한 잠금 경합 문제를 해결할 수 있습니다.
(3) MyISAM의 기본 잠금 스케줄링 메커니즘은 쓰기 우선순위입니다. 이는 모든 애플리케이션에 반드시 적합한 것은 아닙니다. 사용자는 LOW_PRIORITY_UPDATES 매개변수를 설정하거나 INSERT, UPDATE에서 LOW_PRIORITY 옵션을 지정하여 읽기-쓰기 잠금을 조정할 수 있습니다. 및 DELETE 문.
(4) 테이블 잠금의 잠금 세분성과 읽기 및 쓰기 간의 직렬화로 인해 업데이트 작업이 많은 경우 MyISAM 테이블에 심각한 잠금 대기가 발생할 수 있습니다. 잠금 충돌을 줄이기 위해 InnoDB 테이블을 사용하는 것을 고려할 수 있습니다. .

이 기사에서는 InnoDB 테이블에 대해 주로 다음 내용을 논의합니다.
(1) InnoDB의 행 잠금은 인덱스를 통해 데이터에 액세스하지 않는 경우 테이블 잠금을 사용합니다.
(2) InnoDB 갭 잠금(Next-key) 메커니즘과 InnoDB가 갭 잠금을 사용하는 이유를 소개합니다.
다양한 격리 수준에서는 InnoDB의 잠금 메커니즘과 일관된 읽기 전략이 다릅니다.

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

더 낮은 격리 수준을 사용해 보세요. 인덱스를 신중하게 설계하고 인덱스를 사용하여 데이터에 액세스하여 잠금을 더욱 정확하게 하십시오. 그러면 잠금 충돌 가능성이 줄어들고 합리적인 트랜잭션 크기를 선택하고 명시적으로 잠금을 설정할 때 소규모 트랜잭션에 대한 잠금 충돌 가능성이 줄어듭니다. 한 번에 충분한 수준의 잠금을 요청하는 것이 가장 좋습니다. 예를 들어, 데이터를 수정하려면 먼저 공유 잠금을 적용한 다음 수정 시 배타적 잠금을 요청하는 대신 직접 배타적 잠금을 적용하는 것이 가장 좋습니다. 이는 서로 다른 프로그램이 그룹에 액세스할 때 교착 상태가 발생하기 쉽습니다. 테이블의 경우 각 테이블은 가능한 한 고정된 순서로 테이블의 행에 액세스하는 데 동의해야 합니다. 이는 교착 상태의 가능성을 크게 줄일 수 있습니다. 동시 삽입에 대한 갭 잠금의 영향을 피하기 위해 동일한 조건을 사용하십시오. 필요한 경우가 아니면 쿼리할 때 잠금을 표시하지 마십시오. ; 일부 특정 트랜잭션의 경우 테이블 잠금을 사용하여 처리 속도를 높이거나 교착 상태 가능성을 줄일 수 있습니다.

관련 콘텐츠를 더 알고 싶다면 PHP 중국어 웹사이트를 방문하세요: mysql 비디오 튜토리얼

위 내용은 mysql 잠금 메커니즘의 원리에 대한 자세한 설명(2)의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!