MySQL 잠금 및 트랜잭션 격리 수준(소개)

오늘날의 인터넷에서는 대규모 멀티 플레이어 앱을 개발할 때 데이터베이스 없이는 불가능해야 합니다. 모든 사람이 높은 동시성으로 읽고 쓸 수 있도록 보장하는 방법은 항상 어려운 아키텍처 문제였습니다. 우선 높은 동시성을 제거하고 일관된 읽기 및 쓰기를 보장하기 위해 가장 일반적으로 사용되는 방법은 트랜잭션이며 구현의 핵심입니다. 트랜잭션은 잠금 메커니즘입니다.

오늘은 InnoDB 스토리지 엔진이 일관된 읽기 및 쓰기를 충족하기 위해 높은 동시성에서 잠금 메커니즘을 구현하는 방법의 원리와 구현을 소개하겠습니다.

Locks

데이터베이스의 잠금 메커니즘은 파일 시스템과 구별되는 핵심 기능입니다. 공유 리소스에 대한 동시 액세스를 관리하는 데 사용됩니다. InnoDB는 운영 데이터 테이블, LRU 페이지 목록, 버퍼 풀의 데이터 행 등 여러 위치에서 잠금 메커니즘을 사용합니다. 일관성과 무결성을 보장하려면 잠금 메커니즘이 필요합니다.

다른 데이터베이스의 경우 잠금 메커니즘의 설계와 구현이 완전히 다릅니다.

　●　MyISAM 엔진: 테이블 잠금 설계, 동시 읽기는 문제가 없지만 동시 쓰기 성능은 나쁩니다.

　● Microsoft SQL Server: 낙관적 동시성은 행 수준 잠금을 지원합니다. 행 잠금 수가 임계값을 초과하면 테이블 잠금으로 업그레이드됩니다.

　● InnoDB 엔진: 행 잠금을 지원하고 일관된 비잠금 읽기를 제공합니다. 행 잠금에는 추가 오버헤드가 없으며 성능 저하도 없습니다.

　●Oracle: InnoDB 엔진과 매우 유사합니다.

두 가지 잠금 유형: 잠금 및 래치

데이터베이스의 잠금과 래치는 모두 잠금이라고 할 수 있지만 큰 차이가 있습니다.

Latch는 일반적으로 래치라고 하며 중요한 리소스를 작동하는 동시 스레드의 정확성을 보장하는 데 사용됩니다. 개체는 잠금 시간이 매우 짧아야 교착 상태가 감지되지 않습니다. InnoDB 엔진에서는 mutex(뮤텍스)와 rwlock(읽기-쓰기 잠금)으로 구분됩니다.

Lock은 테이블, 페이지, 행과 같은 데이터베이스의 개체를 잠그는 데 사용됩니다. 대상은 커밋/롤백 후 해제되며 교착 상태가 감지됩니다. 행 잠금, 테이블 잠금, 의도 잠금으로 구분됩니다.

아래의 자물쇠는 자물쇠형 자물쇠를 말합니다.

네 가지 잠금 유형

InnoDB는 네 가지 잠금을 지원합니다.

　● 공유 잠금(S Lock): 트랜잭션이 데이터 행을 읽을 수 있도록 허용

● 배타적 잠금(X Lock): 트랜잭션이 삭제되도록 허용 또는 데이터 행 업데이트

　● 의도 공유 잠금(Intention S Lock): 트랜잭션이 테이블의 특정 행에 대한 공유 잠금을 얻으려고 합니다.

● 의도 배타적 잠금(Intention X Lock): 트랜잭션이 테이블의 특정 행에 대한 공유 잠금을 얻으려고 합니다. 테이블의 특정 행에 대한 배타적 잠금

트랜잭션 T1이 행 r의 공유 잠금을 획득하면 행 데이터가 읽기로 변경되지 않으므로 트랜잭션 T2도 행 r의 공유 잠금을 직접 획득할 수 있습니다. 이를 잠금 호환이라고 합니다. .

트랜잭션 T3이 데이터 수정을 위해 행 r의 배타적 잠금을 얻으려면 T1/T2가 행 공유 잠금을 해제할 때까지 기다려야 합니다. 이를 잠금 비호환성이라고 합니다.

S 잠금과 X 잠금은 모두 행 잠금이고, IS 잠금과 IX 잠금은 의도 잠금이며 테이블 잠금에 속합니다. 의도 잠금은 트랜잭션 내의 다음 행에 대해 요청되는 잠금 유형, 즉 테이블 잠금의 더 미세한 세부 수준에서 잠금을 표시하도록 설계되었습니다. InnoDB는 테이블 잠금을 지원하므로 의도 잠금은 전체 테이블 스캔을 제외한 모든 요청을 차단하지 않습니다.

