Using NOLOCK to eliminate deadlocks for application which has pessimistic locking

Question

Working on a Spring-Hibernate application with SQL Server database. We are getting deadlocks. Transaction attributes are defined via Spring config. The application has pessimistic locking implemented in application code. When user starts working on Customer record that record is locked. We have worked to eliminate locks by having shorter transactions, changing transaction isolation level, etc.

As an alternative, would it be practical (although unconventional) approach to eliminate database level locking by specifying NOLOCK for SQLs which cause locks and hence deadlocks?

[11/7/13 7:49:44:694 EDT] 00000049 SystemErr     R Caused by: org.hibernate.exception.LockAcquisitionException: could not initialize a collection: [com.co.app.domain.Customer.customerBusElemtVals#component[custId,txnId]{txnId=11111111, CustomerId=22222222}]
                at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:105)
                at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
                at org.hibernate.loader.Loader.loadCollection(Loader.java:2022)
                at org.hibernate.loader.collection.BatchingCollectionInitializer.initialize(BatchingCollectionInitializer.java:75)
                at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:587)
                at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:83)
                at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1743)
                at org.hibernate.collection.AbstractPersistentCollection.forceInitialization(AbstractPersistentCollection.java:476)
                at org.hibernate.engine.StatefulPersistenceContext.initializeNonLazyCollections(StatefulPersistenceContext.java:867)
                at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:264)
                at org.hibernate.loader.Loader.loadEntity(Loader.java:1881)
                at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:71)
                at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:65)
                at org.hibernate.loader.entity.BatchingEntityLoader.load(BatchingEntityLoader.java:105)
                at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:3072)
                at org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:434)
                at org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:415)
                at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:165)
                at org.hibernate.event.def.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:223)
                at org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:126)
                at org.hibernate.impl.SessionImpl.fireLoad(SessionImpl.java:905)
                at org.hibernate.impl.SessionImpl.get(SessionImpl.java:842)
                at org.hibernate.impl.SessionImpl.get(SessionImpl.java:835)
                at org.springframework.orm.hibernate3.HibernateTemplate$1.doInHibernate(HibernateTemplate.java:528)
                at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:419)
                at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)
                at org.springframework.orm.hibernate3.HibernateTemplate.get(HibernateTemplate.java:522)
                at org.springframework.orm.hibernate3.HibernateTemplate.get(HibernateTemplate.java:516)
                at com.co.app.dao.base.GenericHibernateDAO.get(GenericHibernateDAO.java:43)
                at com.co.app.dao.hibernate.CustomerDAOImpl.getCustomer(CustomerDAOImpl.java:61)
                at com.co.app.dao.helper.FormsDataHelper.getCustomerData(FormsDataHelper.java:103)
                at com.co.app.dao.helper.FormsDataHelper$$FastClassByCGLIB$$fb5cf604.invoke(<generated>)
                at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
                at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:700)
                at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
                at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
                at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
                at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:635)
                at com.co.app.dao.helper.FormsDataHelper$$EnhancerByCGLIB$$414aac28.getCustomerData(<generated>)
                at com.co.app.manager.CommonCustomerDataManager.getCustomerData(ComonCustomerDataManager.java:151)
                at com.co.app.broker.CommonDataBroker.getCustomerFormsData(CommonDataBroker.java:735)
                at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
                at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:79)
                at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
                at java.lang.reflect.Method.invoke(Method.java:618)
                at flex.messaging.services.remoting.adapters.JavaAdapter.invoke(JavaAdapter.java:421)
                at flex.messaging.services.RemotingService.serviceMessage(RemotingService.java:183)
                at flex.messaging.MessageBroker.routeMessageToService(MessageBroker.java:1503)
                at flex.messaging.endpoints.AbstractEndpoint.serviceMessage(AbstractEndpoint.java:884)
                at flex.messaging.endpoints.amf.MessageBrokerFilter.invoke(MessageBrokerFilter.java:121)
                at flex.messaging.endpoints.amf.LegacyFilter.invoke(LegacyFilter.java:158)
                at flex.messaging.endpoints.amf.SessionFilter.invoke(SessionFilter.java:44)
                at flex.messaging.endpoints.amf.BatchProcessFilter.invoke(BatchProcessFilter.java:67)
                at flex.messaging.endpoints.amf.SerializationFilter.invoke(SerializationFilter.java:146)
                at flex.messaging.endpoints.BaseHTTPEndpoint.service(BaseHTTPEndpoint.java:278)
                at flex.messaging.MessageBrokerServlet.service(MessageBrokerServlet.java:322)
                at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
                at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1146)
                at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:592)
                at com.ibm.ws.wswebcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:525)
                at com.ibm.ws.webcontainer.servlet.CacheServletWrapper.handleRequest(CacheServletWrapper.java:90)
                at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:764)
                at com.ibm.ws.wswebcontainer.WebContainer.handleRequest(WebContainer.java:1478)
                at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:133)
                at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:458)
                at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewInformation(HttpInboundLink.java:387)
                at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.ready(HttpInboundLink.java:267)
                at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.sendToDiscriminators(NewConnectionInitialReadCallback.java:214)
                at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.complete(NewConnectionInitialReadCallback.java:113)
                at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:165)
                at com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:217)
                at com.ibm.io.async.AsyncChannelFuture$1.run(AsyncChannelFuture.java:205)
                at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1497)
