Hi, I am using Jboss/EJB3/Hibernate for db operations with SQL Server 2008. From the logs I could see that data is inserted in the table and Hibernate connection manager releases the JDBC connection. However the database table is locked and is being released only when the Jboss application server is brought down. When the server is brought down, I couldn't see the inserted data. Please advice. I have placed the log for reference.

2:21:47,909 DEBUG [org.jboss.ejb3.tx.UserTransactionImpl] new UserTx: org.jboss.ejb3.tx.UserTransactionImpl@32c14
12:21:49,985 DEBUG [org.jboss.ejb3.tx.UserTransactionImpl] UserTx begin: TransactionImple < ac, BasicAction: 0:ffffc0a81437:126a:4d8a1dd4:d status: ActionStatus.RUNNING >
12:21:57,973 DEBUG [org.hibernate.impl.SessionImpl] opened session at timestamp: 13008973179
12:21:58,489 DEBUG [org.hibernate.jdbc.JDBCContext] successfully registered Synchronization
12:21:58,505 DEBUG [org.hibernate.loader.Loader] loading entity: [com.idl.dur.gen.data.CmsCoTbl#CO]
12:21:58,505 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
12:21:58,520 DEBUG [org.hibernate.jdbc.ConnectionManager] opening JDBC connection
12:21:58,520 DEBUG [org.hibernate.SQL] select cmscotbl0_.CO_CD as CO1_37_0_, cmscotbl0_.CO_DESC as CO2_37_0_, cmscotbl0_.SYS_ADD_USER_ID as SYS3_37_0_, cmscotbl0_.SYS_ADD_DT as SYS4_37_0_, cmscotbl0_.SYS_UPDT_USER_ID as SYS5_37_0_, cmscotbl0_.SYS_LAST_UPDT_DT as SYS6_37_0_, cmscotbl0_.CO_EFCTV_DT as CO7_37_0_, cmscotbl0_.CO_TRMNTN_DT as CO8_37_0_ from MEDICAIDDUR.dbo.CMS_CO_TBL cmscotbl0_ where cmscotbl0_.CO_CD=?
12:21:59,300 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to open ResultSet (open ResultSets: 0, globally: 0)
12:21:59,300 DEBUG [org.hibernate.loader.Loader] result row: EntityKey[com.idl.dur.gen.data.CmsCoTbl#CO]
12:21:59,645 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to close ResultSet (open ResultSets: 1, globally: 1)
12:21:59,645 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
12:21:59,676 DEBUG [org.hibernate.jdbc.ConnectionManager] aggressively releasing JDBC connection
12:21:59,676 DEBUG [org.hibernate.jdbc.ConnectionManager] releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
12:21:59,676 DEBUG [org.hibernate.engine.TwoPhaseLoad] resolving associations for [com.idl.dur.gen.data.CmsCoTbl#CO]
12:21:59,879 DEBUG [org.hibernate.engine.TwoPhaseLoad] done materializing entity [com.idl.dur.gen.data.CmsCoTbl#CO]
12:21:59,894 DEBUG [org.hibernate.engine.StatefulPersistenceContext] initializing non-lazy collections
12:21:59,894 DEBUG [org.hibernate.loader.Loader] done entity load
12:22:00,035 DEBUG [org.hibernate.event.def.AbstractSaveEventListener] generated identifier: Neethi, using strategy: org.hibernate.id.Assigned
12:22:10,221 DEBUG [org.jboss.ejb3.tx.UserTransactionImpl] UserTx commit: TransactionImple < ac, BasicAction: 0:ffffc0a81437:126a:4d8a1dd4:d status: ActionStatus.RUNNING >
12:22:10,222 DEBUG [org.hibernate.event.def.AbstractFlushingEventListener] processing flush-time cascades
12:22:10,233 DEBUG [org.hibernate.event.def.AbstractFlushingEventListener] dirty checking collections
12:22:10,376 DEBUG [org.hibernate.engine.Collections] Collection found: [com.idl.dur.gen.data.CmsCoTbl.cmsRoTbls#CO], was: [com.idl.dur.gen.data.CmsCoTbl.cmsRoTbls#CO] (uninitialized)
12:22:10,376 DEBUG [org.hibernate.engine.Collections] Collection found: [com.idl.dur.gen.data.CmsCoTbl.sysUserTbls#CO], was: [com.idl.dur.gen.data.CmsCoTbl.sysUserTbls#CO] (uninitialized)
12:22:10,376 DEBUG [org.hibernate.engine.Collections] Collection found: [com.idl.dur.gen.data.SysUserTbl.sysUserRoleTbls#Neethi], was: [<unreferenced>] (initialized)
12:22:10,423 DEBUG [org.hibernate.event.def.AbstractFlushingEventListener] Flushed: 1 insertions, 0 updates, 0 deletions to 2 objects
12:22:10,423 DEBUG [org.hibernate.event.def.AbstractFlushingEventListener] Flushed: 1 (re)creations, 0 updates, 0 removals to 3 collections
12:22:10,438 DEBUG [org.hibernate.pretty.Printer] listing entities:
12:22:10,470 DEBUG [org.hibernate.pretty.Printer] com.idl.dur.gen.data.SysUserTbl{sysUserLastIpAdr=null, sysUserEnblTs=null, sysUserLockSw=null, sysUserSesnCnt=null, sysUserLastLoginTs=null, geoPrncpalSbdivTbl=null, sysUserEmailAdr=sekar@sekar.com, sysAddDt=2011-03-23 12:21:59.894, sysUpdtUserId=null, sysLastUpdtDt=null, sysUserFaildLoginNum=null, cmsRoTbl=null, sysUserLastFaildLoginTs=null, emailNtfctnFlag=Y, sysAddUserId=null, sysUserLockTs=null, sysUserEnblSw=null, sysUserRoleTbls=[], sysUserDsbldTs=null, sysUser1stName=Rajasekar, sysUserMdlName=B, sysUserId=Neethi, sysUserLastName=Kayamboo, sysUserTelNum=1-571-451-7698, sysUserSesnId=null, cmsCoTbl=com.idl.dur.gen.data.CmsCoTbl#CO, sysUserFaxNum=1-800-123-1234, sysUserPw=sekar}
12:22:10,470 DEBUG [org.hibernate.pretty.Printer] com.idl.dur.gen.data.CmsCoTbl{sysLastUpdtDt=2011-03-16 00:00:00.0, coCd=CO, sysUserTbls=<uninitialized>, cmsRoTbls=<uninitialized>, coTrmntnDt=2099-12-31 00:00:00.0, coEfctvDt=2011-03-16 00:00:00.0, coDesc=CMS Central Office, sysUpdtUserId=DURADM , sysAddDt=2011-03-15 00:00:00.0, sysAddUserId=DURADM }
12:22:10,875 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
12:22:10,875 DEBUG [org.hibernate.jdbc.ConnectionManager] opening JDBC connection
12:22:10,875 DEBUG [org.hibernate.SQL] insert into MEDICAIDDUR.dbo.SYS_USER_TBL (CO_CD, GEO_PRNCPAL_SBDIV_CD, RO_CD, SYS_USER_1ST_NAME, SYS_USER_LAST_NAME, SYS_USER_EMAIL_ADR, SYS_USER_FAX_NUM, SYS_USER_TEL_NUM, SYS_USER_SESN_ID, SYS_USER_SESN_CNT, SYS_USER_LAST_LOGIN_TS, SYS_USER_LOCK_SW, SYS_USER_LOCK_TS, SYS_USER_ENBL_SW, SYS_USER_ENBL_TS, SYS_USER_DSBLD_TS, SYS_USER_FAILD_LOGIN_NUM, SYS_USER_LAST_FAILD_LOGIN_TS, SYS_USER_LAST_IP_ADR, SYS_USER_PW, SYS_ADD_USER_ID, SYS_ADD_DT, SYS_UPDT_USER_ID, SYS_LAST_UPDT_DT, SYS_USER_MDL_NAME, EMAIL_NTFCTN_FLAG, SYS_USER_ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
12:22:10,984 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
12:22:10,984 DEBUG [org.hibernate.jdbc.ConnectionManager] skipping aggressive-release due to flush cycle
12:22:10,984 DEBUG [org.hibernate.jdbc.ConnectionManager] aggressively releasing JDBC connection
12:22:10,984 DEBUG [org.hibernate.jdbc.ConnectionManager] releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]

I am noticing that data is inserted in the database and is not committed through I have enabled CMT and configured Transaction manager lookup. After I bring down the Jboss application server the uncommited data is lost. Am I missing any other configuration?

I am attaching the configuration and the code

<session-factory>
<property name="dialect">
org.hibernate.dialect.SQLServerDialect
</property>
<property name="connection.url">
jdbc:sqlserver://xx.xx.xx.xx:1433;databaseName=MEDICAIDDUR
</property>
<property name="connection.username">CCCCCCin</property>
<property name="connection.password">XXXXX</property>
<property name="connection.driver_class">
com.microsoft.sqlserver.jdbc.SQLServerDriver
</property>
<property name="myeclipse.connection.profile">
SQL Server 2005
</property>
<property name="hibernate.connection.autocommit">
false
</property>
<property name="hibernate.transaction.factory_class">
org.hibernate.transaction.CMTTransactionFactory
</property>
<property name="hibernate.transaction.manager_lookup_class">
org.hibernate.transaction.JBossTransactionManagerLookup
</property>
<property name="hibernate.transaction.flush_before_completion">
true
</property>
<property name="hibernate.transaction.auto_close_session">
true
</property>
<mapping
resource="com/idl/dur/gen/data/RtroDurCrtrPrblmTypTbl.hbm.xml" />
<mapping
.....

@TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)
public UserDTO delegateAddUser(UserDTO userDto) throws RemoteException {

try {

// BO Call
UserBO userBO = new UserBO();
userBO.addUser(userDto);
// After any BO call
CommonUtil.checkForErrorAndException(userDto, context);
//

}catch (Exception ex) {
userDto.setException(ex);
userDto.getApplicationErrors().add(
new ApplicationError("errors.internal",
"Exception happened in Delegate EJB"));
CommonUtil.logExceptionAndRollback(ex, context);
}

return userDto;
}