Semaphores (revisited)

In a previous blog I talked about turning triggers on and off using semaphores. I created a semaphore for every table I needed along with its programs to set and unset the semaphore and to get its current state. This was a rather cumbersome process, because for every new trigger I needed to add these programs to the package. Both the spec and the body need to be recompiled which renders all dependant code to become invalid. This week I came across a similar problem with a new project. I figured I could use roughly the same solution but I needed to find a better way to implement it.

I want the solution to be portable, independent of the schema on which it is deployed and flexible so I can use it as many times as I want. 9i unbreakableActually I want the list of semaphores to be unlimited and I want to be able to reference them by name. Rereading this requirement pointed me in the direction of collections. I have a choice of several collection types (Varray, Nested Tables and Associative Arrays). The first two can also be created in the database as types, the latter just lives inside PL/SQL. Another requirement is that the items should be referenced using a name, rather than a number. Luckily Oracle provides the possibility to index an associative array by varchar2 since version 9iR2. I have never really used this feature until now.

I have come up with the following specification for the package: I must be able to set and clear a semaphore as well as check it’s current status. And the semaphore must not be unset if it is set twice and cleared once. This made me use a number instead of a boolean value to set and clear the semaphore. I have blogged about using a boolean that is really a number earlier. Where I used a separate semaphore for every semaphore I needed, along with it’s own code, variable etc, I know have the possibility to create as many semaphores as I want, without having to change the code.

What if I don’t supply a name for the semaphore at all (or call the set or clear procedure with a NULL-value parameter). In the first version I created this resulted in an error (NULL index….<<find exact error and perhaps link to error>>) so I decided to automagically create a name for this situation. Saving this name into a package variable makes it possible to reuse it, so all subsequent call to the set and clear procedures will use the same semaphore.

I decided to use a GUID for the name, which should result in a unique name for the semaphore. Chances this name is already used by some other semaphore are nearly zero. GUID stands for Globally Unique IDentifier. Steven Feuerstein blogged about the GUIDs before, but in this case it’s more of an escape route, so chances that we will have doubles are zero, especially because it will be generated only once per session. Normally the semaphores will be set and cleared using a name that will hopefully be unique. A good idea for a semaphore name is an object name. For instance, when I want to skip part of the code a trigger, based on a semaphore, I would use the tablename as the name for the semaphore.

Since the package is loaded only once per session, it might be a good idea to populate the escape name in the initialization part of the package, but I decided not to do this, since most of the time you don’t want a name created by the package, because you are providing your own name. This is the correct way to do things and populating the name anyway, would just create useless overhead.

If you are using a lot of semaphores in your code, please take into account that they are all stored in memory. If there are a lot of users/sessions connected at a certain time, this could cause a problem where the program is using up a lot of memory. That is why, when the semaphores is cleared as many times as it is set, it will be removed from the Associative Array to free up the associated memory.

