We have an enterprise application running on WL 12c using eclipselink as the deafult JPA provider. MS SQL server 2008 is the backend database There is an entity on which i have used the following definition . the entity doesnt have any relationship with other entities
@Entity
@Table(name="TCALCNX")
@Cache(isolation=CacheIsolationType.ISOLATED, expiry=0, alwaysRefresh=true)
@OptimisticLocking(type=OptimisticLockingType.SELECTED_COLUMNS, selectedColumns= {@Column(name="REC_UDT_TS")})
As you can see there is an optimistic lock on a selected field which means all users can read the entity but while updating the entity, a check is made to ensure the object hasnt changed since it was read. If it was we get a optimisticlock exception.
Whats happening in my case is when i run the application with optimisticlock on,i rarely get optmisticlock exception but high number of deadlock exceptions. If i run the app with optimistic lock turned off am not getting any deadlock exceptions.
Internal Exception: java.sql.SQLTransactionRollbackException: [FMWGEN][SQLServer
JDBC Driver][SQLServer]Transaction (Process ID 338) was deadlocked on lock
| communication buffer resources with another process and has been chosen as the
deadlock victim. Rerun the transaction.
Error Code: 1205
what has the optimistic lock has to do with deadlocks
Ok assume that there are two transactions that read the object and modififed it . Both the transactions are updating at the same time . if this is the case then i shud be getting the deadlock exception with the optimisticlock turned off. But am not getting any deadlock exception when optimisticlock is turned off.
when i turn on the optimistic lock, looks like there is a lot of concurrency causing deadlock
Is it like with optimsticlock turned on , the updates are not ordered thus causing lot of concurrency
Is there a way to find out if the transactions are ordered or not in ecliselink
the backend database isolation level is read_committed.
Please dont hesitate to post any comment, there could be a clue from anyanswer from which i can get the answer
Entity
@Entity
@Table(name="TCALCNX")
@Cache(isolation=CacheIsolationType.ISOLATED, expiry=0, alwaysRefresh=true)
@OptimisticLocking(type=OptimisticLockingType.SELECTED_COLUMNS,selectedColumns= {@Column(name = "REC_UDT_TS")} )
public class CallContentEntity implements Serializable {
public static final String RECORDSTATUS_ADDED = "01";
public static final String RECORDSTATUS_UPDATED = "02";
public static final String RECORDSTATUS_MARKED_FOR_DELETION = "15";
@Id
@Column(name="CAL_ID_NR")
private String CallID;
@Column(name="CAL_CNX_B")
private byte[] CallContent;
@Column(name="REC_UDT_TS")
private Timestamp UpdateTimestamp;
@Column(name="REC_STS_CD", length=2)
private String RecordStatusCode;
@Column(name="SRC_DAT_CTR_NR", length=2)
private String SourceDataCnterNum;
@Column(name="REC_TYP_VER_NR", length=4)
private String VersionNumber;
@Column(name="REC_SEQ_NR")
private int SequenceNumber;
private static final long serialVersionUID = 1L;
public CallContentEntity() {
super();
}
public CallContentEntity(String callID) {
super();
LookUpValues lkup= LookUpValues.getInstance();
setCallID(callID);
setCallContent(null);
setRecordStatusCode(RECORDSTATUS_ADDED);
setUpdateTimestamp(new java.sql.Timestamp(Calendar.getInstance().getTimeInMillis()));
String sdatacntr= Integer.toString(lkup.getEnvironmentValue());
setSourceDataCnterNum(sdatacntr);
setVersionNumber(lkup.getLookUpValue("callcontentversion"));
setSequenceNumber(1);
}
public String getCallID() {
return this.CallID;
}
public void setCallID(String CallID) {
this.CallID = CallID;
}
public byte[] getCallContent() {
return this.CallContent;
}
public void setCallContent(byte[] CallContent) {
this.CallContent = CallContent;
}
public Timestamp getUpdateTimestamp() {
return this.UpdateTimestamp;
}
public void setUpdateTimestamp(Timestamp UpdateTimestamp) {
this.UpdateTimestamp = UpdateTimestamp;
}
public String getRecordStatusCode() {
return this.RecordStatusCode;
}
public void setRecordStatusCode(String RecordStatusCode) {
this.RecordStatusCode = RecordStatusCode;
}
public String getSourceDataCnterNum() {
return SourceDataCnterNum;
}
public void setSourceDataCnterNum(String sourceDataCnterNum) {
SourceDataCnterNum = sourceDataCnterNum;
}
public String getVersionNumber() {
return VersionNumber;
}
public void setVersionNumber(String versionNumber) {
VersionNumber = versionNumber;
}
public int getSequenceNumber() {
return SequenceNumber;
}
public void setSequenceNumber(int sequenceNumber) {
SequenceNumber = sequenceNumber;
}
Exception
Exception stack:
Local Exception Stack:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLTransactionRollbackException: [FMWGEN][SQLServer JDBC Driver][SQLServer]Transaction (Process ID 338) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Error Code: 1205
Call: SELECT CAL_ID_NR, CAL_CNX_B, REC_STS_CD, REC_SEQ_NR, SRC_DAT_CTR_NR, REC_UDT_TS, REC_TYP_VER_NR FROM TCALCNX WHERE (CAL_ID_NR = ?)
bind => [P4F22420140806182408001244]
Query: ReadObjectQuery(name="readObject" referenceClass=CallContentEntity sql="SELECT CAL_ID_NR, CAL_CNX_B, REC_STS_CD, REC_SEQ_NR, SRC_DAT_CTR_NR, REC_UDT_TS, REC_TYP_VER_NR FROM TCALCNX WHERE (CAL_ID_NR = ?)")
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:644)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:535)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1717)
at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:566)
at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:207)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:207)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:193)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectOneRow(DatasourceCallQueryMechanism.java:666)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectOneRowFromTable(ExpressionQueryMechanism.java:2656)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectOneRow(ExpressionQueryMechanism.java:2627)
at org.eclipse.persistence.queries.ReadObjectQuery.executeObjectLevelReadQuery(ReadObjectQuery.java:450)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1081)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:844)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1040)
at org.eclipse.persistence.queries.ReadObjectQuery.execute(ReadObjectQuery.java:418)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1128)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2871)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1516)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1498)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1449)
at org.eclipse.persistence.internal.jpa.EntityManagerImpl.executeQuery(EntityManagerImpl.java:820)
at org.eclipse.persistence.internal.jpa.EntityManagerImpl.findInternal(EntityManagerImpl.java:760)
at org.eclipse.persistence.internal.jpa.EntityManagerImpl.find(EntityManagerImpl.java:653)
at org.eclipse.persistence.internal.jpa.EntityManagerImpl.find(EntityManagerImpl.java:532)
at sun.reflect.GeneratedMethodAccessor131.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at weblogic.persistence.BasePersistenceContextProxyImpl.invoke(BasePersistenceContextProxyImpl.java:111)
at weblogic.persistence.TransactionalEntityManagerProxyImpl.invoke(TransactionalEntityManagerProxyImpl.java:82)
at weblogic.persistence.BasePersistenceContextProxyImpl.invoke(BasePersistenceContextProxyImpl.java:92)
at $Proxy99.find(Unknown Source)
at com.ups.ivr.ins.ejb.CallContentSession.Lookup(CallContentSession.java:207)
at com.ups.ivr.ins.ejb.CallContentSession_95sf62_CallContentSessionLocalImpl.__WL_invoke(Unknown Source)
at weblogic.ejb.container.internal.SessionLocalMethodInvoker.invoke(SessionLocalMethodInvoker.java:31)
at com.ups.ivr.ins.ejb.CallContentSession_95sf62_CallContentSessionLocalImpl.Lookup(Unknown Source)
at com.ups.ivr.ins.ejb.CallManager.getCurrentCallContent(CallManager.java:402)
at com.ups.ivr.ins.ejb.CallManager_11k7mo_CallManagerLocalImpl.__WL_invoke(Unknown Source)
at weblogic.ejb.container.internal.SessionLocalMethodInvoker.invoke(SessionLocalMethodInvoker.java:31)
at com.ups.ivr.ins.ejb.CallManager_11k7mo_CallManagerLocalImpl.getCurrentCallContent(Unknown Source)
at com.ups.ivr.ins.ejb.LookupSessionBean.Processdata(LookupSessionBean.java:184)
at com.ups.ivr.ins.ejb.LookupSessionBean_1i73wg_LookupSessionBeanLocalImpl.__WL_invoke(Unknown Source)
at weblogic.ejb.container.internal.SessionLocalMethodInvoker.invoke(SessionLocalMethodInvoker.java:31)
at com.ups.ivr.ins.ejb.LookupSessionBean_1i73wg_LookupSessionBeanLocalImpl.Processdata(Unknown Source)
at com.ups.ivr.ins.mwproxy.ClientRequestProcessor.doPost(ClientRequestProcessor.java:140)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:751)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:844)
at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:242)
at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:216)
at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:132)
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:338)
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:221)
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.wrapRun(WebAppServletContext.java:3292)
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3262)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:120)
at weblogic.servlet.provider.WlsSubjectHandle.run(WlsSubjectHandle.java:57)
at weblogic.servlet.internal.WebAppServletContext.doSecuredExecute(WebAppServletContext.java:2171)
at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2097)
at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2075)
at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1514)
at weblogic.servlet.provider.ContainerSupportProviderImpl$WlsRequestExecutor.run(ContainerSupportProviderImpl.java:254)
at weblogic.work.ExecuteThread.execute(ExecuteThread.java:256)
at weblogic.work.ExecuteThread.run(ExecuteThread.java:221)
Caused by: java.sql.SQLTransactionRollbackException: [FMWGEN][SQLServer JDBC Driver][SQLServer]Transaction (Process ID 338) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at weblogic.jdbc.sqlserverbase.ddb_.b(Unknown Source)
at weblogic.jdbc.sqlserverbase.ddb_.a(Unknown Source)
at weblogic.jdbc.sqlserverbase.ddb9.b(Unknown Source)
at weblogic.jdbc.sqlserverbase.ddb9.a(Unknown Source)
at weblogic.jdbc.sqlserver.tds.ddr.v(Unknown Source)
at weblogic.jdbc.sqlserver.tds.ddr.a(Unknown Source)
at weblogic.jdbc.sqlserver.tds.ddq.a(Unknown Source)
at weblogic.jdbc.sqlserver.tds.ddr.a(Unknown Source)
at weblogic.jdbc.sqlserver.tds.ddr.a(Unknown Source)
at weblogic.jdbc.sqlserver.ddh.a(Unknown Source)
at weblogic.jdbc.sqlserverbase.ddcq.k(Unknown Source)
at weblogic.jdbc.sqlserverbase.dddm.next(Unknown Source)
at weblogic.jdbc.wrapper.ResultSet_weblogic_jdbc_sqlserverbase_dddn.next(Unknown Source)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.processResultSet(DatabaseAccessor.java:699)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:621)
... 62 more
[Updated on: Sun, 17 August 2014 19:46]
Report message to a moderator