同時データベース要求が多い場合にデータの整合性を確保するにはどうすればよいですか? MySQL/InnoDB ロックの詳細な説明

この記事は、MySQL/InnoDB における楽観的ロック、悲観的ロック、共有ロック、排他的ロック、行ロック、テーブル ロック、およびデッドロックの概念を理解するものです。これらは、同時データベース リクエストなど、インタビューでよく遭遇します。データの整合性を確保するには?今日は、誰もが面倒で面倒だと思わないように、情報を確認し、MySQL/InnoDB のロックに関する知識ポイントをまとめました。役に立ったと思われる場合は、引き続き共有してください。 apache php mysql

注: MySQL は、プラグイン ストレージ エンジンをサポートするデータベース システムです。この記事のすべての紹介は InnoDB ストレージ エンジンに基づいています。他のエンジンのパフォーマンスはまったく異なります。

ストレージ エンジン ビュー

MySQL は開発者にストレージ エンジンをクエリする機能を提供します。ここでは MySQL5.6.4 を使用します。

SHOW ENGINESSHOW ENGINES

begin！

乐观锁

用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。

举例

1、数据库表设计

三个字段，分别是id,value、version

select id,value,version from TABLE where id=#{id}

2、每次更新表中的value字段时，为了防止发生冲突，需要这样操作

update TABLE
set value=2,version=version+1
where id=#{id} and version=#{version};

悲观锁

与乐观锁相对应的就是悲观锁了。悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟java中的synchronized很相似，所以悲观锁需要耗费较多的时间。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。

说到这里，由悲观锁涉及到的另外两个锁概念就出来了，它们就是共享锁与排它锁。共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴。

使用，排它锁 举例

要使用悲观锁，我们必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。

我们可以使用命令设置MySQL为非autocommit模式：

set autocommit=0;

# 设置完autocommit后，我们就可以执行我们的正常业务了。具体如下：

# 1. 开始事务

begin;/begin work;/start transaction; (三者选一就可以)

# 2. 查询表信息

select status from TABLE where id=1 for update;

# 3. 插入一条数据

insert into TABLE (id,value) values (2,2);

# 4. 修改数据为

update TABLE set value=2 where id=1;

# 5. 提交事务

commit;/commit work;

共享锁

共享锁又称读锁 read lock，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。

如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获得共享锁的事务只能读数据，不能修改数据

打开第一个查询窗口

begin;/begin work;/start transaction;  (三者选一就可以)

SELECT * from TABLE where id = 1  lock in share mode;

然后在另一个查询窗口中，对id为1的数据进行更新

update  TABLE set name="www.souyunku.com" where id =1;

此时，操作界面进入了卡顿状态，过了超时间，提示错误信息

如果在超时前，执行 commit，此更新语句就会成功。

[SQL]update  test_one set name="www.souyunku.com" where id =1;
[Err] 1205 - Lock wait timeout exceeded; try restarting transaction

加上共享锁后，也提示错误信息

update  test_one set name="www.souyunku.com" where id =1 lock in share mode;

[SQL]update  test_one set name="www.souyunku.com" where id =1 lock in share mode;
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'lock in share mode' at line 1

在查询语句后面增加 LOCK IN SHARE MODE，Mysql会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。

加上共享锁后，对于update,insert,delete语句会自动加排它锁。

排它锁

排他锁 exclusive lock（也叫writer lock）又称写锁。

排它锁是悲观锁的一种实现，在上面悲观锁也介绍过。

若事务 1 对数据对象A加上X锁，事务 1 可以读A也可以修改A，其他事务不能再对A加任何锁，直到事物 1 释放A上的锁。这保证了其他事务在事物 1 释放A上的锁之前不能再读取和修改A。排它锁会阻塞所有的排它锁和共享锁

读取为什么要加读锁呢：防止数据在被读取的时候被别的线程加上写锁，

使用方式：在需要执行的语句后面加上for update

開始!

