Reading only new rows from a log-like table in a database

Question

We have the sitation that several servers are inserting chunks of rows into a table in a relational database, and one server reads the new data once in a while from the table. (The table is conceptually some kind of logfile - data is only inserted but never modified, and the reading server shows a tail of the log.) Is there a way to have the reading server only read the new data? We are free to structure the table(s) as we want to.

Some ideas that crossed my mind but do not work are:

• Marking the rows as read does not fit our application: the reading server should not change the database. (Writing to the database for displaying things is not a good thing to do, and there might be several sessions displaying the stuff.)

• We could insert a timestamp in each row that is filled with the database system time. The problem is that this is not the timestamp of the commit time, but of the insert time. If you ask the database "give me all values between now-5 minutes and now" you cannot rely on all values being present, since there might be transactions in progress. You'll have to ask again later for the values in this interval, which is what I wanted to avoid.

• We could insert a running row count filled from a sequence. The same problem with running transactions occurs as when using timestamps.

Is there any solution to the problem, or do I have to apply some heuristics like assuming a maximum transaction time and always asking for values written after "now - maximum transaction time" and reading some data twice?

In case it matters: we use Oracle for this. But I assume answers that work only with other databases, are of general interest as well.

Answer 1

MS SQL have its specific solution:

You can add a column of rowversion data type to table. This column will be automatically updated on related rows by the engine on any update/insert statement.

If writer use ReadCommitted isolation level then reader can use ReadUncommitted isolation level (so it don't need to wait for all transactions to end before returning any results) but with the query like this:

SELECT * FROM [Log]
WHERE Version > @LastKnownVersion
    AND Version < MIN_ACTIVE_ROWVERSION()

Where @LastKnownVersion is the maximum row version processed by the reader and MIN_ACTIVE_ROWVERSION() is a built-in MS SQL function which returns minimum row version number which is still in transaction.

So with this solution even if you have ID=4 committed but ID=3 not yet it will return only rows changed before ID=3 because its version will be exactly MIN_ACTIVE_ROWVERSION().

Advantage of this method is that there is no need for reader to wait for transaction to be committed before getting any results what can be crucial if there is a lot of writers. (Reader could be locked forever.)

Answer 2

The database being used wasn't specified so it's not clear as to whether the solution has to be hammered into an existing deployment or not. There are some queue engines that can be plugged into MySQL that could potentially work. One of them is Q4M. Some commercial databases like Oracle have temporal database functionality that allow for determining transaction time vs valid time vs real time.

When using Oracle, either the pseudo-column ora_rowscn or the useful combination scn_to_timestamp(ora_rowscn) can effectively provide the timestamp for when a row was committed (the SCN in which it took place). Alternatively, Oracle Workspace Manager provides version-enable tables, basically it goes like this: You enable versioning on a table with DBMS_WM.EnableVersioning(...), rows are inserted with an aditional WMSYS.WM_PERIOD(...) field specifying a valid time range, set a valid range for the workspace is set on the reader DBMS_WM.SetValidTime(...).

You could also fake this functionality to a certain degree by meshing your timestamp idea with the commit time heuristic. The idea is simply to store the "valid time" as a column along with the data instead of using an arbitrary delta from now(). In other words a secondary timestamp column that would specify some future date (the "valid time") based on a heuristic of commit time + some acceptable window of delay (perhaps the mean commit time + twice the standard deviation). Alternatively, using some ceil()ing of mean commit time ("at least the commit time but rounding up to, say, 30 second intervals"). The latter would effectively quantize (coalesce?) the time log records would be read. It doesn't seem too different but this way would save you from reading redundant rows. It also solves the problem that the reading application cannot accurately know the commit times of the writing application without writing a lot more code.