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的所有a的数据行，然后去第二个窗口开启第二个事务t2，如下，会看到update语句一直在等待事务t1释放锁资源，过了几秒后，会有报错信息，如下t2事务窗口界面：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> update test.t1 set b='t2' where a=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>

 

3.2，通过3个系统表来查看锁信息

l  1，INNODB_TRX表

先看下表的重要字段以及记录的信息

a)         trx_id：innodb存储引擎内部事务唯一的事务id。

b) trx_state: 현재 트랜잭션의 상태입니다.

c)   trx_started: 트랜잭션이 시작된 시간입니다.

d) trx_requested_lock_id: 트랜잭션 잠금 대기 id(예: trx_state의 상태가 LOCK WAIT이면 이 값은 잠금을 점유한 id, trx_state가 LOCK WAIT가 아닌 경우 이 값은 null입니다. .

e)   

trx_wait_started: 트랜잭션이 시작을 기다리고 있는 시간입니다.

f)  

trx_weight: 트랜잭션 가중치는 트랜잭션에 의해 수정되고 잠긴 행 수를 반영합니다. innodb의 스토리지 엔진에서 교착 상태가 발생하여 롤백이 필요한 경우 innodb 스토리지 엔진은 다음과 같은 트랜잭션을 선택합니다. 가장 작은 값 롤백을 수행합니다.

g)         trx_mysql_thread_id：正在运行的mysql中的线程id，show full processlist显示的记录中的thread_id。

h)         trx_query：事务运行的sql语句，在实际中发现，有时会显示为null值，当为null的时候，就是t2事务中等待锁超时直接报错（ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction）后，trx_query就显示为null值

……

因为前面模拟了事务锁场景，开启了t1和t2事务，现在去查看这个表信息，会有2条记录如下：

mysql> select * from INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 3015646
                 trx_state: LOCK WAIT
               trx_started: 2014-10-07 18:29:39
     trx_requested_lock_id: 3015646:797:3:2
          trx_wait_started: 2014-10-07 18:29:39
                trx_weight: 2
       trx_mysql_thread_id: 18
                 trx_query: update test.t1 set b='t2' where a=1
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 376
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 3015645
                 trx_state: RUNNING
               trx_started: 2014-10-07 18:29:15
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 17
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 2
     trx_lock_memory_bytes: 376
           trx_rows_locked: 4
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)
 
mysql>

다음은 transaction t2Runningtrx_query: update test.t1 set b='와 같은 현재 실행 중인 일부 트랜잭션의 기록입니다. t2' 여기서 a=1의 sql 문, t1이 먼저 실행되므로 trx_state: RUNNING처음 적용한 리소스가 실행 중이었고, t2실행은 trx_state: LOCK WAIT가 t1이 실행 후 리소스를 해제할 때까지 기다리고 있었습니다. 그러나 잠금의 일부 세부 사항을 신중하게 판단할 수는 없습니다. INNODB_LOCKS 테이블 데이터를 살펴봐야 합니다.

l 2, INNODB_LOCKS테이블

a)  lock_id: 잠금 id와 잠긴 공간 id번호, 페이지 번호, 행 번호

b) lock_trx_id: 트랜잭션 id를 잠급니다.

c)     lock_mode: 잠금 모드.

d)         lock_type：锁的类型，表锁还是行锁

e)         lock_table：要加锁的表。

f)          lock_index：锁的索引。

g)         lock_space：innodb存储引擎表空间的id号码

h)         lock_page：被锁住的页的数量，如果是表锁，则为null值。

i)           lock_rec：被锁住的行的数量，如果表锁，则为null值。

j)           lock_data：被锁住的行的主键值，如果表锁，则为null值。

mysql> select * from INNODB_LOCKS\G
*************************** 1. row ***************************
    lock_id: 3015646:797:3:2
lock_trx_id: 3015646
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`t1`
 lock_index: PRIMARY
 lock_space: 797
  lock_page: 3
   lock_rec: 2
  lock_data: 1
*************************** 2. row ***************************
    lock_id: 3015645:797:3:2
lock_trx_id: 3015645
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`t1`
 lock_index: PRIMARY
 lock_space: 797
  lock_page: 3
   lock_rec: 2
  lock_data: 1
2 rows in set (0.00 sec)
 
mysql>

