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

Create another table LOG_REVISION. It contains a single row (an INTEGER).

The logging process should read this table and add the number it finds there to each log record. Lock the row until you commit the transaction.

The reading process should first update the LOG_REVISION by incrementing the number and then read all rows which have the old LOG_REVISION.

[EDIT] There are two more ways around this:

• have another table where you record the rows which you have processed.
• The writer puts the data into an intermediate table and the reader copies the rows to the final place and deletes the processed rows.

Answer 2

I would say your idea for timestamps is valid, but rather than ask for a range, just ask for all values after a certain time. You should get all available values in the database in the most recent time period you selected. Obviously, it won't work for any transactions still in progress that haven't been logged... but you only have to do a single, simple query.

Edit:
You must ensure the timestamp values are unique per row. In which case you only need to keep track of the latest timestamp value you read from the database. The next query to the database is of all values after that. You won't miss any data, nor will you read duplicates. Any ongoing transactions will not have been stored in the database during a query for you to miss, and you are guaranteed to get it the next time you query the database.

Answer 3

This is a possible solution , depending on your situation etc.

Have a column called "read_timestamp" which is null , once a row is read, the reading process will update it to a non-null timestamp.

The reader queries this table with "where read_timestamp is null".

An easier solution would be to go with an estimate (i.e. this row may have been viewed already kind of caveat). Hence you would at any time display the "last 50 rows" or the 'rows that arrived in the last 10 minutes" (with the inaccuracy that another log-viewer may have already pulled those).

A third solution would be to feed these rows using a back-end process to a queue : Each read of a row makes the row disappear from the queue (because its a 'pop' operation). Hence a row can only be viewed once (first-come-first-served).

Answer 4

create an id sequence for the log table so that each log has a unique id. then when the reader reads the logs it will record somewhere the highest id read. Next time it runs it will get all ids after the last recorded id. Any ongoing transaction won't be a problem since you don't get them in the resultset. They will be gathered in the next run.

so if you have:

id | log
1  | blah
2  | blah again
3  | more blah
* transaction to insert row '4' in progress

then you will have fetched all those logs and recorded 3 as last id found. And on the next run:

select id, log from logs where id > last_recorded id order by id #id will be 3

4  | yet again some blah
5  | does this blah never end
6  | omg blah

and record 6 as your new last recorded id. I still think it is good to keep the dates of when the log was made as well.

EDIT ok to catch every thing like that you will have to keep a set of all records read in an alternate location and then get the difference of the read set against the active log table. If you can't touch the log table then you are just dealing with sets and finding what is not in one of the sets.

Answer 5

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.

Answer 6

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.)