Selecting from and updating the same table oracle
Tom: I have a table that contains rows representing work to be "retried".
If the status column contains -1 that means the row has to be resubmitted to a process that takes about 2 minutes to complete. Currently, I have 10 different machines each running the same process that is looking for rows with status = -1, and submitting them for "retry" processing.
We have the several threads only for the purpose of redundancy and performance, and the volume of rows to be processed is high. the 3rd party API apparently (as far as we can tell now) has no way for us to indicate "nowait" on the cursor.
Do you have an idea of how to do this without using "nowait"?
Suppose I want to update a table with data from a subquery that refers to the same table.
I might want to do this for a variety of reasons, such as trying to populate a table with its own aggregate data (this would require assignment from a grouped subquery), updating one row from another row’s data without using non-standard syntax, and so on.
The My SQL manual mentions this at the bottom of the UPDATE documentation: “Currently, you cannot update a table and select from the same table in a subquery.” It’s pretty easy to work around the problem in this contrived example, but there are times when it’s not possible to write the query without subqueries that refer to the update target. Since My SQL materializes subqueries in the clause (“derived tables”) as temporary tables, wrapping the subquery into another inner subquery in the FROM clause causes it to be executed and stored into a temporary table, then referenced implicitly in the outer subquery.
The following statement will succeed: clause, which are rewritten as correlated subqueries, sometimes (usually? Wrapping the subquery in another subquery doesn’t prevent the optimizer from rewriting it as a correlated subquery, though, unless I go to extremes.
In any case it’s better to just rewrite such a query as a join.
Being in a locked state means a thread is already handling it.
Is this the best way to handle the general problem of a queued up set of rows waiting to be processed by several threads?