여기에서 현재 잠금 정보를 볼 수 있습니다. 2 트랜잭션이 잠겨 있습니다. 동일한 데이터를 확인하세요lock_space: 797, lock_page: 3, lock_rec: 2거래 t1 및 트랜잭션 t2는 동일한 innodb 데이터 블록에 액세스한 다음 lock_data필드 정보lock_data: 1 잠긴 데이터 행은 모두 기본 키가 1. 2 거래 t1 및 t2 모두 동일한 리소스에 적용되었습니다. 그러면 잠기고 트랜잭션이 대기 중입니다.

거래

t1 및 은 lock_mode: X 값 >t2는 전용 잠금 장치에 적용됩니다.

PS: 범위 쿼리 업데이트를 수행할 때 lock_data 값이 완전히 정확하지 않습니다. 범위 업데이트를 실행하면 lock_data는 처음 발견된 id의 기본 키 값만 반환합니다. 동시에 InnoDB 스토리지 엔진 버퍼 풀의 용량으로 인해 잠긴 페이지가 교체됩니다. INNODB_LOCKS 테이블에서 이 lock_data는 unNULL 값을 표시합니다. 이는 InnoDB스토리지 엔진은 디스크에서 다른 조회를 수행하지 않습니다.

l 3, INNODB_LOCK_WAITS테이블

거래량이 상대적으로 적을 때 육안으로 확인할 수 있고, Lock Waiting이 자주 발생하는 경우에는 통과할 수 있습니다 INNODB_LOCK_WAITS 테이블의 주요 필드는 다음과 같습니다.

INNODB_LOCK_WAITS 테이블은 다음과 같습니다.

1)         requesting_trx_id：申请锁资源的事务id。

2)         requested_lock_id：申请的锁的id。

3)         blocking_trx_id：阻塞的事务id。

4)         blocking_lock_id：阻塞的锁的id。

去看下当前锁等待信息，如下所示：

mysql> select * from INNODB_LOCK_WAITS\G
*************************** 1. row ***************************
requesting_trx_id: 3015646
requested_lock_id: 3015646:797:3:2
  blocking_trx_id: 3015645
 blocking_lock_id: 3015645:797:3:2
1 row in set (0.00 sec)
mysql>

这里我们可以看到事务t1(3015646)申请了锁资源，而事务t2(3015645)则阻塞了事务t1的申请。我们管理其他表，得到更直观的详细信息，如下所示：

mysql> SELECT it2.`trx_id` AS waiting_trx_id, it2.`trx_mysql_thread_id` AS waiting_thread,it2.`trx_query` AS waiting_query,  it1.`trx_id` AS blocking_trx_id, it1.`trx_mysql_thread_id` blocking_thread, it1.`trx_query` blocking_query FROM `information_schema`.`INNODB_LOCK_WAITS` ilw, `information_schema`.`INNODB_TRX` it1,`information_schema`.`INNODB_TRX` it2 WHERE it1.`trx_id`=ilw.`blocking_trx_id` AND it2.`trx_id`=ilw.`requesting_trx_id`;
+----------------+----------------+-------------------------------------+-----------------+-----------------+----------------+
| waiting_trx_id | waiting_thread | waiting_query                       | blocking_trx_id | blocking_thread | blocking_query |
+----------------+----------------+-------------------------------------+-----------------+-----------------+----------------+
| 3015647        |             18 | update test.t1 set b='t2' where a>2 | 3015645         |              17 | NULL           |
+----------------+----------------+-------------------------------------+-----------------+-----------------+----------------+
1 row in set (0.00 sec)
 
mysql>

 

4, 일관된 비잠금 읽기 작업

4.1, CNR원리분석

일관적 비잠금 읽기(일관적 비잠금 읽기, CNR)는 행 다중 버전화(다중 버전화)를 통해 현재 실행을 읽는 InnoDB 스토리지 엔진을 의미합니다. 시간 데이터베이스에서 실행되는 데이터 . 읽기 행이 delete, update 작업을 실행 중인 경우 읽기 작업은 행이 잠길 때까지 기다리지 않습니다. , 반대로 InnoDB 스토리지 엔진은 다음 그림과 같이 행의 스냅샷 데이터를 읽습니다.

비잠금 읽기, 기다릴 필요가 없기 때문 액세스된 행에 대해

롤링 데이터가 출시되므로 스냅샷 자체에 추가 오버헤드가 없습니다. 또한 스냅샷을 읽는 데는 기록 데이터를 수정할 필요가 없으므로 잠금이 필요하지 않습니다. 비잠금 읽기는 데이터 읽기의 동시성을 크게 향상시킵니다.