オプティミスティックロックは、オプティミスティックロックの最も一般的に使用される実装方法であるデータバージョン(Version)記録メカニズムを使用して実装されます。データバージョンとは何ですか?つまり、通常は数値の「バージョン」フィールドをデータベース テーブルに追加することによって、データにバージョン識別子を追加します。データを読み込む際には、バージョンフィールドの値も一緒に読み込まれ、データが更新されるたびにバージョン値が 1 ずつ増加します。更新を送信するとき、データベース テーブルの対応するレコードの現在のバージョン情報と、データベース テーブルの現在のバージョン番号が最初に取得したバージョン値とを比較します。初回の場合は更新されます。それ以外の場合は、期限切れのデータとみなされます。

🎜例🎜🎜🎜1. データベーステーブルの設計🎜🎜 3 つのフィールド、つまり id、value、version 🎜

SELECT * from TABLE where id = "1"  lock in share mode;  结果集的数据都会加共享锁

🎜2. テーブル内の値フィールドが更新されるたびに、競合しています、これを行う必要があります 🎜

select status from TABLE where id=1 for update;

🎜悲観的ロック🎜🎜 楽観的ロックに相当するのは悲観的ロックです。悲観的ロックとは、データを操作するときに、この操作によってデータの競合が発生すると考えられるため、すべての操作で同じデータを操作するためにロックを取得する必要があることを意味します。これは Java の同期と非常によく似ており、悲観的ロックにはより多くの時間がかかります。 。さらに、楽観的ロックに対応して、悲観的ロックはデータベース自体によって実装され、使用する必要がある場合は、データベースの関連ステートメントを直接呼び出すことができます。 🎜🎜そう言えば、悲観的ロックに関係する他の 2 つのロック概念が出てきます。それらは共有ロックと排他的ロックです。 🎜共有ロックと排他ロックは悲観的ロックの異なる実装です🎜、どちらも悲観的ロックのカテゴリに属します。 🎜🎜🎜排他的ロックの例を使用する🎜🎜🎜悲観的ロックを使用するには、mysql データベースの自動コミット属性をオフにする必要があります。MySQL はデフォルトで自動コミット モードを使用するためです。つまり、更新操作を実行すると、MySQL はすぐに結果を提出してください。 🎜🎜次のコマンドを使用して MySQL を非自動コミット モードに設定できます: 🎜

show OPEN TABLES where In_use > 0;

🎜共有ロック🎜🎜共有ロック (🎜読み取りロック読み取りロック🎜 とも呼ばれる) は、読み取り操作によって作成されるロックです。他のユーザーは同時にデータを読み取ることができますが、すべての共有ロックが解放されるまで、トランザクションはデータを変更する (データの排他的ロックを取得する) ことはできません。 🎜🎜トランザクション T がデータ A に共有ロックを追加した場合、他のトランザクションは A に共有ロックを追加することしかできず、排他ロックを追加することはできません。共有ロックを取得するトランザクションは、データの読み取りのみが可能ですが、データの変更はできません🎜🎜最初のクエリウィンドウを開きます🎜

show processlist

🎜次に、別のクエリウィンドウで、ID 1のデータを更新します🎜

kill id

🎜このとき、操作インターフェイスは次のようになりますスタック状態では、タイムアウト後にエラー メッセージが表示されます🎜🎜 タイムアウト前に commit が実行された場合、この更新ステートメントは成功します。 🎜

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

