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.

--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---

Reply via email to