Lock Select

When selecting data that cannot be processed simultaneously due to business requirements, use the FOR UPDATE keyword provided by Oracle to apply a lock. Set the WAIT time appropriately according to the business requirements.

1. Format

NOWAIT: When the corresponding row is in a locked state, it immediately raises an SQL error (ORA-00054).

WAIT X: When the corresponding row is in a locked state, it retries for X seconds and then raises an SQL error (ORA-30006).

SELECT
     A.ROWID AS "rowId"
     , A.FEDU_EMP_NO AS feduEmpNo
     , A.FEDU_EMP_NM AS feduEmpNm
     , A.FEDU_OCCP_NM AS feduOccpNm
  FROM SMP_EMP_TST A
 WHERE A.FEDU_EMP_NO = #{feduEmpNo}
FOR UPDATE [NOWAIT | WAIT seconds]

2. Development Notes

If the retrieved row is in a locked state, DasCannotAcquireLockException (caused by ORA-00054/ORA-30006) occurs.

try {
    dSmpEmpTst001.selectOneLock01(dSmpEmpTst000Dto, LockUpdate.FOR_UPDATE);
} catch (DasCannotAcquireLockException e) {
    // Logic for handling DB LOCK timeout
}

3. DBIO Sample

Check [Lock Update] in Options. Add ROWID and the FOR UPDATE clause to the single/multiple Select statements.

Figure 1. Lock Select screen

Specify the schema of the SQL written in the popup.

Figure 2. Lock Select screen

4. Bean Sample

@BxmCategory(logicalName = "Multi-Update 2")
public int modifyEmpInfBySelectOneLock(List<DSmpEmpTst000Dto> input)
        throws DefaultApplicationException {

    logger.debug("============== START ==============");
    logger.debug("input = {}", input);

    dSmpEmpTst001 = DefaultApplicationContext.getBean(dSmpEmpTst001, DSmpEmpTst001.class);

    int modifyCnt = 0;

    /**
     * @BXMType Loop
     */
    for (DSmpEmpTst000Dto dSmpEmpTst000Dto : input)
    {
        /**
         * @BXMType DbioCall
         * @Desc Get lock for a row to update
         */
        LockUpdater<DSmpEmpTst000Dto> lockUpdaterDSmpEmpTst = dSmpEmpTst001.selectOneLock01(dSmpEmpTst000Dto, LockUpdate.FOR_UPDATE);

        DSmpEmpTst000Dto lockDSmpEmpTst000Dto = lockUpdaterDSmpEmpTst.updatable();

        /**
         * @BXMType LogicalArea
         * @Desc Mapping data to update
         */
        {
            // Generated by code generator [[
            lockDSmpEmpTst000Dto.setFeduEmpNo(dSmpEmpTst000Dto.getFeduEmpNo());
            lockDSmpEmpTst000Dto.setFeduEmpNm(dSmpEmpTst000Dto.getFeduEmpNm());
            lockDSmpEmpTst000Dto.setFeduOccpNm(dSmpEmpTst000Dto.getFeduOccpNm());
            lockDSmpEmpTst000Dto.setFeduMngrEmpNo(dSmpEmpTst000Dto.getFeduMngrEmpNo());
            lockDSmpEmpTst000Dto.setFeduHireDt(dSmpEmpTst000Dto.getFeduHireDt());
            lockDSmpEmpTst000Dto.setFeduPayAmt(dSmpEmpTst000Dto.getFeduPayAmt());
            lockDSmpEmpTst000Dto.setFeduDeptNo(dSmpEmpTst000Dto.getFeduDeptNo());
            // Generated by code generator ]]
        }

        /**
         * @BXMType DbioCall
         * Update single employee info
         */
        modifyCnt = lockUpdaterDSmpEmpTst.update();

        /**
         * @BXMType IF
         * @Desc If there are no modified row
         */
        if (modifyCnt != 1) {
            /**
             * @BXMType ApplicationException
             * @Desc throw application exception if there are no modified row
             */
            throw new DefaultApplicationException("BXME60003", new Object[] { lockDSmpEmpTst000Dto.getFeduEmpNo() } );
        }
    }

    logger.debug("output = {}", modifyCnt);
    logger.debug("============== END ==============");

    return modifyCnt;
}