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...