When I do a saveOrUpdate(Object) in a DAO instantiated inside a webservice, the sql tables lock. Has anyone else run into this?

Application XML

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:jaxws="http://cxf.apache.org/jaxws"
xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/b ... ns-2.5.xsd
http://cxf.apache.org/jaxws http://cxf.apache.org/schemas/jaxws.xsd
">

<import resource="classpath:META-INF/cxf/cxf.xml" />
<import resource="classpath:META-INF/cxf/cxf-extension-soap.xml" />
<import resource="classpath:META-INF/cxf/cxf-servlet.xml" />

<jaxws:endpoint id="RCM-CallType" implementor="com.rcm.ws.CallType_Parameters_Service_Impl" address="/callType"></jaxws:endpoint>
<jaxws:endpoint id="RCM-Hours" implementor="com.rcm.ws.CallTypeOpenCloseHours_Service_Impl" address="/openCloseHours"></jaxws:endpoint>
<jaxws:endpoint id="RCM-DNIS" implementor="com.rcm.ws.DNIS_Table_Service_Impl" address="/DNIS"></jaxws:endpoint>


<bean id="hibernateSessionFactory"
class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="configLocation" value="WEB-INF/classes/hibernate.cfg.xml"/>
</bean>

<bean id="DNIS_Table" class="com.rcm.model.DNIS_Table">

</bean>

<bean id="CallType_Parameters" class="com.rcm.model.CallType_Parameters">

</bean>

<bean id="CallTypeOpenCloseHours" class="com.rcm.model.CallTypeOpenCloseHours">

</bean>

<bean id="RCMDNISDAO" class="com.rcm.utils.RCMDNISDAO" scope="singleton">
<property name="sessionFactory"><ref local="hibernateSessionFactory"></ref></property>
</bean>

<bean id="DNIS_Table_Service_Impl" class="com.rcm.ws.DNIS_Table_Service_Impl">

</bean>
</beans>

Hibernate.cfg.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory name="sessionfactory">
<property name="hibernate.connection.driver_class">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>
<property name="hibernate.connection.password">strategy_user</property>
<property name="hibernate.connection.url">jdbc:sqlserver://localhost:1433;DatabaseName=routing_db;SelectMethod=cursor</property>
<property name="hibernate.connection.username">strategy_user</property>
<property name="hibernate.connection.release_mode">auto</property>
<property name="show_sql">true</property>
<mapping resource="DNIS.hbm.xml"></mapping>
<mapping resource="CallType.hbm.xml"></mapping>
<mapping resource="Hours.hbm.xml"></mapping>
</session-factory>
</hibernate-configuration>

HBM file

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.rcm.model">
<class name="DNIS_Table" table="dbo.DNIS_CallType_Map">
<id name="Dnis" type="string" column="DNIS"></id>
<property name="calltype" type="string" column="CallType"></property>
<property name="tollFreeNumber" type="string" column="tfn"></property>
</class>
</hibernate-mapping>

DAO

/**
*
*/
package com.rcm.utils;

import javax.servlet.ServletContext;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.springframework.web.context.support.WebApplicationContextUtils;

import com.rcm.model.DNIS_Table;

/**
* @author cjems
*
*/
public class RCMDNISDAO extends HibernateDaoSupport{
private WebApplicationContextUtils wacu;
private Session session;
private ServletContext sc;
private Log log = LogFactory.getLog(this.getClass());

/**
*
*/
public RCMDNISDAO() {
// TODO Auto-generated constructor stub
log.debug("Inside RCMDNISDAO");
}

public void create(DNIS_Table dt){
log.debug("Inside RCMDNISDAO.create");
getHibernateTemplate().saveOrUpdate(dt);
log.debug("Leaving RCMDNISDAO.create");

}

}

Web Service IMPL [CXF]

/* (non-Javadoc)
* @see com.rcm.ws.DNIS_Table_Service#create(com.rcm.model.DNIS_Table)
*/
@Override
public DNIS_Table create(DNIS_Table dt) {
log.debug("Trying to create DNIS");
RCMDNISDAO r = (RCMDNISDAO)getApplicationContext().getBean("RCMDNISDAO");
r.create(dt);
return null;
}


Any help is greatly appreciated

Are you sure that the entire table gets locked (= TABLE-LOCK )?
Or may it be other kind of locks i.e. page-locks.
Can you please show the detailed output of the lock's on the database.

(Me personally get out of such problems by enabling SQLSERVER's READ_COMMITTED_SNAPSHOT isolation database option)

Once I've turned on READ_COMMITTED_SNAPSHOT isolation level on SQL Server at the database level, how do I get hibernate to use it?

You must use the normal java.sql.Connection.TRANSACTION_READ_COMMITTED option on hibernate (=default),
it is then the Database which knows to use READ_COMMITTED_SNAPSHOT instead to normal READ_COMMITTED: