2

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

  1. 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.

  2. 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

UPDATE EXCEPTION

<2014-08-06> [P4F21420140806182553001248] <12:27:24,293> {ERROR} CallContentSession - CallContent Update Exception. 
 Failed to write Lookup content entry:(P4F21420140806182553001248)
Exception stack: 
weblogic.transaction.RollbackException: Unexpected exception in beforeCompletion: sync=org.eclipse.persistence.transaction.JTASynchronizationListener@1c1f9da7

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: UPDATE TCALCNX SET CAL_CNX_B = ?, REC_STS_CD = ?, REC_UDT_TS = ? WHERE ((CAL_ID_NR = ?) AND (REC_UDT_TS = ?))
    bind => [[B@1c1f9699, 02, 2014-08-06 12:27:22.645, P4F21420140806182553001248, 2014-08-06 12:27:21.287]
Query: UpdateObjectQuery(CALL ID :P4F21420140806182553001248
REC_UDT_TS :2014-08-06 12:27:22.645
REC_STS_CD:02
SRC_DT_CNTR:10
VERSION_NUM :001
REC_SEQ_NR:1)
    at weblogic.transaction.internal.TransactionImpl.throwRollbackException(TransactionImpl.java:1884)
    at weblogic.transaction.internal.ServerTransactionImpl.internalCommit(ServerTransactionImpl.java:376)
    at weblogic.transaction.internal.ServerTransactionImpl.commit(ServerTransactionImpl.java:268)
    at weblogic.transaction.internal.TransactionManagerImpl.commit(TransactionManagerImpl.java:308)
    at weblogic.transaction.internal.TransactionManagerImpl.commit(TransactionManagerImpl.java:302)
    at com.ups.ivr.ins.ejb.CallContentSession.Update(CallContentSession.java:276)
    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.Update(Unknown Source)
    at com.ups.ivr.ins.ejb.CallManager.saveCall(CallManager.java:130)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at com.ups.ivr.ins.pojo.concurrent.Task$1.run(Task.java:60)
    at com.ups.ivr.ins.pojo.concurrent.Task.run(Task.java:127)
    at weblogic.work.j2ee.J2EEWorkManager$WorkWithListener.run(J2EEWorkManager.java:184)
    at weblogic.work.ExecuteThread.execute(ExecuteThread.java:256)
    at weblogic.work.ExecuteThread.run(ExecuteThread.java:221)
Caused by: 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: UPDATE TCALCNX SET CAL_CNX_B = ?, REC_STS_CD = ?, REC_UDT_TS = ? WHERE ((CAL_ID_NR = ?) AND (REC_UDT_TS = ?))
    bind => [[B@1c1f9699, 02, 2014-08-06 12:27:22.645, P4F21420140806182553001248, 2014-08-06 12:27:21.287]
Query: UpdateObjectQuery(CALL ID :P4F21420140806182553001248
REC_UDT_TS :2014-08-06 12:27:22.645
REC_STS_CD:02
SRC_DT_CNTR:10
VERSION_NUM :001
REC_SEQ_NR:1)
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:324)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:840)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:906)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:592)
    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.ClientSession.executeCall(ClientSession.java:253)
    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.updateObject(DatasourceCallQueryMechanism.java:749)
    at org.eclipse.persistence.internal.queries.StatementQueryMechanism.updateObject(StatementQueryMechanism.java:432)
    at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.updateObjectForWriteWithChangeSet(DatabaseQueryMechanism.java:1042)
    at org.eclipse.persistence.queries.UpdateObjectQuery.executeCommitWithChangeSet(UpdateObjectQuery.java:84)
    at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.executeWriteWithChangeSet(DatabaseQueryMechanism.java:287)
    at org.eclipse.persistence.queries.WriteObjectQuery.executeDatabaseQuery(WriteObjectQuery.java:58)
    at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:844)
    at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:743)
    at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:108)
    at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:85)
    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.sessions.CommitManager.commitChangedObjectsForClassWithChangeSet(CommitManager.java:265)
    at org.eclipse.persistence.internal.sessions.CommitManager.commitAllObjectsWithChangeSet(CommitManager.java:128)
    at org.eclipse.persistence.internal.sessions.AbstractSession.writeAllObjectsWithChangeSet(AbstractSession.java:3799)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1415)
    at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitToDatabase(RepeatableWriteUnitOfWork.java:636)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithChangeSet(UnitOfWorkImpl.java:1505)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.issueSQLbeforeCompletion(UnitOfWorkImpl.java:3143)
    at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.issueSQLbeforeCompletion(RepeatableWriteUnitOfWork.java:346)
    at org.eclipse.persistence.transaction.AbstractSynchronizationListener.beforeCompletion(AbstractSynchronizationListener.java:157)
    at org.eclipse.persistence.transaction.JTASynchronizationListener.beforeCompletion(JTASynchronizationListener.java:68)
    at weblogic.transaction.internal.ServerSCInfo.doBeforeCompletion(ServerSCInfo.java:1259)
    at weblogic.transaction.internal.ServerSCInfo.callBeforeCompletions(ServerSCInfo.java:1234)
    at weblogic.transaction.internal.ServerSCInfo.startPrePrepareAndChain(ServerSCInfo.java:123)
    at weblogic.transaction.internal.ServerTransactionImpl.localPrePrepareAndChain(ServerTransactionImpl.java:1355)
    at weblogic.transaction.internal.ServerTransactionImpl.globalPrePrepare(ServerTransactionImpl.java:2172)
    at weblogic.transaction.internal.ServerTransactionImpl.internalCommit(ServerTransactionImpl.java:300)
    at weblogic.transaction.internal.ServerTransactionImpl.commit(ServerTransactionImpl.java:267)
    at weblogic.transaction.internal.TransactionManagerImpl.commit(TransactionManagerImpl.java:307)
    at weblogic.transaction.internal.TransactionManagerImpl.commit(TransactionManagerImpl.java:301)
    at com.ups.ivr.ins.ejb.CallContentSession.Update(CallContentSession.java:276)
    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.Update(Unknown Source)
    at com.ups.ivr.ins.ejb.CallManager.saveCall(CallManager.java:130)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at com.ups.ivr.ins.pojo.concurrent.Task$1.run(Task.java:60)
    at com.ups.ivr.ins.pojo.concurrent.Task.run(Task.java:126)
    ... 3 more
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.ddj.m(Unknown Source)
    at weblogic.jdbc.sqlserverbase.ddel.e(Unknown Source)
    at weblogic.jdbc.sqlserverbase.ddel.a(Unknown Source)
    at weblogic.jdbc.sqlserverbase.ddde.a(Unknown Source)
    at weblogic.jdbc.sqlserverbase.ddel.v(Unknown Source)
    at weblogic.jdbc.sqlserverbase.ddel.x(Unknown Source)
    at weblogic.jdbc.sqlserverbase.ddde.executeUpdate(Unknown Source)
    at weblogic.jdbc.wrapper.PreparedStatement.executeUpdate(PreparedStatement.java:167)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:831)
    ... 54 more
