In Oracle, a way for Updates to not lock rows?

I have an Update query that recalculates -every- column value in exactly one row, each time. I've been seeing more row-level lock contention, due to these Update queries occurring on the same row.

I'm thinking maybe one solution would be to have subsequent Updates simply preempt any Updates already in progress. Is this possible? Does Oracle support this kind of Update?

To spell out the idea in full:

1. update query #1 begins, in its own transaction
2. needs to update row X
3. acquires lock on row X
4. update query #2 begins, again in its own trans开发者_JS百科action
5. blocks, waiting for query #1 to release the lock on row X.

My thought is, can step 5 simply be: query #1 is aborted, query #2 proceeds. Or maybe dispense with acquiring the row-level lock in the first place.

I realize this logic would be disastrously wrong should the update query be updating only a subset of columns in a given row. But it's not -- every column gets recalculated, each time.

I'd ask whether a physical table is the right mechanism for whatever you are doing. One factor is how transactions needs to be handled. Anything that means "Don't lock for the duration of the transaction" will run into transactional issues.

There are a couple of non-transactional options:

Global context values might be useful (depends if you are on RAC) and how to handle persistence after a restart.

Another option is DBMS_PIPE where you'd have a background process maintaining that table and the separate sessions send messages to that process rather than update the table directly.

Queuing is another thought.

If you just need to need to reduce the time the record is locked, autonomous transactions could be the answer

It's possible to do the opposite of what you're asking, have query 2 fail if query 1 is in progress using SELECT FOR UPDATE and NOWAIT.

Alternatively, you could try to see if you can get the desired effect by adjusting the isolation level, but I do not recommend this without extensive testing, as you don't know what knock-on effects it may have.

Oracle's UPDATE doesn't support any locking hints.

But OraFAQ forum suggests such hacky workaround:

DECLARE
  x CHAR(1);
BEGIN
  SELECT 'x' INTO x
  FROM tablea
  WHERE -- your update condition
  FOR UPDATE OF cola NOWAIT;

  UPDATE tablea
  SET cola = value
  WHERE -- your update condition
EXCEPTION
  WHEN OTHERS THEN
  NULL; -- handle the exception
END;