MySQL 過去を振り返り、新しいことを学ぶ -- Innodb ストレージ エンジンのロック

最近、ロックの問題にたくさん遭遇したので、それらを解決した後、ロックに関する本を注意深く読みました。それらは次のように構成されています:

1、ロックの種類。

Innodb ストレージ エンジンは、次の 2 の標準行レベル ロックを実装しています:

? 共有ロック (S ロック) により、トランザクションは 1 行のデータを読み取ることができます。

? 排他的ロック(X ロック)。トランザクションがデータ行を削除または更新できるようにします。

トランザクションが行 r の共有ロックを取得すると、読み取りによって行 が変更されないため、別のトランザクションもすぐに行 r の共有ロックを取得できます。 この場合、rのデータはロック対応です。ただし、トランザクションが行 r の排他ロックを取得したい場合は、トランザクションが行 r— を解放するまで待つ必要があります。この場合、ロックは互換性がありません。次の表に示すように、この 2 つは互換性がありません:

排他ロックと共有ロックの互換性

競合

競合

S 共有ロック

競合

互換性があります

2、ロック拡張

Innodb ストレージ エンジンは、行レベルでのロックとテーブル レベルでのロックを同時に許可する多粒度ロックをサポートしています。時間が存在する。さまざまな粒度でのロック操作をサポートするために、InnoDB ストレージ エンジンは、インテンション ロックという追加のロック方法をサポートしています。インテンション ロックは、主にトランザクション内の次の行に要求されるロックの種類を明らかにするために設計されたテーブル レベルのロックです。また、2 つのタイプに分けられます:

? 意図された共有ロック (IS Lock)、トランザクションはテーブル内の特定の行の共有ロックを取得したいと考えています。

? 意図的な排他ロック (IX Lock)、トランザクションはテーブル内の特定の行に対して排他ロックを取得しようとしています。

InnoDB は行レベルのロックをサポートしているため、インテンション ロックはテーブル全体 scan 以外のリクエストを実際にはブロックしません。共有ロック、排他ロック、意図共有ロック、意図排他ロックはすべて相互に互換性があります/相互に排他的な関係は、互換性マトリックスで表すことができます(yは互換性を意味し、n は互換性がないことを意味します)、以下に示すように:

競合

	IS 意図共有ロック		Clash	S シェアロック
	互換性	競合	互換性
IX 意図排他ロック	紛争	紛争	互換性あり	互換性あり
はインテント共有ロック	競合	互換性あり	互換性あり	対応

それらの間の関係はすべて互換性があります。つまり、それらはすべて単に「意図的」であり、まだ YY ステージで実際には何もしていないので、互換性があります。 残りは X と IX 、 S これら 4 つの関係セットは、関係から派生します。

簡単に言うと、の関係なぜ？なぜなら、トランザクションは IX ロックを取得した後、X ロックを取得する権利を有するからです。 XとIXが互換性がある場合、両方のトランザクションがを取得する状況が発生します。相互排他は矛盾するため、XとIXは相互排他のみ可能です。残りの 3 セットの関係は類似しており、同じ方法で導出できます。 3、ロックシーンをシミュレートします

InnoDB Pluginを導入する前は、SHOW FULL PROCESSLISとSHOW ENGINE INNODB STATUSを介して現在のデータベースリクエストを表示し、トランザクション内のロック状況を判断することしかできませんでした。新しいバージョンの InnoDB プラグイン では、3 テーブル、INNODB_LOCKS、INNODB_TRX が information_schema に追加されましたライブラリ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的所有a09fbe79aedf80e9e036f207afb84b7ef update t1 set a=111 where a=1;ERROR 1205 (HY000): ロック待機タイムアウトを超過しました

mysql>

この時点で、セッション

B

のトランザクション

2

が終了します

update t1 set a=111 where a=1;'s 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）！