Solved

Error when attempting to delete a record in SQL Server database

Posted on 2014-01-21
4
817 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
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

Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

Question has a verified solution.

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

There is a huge demand for CodeIgniter among the PHP web developers due to its dynamic features and benefits these days. It is one of most popular and agile open source PHP framework for creating robust web applications in PHP web development field.…
ADCs have gained traction within the last decade, largely due to increased demand for legacy load balancing appliances to handle more advanced application delivery requirements and improve application performance.
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…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

821 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