잠금 호환성:

호환되지 않음IX 호환 가능호환 가능호환되지 않음호환되지 않음S호환호환되지 않음호환됨호환되지 않음X호환되지 않음호환되지 않음호환되지 않음호환되지 않음 InnoDB1.0부터 INNODB_TRX(트랜잭션 테이블), INNODB_LOCKS(잠금 테이블), INNODB_LOCK_WAITS(잠금 대기 테이블)가 INFORMATION_SCHEMA 아키텍처에 추가되어 현재 트랜잭션을 실시간으로 모니터링하고 분석할 수 있습니다. 가능 테이블 문제가 있습니다.

	IS	IX	S
		트랜잭션 및 잠금 정보를 저장하는 세 개의 테이블		show Engine innodb status 명령을 통해 transaction 섹션에서 현재 잠금 요청 정보를 볼 수 있습니다.

세 테이블의 정의는 다음과 같습니다.

INNODB_TRX

show engine innodb status命令在事务部分查看当前锁请求的信息。

从InnoDB1.0开始，在INFORMATION_SCHEMA架构下添加了INNODB_TRX（transaction事务表）、INNODB_LOCKS（锁表）、INNODB_LOCK_WAITS（锁等待表），通过这三张表，可以让我们实时监控当前事务并分析可能存在的表问题。

三个表的定义分别为：

InnoDB 스토리지 엔진 내의 고유 트랜잭션 ID현재 트랜잭션의 상태트랜잭션의 시작 시간대기 중인 트랜잭션의 Lock IDC, 상태가 LOCK WAIT가 아닌 경우 NULL시작 대기 중인 트랜잭션 시간 트랜잭션에 의해 수정되고 잠긴 행 수를 반영하는 트랜잭션 가중치입니다. 롤백이 필요한 경우 롤백 값이 가장 작은 트랜잭션을 선택합니다MySQL 스레드 ID, 프로세스 목록 결과 표시트랜잭션에 의해 실행되는 SQL 문

INNODB_TRXtrx_id

trx_state

trx_started

trx_requested_lock_id

trx_wait_started

trx_weight

trx_mysql_thread_id

trx_query

INNODB_LOCKSINNODB_LOCKS
lock_id	锁ID
lock_trx_id	事务ID
lock_mode	锁的模式
lock_type	锁的类型，表锁或行锁
lock_table	要加锁的表
lock_index	锁住的索引
lock_space	锁对象的space id
lock_page	事务锁定页的数量，表锁时为NULL
lock_rec	事务锁定行的数量，表锁时为NULL
lock_data	事务锁定记录的主键值，表锁时为NULL

INNODB_LOCK_WAITS
requesting_trx_id	申请锁资源的事务ID
requesting_lock_id	申请的锁的ID
blocking_trx_id	阻塞的事务ID
blocking_lock_id	阻塞的锁的ID

通过INNODB_TRX我们可以看到所有的事务，以及事务是否被阻塞，阻塞的锁ID是什么。
之后，通过INNODB_LOCKS查看所有的锁信息。
之后，通过INNODB_LOCK_WAITS可以查看到锁的等待信息以及阻塞关系。

通过这三种表能够较为清晰的查看事务和锁的情况，也可以联合查询，在下面的一些场景下我们会来展示这三个表的内容。

隔离级别

首先我们来说下数据库的四种事务隔离级别：

　●　READ UNCOMMITTED（0）: 浏览访问级别，存在脏读、不可重复读、幻读

　●　READ COMMITTED（1）: 游标稳定级别，存在不可重复度、幻读

　●　REPEATABLE READ（2）: 存在幻读

　●　SERIALIZABLE（3）: 隔离级别，保证事务安全，但完全串行，性能低

这四种事务隔离级别是指定的SQL标准，InnoDB默认的隔离级别是REAPEATABLE READ，但与其他数据库不同的时，它同时使用了Next-Key-Lock锁的算法，能够避免幻读的产生，因此能够完全满足事务的隔离性要求，即达到SERIALIZABLE隔离级别。

隔离级别越低，事务请求的锁越少或持锁时间越短，因此大部分数据库的默认隔离级别为READ COMMITED。但是有相关的分析也指出，隔离级别的性能开销几乎一样，因此用户无须通过调整隔离级别来提高性能。

