Oracle Locking Using DBMS_LOCK

We are using the folling Oracle functions to perform row level locking. These functions are called from our WinForms application using dotConnect for Oracle and Enterprise Library 4.1.

-- internal function to get a lock handle
-- (private for use by REQUEST_LOCK and RELEASE_LOCK)
CREATE OR REPLACE FUNCTION GET_HANDLE (i_lock_name IN VARCHAR2) RETURN VARCHAR2 IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_lock_handle VARCHAR2(128);
BEGIN
DBMS_LOCK.ALLOCATE_UNIQUE (
lockname => i_lock_name,
lockhandle => v_lock_handle,
expiration_secs => 864000); -- 10 days
RETURN v_lock_handle;
END GET_HANDLE;

CREATE OR REPLACE PROCEDURE REQUEST_LOCK (i_lock_name IN VARCHAR2) IS
v_lock_status NUMBER;
BEGIN
v_lock_status := DBMS_LOCK.REQUEST(
lockhandle => GET_HANDLE(i_lock_name),
lockmode => DBMS_LOCK.X_MODE, -- eXclusive
timeout => 0, -- do not wait
release_on_commit => FALSE);
CASE v_lock_status
WHEN 0 THEN NULL;
WHEN 2 THEN RAISE_APPLICATION_ERROR(-20000,'deadlock detected');
WHEN 4 THEN RAISE_APPLICATION_ERROR(-20000,'lock already obtained');
ELSE RAISE_APPLICATION_ERROR(-20000,'request lock failed - ' || TO_CHAR(v_lock_status));
END CASE;
END REQUEST_LOCK;

-- wrapper to release a lock
CREATE OR REPLACE PROCEDURE RELEASE_LOCK (i_lock_name IN VARCHAR2) IS
v_lock_status NUMBER;
BEGIN
v_lock_status := DBMS_LOCK.RELEASE(
lockhandle => GET_HANDLE(i_lock_name));
IF v_lock_status > 0 THEN
RAISE_APPLICATION_ERROR(-20000,'release lock failed - ' || TO_CHAR(v_lock_status));
END IF;
END RELEASE_LOCK;


These are the .Net Fuctions that call the Oracle lock functions mentioned above:

Public Function RequestLock(ByVal lockName As String) As Boolean
Dim isLocked As Boolean = True

Try
Dim db As Database
Dim cmd As DbCommand

db = DatabaseFactory.CreateDatabase()
cmd = db.GetStoredProcCommand("REQUEST_LOCK", lockName)
cmd.Connection = _DatabaseConnection.GetConnection()
db.ExecuteNonQuery(cmd)

Catch ex As Exception
isLocked = False
End Try

Return isLocked
End Function

Public Function ReleaseLock(ByVal lockName As String) As Boolean
Dim isReleased As Boolean = True

Try
Dim db As Database
Dim cmd As DbCommand

db = DatabaseFactory.CreateDatabase()
cmd = db.GetStoredProcCommand("RELEASE_LOCK", lockName)
cmd.Connection = _DatabaseConnection.GetConnection()

db.ExecuteNonQuery(cmd)

Catch ex As Exception
isReleased = False
End Try

Return isReleased
End Function


The problem I am having is creating a lock and having that lock persist. I have tried Direct and OCI modes and used the pooling and non-pooling options in connection string and nothing I try will work for us. The Direct/Pooling options works for a period of time but if we monitor the Oracle sessions the connections that hold the locks are closed. Obviously when the connections are dropping the locks disappear as well.

Sample connect strings we have tried.

providerName="dotConnect for Oracle" />
providerName="dotConnect for Oracle" />
providerName="dotConnect for Oracle" />
providerName="dotConnect for Oracle" />