MySQL の行レベルのロックとは何を意味しますか?

最初に知っておく必要があるのは、mysql ロックは特定のストレージ エンジンによって実装されているということです。したがって、MySQL のデフォルト エンジン MyISAM とサードパーティのプラグイン エンジン InnoDB のロック実装メカニズムには違いがあります。

#Mysql には、テーブル レベルのロック、ページ レベルのロック、行レベルのロックという 3 つのレベルのロックがあります

1. 定義

Every time データの行をロックするロック メカニズムは、行レベルのロック (行レベル) です。行レベルのロックは、MySQL 自体によって実装されるロック方式ではなく、他のストレージ エンジンによって実装されます

2. 利点と欠点

1. 利点

• ロックの粒度が小さいため、競合率が低く、同時実行性が高くなります。

2. 欠点

• 実装が複雑で、コストがかかります。

• ロックが遅く、デッドロックが発生しやすい

3. ストレージ エンジンのサポート

Use row-レベル ロック 主なものは、InnoDB ストレージ エンジンと MySQL 分散ストレージ エンジン NDBCluster

4. 行レベル ロックのタイプ

InnoDB の行レベル ロックも 2 つのタイプに分類されます: 共有ロックと排他ロックロック メカニズムの実装中に行レベルのロックとテーブル レベルのロックが共存できるようにするために、InnoDB はインテンション ロック (テーブル レベルのロック) の概念も使用しており、インテンション共有があります。ロックと意図排他ロックの2種類。

インテンション ロックの機能は、トランザクションがリソース ロックを取得する必要があるときに、必要なリソースがすでに排他ロックによって占有されている場合、トランザクションはロックが必要なテーブルに適切な行を追加できることです。インテンションロック。共有ロックが必要な場合は、テーブルに意図的な共有ロックを追加します。特定の行 (または一部の行) に排他ロックを追加する必要がある場合は、まずテーブルに意図的な排他ロックを追加します。

複数のインテンション共有ロックは同時に存在できますが、同時に存在できるインテンション排他ロックは 1 つだけです。したがって、InnoDB のロック モードは、実際には共有ロック (S)、排他ロック (X)、意図共有ロック (IS)、意図排他ロック (IX) の 4 つのタイプに分類できると言えます。モード互換性:

5. 行レベルのロックの実装方法

InnoDB の行ロックは、インデックスのインデックス エントリをロックすることによって実装されます。したがって、InnoDB はインデックス条件を通じてデータが取得される場合にのみ行レベルのロックを使用し、それ以外の場合はテーブル ロックを使用します。その他の注意事項:

インデックス条件なしでクエリを実行する場合、InnoDB は行ロックの代わりにテーブル ロックを使用します。

• MySQL の行ロックはレコードではなくインデックスに対するロックであるため、異なる行のレコードにアクセスした場合でも、同じインデックス キーを使用するとロック競合が発生します。が発生します。

• テーブルに複数のインデックスがある場合、異なるトランザクションは異なるインデックスを使用して異なる行をロックできます。さらに、主キー インデックス、一意のインデックス、または通常のインデックスのいずれを使用する場合でも、InnoDB は行ロックを使用します。データをロックします。

• 条件でインデックス フィールドが使用されている場合でも、データの取得にインデックスを使用するかどうかは、さまざまな実行プランのコストを判断して MySQL によって決定されます。スキャン効率が高くなります。たとえば、一部の非常に小さなテーブルの場合、インデックスは使用されません。この場合、InnoDB は行ロックの代わりにテーブル ロックを使用します。したがって、ロックの競合を分析するときは、SQL 実行計画をチェックして、インデックスが実際に使用されているかどうかを確認することを忘れないでください。

• 暗黙的なロック:

InnoDB は自動的にインテンション ロックを追加します。

• UPDATE、DELETE、INSERT ステートメントの場合、InnoDB は関連するデータ セットに排他ロック (X) を自動的に追加します;

• 通常の SELECT の場合ステートメント、InnoDB はロックを追加しません;

• 表示ロック:

共有ロック (S): SELECT * FROM table_name WHERE ...共有モードでのロック

• 排他ロック (X): SELECT * FROM table_name WHERE ... FOR UPDATE

• Use SELECT ... IN共有モード 共有ロックの取得は、主に、データの依存関係が必要な場合にレコードの特定の行が存在するかどうかを確認し、このレコードに対して UPDATE または DELETE 操作が実行されないようにするために使用されます。

ただし、現在のトランザクションでもレコードを更新する必要がある場合は、デッドロックが発生する可能性があります。行レコードをロックした後に更新する必要があるアプリケーションの場合は、SELECT... FOR UPDATE メソッドを使用する必要があります。排他的ロックを取得するために使用されます。

InnoDB でテーブルをロックする方法:

