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

Network it in WD Red

There's an industry-leading WD Red drive for every compatible NAS system to help fulfill your data storage needs. With drives up to 8TB, WD Red offers a wide array of solutions for customers looking to build the biggest, best-performing NAS storage solution.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Merge Statement 3 40
SQL Server stored proc 2 13
ORA-00923: FROM keyword not found where expected 3 36
Changing the datatype of a column from nvarchar to date 47 15
Recently I spent hours debugging an issue in a Rails project where ActiveRecord was causing MySQL errors trying to create a User object of a class at the top level of a Single Table Inheritance model structure.  It turns out `.create` behaves differ…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

867 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

22 Experts available now in Live!

Get 1:1 Help Now