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