Bug: Oracle dead locks encountered when local variable lineage_name is not fully qualified

Description

In the following block of code within the SDE.VERSION_USER_DDL package and L_lineage_flag procedure, the local variable lineage_name is not fully qualified resulting in the UPDATE statement's where clause to be an equality. Note: lineage_name = lineage_name means UPDATE all rows:

Code:
IF lineage_found THEN 
          -- If there was already an entry for this lineage, update it.
          IF current_time_last_modified > found_time_last_modified THEN 
             new_time_last_modified := current_time_last_modified; 
          ELSE 
             new_time_last_modified := found_time_last_modified + a_second; 
          END IF; 

          UPDATE SDE.lineages_modified 
          SET    time_last_modified = new_time_last_modified 
          WHERE  lineage_name = lineage_name;

The problem results in deadlocking because multiple sessions are updating all rows simultaneously.

Cause

The WHERE clause for statement must fully qualify the lineage_name variable using the referece to the procedure where the variable is declared. For example:

Code:
WHERE lineage_name = L_lineage_flag.lineage_name

Workaround

Update the SDE.VERSION_USER_DDL package.

1. Disconnect all users, including all Direct connect and Application Server connections, and shutdown ArcSDE.
2. Navigate to the SDEHOME/lib directory and open the version_user_ddl.spb file in a text editor.
3. Find the following line of code in the L_lineage_flag procedure:
   
   

   Code:
   WHERE lineage_name = lineage_name

   
   
4. Alter the line of code to state:
   
   

   Code:
   WHERE lineage_name = L_lineage_flag.lineage_name

   
   
5. Save and exit the text editor.
6. At the command prompt, navigate to the SDEHOME\lib directory.
7. Start SQL*Plus as the sde user.
8. Type the following command to rebuild the package.
   
   For ArcSDE 9.2:
   
   

   Code:
   start version_user_ddl.spb sde

   
   For all previous versions of ArcSDE:
   
   

   Code:
   start version_user_ddl.spb

   
   
9. Restart ArcSDE.

Last Published : 5/5/2016

Article ID: 000009133