Oracle Locking Using DBMS_LOCK

Oracle Locking Using DBMS_LOCK

Postby kevin.weir » Wed 03 Jun 2009 19:40

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" />




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
 
Posts: 12
Joined: Wed 17 Sep 2008 22:25

Postby kevin.weir » Wed 03 Jun 2009 22:46

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
kevin.weir
 
Posts: 12
Joined: Wed 17 Sep 2008 22:25


Return to dotConnect for Oracle