January 23, 2008 at 11:33 am
Guys,
We converted SQL Server 2000 SE production environment to SQL Server 2005 (version 9.0.3054). All databases are in 80 compatibility mode. We have deadlocks on daily basis and Java application goes down. Java environment uses old jTDS driver - that's why we are not moving toward 90 compatibility level yet.
I've set up SQLProfiler lock event with 3 variables : Deadlock, Deadlock chain, Deadlock Graph and disabled all other events including, SQL:Batch Starting , SQL:Batch Completed. It captures lock event , but OVALS don't show SQL stmt(box is empty or refers to the object_id that doesn't exist). My another consideration is to change Query Wait parameter , but I don't know new value to set to (it's -1 by default).
Deadlock event lasts 3 sec, before victim gets chosen.
Please advice. Thank you, Mike.
January 23, 2008 at 11:44 am
Switch trace flag 1204 on. That makes SQL write the deadlock graph into the error log. It's not as pretty as the profiler version, but it will work.
If you're unsure how to interpret the deadlock graph, post it here. There are lots of people who know how to interpret those.
Changing the lock timeout won't change a thing. That just affects how long a process will wait for a lock (default of -1 is forever) Since a deadlock will never resolve on its own, and SQL knows that, the SQL engine will pick a victim as soon as a deadlock occurs and roll the selected process's transactions back.
Just for interest, is this a vendor Java app? If so, can you mention a name? I've had some very unpleasant experiences with a vendor java app deadlocking several times a minute.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 23, 2008 at 2:08 pm
Gail,
do i need to run SQLDiag to post Error log here ?
Our environment is a home grown Java application , not 3rd party...
Thank you for helping us, Mike.
January 23, 2008 at 3:34 pm
I used SQL Profiler a few months back to pull out Deadlock information. While I'm not entirely sure what your current issues are, I'm hoping that some of this will help.
I set up a Profiler job, tracking the "Deadlock Graph" and "Lock: Deadlock" events, and I included the TextData column in both sets. Lock: Deadlock was occasionally useful/informational, but the real data came from Deadlock Graph.
When a deadlock events occurs, selecting the Profiler row for Deadlock Graph for that event displays a graphic that attempts to explain what's going on (maximise the pane/window to make it more legible). This can be useful, but it is not the full picture of what's going on; behind the scenes, SQL is actually returning a chunk of detailed XML on the deadlock, and while Profiler does what it can to display it there's a LOT more information that fits in a readily comprehensible picture.
To get at the XML, I highlit the Deadlock Graph line in profiler, hit "cut", opened a text window somewhere, and hit "paste". This copies both the XML and the other columns that were selected; delete the other column's data and you have a valid chunk of XML.
I then opened up the XML [Sidebar: The Deadlock stuff is pretty detailed, so use whatever tool you are most comfortable with. I use Microsoft's "XML Notepad" utility (free download) to view XML] and spent a long time staring at it, until it started to make sense. There's object names, object_ids, and many other crunchy bits in there that will ultimately spell out precisely what's actually going on.
To mention, I was also working on an in-house developed Java application, which used a version of jTDS used in conjuntion with Hibernate, which together are known as :sick:. When I finally grokked what the two deadlocking queries were, and factored in all possible foreign key and indexing issues, I determined that it was physically impossible for them to deadlock. After assuming Bad Underlying Fundamentals and burning a lot more time on Profiler, I discovered that :sick: was opening connections with SET IMPLICIT_TRANSACTIONS on... meaning locks were persisting on statements executed before the deadlocking queries were run. Eventually the developers listened to me, the config files got reset, and we moved on to the next performance problem. I certainly hope you're not encountering anything as ugly as this!
Philip
January 23, 2008 at 4:02 pm
Philip,
thank you for the post. I will try to incorporate your finding and let you know how it works in my case.
Mike.
January 24, 2008 at 12:44 am
Mike Landa (1/23/2008)
Gail,do i need to run SQLDiag to post Error log here ?
No. It's written into the normal sql server error log. You can view the error log through management studio or, if you have access to the server, you can read the error log file with a text editor.
Probably management studio's easier.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 24, 2008 at 5:40 pm
Philip,
we have exactly the same case here as you had before.
IMPLICIT_TRANSACTIONS option behavior has changed and Java/Hibernate (jTDS old version 0.9) code does not commit every DML stmt (intended behavior) in SQL Server 2005 causing deadlocks.
It means that the option is ON which was not the case against SQL Server 2000. Why jDTS driver has changed its behavior - nobody knows, but what makes it even worse that the developers have no clue how to explicitly turn this option OFF in the hibernate configuration file... There is also a fear that it potentially can impact the application
Any suggestions ? Thanks again.
Mike.
January 24, 2008 at 9:21 pm
I believe it's a setting in a config file, for jTDS or Hibernate, or possibly a setting within Hibernate for a set or class of connections--definitely it was ON for some connections and OFF for others.
I made noise, the developers found and fixed something, and the network admin pushed the code. However, this was two-three months back, and I now work at another company (I chose to move on)... but I just IMed the network admin (thanks Brent!) and he sent me what he thinks might be the relevant part of the stuff he pushed back then:
[font="Courier New"]
<Resource name="jdbc/name" auth="Container" driverClass="net.sourceforge.jtds.jdbc.Driver"
initialPoolSize="5" minPoolSize="5" maxPoolSize="100"
acquireIncrement="5" maxIdleTime="91" idleConnectionTestPeriod="30"
factory="org.apache.naming.factory.BeanFactory"
type="com.mchange.v2.c3p0.ComboPooledDataSource"
user="username" password="password"
jdbcUrl="jdbc:jtds:sqlserver://db.domain.com;DatabaseName=dbname;sendStringParametersAsUnicode=false;defaultAutoCommit=false;useLO
Bs=false;wsid=webserverid;maxStatements=0;socketTimeout=35"/>[/font]
Now I don't know what this all means. c3p0 is used by Hibernate (at least as installed there), so this is a Hibernate-type config file, but which parts might control SET IMPLICIT_TRANSACTIONS is beyond me. I'd recommend passing this to your developers along with a link to Google and have them do the research. If it's not set in the strings above, a good search should dig up whatever really matters.
Oh, and that "sendStringParametersAsUnicdoe=false" is absolutely critical IF you don't store strings as Unicode. Some of our key indexes were being ignored, and it was a long time before I happened to notice that all the SARGs appearing in SQL Profiler where of the form N'searchstring'. Yes, me and :sick:, we're old friends.
Philip
January 25, 2008 at 12:44 pm
Philip,
thank you so much. Are you in Hawaii ? I'm in San Francisco.
Mike.
January 25, 2008 at 2:29 pm
I wish I were in Hawaii -- here in Chicago, temps are in the single digits, and negative with wind chill.
Philip
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply