MySQL 과거를 검토하고 새로운 내용 배우기 - Innodb 스토리지 엔진의 잠금

최근 자물쇠 문제를 많이 접해서 해결한 후 자물쇠에 관한 책을 꼼꼼히 읽어보았습니다.

1 , 잠금 유형

Innodb 스토리지 엔진 구현 다음 2개의 표준 행 수준 잠금이 제공됩니다.

? 공유 잠금 (S 잠금) , 트랜잭션이 데이터 행을 읽을 수 있도록 허용합니다.

? 배타적 잠금 (X 잠금), 트랜잭션에서 데이터 행을 삭제하거나 업데이트할 수 있습니다.

트랜잭션이 r 행에서 공유 잠금을 획득하면 또 다른 트랜잭션은 r 행의 공유 잠금을 즉시 획득할 수도 있습니다. 읽기 작업이 r 행의 데이터를 변경하지 않기 때문입니다. 케이스 잠금 호환. 그러나 트랜잭션이 r 행에서 배타적 잠금을 얻으려면 트랜잭션이 r—이 경우 두 잠금 장치 간의 호환성은 다음 표에 나와 있습니다.

X 충돌충돌S 충돌호환

2, 잠금 연장

Innodb스토리지 엔진은 행 수준 잠금과 테이블 수준 잠금이 동시에 존재할 수 있는 다중 세분성 잠금을 지원합니다. 다양한 세부 수준의 잠금 작업을 지원하기 위해 InnoDB 스토리지 엔진은 의도 잠금이라는 추가 잠금 방법을 지원합니다. 의도 잠금은 주로 트랜잭션 내의 다음 행에 대해 요청되는 잠금 유형을 표시하기 위해 설계된 테이블 수준 잠금입니다. 또한

? 의도 공유 잠금(IS 잠금)의 두 가지 유형으로 나뉩니다. 트랜잭션은 테이블 공유 잠금.

? 의도적인 배타적 잠금(IX 잠금), 트랜잭션이 테이블의 특정 행에 대한 배타적 잠금을 얻으려고 합니다.

InnoDB는 행 수준 잠금을 지원하므로 의도 잠금은 실제로 작동하지 않습니다. 어셈블리는 전체 테이블 스캔을 제외한 모든 요청을 차단합니다. 공유 잠금, 배타적 잠금, 의도 공유 잠금, 의도 배타적 잠금은 모두 서로 호환 가능/ 상호 배타적 관계로, 이는 호환성 매트릭스로 표현 가능(y는 호환됨을 의미하고, n은 호환되지 않음을 의미합니다 )는 아래와 같습니다 :

전용잠금과 공유잠금의 호환성
	X전용잠금	S🎜>
독점잠금
공유잠금

IX IS X 충돌갈등갈등충돌S 갈등

	X전용잠금	S🎜>	의도 배타 잠금	의도 공유 잠금
독점잠금
공유잠금		호환	충돌
IX 의도전용잠금	충돌	충돌	호환
IS 의도 공유 잠금과 호환됩니다.	충돌	호환	호환	호환

  분석 : X와 S의 상호 호환성 관계 1단계에 대해서는 IX와 IS의 상호 관계가 모두 호환된다는 점을 설명했습니다. 이해하세요. 왜냐하면 그들은 단지 "의도적"이고 아직 YY 단계에 있기 때문입니다. 따라서 그들은

left 다음은 X와 IX, >IS, S 및 IX, S 및 IS를 사용하면 됩니다. X와 S의 관계에서 이 네 가지 관계를 추론해 보세요.

간단히 말하면 🎜>과 X의 관계의 X 및 IX입니다. . 왜? 트랜잭션이 IX 잠금을 획득한 후 X 잠금을 획득할 권한을 갖기 때문입니다. X 및 IX가 호환되는 경우 두 트랜잭션 모두 X 잠금 상황을 얻습니다. , 이는 우리가 알고 있는 X 및 X와 모순되므로 X 과 IX는 상호 배타적인 관계만 가질 수 있습니다. 나머지 세 가지 관계 세트는 유사하며 동일한 방식으로 파생될 수 있습니다.

