0

I'm implementing a background process for moving event log records from SQL database to mongoDB.

Event log / audit trail entries are known to change only once at the end of the event. The process is like this:

1) an event log entry gets created to fixate that a business process has been initiated

2) a new transaction is started for the business process

3) audit trail entries are created

4) attempts to update the event log entry with successful status

5) the transaction completes

6) if the transaction fails - updates the event log entry as failed

So, theoretically, the background process could safely read all the log entries that have been already marked as failed/successful and, most probably, after specific timeout could also safely read the entries that for unknown reason are stuck in "Process was started" state.

To avoid any locks on the event log table while the background process is reading event log records in batches, I would like to use a relaxed isolation level but I'm not sure which one would be safe to use on a table that will have lots of parallel inserts and updates occurring constantly (albeit updates only on the records that my background process will ignore; so I don't care about dirty reads).

In my case it seems to be acceptable to miss the records that are being inserted right now (I'll get them in the next background job run anyway) but it is not acceptable to get duplicate/missing records among the older records that aren't being updated right now.

P.S. You might ask - why not log directly to mongoDB? There are two reasons: 1) the database has many triggers and stored procs that log to SQL table and the customer doesn't want to reimplement all of that; 2) the customer wants to ensure atomicity of event log/audit trail with the transaction of the business process and he's afraid that with direct journaling to mongoDB there might be cases when for some (most probably - very critical) reason event log entries go missing while the SQL transaction has succeeded and the data was changed. It's not trivially possible to include writing to mongoDB in a single atomic unit of work with an SQL transaction.

JustAMartin
  • 13,165
  • 18
  • 99
  • 183
  • 2
    Why not use `SNAPSHOT` isolation for the log reader? – Dan Guzman Sep 27 '19 at 10:27
  • @DanGuzman Thanks for the idea, I guess I'll have to reformulate the question then (will edit it). – JustAMartin Sep 27 '19 at 10:45
  • In this case, read committed is safe. Since the completed logs are immutable, it seems to me snapshot or read committed snapshot does not provide extra benifit – PeterHe Sep 27 '19 at 13:59
  • Also just curious, with all those good reasons you mentioned to use SQL server for the logs, why move the data to mongoDB after inserted to SQL? Anything you can't do in SQL Server and you have to leverage mongo? – PeterHe Sep 27 '19 at 14:01
  • @PeterHe Because event log table grows too fast - it is already 10Gb worth of data, while the business data occupies only about 1Gb. This makes database copies very space-inefficient. – JustAMartin Sep 27 '19 at 14:04

0 Answers0