In my current application we frequently need to migrate data from one release to the next. Most of the time these migrations are straight forward, an alter table here, a new table there, etc. However, every now and again we need to run a migrate script that takes the existing data and updates it e.g.
Update users set status='active' where is_active = 1
We have some very large tables and so this could update a lot of rows. The main problem with running the above sql on a table containing 100,000k rows is that it is an all or nothing approach, i.e. the sql either updates every row or no rows. This creates a very long running transaction and should it fail updating the 99,999th row the rollback will be take even longer. Doing this in production can take your site out of action for a long time. Transactions of this kind frequently do fail when they run out of log space as they swamp the transaction log with updates.
The solution is to split the single large transaction into many small ones (something Jim Gray refers to as a mini-batch). To achieve this we need to keep track of the last row that we have processed for the current batch. We record this in a database table e.g.
Create table batchcontext (last_id_done INTEGER);
Initially we set last_id_done to 0. Now the update logic needs to be as follows, note ‘:step_size’ should be something like 100.
:last_id_done = 0
While (:last_id_done > :max user id);
Begin Work;
Select last_id_done from batchcontext into :last_id_done;
Update users set status='active' where is_active = 1 and id between :last_id_done +1 and :last_id_done + :step_size;
Update batchcontext set last_id_done = :last_id_done + :step_size;
Commit Work;
End While;
The important point is that the single unit of work contains both the update logic to the users table and the update to the batchcontext table. That way if the application fails at any point it can be restarted and picks up where it left off.