user3491141
  • 53
  • 8

2 Answers2

2

I'm not sure what this has to do with Optimistic locking (it likely does not). You should reduce what you are doing within your transactions so that any locks they obtain are held for shorter periods, such as only calling flush when it is absolutely required. Flush causes statements to be issued to the DB which will obtain locks earlier than might be needed, and will be held until the transaction fully commits, making it easier for transactions to conflict.

Optimistic locking in EclipseLink does nothing special other than add the changed field value to the where clause when modifying the corresponding entity. This does not affect the ordering of statements. It could be that your application's handling of optimistic lock exceptions is causing a delay or extra concurrent load if it retries the same operations over and over on a contentious row.

Chris
  • 20,138
  • 2
  • 29
  • 43
  • You need to use read committed snapshot isolation as described here http://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx to allow your transactions to operate on the same database rows. – Chris Aug 18 '14 at 18:53
  • The database transaction isolation level is read_committed only. I was also under the same understandng that optimistic lock just adds additional condition to where clause. but unfortunately its not the case, eclieslink has internal transactions in addition to the transaction managment provided by the app server – user3491141 Aug 19 '14 at 17:52
  • EclipseLink transaction management has nothing to do with Optimistic locking. EclipseLink orders updates, inserts and deletes, but your problem isn't with two transactions, but with a second transaction getting stuck somehow on a read. I don't know what locks that second transaction would hold - the only way it would is if you are using pessimistic locking, or have flushed data prior to doing the read and keep the transaction going. As mentioned you should check your database settings to see if there is a way to read without being locked on other transactions. – Chris Aug 20 '14 at 00:17
  • Chris,I have updated the post with the deadlock exception while updating as well. – user3491141 Aug 20 '14 at 20:05
  • The second read transaction (tx2) is not stuck , it is trying to read the record but the record is busy under modification by another tx (say tx_) . Database is not allowing the tx2 to read the record until tx_ is completed. In you comment you havementioned Eclipselink orers updatrs, inserts and deletes, can you provide me with some links in this direction – user3491141 Aug 20 '14 at 20:08
  • Your exception shows the db thinks tx2 is in a deadlock. You can see the ordering by setting up EclipseLink logging to Fine or Finest to see the SQL it issues. Deadlocks can happen in a number of situations where the rows become locked and ordering will not always help. If your database prevents reads while a row is being updated in a transaction, check that you are not calling flush to lock rows before the transaction is ready to complete, and you might want to use a different locking strategy if you cannot change how the DB behaves, since threads are waiting on writers to complete anyway. – Chris Aug 21 '14 at 14:08
  • As you said this has nothing to do with Optmisitic locking. Set up the following properties in persistence.xml – user3491141 Aug 25 '14 at 14:12
  • I see the sql statements only when there is an exception. how to enable printing the SQL statements for all transactions hitting the database. Moreover how to know if a transaction is committed / flushed the transaction . This can help me understand which transaction is holding the lock – user3491141 Aug 25 '14 at 14:18
  • old thread but eclipselink logging is described here http://wiki.eclipse.org/EclipseLink/Examples/JPA/Logging You proably need to set the property if you want to control logging through EclipseLink properties, otherwise it must be controlled through the WL console. – Chris Sep 25 '14 at 00:16
