MySQL Werfen Sie einen Blick auf die Vergangenheit und lernen Sie das Neue kennen – Sperren in der Innodb-Speicher-Engine

3.1, offene Transaktion t1, t2, Simulieren Sie die Sperre

, um 2Sitzungsfenster zu öffnen, und öffnen Sie 2 Transaktionen t1 und t2.

Öffnen Sie die Transaktion im ersten Fenster t1Führen Sie einen Sperrvorgang aus, wie folgt t1Transaktionsfensterschnittstelle:

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<5的数据行，然后去第二个窗口开启第二个事务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: Der Status der aktuellen Transaktion.

c)   trx_started: Der Zeitpunkt, zu dem die Transaktion gestartet wurde.

d) trx_requested_lock_id: Warten auf Transaktionssperre id, z. B. Der Status von trx_state ist LOCK WAIT, dann stellt dieser Wert die id< dar, die die Sperre belegt hat Ressource vor der aktuellen Transaktion. 🎜>, wenn trx_state nicht LOCK WAIT ist, ist dieser Wert null .

e)   trx_wait_started: Die Zeit, die die Transaktion auf den Start wartet.

f)  trx_weight: Die Gewichtung einer Transaktion spiegelt die Anzahl der Zeilen wider, die durch eine Transaktion geändert und gesperrt wurden. Wenn in der Speicher-Engine von innodb ein Deadlock auftritt und ein Rollback erforderlich ist, wählt die Speicher-Engine innodb die Transaktion mit aus kleinster Wert Rollback durchführen.

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>

Hier ist nur eine Aufzeichnung einiger aktuell laufender Transaktionen, wie z. B. Transaktion t2Runningtrx_query: update test.t1 set b=' t2' wobei die sql-Anweisung von a=1, t1 zuerst ausgeführt wird, also trx_state: RUNNINGDie zuerst beantragte Ressource wurde ausgeführt und die Ressource wurde nach t2run<🎜 angewendet > ist so trx_state: LOCK WAIT hat auf t1 gewartet, um Ressourcen nach der Ausführung freizugeben. Allerdings können wir einige Details der Sperre nicht sorgfältig beurteilen. Wir müssen uns die Tabellendaten INNODB_LOCKS ansehen.

l 2, INNODB_LOCKSTabelle

a)  lock_id: lock Die id und das gesperrte Leerzeichen idNummer, Seitenzahl, Zeilennummer

b) lock_trx_id: Transaktion id sperren.

c)     lock_mode: Sperrmodus.

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>

Hier können wir die aktuellen Sperrinformationen sehen, 2Transaktionen sind gesperrt, siehe die gleichen Datenlock_space: 797, lock_page: 3, lock_rec: 2Es kann geschlossen werden, dass die Transaktion t1 und Transaktion t2 greifen auf denselben innodb Datenblock zu und übergeben dann lock_dataFeldinformationenlock_data: 1 Es ist ersichtlich, dass es sich bei den gesperrten Datenzeilen um Datensätze mit dem Primärschlüssel 1<🎜 handelt >. 2 Transaktionen t1 und t2 beantragten beide die gleichen Ressourcen, wird also gesperrt und die Transaktion wartet.

Transaktionen t1 und können auch über den lock_mode: X<🎜 eingesehen werden > Wert >t2 gilt für exklusive Sperren.

PS: Bei der Aktualisierung der Bereichsabfrage ist der Wert von lock_data nicht ganz korrekt. Wenn wir eine Bereichsaktualisierung durchführen, gibt lock_data nur den Primärschlüsselwert der ersten Zeile zurück, die zuerst gefunden wurde id; Die aktuelle Ressource ist gesperrt. Gleichzeitig wird die gesperrte Seite aufgrund der Kapazität des InnoDB-Speicher-Engine-Pufferpools ersetzt 🎜> Tabelle, diese lock_data zeigt einen NULL-Wert an, was InnoDB<🎜 bedeutet >Die Speicher-Engine führt keine weitere Suche auf der Festplatte durch.

3, INNODB_LOCK_WAITSTabelleWenn das Transaktionsvolumen relativ klein ist, können wir es visuell überprüfen. Wenn das Transaktionsvolumen sehr groß ist und häufig auf Sperren gewartet wird, können wir zu diesem Zeitpunkt bestehen

