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:
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:
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