MySQL Atomic UPDATE in InnoDB vs MyISAM

Question

Is this “compare and replace” operator always an atom independent of the engine (for example, InnoDB or MyISAM)?

UPDATE tbl_name SET locked=1 WHERE id=ID AND locked <> 1; 

I ask for this because I intend to use this statement to lock at the row level, compatible with both transactional and transactional database tables.

This is the recommended method for MyISAM , but I'm not sure if this works for InnoDB, as the documentation suggests using transactions instead.

Answer 1

Yes. In InnoDB, the row will be locked (you have a unique index on id, the update blocks all the rows that it should check), it is updated and blocked. If you are not involved in an explicit transaction / automatic commit, each statement runs in its own transaction, but the transaction and locks are still in progress.