How to investigate the reason if face deadlock problem? (From DBMR1703, version: DBMaker 4.1.2, OS: Windows XP Embedded SP1)
To investigate the reason, you should monitor lock first, for example, as the following:
dmSQL> select USER_NAME, TABLE_NAME, SYSLOCK.LK_OBJECT_ID,
SYSLOCK.LK_GRAN, SYSLOCK.HOLD_LK_CONNECTION, SYSLOCK.LK_CURRENT_MODE
from SYSLOCK, SYSTEM.SYSTABLE, SYSUSER where HOLD_LK_CONNECTION = CONNECTION_ID and LK_OBJECT_ID = TABLE_OID and TABLE_OWNER != 'SYSTEM' and LK_GRAN != 'SYSTEM';
USER_NAME TABLE_NAME LK_OBJECT_ID LK_GRAN HOLD_LK_CONNECTION LK_CURRENT_MODE
========== ========== ============== ======== ==================== ==================
SYSADM D 1b00000000000600 TABLE 575 X
1 rows selected
Then you should select SYSUSER in every several seconds to get information about locks status on objects when dead lock occurred.
All the information that gets from the above will helpful for you to investigate the reason.