2 Replies Latest reply on Feb 26, 2004 7:33 AM by glyn_walters

    sql server locking

    glyn_walters Newbie


      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">
       <datasource-mapping>MS SQLSERVER2000</datasource-mapping>

      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.

        • 1. Re: sql server locking
          Adrian Brock Master

          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.


          • 2. Re: sql server locking
            glyn_walters Newbie

            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.