Sep 13, 2018

UPDATE before first checking if necessary to reduce deadlock in MySQL

If the conditions are not met, update will do nothing. An empty update is just as fast as an empty select.

A potential side affect of always calling the UPDATE is the locking that needs to be put to insure that no other connection modifies these rows.

  • If the table is MyISAM - a lock will be places on the he entire table during the search.
  • If the table is InnoDB, locks will be places on the indexes/gaps.

From the Docs:

A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row.