Unfortunately, you're describing normal, documented, InnoDB behaviour that's at least partially "for historical reasons". Backups take a lock on tables by default, and because the READ LOCAL lock mysqldump uses is a MyISAM-only thing, the lock gets promoted to a write lock automatically in InnoDB. MySQL (et al.) UPDATE statements take a write lock at the *beginning* of query execution - NOT at the moment they want to write something. Ditto for DELETEs, AFAIK. Normally not a problem for transactional behaviour, but inconvenient here.
There's one glimmer of hope, which I'll get to in a sec...
mysqldump(1) documents that --opt includes --lock-tables, and also that --opt is turned on by default, so locking is the default.
You can use --skip-lock-tables, at the risk of potentially getting an even more inconsistent (non-isolated) view of the data during backups.
Note that you are NOT guaranteed full transaction isolation / consistency during a mysqldump anyway, as --lock-tables says:
[...] The tables are locked with READ LOCAL to allow concurrent inserts in the case of MyISAM tables. For transactional tables such as InnoDB, --single-transaction is a much better option than --lock-tables because it does not need to lock the tables at all. Because --lock-tables locks tables for each database separately, this option does not guarantee that the tables in the dump file are logically consistent between databases. Tables in different databases may be dumped in completely different states.
I would try --single-transaction and test; I don't have any convenient way of testing it right now.
If you absolutely need 100% *perfect* self-consistent backups while the underlying tables are still being written to, you need a different RDBMS. For example, PostgreSQL does transaction-isolated backups correctly, out of the box. I think --single-transaction should get you most of the way there without switching products.
-Adam
-----Original Message----- From: Roundtable roundtable-bounces@muug.ca On Behalf Of Trevor Cordes Sent: Friday, February 3, 2023 9:37 PM To: MUUG RndTbl roundtable@muug.ca Subject: [RndTbl] mysql update delays when no rows match, when backup running
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... _______________________________________________ Roundtable mailing list Roundtable@muug.ca https://muug.ca/mailman/listinfo/roundtable