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는 테이블 잠금을 지원하므로 의도 잠금은 전체 테이블 스캔을 제외한 모든 요청을 차단하지 않습니다.

잠금 호환성:

INNODB_TRX

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

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)

[mysqld]
transaction-isolation = 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)

#在事务开始前我们可以分别调整为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)

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