dead locks in sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

dead locks in sql

Author

Topic

ajithprasath
Starting Member

3 Posts

Posted - 2008-07-30 : 00:30:03

I am getting dead locks in sql server 2000.

I am using jdbc sql server 2005 driver to connect to the database.

When seeing the process only one process is being blocked and the process are being blocked by the related process.

When analyzing the blocked process only shared and range s-s locks are there on the table. The other process which is blocked by this process has indent exclusive lock which is what affecting my project. I wanted to know whether because of lot of shared lock in a particular table this indent exclusive has happened.

Can anyone suggest reasons for the lock, whether it may be due to statements not being properly closed in application or some other reasons.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-07-30 : 03:53:56

Are you seeing blocking, or deadlocks?
If its deadlocks then one of the processes will be getting an error saying that it was involved in a deadlock and picked as the victim.

Locking can have a number of causes, long running queries, open transactions, etc. I've often seen JDBC using implicit transactions. That means the first statement will start a transaction that does not get automatically committed. You may want to check that. I think you'll find it on the properties of the connection string or the jdbc driver

--
Gail Shaw
SQL Server MVP

ajithprasath
Starting Member

3 Posts

Posted - 2008-07-30 : 05:19:19

hi all

i am also getting the following error DBComms.receive method error

- An exception occurred during the DBComms.receive method. Operation:socket closed. Context:(2962) [Thread[http-8080-Processor23,5,main], IO:242a0, Dbc:null]. PktNum:0. TotalReceived:0. PktSize:4,096.
com.microsoft.sqlserver.jdbc.SQLServerException: An exception occurred during the DBComms.receive method. Operation:socket closed. Context:(2962) [Thread[http-8080-Processor23,5,main], IO:242a0, Dbc:null]. PktNum:0. TotalReceived:0. PktSize:4,096.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.DBComms.receive(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
at mgeretail.objects.RetailQuote.getMarkupRate(RetailQuote.java:477)
at mgeretail.objects.RetailQuote.getMarkedupPrice(RetailQuote.java:561)
at mgeretail.view.SalesTransactions.go_action(SalesTransactions.java:3397)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.sun.faces.el.MethodBindingImpl.invoke(MethodBindingImpl.java:126)
at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:72)
at com.sun.rave.web.ui.appbase.faces.ActionListenerImpl.processAction(ActionListenerImpl.java:57)
at javax.faces.component.UICommand.broadcast(UICommand.java:312)
at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:267)
at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:381)
at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:75)
at com.sun.faces.lifecycle.LifecycleImpl.phase(LifecycleImpl.java:221)
at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:90)
at javax.faces.webapp.FacesServlet.service(FacesServlet.java:197)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at com.sun.rave.web.ui.util.UploadFilter.doFilter(UploadFilter.java:194)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at mgeretail.gateway.SecurityFilter.doFilter(SecurityFilter.java:205)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Unknown Source)

ajithprasath
Starting Member

3 Posts

Posted - 2008-07-30 : 06:03:52

hi GilaMonster

we are testing the application for 4-5 users. for that itself we are getting often the blocking problem and the system gets hanged. Can i know more about implicit transaction. Manually we are not setting implicit transaction parameter. Our connection polling context file is

<Context path="/mgeretail" docBase="C:/mgeretail/build/web" debug="0" privileged="true">

<Resource auth="Container"
description="My project datasource"
name="jdbc/mgeretail"
type="javax.sql.DataSource"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://utopia:1433;SelectMethod=cursor;DatabaseName=mge22"
maxActive="100" maxIdle="50" maxWait="70000" removeAbandoned="true" removeAbandonedTimeout="60" />

<Manager className="org.apache.catalina.session.PersistentManager" saveOnRestart="false"/>

</Context>

Normally we get the connection in autocommit as false and commit only when there is any updation. i seen that implicit transaction opens a transaction even for select statement. will that be a problem.

i get another error also often, which i have posted in the same post while testing for 4-5 users. because of that connection is getting closed automatically by driver because of which we face a problem

quote:
Originally posted by GilaMonster

Are you seeing blocking, or deadlocks?
If its deadlocks then one of the processes will be getting an error saying that it was involved in a deadlock and picked as the victim.

Locking can have a number of causes, long running queries, open transactions, etc. I've often seen JDBC using implicit transactions. That means the first statement will start a transaction that does not get automatically committed. You may want to check that. I think you'll find it on the properties of the connection string or the jdbc driver

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources