mysql中的并行事务 [英] Parallel Transactions in mysql

问题描述

我的数据库包含名为sample_table的表，该表由名为user_first_name的一列组成，并且该表为空

My database contains table named sample_table consists of one column named as user_first_name and the table was empty

假设我有2笔这样的交易

Suppose I have 2 transactions like this

START TRANSACTION;
INSERT INTO sample_table VALUES("TEJA");
INSERT INTO sample_table VALUES("TEJA");
INSERT INTO sample_table VALUES("TEJA");
INSERT INTO sample_table VALUES("TEJA");
INSERT INTO sample_table VALUES("TEJA");
INSERT INTO sample_table VALUES("TEJA");
INSERT INTO sample_table VALUES("TEJA");
//......... till 10000 times
COMMIT

START TRANSACTION;
SELECT * FROM sample_table;
SELECT * FROM sample_table;
SELECT * FROM sample_table;
SELECT * FROM sample_table;
SELECT * FROM sample_table;
SELECT * FROM sample_table;
//........ till 10000 times 
COMMIT

我以这种方式并行运行这两个事务，首先是transaction1开始，然后是transaction1在运行时的transaction2.我希望结果将包含一些名称为TEJA的行.但是我得到了一个空洞的结果. 请帮我解释为什么结果为空?

I run these two transactions in parallel in such a way, first the transaction1 started and then transaction2 when transaction1 was running. I expected that the result will have some rows with name TEJA. But I am getting an empty result. Please help me to explain why the result is empty?

推荐答案

这完全取决于您设置的ISOLATION LEVEL.

It all depends on the ISOLATION LEVEL you have set.

您可以查看此查询中设置了哪一个:

You can see which one you have set with this query:

select @@global.tx_isolation, @@session.tx_isolation;

在解释不同的隔离级别之前，让我解释一下他们试图避免哪些问题:

Before explaining the different isolation levels, let me explain which problems they are trying to avoid:

• 脏读取:另一个事务读取尚未完成的事务的数据.

• The dirty read: Data of a not yet finished transaction are read by another transaction.

丢失的更新:两个事务并行修改表中的一个条目.当两个事务都完成时，仅应用一个修改.

The lost update: Two transactions modify an entry in a table in parallel. When both transactions finished, only one modification is applied.

不可重复读取:重复读取会导致不同的结果.

The non-repeatable read: Reoccuring reads lead to different results.

幻读:在事务期间，另一笔事务添加，删除或修改表条目.

The phantom read: During a transaction another transaction adds, removes or modifies table entries.

现在使用不同的隔离级别.

Now for the different isolation levels.

读未提交:
使用此隔离级别，读操作将忽略任何类型的锁，因此可能会发生上述任何问题.

Read Uncommitted:
With this isolation level read operations ignore any kind of locks, therefore any of the above mentioned problems can occur.

已提交阅读:
此隔离级别为应修改的对象的整个事务设置了写锁定.读锁仅在读取数据时设置.因此，可能会发生不可重复的读取和幻像读取.

Read Committed:
This isolation level sets a write lock for the whole transaction on objects that should be modified. Read locks are only set when reading data. Therefore non-repeatable read and phantom read can occur.

可重复阅读:
通过这种隔离级别，可以确保在参数相同时，重复发生的读取操作始终会产生相同的结果.在事务的总持续时间内，为读写操作设置了锁定.因此，只能进行幻像读取.

Repeatable Read:
With this isolation level it is ensured, that reoccuring read operations always yield the same result when the parameters are the same. Locks are set for read and write operations for the total duration of the transaction. Therefore only phantom reads can occur.

可序列化:
最高的隔离级别确保并行运行的事务的结果与事务将一个接一个地运行的结果相同.大多数数据库并没有真正一个接一个地运行这些事务，这会导致性能损失过多.因此，可能发生一项事务中止的情况.例如，MySQL通过MVCC(多版本并发控制)实现了这一点.如果您想了解更多，请使用Google.这个答案太多了.

Serializable:
The highest isolation level guarantees, that the result of parallel running transactions is the same as if the transactions would be running one after another. Most databases do not really run those transactions one after another, it would be too much of a performance loss. Therefore it can happen, that one transaction gets aborted. MySQL for example realizes this with MVCC (Multi-Version Concurrency Control). Google it, if you want to know more. It's too much for this answer.

此表中的所有内容也对此进行了解释:

All in all this table explains it as well:

                 | Lost updates | Dirty Read | Non-Repeatable Read | Phantom Read
---------------------------------------------------------------------------------
Read Uncommitted | possible     | possible   | possible            | possible
Read Committed   | impossible   | impossible | possible            | possible
Repeatable Read  | impossible   | impossible | impossible          | possible
Serializable     | impossible   | impossible | impossible          | impossible

• 在此手动输入中是一些更多信息关于它以及如何设置隔离级别.
• In this manual entry is some more info about it and how to set the isolation level.

这篇关于mysql中的并行事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！