Instead, what MySQL does is the following:
- call handler::write_row to attempt an insertion, if it succeeds, we are done
- if handler::write_row returns an error indicating a duplicate key, outside of the handler, apply the necessary update to the row
- call handler::update_row to apply the update
So, the moral of the story is this. In MySQL, “insert … on duplicate key update” is slower than “replace into”.
REPLACE INTO might be faster but the semantics are:
INSERT; if failure then DELETE; INSERT; fi
Option::set() is largely called to update the value for an existing setting, we should try:
UPDATE; if affected_rows === 0 then INSERT; fi
It's not an atomic operation but overall, this approach strikes a good balance between performance and ACID.
A side-effect of this PR is that the SQL is database agnostic.