Solved

Error when attempting to delete a record in SQL Server database

Posted on 2014-01-21
4
799 Views
Last Modified: 2014-02-08
Hi we are getting an exception when attempting to delete a record from our SQL Sever database.  Our application is Java J2EE application.  We are using SQL Server 2008 for the database, Glassfish 3.1.2, and Struts version 1.8 I think.  Here is the stacktrace:

[#|2014-01-17T08:19:07.325-0700|WARNING|glassfish3.1.2|javax.enterprise.system.core.transaction.com.sun.jts.jta|_ThreadID=131;_ThreadName=Thread-2;|JTS5054: Unexpected error occurred in after completion
Local Exception Stack:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.4.0.v20120608-r11652): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: The query has timed out.
Error Code: 0
Call: DELETE FROM tbl450Disclosures WHERE (id = ?)
        bind => [1 parameter bound]
Query: DeleteObjectQuery(processStatusId=756141processDefinition= processDefinitionId=5 processDefinitionName=OGE 450 New EntrantprocessPathDesc=mil.army.us.sec.fdm.disclosure.oge450.DisclosureStateOGE450@2480c99b)
        at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:324)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:851)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:913)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:594)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:537)
        at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1800)
        at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:286)
        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.deleteObject(DatasourceCallQueryMechanism.java:184)
        at org.eclipse.persistence.internal.queries.StatementQueryMechanism.deleteObject(StatementQueryMechanism.java:101)
        at org.eclipse.persistence.queries.DeleteObjectQuery.executeDatabaseQuery(DeleteObjectQuery.java:214)
        at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:852)
        at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:751)
        at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:108)
        at org.eclipse.persistence.queries.DeleteObjectQuery.executeInUnitOfWorkObjectLevelModifyQuery(DeleteObjectQuery.java:115)
        at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:85)
        at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2875)
        at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1602)
        at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1584)
        at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1535)
        at org.eclipse.persistence.internal.sessions.CommitManager.deleteAllObjects(CommitManager.java:334)
        at org.eclipse.persistence.internal.sessions.CommitManager.deleteAllObjects(CommitManager.java:288)
        at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1422)
        at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitToDatabase(RepeatableWriteUnitOfWork.java:634)
        at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithChangeSet(UnitOfWorkImpl.java:1509)
        at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.issueSQLbeforeCompletion(UnitOfWorkImpl.java:3147)
        at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.issueSQLbeforeCompletion(RepeatableWriteUnitOfWork.java:345)
        at org.eclipse.persistence.transaction.AbstractSynchronizationListener.beforeCompletion(AbstractSynchronizationListener.java:157)
        at org.eclipse.persistence.transaction.JTASynchronizationListener.beforeCompletion(JTASynchronizationListener.java:68)
        at com.sun.jts.jta.SynchronizationImpl.before_completion(SynchronizationImpl.java:99)
        at com.sun.jts.CosTransactions.RegisteredSyncs.distributeBefore(RegisteredSyncs.java:158)
        at com.sun.jts.CosTransactions.TopCoordinator.beforeCompletion(TopCoordinator.java:2548)
        at com.sun.jts.CosTransactions.CoordinatorTerm.commit(CoordinatorTerm.java:279)
        at com.sun.jts.CosTransactions.TerminatorImpl.commit(TerminatorImpl.java:250)
        at com.sun.jts.CosTransactions.CurrentImpl.commit(CurrentImpl.java:633)
        at com.sun.jts.jta.TransactionManagerImpl.commit(TransactionManagerImpl.java:332)
        at com.sun.enterprise.transaction.jts.JavaEETransactionManagerJTSDelegate.commitDistributedTransaction(JavaEETransactionManagerJTSDelegate.java:185)
        at com.sun.enterprise.transaction.JavaEETransactionManagerSimplified.commit(JavaEETransactionManagerSimplified.java:861)
        at com.sun.enterprise.transaction.UserTransactionImpl.commit(UserTransactionImpl.java:208)
        at mil.army.us.sec.core.persistence.PersistenceManager.commitTX(PersistenceManager.java:245)
        at sec.web.action.myInfo.requiredActivity.RemoveActivityAction.removeActivity(RemoveActivityAction.java:114)
        at sec.web.action.myInfo.requiredActivity.RemoveActivityAction.execute(RemoveActivityAction.java:186)
        at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
        at sec.web.helper.StrutsReqProcessor.processActionPerform(StrutsReqProcessor.java:108)
        at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
        at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
        at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:688)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:770)
        at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1550)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:343)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:217)
        at sec.web.filter.RequestFilter.doFilter(RequestFilter.java:97)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:256)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:217)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:279)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
        at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:655)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:595)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:161)
        at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:655)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:595)
        at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:328)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:231)
        at com.sun.enterprise.v3.services.impl.ContainerMapper$AdapterCallable.call(ContainerMapper.java:317)
        at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:195)
        at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:860)
        at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:757)
        at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:1056)
        at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:229)
        at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:137)
        at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:104)
        at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:90)
        at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:79)
        at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:54)
        at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:59)
        at com.sun.grizzly.ContextTask.run(ContextTask.java:71)
        at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:532)
        at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:513)
        at java.lang.Thread.run(Thread.java:722)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The query has timed out.
        at com.microsoft.sqlserver.jdbc.TDSCommand.checkForInterrupt(IOBuffer.java:5918)
        at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:70)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1510)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:314)
        at com.sun.gjc.spi.base.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:125)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:842)
        ... 79 more
|#]
0
Comment
Question by:gdkinney_2
4 Comments
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 500 total points
Comment Utility
It looks as though the query is timing out (from line 8 of your exception stack). There's likely not a permission-related issue, but there are a couple of potential causes here:

 - Your statement is trying to delete a lot of records at once and it's taking longer than the timeout setting in your Java application. To see if this is the case, change your statement to a SELECT statement and look at how many rows are returned. Then, you can either extend the timeout for your query, or you can break your delete into smaller batches.
 - Your statement is being blocked by another option transaction, and is expiring the timeout waiting for that transaction to complete. If this is the case, your connection should appear to hang until it times out - while it's hung, you (or one of your DBAs) can execute an "EXEC SP_WHO2" on the SQL Server and look for a transaction that has a value in the "BLK" column - this means the transaction is blocked, and the blocking process will be listed in that column.
0
 

Author Comment

by:gdkinney_2
Comment Utility
Hi ryanmccauley,

I searched through the code and I only found one place where a delete was done on the table "tbl450Disclosures".  It is as follows:

"delete from tbl450disclosures where id = @disclosureid and activityid = @activityid".

But as you can see the only delete on table tbl450disclosures in the code uses 2 parameters.  Could this be an issue?  It looks like the delete in the stacktrace uses 1 parameter.
0
 
LVL 13

Expert Comment

by:magarity
Comment Utility
The case of the missing second bind variable is independent of why it is timing out.  I agree it is probably just because of another transaction being held open. Follow the steps above and get the sp who while the process is running.
0
 
LVL 12

Expert Comment

by:Sharon Seth
Comment Utility
What happens when you execute the same DELETE stmnt from a DB client?
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
The purpose of this video is to demonstrate how to set up the WordPress backend so that each page automatically generates a Mailchimp signup form in the sidebar. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now