Changing many rows.
WHAT GOES WRONG WITH A SINGLE LARGE STATEMENT
Locks held for the duration Enormous undo generated Replication delayed while it applies The transaction log filling
WHY REPLICATION MATTERS HERE
A statement taking minutes on the primary applies serially on the replica.
WHAT TO DO INSTEAD
Process in batches.
WHAT SIZE
Small enough to complete quickly; thousands rather than millions.
WHAT TO ADD BETWEEN BATCHES
A brief pause.
WHY
It lets replication and other work catch up.
HOW TO BATCH RELIABLY
Order by the primary key and proceed in ranges.
WHY NOT USE OFFSETS
They become slower as you progress.
WHAT TO DO BEFORE ANY BULK CHANGE
Run the equivalent select and check the count.
WHY
It confirms the condition matches what you intend.
WHAT TO ALSO DO
Take a backup, or at least copy the affected rows.
WHAT TO AVOID
Running it during peak hours Wrapping the whole thing in one transaction
WHAT TO MONITOR WHILE IT RUNS
Replication lag Lock waits Disk space
WHAT TO BUILD IN
The ability to stop and resume.
