For simplicity, say there are two tables A and B, with unique indexes on non-key INT columns COLUMN_A and COLUMN_B respectively. I would like to do something like the following pseudo-SQL. (Note that this would be in a stored procedure but, according to https://mariadb.com/kb/en/lock-tables/, that's not allowed.)

IF COLUMN_A != x for all rows of A
   Single query to insert a row into B with COLUMN_B = x (if not exist)

The problem is that other parts of the code could read A and B, see that x does not exist in either, and try to insert x into A between the IF statement and insertion query. The race condition seems to necessitate a read/write lock on A. I don't think that InnoDB's internal locking would prevent this from happening (i.e. any locks used during the IF statement would be released before the execution of the insertion).

Crucially, COLUMN_A and COLUMN_B are formally unrelated so there doesn't appear to be a straightforward way to enforce a uniqueness constraint between them (given that a view involving both A and B probably wouldn't be updatable). (I would be fine creating some sort of "relationship" between them as long as they remain in separate tables but I'm not sure if there is anything that would do this.) Is it necessary to have a table lock on A in this case?

Something like this would seem like a better solution: Can I use row locks on rows that have not been created yet? But this question is about Postgres and the features don't appear to be available in MySQL.

Thank you.

CodePudding user response：

Put the condition into the INSERT statement rather than using the IF statement.

INSERT INTO B (col1, col2, col3, ...)
SELECT val1, val2, val3, ...
FROM DUAL
WHERE NOT EXISTS (
    SELECT *
    FROM A
    WHERE column_a = 'x'
)