Hibernate DB Connection MS SQL "AWAITING COMMAND" all The Transaction

[Sameera Abeysekara Gunawardena]

I write db transaction application with using frameworks struts and hibernate.

I have problem when did the any transaction MS SQL server keep there status "AWAITING COMMAND".i got these DB status using "SP_WHO" command.so this issue can be raise memory problem.because my application gets more memory then gives
out of memory exception this application deploy on tomcat.

please give me advices to solve these problems

Thanks
Sameera

[Sameera Abeysekara Gunawardena]

This is argent please help me

Sameera

[Cameron Wallace McKenzie]

How are you doing the transaction? Is it one transaction per request, or do you have multiple transactions per request?

-Cameron McKenzie

[Paul Sturrock]

Are you using a transaction for reads? Do you comit these?

[Sameera Abeysekara Gunawardena]

Cameron McKenzie

How are you doing the transaction?

What you mean in this question

Is it one transaction per request, or do you have multiple transactions per request?

Only one transaction per request.

Paul Sturrock

Are you using a transaction for reads? Do you comit these?

Yes i do transactions for read.No i didn't commit.

Sameera

[Paul Sturrock]

Yes i do transactions for read.No i didn't commit.

This is why SQL Server is in the awaiting command state, you have started a transaction then let it time out. You need to either not use a transaction to do a read (what does it gain you after all?) or properly end the transaction when you've finished with it.

[Sameera Abeysekara Gunawardena]

properly end the transaction when you've finished with it.

What you mean in above sentence How can i properly end read transaction

[Paul Sturrock]

Transactions are open unless they have been commited or rolled back. Do one or the other and SQL Serevr will stop waiting for acommand.

Wrapping reads in a transaction is a little dubious however. Not sure why you would necessarily want to do this in the first place?

[Sameera Abeysekara Gunawardena]

I write commit code statement after every read transactions but still occurred that problem dont you know any solution
to avoid this problem.

[Paul Sturrock]

I have given you the most likely solution. The awaiting command status means SQL Server is waiting for some input from the client. If you've made sure the client has not left any transaction open (e.g. what happens when an exception occurs? did you kill or allow to time out existsing spids in this state?) then there should be none.

An easier solution would be to not use transactions for reads. Why do you need transactional reads?

[Sameera Abeysekara Gunawardena]

i need to doing read transaction. if not how can i the get data from data base.i am not clear your solution please explain.

[Paul Sturrock]

What are transaction for? They are for ensuring data integrity during updates. If you are not updating anything why use one? You don't need a transaction to perform a select query.

[Sameera Abeysekara Gunawardena]

No i don't use the transaction when i select some thing.I want to update database.

What are transaction for? They are for ensuring data integrity during updates

i couldn't understand above what you tiring to tel.

[Paul Sturrock]

No i don't use the transaction when i select some thing.I want to update database.

Now I'm confused. You said (three times) that you use transactions for reads.

What I'm trying to say is if all you are doing is running select statements you don't need the overhead of a transaction; it gives you nothing. If you are doing updates then yes, you'll need a transaction and you will need to end it properly.

[Sameera Abeysekara Gunawardena]

No I trying to tell when i use transactions within the this application.
I am properly ended every transaction.

Can you send sample code for how can use transaction and how can transaction ended properly in using hibernate

[Paul Sturrock]

No I trying to tell when i use transactions within the this application.
I am properly ended every transaction.

OK, then you've done all you can. Assuming the transaction is properly ended then the only other thing that possibly could be causing SQL Server to wait is the connection pool (which you can't do anything about anyway).

So, re-reading your original problem. Do you actually have out of memory errors? Or are you just worried this may cause them? The awaiting command state shouldn't be causing much in the way of memory issues in Tomcat.

[Sameera Abeysekara Gunawardena]

So, re-reading your original problem. Do you actually have out of memory errors? Or are you just worried this may cause them? The awaiting command state shouldn't be causing much in the way of memory issues in Tomcat.

Yes I worried this may cause them.so how can be raised out of memory error i checked other codes very well.
Do you know about good memory profiling tool.

[Paul Sturrock]

So, you don't actually have any? I suspect you may be worrying about nothing then, its unlikely that the memory used by a connection holding on to resources would be the thing that causes an out of memory error.

We profile with Optimizeit or RSA. Both are pretty good. JProfiler is also supposed to be good.

[Sameera Abeysekara Gunawardena]

OK I will see Is there any memory leaks using you suggested tools.

Thanks for your grate advices i really appreciated.

Best Regards
Sameera

[Paul Sturrock]

You are welcome