[RndTbl] mysql update delays when no rows match, when backup running

[Trevor Cordes]

Question:
Mysql (MariaDB actually, fairly recent version) issue.  Innodb.

Full db backup (table by table) runs each night 5am.

There's a massive (few GB) table that takes a couple of mins to backup.

When this table is getting backed-up, updates to the table pause until the
backup is done.  Selects don't seem to pause(?).  However, even updates
that will match zero rows seem to pause!  Shouldn't the engine be doing a
select (within a transaction internally) and then quitting the query?  It
seems like it's wanting to get some sort of lock before doing anything for
the update, even the select step.

Maybe this makes sense?  I suppose if I was doing the locking with
transactions in my code (I'm not), I would do a select FOR UPDATE and then
update if I had to?  Would the "select for update" also pause on the
select step?

I was thinking change my db library to make all update calls a
select-for-update then the update only if needed, but if my hunch is
correct, it won't fix anything, and slow things down a touch because I'm
doing the internal work myself?

And I can't blanket replace all updates with select/update without a "for
update" because there could be race conditions between the select/update?

Maybe the correct approach is on an instance-by-instance basis where I
know I don't care at all about races (like this case) I could replace the
update with select (no "for update") plus an update.

If I make that an option in my db library, and use it liberally, will I be
slowing down (much) the common case of updates running outside of
backup time, because then both I and the engine are doing a select?
There's no way I'm going to change it to "if backup running, do select
first, if not do update by itself"  :-)   At least, I hope I'm not going
to do that!!

It's really only 1 table of mine that is multi-GB and has a long backup
time, otherwise this would be a non-issue.  I was kind of hoping inno took
care of all this stuff for me...