Diagnostics challenge. Part I

Yesterday a former customer asked me to help with a performance issuePERFORMANCE ISSUE – database was suffering from a lot of locks:

and the most “popular” SQL statement was:

update dm_sysobject_s set i_vstamp=i_vstamp where r_object_id=:"SYS_B_0" and i_vstamp =:"SYS_B_1"

How to diagnose such issues in Documentum environment? At first, we need to know what java code produces such SQL update statement, let gather this information using tracing facilities:

[DM_SESSION_I_SESSION_START]info:  "Session 01024be980006909 started for user dmadmin."


Connected to Documentum Server running Release 7.2.0030.0195  Linux64.Oracle
Session id is s0
API> apply,c,,SQL_TRACE,SESSION_ID,S,01024be980006909,LEVEL,I,10
...  
q0   
API> apply,c,,LOG_ON,DETAIL,B,T
...
q1
API> create,c,dm_document
...
09024be980029502
API> save,c,l
...
OK
API> set,c,l,object_name
SET> xxx
...
OK
API> save,c,l
...
OK
API> checkout,c,l
...
09024be980029502
API> unlock,c,l
...
OK
API> begintran,c
...
OK
API> lock,c,l
...
OK
API> abort,c,
...
OK
API> begintran,c
...
OK
API> lock,c,l,T
...
OK
API> abort,c,l

and gathered SQL patterns are following:

com.documentum.fc.client.IDfPersistentObject#save:

UPDATE DM_SYSOBJECT_S dm_dbalias_B
   SET OBJECT_NAME = 'xxx',
       R_MODIFY_DATE =
          TO_DATE ('2016/11/03.00.21.49', 'YYYY/MM/DD.HH24.MI.SS'),
       R_MODIFIER = 'dmadmin',
       R_LOCK_OWNER = ' ',
       R_LOCK_DATE = TO_DATE ('0001/01/01.00.00.00', 'YYYY/MM/DD.HH24.MI.SS'),
       R_LOCK_MACHINE = ' ',
       I_VSTAMP = 1
 WHERE (    dm_dbalias_B.R_OBJECT_ID = :dmb_handle
        AND dm_dbalias_B.I_VSTAMP = :dmb_version);

com.documentum.fc.client.IDfSysObject#checkout:

UPDATE DM_SYSOBJECT_S dm_dbalias_B
   SET R_LOCK_OWNER = 'dmadmin',
       R_LOCK_DATE = TO_DATE ('2016/11/03.00.21.56', 'YYYY/MM/DD.HH24.MI.SS'),
       R_LOCK_MACHINE = 'docu72dev01',
       I_VSTAMP = 2
 WHERE (    dm_dbalias_B.R_OBJECT_ID = :dmb_handle
        AND dm_dbalias_B.I_VSTAMP = :dmb_version);

com.documentum.fc.client.IDfSysObject#cancelCheckout:

UPDATE DM_SYSOBJECT_S dm_dbalias_B
   SET R_LOCK_OWNER = ' ',
       R_LOCK_DATE = TO_DATE ('0001/01/01.00.00.00', 'YYYY/MM/DD.HH24.MI.SS'),
       R_LOCK_MACHINE = ' ',
       I_VSTAMP = 3
 WHERE (    dm_dbalias_B.R_OBJECT_ID = :dmb_handle
        AND dm_dbalias_B.I_VSTAMP = :dmb_version);

com.documentum.fc.client.IDfPersistentObject#lock:

UPDATE dm_sysobject_s
   SET i_vstamp = i_vstamp
 WHERE r_object_id = '09024be980029502';

com.documentum.fc.client.IDfPersistentObject#lockEx(true):

UPDATE dm_sysobject_s
   SET i_vstamp = i_vstamp
 WHERE r_object_id = '09024be980029502' AND i_vstamp = 3;

Now, it is clear that stuck SQL updates relate to IDfPersistentObject#lockEx(true) call, but it is only the part of the problem – when customer was putting IDfPersistentObject#lockEx(true) calls into his code he was expecting concurrency issues in certain places of code, so the fact that IDfPersistentObject#lockEx(true) calls get stuck is not a root cause – it is an expected behaviour, the problem is to investigate why these calls get stuck for a long time.

PS.

I can’t understand the purpose of IDfPersistentObject#lockEx(true) call – from programming perspective it’s behaviour is extremely weird:

S#1> begintran,c,
...
OK
S#1> save,c,09024be980029502
...
OK
S#2> begintran,c,
...
OK
S#2> lock,c,09024be980029502,T
...
S#1> commit,c
...
OK

S#2 [DM_OBJ_MGR_E_VERSION_MISMATCH]error:  "save of object 09024be980029502 
    of type dm_document failed because of version mismatch: old version was 3"

S#2> lock,c,09024be980029502,T
...
[DM_OBJ_MGR_E_VERSION_MISMATCH]error:  "save of object 09024be980029502 
     of type dm_document failed because of version mismatch: old version was 5"


S#2> revert,c,09024be980029502
...
OK
S#2> lock,c,09024be980029502,T
...
OK

So, the corresponding java code would be:

public static void lock(IDfPersistentObject object) throws DfException {
	while (true) {
		object.revert();
		try {
			object.lockEx(true);
			break;
		} catch (DfException ex) {
			if (!"DM_OBJ_MGR_E_VERSION_MISMATCH".equals(ex.getMessageId())) {
				throw ex;
			}
		}
	}
}

why do not write it in more straightforward way (the most suitable ways is described in Another way to implement “controlled” database lock):

public static void lock(IDfPersistentObject object) throws DfException {
	object.lock();
	object.revert();
}

?

2 thoughts on “Diagnostics challenge. Part I

  1. Pingback: aspects or not aspects… | Documentum in a (nuts)HELL
  2. Pingback: ACL performance | Documentum in a (nuts)HELL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s