Stored Procedure Changes - Design Specification

High-Level Algorithm

A procedure is loaded into memory during its creation or when a request refers to it for the first time. Once loaded they continue to remain till the last attachment goes away. During loading, the procedure body is compiled and converted into procedure request. It is at this time that the use count of dependent procedure is incremented. Even in case of a user request that selects from a procedure or executes a procedure, the procedures use count is incremented as a part of users request's compilation. The use count is decremented when the request referring to a procedure is released. The use count is also incremented when a procedure is executed. This is done as a part of TRA_post_resource() function i.e. as a result of a transactions interest on the procedure. Hence this will be decremented only when a commit or rollback happens.

The important thing is to decide the next course of action once we realize a stored procedure is in use. In InterBase V5.5 kits and above we do the following

• In case the request is for delete procedure then the procedure and all its dependencies are deleted from the system tables. The slot in dbb_procedures is made null. The copy in cache however is not deleted so that the requests that still point to this procedure can continue to execute. The copy now is termed as floating copy or zombie copy. This copy will continue to float around in memory till the last reference to it active. Once all references are gone the copy will be freed.
• In case the request is to modify procedure then first the slot in dbb_procedures is made null. Next a fresh copy of the same procedure is loaded. This is done to ensure that we don't meddle with the copy that is in use. Now we go ahead modifying the copy as usual. Note that there are two copies now. An old copy and new modified copy. The old copy i.e. floating copy remains till the last reference to it is active.

Internal Data Structures

The use count variable was already part of procedure block. It was not being used until now. There is however a new variable added to the procedure block named:

USHORT prc_alter_count

This variable keeps track of number of times a procedure in cache has been altered.

An in-cache procedure can be altered only MAX_PROC_ALTER times, which is defined as:

#define MAX_PROC_ALTER    64

Detailed Algorithm

Use count increment:

Use count increment is done in following places: cmp.c:pass1() and exe.c:EXE_start() functions post interests on the procedures and cmp.c:CMP_make_request() and tra.c :TRA_post_resource() increments the use counts. The psuedo-code for these functions is shown below:

pass1( node)
{
switch node_type
    {
     …..
     case nod_exec_proc :
           …..
           post an interest on this procedure resource;
           break;

     case nod_stream:
           call pass1_rse() which calls pass1_source() which in turn posts an interest if the stream is from a procedure
    }
…..
}

CMP_make_request()
{
pass1( node );
pass2 (node);
allocate request block;
for every resource type pass1() registered interest on
do
    switch resource type
     {
     …..
     case procedure:
         increment procedure->use_count;
         break
        …..
    }
done
….
}

EXE_start(request)
{
….
TRA_post_resource(request->req_resource)
…
looper()
…
}

TRA_post_resource (resource)
{
for every resource in request if it is not already in transactions resource list
do
    switch resource_type
    {
    ….
    case procedure:
        Increment procedure->use_count;
        break;
    }
done
….
}

The function CMP_make_request() is used to make every request ( user, procedure or trigger). EXE_start() is called when executing a request.

Use count usage:

These use_counts are used while dropping or altering the stored procedures. This is done in dfw.e:delete_procedure()/modify_procedure() functions. The delete_procedure() function checks, in phase 4, to see if the use count is set. If it is set then it deletes the dependencies for that procedure, NULLs the dbb_procedures vector for that procedure and returns without removing it from the cache. When the request using this procedure is finished, the procedure gets removed from the cache. This is taken care by the function CMP_decrement_prc_use_count() function. The psudeo-code for delete_procedure() is show below:

delete_procedure(tdbb, phase...)
{
switch (phase)
    {
    ....
    case 4:
        .....
        if (procedure->prc_use_count)
            {
            MET_delete_dependencies (tdbb, work->dfw_name, obj_procedure);
            /* Null the vector */
            dbb_procedures->vec_object[procedure->prc_id]=NULL_PTR;
            return FALSE;
            }
    ....
}

The modify_procedure() function first acquires a mutex lock to ensure that no other threads will access this procedure. Next it checks the use_count of the procedure. If the use count is set and the alter_count is not greater than MAX_PROC_ALTER, then it NULLs this procedure vector and loads a new copy into memory. It then goes ahead and modifies this copy. The psuedo-code is shown below:

modify_procedure(tdbb, phase...)
{
switch (phase)
    {
    ....
    case 4:
        .....
        /* Take the recursive mutex lock */
        if (THD_rec_mutex_lock (&tdbb->tdbb_database->dbb_sp_rec_mutex))
            {
            THREAD_ENTER;
            return FALSE;
            }
        if (procedure->prc_use_count)
            {
            if the alter count > MAX_PROC_ALTER
                then
                   generate error  /* Msg357: too many versions */
            endif
            /* Null the vector */
            dbb_procedures->vec_object[procedure->prc_id]=NULL_PTR;
            /* Load a new copy of the procedure */
            procedure=MET_lookup_procedure_id (tdbb, work->dfw_id, FALSE, PRC_being_altered)
            /* Increment the alter count */
            ++procedure->prc_alter_count;
            }

        /* Go ahead and modify this copy now */
        ......
    }

Use count decrement:

Use count is decremented in cmp.c:CMP_decrement_prc_use_count(), which is called from CMP_release() and TRA_release_transaction():

CMP_release (request)
{
for each resource in the request
do
    switch resource type
    {
        ….
        case procedure :
            CMP_decrement_prc_use_count ( resource->procedure );
            break;
        …..
    }
done
….
}

TRA_release_transaction()
{
……
for each resource in the transaction list
do
    switch resource type
    {
    ……
    case procedure :
        CMP_decrement_prc_use_count ( resource->procedure );
        break;
    ……
    }
done
……
}

CMP_decrement_prc_use_count()
{
assert ( procedure->use_count !=0)
--procedure->use_count;
if the use count became zero AND if the procedure is a floating copy then
    {
    CMP_release ( procedure->prc_request )
    MET_remove_procedure (procedure); /* Remove it from the cache since there are no more references to it */
    }
}

The function CMP_release() is called to release a request and TRA_release_transaction() is called when a commit or rollback is issued.

Caveat

In case of recursive procedures (either self recursive or looping i.e. procA->procB->procA) the use_count will be set irrespective whether the procedure is in use or not. For e.g. consider a normal case procA calling procB. As the engine compiles procA it realizes that procA depends on another procedure procB, Checks to see if procB is in cache if not brings it into cache and increments its use count. Hence procB's use count goes up. After procA is loaded into the cache the use count of procA is 0 and use count of procB is 1.

Now consider the recursive case procA calling itself. The engine while compiling procA notices procA is dependent upon another procedure, in this case procA itself, notices procA is already in cache and increments its use count. Hence after procA is loaded its use count is 1.

This leads to the creation of unnecessary extra copies of these procedure every time one modifies them. This was also one of the reason why an upper limit on procedure alter count was enforced.

New/Affected Modules

All the affected modules are from jrd component.

• cmp.c
• cmp_proto.h
• dfw.e
• exe.c
• jrd.c
• jrd.h
• met.e
• met_proto.h
• par.c
• rse.c
• tra.c
• vio.c