Mailing-List: contact dev-help@openjpa.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@openjpa.apache.org
Received-SPF: pass (athena.apache.org: domain of michael.d.dick@gmail.com
 designates 209.85.146.183 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=beta;
        h=received:message-id:date:from:sender:to:subject:mime-version:content-type:x-google-sender-auth;
        b=RfQq4c6Rolt9tCcSpaES22L6A6UcaXTPPZ7x5tOq/tF1Lo4yFUWNtHqeJ61qLnylWjxeoHy7yqETWK7hAl06CL3ZQgcdJ0UTcBd5PRMG3Tzamx/firHK8z2syLXMtw2kQ5dhBtQ5cnovKgOEOJIr8IRTOPIPKqHmmk/KalvKnaA=
Message-ID: <72c1350f0710261638yedf4f8ci78ead2548b408e17@mail.gmail.com>
Date: Fri, 26 Oct 2007 18:38:00 -0500
From: "Michael Dick" <mikedd@apache.org>
Sender: michael.d.dick@gmail.com
To: dev@openjpa.apache.org
Subject: Sequence table problems with SQLServer
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_8361_28400912.1193441880555"

------=_Part_8361_28400912.1193441880555
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi,

I've run into tricky issue with Sequence tables on SQLServer.

After some time during a stress test I get this exception when trying to
update the sequence table:
openjpa.jdbc.SQL: Trace: <t 25198719, conn 17636058> executing prepstmnt
4953425 SELECT SEQUENCE_VALUE FROM OPENJPA_SEQUENCE_TABLE WHERE ID = ?
[params=(int) 0]
. . .
openjpa.jdbc.SQL: Trace: <t 25198719, conn 17636058> executing prepstmnt
19626156 UPDATE OPENJPA_SEQUENCE_TABLE SET SEQUENCE_VALUE = ? WHERE ID = ?
AND SEQUENCE_VALUE = ? [params=(long) 48601, (int) 0, (long) 48551]
. . .
java.sql.SQLException: [IBM][SQLServer JDBC Driver][SQLServer]Transaction
(Process ID 85) was deadlocked on lock resources with another process and
has been chosen as the deadlock victim. Rerun the transaction.
    at com.ibm.websphere.jdbc.base.BaseExceptions.createException(Unknown
Source)
    at com.ibm.websphere.jdbc.base.BaseExceptions.getException(Unknown
Source)
 . . .
    at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate
(DelegatingPreparedStatement.java:269)
    at
org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeUpdate
(LoggingConnectionDecorator.java:856)

I believe I'm hitting the exception because SQLServer doesn't support the
FOR UPDATE clause. Normally the first SQL statement shown above would have
locked the row preventing the deadlock. SQLServer does support the WITH
(UPDLOCK) hint, but I haven't found a convenient way to isolate the change
so that it only affects TableSequences.

Has anyone else run into this problem, or is there a better way to solve it
(than using WITH (UPDLOCK))?

-Mike

------=_Part_8361_28400912.1193441880555--