Application Hang when delete() method execute

[Sameera Abeysekara Gunawardena]

I tried to delete table row using the delete() method(in side session object)
when execute delete method and commit() method application hang i couldn't understand what is the happening.

But i tried my sample Data Base In that time working fine.When i change to original Data Base this will happen.

This is my code :

session = dbc.DBConnector();
Transaction tra=session.beginTransaction();
session.delete(works);
tra.commit();
session.clear();


Thanks
Sameera

[Paul Sturrock]

Have you looked at the database to see if there are any threads blocking?

[Sameera Abeysekara Gunawardena]

Thanks for the reply

But cant understand how can I check weather if there are any threads blocking?
Do you have a ideas?

Thanks
Sameera

[Paul Sturrock]

What database are you using?

[Sameera Abeysekara Gunawardena]

MS SQL Server 2000

[Paul Sturrock]

SQL Server implements read committed transaction isolation by using the concept of "shared" locks. You get a shared lock whenever you issue a select statement. The lock is shared, so it wont prevent other people also selecting from the same table (they will share the same lock). However if you add any sort of data manipulation in there you can have issues resulting in blocks. Blocks are different from deadlocks. The database can recover from deadlocks - it just chooses a thread, makes it the deadlock victim and kills it. In the case of blocks the server can't do this - because blocks can be valid (if for example you are debugging, your application is holding the database in a certain state deliberately and you don't want that state to change). Unfortunately, if a client starts a transaction and does not commit or rollback this will leave a thread in the "awaiting command" state and also hold on to the shared locks.

Excute the procedure sp_who and look at the results. You should find somewhere in there threads that are blocked and the thread id that is blocking them. Look at the state of the blocking thread and which application is running it. Is it your app? You are using Hibernate so you are using transactions, so - are you commiting/rolling back them all as appropriate?

[Sameera Abeysekara Gunawardena]

I am doing this functions appropriately.I have no idea about how to execute procedure sp_who ?
Can you tell how is this same code work fine with other database

Thanks
Sameera

[Paul Sturrock]

Sameera Abeysekara Gunawardena wrote:I am doing this functions appropriately.I have no idea about how to execute procedure sp_who ?
Can you tell how is this same code work fine with other database

Thanks
Sameera

See the above explanation. Locks are data dependent and also dependent on who is connected to the database and what they are doing.

If you are using SQL Server I'd strongly recommend taking the time learning how to execute stored procedures. There is a wealth of information available from the system procedures, it will make your life so much easier.

Have a read of the SQL Server documentation; the section on SQL basics is what you need to learn. To get you going:

execute sp_who()

is the syntax you use to execute sp_who.

[Sameera Abeysekara Gunawardena]

I executed sp_who procedure and i checked the result.there is "awaiting commands" ,but when i delete command executed the state change to the DELETE but still hang the application.

How is release this shared lock in hibernate?

[Paul Sturrock]

I executed sp_who procedure and i checked the result.there is "awaiting commands" ,but when i delete command executed the state change to the DELETE but still hang the application.

And were any of these blocking other threads? (sp_who will tell you this too)

How is release this shared lock in hibernate?

You can't explicitly. If this is a blocking problem (did sp_who report any blocked threads?) then it is a database issue probably cause by unresolved transactions (though there could be other casues).

[Sameera Abeysekara Gunawardena]

And were any of these blocking other threads? (sp_who will tell you this too)

Yes there were some other blocking threads.

You can't explicitly. If this is a blocking problem (did sp_who report any blocked threads?) then it is a database issue probably cause by unresolved transactions (though there could be other casues).

You mean there is no any solution to that problem.If have any solution please tell.

[Paul Sturrock]

Did these blocking threads come from your applciation or another? If they came from yours look at your transaction handling. Do you commit or rollback every transaction?

[Sameera Abeysekara Gunawardena]

Yes through my application.
No i do not commit or rollback every transactions.



Thanks
Sameera

[Sameera Abeysekara Gunawardena]

Do you commit or rollback every transaction?

I added commit statement after every transactions bit still occurring same problem.


I checked the locking states using sp_who but still there is "awaiting command" state . Can we set default locking
method using connection URL or manually.

Do you have any other ideas about this?

Thanks
Sameera

[Paul Sturrock]

I checked the locking states using sp_who but still there is "awaiting command" state . Can we set default locking
method using connection URL or manually.

