Oracle user locks (dbms_lock): guaranteed order of requests granted

lockingoracle

I was wondering if Oracle guarantees that the sequence of dbms_lock.request granted is the same as the sequence in which they were requested.
In my tests it always was the case, but I couldn't find any official statement in the Oracle documentation.

So let's assume we have at least three sessions:

Session 1 requests the lock via dbms_lock.request(1) and is granted the lock immediately (as the lock is not yet held by any other session)
Session 2 requests the lock via dbms_lock.request(1) and has to wait
Session 3 requests the lock via dbms_lock.request(1) and also has to wait
Now Session 1 releases the lock via dbms_lock.release(1) (or trx end or session end)

Is it guaranteed that Session 2 will now receive the lock and Session 3 still has to wait until Session 2 releases? Or might Session 3 receive the lock before Session 2?

Edit: if the answer is "yes, guaranteed", please provide link to Oracle documentation, whitepaper, V$ view or the like.

Edit 2: Database version in question is 11g and up

Best Answer

Yes!

There is a wait queue and the first requester is the first served. Beware of deadlocks...

Related Solutions

Sql-server – ASPState database locking and growth problems

I suspect you implemented Greg's replacement procedure as pre-emptive optimization. The one-row-at-a-time approach restricts locking to a single row, sure, but it's going to take a lot longer - especially if you force SQL Server to attack rows in a random order.

My suggestion would be to revert to the original procedure:

ALTER PROCEDURE dbo.DeleteExpiredSessions
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @now DATETIME;
  SET @now = GETUTCDATE();

  DELETE ASPState..ASPStateTempSessions 
    WHERE Expires < @now;
END
GO

If you find that this becomes a performance issue because of the locks taken, you can split this out and even reduce impact on the logs using something like:

ALTER PROCEDURE dbo.DeleteExpiredSessions
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @now DATETIME, @c INT;
  SELECT @now = GETUTCDATE(), @c = 1;

  BEGIN TRANSACTION;

  WHILE @c <> 0
  BEGIN
    ;WITH x AS 
    (
      SELECT TOP (1000) SessionId
        FROM dbo.ASPStateTempSessions
        WHERE Expires < @now
        ORDER BY SessionId
    ) 
    DELETE x;

    SET @c = @@ROWCOUNT;

    IF @@TRANCOUNT = 1
    BEGIN
      COMMIT TRANSACTION;
      BEGIN TRANSACTION;
    END
  END

  IF @@TRANCOUNT = 1
  BEGIN
    COMMIT TRANSACTION;
  END
END
GO

This is going to be much better, IMHO, than using a randomized cursor to delete one row at a time. You can tweak the value in TOP (1000) based on actual observation.

A couple of other ideas that might help:

(a) set the ASPState database's recovery model to simple (it defaults to full).

(b) change ASPState_Job_DeleteExpiredSessions to run every 5 or 10 minutes instead of every minute. If this job is taking > 1 minute to run then it is going to always be running. Hopefully it isn't completely linear, meaning that waiting 5 minutes instead of 1 won't queue up more than 5 minutes of work instead of 1. Implementing my suggestions above should help with this.

How to find who has allocated a user lock via dbms_lock

The type of locks you are referring to are stored in table (not view) SYS.DBMS_LOCK_ALLOCATED. I believe they only have a "handle", but not a specific owner, i.e.: they all belong to SYS. That said, if you have active blocking situation going on (someone is waiting for the lock and it's not getting released by the session who allocated the lock) then this query might show what's going on:

SELECT blocker.username blocker, blocker.sid blocker_session
     , blocked.username blocked, blocked.sid blocked_session
  FROM v$session blocked
  JOIN v$session blocker
  ON (blocked.blocking_session = blocker.sid)
 WHERE blocked.wait_class = 'Application'
   AND blocked.event='enq: UL - contention';

Related Question