Lock the SQL table, then execute the transaction to insert the conditional value into the select value

Question

I need to do the following:

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

I do this using a transaction in an InnoDB table as follows:

$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 want to insert these rows if there is no row in the table where the column has a value of "42".

So I:

 $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, since a line with uid = 42 can be inserted immediately after checking this line and right before inserting new lines.

How do i solve this?

Is it possible to lock a table and then execute an InnoDB transaction inside a table lock?

Is it possible to select inside a transaction to check existing rows using uid = 42? Is it race free?

Answer 1

You can use a semaphore architecture type. For example, create a table called "semaphore" or whatever you want to name. Let's say that there is only one field in the table, and this field is unique and is called "sem". Now run "INSERT INTO semaphore SET sem = 42";

It is good that the INSERT operator is atomic and will mean that at the moment when someone else is trying to insert 42, they will be given an error indicating the duplicate key.

Then do inserts in the source table. Do it all inside the transaction. In SQL, it will 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 delete it later is twice:

• I think you do not need to delete it, but release it for clean data, we will;)
• The reason you delete after COMMIT, you want to lock the lock until the transaction is completed.

Side note. Semaphores are usually used when the auto-increment field will not work. Then you use a single-entry semaphore table to serialize the inserts and lock the primary key.

Hope this helps :)