November 2004 - Posts

Oracle Team Development – Locking Oracle Objects

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 schema
declare
  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.

Of Delegates and Events

I have often read and heard explanations of the differences between a Delegate and Events in the .NET framework; I thought I would try my hand at explaining the relationship between delegates and events. I will not be addressing all the finer details; I will purposely ignore most of the metadata related to events. I will only describe as much as I require conveying the relationship between delegates and events. Please do not expect this to be an exhaustive review of how to work with delegates and events or for that matter how they work.

For the purposes of this explanation I will use C# and IL for the examples, but most aspects should be relevant for VB.NET or any other language targeting the .NET Framework that exposes the .NET event mechanism.

The delegate declaration defines a new data type; instances of this data type can maintain a list of zero or more methods; each method in the list must have a signature that matches the delegate data type declaration. Take the following declaration for example

public delegate void ValueChangedDelegate(object sender, EventArgs e);

This declares a new delegate type called ValueChangedDelegate. The type definition also defines the signature of the method that can be added to instances of this type. In this example the method must return void (function Sub in VB.NET) and accept object and EventArgs parameters.

The next code snippet declares a class called Foo that exposes a delegate as an instance variable called ValueHasChanged.

public class Foo
{
 
public ValueChangedDelegate ValueHasChanged;

  public void ChangeTheValue()
  {
   
if (ValueHasChanged != null)
      ValueHasChanged(
null, EventArgs.Empty);
  }
}

The above class pretty much addresses everything we would need to put together an event mechanism. We can add handlers to the delegate instance and at the appropriate time we can invoke the delegate to fire the handlers. In the sample code the event is fired when ever the method ChangeTheValue is called, of course in a real application it would do more that just invoke the delegate and in we would need to take extra precautions to prevent race conditions.

The following snippet demonstrates subscribing to our new "event" (note the quotes).

static void Main()
{
  Foo myFoo =
new Foo(); 
  myFoo.ValueHasChanged =
new ValueChangedDelegate(OnValueChanged);
}

static void OnValueChanged(object sender, EventArgs e)
{
  System.Diagnostics.Debug.WriteLine("It fired!!!!");
}

Our Foo class is instantiated and the reference stored in the variable myFoo, following that we create an instance of our delegate passing a method to the constructor; I will refer to this method as the handler method. The method OnValueChanged’s signature matches that required by our delegate. The newly created delegate instance is then assigned to the ValueHasChanged instance variable of the myFoo instance. Now when ever the delegate myFoo.ValueHasChanged is invoked the static method OnValueChanged will be called. Note that even though I have used static methods here neither events or delegates are limited to using static methods. At this point potential subscribers are free to add there handler functions to the myFoo.ValueChangedDelegate and these will be called when ever the delegate is invoked.

What has been described thus far addresses what we need to create and handle events. And in terms of pure functionality delegates do fit the bill here. The problem with we have so far is with the accessibility of the delegate, as it stands anybody using and instance of the Foo class can subscribe to the "event", but also just as easily invoke the delegate directly artificially firing the all handlers into action. The following code shows this.

Foo myFoo = new Foo();
myFoo.ValueHasChanged =
new ValueChangedDelegate(new ValueChangedDelegate(OnValueChanged));

// Even though the value has not change I am going to trick everyone
// into thinking it has by firing the event
if (myFoo.ValueHasChanged != null)
  myFoo.ValueHasChanged(
null, EventArgs.Empty);

Clearly we would not want our "event" fired at any arbitrary time, so we go to the drawing board and think through the problem and come up with the following solution.

public class Foo
{
 
private ValueChangedDelegate _valueHasChanged;
 
public void AddValueChangedHandler(ValueChangedDelegate handler)
  {
    _valueHasChanged += handler;
  }

  public void RemoveValueChangedHandler(ValueChangedDelegate handler)
  {
    _valueHasChanged -= handler;
  }

  public void ChangeTheValue()
  { 

   
if (_valueHasChanged != null)
      _valueHasChanged(
null, EventArgs.Empty);
  }
}

This solution does not look to bad, it can be improved, but the goal has been achieved we limit the access to the delegate while still allowing subscribers to subscribe and unsubscribe, while only the class itself can actually fire the event. The two problems with this solution is the amount of code we have to write. And when we make improvements to what we have, and we can/should make some, we need to go address all our hand rolled code, especially problematic after we have defined a number of delegates in many classes. We have also lost our ability to use the += and -= operator to subscribe and unsubscribe.

This is where events come into the picture; a .NET event formalizes and improves on the above pattern with the advantage of less typing and a nicer syntax for adding and removing handler functions. The following class definition uses the event modifier (yes modifier) to expose the delegate.

public class Foo2
{
  public event ValueChangedDelegate ValueHasChanged;

  public void ChangeTheValue()
  {
    if (ValueHasChanged != null)
      ValueHasChanged(
null, EventArgs.Empty);
  }
}

The new version of our class Foo2 now exposes the ValueHasChanged delegate as an event. This effect of this is that the compiler emits code that is very similar to the code we generated by hand earlier.

If you use ILDASM to inspect the code you will see that a private member has been declared.

.field private class WindowsApplication1.ValueChangedDelegate ValueHasChanged

This declaration is followed by the two compiler generated methods add_ValueHasChanged and remove_ValueHasChanged. These members correspond to our earlier solutions methods, AddValueChangedHandler and RemoveValueChangedHandler. The following is the IL declaration for the add_ValueHasChanged and remove_ValueHasChanged method.

.method public hidebysig specialname instance void add_ValueHasChanged (class Sample.ValueChangedDelegate 'value') cil managed synchronized

.method public hidebysig specialname instance void remove_ValueChangedAsEvent(class WindowsApplication1.ValueChangedDelegate 'value') cil managed synchronized

I am not going to delve into the rest of the metadata that is emitted for events, but it should be clear that by adding the event modifier to the delegate member we have modified how the delegate is exposed to users of the class just like with our hand rolled version, with two added benefits

· Less coding

· More consistent syntax for subscribing to events

· Thread safe subscription to events

The second and last points are support added by the compiler, whereby the subscriber can use the += operator to subscribe to the event and the compiler will resolve this to a call to add_ValueHasChanged and -= will resolve to remove_ValueHasChanged. As for thread safety, calling the add_ValueHasChanged and remove_ValueHasChanged methods is done in a thread safe manner. Look at the IL declarations for these methods you will notice the synchronized flag, this flag specifies that the method can only be called from one thread at a time, still more functionality that we would have to add to our hand rolled solution.

In summary I leave you with the following:

Events are not like delegates, they ARE delegates. The event modifier defines how these delegates are published to subscribers.