aspose file tools *
The moose likes Object Relational Mapping and the fly likes Hibernate DB Connection MS SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Hibernate DB Connection MS SQL "AWAITING COMMAND" all The Transaction" Watch "Hibernate DB Connection MS SQL "AWAITING COMMAND" all The Transaction" New topic
Author

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

Sameera Abeysekara Gunawardena
Ranch Hand

Joined: Feb 22, 2007
Posts: 37
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
Ranch Hand

Joined: Feb 22, 2007
Posts: 37
This is argent please help me

Sameera
Cameron Wallace McKenzie
author and cow tipper
Saloon Keeper

Joined: Aug 26, 2006
Posts: 4968
    
    1

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

-Cameron McKenzie
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

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


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Sameera Abeysekara Gunawardena
Ranch Hand

Joined: Feb 22, 2007
Posts: 37
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
Bartender

Joined: Apr 14, 2004
Posts: 10336


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
Ranch Hand

Joined: Feb 22, 2007
Posts: 37
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
Bartender

Joined: Apr 14, 2004
Posts: 10336

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
Ranch Hand

Joined: Feb 22, 2007
Posts: 37
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
Bartender

Joined: Apr 14, 2004
Posts: 10336

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
Ranch Hand

Joined: Feb 22, 2007
Posts: 37
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
Bartender

Joined: Apr 14, 2004
Posts: 10336

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
Ranch Hand

Joined: Feb 22, 2007
Posts: 37
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
Bartender

Joined: Apr 14, 2004
Posts: 10336


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
Ranch Hand

Joined: Feb 22, 2007
Posts: 37
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
Bartender

Joined: Apr 14, 2004
Posts: 10336


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
Ranch Hand

Joined: Feb 22, 2007
Posts: 37
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
Bartender

Joined: Apr 14, 2004
Posts: 10336

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
Ranch Hand

Joined: Feb 22, 2007
Posts: 37
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
Bartender

Joined: Apr 14, 2004
Posts: 10336

You are welcome
 
Consider Paul's rocket mass heater.
 
subject: Hibernate DB Connection MS SQL "AWAITING COMMAND" all The Transaction