2

My java class will be invoked with a certain number of records(consider 1000) every time. Each time when it got invoked , it needs to insert all the 1000 records into table. Now consider it got invoked three times(means it inserted 3000 records into db in three transactions). Now if any error occurred during third transaction insertion, it needs to rollback all the transactions insertions. Is there any possibility make all three transactions into single transaction(but code receives 1000 records periodically). Please guide me to achieve this scenario.

Prudhvi B
  • 144
  • 2
  • 13
  • If it needs to roll back all three transactions, then there isn't three transactions. There's one transaction. – Kayaman Aug 26 '16 at 12:49
  • "Rollback" is a DB functionality inside a transaction. Once you have committed the transaction, there is no automatic way to do rollbacks. It will be up to you to design your program so it can offer that functionality (and usually that is far from trivial). – SJuan76 Aug 26 '16 at 12:50
  • @Kayaman Is it possible to commit all 3000 records at a time? note: I will be receiving 1000 records periodically. – Prudhvi B Aug 26 '16 at 12:59
  • @PrudhviB Could be. It depends on whether your application can deal with a long transaction like that. – Kayaman Aug 26 '16 at 13:03
  • Maybe instead you should consider a design where you use a staging table for received data, and after the third set of data is successfully received, you move it to the permanent location as the 'commit' (and delete from the staging table when something went wrong as the 'rollback'). – Mark Rotteveel Aug 26 '16 at 15:06

1 Answers1

0

You would need a way to keep track of all the insertions during each transaction and then have a function delete them if there is a rollback called. There is no way to easily rollback a transaction after it is committed.

SJR59
  • 73
  • 3
  • 14
  • Is there any possibility to make all three transactions into single transaction? (but i will receive 1000 records periodically, not at a time). so that I can commit all the insertions at a time. – Prudhvi B Aug 26 '16 at 13:06
  • Possibly, depends on how your code is set up. Sounds like you wouldn't commit your transaction until the very end of the program which doesn't seem like a good idea, but if you wanna update your question to add ur code then I could have a better idea. – SJR59 Aug 26 '16 at 13:18