MySQL のトランザクションとロックについて詳しく学ぶ
- 青灯夜游転載
- 2021-09-10 19:32:501881ブラウズ
MySQL データベースはマルチユーザー アクセス システムであるため、複数のユーザーが同時にデータの読み取りと更新を行った場合、データは破壊されないため、ロックが発生します。ユーザーがデータベース内のレコードを変更しようとするときは、まずロックを取得する必要があります。その後、ロックを保持しているユーザーが変更を行っている間、他のユーザーはこれらのレコードを変更できません。 [関連する推奨事項: mysql ビデオ チュートリアル ]
MySQL のロック
しかし、他のデータベースと比較して、MySQL のロック メカニズムは比較的単純です。ストレージ エンジンには異なるロック メカニズムがあります。MylSAM および MEMORY ストレージ エンジンはテーブル レベルのロックを使用し、BDB ストレージ エンジンはページ ロックを使用し、一般的に使用される InnoDB ストレージ エンジンは行レベルのロックとテーブル レベルのロックをサポートします。デフォルトでは行レベルロックが使用されています。
これら 3 種類のロックの特徴は次のとおりです。
テーブル レベルのロック: 低いオーバーヘッド、高速なロック、デッドロックなし、大きなロック粒度、およびロック競合。確率は最も高く、同時実行性は最も低くなります。
行レベルのロック: オーバーヘッドが高く、ロックが遅く、デッドロックが発生します。ロックの粒度は最も小さく、ロックの競合の可能性は最も低く、同時実行性は最も高くなります。
ページ ロック: コストとロック時間はテーブル ロックと行ロックの間です。デッドロックが発生します。ロックの粒度はテーブル ロックと行ロックの間です。同時実行性は平均です。
#MyISAM
MyISAM テーブル ロックMySQL が提供するものは次のとおりです。
- READ LOCK: ユーザーがテーブルからのデータの読み取りのみを許可します。
- WRITE LOCK: ユーザーがテーブルの読み取りと書き込みをできるようにします。
- テーブルに対する MyISAM の読み取り操作は、同じテーブルに対する他のユーザーの読み取りリクエストをブロックしませんが、同じテーブルに対する書き込みリクエストはブロックします。テーブルに対する MyISAM の書き込み操作はブロックされます。同じテーブル上の他のユーザーの読み取り操作と書き込み操作、MyISAM テーブルの読み取り操作と書き込み操作、および書き込み操作はシリアルです。
MyISAM は、クエリ ステートメント (SELECT) を実行する前に、使用されるすべてのテーブルに読み取りロックを自動的に追加し、更新操作 (UPDATE、DELETE、INSERT など) を実行する前に、関連するテーブルに書き込みロックを自動的に追加します。このプロセスには手動介入が必要ないため、通常は LOCK TABLE コマンドを使用して MyISAM テーブルを明示的にロックする必要はありませんが、ロックの表示には問題はありません。
また、LOCK TABLES を使用してテーブルにテーブル ロックを明示的に追加する場合は、関係するテーブルのすべてのロックを同時に取得する必要があります。これは、LOCK TABLES の実行後は、明示的にロックされたテーブルにしかアクセスできないためです。テーブルではありません。テーブルがロックされていない場合、エラーが発生します。同時に、読み取りロックが追加されている場合は、クエリ操作のみが実行でき、更新操作は実行できません。それ以外の場合は、エラーが報告されます。同様です。自動ロックの場合は true これはまさに、MyISAM テーブルではデッドロックが発生しません。
例を見てみましょう。
1. テーブルを作成します
CREATE TABLE test_table (
Id INT NOT NULL AUTO_INCREMENT,
Name VARCHAR(50) NOT NULL,
Message VARCHAR(80) NOT NULL,
PRIMARY KEY (Id)
);2. セッション 1 は書き込みロックを取得します
mysql> lock table test_table write; Query OK, 0 rows affected (0.01 sec)
3. セッション 2 は読み取ります。
セッションが WRITE ロックを保持している場合、他のすべてのセッションはテーブルのデータにアクセスできないことがわかっているため、2 番目のセッションが次のステートメントを実行すると、常に待機状態になります。
mysql> select * from test_table;
4. セッション 1 のロック解除
unlock table;同時挿入
MyISAM の読み取りおよび書き込み操作はシリアルですが、concurrent_insert に従って設定できます。 、MyISAM が並列クエリと挿入をサポートできるようにします。
concurrent_insert の値は次のとおりです:
- 0: 同時挿入機能は許可されません。
- 1: ホールのないテーブルの同時挿入を許可し、データ ファイルの最後に新しいデータを追加します (デフォルト)。
- 2: テーブルに穴があるかどうかに関係なく、データ ファイルの末尾への同時挿入が許可されます。
- ホールとは、テーブルの中央にある削除されていない行を指します。
InnoDBInnoDB は MyISAM とは異なり、トランザクションをサポートしていることと、トランザクションをサポートしていることの 2 つの特徴があります。行レベルのロック: レベル ロックとテーブル ロックの間には多くの違いがあります。
トランザクションの特性
- アトミック性
-
トランザクションはアトミックな操作単位であり、データに対するすべての変更は実行されます。あるいは何もしない。
一貫性 -
データは、トランザクションの開始時と完了時に一貫性を維持する必要があります。これは、データの整合性を維持するために、関連するすべてのデータ ルールをトランザクションの変更に適用する必要があることを意味します。
分離 -
データベース システムは、トランザクションが外部の同時操作の影響を受けず、「独立した」環境で実行できることを保証します。トランザクション処理は外部からは見えません。
永続性 -
トランザクション完了後のデータへの変更は永続的であり、システム障害が発生した場合でも維持できます。
#同時トランザクション処理によって引き起こされる問題
シリアル処理と比較すると、リソース使用率が向上し、より多くのユーザーをサポートできますが、同時トランザクションはまた、処理によっては、主に次のような問題が発生することがあります。
#アップデートが失われました由于每个事务都不知道其他事务的存在，就会发生丢失更新问题，也就是最后的更新覆盖了由其他事务所做的更新。
脏读
脏读又称无效数据的读出，当事务1将某一值修改后，然后事务2读取该值，后面事务1又因为一些原因撤销对该值的修改，这就导致了事务2所读取到的数据是无效的。
不可重复读
指的是一个事务在读取某些数据后，再次读取之前读过的数据，却发现读出的数据已经发生了改变。
幻读
当事务1按相同的查询条件重新读取以前查询过的数据时，却发现其他事务插入了满足这个条件的新数据。
事务隔离级别
上面说的"更新丢失"是应该完全避免的，但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁。
而脏读、不可重复读、幻读，都是数据库读一致性问题，必须由数据库提供事务隔离机制来解决。数据库实现事务隔离的方式，可分为以下两种，一种是在读取数据前加锁，阻止其他事务对数据进行修改，另一种不需要锁，通过MVCC或MCC来实现，这种技术叫做数据多版本并发控制，通过一定机制生成一个数据请求时间点的一致性数据快照，并用这个快照来提供一定级别的一致性读取。
数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上串行化进行。
InnoDB有四个事务隔离级别： READ UNCOMMITTED， READ COMMITTED， REPEATABLE READ，和 SERIALIZABLE。默认隔离级别是REPEATABLE READ。
| 隔离级别 | 脏读 | 不可重复性 | 幻读 |
|---|---|---|---|
| 读未提交 | √ | √ | √ |
| 读已提交 | × | √ | √ |
| 可重复读取 | × | × | √ |
| 可序列化（serializable） | × | × | × |
查询/更改隔离级别
显示隔离级别 show global variables like '%isolation%'; select @@transaction_isolation; 设置隔离级别 set global transaction_isolation ='read-committed'; set session transaction isolation level read uncommitted;
READ UNCOMMITTED（读未提交）
在这个隔离级别，所有事务都可以看到其他未提交事务的执行结果。这种隔离级别在实际应用中很少使用，读取未提交的数据也称为脏读。
例子
启动两个会话，并设置隔离级别为READ UNCOMMITTED。
mysql> select * from user; +-----------+---------+ | user_name | balance | +-----------+---------+ | 张三 | 100 | | 李四 | 100 | | 王五 | 80 | +-----------+---------+
| 时间 | 事务1 | 事务2 |
|---|---|---|
| T1 | begin; | begin; |
| T2 | select * from user where user_name="张三"; 此时张三余额100 |
|
| T3 | select * from user where user_name="张三"; 此时张三余额100 |
|
| T4 | update user set balance =80 where user_name ="张三"; | |
| T4 | select * from user where user_name="张三"; 此时张三余额80 |
|
| T5 | commit | commit |
可以看到，在T4时刻，事务1没有提交，但是事务2可以看到被事务1锁更改的数据。
READ COMMITTED （读已提交）
这是大多数数据库系统的默认隔离级别，但不是MySQL的默认级别，他避免了脏读现象，因为在任何未提交的事务前，对任何其他事务都是不可见的，也就是其他事务看不到未提交的数据，允许不可重复读。
例子
将两个会话中隔离级别设置为读已提交 set session transaction isolation level read committed;
| 时间 | 事务1 | 事务2 |
|---|---|---|
| T1 | begin; | begin; |
| T2 | select * from user where user_name="张三"; 此时张三余额100 |
|
| T3 | select * from user where user_name="张三"; 此时张三余额100 |
|
| T4 | update user set balance =80 where user_name ="张三"; | |
| T4 | select * from user where user_name="张三"; 此时张三余额100 |
|
| T5 | commit | |
| T5 | select * from user where user_name="张三"; 此时张三余额80 |
可以看到，在T4时刻，事务1没有提交，但是事务2读取到的数据还是100,当事务1提交后，事务2才可以看到。
REPEATABLE READ （可重复读）
这是 MySQL 的默认事务隔离级别，它确保同一事务读取数据时，将看到相同的数据行，但是会出现幻读，当事务1按条件进行查询后，另一个事务在该范围内插入一个新数据，那么事务1再次读取时，就会读到这个新数据。InnoDB 和 Falcon 存储引擎通过 mvcc（多版本并发控制）机制解决了这个问题。
例子
设置两个会话隔离级别为可重复读 set session transaction isolation level repeatable read;
| 时间 | 事务1 | 事务2 |
|---|---|---|
| T1 | begin; | begin; |
| T2 | update user set balance =80 where user_name ="张三"; | |
| T3 | commit; | |
| T4 | select * from user where user_name="张三"; 张三余额为100 |
可以看到，在T3时刻，事务1已经提交更改，但是在T4时刻的事务2中，还是读取到了原来的数据，但是如果事务2在原来的基础上再减10元，那么最终余额是90还是70呢？,答案是70。.
mysql> update user set balance=balance-10 where user_name="张三"; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from user where user_name="张三"; +-----------+---------+ | user_name | balance | +-----------+---------+ | 张三 | 70 | +-----------+---------+ 1 row in set (0.00 sec)
SERIALIZABLE （序列化）
他是最高的隔离级别，InnoDB将所有普通SELECT语句隐式转换为SELECT ... LOCK IN SHARE MODE，所有事务按照顺序依次执行，因此，脏读、不可重复读、幻读都不会出现。但是，由于事务是串行执行，所以效率会大大下降，
例子
设置隔离级别为序列化 set session transaction isolation level serializable;
| 时间 | 事务1 | 事务2 |
|---|---|---|
| T1 | begin; | begin; |
| T2 | select * from user where user_name="张三"; | |
| T3 | update user set balance =80 where user_name ="张三"; |
这一次，有趣的是，事务2在T3时刻更新被阻止了,原因是在serializable隔离级别下，MySQL隐式地将所有普通SELECT查询转换为SELECT FOR SHARE， 持有SELECT FOR SHARE锁的事务只允许其他事务对SELECT行进行处理，而不允许其他事务UPDATE或DELETE它们。
所以有了这个锁定机制，我们之前看到的不一致数据场景就不再可能了。
但是，这个锁具有超时时间，在等待一会后，如果其他事务在这段时间内没有提交或回滚释放锁，将抛出锁等待超时错误，如下所示：
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
InnoDB行锁
InnoDB 的行级锁也分为共享锁和排他锁两种。
共享锁允许持有锁的事务读取行。
独占锁允许持有锁事务的更新或删除行。
为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁，这两种意向锁都是表锁。
- 意向共享锁 事务想要获得一张表中某几行的共享锁。
- 意向排他锁 事务想要获得一张表中某几行的排他锁。
InnoDB 行锁是通过锁定索引上的索引条目来实现的，因此，InnoDB 只有在通过索引条件检索到数据时才使用行级锁；否则，InnoDB 将使用表锁。
我们可以显示的加锁，但对于update、delete、insert语句，InnoDB会自动给涉及的数据集加排他锁，对于普通的 select 语句，InnoDB 不会加任何锁，下面是显示的加锁方式：
- 共享锁：
SELECT FROM table_name WHERE … LOCK IN SHARE MODE - 排他锁：
SELECT * FROM table_name WHERE … FOR UPDATE
Next-Key锁
当我们使用范围条件而不是相等条件检索数据，并请求其共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于在条件范围内但并不存在的记录，叫做间隙(GAP), InnoDB也会对这个"间隙"加锁，这种锁机制就是所谓的Next-Key锁。
举例来说，假如user表中只有101条记录，其user_id的值分别是1.2. ..100. 101，当查找大于100的user_id时，使用下面SQL。
select.* from emp where user_id > 100 for update;
这就是一个范围条件的查询， InnoDB不仅会对user_id为101的记录加锁，也会对user_id大于101的"间隙"加锁，虽然这些记录并不存在。
InnoDB使用Next-Key锁的目的，一方面是为了防止幻读，另一方面， 是为了满足恢复和复制的需要。
更多编程相关知识，请访问：编程视频！！
以上がMySQL のトランザクションとロックについて詳しく学ぶの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。