Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

Re^3: Database queue logic

by monarch (Priest)
on Jun 14, 2005 at 12:41 UTC ( [id://466510]=note: print w/replies, xml ) Need Help??


in reply to Re^2: Database queue logic
in thread Database queue logic

You're right, you may be penalised with having to wait. But there are advantages in this.

Firstly, is or is not a requirement that two SELECTs are prevented from occuring at the same time?

Thus can you or can you not afford to risk having two processes perform that SELECT at the same time?

You have a choice. Risk it. Or use a lock of some sort to protect against the situation whereby both processes have nothing else to do but perform a SELECT.

Think about it. Reading a record from a database (SELECT) is likely to take a fraction of the time that writing a record to the database will take (DELETE). Hence, the odds of both your processes attempting to SELECT at the same time are low.

So this approach will give you SAFETY while using multiple processes. You could scale to 3, 4, or 10 processes using this technique.

Other techniques all impose overhead as well (and odd/even approaches perform worse if the processing time between processes varies at all).

Update: changed grammar a little.

Update 2: as pointed out by the reply to this post by Foxcub I was wrong to ignore the fact that the DELETE also needs to occur within the atomic action that contains the SELECT. Apologies.

Update 3: added readmore tags.

Replies are listed 'Best First'.
Re^4: Database queue logic
by Tanalis (Curate) on Jun 14, 2005 at 13:20 UTC
    "Reading a record from a database (SELECT) is likely to take a fraction of the time that writing a record to the database will take (DELETE). Hence, the odds of both your processes attempting to SELECT at the same time are low."

    Surely the time taken for a select or delete is largely irrelevant here?

    The OP indicates that the workflow for the current application is to select a number of rows, process them, and finally delete the rows from the database.

    To make use of a mutex around the select in an attempt to multi-thread or multi-process this operation would seem futile unless the database is also updated within the mutex to indicate that a set of rows is already being processed.

    This update allows following processes to pick up the next batch of rows for processing. Without performing that update, it's be necessary to lock around the entire transaction (from the initial select until after the delete has occurred) - meaning that the solution would be no faster than a simple single process operation.

    If it was your intention to indicate that, your comment about the time taken for a select and a delete is irrelevant and misleading. If not, I don't like your thinking: it's more than a little flawed.

    Without that locking or update it'd be very likely for every row to be processed more than once, which may or may not be an issue depending on the situation. More important, though, is the fact that repeated processing of the same data is both useless and potentially time consuming, largely negating the benefits of using a multi-process application architecture in the first place.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://466510]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others musing on the Monastery: (3)
As of 2024-03-19 04:20 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found