Architecture design for a large number of db records upgrade

Question

Honestly struggled a bit with the title of the question. Here is the deal. I have a specific set of db operations on various tables across an db. In a simpler sense it's kind of a migration, that I want to roll out in production i.e. among live users.

So, the requirement is how I can do this in transaction without actually using transaction? There are a few assumptions and may be incorrect, please feel free to help me figure it out.

1. I think we can't use transaction in db level cause this process may take too long, have around 1k independent operation that may require to validate the existing state of a record and updating it.
2. Then again if for any reason, something thing goes wrong in between, I don't want to end up with partially executed migrations. In another note, this feels like an software upgrade, when they put in new version changes and in case of an issue rolls back. If I strictly based on that, I have to get the current records that are going to be affected from migration, keep them for restoring purpose, then apply the migration. If anything fails, re-apply with original records (kind of reverting) with the same risk.

With a server (node.js) and live user involved, it sounds more risky and difficult to control the whole thing. Is there any idea on level of architecture that can help? Thanks in advance!

Updates

Are you expecting that a transaction will lock a table for several seconds or minutes? Can it be broken into small batches?

When you say it will "take too long," does that mean it has to complete within a certain timeframe for business requirements, or you're concerned it will block table updates for too long

Yes, it can take a significant time and indeed, we can divide the process into small batches. There are 2 things that we need to be ensure of. The whole operation (even broken into batches) should be transaction in nature. I am not really concerned about the time it's going to take, instead the tables/rows will stay locked for that time, in case anyone is trying out something.

Answer 1

You should test performing all the changes in a single transaction. If you can make it work, this is by far the best option.

To minimize the transaction runtime, get all the data loaded into SQL Server before you start the transaction, using staging tables or temporary tables (or JSON, or XML or Table-Valued Parameters).

And consider turning on the READ COMMITTED SNAPSHOT database option to enable the application to continue to read tables affected by uncommitted transactions.

But this:

1k independent operation that may require to validate the existing state of a record and updating it.

seems like something that can be performed in a second or two, at most. If running entirely on the server-side.

If you determine that the transaction has too great an impact on the application then you have to pick an option that has a some significant downsides, such as:

1) Perform the changes during application downtime

2) Copy the affected tables and keep the copies in sync with triggers or replication. Apply the changes to the copies, and then perform an ALTER TABLE .. SWITCH or table renames in a transaction to replace the production tables with the copies.

3) Write "undo" scripts to back-out incomplete changes on failure.