英文:
Which of the several sessions waiting to acquire an exclusive row lock will be notified first upon release in Oracle RDBMS?
问题
以下是已翻译的内容：
假设如下：
- 存在一个用户会话，该会话在表中保持对多个行的独占锁。
- 启动了两个（或更多）其他会话，每个会话都请求获取当前保持的某些锁定。
- 为了简化情况，让我们假设新会话只想获取一个行锁，而且它们都想获取相同的行锁。实际上，以这种方式完全发生的可能性不大，因为每个会话都会尝试锁定一组相连接的行，但我怀疑这不会影响这个问题的答案。
- 文档 中关于并发性的说明如下：
> 如果锁处于活动状态，则会话会请求在锁被释放时通知它。 - 最初持有锁的会话提交并释放其锁定。
在这两个等待的会话中，哪一个会收到通知，表示它现在可以获取锁定？是否有数据库设置可以控制这一点？
如果等待者是以随机顺序通知的，是否意味着当它们竞争同一个锁时有可能使其中一个会话饥饿（无法获取锁），或者是否有一种机制来处理这种情况？
英文:
Suppose the following:
- There is a user session which holds exclusive locks on several rows in the table.
- Two (or more) other sessions are started, each with the request to acquire some of the currently held locks
- To make the case easier, let's assume that the new sessions want to acquire just one row lock, and they both want the same one. In reality it's unlikely to happen exactly that way, because each session will try to lock an "island" of connected rows, but I doubt this impacts the answer to this question.
- Documentation on concurrency says, that:
> If the lock is active, then the session asks to be notified when the lock is released.` - The original session which held the locks commits and releases its locks.
Which one of the two waiting sessions will get notified that it is now able to acquire locks? Is there a DB setting that controls this?
If the waiters are notified in random order, does that mean that there is a possibility of lock-starving one of the sessions when a lot of them compete for the same lock, or is there a mechanism to address this case?
答案1
得分: 2
当你需要一个资源（而其他人拥有它）时，你会进入一个“等待者”队列，正如名称所示，你会排队。
如果你真的想深入了解，可以让一个会话锁定一行，然后让其他几个会话等待该行，然后执行：
alter session set events = 'immediate trace name enqueues level 3';
以获取显示各种队列情况的跟踪文件。
关于这方面的一个优秀资源是：《Oracle核心》，作者Jonathan Lewis。
英文:
When you're after a resource (and someone else owns it) you go on a "waiters" queue, and as the name suggests, you get "in line".
If you really want to dig into the weeds, have one session lock a row, then get a couple of others to wait for that row, and then do:
alter session set events = 'immediate trace name enqueues level 3';
to get a trace file showing the various queues floating around.
An excellent resource for this is: Oracle Core, by Jonathan Lewis.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论