Locks - How and Why

You want your OSS applications to be widely used, especially after making a big investment in them.  And one of the most critical issues that arises during deployment is data contention.  You have two people trying to make updates to the same device at the same time.  The first user makes some changes, then the 2nd user makes a change and overwrites the first user’s changes. 

Most applications, like Cramer and NetCracker, simply defer to Oracle’s record locking to manage contention.  But that doesn’t work – and most people don’t learn that until it’s too late.  Oracle’s locking mechanisms are geared to transaction level locking, which seeks to minimize the time that a record is locked – and it works extremely well in that context!  Oracle locks are intended to have a very short life – typically measured in milliseconds.

However, user-level locks have a much longer life.  When a user goes to make updates to a device or to a circuit design, they will probably have the object locked for minutes – sometimes hours.  During that time, the object must be readable and some of its attributes assignable (for example, a shelf may be locked, but its ports may be assignable to a circuit design).

There are several reasons that you do not want to depend on Oracle’s locking for user-level locking:

  • When Oracle locks a record, the other write operations (like the 2nd user’s updates) are simply blocked and forced to wait until the lock is cleared.  There is no notification that there a lock blocking the operation – it just looks like the system is hanging.
  • It is difficult to determine who is holding a lock or how long he/she has been holding the lock.  What happens when someone gets called away from their desk and they have an object locked? 
  • An object in the database may extend across multiple tables (like a circuit design that involves 10 or more tables in the underlying schema) and locking all of them for an extended period of time will cripple the system.
  • Other than killing a session (which is kind of rash), there is no easy way of overriding or releasing a lock explicitly.

Granite applies a “soft-lock” mechanism to apply locks in the middle-tier, before you get to the Oracle database.  Each major object class (i.e. Site, Equipment, Cables, Circuit Paths, Networks, Segments) and Advanced Modeling Object (AMO) class has a two attributes that manage locking:

  • lock_by – which contains the name of the user who has locked the object
  • lock_ts – containing the timestamp of the lock

By looking at these fields, you can see who holds the lock and how long they’ve had it.  That way, you can call them and ask how much longer they’ll be.  Or, if they’ve had the lock too long, you can break the lock (if you have privileges to do that).

We recommend the following practice for releasing stale locks in Granite.  If someone is holding a lock for more than a day, they have most likely forgotten about it.  You can run the attached SQL script, release_stale_locks.sql, from cron nightly to remove any locks that are more than a day old.


            --
--  This script scans all of the locks in Granite and releases any locks
--  that have been in place for more than 24 hrs.
--
SET serveroutput ON size unlimited
spool release_dayold_locks.log
--
--  Check for Site Locks
--
DECLARE
  lockCnt NUMBER(9,0);
  thisSite site_inst%rowtype;
  CURSOR cSite
  IS
    SELECT * FROM site_inst WHERE lock_ts < (SYSDATE - 1) ;
BEGIN
  lockCnt := 0 ;
  --
  OPEN cSite ;
  LOOP
    FETCH cSite INTO thisSite ;
    EXIT
  WHEN cSite%notfound ;
    --
    dbms_output.put_line('Releasing Site lock on ' || thisSite.site_hum_id || ' by ' || thisSite.lock_by || ' since ' || TO_CHAR( thisSite.lock_ts, 'DD-MON-YYYY HH24:MI'));
    UPDATE site_inst
    SET lock_ts        = NULL,
      lock_by          = NULL
    WHERE site_inst_id = thisSite.site_inst_id ;
    INSERT
    INTO inst_audit
      (
        element_type,
        inst_id,
        version_chgd,
        edit_operation,
        chg_ts,
        chg_by,
        chg_desc
      )
      VALUES
      (
        'I',
        thisSite.site_inst_id,
        thisSite.inst_ver,
        'B',
        SYSDATE,
        'Auto-unlock',
        'Unlocked stale lock'
      );
    lockCnt := lockCnt + 1;
  END LOOP;
  CLOSE cSite ;
  dbms_output.put_line( lockCnt || ' Site locks released') ;
  COMMIT;
END ;
/
--
--  Check for Equipment Locks
--
DECLARE
  lockCnt NUMBER(9,0);
  siteName site_inst.site_hum_id%type;
  thisEq equip_inst%rowtype;
  CURSOR cEq
  IS
    SELECT s.site_hum_id,
      q.equip_inst_id,
      q.lock_by,
      q.lock_ts,
      q.descr,
      q.inst_ver
    FROM equip_inst q,
      site_inst s
    WHERE q.lock_ts    < (SYSDATE - 1)
    AND s.site_inst_id = q.site_inst_id;
