sql server locking

[glyn_walters]

Hi

Does anyone successfully use sql server as the CMP datasource in a clustered environment?

I am using commit option B and have the following settings in jbosscmp-jdbc:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE jbosscmp-jdbc PUBLIC "-//JBoss//DTD JBOSSCMP-JDBC 3.2//EN" "http://www.jboss.org/j2ee/dtd/jbosscmp-jdbc_3_2.dtd">

<jbosscmp-jdbc>
 <enterprise-beans>
 <entity>
 <ejb-name>BBClientEJB</ejb-name>
 <datasource>java:/MS-JBOSS</datasource>
 <datasource-mapping>MS SQLSERVER2000</datasource-mapping>
 <row-locking>TRUE</row-locking>
 </entity>
 </enterprise-beans>
</jbosscmp-jdbc>

The datasource mapping is the standardjbosscmp-jdbc settings with the row locking attribute set to:

SELECT ?1 FROM ?2 with (xlock) WHERE ?3

I get a lot of deadlocking just calling getters and setters on my clustered EJBs. I would be grateful to know if anyone has had any success in this environment with sqlserver as I am wondering if it is sqlserver's row locking capabilities that might be the issue.

Thanks for any advice.
Glyn

[adrian.brock]

You often get problems with MSSQL server
when there are no indices supporting your primary or foreign key constraints.

JBossCMP just adds the constraints, it does not add the indicies.

Regards,
Adrian

[glyn_walters]

Thanks for the advice. I have seen a few issues users on these boards have been having with sql server. Particularly the way sql server may promote a row lock to a page or table lock. I am stuck with using sql server and am nervous about whether I will be creating problems for myself trying to use it in a clustered environment but will carry on with it and monitor the locking situation.