Search code examples
intershop

Emergency unlocking of resources in database

We are having problem on a live production system. One of the nodes stopped working properly (because of problems with network file system on which is it hosted) and that happened while the channel staging process was in progress.

Because of that really bad timing, the staging process remained unfinished and all locked resources remained that way which prevented editing products or catalogs on live system.

1st solution we have tried is restarting servers node by node, that didn't help. 2nd solution we tried executing SQLs mentioned in this support article: https://support.intershop.com/kb/index.php/Display/2350K6

The exact SQLs we have executed are below, first one is for deleting from RESOURCELOCK table:

DELETE FROM RESOURCELOCK rl WHERE rl.LOCKID IN (SELECT
 resourcelock.lockid
FROM 
 isresource , 
 domaininformation resourcedomain, 
 process,
 basiccredentials , 
 domaininformation userdomain, 
 resourcelock ,
 isresource_av 
WHERE (
 (isresource.domainid = resourcedomain.domainid) 
  AND (isresource.resourceownerid = process.uuid) 
  AND (resourcelock.lockid = isresource.uuid) 
  AND (process.userid = basiccredentials.basicprofileid(+)) 
  AND (basiccredentials.domainid = userdomain.domainid(+)) 
  AND (isresource_av.ownerid(+) = isresource.uuid) 
  AND (isresource.resourceownerid is not null) 
  AND (isresource_av.name(+) = 'locknestinglevel')
  AND (process.name = 'StagingProcess')
));

And another one for ISRESOURCE table:

UPDATE isresource
SET
  resourceownerid=null,
  lockexpirationdate=null,
  lockcreationdate=null,
  lockingthreadid=null
 WHERE
  RESOURCEOWNERID='QigK85q6scAAAAF9Pf9fHEwf'; //UUID of StagingProcess

Now this has somewhat helped as it allowed for single products to be staged, but here are still two problems remaining:

  1. Products can't be manually locked on live system for editing, when lock icon is clicked the page refreshes but it looks like it is still unlocked, but records are created for each product which is clicked on in ISRESOURCE table altough they are incomplete (the is no RESOURCEOWNERID, lock creation date, or lock expiration date), this can be seen below: Incomplete ISRESOURCE records Also processes are tried to be created for product locking but they are all failing or running without end date as can be seen here: Product Editing processes

Now for the second problem: The channel staging cannot be started and it fails with message:

ERROR - Could not lock resources for process 'StagingProcess': Error finding resource lock with lockid: .0kK_SlyFFUAAAFlhGJujvESnull

That resource is MARKETING_Promotion resource: Marketing promotion resource

Both problems started occuring after running above mentioned SQLs and it seems they are related, any advice on how to resolve this situation would be helpfull.

Solution

  • The first SQL that I posted shouldn't have been run:

    DELETE FROM RESOURCELOCK rl WHERE rl.LOCKID IN....

    The fix was to restore deleted resource locks and just set the lock fields in ISRESOURCE table to null with the second SQL:

    UPDATE isresource
SET
  resourceownerid=null,
  lockexpirationdate=null,
  lockcreationdate=null,
  lockingthreadid=null
WHERE
  RESOURCEOWNERID='QigK85q6scAAAAF9Pf9fHEwf'; //UUID of StagingProcess