Caused by: java.sql.SQLException: [IBM][SQLServer JDBC Driver][SQLServer]Transaction (Process ID 368) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
                at com.ibm.websphere.jdbc.base.BaseExceptions.createException(Unknown Source)
                at com.ibm.websphere.jdbc.base.BaseExceptions.getException(Unknown Source)
                at com.ibm.websphere.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
                at com.ibm.websphere.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
                at com.ibm.websphere.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown Source)
                at com.ibm.websphere.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
                at com.ibm.websphere.jdbc.sqlserver.tds.TDSRequest.getRow(Unknown Source)
                at com.ibm.websphere.jdbc.sqlserver.SQLServerImplResultSet.fetchAtPosition(Unknown Source)
                at com.ibm.websphere.jdbc.base.BaseImplResultSet.next(Unknown Source)
                at com.ibm.websphere.jdbc.base.BaseResultSet.next(Unknown Source)
                at com.ibm.websphere.jdbcx.base.BaseResultSetWrapper.next(Unknown Source)
                at com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet.next(WSJdbcResultSet.java:2509)
                at org.hibernate.loader.Loader.doQuery(Loader.java:720)
                at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
                at org.hibernate.loader.Loader.loadCollection(Loader.java:2015)
                ... 70 more

[11/7/13 7:49:44:698 EDT] 00000049 SystemErr     R            at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:105)
[11/7/13 7:49:44:698 EDT] 00000049 SystemErr     R            at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
[11/7/13 7:49:44:699 EDT] 00000049 SystemErr     R            at org.hibernate.loader.Loader.loadCollection

11/7/13 13:49:11:605 EDT] 000001c7 SystemErr     R           at com.ibm.io.async.AsyncChannelFuture$1.run(AsyncChannelFuture.java:205)
[11/7/13 13:49:11:605 EDT] 000001c7 SystemErr     R          at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1497)
[11/7/13 13:49:11:605 EDT] 000001c7 SystemErr     R Caused by: org.hibernate.exception.LockAcquisitionException: could not delete: [com.co.app.domain.CustomerBusElemtVal#component[CustomerId,txnId,busnsElemtId,optnNbr]{txnId=22222, optnNbr=99, CustomerId=1111, busElementId=BUSNS_ELEM2}]
                at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:105)
                at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
                at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:2569)
                at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:2725)
                at org.hibernate.action.EntityDeleteAction.execute(EntityDeleteAction.java:97)

11/7/13 10:55:36:384 EDT] 00000153 SystemErr     R           at com.ibm.io.async.AsyncChannelFuture$1.run(AsyncChannelFuture.java:205)
[11/7/13 10:55:36:384 EDT] 00000153 SystemErr     R         at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1497)
[11/7/13 10:55:36:384 EDT] 00000153 SystemErr     R Caused by: org.hibernate.exception.LockAcquisitionException: could not update: [com.co.app.domain.Customer#component[CustomerId,txnId]{txnId=2222, CustomerId=1111}]
                at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:105)
                at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
                at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2453)
                at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:2335)
                at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2635)
                at org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java:115)
                at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279)
                at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263)
                at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:168)

nolock discussion http://stackoverflow.com/questions/686724/sql-when-should-you-use-with-nolock — Sanj, Nov 10 '13 at 00:14

score 2 · Accepted Answer · answered Nov 10 '13 at 00:21

Like you said this particular question is going to be very subjective. No lock hints on read only queries can improve performance but you run the risk of getting dirty data if your data is very transactional. I would say look into read committed snapshot or snapshot isolation to try and implement a more optimistic locking scenario. The exact answer will always vary from database to database and needs professional analysis to give you a 100% answer.

Thanks. Will check with DBA about snapshot isolation. – Vineet Bhatia Nov 10 '13 at 02:27 — Vineet Bhatia, Nov 10 '13 at 02:27

score 1 · Answer 2 · answered Nov 10 '13 at 00:24

One cannot give you a definite aswer without knowing your system but I hope my comments below may help.

I would be very careful with using NOLOCK, even for read-only data. If data integrity is important in your application, I would not use it at all. If you're using NOLOCK, you're also reading uncommitted data, e.g. data that is only partially written by some other process which may or may not be rolled back.

While you will avoid deadlocks, it may come at a much bigger cost. I think it's better to try to eliminate deadlocks to the minimum by the code/database redesign and recoding and handle occasional deadlocks when they happen.

Also note what is written on MSDN about using NOLOCK:

For UPDATE or DELETE statements: This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Thanks did not know that in future versions nolock for update or delete would be removed. In this application, one user works on a particular customer record (and associated data) at a time. Instead of nolock, we could also do the same by specifying transaction isolation level from middle-tier code. — Vineet Bhatia, Nov 10 '13 at 02:24

Using NOLOCK to eliminate deadlocks for application which has pessimistic locking

2 Answers2