The code is definitely not complete and open for improvements, but here it is.

   1: CREATE OR REPLACE PACKAGE amis_semaphores
   2: IS
   3:   -- Author  : PATRICK BAREL
   4:   -- Purpose : Semaphores to turn things on and off
   5:  
   6:   -- Public type declarations
   7:   -- The actual semaphoretype
   8:   SUBTYPE semaphore_t IS PLS_INTEGER;
   9:  
  10:   -- Public constant declarations
  11:  
  12:   -- Public variable declarations
  13:  
  14:   -- Public function and procedure declarations
  15:   FUNCTION issemaphoreset(
  16:     semaphorename_in               IN      VARCHAR2 default null )
  17:     RETURN BOOLEAN;
  18:  
  19:   PROCEDURE setsemaphore(
  20:     semaphorename_in               IN      VARCHAR2 default null );
  21:  
  22:   PROCEDURE clrsemaphore(
  23:     semaphorename_in               IN      VARCHAR2 default null );
  24: END amis_semaphores;
  25: /

   1: CREATE OR REPLACE PACKAGE BODY amis_semaphores
   2: IS
   3:   -- Private type declarations
   4:   -- The type to index the AA by.
   5:   SUBTYPE semaphore_index_t IS VARCHAR2( 100 );
   6:   -- The AA of semaphores
   7:   TYPE semaphore_ct IS TABLE OF semaphore_t
   8:     INDEX BY semaphore_index_t;
   9:  
  10:   -- Private constant declarations
  11:  
  12:   -- Private variable declarations
  13:   -- The variable to hold the collection of semaphores
  14:   g_semaphores                             semaphore_ct;
  15:   -- The variable to hold the internal name if no name is specified.
  16:   g_internalname                           semaphore_index_t;
  17:   -- Function and procedure implementations
  18: /******************************************************************************\
  19: || procedure   : initialization
  20: || parameters  : 
  21: ||               
  22: || purpose     : Initialize the package variables. This runs when the package is
  23: ||               first loaded.
  24: ||               
  25: || author      : Patrick Barel
  26: || created     : 2/25/2009
  27: || (C) 2009    : AMIS Services
  28: \******************************************************************************/
  29:   PROCEDURE initialization
  30:   IS
  31:   BEGIN
  32:     -- empty the entire collection
  33:     g_semaphores.DELETE;
  34:   END initialization;
  35:  
  36: /******************************************************************************\
  37: || function    : getinternalname
  38: || parameters  : <none>
  39: ||               
  40: || purpose     : Return a name generated by a GUID. If it has already been
  41: ||               generated before, then return that name.
  42: ||               
  43: || author      : Patrick Barel
  44: || created     : 5/6/2009
  45: || (C) 2009    : AMIS Services
  46: \******************************************************************************/
  47:   function getinternalname return semaphore_index_t
  48:   is
  49:     l_returnvalue semaphore_index_t;
  50:   begin
  51:     if g_internalname is null then
  52:       g_internalname := SYS_GUID; -- generate a name based on a GUID
  53:     end if;
  54:     l_returnvalue := g_internalname;
  55:     RETURN l_returnvalue;
  56:   end getinternalname;
  57: /******************************************************************************\
  58: || procedure   : tryaaitem
  59: || parameters  : semaphorename_in               IN      VARCHAR2
  60: ||               
  61: || purpose     : try to get the value from a semaphore item. If this fails, then
  62: ||               the item doesn't exist and it should be created.
  63: ||               
  64: || author      : Patrick Barel
  65: || created     : 2/25/2009
  66: || (C) 2009    : AMIS Services
  67: \******************************************************************************/
  68:   procedure tryaaitem(
  69:     semaphorename_in               IN      VARCHAR2 )
  70:   is
  71:     ldummy                                   semaphore_t;
  72:   BEGIN
  73:     BEGIN
  74:       -- try to get the value from the global
  75:       -- just to mimic a try..except..end construction
  76:       ldummy   := g_semaphores( semaphorename_in );
  77:     EXCEPTION
  78:       WHEN NO_DATA_FOUND THEN
  79:         -- if it doesn't exist, then create it
  80:         g_semaphores( semaphorename_in )   := null;
  81:       WHEN OTHERS THEN
  82:         RAISE;
  83:     END;
  84:   end tryaaitem;
  85: /******************************************************************************\
  86: || procedure   : inc
  87: || parameters  : value_inout                 IN OUT  PLS_INTEGER
  88: ||               
  89: || purpose     : Increase value_inout by 1
  90: ||               
  91: || author      : Patrick Barel
  92: || created     : 2/24/2009
  93: || (C) 2009    : AMIS Services
  94: \******************************************************************************/
  95:   PROCEDURE inc(
  96:     value_inout                    IN OUT  PLS_INTEGER )
  97:   IS
  98:   BEGIN
  99:     value_inout   := NVL( value_inout, 0 ) + 1;
 100:   END inc;
 101:  
 102: /******************************************************************************\
 103: || procedure   : dec
 104: || parameters  : value_inout                 IN OUT  PLS_INTEGER
 105: ||               
 106: || purpose     : Decrease value_inout by 1
 107: ||               
 108: || author      : Patrick Barel
 109: || created     : 2/24/2009
 110: || (C) 2009    : AMIS Services
 111: \******************************************************************************/
 112:   PROCEDURE dec(
 113:     value_inout                    IN OUT  PLS_INTEGER )
 114:   IS
 115:   BEGIN
 116:     value_inout   := NVL( value_inout, 0 ) - 1;
 117:   END DEC;
 118:  
 119: /******************************************************************************\
 120: || function    : issemaphoresert
 121: || parameters  : semaphorename_in               IN      VARCHAR2
 122: ||               
 123: || purpose     : Check if a semaphore by the name of semaphorename_in is set
 124: ||               
 125: || author      : Patrick Barel
 126: || created     : 2/24/2009
 127: || (C) 2009    : AMIS Services
 128: \******************************************************************************/
 129:   FUNCTION issemaphoreset(
 130:     semaphorename_in               IN      VARCHAR2 )
 131:     RETURN BOOLEAN
 132:   IS
 133:     l_returnvalue                            BOOLEAN;
 134:     l_semaphorename semaphore_index_t;
 135:   BEGIN
 136:     -- if a null value is given, then create a guid, or use the last one
 137:     l_semaphorename := nvl(semaphorename_in, getinternalname);
 138:     BEGIN
 139:       l_returnvalue   :=( g_semaphores( l_semaphorename ) > 0 );
 140:     EXCEPTION
 141:       WHEN OTHERS THEN
 142:         l_returnvalue   := FALSE;
 143:     END;
 144:  
 145:     RETURN l_returnvalue;
 146:   END issemaphoreset;
 147:  
 148: /******************************************************************************\
 149: || procedure   : setsemaphore
 150: || parameters  : semaphorename_in               IN      VARCHAR2
 151: ||               
 152: || purpose     : set a semaphore by the name of semaphore_in, i.e. increase
 153: ||               it's number by 1
 154: ||               
 155: || author      : Patrick Barel
 156: || created     : 2/24/2009
 157: || (C) 2009    : AMIS Services
 158: \******************************************************************************/
 159:   PROCEDURE setsemaphore(
 160:     semaphorename_in               IN      VARCHAR2 )
 161:   IS
 162:     l_semaphorename semaphore_index_t;
 163:   BEGIN
 164:     -- if a null value is given, then create a guid, or use the last one
 165:     l_semaphorename := nvl(semaphorename_in, getinternalname);
 166:     tryaaitem( semaphorename_in => l_semaphorename );
 167:     inc( g_semaphores( l_semaphorename ));
 168:   END setsemaphore;
 169:  
 170: /******************************************************************************\
 171: || procedure   : clrsemaphore
 172: || parameters  : semaphorename_in               IN      VARCHAR2
 173: ||               
 174: || purpose     : clear a semaphore by the name of semaphore_in, i.e. decrease
 175: ||               it's number by 1. If it reaches the value of zero, then it is
 176: ||               removed from the associative array.
 177: ||               
 178: || author      : Patrick Barel
 179: || created     : 2/24/2009
 180: || (C) 2009    : AMIS Services
 181: \******************************************************************************/
 182:   PROCEDURE clrsemaphore(
 183:     semaphorename_in               IN      VARCHAR2 )
 184:   IS
 185:     l_semaphorename semaphore_index_t;
 186:   BEGIN
 187:     -- if a null value is given, then create a guid, or use the last one
 188:     l_semaphorename := nvl(semaphorename_in, getinternalname);
 189:     tryaaitem( semaphorename_in => l_semaphorename );
 190:     DEC( g_semaphores( l_semaphorename ));
 191:     g_semaphores( l_semaphorename ) := greatest(g_semaphores( l_semaphorename ),0);
 192:     -- remove if the semaphore is 0 i.e. cleared
 193:     if g_semaphores( l_semaphorename ) = 0 then
 194:       g_semaphores.delete( l_semaphorename );
 195:     end if;
 196:   END clrsemaphore;
 197: BEGIN
 198:   initialization;
 199: END amis_semaphores;
 200: /

Leave a Reply

Your email address will not be published.