LOCK TABLES を使用して InnoDB テーブルをロックする場合は、AUTOCOMMIT を 0 に設定するように注意してください。そうしないと、MySQL はトランザクションが終了する前にテーブルをロックしません。 UNLOCK TABLES は暗黙的にトランザクションをコミットするため、テーブル ロックの解放に UNLOCK TABLES を使用しないでください。COMMIT または ROLLBACK は、LOCK TABLES で追加されたテーブル レベルのロックを解放できません。また、テーブル ロックを解放するには UNLOCK TABLES を使用する必要があります。

SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and t2 here];
COMMIT;
UNLOCK TABLES;

誰もがテーブル ロックを使用しているのですから、MyISAM エンジンを選択してみてはいかがでしょうか?

六、间隙锁（Next-Key锁）

1. 间隙锁定义：

Innodb的锁定规则是通过在指向数据记录的第一个索引键之前和最后一个索引键之后的空域空间上标记锁定信息而实现的。 Innodb的这种锁定实现方式被称为“ NEXT-KEY locking” （间隙锁），因为Query执行过程中通过范围查找的话，它会锁定整个范围内所有的索引键值，即使这个键值并不存在。

例：假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：

mysql> select * from emp where empid > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

2. 间隙锁的缺点：

• 间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

• 当Query无法利用索引的时候， Innodb会放弃使用行级别锁定而改用表级别的锁定，造成并发性能的降低；

• 当Quuery使用的索引并不包含所有过滤条件的时候，数据检索使用到的索引键所指向的数据可能有部分并不属于该Query的结果集的行列，但是也会被锁定，因为间隙锁锁定的是一个范围，而不是具体的索引键；

• 当Query在使用索引定位数据的时候，如果使用的索引键一样但访问的数据行不同的时候（索引只是过滤条件的一部分），一样会被锁定

3 . 间隙锁的作用：

• 防止幻读，以满足相关隔离级别的要求。

• 为了数据恢复和复制的需要。

4. 注意

• 在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

• InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁。

七、查看行级锁争用情况

执行SQL:mysql> show status like 'InnoDB_row_lock%';

mysql> show status like 'InnoDB_row_lock%';
+-------------------------------+-------+| Variable_name                 | Value |
+-------------------------------+-------+| InnoDB_row_lock_current_waits | 0     |
| InnoDB_row_lock_time          | 0     |
| InnoDB_row_lock_time_avg      | 0     |
| InnoDB_row_lock_time_max      | 0     |
| InnoDB_row_lock_waits         | 0     |+-------------------------------+-------+

如果发现锁争用比较严重，还可以通过设置InnoDB Monitors 来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。如：

设置监视器：mysql> create table InnoDB_monitor(a INT) engine=InnoDB;

查看：mysql> show engine InnoDB status;

停止查看：mysql> drop table InnoDB_monitor;

具体参考：InnoDB Monitor

八、死锁

什么是死锁：你等我释放锁，我等你释放锁就会形成死锁。

如何发现死锁： 在InnoDB的事务管理和锁定机制中，有专门检测死锁的机制，会在系统中产生死锁之后的很短时间内就检测到该死锁的存在

解决办法：

• 回滚较小的那个事务

• 在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT…FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题。

判断事务大小：事务各自插入、更新或者删除的数据量

注意：

• 当产生死锁的场景中涉及到不止InnoDB存储引擎的时候，InnoDB是没办法检测到该死锁的，这时候就只能通过锁定超时限制参数InnoDB_lock_wait_timeout来解决。

九、优化行级锁定

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。但是，InnoDB的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

（1）要想合理利用InnoDB的行级锁定，做到扬长避短，我们必须做好以下工作：

• インデックス キーを介してロックできないために InnoDB がテーブル レベルのロックにアップグレードされるのを防ぐために、すべてのデータ取得をできる限りインデックスを介して完了させます。

• InnoDB がインデックス キーをロックする際にできる限り正確になるようにインデックスを合理的に設計し、ロック範囲をできる限り狭くし、他のクエリの実行に影響を与える不要なロックを回避します。

• ギャップ ロックの悪影響によりロックされるべきではないレコードがロックされるのを避けるために、範囲ベースのデータ取得フィルター条件をできる限り減らします。

• トランザクションのサイズを制御し、ロックを減らすようにしてください。リソースの量とロック時間の長さ;

• ビジネス環境が許せば、より低いレベルのトランザクション分離を使用して、ロックを減らすようにしてください。 MySQL がトランザクション分離レベルを実装するための追加コスト。

(2) InnoDB の行レベルのロックとトランザクションの性質により、デッドロックが確実に発生します。デッドロックの可能性を減らすためによく使用されるヒントをいくつか紹介します。

同様のビジネス モジュールでは、デッドロックを防ぐために同じアクセス シーケンスでアクセスするようにしてください。
同じトランザクション内で、すべてのリソースに対して可能な限りの操作を行うようにしてください。デッドロックの可能性を減らすために 1 つのロックに必要です;
デッドロックが非常に発生しやすいビジネス部分については、ロックの粒度をアップグレードし、テーブル レベルのロックを使用して、ロックの可能性を減らすことができます。デッドロックの可能性。
関連する推奨事項:「

mysql チュートリアル

」

以上がMySQL の行レベルのロックとは何を意味しますか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。