MySQL concurrent INSERTs

concurrencylockingMySQL

I have a MySQL database with InnoDB tables.
There are different client processes making SELECT (to check the existence of a value) and INSERT or UPDATE (depending on the result of the select) statements.
What I fear is a possible concurrent access to data causing only INSERTs and no UPDATEs.
Is LOCK Table WRITE the only solution?

Best Answer

It looks like you need SELECT ... FOR UPDATE

Pseudo code:

START TRANSACTION;
SELECT some_column INTO @value FROM table_name WHERE ... FOR UPDATE;
IF @value = 'some value' THEN
  INSERT INTO table_name VALUES(...);
END IF;
INSERT INTO referenced_table VALUES(...);
COMMIT;
Related Question