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.

You May Also Like

33rd Degree day 2 review

Second day of 33rd had no keynotes, and thus was even more intense. A good conference is a conference, where every hour you have a hard dilemma, because there are just too many interesting presentations to see. 33rd was definitely such a conference, and the seconds day really shined.

There were two workshops going on through the day, one about JEE6 and another about parallel programming in Java. I was considering both, but decided to go for presentations instead. Being on the Spring side of the force, I know just as much JEE as I need, and with fantastic GPars (which has Fork/Join, actors, STM , and much more), I won't need to go back to Java concurrency for a while.

GEB - Very Groovy browser automation

Luke Daley works for Gradleware, and apart from being cheerful Australian, he's a commiter to Grails, Spock and a guy behind Geb, a  browser automation lib using WebDriver, similar to Selenium a bit (though without IDE and other features).

I have to admit, there was a time where I really hated Selenium. It just felt so wrong to be writing tests that way, slow, unproductive and against the beauty of TDD. For years I've been treating frontend as a completely different animal. Uncle Bob once said at a Ruby conference: "I'll tell you what my solution to frontend tests is: I just don't". But then, you can only go so far with complex GUIs without tests, and once I've started working with Wicket and its test framework, my perspective changed. If Wicked has one thing done right, it's the frontend testing framework. Sure tests are slow, on par with integration tests, but it is way better than anything where the browser has to start up front, and I could finally do TDD with it.

Working with Grails lately, I was more than eager to learn a proper way to do these kind of tests with Groovy.

GEB looks great. You build your own API for every page you have, using CSS selectors, very similar to jQuery, and then write your tests using your own DSL. Sounds a bit complicated, but assuming you are not doing simple HTML pages, this is probably the way to go fast. I'd have to verify that on a project though, since with frontend, too many things look good on paper and than fall out in code.

The presentation was great, Luke managed to answer all the questions and get people interested. On a side note, WebDriver may become a W3C standard soon, which would really easy browser manipulation for us. Apart from thing I expected Geb to have, there are some nice surprises like working with remote browsers (e.g. IE on remote machine), dumping HTML at the end of the test and even making screenshots (assuming you are not working with headless browser).

Micro services - Java, the Unix Way

James Lewis works for ThoughtWorks and gave a presentation, for which alone it was worth to go to Krakow. No, seriously, that was a gem I really didn't see coming. Let me explain what it was about and then why it was such a mind-opener.
ThoughtWorks had a client, a big investment bank, lots of cash, lots of requirements. They spent five weeks getting the analysis done on the highest possible level, without getting into details yet (JEDI: just enough design initially). The numbers were clear: it was enormous, it will take them forever to finish, and what's worse, requirements were contradictory. The system had to have all three guarantees of the CAP theorem, a thing which is PROVED to be impossible.
So how do you deal with such a request? Being ThoughtWorks you probably never say "we can't", and having an investment bank for a client, you already smell the mountains of freshly printed money. This isn't something you don't want to try, it's just scary and challenging as much as it gets.
And then, looking at the requirements and drawing initial architecture, they've reflected, that there is a way to see the light in this darkness, and not to end up with one, monstrous application, which would be hard to finish and impossible to maintain. They have analyzed flows of data, and came up with an idea.
What if we create several applications, each so small, that you can literally "fit it in your head", each communicating with a simple web protocol (Atom), each doing one thing and one thing only, each with it's own simple embedded web server, each working on it's own port, and finding out other services through some location mechanism. What if we don't treat the web as an external environment for our application, but instead build the system as if it was inside the web, with the advantages of all the web solutions, like proxies, caches, just adding a small queue before each service, to be able to turn it off and on, without loosing anything. And we could even use a different technology, with different pair of CAP guarantees, for each of those services/applications.
Now let me tell you why it's so important for me.
If you read this blog, you may have noticed the subtitle "fighting chaos in the Dark Age of Technology". It's there, because for my whole IT life I've been pursuing one goal: to be able to build things, that would be easy to maintain. Programming is a pure pleasure, and as long as you stay near the "hello world" kind of complexity, you have nothing but fun. If we ever feel burned out, demotivated or puzzled, it's when our systems grow so much, that we can no longer understand what's going on. We lose control. And from that point, it's usually just a way downward, towards complete chaos and pain.
All the architecture, all the ideas, practices and patterns, are there for just this reason - to move the border of complexity further, to make the size of "possible to fit in your head" larger. To postpone going into chaos. To bring order and understanding into our systems.
And that really works. With TDD, DDD, CQRS I can build things which are larger in terms of features, and simpler in terms of complexity. After discovering and understanding the methods (XP, Scrum/Kanbad) my next mental shift came with Domain Driven Design. I've learned the building block, the ideas and the main concept of Bounded Contexts. And that you can and should use a different architecture/tools for each of them, simplifying the code with the usage patterns of that specific context in your ming.
That has changed a lot in my life. No longer I have to choose one database, one language and one architecture for the whole application. I can divide and conquer, choose what I want to sacrifice and what advantages I want here, in this specific place of my app, not worrying about other places where it won't fit.
But there is one problem in here: the limit of technologies I'm using, to keep the system simple, and not require omnipotence to be able to maintain, to fix bugs or implement Change Requests.
And here is the accidental solution, ThoughtWorks' micro services bring: if you system is build of the web, of small services that do one thing only, and communicate through simple protocol (like Atom), there is little code to understand, and in case of bugs or Change Requests, you can just tear down one of the services. and build it anew.
James called that "Small enough to throw them away. Rewrite over maintain". Now, isn't that a brilliant idea? Say you have a system like that, build over seven years ago, and you've got a big bag of new requests from your client. Instead of re-learning old technologies, or paying extra effort to try to bring them up-to-date (which is often simply impossible), you decide which services you are going to rewrite using the best tools of your times, and you do it, never having to dig into the original code, except for specification tests.
Too good to be true? Well, there are caveats. First, you need DevOps in your teams, to get the benefits of the web inside your system, and to build in the we as opposite to against it. Second, integration can be tricky. Third, there is not enough of experience with this architecture, to make it safe. Unless... unless you realize, that UNIX was build this way, with small tools and pipes.
That, perhaps. is the best recommendation possible.

