在Oracle中，一种更新不锁定行的方法吗? [英] In Oracle, a way for Updates to not lock rows?

问题描述

我有一个Update查询，它每次都精确地重新计算一行中的每列值. 由于这些更新查询发生在同一行上，因此我一直看到更多的行级锁争用.

我在想，也许一种解决方案是先进行后续更新，然后再抢先进行任何正在进行的更新.这可能吗? Oracle是否支持这种更新?

要详细说明该想法:

1. 更新查询#1以其自身的事务开始
2. 需要更新X行
3. 在X行获取锁
4. 更新查询#2再次开始，在它自己的事务中
5. 阻止，等待查询#1释放X行上的锁定.

我的想法是，可以简单地将步骤5设为:查询#1中止，查询#2继续.或者也许首先省去了行级锁定.

我意识到，如果更新查询仅更新给定行中列的子集，则此逻辑将是灾难性的错误.但这不是-每次都会重新计算每一列.

解决方案

我想问一问，物理表是否适合您的工作.一个因素是如何处理交易.任何表示在交易期间不锁定"的内容都会遇到交易问题.

有两个非交易选项:

全局上下文值可能有用(取决于您是否使用RAC)以及重新启动后如何处理持久性.

另一个选项是 DBMS_PIPE 您将拥有一个维护该表的后台进程，并且单独的会话将消息发送到该进程，而不是直接更新该表.

排队是另一种想法. /p>

如果只需要减少记录锁定的时间，请自主交易可能是答案

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.

解决方案

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

这篇关于在Oracle中，一种更新不锁定行的方法吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！