BEGIN
  lockCnt := 0 ;
  --
  OPEN cEq ;
  LOOP
    FETCH cEq
    INTO siteName,
      thisEq.equip_inst_id,
      thisEq.lock_by,
      thisEq.lock_ts,
      thisEq.descr,
      thisEq.inst_ver ;
    EXIT
  WHEN cEq%notfound ;
    --
    dbms_output.put_line('Releasing Eqpt lock on ' || siteName || ' - ' || thisEq.descr || ' by ' || thisEq.lock_by || ' since ' || TO_CHAR( thisEq.lock_ts, 'DD-MON-YYYY HH24:MI'));
    UPDATE equip_inst
    SET lock_ts         = NULL,
      lock_by           = NULL
    WHERE equip_inst_id = thisEq.equip_inst_id ;
    INSERT
    INTO inst_audit
      (
        element_type,
        inst_id,
        version_chgd,
        edit_operation,
        chg_ts,
        chg_by,
        chg_desc
      )
      VALUES
      (
        'Q',
        thisEq.equip_inst_id,
        thisEq.inst_ver,
        'B',
        SYSDATE,
        'Auto-unlock',
        'Unlocked stale lock'
      );
    lockCnt := lockCnt + 1;
  END LOOP;
  CLOSE cEq ;
  dbms_output.put_line( lockCnt || ' Equipment locks released') ;
  COMMIT;
END ;
/
--
--  Check for Segment Locks
--
DECLARE
  lockCnt NUMBER(9,0);
  thisSeg circ_inst%rowtype;
  CURSOR cCirc
  IS
    SELECT * FROM circ_inst WHERE lock_ts < (SYSDATE - 1) ;
BEGIN
  lockCnt := 0 ;
  --
  OPEN cCirc ;
  LOOP
    FETCH cCirc INTO thisSeg ;
    EXIT
  WHEN cCirc%notfound ;
    --
    dbms_output.put_line('Releasing Segment lock on ' || thisSeg.circ_hum_id || ' by ' || thisSeg.lock_by || ' since ' || TO_CHAR( thisSeg.lock_ts, 'DD-MON-YYYY HH24:MI'));
    UPDATE circ_inst
    SET lock_ts        = NULL,
      lock_by          = NULL
    WHERE circ_inst_id = thisSeg.circ_inst_id ;
    INSERT
    INTO inst_audit
      (
        element_type,
        inst_id,
        version_chgd,
        edit_operation,
        chg_ts,
        chg_by,
        chg_desc
      )
      VALUES
      (
        'S',
        thisSeg.circ_inst_id,
        thisSeg.inst_ver,
        'B',
        SYSDATE,
        'Auto-unlock',
        'Unlocked stale lock'
      );
    lockCnt := lockCnt + 1;
  END LOOP;
  CLOSE cCirc ;
  dbms_output.put_line( lockCnt || ' Segment locks released') ;
  COMMIT;
END ;
/
--
--  Check for Circuit Path Locks
--
DECLARE
  lockCnt NUMBER(9,0);
  thisPath circ_path_inst%rowtype;
  CURSOR cCirc
  IS
    SELECT * FROM circ_path_inst WHERE lock_ts < (SYSDATE - 1) ;
BEGIN
  lockCnt := 0 ;
  --
  OPEN cCirc ;
  LOOP
    FETCH cCirc INTO thisPath ;
    EXIT
  WHEN cCirc%notfound ;
    --
    dbms_output.put_line('Releasing Path/Network lock on ' || thisPath.circ_path_hum_id || ' by ' || thisPath.lock_by || ' since ' || TO_CHAR( thisPath.lock_ts, 'DD-MON-YYYY HH24:MI'));
    UPDATE circ_path_inst
    SET lock_ts             = NULL,
      lock_by               = NULL
    WHERE circ_path_inst_id = thisPath.circ_path_inst_id ;
    INSERT
    INTO inst_audit
      (
        element_type,
        inst_id,
        version_chgd,
        edit_operation,
        chg_ts,
        chg_by,
        chg_desc
      )
      VALUES
      (
        DECODE( thisPath.path_class, 'N', 'K', 'P'),
        thisPath.circ_path_inst_id,
        thisPath.inst_ver,
        'B',
        SYSDATE,
        'Auto-unlock',
        'Unlocked stale lock'
      );
    lockCnt := lockCnt + 1;
  END LOOP;
  CLOSE cCirc ;
  dbms_output.put_line( lockCnt || ' circuit path/network locks released') ;
  COMMIT;
