1

Suppose the following:

  1. There is a user session which holds exclusive locks on several rows in the table.
  2. 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.
  3. Documentation on concurrency says, that:

    If the lock is active, then the session asks to be notified when the lock is released.`

  4. 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?

M. Prokhorov
  • 3,894
  • 25
  • 39
  • as per my knwoldge its notified by the first waiter. the first session ask for the row will get it. You can know it in active session history, youll see which one blocked first – Baalback Jun 08 '23 at 13:00
  • @Baalback, so in other words, there is a linked list of waiters to notify. Because my two new sessions start concurrently, it's undefined which one of them will get the lock next, but any other session will only ever go to the end of the queue, and will never jump to the start of it? Do you know of a doc which describes this, or are you speaking from experience only? – M. Prokhorov Jun 08 '23 at 13:06

1 Answers1

2

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.

Connor McDonald
  • 10,418
  • 1
  • 11
  • 16
  • By "queue", do we mean a list of strict ordering, where there can only be one "next", which is chosen at the time of adding to the queue? I'm asking because some programming platforms have unordered queues for waiters, where they are stated to be free to notify a random waiter as well - one of those is Java's `Object::notify`, for example, where order in which the waiters notified is undefined and is a JVM implementation detail. – M. Prokhorov Jun 13 '23 at 10:44
  • my understanding is linked list, so yes, ordered – Connor McDonald Jun 15 '23 at 02:56