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!

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.