Updates and Deletes in MemSQL lock the row they are updating, and only that row. If the update is a singleton update (or upsert), then this error means that two processes are trying to update the same row. This itself isn't the end of the world, we have a setting - lock_wait_timeout - which defaults to 60s. If a query is waiting on a row for more than 60s it fails, which should be plenty of time. So the first query updates, then the second. If you're seeing this error there is serious contention. This happens most often when updating on a field that is not indexed, since MemSQL takes a very short lock on every row while scanning through the entire table to see if it matches the where clause on the update.
If you're experiencing contention with singleton transactions the solution is to implement indexes.
The following query is giving us the lock wait timeout exceeded error:
DELETE FROM bucket WHERE meta_uid_id = ?
Add an index on
If you're experiencing contention when multistatement transactions are involved you can turn off
show variables like '%multistatement_transactions%';
Shows whether multistatement transactions are on, and
SET GLOBAL multistatement_transactions = off;
turns it off.