InnoDB

스토리지 엔진의 기본 설정에서는 이것이 기본 읽기입니다. 이런 식으로 읽기는 테이블의 잠금을 점유하지 않고 기다리지 않습니다. 그러나 읽기 방법은 트랜잭션 격리 수준에 따라 다릅니다. 모든 트랜잭션 격리 수준이 일관된 읽기인 것은 아닙니다. 마찬가지로 둘 다 일관된 읽기를 사용하더라도 스냅샷 데이터의 정의는 다릅니다.

 

快照数据其实就是当前数据之前的历史版本，可能有多个版本。如上图所示，一个行可能不止有一个快照数据。我们称这种技术为行多版本技术。由此带来的并发控制，称之为多版本并发控制（Multi Version Concurrency Control，MVCC）。

 

在Read Committed和Repeatable Read模式下，innodb存储引擎使用默认的非锁定一致读。在Read Committed隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据；而在Repeatable Read隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。

 

4.2，CNR实例

开启2个Session A和B。

Session  A：
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
 
mysql> select * from t1 where a=1;
+---+----+----+
| a | b  | c  |
+---+----+----+
| 1 | c2 | c2 |
+---+----+----+
1 row in set (0.00 sec)
 
mysql>

Session A中事务已经开始，读取了a=1的数据，但是还没有结束事务，这时我们再开启一个Session B，以此模拟并发的情况，然后对Session B做如下操作：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> update t1 set a=111 where a=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql>

Session 中将a=1的行修改为a=111，但是事务同样没有提交，这样a=1的行其实加了一个X锁。这时如果再在Session A中读取a=1的数据，根据innodb存储引擎的特性，在Read Committed和Repeatable Read事务隔离级别下，会使用非锁定的一致性读。回到Session A，节着上次未提交的事务，执行select * from t1 where a=1;的操作，显示的数据应该都是原来的数据：

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

因为当前a=1的数据被修改了1次，所以只有一个版本的数据，接着我们在Session B中commit上次的事务。如：

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
 
mysql>

 

Session B提交事务后，这时再在Session A中运行select * from t1 where a=1;的sql语句，在READ-COMMITTED和REPEATABLE-READ事务隔离级别下，得到的结果就会不一样，对于READ-COMMITTED模事务隔离级别，它总是读取行的最新版本，如果行被锁定了，则读取该行的最新一个快照(fresh snapshot)。因此在这个例子中，因为Session B已经commit了事务，所以在READ-COMMITTED事务隔离级别下会得到如下结果，查询a=1就是为null记录，因为a=1的已经被commit成了a=111，但是如果查询a=111的记录则会被查到，如下所示：

mysql> show variables like 'tx_isolation';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)
 
mysql> select * from t1 where a=1;
Empty set (0.00 sec)
 
mysql> select * from t1 where a=111;
+-----+----+----+
| a   | b  | c  |
+-----+----+----+
| 111 | c2 | c2 |
+-----+----+----+
1 row in set (0.01 sec)
 
mysql>

 

但是如果在REPEATABLE-READ事务隔离级别下，总是读取事务开始时的数据，所以得到的结果截然不同，如下所示：

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
 
mysql> select * from t1 where a=1;
+---+----+----+
| a | b  | c  |
+---+----+----+
| 1 | c0 | c2 |
+---+----+----+
1 row in set (0.00 sec)
 
mysql> select * from t1 where a=111;
Empty set (0.00 sec)
 
mysql>

 

对于READ-COMMITTED的事务隔离级别而言，从数据库理论的角度来看，其实违反了事务ACID的I的特性，既是隔离性，整理成时序表，如下图所示。

Time	Session  A	Session  B
| time 1	Begin; Select * from t1 where a=1;有记录
| time 2		시작; 업데이트 t1 set a=111 여기서 a=1;
| 시간 3	a=1인 t1에서 * 선택;有记录
| 시간 4		커밋;
| 시간 5	t1에서 *를 선택합니다. 여기서 a=1; 无记录
뷔 6시	커밋;

세션 내내 나 격리 원칙을 따르는 경우 세션 A, 선택 * a=1;이 쿼리된 데이터를 유지해야 하는 t1부터 5번 세션 A가 종료되지 않은 상태에서 쿼리 결과가 변경되어 시간 1 및 시간 3과 일치하지 않으며 ACID 격리를 충족하지 않습니다.

