CODEWITHSUNDEEP
phpmysqlpdoinnodb
Olhovsky
Olhovsky

Reputation: 5549

Lock SQL table, then perform transaction, for inserting conditional on select value

I need to accomplish the following:

I need to insert two rows into the same table, and either both are inserted, or neither (atomically insert two rows).

I do this using a transaction on an InnoDB table like so:

$db->beginTransaction();

# Using PDO prepared statments, execute the following queries:
INSERT INTO t1 set uid=42, foo=1
INSERT INTO t1 set uid=42, foo=2

$db->commit();

However I also only wish to insert these rows if there is no row in the table where a column has value '42'.

So I do:

$stmt = $db->prepare("SELECT id FROM t1 WHERE uid != ?");
$stmt->execute(array(42));
if($stmt->fetchColumn() < 0){
    # There is no row with uid=42, so
    # perform insertions here as per above.
    INSERT INTO t1 set uid=42, foo=1
    INSERT INTO t1 set uid=42, foo=2
}

However there is a race condition here, since a row with uid=42 could be inserted right after checking for that row, and right before inserting new rows.

How should I solve this?

Can I lock the table and then perform the InnoDB transaction inside of the table lock?

Can I do the select inside of the transaction to check for existing rows with uid=42? Is that race-condition-free?

Upvotes: 4

Views: 482

Answers (1)

Sarel
Sarel

Reputation: 1240

You could use a semaphore type of architecture. Example, create a table called "semaphore" or whatever you want to call it. Let's say that table has only one field in it, and that field is unique, and is called "sem". Now, run "INSERT INTO semaphore SET sem = 42";

Ok, that INSERT statement is atomic, and will mean that the moment after that when somebody else tries to inset 42, they get given an error stating duplicate key.

Then, do the inserts in the original table. Do all this inside a transaction. In SQL it would look like this:

BEGIN TRANSACTION;
INSERT INTO semaphore SET sem = 42;
INSERT INTO t1 set uid=42, foo=1;
INSERT INTO t1 set uid=42, foo=2;
COMMIT;
DELETE FROM semaphore WHERE sem = 42;

The reason you do the delete afterward is two fold:

  1. I guess you don't need to delete it, but let's go for clean data shall we ;)
  2. The reason you delete after the COMMIT is you want to keep the blocking-lock on it until the transaction is complete.

Side-note: Semaphores are usually used when an auto-increment field won't do. Then you use a semaphore table with only one record in it to serialize inserts and block the primary key.

Hope it helps :)

Upvotes: 4

Related Questions