Oracle Locking Using DBMS_LOCK

[kevin.weir]

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.









Basically we would like to use dotConnect for Oracle to request a lock and hold a lock until we release it. Is this possible?

[kevin.weir]

I have resolved problem by not using Enterprise Library. Instead my .NET functions RequestLock and ReleaseLock were modified to use dotConnect for Oracle classes directly. i.e OracleConnection and OracleCommand.

The problem with Enterprise Library was how those classes were managing connections.

The code you see below now works. The _DatabaseConnection object is created when the application starts up. It opens up a connection (OracleConnection object) that is subsequently referenced by these functions. The key is the connection does not go away and therefore the locks are maintained.

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

Try
Dim conn As OracleConnection
Dim cmd As OracleCommand
Dim param As OracleParameter

conn = _DatabaseConnection.GetConnection()

cmd = conn.CreateCommand()
cmd.CommandText = "REQUEST_LOCK"
cmd.CommandType = CommandType.StoredProcedure

param = cmd.CreateParameter()
param.Direction = ParameterDirection.Input
param.OracleDbType = Devart.Data.Oracle.OracleDbType.VarChar
param.Value = lockName
param.ParameterName = "i_lock_name"

cmd.Parameters.Add(param)
cmd.ExecuteNonQuery()

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 conn As OracleConnection
Dim cmd As OracleCommand
Dim param As OracleParameter

conn = _DatabaseConnection.GetConnection()

cmd = conn.CreateCommand()
cmd.CommandText = "RELEASE_LOCK"
cmd.CommandType = CommandType.StoredProcedure

param = cmd.CreateParameter()
param.Direction = ParameterDirection.Input
param.OracleDbType = Devart.Data.Oracle.OracleDbType.VarChar
param.Value = lockName
param.ParameterName = "i_lock_name"

cmd.Parameters.Add(param)
cmd.ExecuteNonQuery()

Catch ex As Exception
isReleased = False
End Try

Return isReleased
End Function