1

Optimistic Locking is a strategy where you read a record, take note of a version number (other methods to do this involve dates, timestamps or checksums/hashes) and check that the version hasn't changed before you write the record back. When you write the record back you filter the update on the version to make sure it's atomic.

When using optimistic locking we still have a deadlock situation which is not covered by the lock reordering:

  • Tx1 and Tx2 transactions executing in parallel on two nodes N1 and N2 and writing the keys {a,b}

  • consistentHash(a) = {N3} and consistentHash(b) = {N4}

  • with some right timing, during prepare time it is possible for these two transactions to deadlock:

    • Tx1 lock acquired on "a" @ N3
    • Tx2 lock acquired on "b" @ N4
    • Tx1 cannot progress acquiring lock on "b" @ N4, that lock is acquired by Tx2
    • Tx2 cannot acquire lock on "a" @ N3 as that lock is held by Tx1
    • Tx1 and Tx2 are waiting for each other ⇒ deadlock

A fast solution is to change Optimistic Lock to Pessimistic Lock in order to handle the situations, else i suggest you this link.

Xstian
  • 8,184
  • 10
  • 42
  • 72
  • Sorry couldn't relate Nodes and keys to the database terms . Do you mean the nodes to the database rows/records and keys to the primary key. Help me understand this in terms of Eclipselink JPA – user3491141 Aug 18 '14 at 03:45
  • Looks to me like the transactions are not ordered when i use optimistic lock on the entity. How to find out if the transactions are ordered or not? Is there a way i can print them – user3491141 Aug 18 '14 at 03:53
  • the problem is this.. i think that you cannot order the transactions. – Xstian Aug 18 '14 at 14:48
  • If you use Pessimistic Lock you can handle all persistences(ordered that you want). – Xstian Aug 19 '14 at 18:06
  • @user3491141 Have you solved this issue? if not put more information in order to help you better. – Xstian Aug 23 '14 at 00:05
  • No by removing the optmistic lock the issue looked like resolved but when moved to production , i started experiencing the deadlock again . – user3491141 Aug 24 '14 at 00:48
  • As you said this has nothing to do with Optmisitic locking. Set up the following properties in persistence.xml – user3491141 Aug 24 '14 at 00:50
  • I see the sql statements only when there is an exception. how to enable printing the SQL statements for all transactions hitting the database. Moreover how to know if a transaction is committed / flushed the transaction . This can help me understand which transaction is holding the lock – user3491141 Aug 24 '14 at 00:55
  • thanks for the follow up Xstian, have been on this issue for a long time – user3491141 Aug 24 '14 at 00:56