Dead lock issue

Question

Post reply

Dead lock issue

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Lavanyasri SSCarpal Tunnel Points: 4907 More actions · Answer 1

HI Gila,

Nothing more that can really be said with the little you posted.

Thanks for the update. What are the inputs you required from my side to find out this issue ??

I said same thing [what u mentioned in the mail ] to one of my superior , I got a very strange response from him "" application team don't write the SQL related code " like

{

EXEC master..xp_sqljdbc_xa_end @P0 OUT, @P1 OUT, @P2, @P3, @P4

EXEC master..xp_sqljdbc_xa_start @P0 OUT, @P1 OUT, @P2, @P3, @P4, @P5 OUT, @P6

EXEC master..xp_sqljdbc_xa_commit @P0 OUT, @P1 OUT, @P2, @P3, @P4

}

Thanks,

Lavanya

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

I never said the app team did. Those are Java transactional procedures and are used by Java to process transactions. They're not user-written, they can't be user-customised. If they're causing a problem you need to speak with Java people or do some searching for similar problems, as in general those will not give problems and they can't be tuned.

For SQL performance problems:

If you have no idea where to start, get a specialist in to help. Or

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lavanyasri SSCarpal Tunnel Points: 4907 More actions · Answer 3

HI Gila,

My windows server is windows 2008 standard edition .

http://support.microsoft.com/default.aspx?scid=kb;en-us;817066

As per the above article we need to Registry Entries Are Required for XA Transaction Support . I checked with my registry enteries ,its not configured in my windows system .

Now my question is Registry Entries Are Required for XA Transaction Support ???

Sorry for breaking the chain .. but i need to resolve this issue ASAP .

Thanks

Lavanya

SpringTownDBA SSCertifiable Points: 6806 More actions · Answer 4

I getting an error from appllication side is No ManagedConnections available within configured blocking timeout

10 seconds on google says this is a timeout retrieving a connection from the connection pool and you've got a J2EE application.

Two likely possibilites:

1. Either the application has increased in usage and the configured max connection pool size is now too small (java config change)

2. Java programmers introduced a bug related to how they handle connections and/or transactions that is preventing connections from being reused. (Java code change)

Iam facing some performance issues with my server . I am trying to find out root cause of this , so i am going step by step.

1. I try to find out deadlocks by enabling dbcc and profiler -- no deadlocks

2. i find out some queries and posted -- as per u r suggestion its related to JDBC

3. Now i am looking for isloation levels [may be it cause for performance ]

Final my aim is to find out the performance issue is related to db side or application side .

What performance issues? Did the issues start suddenly or gradually? Have you check the cpu/memory usage on the application server?

HI Gila ,

Thanks for the quick response . At that time i find a query

(@P0 int OUTPUT,@P1 nvarchar(4000) OUTPUT,@P2 varbinary(8000),@P3 varbinary(8000),@P4 int)EXEC master..xp_sqljdbc_xa_commit @P0 OUT, @P1 OUT, @P2, @P3, @P4

I am continuously seeing the below queries taking more cpu time .

"i am seeing the below queries taking more cpu compare to other " provides no useful information.

Is this query completing successfully?

Is it timing out?

Is it returning an error message?

How long does it take to run? .25 seconds? 30 seconds?

how often is it being called? 10 times per second? 3-4 times per minute?

As others have mentioned, these are related to jdbc xa transactions. These are external stored procedures (aka, functions that live in a dll) whose performance doesn't have anything to do with sql server databases or deadlocks or transaction isolation levels. They rely on MSDTC. If you're seeing slow performance with them only, then your problem is MSDTC.

see http://msdn.microsoft.com/en-us/library/ms378940(v=SQL.100).aspx for more details on sqlserver's support and implementation of xa transactions.

So, we've got a java connection pool that is running out of connections and bad performance from MSDTC.

Doesn't sound like a sql server problem.

good luck.