Icon View Incident Report

Serious Serious
Reported By: Ulrich Doewich
Reported On: 1/16/2009
For: Version 2.02 Build 7
# 2907 ALTER JOB Followed By PUBLISH DATABASE Statement in a Script or Job Causes Hang Due to Lock Conflict

I've just run into a small problem while updating my EDB server side code:

while the following code executes successfully during start up (UpgradeDb is True):

The subsequent PUBLISH instruction gets stuck and loops indefinitely (I have to kill the server via Task Manager).

if Config.Gen_PrimaryServer then begin
   StartDateStr := '2008-01-05';
   EndDateStr := '2018-01-06';
   StartTimeStr := '01:00';
   EndTimeStr := '23:30';
   SQL.Text := 'SELECT * FROM Jobs WHERE Name=' + 
EDBEngine1.QuotedSQLStr('Maintenance');
   Open; // check if the job already exists
   if (RecordCount = 0) or (UpgradeDb) then begin
     SQL.Clear;
     if UpgradeDb then begin
       SQL.Add('SELECT StartDate,EndDate,StartTime,EndTime FROM Jobs 
WHERE Name=' + EDBEngine1.QuotedSQLStr('Maintenance'));
       Open;
       if RecordCount > 0 then begin
         StartDateStr := FormatDateTime('yyyy-mm-dd', 
FieldByName('StartDate').AsDateTime);
         EndDateStr := FormatDateTime('yyyy-mm-dd', 
FieldByName('EndDate').AsDateTime);
         StartTimeStr := FormatDateTime('hh:nn', 
FieldByName('StartTime').AsDateTime);
         EndTimeStr := FormatDateTime('hh:nn', 
FieldByName('EndTime').AsDateTime);
       end;
       {$IF Defined(DEBUG)}
       AddToLog('updating "Maintenance" job');
       {$IFEND}
       SQL.Clear;
       SQL.Add('ALTER JOB Maintenance');
     end
     else begin
       {$IF Defined(DEBUG)}
       AddToLog('creating "Maintenance" job');
       {$IFEND}
       SQL.Add('CREATE JOB Maintenance');
     end;
     SQL.Add('RUN AS "System"');
     SQL.Add('FROM DATE ''' + StartDateStr + ''' TO DATE ''' + 
EndDateStr + '''');
     SQL.Add('HOURLY');
     SQL.Add('BETWEEN TIME ''' + StartTimeStr + ''' AND TIME ''' + 
EndTimeStr + '''');
     SQL.Add('BEGIN');
     SQL.Add('   USE RCS;');
     SQL.Add('   CALL ProcPurgeConfData;');
     SQL.Add('   CALL ProcPurgeLog;');
     SQL.Add('   CALL ProcGenerateUpdates;');
     SQL.Add('   USE;');
     SQL.Add('END');
     ExecSQL;
     {$IF Defined(DEBUG)}
     AddToLog('  success');
     {$IFEND}
   end;
end



Comments Comments
The problem was with the ALTER JOB acquiring a read lock on the database catalog during compilation and then not releasing it properly, thus preventing the PUBLISH from obtaining a write lock on the database catalog.


Resolution Resolution
Fixed Problem on 2/11/2009 in version 2.02 build 8


Products Affected Products Affected
ElevateDB Additional Software and Utilities
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial
ElevateDB LCL Standard with Source
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial

Image