java - 使用 NOLOCK 消除具有悲观锁定的应用程序的死锁

Question

使用 SQL Server 数据库开发 Spring-Hibernate 应用程序。我们陷入僵局。事务属性是通过 Spring 配置定义的。该应用程序在应用程序代码中实现了悲观锁定。当用户开始处理客户记录时，该记录将被锁定。我们努力通过缩短事务、更改事务隔离级别等来消除锁。

作为替代方案，通过为导致锁定并因此导致死锁的 SQL 指定 NOLOCK 来消除数据库级锁定是否可行(尽管非常规)？

[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)

Answer 1

正如您所说，这个特定问题将非常主观。只读查询上没有锁定提示可以提高性能，但如果数据的事务性很强，则可能会面临获取脏数据的风险。我会说研究读已提交快照或快照隔离，以尝试实现更乐观的锁定方案。每个数据库的确切答案总是不同的，需要专业的分析才能给你 100% 的答案。