MySQL事务和并发插入 [英] MySQL transactions and concurrent inserts

问题描述

我已经有人编写了这个脚本，我仔细检查了一下，发现不确定的东西是安全的:

I've got this script made by someone and I looked over it and found something I'm not sure is safe:

在一个事务中，有2个连续的插入，分别位于2个不同的表中，均具有自动递增的主键.它们应该是相同的(从第一个插入生成的主键=从第二个插入生成的主键).

Inside a transaction there are 2 consecutive inserts, in 2 different tables, both with auto incremented primary keys. They are supposed to be the same (the primary key generated from the first insert = the primary key generated from the second insert).

不要问我为什么，这只是脚本的制作方式.

Don't ask me why, it's just how the script was made.

我是交易新手，所以我不太清楚这里是否存在并发问题.我正在考虑另一个线程同时运行并最终生成类似于以下内容的密钥的可能性:

I'm new to transactions and I can't quite figure out if there is a concurrency issue here. I am thinking about the possibility of another thread running in the same time and ending up with keys being generated something like:

Thread #1:    Table_A ID: 3                                        Table_B ID: 4
Thread #2:                      Table_A ID: 4     Table_B ID: 3

我敢肯定(今天我第一次才准备好与交易相关的文档)交易不能抵御这种情况，但我只是想确保自己做对了.

I'm pretty sure (I've only ready transaction-related documentation today for the first time) that a transaction would not protect against this, but I just want to be sure I got it right.

谢谢！

推荐答案

您需要将两个连接都置于可序列化的事务隔离级别中，以避免出现您要描述的情况，方法是在每个连接上设置tx_isolation，方法是:

You need to put both connections in serializable transaction isolation level in order to avoid the scenario you are describing, either by setting the tx_isolation on each connection with:

SET @@tx_isolation = SERIALIZABLE;

或

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

或通过以下方式设置全局隔离级别:

or by setting the global isolation level with:

SET @@global.tx_isolation = SERIALIZABLE;

或

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

，任何随后打开的连接都会继承该

.在该级别上，如果另一个事务已经在进行中，则事务将阻止任何查询，即.事务已经在相同的表上发出了查询(读或写).

which is inherited by any subsequently opened connection. At that level, transactions will block on any queries if another transaction is already in progress, ie. a transaction has already emitted a query (read or write) on the same tables.

有关更多信息，请参见 mysql文档详细信息.

See the mysql documentation for further details.

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