Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Sameera Abeysekara Gunawardena
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is argent please help me

Sameera
 
Cameron Wallace McKenzie
author and cow tipper
Saloon Keeper
Posts: 4968
1
Hibernate Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Are you using a transaction for reads? Do you comit these?
 
Sameera Abeysekara Gunawardena
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You are welcome
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic