INSERT only if row with specific conditions does not exist – with some concerns about concurrency and atomicity

  Kiến thức lập trình

I need to insert a row in a table only if a row with a specific set of conditions is not already found in that table.

Given that this problem is (extremely) common, I searched through answers and came up with the following page:

How can I do ‘insert if not exists’ in MySQL?

I don’t want to use INSERT IGNORE or other MariaDB/MySQL-specific statements if possible, so I especially looked at the second (/a/3025332/19358731) and 7th (/a/9590881/19358731) answers.

Then, I remembered that single SQL statements are not atomic:

Is a single SQL Server statement atomic and consistent?

This makes me believe that the solutions provided in my first links are indeed wrong, as will potentially have many concurrent queries – I’m thinking that the SELECT subquery within each of the INSERT statement in these answers will not execute atomically within their respective parent INSERT statement.

I have two complementary questions:

  • is the solution (INSERT INTO table SELECT ... NOT EXISTS (SELECT ...)) provided in my first link indeed right? Are the queries indeed safe (atomic)?
  • in addition to my first question, can I also use the following PHP code to fulfill my needs (performance will just be lower I imagine):
$statement = $pdo->prepare("SELECT ... FROM table WHERE conditions LIMIT 1");
if ($row = $statement->fetch()) {
  $statement2 = $pdo->prepare("INSERT INTO table ...");

Theme wordpress giá rẻ Theme wordpress giá rẻ Thiết kế website