Current source control systems such as Visual Source Safe, CVS, and Subversion is that they all rely on having a working copy of the source code under development on the developers local machine. Unfortunately this does not map quite so easily to the database environment. This is particularly problematic when multiple developers are working on database objects on the same server.
The Problem
The in our environment we are using Oracle as the backend database. One of the problems the Oracle developers are facing is when more than one of them is working on the same Package at the same time. Most tools allow you to extract the source for the Package edit the package and then compile it. It is at the compile stage that the problem occurs. If two developers edit the same package at the same time, the one that compiles last will overwrite the changes made by the other developer. Obviously there is another option, each developer could have his/her own database instance to work against and then use version control software to maintain the sources, but for this team this is how they work and since I have already thrown a spoke in the wheels by introducing Subversion I did not want to push things.
Finding a solution
With my focus on Microsoft Technologies, I am not particularly knowledgeable about Oracle, but I decided that I would take up the challenge to find a solution to this problem and learn a thing or two about Oracle along the way. Obviously the problem with piecing together a solution for a technology that you are not particularly knowledgeable about is that you run the risk of re-inventing the wheel. But assuming that the database developers are familiar with there tools there did not seem to be a satisfactory solution to the problem.
On of the few things I do know about Oracle is that they allow you to create triggers on the schema. These triggers are fired when changes are made to the existing schema. With this I decided I would attempt to piece together an object locking scheme where the developer could lock a database object before working on it and then once he has compiled and tested the object he can unlock it. To compile an object the object must be locked by the developer attempting the compile, if not a message should be displayed indicating that the object is either not locked or is locked by another developer. The solution should also be independent of the tools that are being used by the developer.
The solution
As it turns out the solution to this problem was rather easily implemented even with my limited knowledge of Oracle PL/SQL. I created a table that maintains a list of locked objects.
create
table DBSCC_LOCKS
(
OBJTYPE
varchar2(
19) not null,
OBJNAME varchar2(30) not null,
LOCKEDBY varchar2(64) not null,
constraint PK_DBSCC_LOCKS primary key (OBJTYPE, OBJNAME)
)
The table has the object type for instance PROCEDURE or PACKAGE etc. and the name of the object and who has the object locked. For my purposed I decided to use the machine name the complete solution actually uses the OS user for this purpose.
The next step was to provide a trigger that would check against the DBSCC_LOCKS table before allowing a object creation to proceed.
create
or replace trigger TR_DBSCC_CHANGE
before create on schemadeclare vMachine v$session.MACHINE%
TYPE;
vLockedBy DBSCC_LOCKS.LOCKEDBY%
TYPE;
vObjExists
integer;
begin -- Only locking PROCEDURES, PACKAGE and PACKAGE BODY
if ora_dict_obj_type in ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY') then
-- Get the machine name that is executing the current session
select s.machine into vMachine from v$session s
where s.audsid = userenv('sessionid');
-- Check if the object already exists
select count(*) into vObjExists from ALL_OBJECTS o
where o.object_type = ora_dict_obj_type
and o.object_name = ora_dict_obj_name;
-- If the object does not exist then we allow the
if vObjExists = 0 then
return;
end if;
-- Query the DBSCC_LOCKS table to see if and who
-- has the object locked
begin
select l.LOCKEDBY into vLockedBy from DBSCC_LOCKS l
where l.OBJTYPE = ora_dict_obj_type
and l.OBJNAME = ora_dict_obj_name;
exception
when NO_DATA_FOUND then
vLockedBy := null;
end;
-- Test the lock state of the object
if vLockedBy is null then -- Not locked
raise_application_error(-20001,
ora_dict_obj_type || '.' || ora_dict_obj_name || ' is not locked, please use DBSCC_LOCK before updating');
elsif vLockedBy <> vMachine then -- Locked from another workstation
raise_application_error(-20001,
ora_dict_obj_type || '.' || ora_dict_obj_name || ' cannot be created/updated because it is currently locked by ' || vLockedBy);
end if;
-- If we get here then the workstation owning the session
-- has the lock and the creation can proceed.
end if;
end;
Now this is where my lack of PL/SQL experience really shows itself, I feel that there are a number of things that can be improved but as it stands it works.
The trigger checks if the object being created is one of the types that we are expecting to be locked before creation. Then the machine name is extracted from the current session (the OS user could be used instead). The trigger checks that the object is an existing object; if it is further tests are done to check that the machine creating the object has a lock on the existing object. In the case that the object is locked by the machine issuing the create or this is the first time the object is being created the trigger allows the create to proceed. Otherwise an exception is raised indicating either that the object must be locked or that it is already locked from elsewhere. Now all that is left is to enable the developer to easily lock and unlock database objects.
The following procedure can be called by the developer to lock the intended database object.
create
or replace procedure DBSCC_LOCK(
p_objtype
in DBSCC_LOCKS.OBJTYPE%
TYPE,
p_objname
in DBSCC_LOCKS.OBJNAME%
TYPE)
is vLocks
integer;
vCanLock
integer;
vLockedBy DBSCC_LOCKS.LOCKEDBY%
TYPE;
vMachine v$session.MACHINE%
TYPE;
vObjType DBSCC_LOCKS.OBJTYPE%
TYPE;
vObjName DBSCC_LOCKS.OBJNAME%
TYPE;
begin vObjType := upper(
trim(p_objtype));
vObjName := upper(
trim(p_objname));
-- Validate object type
if not vObjType in ('PROCEDURE', 'FUNCTION', 'PACKAGE') then
raise_application_error(-20001, 'Only objects of type PROCEDURE, FUNCTION and PACKAGE can be locked');
end if;
-- Validate object that the object exists
select count(*) into vCanLock
from ALL_OBJECTS o
where o.object_type = vObjType
and o.object_name = vObjName;
if vCanLock = 0 then
raise_application_error(-20001, vObjType || '.' || vObjName || ' does not exist');
end if;
-- Assume the object is already locked
vLocks := 1;
-- Query the locks table to determine if and who
-- has the object locked.
begin
select scc.LOCKEDBY into vlockedBy
from DBSCC_LOCKS scc
where scc.OBJTYPE = vObjType
and scc.OBJNAME = vObjName;
exception
-- NO_DATA_FOUND implies that the object is not locked
when NO_DATA_FOUND then
vLocks := 0;
end;
-- If the object is locked inform the caller that the object is
-- already locked
if (vLocks > 0) then
raise_application_error(-20001, vObjType || '.' || vObjName || ' is locked by ' || vLockedBy);
else
-- Get the name of the workstation requesting the lock
select s.MACHINE into vMachine
from v$session s
where audsid = userenv('sessionid');
-- Special case PACKAGE objects so that both the
-- PACKAGE and PACKAGE BODY are locked
if vObjType = 'PACKAGE' then
insert into DBSCC_LOCKS
(OBJTYPE, OBJNAME, LOCKEDBY)
values
('PACKAGE', vObjName, vMachine);
insert into DBSCC_LOCKS
(OBJTYPE, OBJNAME, LOCKEDBY)
values
('PACKAGE BODY', vObjName, vMachine);
else
insert into DBSCC_LOCKS
(OBJTYPE, OBJNAME, LOCKEDBY)
values
(vObjType, vObjName, vMachine);
end if;
commit;
end if;
end DBSCC_LOCK;
The procedure mostly just checks that the object is not already locked and if not makes an entry into the DBSCC_LOCKS table. One special case is for packages, if a package is locked an entry is made for both the package and the package body. The reciprocal of this procedure is the DBSCC_UNLOCK, this procedure is used to unlock a database object.
create
or replace procedure DBSCC_UNLOCK(
p_objtype
in DBSCC_LOCKS.OBJTYPE%
TYPE,
p_objname
in DBSCC_LOCKS.OBJNAME%
TYPE,
p_force
in integer :=
0)
is
vMachine v$session.MACHINE%TYPE;
vObjType DBSCC_LOCKS.OBJTYPE%TYPE;
vObjName DBSCC_LOCKS.OBJNAME%TYPE;
vLocks integer;
begin
vObjType := upper(trim(p_objtype));
vObjName := upper(trim(p_objname));
-- Validate object type
if not vObjType in ('PROCEDURE', 'FUNCTION', 'PACKAGE') then
raise_application_error(-20001, 'Only objects of type PROCEDURE, FUNCTION and PACKAGE are supported');
end if;
-- Get the name of the workstation executing the request
select s.machine into vMachine
from v$session s
where audsid = userenv('sessionid');
-- Determine if the object is locked and if it is locked
-- by the caller, if p_force is non-zero then the machine
-- is ignored
select count(*) into vLocks
from DBSCC_LOCKS l
where l.OBJTYPE = vObjType
and l.OBJNAME = vObjName
and (l.LOCKEDBY = vMachine or p_force <> 0);
-- If locked then remove the entry from the DBSCC_LOCKS
-- table. PACKAGE and PACKAGE BODY are special cased
if vLocks > 0 then
if vObjType = 'PACKAGE' then
delete from DBSCC_LOCKS
where OBJTYPE in ('PACKAGE', 'PACKAGE BODY')
and OBJNAME = vObjName;
else
delete from DBSCC_LOCKS
where OBJTYPE = vObjType
and OBJNAME = vObjName;
end if;
commit;
else
raise_application_error(-20001, 'You do not have ' || vObjType || '.' || vObjName ||' locked');
end if;
end DBSCC_UNLOCK;
DBSCC_UNLOCK does pretty much the reverse, it checks that the machine issuing the unlock in fact has the object locked and if so removes the entry from the DBSCC_LOCKS table. Again there is a special case for packages, where unlocking a package removes both the entry for the PACKAGE and PACKAGE BODY. Additionally the DBSCC_UNLOCK procedure includes a force parameter which when set will force an object to be unlocked even if it is not currently locked by the user.
So a few questions, if I keep saying the OS User can be used rather than the machine name why did I opt to use the machine name. Well, this might just be ignorance, but I recall reading somewhere that OS User might not be reliable from all environments especially when connecting via SQL*NET. Since this solution has been released to the team they have provided some nice enhancements. One of those was integrating this with PL/SQL Developer, the environment predominantly used by the team members, now they can just right click on an object in the object browser and lock/unlock, in the case of the object being locked the context menu even shows who has it locked, they have also changed the procedures to use the OS User rather than the machine name, maybe I should make that change some time soon.
Hope fully those of you out there that have more Oracle expertise than I do will point out the short comings in my implementation as well as maybe a better or alternative solution to the problem.