If the transaction begins later, the state of the db before vs after the computations might be different (e.g. because another process added/removed some data), so you might end up with inconsistencies (a source table that doesn't correspond to the target table) On the other hand, a transaction that lasts 2-3 hours will effectively block other operations and that's bad

I would see if you can live with the fact that the data is X minutes/hours stale, as long as you can make it a consistent calculation on a "snapshot" of what the data looked like at one point in time. So I would create a new table, and in a transaction, 1) put all of the source data in there, and 2) record the current datetime. After working on that data in memory, in a separate transaction, I'd replace the data in the target table. You'd also want to have a way to know what datetime the data is valid for (possibly in a "jobs" type table).