Jens wrote:
> I have a database scenario (i'm using Oracle) in which several
> processes make inserts into a table and a single process selects from
> it. The table is basically used as a intermediate storage, to which
> multiple processes (in the following called the Writers) write log
> events, and from which a single processes (in the following referred
> to as the Reader) reads the events for further processing. The Reader
> must read all events inserted into the table.
>
> Currenly, this is done by each inserted record being assigned an id
> from an ascending sequence. The reader periodically selects a block of
> entries from the table where the id is larger than the largest id of
> the proviously read block. E.g. something like:
>
> SELECT
> *
> FROM
> TRANSACTION_LOG
> WHERE
> id > (
> SELECT
> last_id
> FROM
> READER_STATUS
> );
>
> The problem with this approach is that since writers operate
> concurrently, rows are not always inserted in order according to their
> assigned id, even though these are assigned in sequentially ascending
> order. That is, a row with id=100 is sometimes written after a record
> with id=110, because the process writing the row with id=110 started
> after the processes writing the record id=100, but commited first.
> This can result in the Reader missing the row with id=100 if it has
> already read row with id=110.
>
> Forcing the Writers to exclusive lock on the table would solve the
> problem as this would force them to insert sequentially and also for
> the Reader to wait for any outstanding commits. This, however, would
> probably not be very fast.
>
> It is my thinking, that it would suffice for the Reader to wait for
> any outstanding Writer commits before reading. That is, Writers may
> continue to operate concurrently as longs as the Reader does read
> until all writers have finished.
>
> My question is this: How can i instruct my reader process to wait for
> any outstanding commits of my writer processes? Any alternative
> suggesting to the above problem is also welcome.
>
> >
>
A couple of quick and dirty alternatives early in the morning .. none
are perfect but they might be a start for thought processes:
1) Modify your transaction_log table to add a 'processed_flag' column
that is initially 'N' (or null or whatever and then have your reader
update the table as you go through it with a "Y" in that column. Then
you could just select where processed_flag = 'N'. (or null or whatever)
It means that you have to select the records for update and you probably
want to index that flag field you may need to update your source
system to make sure that the column is properly populated or at least
that you are not buggering anything up by adding a column so that is a
drawback.
2) If you cannot modify the transaction_log but you have the ability to
create another table you can have your reader process write each record
to a 2nd table as it proceeds and then delete the record from the
original table. This has the drawback that if there is anything else
that needs to read the transaction log they will have to be pointed at
the 2nd table but there are ways around this with synonyms, views and
other things.
3) a variation of #2 is that you could put an 'on insert' trigger on the
transaction log that writes just the ID to a second table and then your
select joins that 2nd table to the transaction log on the ID to get the
rows to process. As you process, you delete the IDs (or flag them,
similarly to #1) from the 2nd table so they wouldnt be reprocessed
ok .. time for a shower but maybe those 3 will give you some ideas .. I
have used all 3 methods at some point over the years and there are any
number of other ways to do it that I am sure you will get from other folks.
One thing to note is that timestamps (if they are offered as a solution)
don't really work because it is possible for a processed and
unprocessed record to have the same timestamp (depending on your clock
resolution). I have been bitten by this one.
Rob
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
[EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
