CODEWITHSUNDEEP
oracle-database
fortm
fortm

Reputation: 4208

DBMS_LOCK.ALLOCATE_UNIQUE lock release

Between calls to DBMS_LOCK.ALLOCATE_UNIQUE and DBMS_LOCK.RELEASE inside a Stored Procedure, if there is an intermediate COMMIT in Stored procedure is Lock released at statement where COMMIT is present ?

1. SELECT QUERY
2. DBMS_LOCK.ALLOCATE_UNIQUE
...
3. COMMIT
4. BEGIN CURSOR
...
5. DBMS_LOCK.RELEASE

So will the lock released after statement 3 or after 5 ?

Upvotes: 1

Views: 1870

Answers (1)

Sanders the Softwarer
Sanders the Softwarer

Reputation: 2496

Your question based on wrong guess that ALLOCATE_UNIQUE establishes a lock. In sober fact this procedure does nothing except of save name-value pair, lock name => lock handle (and it applies internal commit, so don't use it in the middle of your transaction).

To establish lock you should use DBMS_LOCK.REQUEST function. It has parameter named release_on_commit so both cases are viable: lock can be released either at statement 3 or at statement 5 depends of request mode.

Upvotes: 3

Related Questions