EBR – Part 2: Locking, Blocking and ORA-04068
This is part 2 of a post series about EBR.
In part 1 we created the baseline model and code – a table (PEOPLE) and two packages (PEOPLE_DL and APP_MGR).
In this post we’ll start handling the first type of change request: changing a package body.
Visit the index page for all the parts of the series
The Task
We need to change the implementation of the PEOPLE_DL package; i.e. we need to change the package body.
There are no API changes (the package spec is not changed) and no table changes.
And of course, as we speak about EBR, the upgrade from the base version to the new one should be online.
The Problems
LOCKING AND BLOCKING
An online upgrade means that the application users should be able to continue working uninterruptedly. The code objects that they use should remain valid and available at any time.
One of the many advantages of using PL/SQL packages is that a package body has no dependent objects. It means that compiling a package body never invalidates other code objects.
So what is the challenge in changing a package body in an online upgrade? Why can’t we just compile the package body?
One problem is that in order to compile the package body we have to exclusively lock it – to make sure no other session is using it while it’s being compiled. It means that:
- The compilation is blocked by any other session that is currently using the package
and
- As soon as the compiling session starts waiting to lock the package, and until the compilation is done, it blocks any other session that wants to use the package
In a busy system, this can lead to a snowball of locks, and the upgrade becomes an offline one (and of the worst kind – an unplanned offline upgrade).
We can easily simulate this.
Let’s create a package pkg with a single procedure p that does… nothing.
create or replace package pkg as
procedure p;
end pkg;
/
create or replace package body pkg as
g_var number := 42;
procedure p is
begin
null;
end p;
end pkg;
/
And let’s create two standalone procedures: test_execute that calls pkg.p and then sleeps for 10 seconds; and test_compile that simply compiles the pkg package body:
create or replace procedure test_execute as
begin
pkg.p;
dbms_lock.sleep(10);
end test_execute;
/
create or replace procedure test_compile as
begin
execute immediate 'alter package pkg compile body';
end test_compile;
/
Now I’ll schedule 8 jobs; each one starts 1 second after the previous one has started. All of them represent end-user sessions, except for the third one, which represents the developer session. Each of the end-user sessions calls the test_execute procedure, and the developer session calls the test_compile procedure.
declare
v_base_time date := sysdate;
begin
for i in 1 .. 8
loop
dbms_scheduler.create_job(
job_name => '"session#' || i || ' (' ||
case i when 3 then 'developer' else 'end user' end ||
')"',
job_type => 'STORED_PROCEDURE',
job_action => case i when 3 then 'test_compile' else 'test_execute' end,
start_date => v_base_time + numtodsinterval(i, 'second'),
enabled => true);
end loop;
end;
/
The following query shows library cache pins and locks held and requested by the various sessions:
col action format a21
col event format a20
col blocker format a21
col object_type format a20
col object_name format a20
col mode_held format a10
col mode_requested format a10
break on action on event on blocker on blocked_duration skip 1
select s.action,
s.event,
b.action blocker,
nvl2(b.sid,s.seconds_in_wait,null) blocked_duration,
o.kglobtyd object_type,
o.kglnaobj object_name,
l.type,
decode(l.mode_held, 1, 'null', 2, 'share', 3, 'exclusive') mode_held,
decode(l.mode_requested, 1, 'null', 2, 'share', 3, 'exclusive') mode_requested
from v$session s,
v$libcache_locks l,
x$kglob o,
v$session b
where s.saddr = l.holding_user_session
and o.kglhdadr = l.object_handle
and o.kglnaown = 'DEMO5'
and (l.mode_held in (2, 3) or l.mode_requested in (2, 3))
and b.sid(+) = s.blocking_session
order by action,
object_type,
object_name;
I executed this query about 2 seconds after the last job has started:
ACTION EVENT BLOCKER BLOCKED_DURATION OBJECT_TYPE OBJECT_NAME TYPE MODE_HELD MODE_REQUE
--------------------- -------------------- --------------------- ---------------- -------------------- -------------------- ---------- ---------- ----------
session#1 (end user) PL/SQL lock timer PACKAGE PKG PIN share
PACKAGE BODY PKG PIN share
PROCEDURE TEST_EXECUTE PIN share
session#2 (end user) PL/SQL lock timer PACKAGE PKG PIN share
PACKAGE BODY PKG PIN share
PROCEDURE TEST_EXECUTE PIN share
session#3 (developer) library cache pin session#2 (end user) 7 PACKAGE BODY PKG LOCK exclusive
PACKAGE BODY PKG PIN exclusive
PROCEDURE TEST_COMPILE PIN share
session#4 (end user) library cache pin session#3 (developer) 6 PACKAGE PKG PIN share
PACKAGE BODY PKG PIN share
PROCEDURE TEST_EXECUTE PIN share
session#5 (end user) library cache pin session#3 (developer) 5 PACKAGE PKG PIN share
PACKAGE BODY PKG PIN share
PROCEDURE TEST_EXECUTE PIN share
session#6 (end user) library cache pin session#3 (developer) 4 PACKAGE PKG PIN share
PACKAGE BODY PKG PIN share
PROCEDURE TEST_EXECUTE PIN share
session#7 (end user) library cache pin session#3 (developer) 3 PACKAGE PKG PIN share
PACKAGE BODY PKG PIN share
PROCEDURE TEST_EXECUTE PIN share
session#8 (end user) library cache pin session#3 (developer) 2 PACKAGE PKG PIN share
PACKAGE BODY PKG PIN share
PROCEDURE TEST_EXECUTE PIN share
24 rows selected.
We can see that in order to execute objects, the (end-user) sessions have to hold a library cache pin on them in share mode. And for compiling an object, the (developer) session has to hold the pin in exclusive mode.
Note that the library cache pins on the pkg package spec and body are held not only during the time when the package is explicitly used, but since its use until the end of the top level call in which it is included.
Since I used jobs for scheduling the test, we can now query user_scheduler_job_run_details to see when each session started and ended:
col job_name format a21
col start_time format a10
col end_time format a10
col progress format a25
select d.job_name,
to_char(d.actual_start_date, 'hh24:mi:ss') start_time,
to_char(d.actual_start_date + d.run_duration, 'hh24:mi:ss') end_time,
lpad(' ', round(extract(second from d.actual_start_date - min(d.actual_start_date) over()))) ||
lpad('*', extract(second from d.run_duration), '*') progress
from user_scheduler_job_run_details d
where d.job_name like 'session%'
order by start_time
JOB_NAME START_TIME END_TIME PROGRESS
--------------------- ---------- ---------- -------------------------
session#1 (end user) 09:03:12 09:03:22 **********
session#2 (end user) 09:03:13 09:03:23 **********
session#3 (developer) 09:03:14 09:03:23 *********
session#4 (end user) 09:03:15 09:03:33 ******************
session#5 (end user) 09:03:16 09:03:33 *****************
session#6 (end user) 09:03:17 09:03:33 ****************
session#7 (end user) 09:03:18 09:03:33 ***************
session#8 (end user) 09:03:19 09:03:33 **************
8 rows selected.
Note: I colored in red the time in which each session was blocked.
To summarize the test results:
- The end-user sessions 1 and 2 were executed concurrently
- The developer session was being blocked until end-user sessions 1 and 2 were done
- All the other end-user sessions (4 through 8) were being blocked by the developer session, and all of them were released and were running concurrently once the developer session was done
ORA-04068
There is another potential problem with compiling a package body during an online upgrade. If the package body includes global variables, and a session has used the package before the upgrade, then if the same session tries to use the package after the upgrade it will get the error “ORA-04068: existing state of packages has been discarded”.
Let’s see it…
Note that the pkg package body includes a global variable g_var.
From one session we’ll execute pkg.p:
exec pkg.p PL/SQL procedure successfully completed.
From a second session we’ll compile the package body:
alter package pkg compile body; Package body altered.
And then we’ll call pkg.p again from the first session:
exec pkg.p BEGIN pkg.p; END; * ERROR at line 1: ORA-04068: existing state of packages has been discarded ORA-04061: existing state of package body "DEMO5.PKG" has been invalidated ORA-04065: not executed, altered or dropped package body "DEMO5.PKG" ORA-06508: PL/SQL: could not find program unit being called: "DEMO5.PKG" ORA-06512: at line 1
The Solution
We’ve seen that even a simple change, that requires only a single package body compilation, may cause severe problems if it’s carelessly applied to an active system.
So how can we apply such a change in both online and safe way? As we’ll see in the next part of this series, we can do it easily, using editions.
For other parts of the EBR series, please visit the index page