sp_who does not return a property called locking status, what do you mean by this? There will be many threads in the awaiting command state - this does not mean they are blocking. Do you have any thread in there reported as blocked? If you are not sure can you post the output from sp_who?

Can we set default locking method using connection URL or manually.

You can change the default transaction isolation level of the database. But I would not do this, since you either compromise performance or worse compromise data integrity going down this route. If you have a blocking problem this will be caused almost certainly by your code, so its much better to fix your code than workround your bug by tinkering with the database.

[Sameera Abeysekara Gunawardena]

Do you have any thread in there reported as blocked? If you are not sure can you post the output from sp_who?

No there is blocked treads.This is the result of when executing sp_who

51 0 sleeping sa SAMEERA 0 OPROWORKSHEET AWAITING COMMAND
52 0 runnable sa SAMEERA 0 OPROWORKSHEET SELECT
53 0 sleeping sa sameera.opropdc.lk 0 OPROWORKSHEET AWAITING COMMAND
54 0 sleeping sa sameera.opropdc.lk 0 OPROWORKSHEET AWAITING COMMAND
55 0 sleeping sa sameera.opropdc.lk 0 OPROWORKSHEET AWAITING COMMAND
56 0 sleeping sa sameera.opropdc.lk 0 OPROWORKSHEET AWAITING COMMAND
57 0 sleeping sa sameera.opropdc.lk 0 OPROWORKSHEET AWAITING COMMAND
58 0 sleeping sa sameera.opropdc.lk 57 OPROWORKSHEET DELETE

If you have a blocking problem this will be caused almost certainly by your code, so its much better to fix your code than workround your bug by tinkering with the database.

If how ever how can this same code work with other DataBase?

[Paul Sturrock]

OK, so sp_who it telling you that thread 58 is being blocked by thread 57. Thread 57 is in the awaiting command state, which means it is waiting for the client to do something. Normally this only ever occurs when the client has not commited or rolled back an open transaction. It can also happen when the client is debugging and paused on a break point. One thing we can be fairly sure of, its not the database itself that is causing the problem, its something the client is up to.

The hosts connecting to the database all appears to be you - its not a good idea to connect normally as sa, since sa would never be used in a production system and sa will behave differently than all other users.

I added commit statement after every transactions bit still occurring same problem.

...and this is always run? Even if there is an exception or error raised?

You appear to be using a custom class to get your Session. Can we see the code involved in this too?

I can't remember is SQL Server 200 has this, but you may have sp_who2 on your database. Try running that so we can find more out about the offending thread.

If how ever how can this same code work with other DataBase?

Is the schema identical? If it is almost certainly because the data is different. Blocks/deadlocks/poor performance are all very much data bound issues. My guess is the operation on one database does more than you perhaps realize because of the data. Whatever "works" is, does it have associations? Are they populated? Are they defiend with cascade deletes?

[Sameera Abeysekara Gunawardena]

its not a good idea to connect normally as sa, since sa would never be used in a production system and sa will behave differently than all other users

No i connected through the user not default one(i mean not sa).

...and this is always run? Even if there is an exception or error raised?

I didn't understand "...and this is always run?".No any exception or errors not raised.i am also wondering.when i delete statement executed application hang.

Can we see the code involved in this too?

session = dbc.DBConnector();//get the session from DBConnector class
Transaction tra=session.beginTransaction();
session.delete(works);//works is to be deleted object form database
tra.commit();
session.clear();

//Get session
SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
session=sessionFactory.openSession();
return session;

Is the schema identical?

Yes schema is identical.

Whatever "works" is, does it have associations? Are they populated? Are they defiend with cascade deletes?

No any relationships you mentioned above in side the "works" object.

[Paul Sturrock]

No i connected through the user not default one(i mean not sa).

You are connected as sa according to the output you posted above.

...and this is always run? Even if there is an exception or error raised?

I mean is every transaction commited or rolled back. Not the transaction round the code you are focused on now since this is where the trouble is but other code that uses the same tables. Do you roll transaction back in finally blocks if the code inside the transaction fails?

It was the code in the DBConnection class that I was interested in. How do you open/get your session?

Yes schema is identical.

Same indices? All the statistics up to date? Same data?

Any triggers involved?

[Sameera Abeysekara Gunawardena]

You are connected as sa according to the output you posted above.