查看和修改事务隔离级别的命令:

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set (0.00 sec)

mysql> set session transaction isolation level SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)

示例中修改了本次会话的事务隔离级别，如果需要修改全局参数，可以替换session为global。如果想要永久修改，需要修改配置文件：

[mysqld]
transaction-isolation = READ-COMMITED

在SERIALIZABLE的事务隔离级别，InnoDB会对每个SELECT语句后自动加上LOCK IN SHARE MODE，来对读操作加上一个共享锁，因此不再支持一致性的非锁定读。

由于InnoDB在REPEATABLE READ隔离级别就可以达到SERIALIZABLE，因此一般不用使用最高隔离级别。

一致性非锁定读和多版本并发控制

一致性非锁定读（consistent nonlocking read）是指InnoDB通过行多版本控制（Multi Version Concurrency Control, MVCC）的方法来读取当前执行时间数据库中行的数据。

即如果读取的行正在执行变更操作，这时读取不会等待行锁的释放，而是会读取行的一个快照数据。快照是指该行的一个历史数据，通过undo操作来完成。这种方式极大提高了数据库的并发性，这也是InnoDB的默认设置。

快照是当前行的一个历史版本，但可能存在多个版本，行数据存在多个快照数据，这种技术成为行多版本技术，由此带来的并发控制，称为多版本并发控制（MVCC）。InnoDB在READ COMMITED 和 REPEATABLE READ隔离级别时，会使用非锁定的一致性读，但是在这两种隔离级别使用的快找数据定义却不同：

　●　READ COMMITED: 总是读取最新一份快照

　●　REPEATABLE READ: 总是读取事务开始时的行数据版本

我们执行一个示例：

lock_idlock IDlock_trx_id거래 IDlock_modelock_type잠금 유형, 테이블 잠금 또는 행 잠금#🎜 🎜 #잠길 테이블lock_index잠금 개체의 공간 IDlock_rec테이블이 잠길 때 트랜잭션 잠긴 행 수 NULL트랜잭션 잠금 기록의 기본 키 값, NULL자원 잠금 신청 거래 ID잠금 신청 ID#🎜🎜 ##🎜 🎜#blocking_lock_id차단 잠금 ID# 🎜🎜#이후 INNODB_LOCKS를 통해 모든 잠금 정보를 확인하세요. 일관적인 비잠금 읽기

一致性非锁定读
		# 🎜🎜#LOCK MODE
		lock_table
	잠긴 인덱스# 🎜🎜## 🎜🎜#	lock_space
	lock_page #🎜 🎜#트랜잭션 잠긴 페이지 수, 테이블이 잠긴 경우 NULL
		lock_data
# ㅋㅋㅋ #🎜 🎜#		requesting_lock_id
blocking_trx_id	차단 거래 ID
	INNODB_TRX를 통해 모든 거래, 해당 거래의 차단 여부, 차단된 잠금 ID가 무엇인지 확인할 수 있습니다.	이후 INNODB_LOCK_WAITS를 통해 Lock의 대기 정보 및 차단 관계를 확인할 수 있습니다. 이 세 테이블을 통해 트랜잭션 및 잠금 상태를 보다 명확하게 볼 수 있으며, 다음 시나리오에서는 이 세 테이블의 내용을 보여줍니다. 격리 수준 우선 4가지 트랜잭션에 대해 이야기해 보겠습니다. 데이터베이스 격리 수준: ●READ UNCOMMITTED(0): 탐색 액세스 수준, 더티 읽기, 반복 불가능 읽기, 팬텀 읽기 존재 ●READ COMMITTED(1): 커서 안정성 수준에는 반복 불가능, 가상 읽기가 있습니다. ● REPEATABLE READ (2): 가상 읽기가 있습니다. ● SERIALIZABLE (3): 격리 수준, 트랜잭션 보안을 보장하지만 완전 직렬 및 저성능 이 네 가지 트랜잭션 격리 수준은 SQL 표준에 의해 지정되지만 InnoDB의 기본 격리 수준은 REAPEATABLE READ이지만 다른 데이터베이스와 달리 Next-Key-Lock 잠금 알고리즘도 사용합니다. 팬텀 읽기 발생을 방지하므로 트랜잭션의 격리 요구 사항을 완전히 충족할 수 있습니다. 즉, SERIALIZABLE 격리 수준에 도달합니다. 격리 수준이 낮을수록 트랜잭션에서 요청하는 잠금 수가 적거나 잠금 유지 시간이 짧아지므로 대부분의 데이터베이스의 기본 격리 수준은 READ COMMITED입니다. 그러나 관련 분석에서는 격리 수준의 성능 오버헤드가 거의 동일하므로 사용자가 성능 향상을 위해 격리 수준을 조정할 필요가 없다는 점도 지적합니다. 트랜잭션 격리 수준을 보고 수정하는 명령: #在事务开始前我们可以分别调整为0、1、2三种隔离级别，来查看不同的输出 mysql> set session transaction isolation level READ UNCOMMITTED; Query OK, 0 rows affected (0.00 sec) mysql> select @@tx_isolation; +------------------+ | @@tx_isolation   | +------------------+ | READ-UNCOMMITTED | +------------------+ 1 row in set (0.00 sec) # A会话：T1事务 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from z where a = 3; +---+------+ | a | b    | +---+------+ | 3 |    1 | +---+------+ 1 row in set (0.00 sec) # B会话：T2事务 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update z set b=2 where a=3; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0 # A会话：T1事务，如果此时隔离级别是READ-UNCOMMITTED，因为此刻事务2可能会回滚，所以出现了脏读 mysql> select * from z where a=3; +---+------+ | a | b    | +---+------+ | 3 |    2 | +---+------+ 1 row in set (0.00 sec) # A会话：T1事务，如果此时隔离级别是大于READ-UNCOMMITTED的更高级别 mysql> select * from z where a=3; +---+------+ | a | b    | +---+------+ | 3 |    1 | +---+------+ 1 row in set (0.00 sec) # B会话：T2事务 mysql> commit; Query OK, 0 rows affected (0.00 sec) # A会话：T1事务，如果此时隔离级别是READ-COMMITTED，因为数据和事务开始时读取的出现了不一致，因此称为不可重复读，能够读到其他事务的结果，违反了事务的隔离性 mysql> select * from z where a=3; +---+------+ | a | b    | +---+------+ | 3 |    2 | +---+------+ 1 row in set (0.00 sec) # A会话：T1事务，如果此时隔离级别是大于READ-COMMITTED的更高级别 mysql> select * from z where a=3; +---+------+ | a | b    | +---+------+ | 3 |    1 | +---+------+ 1 row in set (0.00 sec) # A会话：T1事务 mysql> commit; Query OK, 0 rows affected (0.00 sec) 예제에서 이 세션의 트랜잭션 격리 수준을 수정해야 하는 경우 전역 매개변수를 수정할 수 있습니다. 세션을 전역으로 바꾸십시오. 영구적인 변경을 원할 경우 구성 파일을 수정해야 합니다: rrreee SERIALIZABLE의 트랜잭션 격리 수준에서 InnoDB는 각 SELECT 문 다음에 자동으로 LOCK IN SHARE MODE를 추가하여 공유를 추가합니다. 읽기 작업이 잠금이므로 일관된 비잠금 읽기가 더 이상 지원되지 않습니다. InnoDB는 REPEATABLE READ 격리 수준에서 SERIALIZABLE에 도달할 수 있으므로 일반적으로 가장 높은 격리 수준을 사용할 필요는 없습니다. 일관적인 비잠금 읽기 및 다중 버전 동시성 제어 일관성 일관된 비잠금 읽기란 InnoDB가 MVCC(Row Multi-Version Concurrency Control) 방식을 통해 현재 실행 시점에 데이터베이스의 행 데이터를 읽는 것을 의미한다. 즉, 읽기 행이 변경 작업을 진행 중인 경우 읽기는 행 잠금이 해제될 때까지 기다리지 않고 행의 스냅샷 데이터를 읽습니다. 스냅샷은 실행 취소 작업을 통해 완료된 행의 기록 데이터를 나타냅니다. 이 방법은 InnoDB의 기본 설정이기도 한 데이터베이스의 동시성을 크게 향상시킵니다. 스냅샷은 현재 행의 기록 버전이지만 여러 버전이 있을 수 있습니다. 행 데이터의 스냅샷이 여러 개 있을 수 있습니다. 이 기술은 행 다중 버전 기술이 되며 결과적인 동시성 제어를 다중 버전이라고 합니다. 동시성 제어(MVCC). InnoDB는 READ COMMITED 및 REPEATABLE READ 격리 수준에서 잠금 없는 일관된 읽기를 사용하지만 이 두 격리 수준에서 사용되는 빠른 데이터 정의는 다릅니다. ● READ COMMITED: 항상 읽기 최신 스냅샷 가져오기 p> ● REPEATABLE READ: 항상 트랜잭션 시작 시 행 데이터 버전을 읽습니다. 예를 수행합니다: #🎜🎜 #