Concurrency without Pain in Pure Java

Throughout the whole conference, Grzegorz Duda had a publicly accessible wall, with sticky notes and two sides: what's bad and what's good. One of the note on the "bad" side was saying: "Sławek Sobótka and Paweł Lipiński at the same time? WTF?". 
I had the same thought. I wanted to see both. I was luckier though, since I'm pretty sure I'll yet be able too see their presentations this year, as 33rd is the first conference in a long run of conferences planned for 2012. Not being able to decide which one to see, I've decided to go for Venkat Subramaniam and his talk about concurrency. Unless we are lucky at 4Developers, we probably won't see Venkat again this year.
Unfortunately for me, the talk ("show" seems like a more proper word), was very basic, and while very entertaining, not deep enough for me. Venkat used Closure STM to show how bad concurrency is in pure Java, and how easy it is with STM. What can I say, it's been repeated so often, it's kind of obvious by now.
Venkat didn't have enough time to show the Actor model in Java. That's sad, as the further his talk, the more interesting it was. Perhaps there should be a few 90min sessions next year?

Smarter Testing with Spock

After the lunch, I had a chance to go for Sławek Sobótka again, but this time I've decided to listen to one of the commiters of Spock, the best thing in testing world since Mockito. 
Not really convinced? Gradle is using Spock (not surprisingly), Spring is starting to use Spock. I've had some experience with Spock, and it was fabulous. We even had a Spock workshop at TouK, lately. I wanted to see what Luke Daley can teach me in an hour. 
That was a time well spent. Apart from things I knew already, Luke explained how to share state between tests (@Shared), how to verify exceptions (thrown()), keep old values of variables (old()), how to parametrize description with @Unroll and #parameterName, how to set up data from db or whatever with <<, and a bit more advanced trick with mocking mechanism. Stubbing with closures was especially interesting.

What's new in Groovy 2.0?

Guillaume Laforge is the project lead of Groovy and his presentation was the opposite to what we could see earlier about next versions of Java. Most visible changes were already done in 1.8, with all the AST transformations, and Guillaume spent some time re-introducing them, but then he moved to 2.0, and here apart from multicatch in "throw", the major thing is static compilation and type checking.
We are in the days, were the performance difference between Java and Groovy falls to a mere 20%.  That's really little compared to where it all started from (orders of magnitude). That's cool. Also, after reading some posts and successful stories about Groovy++ use, I'd really like to try static compilation with this language
Someone from the audience asked a good question. Why not use Groovy++ as the base for static compilation instead. It turned out that Groovy++ author was also there. The main reason Guillaume gave, were small differences in how they want to handle internal things. If static compilation works fine with 2.0, Groovy++ may soon die, I guess.

Scala for the Intrigued


For the last talk this day, I've chosen a bit of Scala, by Venkat Subramaniam. That was unfortunately a completely basic introduction, and after spending 15 minutes listening about differences between var and val, I've left to get prepared to the BOF session, which I had with Maciek Próchniak.

BOF: Beautiful failures


I'm not in the position to review my own talk, and conclude whether it's failure was beautiful or not, but there is one things I've learned from it.
Never, under none circumstances, never drink five coffees the day you give a talk. To keep my mind active without being overwhelmed by all the interesting knowledge, I drank those five coffees, and to my surprise, when the talk started, the adrenaline shot brought me over the level, where you loose your breath, your pulse, and you start to loose control over your own voice. Not a really nice experience. I've had the effects of caffeine intoxication for the next two days. Lesson learned, I'm staying away from black beans for some time.
If you want the slides, you can find them here.
And that was the end of the day. We went to the party, to the afterparty, we got drunk, we got the soft-reset of our caches, and there came another day of the conference.

You can find my review from the last day in here.

Devoxx 2012 review


I'm sitting in a train to Charleroi, looking through a window at the Denmark landscape, street lights flashing by, people comming home from work, getting out for a Friday night party, or having a family dinner. To my left, guys from SoftwareMill are playing cards.
I don't really see them. My mind is busy elsewhere, sorting out and processing last two days in Antwerp, where 3400 developers, from 41 different countries, listened to 200 different sessions at the Devoxx, AFAIK the biggest Java conference this year.