Re: Cross Platform FIFO Queue


"Peter Olcott" <NoSpam@xxxxxxxxxxxxxx> wrote in message
news:09ednakBvaREpjfWnZ2dnUVZ_u6dnZ2d@xxxxxxxxxxxxxxx

"Marcel Müller" <news.5.maazl@xxxxxxxxxxxxxxx> wrote in
message
news:4baa0ddf$0$6978$9b4e6d93@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi,

Peter Olcott wrote:
I am now leaning towards MySQL to handle this. I will
need a database to handle user accounts anyway.
Hopefully MySQL can handle record locking.

any database must implement locks.

I could simply read from the front of the table (in
record number order) and append to the back of the
table.

There is no 'front' and 'back' in the table unless you
introduce a strongly monotonic column as primary key. Of
course, you could do that with an auto increment column
as primary key.

Isn't there always some sort of sequential record number
that is based on the order that rows were initially added
to a table? I want to avoid indexes on this because they
would unnecessarily degrade performance.


The read would mark this item a being actively
processed, and delete it (or mark it to be considered as
deleted) when processing is complete.

This does not work. Think about the LUWs.

I don't know that acronym.


Either the consumer updates a line and set the status to
'in process' and /immediately/ commits this change. In
this case the request is forever lost in this state if
the consumer dies for some reason.

Or the consumer does /not/ commit the DB immediately. In
this case the behavior depends on the database and the
transaction isolation level. In READ COMMITED mode (which
is the default) other consumer are either forced to wait
on the row to become unlocked or see the old value. The
first case would block all other consumers at the SELECT.
The second case will cause the request to be serviced
multiple times but the following consumers will block on
the UPDATE statement. Any case is unusable since it will
block all servers with only one request.

In fact you have to place a exclusive write lock at the
moment where the consumer selects the next work item
(isolation level serializable). And furthermore the
SELECT must not wait on locked records, instead it must
skip them. If a consumer dies unexpectedly the database
does a rollback and the incomplete work item is
implicitly put back in the queue.
This gives you maximum throughput while keeping
transactional data integrity at service level EO (exactly
once).

If the database does not support the write lock at select
you could use a trick. Issue an update statement on the
status column with a row count limit of 1. This will
fetch exactly one work item and place a write lock on it.

Btw. I am in doubt that MySQL fully supports transaction
isolation levels. So you have to study the database
documentation very carefully, to get a reliable
implementation even when the DB kernel is updated.


I am guessing that the append to the end of the table
will may require a file lock. (Unless I recycle deleted
records first).

Databases do not work with file locks. The use exclusive
access anyway. So the synchronization is no longer
distributed over different processes.


If I recycle deleted records then the process must look
for the first of several records that are marked as
available for processing.

I would strongly recommend not to do this.
Use a strongly monotonic column. And delete serviced
records immediately. Anything else would blow up your
table. If you want a logging, move the records to another
table after completion, and do this in the same
transaction.


Does this sound good? Are there any issues that I have
not considered? Hopefully MySQL is stable and robust
enough.

Sorry, I have not implemented this pattern in MySQL so
far. Only MSSQL, Oracle and OpenSQL (ABAP).


Marcel

My database experience is at a lower level of programming.
In this case it seems that the problem is simple. If SQL
does not work this way I can always do this myself from
scratch.

Two tables:
(1) (InProcess Table) A tiny table of records that are
currently being processed, this is expected to have far
fewer than 100 rows. (based on a multiple of the expected
peak load) This is the table that controls the process
flow. It is kept very small so it can be read sequentially
very quickly. There are three status values:
(a) Available for Processing
(b) Currently being Processed.
(c) Empty, available to be re-used.
This table has a direct link by record number in the
Transaction History table.

(2) (Transaction History Table) A much larger table audit
and recovery purposes. This table contains all of the
details of every transaction along with two status codes:
(a) Currently being Processed.
(b) Processed is completed

The HTTP WebServer process adds records to the Transaction
History Table, and the InProcess table. It adds records to
the InProcess table by first looking for an record that is
marked as Empty. If it does not find one, it reports this
as an error. For now we can assume that this never occurs.
The number of available records will be initialized to a
multiple of the expected peak load.

(1) Read the first sequential record in the InProcess
table.
(2) If its status indicates that it is available lock this
record and if the record lock is successful change its
status to in processing, commit this change.
(3) When processing successfully completes mark the
in-processing record as empty (available for re-use) in
the InProcess table and also update the transaction
history table status to Completed commit this change and
unlock the record. (The Transaction History table record
should not have to be locked because record access is
limited by the InProcess table, a one-to-one mapping).

If all of the data access goes through a single process,
(such as a single instance of MySQL) then it would be
possible to be accomplished much more quickly than actual
disk access time. In this case the record lock would lock
a region of memory instead of a region of disk space. The
SQL provider could write these changes to disk when it
gets a free moment. Changes would be lost if the SQL
provider crashed, before the changes are written but
processing of the transaction could be much faster. If the
changes are lost, the transaction history table would
provide the means of recovery.




This is just a rough sketch to assess feasibility. I already
thought of ways to improve it. If it is not easy to do in
SQL, I will go back to the sockets level of IPC.


.

Relevant Pages

  • Re: Cross Platform FIFO Queue
    ... need a database to handle user accounts anyway. ... MySQL can handle record locking. ... If the database does not support the write lock at select ... I am in doubt that MySQL fully supports transaction ...
    (comp.programming.threads)
  • Re: I need the ODBC 2.0 Oracle equivalent of a record lock
    ... The trick was that if CommitTransaction was not made, we had to record, in ... it was rolled back before the database system became publicly ... So database integrity is NEVER an issue: a transaction ... when the database is restarted, etc. So if you lock table B, then lock table A, make a ...
    (microsoft.public.vc.mfc)
  • Re: I need the ODBC 2.0 Oracle equivalent of a record lock
    ... So database integrity is NEVER an issue: a transaction ... when the database is restarted, etc. So if you lock table B, then lock table A, make a ... and it is part of what they guarantee as part of the product. ...
    (microsoft.public.vc.mfc)
  • Re: I need the ODBC 2.0 Oracle equivalent of a record lock
    ... So database integrity is NEVER an issue: a transaction ... when the database is restarted, etc. So if you lock table B, then lock table A, make a ... and it is part of what they guarantee as part of the product. ...
    (microsoft.public.vc.mfc)
  • Re: [PHP] Re: Getting last record ID created from DB
    ... increment auto id fields (e.g. they could fill in the blanks from ... My own knowledge of mysql is about 5 years old and never really used. ... transferrable to other database engines. ... the lock once the Id is retrieved using any of these methods. ...
    (php.general)