#🎜🎜#time#🎜🎜##🎜🎜# 세션 A#🎜🎜## 🎜🎜# 세션 B#🎜🎜##🎜🎜##🎜🎜##🎜🎜#1#🎜🎜##🎜🎜#BEGIN#🎜🎜##🎜🎜##🎜🎜## 🎜 🎜## 🎜🎜# #🎜🎜##🎜🎜#2#🎜🎜##🎜🎜#select * from z 여기서 a = 3;#🎜🎜##🎜🎜##🎜🎜##🎜🎜##🎜 🎜##🎜🎜# #🎜🎜#3#🎜🎜##🎜🎜##🎜🎜##🎜🎜##🎜🎜#BEGIN#🎜🎜##🎜🎜##🎜🎜##🎜🎜#4# 🎜🎜##🎜 🎜 ##🎜🎜##🎜🎜##🎜🎜#update z set b=2 여기서 a=3;#🎜🎜##🎜🎜##🎜🎜##🎜🎜#5#🎜🎜 ##🎜🎜#select * z에서 ##🎜🎜##🎜🎜##🎜🎜#COMMIT;#🎜🎜##🎜🎜##🎜🎜##🎜🎜#7#🎜🎜##🎜🎜#select * z에서 a = 3;# 🎜🎜##🎜🎜##🎜🎜##🎜🎜##🎜🎜##🎜🎜##🎜🎜#8#🎜🎜##🎜🎜#COMMIT;#🎜🎜# #🎜🎜##🎜 🎜# #🎜🎜##🎜🎜##🎜🎜##🎜🎜#

在这个例子中我们可以清晰的看到0、1、2三种隔离级别的区别：

#在事务开始前我们可以分别调整为0、1、2三种隔离级别，来查看不同的输出
mysql> set session transaction isolation level READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

# A会话：T1事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from z where a = 3;
+---+------+
| a | b    |
+---+------+
| 3 |    1 |
+---+------+
1 row in set (0.00 sec)

# B会话：T2事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update z set b=2 where a=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# A会话：T1事务，如果此时隔离级别是READ-UNCOMMITTED，因为此刻事务2可能会回滚，所以出现了脏读
mysql> select * from z where a=3;
+---+------+
| a | b    |
+---+------+
| 3 |    2 |
+---+------+
1 row in set (0.00 sec)

# A会话：T1事务，如果此时隔离级别是大于READ-UNCOMMITTED的更高级别
mysql> select * from z where a=3;
+---+------+
| a | b    |
+---+------+
| 3 |    1 |
+---+------+
1 row in set (0.00 sec)

# B会话：T2事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

# A会话：T1事务，如果此时隔离级别是READ-COMMITTED，因为数据和事务开始时读取的出现了不一致，因此称为不可重复读，能够读到其他事务的结果，违反了事务的隔离性
mysql> select * from z where a=3;
+---+------+
| a | b    |
+---+------+
| 3 |    2 |
+---+------+
1 row in set (0.00 sec)

# A会话：T1事务，如果此时隔离级别是大于READ-COMMITTED的更高级别
mysql> select * from z where a=3;
+---+------+
| a | b    |
+---+------+
| 3 |    1 |
+---+------+
1 row in set (0.00 sec)

# A会话：T1事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

一致性锁定读和SERIALIZABLE隔离

在默认的REPEATABLE READ隔离级别时，InnoDB使用的是一致性非锁定读。但有时我们也需要显示的指定使用一致性锁定读来保证读取操作时对数据进行加锁达到一致性。这要求数据库支持锁定读加锁语句：

　●　select ... for update: 读取时对行记录加X锁

　●　select ... lock in share mode：读取时对行记录加一个S锁

这两种锁必须在一个事务中，当事务提交后锁也就释放了，因此务必加上BEGIN, START TRANSACTION或者SET AUTOCOMMIT=0。

我们在前面隔离级别时也说过SERIALIZABLE隔离级别会对读操作自动加上LOCK IN SHARE MODE指令来加上一个共享锁，因此不再支持一致性的非锁定读。这也是隔离级别3的一大特性。

总结

由于锁的概念非常重要，这里先讲了锁的概念、锁的类型、锁的信息查看、事务的隔离级别和区别，后面我们会继续说锁的算法、锁的三种问题和幻读、死锁和锁升级。

推荐学习：MySQL教程

위 내용은 MySQL 잠금 및 트랜잭션 격리 수준(소개)의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!