0

I have a situation wherein as part of a online transaction, I have to save some data into other database, a slight latency (few seconds) in updating the other database is fine. Now since both databases are Oracle, I have below 3 options, I need some insight as which one is better.

  1. Oracle Database Links: Wherein I convert the SQL into PL/SQL and make my database take care of writing into another Oracle based database for DEV env both the databases are in same server as different schema while in production they happen to be two separate ORACLE RACs separated by a few routers and switches.

  2. Spring Batch: Use a batch job somehow to pick the transactions from my source database and process and write into another target database. This way my online transactions would not fail it other database ever goes down or hits a perf issue or face a network issue. And if they ever fail I can code for job restart ability. Is Spring batch well suited for such event publishing case? Would I hit any challenge in future?

  3. 2-Phase-Commit: I simply implement 2PC and save the data in both the database in a transaction. Or maybe make it look more future proof and save in a messaging system and my source database.

bjethwan
  • 97
  • 1
  • 3
  • What about an event sourcing architecture? – Michael Minella Mar 02 '18 at 16:08
  • Hi Michael, Thanks for replying. In this particular case event sourcing looks pretty big and then customer doesn't have an event source database in place already, coupled with that I own only one side of this application and happen to be enhancing this existing Java based batch job based integration. The existing job is very basic Java code triggered by unix cron. However, before introducing Spring Batch to it, I wanted some advice on Oracle Database Links. As given that they don't have anything to write for ItemProcessor. BTW. I liked your talks and work on Spring Batch. You are really good. – bjethwan Mar 05 '18 at 03:38
  • Thanks. I don't have any practical experience with Oracle Database Links. I will say that going that route does tie you more closely to Oracle. I'm sure you know my thoughts on Spring Batch ;) 2PC is a ball of worms that I'd avoid if at all possible. – Michael Minella Mar 05 '18 at 16:21
  • Michael - Just out of curiosity, how do you make commits/rollbacks in Spring Batch, when saving data in Job Repository and Target resource for ItemWriter? – bjethwan Mar 06 '18 at 04:48
  • They need to use the same data source or you are stuck with two phase commits. – Michael Minella Mar 06 '18 at 05:13

0 Answers0