English

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.