DDevelopment & Design

Solving package dependency problem using synonyms

Problem I take part in supporting an ODS system. One of the biggest problem during deployment of revisions is the dependency between packages. Suppose a package A has to be changed and that it is referenced be two packages: B1 and B2 which are used in some database sessions. If the the A package is altered and it contained some global variables then the next execution of the packages B1 or B2 in those sessions triggers “ORA-04061: existing state of package has been invalidated” error. This is because global variables preserve their values during database sessions. Alteration of the package code may change the variables, delete some of them, add others. Therefore, they have to be initialized again. Reseting the global variables during the session could lead to inconsistency. In order to prevent this. Oracle throws the ORA-04061 exception. This mechanism is described in details on the page 

http://oraclequirks.blogspot.com/2007/03/ora-04061-existing-state-of-package-has.html

In addition, alteration of the package body or package specification is not possible as long as it used in a session. The replacing package DDL (CREATE OR REPLACE PACKAGE [BODY]) waits until no session uses the package. This may never happen – if new session starts using package before the previous one stops doing that. In order to change the package – all sessions using it must be killed. It is quite common that a session cannot be killed right now, but is moved into MARKED FOR KILLED state. As a result, the deployment of new package version is postponed for indefinite period of time until the session is really killed. I even created a script which kills all sessions which blocks any session started from my computer by my OS user. The script is really crude but it works and is really useful so I am posting it here.

DECLARE
  CURSOR c_sql_hist(pp_machine VARCHAR2, pp_osuser VARCHAR2) IS
    SELECT ash.blocking_session, ash.blocking_session_status, ash.blocking_session_serial#, s.program, s.module, s.action
      FROM v$active_session_history ash
      JOIN v$session s ON ash.blocking_session = s.sid
                      AND ash.blocking_session_serial# = s.serial#
     WHERE ash.session_id in ( select sid from v$session where machine = pp_machine
     and osuser = pp_osuser )
       AND ash.sample_time > SYSDATE - 2 / 24 / 3600
     ORDER BY sample_time DESC;
  v_r c_sql_hist%ROWTYPE;

  v_my_machine v$session.MACHINE%TYPE;
  v_my_osuser v$session.osuser%TYPE;
BEGIN
  select machine, osuser into v_my_machine, v_my_osuser from v$session
  where sid = ( Select Sid from v$mystat where rownum = 1);

  for i in 1..10000 loop  -- just to prevent infinite execution
    dbms_output.put_line('step: ' || i );
    OPEN c_sql_hist(v_my_machine, v_my_osuser);
    FETCH c_sql_hist
      INTO v_r;
    IF NOT c_sql_hist%NOTFOUND THEN
      dbms_output.put_line(v_r.blocking_session || ' ' || v_r.blocking_session_status || ' ' || v_r.blocking_session_serial# || ' ' || v_r.program || ' ' ||
                           v_r.module || ' ' || v_r.action);
      execute immediate ' ALTER SYSTEM KILL SESSION ''' || v_r.blocking_session || ',' || v_r.blocking_session_serial# || '''';
    else
      exit;
    END IF;
    CLOSE c_sql_hist;
    dbms_lock.sleep(0.5); -- sleep is used to get new blocking sessions
  end loop;
END;

In order to reduce the dependency problems described above, various tool packages, which should be located in one central schema, were copied to all schemas. As a result, they may be changed without killing all (only some) processes. However, this solution is terrible from the point of view of code maintenance.

Solution

The solution to this problem is to reference packages by synonyms. If the package need to be changed – a new version of it is installed with a different name. After it is tested – the synonym is altered to point the new version. The nearest execution of this synonym within a session causes the new package to be executed. Its global variables are initialized, their previous values are lost but this is not a problem in our ODS as they work as a cache and does not hold any state between executions. There is an additional limitation that one procedure or package cannot be executed concurrently is its instances use different object through the same synonym. Suppose that after B1 is executed, the A synonym is changed to point to A_v2. If B1 is executed again (directly or indirectly) then the second execution of B1 will not start before the old execution using the old package is finished. However, this is not really a problem. The solution described above allows to deploy database code efficiently. It is however necessary to remember how this mechanism works and what the limitations are. A naming convention needs also to be developed (for example adding the result of TO_CHAR(sysdate, ‘$MM_DD’), which is dollar sing, month and day of deployment, to the package name). It is also necessary to give other schemas the same grant to new package version as were given to the old package version.

— Previous article

<!--:en-->Complex flows with Apache Camel<!--:-->

Next article —

<!--:en-->Accessing Microsoft SQL Server from Oracle<!--:-->

You May Also Like
DDevelopment & Design

Simple trick to DRY your Grails controller

Grails controllers are not very DRY. It's easy to find duplicated code fragments in default generated controller. Take a look at code sample below. It is duplicated four times in show, edit, update and delete actions:

class BookController {
def show() {
def bookInstance = Book.get(params.id)
if (!bookInstance) {
flash.message = message(code: 'default.not.found.message', args: [message(code: 'book.label', default: 'Book'), params.id])
redirect(action: "list")
return
        }
        [bookInstance: bookInstance]
    }
}

Why is it duplicated?

There is a reason for that duplication, though. If you move this snippet to a method, it can redirect to "list" action, but it can't prevent controller from further execution. After you call redirect, response status changes to 302, but after method exits, controller still runs subsequent code.

Solution

At TouK we've implemented a simple trick to resolve that situation:

  1. wrap everything with a simple withStoppingOnRender method,
  2. whenever you want to render or redirect AND stop controller execution - throw EndRenderingException.

We call it Big Return - return from a method and return from a controller at once. Here is how it works:

class BookController {
def show(Long id) {
withStoppingOnRender {
Book bookInstance = Book.get(id)
validateInstanceExists(bookInstance)
            [bookInstance: bookInstance]
        }
    }

protected Object withStoppingOnRender(Closure closure) {
try {
return closure.call()
        } catch (EndRenderingException e) {}
    }

private void validateInstanceExists(Book instance) {
if (!instance) {
flash.message = message(code: 'default.not.found.message', args: [message(code: 'book.label', default: 'Book'), params.id])
redirect(action: "list")
throw new EndRenderingException()
        }
    }
}

class EndRenderingException extends RuntimeException {}

Example usage

For simple CRUD controllers, you can use this solution and create some BaseController class for your controllers. We use withStoppingOnRender in every controller so code doesn't look like a spaghetti, we follow DRY principle and code is self-documented. Win-win-win! Here is a more complex example:

class DealerController {
@Transactional
def update() {
withStoppingOnRender {
Dealer dealerInstance = Dealer.get(params.id)
validateInstanceExists(dealerInstance)
validateAccountInExternalService(dealerInstance)
checkIfInstanceWasConcurrentlyModified(dealerInstance, params.version)
            dealerInstance.properties = params
saveUpdatedInstance(dealerInstance)
redirectToAfterUpdate(dealerInstance)
        }
    }
}