5

, 선택...업데이트 및 선택...공유 모드 잠금기본적으로

innodb

스토리지 엔진의 select 작업은 일관성을 사용합니다. 읽기를 잠그고 있지만 경우에 따라 읽기 작업을 잠가야 할 수도 있습니다. Innodb스토리지 엔진은 2 종류의 추가 잠금 작업;? SELECT ... FOR UPDATE 읽은 행 레코드 X에

를 추가하면 다른 트랜잭션은 이 행에 대해 dml 또는 select 작업을 수행하려는 경우 차단됩니다.

?  SELECT ... LOCK IN SHARE MODE 对于读取的行记录添加一个S共享锁。其它事务可以向被锁定的行加S锁，但是不允许添加X锁，否则会被阻塞。

对于一致性 非锁定读，即使读取的行数已经被SELECT ... FOR UPDATE了，但是也是可以进行读取的。

         PS：… FOR UPDATE以及LOCK IN SHARE MODE必须在一个事务中，如果事务commit了，锁也就释放了，所以在使用的时候务必加上begin;start transaction或者set autocommit=0;

 

例子如下：

会话A：开启事务，执行LOCK IN SHARE MODE;锁定

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT * FROM t1 WHERE a=1 LOCK IN SHARE MODE;
+---+----+----+
| a | b  | c  |
+---+----+----+
| 1 | c0 | c2 |
+---+----+----+
1 row in set (0.00 sec)
 
mysql>

 

同时在另外一个窗口开启会话B，执行dml操作

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> update t1 set a=111 where a=1;

…这里会卡住，没有信息。

 

再开启一个会话C，查询INNODB_LOCKS、INNODB_TRX、INNODB_LOCK_WAITS表，就会看到锁的详细信息：

mysql> select * from INNODB_LOCKS;
+-----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| lock_id         | lock_trx_id | lock_mode | lock_type | lock_table  | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| 3015708:797:3:2 | 3015708     | X         | RECORD    | `test`.`t1` | PRIMARY    |        797 |         3 |        2 | 1         |
| 3015706:797:3:2 | 3015706     | S         | RECORD    | `test`.`t1` | PRIMARY    |        797 |         3 |        2 | 1         |
+-----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
2 rows in set (0.00 sec)
 
mysql>
mysql> select * from INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 3015708           | 3015708:797:3:2   | 3015706         | 3015706:797:3:2  |
+-------------------+-------------------+-----------------+------------------+
1 row in set (0.00 sec)
 
mysql>
mysql> SELECT it2.`trx_id` AS waiting_trx_id, it2.`trx_state` AS waiting_trx_status,it2.`trx_mysql_thread_id` AS waiting_thread,it2.`trx_query` AS waiting_query,
    -> it1.`trx_id` AS blocking_trx_id, it1.`trx_mysql_thread_id` blocking_thread, it1.`trx_query` blocking_query
    -> FROM `information_schema`.`INNODB_LOCK_WAITS` ilw, `information_schema`.`INNODB_TRX` it1,`information_schema`.`INNODB_TRX` it2
    -> WHERE it1.`trx_id`=ilw.`blocking_trx_id` AND it2.`trx_id`=ilw.`requesting_trx_id`;
+----------------+--------------------+----------------+-------------------------------+-----------------+-----------------+----------------+
| waiting_trx_id | waiting_trx_status | waiting_thread | waiting_query                 | blocking_trx_id | blocking_thread | blocking_query |
+----------------+--------------------+----------------+-------------------------------+-----------------+-----------------+----------------+
| 3015708        | LOCK WAIT          |             18 | update t1 set a=111 where a=1 | 3015706         |              21 | NULL           |
+----------------+--------------------+----------------+-------------------------------+-----------------+-----------------+----------------+
1 row in set (0.00 sec)
 
mysql>

会话A开启的事务1（事务id：3015706）执行了SELECT * FROM t1 WHERE a=1 LOCK IN SHARE MODE;

문은 a=1 줄에 S 잠금을 추가했으므로 세션 B열린 거래2(거래ID:23015708) update t1 set a=111 where a=1;sql 문을 a=1 을 추가하면 2의 상태 값이 기다리고 있던 LOCK WAIT이 됩니다. 트랜잭션이 시간 초과를 기다릴 때까지 오류는 다음과 같이 보고됩니다. mysql> 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）！