1

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.

  • I think you're going to need to be more specific about what problems you are anticipating. 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. Please read your question carefully as if you know nothing about the situation to put yourself in another reader's position. – Tony Hinkle Mar 9 at 22:20
  • @TonyHinkle thanks for your comment. Really did not think it through. I have updated the question. Hope it helps! – Gautam Kumar Samal Mar 11 at 7:13
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.

  • thanks. This is what I needed to hear. I am trying out going in a single transaction for now. The other option of maintaining backups, rolling back by applications seems dangerous and prone to a lot of inconsistencies. – Gautam Kumar Samal Mar 12 at 8:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.