🎜 共有ロックを追加すると、クエリ ステートメントの後に 🎜 LOCK IN SHARE MODE🎜 を追加すると、エラー メッセージも表示されます。他のスレッドがクエリ結果セット内の行に対して排他ロックを使用していない場合、そのスレッドは共有ロックを正常に適用できますが、それ以外の場合はブロックされます。他のスレッドも共有ロックを使用してテーブルを読み取ることができ、これらのスレッドは同じバージョンのデータを読み取ります。 🎜🎜共有ロックを追加すると、排他ロックが update、insert、delete ステートメントに自動的に追加されます。 🎜🎜排他ロック🎜🎜排他ロック排他ロック（ライターロックとも呼ばれます）は🎜書き込みロック🎜とも呼ばれます。 🎜🎜🎜排他的ロックは、上でも紹介した悲観的ロックの実装です🎜。 🎜🎜トランザクション 1 がデータ オブジェクト A に X ロックを追加した場合、トランザクション 1 は A を読み取りまたは変更できます。トランザクション 1 が A のロックを解放するまで、他のトランザクションは A にそれ以上ロックを追加できません。これにより、トランザクション 1 が A のロックを解放するまで、他のトランザクションは A を読み取ったり変更したりできなくなります。排他ロックはすべての排他ロックと共有ロックをブロックします🎜🎜読み取り時に読み取りロックを追加する必要がある理由: 読み取り時に他のスレッドによってデータが書き込みロックに追加されるのを防ぐためです🎜🎜使用法: 実行する必要がある場合🎜🎜行ロック🎜🎜行ロックは、文字通りに理解すると、あるものにロックを追加することを意味します。行、つまり、単なるレコードとロックです。 🎜

注意：行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。

共享锁：

名词解释：共享锁又叫做读锁，所有的事务只能对其进行读操作不能写操作，加上共享锁后在事务结束之前其他事务只能再加共享锁，除此之外其他任何类型的锁都不能再加了。

SELECT * from TABLE where id = "1"  lock in share mode;  结果集的数据都会加共享锁

排他锁：

名词解释：若某个事物对某一行加上了排他锁，只能这个事务对其进行读写，在此事务结束之前，其他事务不能对其进行加任何锁，其他进程可以读取,不能进行写操作，需等待其释放。

select status from TABLE where id=1 for update;

可以参考之前演示的共享锁,排它锁语句

由于对于表中,id字段为主键，就也相当于索引。执行加锁时，会将id这个索引为1的记录加上锁，那么这个锁就是行锁。

表锁

如何加表锁

innodb 的行锁是在有索引的情况下,没有索引的表是锁定全表的.

Innodb中的行锁与表锁

前面提到过，在Innodb引擎中既支持行锁也支持表锁，那么什么时候会锁住整张表，什么时候或只锁住一行呢？
只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。行级锁的缺点是：由于需要请求大量的锁资源，所以速度慢，内存消耗大。

死锁

死锁（Deadlock） 
所谓死锁：是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。由于资源占用是互斥的，当某个进程提出申请资源后，使得有关进程在无外力协助下，永远分配不到必需的资源而无法继续运行，这就产生了一种特殊现象死锁。

解除正在死锁的状态有两种方法：

第一种：

1.查询是否锁表

show OPEN TABLES where In_use > 0;

2.查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程）

show processlist

3.杀死进程id（就是上面命令的id列）

kill id

第二种：

1：查看当前的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2：查看当前锁定的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

3：查看当前等锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

杀死进程

kill 线程ID

如果系统资源充足，进程的资源请求都能够得到满足，死锁出现的可能性就很低，否则就会因争夺有限的资源而陷入死锁。其次，进程运行推进顺序与速度不同，也可能产生死锁。
产生死锁的四个必要条件：

（1） 互斥条件：一个资源每次只能被一个进程使用。
（2） 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。
（3） 不剥夺条件:进程已获得的资源，在末使用完之前，不能强行剥夺。
（4） 循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。

虽然不能完全避免死锁，但可以使死锁的数量减至最少。将死锁减至最少可以增加事务的吞吐量并减少系统开销，因为只有很少的事务回滚，而回滚会取消事务执行的所有工作。由于死锁时回滚而由应用程序重新提交。

下列方法有助于最大限度地降低死锁：

（1）按同一顺序访问对象。
（2）避免事务中的用户交互。
（3）保持事务简短并在一个批处理中。
（4）使用低隔离级别。
（5）使用绑定连接。

end！

相关文章：

数据库并发事务控制 二：mysql数据库锁机制

Mysql数据库锁定机制详细介绍

相关视频：

数据库设计那些事

以上が同時データベース要求が多い場合にデータの整合性を確保するにはどうすればよいですか? MySQL/InnoDB ロックの詳細な説明の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。