3

, 시뮬레이션된 잠금 장면

InnoDB 플러그인 이전에는 SHOW FULL PROCESSLIS 및 SHOW ENGINE만 전달할 수 있었습니다. INNODB STATUS 현재 데이터베이스 요청을 확인한 다음 트랜잭션의 잠금 상황을 확인합니다. InnoDB Plugin 새 버전에서는 information_schema에 3이 추가되었습니다. 라이브러리 테이블, INNODB_LOCKS, INNODB_TRX, INNODB_LOCK_WAITS. 이러한 3 테이블을 통해 현재 트랜잭션을 보다 쉽게 ​​모니터링하고 가능한 잠금 문제를 분석할 수 있습니다. 데이터베이스가 정상적으로 실행 중이라면 이 3 테이블은 아무런 기록도 없이 비어 있을 것입니다.

3.1, 공개 거래 t1, t2, 잠금

을 시뮬레이션하여 2세션창을 열고 2 거래 t1 및 t2.

첫 번째 창에서 거래 열기t1다음과 같이 잠금 작업을 수행합니다 t1거래 창 인터페이스:

mysql> set autocommit =0;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
  开始执行锁定操作
mysql> select * from test.t1 where a<5 for update;
+---+----+----+
| a | b  | c  |
+---+----+----+
| 1 | c2 | c2 |
| 2 | a  |    |
| 3 | r5 | r3 |
| 4 | r4 | r5 |
+---+----+----+
4 rows in set (0.00 sec)

mysql>

这个时候，事务t1已经锁定了表t1的所有ace9b6cbcef92d0d53603f70ef979e042 update t1 set a=111 where a=1;ERROR 1205 (HY000) : 잠금 대기 시간 초과, 트랜잭션 다시 시작mysql>

이때 세션

B

2의 거래가 종료되었습니다

update t1 set a=111 where a=1; 의dml작업을 요청합니다. 6

, 자동 증가 및 잠금

自增长在数据库中是非常常见的一种属性，在Innodb的存储引擎的内存结构中，对每个含有自增长值的表都有一个自增长计数器（auto-increment counter）。当对有自增长字段的表进行insert时候，这个计数器会被初始化，执行如下的sql语句来得到计数器的值。

SELECT MAX(auto_inc_col) FROM tablename FOR UPDATE;

插入操作会依据这个自增长的计数器值+1赋予自增长列，这个实现方式称为AUTO-INC Locking，这种锁其实是一种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的sql语句后立即释放。

 

从mysql 5.1.22版本开始，提供了一些轻量级互斥的自增长实现机制，这种机制大大提高自增长值插入的性能。还提供了一个参数innodb_autoinc_lock_mode，默认值为1.

 

自增长的分类：

在mysql的innodb表中，自增长列必须是索引，而且必须为索引的第一列，如果是第二个列会报错如下所示：

mysql> CREATE TABLE t(a INT AUTO_INCREMENT ,b VARCHAR(10),KEY (b,a));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql>
mysql> CREATE TABLE t(a INT AUTO_INCREMENT ,b VARCHAR(10),PRIMARY KEY (a),KEY (b,a));
Query OK, 0 rows affected (0.01 sec)
 
mysql>

而在myisam表中，则没有这样的限制，如下所示：

mysql>  CREATE TABLE t_myisam(a INT AUTO_INCREMENT ,b VARCHAR(10),KEY (b,a))engine=myisam;
Query OK, 0 rows affected (0.01 sec)

 

mysql>

 

7，MySQL外键和锁

在innodb存储引擎中，对于一个外键列，如果没有显式的针对这个列添加索引Innodb存储引擎会自动的对其添加一个索引，这样可以避免表锁，这点比oracle做的较好一些，oracle需要自己手动添加外键锁。

 以上就是MySQL 温故而知新--Innodb存储引擎中的锁的内容，更多相关内容请关注PHP中文网（www.php.cn）！