END ;
/
--
--  Check for Cable Locks
--
DECLARE
  lockCnt NUMBER(9,0);
  thisCable cable_inst%rowtype;
  CURSOR cCable
  IS
    SELECT * FROM cable_inst WHERE lock_ts < (SYSDATE - 1) ;
BEGIN
  lockCnt := 0 ;
  --
  OPEN cCable ;
  LOOP
    FETCH cCable INTO thisCable ;
    EXIT
  WHEN cCable%notfound ;
    --
    dbms_output.put_line('Releasing Cable lock on ' || thisCable.cable_name || ' by ' || thisCable.lock_by || ' since ' || TO_CHAR( thisCable.lock_ts, 'DD-MON-YYYY HH24:MI'));
    UPDATE cable_inst
    SET lock_ts         = NULL,
      lock_by           = NULL
    WHERE cable_inst_id = thisCable.cable_inst_id ;
    INSERT
    INTO inst_audit
      (
        element_type,
        inst_id,
        version_chgd,
        edit_operation,
        chg_ts,
        chg_by,
        chg_desc
      )
      VALUES
      (
        'B',
        thisCable.cable_inst_id,
        thisCable.inst_ver,
        'B',
        SYSDATE,
        'Auto-unlock',
        'Unlocked stale lock'
      );
    lockCnt := lockCnt + 1;
  END LOOP;
  CLOSE cCable ;
  dbms_output.put_line( lockCnt || ' cable locks released') ;
  COMMIT;
END ;
/
--
--  Check for Customer Locks
--
DECLARE
  lockCnt NUMBER(9,0);
  thisCust val_customer%rowtype;
  CURSOR cCust
  IS
    SELECT * FROM val_customer WHERE lock_ts < (SYSDATE - 1) ;
BEGIN
  lockCnt := 0 ;
  --
  OPEN cCust ;
  LOOP
    FETCH cCust INTO thisCust ;
    EXIT
  WHEN cCust%notfound ;
    --
    dbms_output.put_line('Releasing Customer lock on ' || thisCust.customer_id || ' by ' || thisCust.lock_by || ' since ' || TO_CHAR( thisCust.lock_ts, 'DD-MON-YYYY HH24:MI'));
    UPDATE val_customer
    SET lock_ts        = NULL,
      lock_by          = NULL
    WHERE cust_inst_id = thisCust.cust_inst_id ;
    INSERT
    INTO inst_audit
      (
        element_type,
        inst_id,
        version_chgd,
        edit_operation,
        chg_ts,
        chg_by,
        chg_desc
      )
      VALUES
      (
        'M',
        thisCust.cust_inst_id,
        thisCust.inst_ver,
        'B',
        SYSDATE,
        'Auto-unlock',
        'Unlocked stale lock'
      );
    lockCnt := lockCnt + 1;
  END LOOP;
  CLOSE cCust ;
  dbms_output.put_line( lockCnt || ' Customer locks released') ;
  COMMIT;
END ;
/
--
--  Check for AMO Locks
--
DECLARE
  lockCnt NUMBER(9,0);
  thisAMO resource_inst%rowtype;
  CURSOR cAMO
  IS
    SELECT * FROM resource_inst WHERE lock_ts < (SYSDATE - 1) ;
BEGIN
  lockCnt := 0 ;
  --
  OPEN cAMO ;
  LOOP
    FETCH cAMO INTO thisAMO ;
    EXIT
  WHEN cAMO%notfound ;
    --
    dbms_output.put_line('Releasing AMO lock on ' || thisAMO.name || ' by ' || thisAMO.lock_by || ' since ' || TO_CHAR( thisAMO.lock_ts, 'DD-MON-YYYY HH24:MI'));
    UPDATE resource_inst
    SET lock_ts            = NULL,
      lock_by              = NULL
    WHERE resource_inst_id = thisAMO.resource_inst_id ;
    INSERT
    INTO inst_audit
      (
        element_type,
        inst_id,
        version_chgd,
        edit_operation,
        chg_ts,
        chg_by,
        chg_desc
      )
      VALUES
      (
        '1',
        thisAMO.resource_inst_id,
        thisAMO.inst_ver,
        'B',
        SYSDATE,
        'Auto-unlock',
        'Unlocked stale lock'
      );
    lockCnt := lockCnt + 1;
  END LOOP;
  CLOSE cAMO ;
  dbms_output.put_line( lockCnt || ' AMO locks released') ;
  COMMIT;
END ;
/
spool OFF