Error when attempting to delete a record in SQL Server database

Posted on 2014-01-21
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||_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: 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
        at org.eclipse.persistence.exceptions.DatabaseException.sqlException(
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(
        at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(
        at org.eclipse.persistence.sessions.server.ClientSession.executeCall(
        at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(
        at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(
        at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.deleteObject(
        at org.eclipse.persistence.internal.queries.StatementQueryMechanism.deleteObject(
        at org.eclipse.persistence.queries.DeleteObjectQuery.executeDatabaseQuery(
        at org.eclipse.persistence.queries.DatabaseQuery.execute(
        at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(
        at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(
        at org.eclipse.persistence.queries.DeleteObjectQuery.executeInUnitOfWorkObjectLevelModifyQuery(
        at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWork(
        at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(
        at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(
        at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(
        at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(
        at org.eclipse.persistence.internal.sessions.CommitManager.deleteAllObjects(
        at org.eclipse.persistence.internal.sessions.CommitManager.deleteAllObjects(
        at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabase(
        at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitToDatabase(
        at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithChangeSet(
        at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.issueSQLbeforeCompletion(
        at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.issueSQLbeforeCompletion(
        at org.eclipse.persistence.transaction.AbstractSynchronizationListener.beforeCompletion(
        at org.eclipse.persistence.transaction.JTASynchronizationListener.beforeCompletion(
        at com.sun.jts.jta.SynchronizationImpl.before_completion(
        at com.sun.jts.CosTransactions.RegisteredSyncs.distributeBefore(
        at com.sun.jts.CosTransactions.TopCoordinator.beforeCompletion(
        at com.sun.jts.CosTransactions.CoordinatorTerm.commit(
        at com.sun.jts.CosTransactions.TerminatorImpl.commit(
        at com.sun.jts.CosTransactions.CurrentImpl.commit(
        at com.sun.jts.jta.TransactionManagerImpl.commit(
        at com.sun.enterprise.transaction.jts.JavaEETransactionManagerJTSDelegate.commitDistributedTransaction(
        at com.sun.enterprise.transaction.JavaEETransactionManagerSimplified.commit(
        at com.sun.enterprise.transaction.UserTransactionImpl.commit(
        at sec.web.action.myInfo.requiredActivity.RemoveActivityAction.removeActivity(
        at sec.web.action.myInfo.requiredActivity.RemoveActivityAction.execute(
        at org.apache.struts.action.RequestProcessor.processActionPerform(
        at sec.web.helper.StrutsReqProcessor.processActionPerform(
        at org.apache.struts.action.RequestProcessor.process(
        at org.apache.struts.action.ActionServlet.process(
        at org.apache.struts.action.ActionServlet.doPost(
        at javax.servlet.http.HttpServlet.service(
        at javax.servlet.http.HttpServlet.service(
        at org.apache.catalina.core.StandardWrapper.service(
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(
        at sec.web.filter.RequestFilter.doFilter(
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(
        at org.apache.catalina.core.StandardWrapperValve.invoke(
        at org.apache.catalina.core.StandardContextValve.invoke(
        at org.apache.catalina.core.StandardPipeline.doInvoke(
        at org.apache.catalina.core.StandardPipeline.invoke(
        at org.apache.catalina.core.StandardHostValve.invoke(
        at org.apache.catalina.core.StandardPipeline.doInvoke(
        at org.apache.catalina.core.StandardPipeline.invoke(
        at org.apache.catalina.connector.CoyoteAdapter.doService(
        at org.apache.catalina.connector.CoyoteAdapter.service(
        at com.sun.grizzly.http.ProcessorTask.invokeAdapter(
        at com.sun.grizzly.http.ProcessorTask.doProcess(
        at com.sun.grizzly.http.ProcessorTask.process(
        at com.sun.grizzly.http.DefaultProtocolFilter.execute(
        at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(
        at com.sun.grizzly.DefaultProtocolChain.execute(
        at com.sun.grizzly.DefaultProtocolChain.execute(
        at com.sun.grizzly.http.HttpProtocolChain.execute(
        at com.sun.grizzly.ProtocolChainContextTask.doCall(
        at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(
        at com.sun.grizzly.util.AbstractThreadPool$
Caused by: The query has timed out.
        at com.sun.gjc.spi.base.PreparedStatementWrapper.executeUpdate(
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(
        ... 79 more
Question by:gdkinney_2
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
LVL 28

Accepted Solution

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.

Author Comment

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.
LVL 13

Expert Comment

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.
LVL 12

Expert Comment

by:Sharon Seth
ID: 39799685
What happens when you execute the same DELETE stmnt from a DB client?

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Convert websphere application server default chained Certificates from 1024 to 2048 keysize or higher size and also you can change signatureAlgorithm . Please make sure Websphere Application Server fixpack or Above. The following steps a…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
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…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor ( Top Charts is a view in which you can set seve…

707 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