DAO design for writing big XML file on database

Question

I am currently working on JavaEE application (Spring, Hibernate). I have to put a big XML file (more than 1 gigabyte) on a relational database (Postgres).

The application does not use batch processing. I've done some searching but I did not find any solution for the design of the DAO layer: if I use only one transaction, the server will not response to any request until it finishes the insertion of rows to fill a complex database schema (a huge number of rows: the order of added rows is thousands (for every table)). So, using 1 transaction is not a good idea. I can split XML file basing on its tags data: every tag content will be inserted on a row. The idea is to use multithreading to manage transactions (every transaction inserts a defined number of rows). Is it a good idea? I have found a difficulties to would know how to define the necessary number of transactions to maintain a good time response of the application. I also search how to manage failure of certain transactions. For example, If only 3 transactions write over 1000000 fail, I should try again all the transactions?

When searching, I find that batch processing like Spring batch manages database records and transactions failure. But in my application, we did not use batch processing.

Unfortunately, I can not change the database to Nsql database or add Spring Batch framework to the project.

N.B: I can not bypass Spring and Hibernate in this project, but I am open to any suggestion even for curiosity.

Answer 1

Your problem and technology stacks are very similar to a project that I am working on as an application architect at this time so I am going to give you my best advice on how to proceed given the information and constraints you have provided.

Your instincts are correct that the best choice for this project would be to use Spring Batch or something similar to it. What you are effectively doing is exactly what batch processing is and your attempts to introduce multi threading and working to avoid running out of memory in processing are easily handled in Spring Batch. It sounds like from my perspective that your client had a poorly designed application for the intended features, and you were asked to clean up the mess but not at the expense of a rewrite.

So I am not saying that you must use Spring Batch but I want to give you some context as to why Spring Batch is the best choice. This will help you design your approach properly.

Readers, Processors and Writers

The idea behind Readers is to read in a subset of the data to be processed. This can typically be done however you are reading the XML file now. Your reader keeps track of where it is on the file position. It is creating objects for the processor.

The processor will perform any business or integration logic that you might have.

The writer can use a tool like Hibernate to write out individual records to the relational database.

Chunking and Transactions

A chunk of data is just a subset of data objects that you read in, process and write out in a single contiguous transaction. If the transaction completes through all the way then it is clearly okay to commit to the database. In the event of an exception you will want to define exception behaviour to where you rollback the transaction at the database level and properly log which chunk of records failed to be completed successfully. Perhaps as part of this rollback behaviour you want to include some notification event behaviour to email a support group to look at the problem. Utilizing a transaction framework through Spring + JTA is the best approach.

Realistically though you can't have a discussion about what to do when there is an exception without looking at your business requirements (or as I suspect, perhaps the lack of business requirements from your client here). Defining what happens when some records don't process isn't something we can tell you, it is something that has to be addressed in your business requirements or else it is a gap.

Regardless how you approach what to do in your rollback behaviour, 1GB of data for a single file is too much for a single transaction and it would be wasteful to throw away all of the processing that went into that file because of what might amount to an unexpected character in some arbitrary record.

• You want to chunk your input data to a reasonable size such that there is adequate memory for all currently processing files at the same time.
• You want your chunk to be individually transacted such that once it completes you will not have to revisit these records again
• You want to process these files one chunk at a time at first, and only after you are not reaching desired performance metrics should you consider a multi threaded or distributed approach.
• You want to log what chunk you are currently processing in the database in some kind of meta data table, and if a chunk fails then in your exception rollback behaviour you want to update within the database which chunk failed on the job.
• If a chunk fails on a file, you should stop processing altogether until the problem is identified and fixed. This might be human involvement so you probably need to consider a support functionality to restart a failed job where it left off.

Performance and Scaling

This is hard for me to help with since I don't know where the file comes from, how the file processing job is invoked, and what non-functional requirements you have around performance. My advice here of course is that the safe bet is to process as individual transactions in a single threaded way to start off. Multi threading or even introducing parallel processing and distributed computing here could be potentially very complicated if you are trying to roll your own. Frameworks like Spring Batch help you manage this if you need it but there is a good chance that you won't if the client did not offer any strict performance requirements. Your concerns about deadlocking the database and staying within the memory constraints on your server are alleviated by handling this in a single threaded way.

Answer 2

What Spring Batch will do for you is decouple the reading, writing and business logic tasks of your problem while preserving causality. It doesn't introduce any form of database transaction batching, you'd still be on the hook for that (though the Reader abstraction would make that a tad simpler). So if all you want to do is perform your inserts in efficient batches of SQL, Spring Batch provides nothing, really, but an opinionated framework that you don't know.

When you get right down to it, if you're traversing an XML document and building Hibernate objects from it, what really matters to the database is:

• causal consistency, which uncontrolled multi-threading of small transactions might violate
• minimum application time spent inside a database transaction (which means not opening a transaction until all data is available and not performing business logic while the transaction is active)
• the ability to control the size of a commit (so as not to commit 1 record at a time OR commit everything all at once)

For maximum efficiency WITH consistency and WITHOUT a framework, what you need is a minimum of two threads:

• One that's reading and creating objects, as fast as it can
• One that's writing objects to a database, as many as are available and in the order they were read

A simple way to provide this behavior with Hibernate involved:

• Have one Reader-Creator thread that reads the XML file and creates Hibernate objects, but does not persist them. Instead, it stores them on a bounded BlockingQueue (BQ).
• Have a second Persister-Writer thread that shares the BQ with the first thread. In a loop, it polls for a new object to be added to the BQ. When one is discovered, it is persisted using a new EntityManager (EM), along with N-1 additional elements that are currently available (using drainTo, which doesn't block). Then, flush is called on the EM, and we continue the next loop (throwing out the old EM in the process).

In this pattern, the queue that connects the two threads is generally called a "Mailbox" and what we're doing is a cheap approximation of Actor Model programming. But in the end:

• you are protected from out of memory issues by properly sizing the BlockingQueue, as the first thread will block if it becomes full and the second thread throws away all of the state it cares about on each loop
• causal consistency is guaranteed because all objects will be persisted and flushed to the database in the order they are read
• database performance is improved because N objects are flushed at a time rather than 1 or ∞ (you may experiment for an optimal value of N or just use 1000)
• No new frameworks are involved -- indeed, this is likely less than 50 LoC

You will need some way to ensure that the second thread stops after the first thread runs out of the work and the last piece of work in the BQ has been committed, otherwise you'll exit the program before all content has been saved. A common technique for this is to create a "poison pill" message -- something that doesn't look anything like the types of message usually received by the Persister-Writer that causes it to flush any remaining objects and break out of its loop.