Be The First To Get Support Updates
Want to know about the latest technical content and software updates?
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.
- Disconnect all users, including all Direct connect and Application Server connections, and shutdown ArcSDE.
- Navigate to the SDEHOME/lib directory and open the version_user_ddl.spb file in a text editor.
- Find the following line of code in the L_lineage_flag procedure:
Code:
WHERE lineage_name = lineage_name - Alter the line of code to state:
Code:
WHERE lineage_name = L_lineage_flag.lineage_name - Save and exit the text editor.
- At the command prompt, navigate to the SDEHOME\lib directory.
- Start SQL*Plus as the sde user.
- 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 - Restart ArcSDE.
Last Published : 5/5/2016
Article ID: 000009133