Solved

Error when attempting to delete a record in SQL Server database

Posted on 2014-01-21
4
831 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 500 total points
ID: 39797232
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
ID: 39797427
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
ID: 39797750
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
ID: 39799685
What happens when you execute the same DELETE stmnt from a DB client?
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL DATEADD 10 87
MS SQL Server select from Sub Table 14 48
Applying Roles in Common Scenarios 3 40
when sa SQL Login becomes locked 6 22
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…

740 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