No sure i am connected through the user(wstest).

I mean is every transaction commited or rolled back. Not the transaction round the code you are focused on now since this is where the trouble is but other code that uses the same tables.

Yes i did that after you told but not solved.I couldn't understand why this problem occur for one database i used same code and same data.

Same indices? All the statistics up to date? Same data?

Yes Same indices,All the statistics up to date,Same data

Any triggers involved?

No any triggers

[Paul Sturrock]

No sure i am connected through the user(wstest).

If so, the output of sp_who is wrong, since you are clearly connecting to the database as sa according to this. The forth column is the loginame - are you remembering on SQL Server that a database user and a server login are different things?

I'm not sure what else to suggest here. The output of sp_who shows that it is something in your application that is causing the block, but since you assert you are connecting as one user and the connection details in the sp_who output say you are connecting as another I am not sure the the output you posted is related to the problem.

Perhaps we can try a different approach: you could enable sql logging for Hibernate and watch for the exact query that hangs. Then perhaps you will have a better appreciation of which parts of your application to check for problems. Also, where do you close your Session? And can you post your session factor yconfiguration?

[Sameera Abeysekara Gunawardena]

where do you close your Session?

I never close the session because i accessed database object in my JSP pages.

can you post your session factor yconfiguration?

Do you want hibernate configuration file other thing?

[Paul Sturrock]

I never close the session because i accessed database object in my JSP pages

So you open one session (?) and all users of your application share this session instance?

Do you want hibernate configuration file other thing?

Yes.

Also, just to make sure I understand, the content of dbc.DBConnector() is this:

//Get session
SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
session=sessionFactory.openSession();
return session; 

So you are configuring a new session factory every time you request a new session?

[Sameera Abeysekara Gunawardena]

So you open one session (?) and all users of your application share this session instance?

No when need session in that create and configure the session.

My hibernate configuration file contents.
<hibernate-configuration>
<session-factory>
<!-- Database connection settings -->
<property name="hibernate.connection.driver_class">com.microsoft.jdbc.sqlserver.SQLServerDriver</property>
<property name="hibernate.connection.url">jdbc:sqlserver://127.0.0.1:1433;DatabaseName=OPROWORKSHEET;integratedSecurity=true;SelectMethod=Cursor</property>

<property name="hibernate.connection.username">wstest</property>
<property name="hibernate.connection.password">wstest</property>

<!-- JDBC connection pool (use the built-in) -->
<property name="hibernate.connection.pool_size">50</property>

<!-- Echo all executed SQL to stdout -->
<property name="show_sql">true</property>

<!--MSSQL dialect -->
<property name="dialect">org.hibernate.dialect.SQLServerDialect</property>


<!-- Mapping files -->
<mapping resource="Employee.hbm.xml"/>
<mapping resource="WorkSheet.hmb.xml"/>

</session-factory>

So you are configuring a new session factory every time you request a new session?

Yes i configured new session every time.

[Paul Sturrock]

OK so what we can see from this is you are connecting to SQL Server using the windows credentials of whatever machine you run your application on - you are not using the user wstest you think you are. I wouldn not reccomend windows integrated authentication because it uses sa privilages and (as I said before) sa is special.

You are also using a cursor for all database operations. Why? Using a cursor will generate extra scroll locking in addition to the normal shared locks issues for a select statement. This will probably increase the chance of blocks. Unless you have a good reason to I would not use cursors.

Yes i configured new session every time.

You are configuring a new SessionFactory every time. This very is bad. SessionFactories are heavy-weight things and typically should be configured once and shared. I'd change your DBCcnnect() method.

So you open one session (?) and all users of your application share this session instance?

But you never close any of them. This will eat up connectings, increase the number of locks on the database and slow Hibernate down. Have a read of the Hibernate documentation about session management.

[Sameera Abeysekara Gunawardena]

I got the solution

<property name="hibernate.connection.url">jdbc:sqlserver://127.0.0.1:1433;DatabaseName=OPROWORKSHEET;integratedSecurity=true;SelectMethod=Cursor</property>

when i removed "SelectMethod=Cursor" it work finely.I got the very important advices about "MS SQL Server" and "Hibernate" from you

Thanks for all the your help and advices.

Thanks
Sameera

[Paul Sturrock]

You are welcome. Glad you got it resolved.

[Ravindra Verma]

Great work Paul...