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

Question

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 transaction
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.

Answer 1

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

Answer 2

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.