lauten wie folgt: 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, konsistenter, nicht sperrender Lesevorgang

4.1, CNRPrinzipanalyse

Konsistenter, nicht sperrender Lesevorgang (konsistenter, nicht sperrender Lesevorgang, bezeichnet als CNR) bezieht sich auf die InnoDB-Speicher-Engine, die die aktuelle Ausführung durch Zeilen-Multiversionierung (Multiversionierung) liest. Daten laufen in der Zeitdatenbank . Wenn die gelesene Zeile Lösch--, Aktualisierungs--Vorgänge ausführt, wartet der Lesevorgang nicht darauf, dass die Zeile gesperrt wird Im Gegensatz dazu liest die Speicher-Engine InnoDB Snapshot-Daten der Zeile, wie in der folgenden Abbildung dargestellt:

Nicht sperrendes Lesen, da keine Wartezeit erforderlich ist für zugegriffene Zeilen< Die Veröffentlichung von 🎜> Rollierende Daten, sodass der Snapshot selbst keinen zusätzlichen Overhead verursacht. Darüber hinaus ist für das Lesen des Snapshots keine Sperre erforderlich, da keine Änderung historischer Daten erforderlich ist. Nicht sperrendes Lesen verbessert die Parallelität des Datenlesens erheblich. In den Standardeinstellungen der

-Speicher-Engine ist dies das Standardlesen Auf diese Weise wird der Lesevorgang nicht in Anspruch genommen und es wird nicht darauf gewartet, dass die Tabelle gesperrt wird. Allerdings unterscheiden sich die Lesemethoden je nach Transaktionsisolationsstufe. Nicht jede Transaktionsisolationsstufe ist konsistent. Auch wenn beide konsistente Lesevorgänge verwenden, ist die Definition von Snapshot-Daten unterschiedlich.

快照数据其实就是当前数据之前的历史版本，可能有多个版本。如上图所示，一个行可能不止有一个快照数据。我们称这种技术为行多版本技术。由此带来的并发控制，称之为多版本并发控制（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的特性，既是隔离性，整理成时序表，如下图所示。

Wenn das I-Prinzip der Isolation während der gesamten Sitzung Sitzung A befolgt wird, Auswählen * ab t1, wobei a=1; die abgefragten Daten behalten sollte, aber zum Zeitpunkt 5Bevor Sitzung A nicht beendet wurde, hat sich das Abfrageergebnis geändert und ist stimmt nicht mit Zeit 1 und Zeit 3 ​​überein und erfüllt nicht die Isolierung von ACID .

, AUSWÄHLEN ... FÜR UPDATE && AUSWÄHLEN ... IM TEILUNGSMODUS SPERRENStandardmäßig verwendet der

-Vorgang der innodb-Speicher-Engine Konsistenz. Sperren des Lesens, aber in einigen Fällen muss der Lesevorgang gesperrt werden. InnodbDie Speicher-Engine unterstützt 2 Arten von Add-Lock-Vorgängen;? SELECT ... FOR UPDATE Eine

exklusive Sperre, andere Transaktionen werden berücksichtigt blockiert, wenn sie dml oder select-Vorgänge für diese Zeilen ausführen möchten.

? 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;

-Anweisung hat die S-Sperre zur Zeile a=1 hinzugefügt, also zur Sitzung BGeöffnete Transaktion2 (TransaktionID:23015708) führt die Anweisung update t1 set a=111 where a=1;sql für die Zeile von a=1<🎜 aus > Hinzufügen von , sodass der Statuswert der Transaktion 2 LOCK WAIT ist, die gewartet hat. Bis die Transaktion auf eine Zeitüberschreitung wartet, wird der Fehler wie folgt gemeldet: mysql> update t1 set a=111 where a=1;FEHLER 1205 (HY000): Sperrwartezeitüberschreitung; versuchen Sie, die Transaktion neu zu startenmysql>

Zu diesem Zeitpunkt Sitzung

B< Die Transaktion in 🎜>

wird beendet

'sdmlAnforderungsvorgang. 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中文网（m.sbmmt.com）！