Is this "compare and swap" statement always atomic regardless of engine (e.g. InnoDB or MyISAM)? :
UPDATE tbl_name SET locked=1 WHERE id=ID AND locked <> 1;
I ask this because I intend to use this statement to do pseudo row-level locking that is compatible with both transactional and non-transactional database tables.
This is the method that is recommended for MyISAM, but I am uncertain as to whether this works for InnoDB since the documentation suggests using transactions instead.