与mysql_last_id()的竞争条件 [英] Race Conditions with mysql_last_id()

问题描述

我已经花了大部分时间试图找到答案，但是似乎没有一个答案.我需要一个函数，该函数在表中创建一行，从插入的行中检索自动增量，然后使用该值在第二个表中插入另一行.在功能上类似于以下php:

I've spent the better part of the day trying to find an answer to this, but there just doesn't seem to be one out there. I have need of function which creates a row in a table, retrieves the auto-increment from the inserted row, and then uses that value to insert another row in a second table. Something functionally similar to the following php:

$mysql_query("INSERT INTO table1 SET columns='values'");
$id = mysql_insert_id();
$mysql_query("INSERT INTO table2 SET id='$id', columns='values'");

我遇到的问题是在我的应用程序中，所有类之间共享一个MySQL连接，因此我担心这可能会导致竞争状况，其中另一个类在运行第一个之间插入一行和上面的第二行.

The problem I'm running into is that in my application, a single MySQL connection is shared between all the classes, so I'm worried that this might cause a race condition in which another class inserts a row between running the 1st and 2nd line of the above.

我能想到的最简单的解决方案是使所有使用mysql_insert_id()的函数打开一个新的，唯一的连接，但是这似乎有很多不必要的开销.我玩过的其他答案包括插入一个唯一值(可能是用UUID()创建的)并使用该值代替自动递增值，或者可能将第一个插入和对LAST_INSERT_ID()的调用放入存储的函数中(尽管我不确定这是否可以解决可能的比赛条件.

The simplest solution I can come up with is having any functions that use mysql_insert_id() open a new, unique, connection, but that seems like a huge amount of unnecessary overhead. Other answers I've played around with include inserting a unique value (possibly created with UUID()) and using that instead of an auto-increment value, or possibly putting the first insert and a call to LAST_INSERT_ID() in a stored function (though I'm not sure if that would resolve the possible race condition or not).

我也一直在研究交易是否有帮助，但是关于mysql_insert_id()与交易如何精确交互的文献很少.尽我所知，它们在这里可能无济于事，因为另一个与交易锁没有冲突的INSERT仍将能够执行并可能导致相同的竞争条件.

I've also been looking into transactions to see if they may help, but there is little documentation to be found regarding how exactly mysql_insert_id() interacts with them. Best I can tell, they probably won't help here, as another INSERT that didn't conflict with any of the transaction's locks would still be able to execute and possibly cause the same race condition.

因此，假设我没有上述任何错误(如果我没有错，请更正我)，确保100％的第二个INSERT使用正确值的最佳方法是什么?

So, assuming I'm not in error with any of the above (and please correct me if I am), what is the best way to ensure 100% that the second INSERT uses the proper value?

推荐答案

php脚本的执行是线性的，而不是多线程的.
因此，当一个对象与另一个对象同时执行时，就不可能有条件

php script's execution is linear, not multi-threaded.
so, that's impossible to have a condition when one object being executed at the same time with another

唯一可能的问题是持久连接.但是似乎相同的连接无论如何不能被2个单独的调用使用.即使它是持久性的，也已经在使用中-另一个脚本无法使用它.因此，也不会有问题.

The only possible issue can be with persistent connection. But it seems that the same connection cannot be used by 2 separate calls anyway. Even if it's persistent one, if it's already in use - it cannot be usedby another script. Thus, there will be no issues either.

这篇关于与mysql_last_id()的竞争条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！