Update a record or insert if it doesn't exists, with locking

Question

I would like to update a record if exists. Thats the easiest part, but that record may not exist, so let's create it first. Pseudocode:

UPDATE x SET y=1 WHERE id=5

if the result of it is 0, then...

a, LOCK TABLE x
b, INSERT INTO...
c, UNLOCK TABLE x

But I cant find a concrete tutorial. I suppose the step A must be "true" if locking was successfull, and "false" if already locked. But its always true, in case of simultenaously running.

(Ps: I know there are replace into and those kind of operations, but this time this is just what I need)

Answer 1

Ideally you should use a transaction or INSERT INTO ... ON DUPLICAGTE UPDATE ....

But if you really have to use locking, you have to put both UPDATE and INSERT within the locked region.

LOCK TABLE x;
UPDATE x SET y = 1 WHERE id = 5;
IF FOUND_ROWS() = 0 
THEN
    INSERT INTO x (id, y) VALUES (5, 1);
END IF
UNLOCK TABLE x;