文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
首页
学习
活动
专区
圈层
工具
MCP广场
返回腾讯云官网
社区首页 >问答首页 >hibernate + microsoft sql server 2016 + microsoft jdbc的datetime列上的乐观锁定异常

问hibernate + microsoft sql server 2016 + microsoft jdbc的datetime列上的乐观锁定异常
EN

Stack Overflow用户
提问于 2018-09-04 17:05:47
回答 2查看 642关注 0票数 0

我对hibernate + mssql 2016 + microsoft jdbc driver + datetime列的组合有一个问题。

同样的软件，它与其他数据库(oracle，mysql，但也包括mssql < 2016 )和使用jtds驱动程序的mssql 2016完美配合，所以我认为问题出在microsoft jdbc驱动程序上。

我使用这个库版本：

代码语言：javascript
运行
复制
<dependencies>
  <dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-core</artifactId>
    <version>5.3.5.Final</version>
  </dependency>        
  <dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>7.0.0.jre8</version>
  </dependency>        
</dependencies>

hibernate.cfg.xml：

代码语言：javascript
运行
复制
<hibernate-configuration>
  <session-factory>
        <property name="connection.driver_class">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>
        <property name="connection.url">jdbc:sqlserver://sql2016host\Sql2016;databaseName=problem</property>
        <property name="connection.username">user</property>
        <property name="connection.password">password</property>

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

        <!-- SQL dialect -->
        <property name="dialect">org.hibernate.dialect.SQLServer2012Dialect</property>

        <!-- Enable Hibernate's automatic session context management -->
        <property name="current_session_context_class">thread</property>

        <!-- Disable the second-level cache  -->
        <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>

        <!-- Echo all executed SQL to stdout -->
        <property name="show_sql">true</property>
      <mapping resource="User.hbm.xml"/>
  </session-factory>
</hibernate-configuration>

User.hbm.xml：

代码语言：javascript
运行
复制
<hibernate-mapping package="...">
  <class name="User" table="USERS">
    <id name="id" type="long" column="ID">
      <generator class="native">
        <param name="sequence_name">HIBERNATE_SEQUENCE</param>
      </generator>
    </id>
    <timestamp name="lastChange" column="LAST_CHANGE"/>
    <property name="userId" column="USERID" type="string" not-null="true"/>
    <property name="domain" column="DOMAIN" type="string" />
    <property name="expiredOn" column="EXPIRED_ON" type="timestamp" />
    <property name="firstName" column="FIRSTNAME" type="string" not-null="true"/>
    <property name="lastName" column="LASTNAME" type="string" not-null="true"/>
    <property name="language" column="LANGUAGE" type="string" not-null="true"/>
    <property name="role" column="ROLE" type="long" not-null="true"/>
    <property name="powerManager" column="POWERMANAGER" type="boolean" not-null="true"/>
    <property name="notes" column="DESCRIPTION" type="string" not-null="false"/>
    <property name="company" column="COMPANY" type="string" not-null="false"/>
    <property name="organization" column="ORGANIZATION" type="string" not-null="false"/>

  </class>
</hibernate-mapping>

数据库表：

代码语言：javascript
运行
复制
CREATE TABLE USERS(
    ID numeric(19, 0) IDENTITY(1,1) NOT NULL,
    LAST_CHANGE datetime NOT NULL,
    USERID nvarchar(64) NOT NULL,
    DOMAIN nvarchar(64) NULL,
    SID nvarchar(255) NULL,
    EXPIRED_ON datetime NULL,
    FIRSTNAME nvarchar(255) NOT NULL,
    LASTNAME nvarchar(255) NOT NULL,
    LANGUAGE nvarchar(255) NOT NULL,
    ROLE numeric(19, 0) NOT NULL,
    POWERMANAGER tinyint NULL,
    AUTH_TYPE int NULL,
    AUTH_PWD_ID numeric(19, 0) NULL,
    AUTH_PWD_CHANGE tinyint NULL,
    AUTH_PWD_NOEXPIRE tinyint NULL,
    AUTH_PWD_ENFORCE_POLICIES tinyint NULL,
    AUTH_LOGIN_SUCCESS_DATE datetime NULL,
    AUTH_LOGIN_ERROR_DATE datetime NULL,
    AUTH_LOGIN_ERROR_COUNT int NOT NULL,
    DESCRIPTION nvarchar(255) NULL,
    COMPANY nvarchar(64) NULL,
    ORGANIZATION nvarchar(64) NULL
)

User.java：

代码语言：javascript
运行
复制
public class User {

    private long id;
    private Date lastChange;        

    private String userId;
    private String domain;
    private String firstName;
    private String lastName;
    private String language;
    private String notes;
    private String company;
    private String organization;
    private Date expiredOn;
    private long role;
    private boolean powerManager;

    public User() {
    }

    public long getId() ..
    public void setId(long id) ...

    public Date getLastChange() ...
    public void setLastChange(Date lastChange) ...      

    public String getUserId() ...
    public void setUserId(String userId) ...

    public String getDomain() ...
    public void setDomain(String domain) ...

    ....
}

Main.java，这是一个命令行，单线程main()：

代码语言：javascript
运行
复制
    private void test() {

        try {
            Session session = HibernateUtil.getSessionFactory().getCurrentSession();

            session.beginTransaction();

            // i load and update the user 'USER'        
            User u = getUserAuth(session, "USER");
            u.setCompany("NEWCO");
            session.update(u);

            session.getTransaction().commit();
        } catch(Exception e) {
            e.printStackTrace();
        }
    }

    private User getUserAuth(Session session, String userId) throws Exception
    {
      TypedQuery<User> query = session.createQuery("from User u where u.domain = NULL and upper(u.userId) = upper(:userId)", User.class);
      query.setParameter("userId", userId);
      List<User> users = query.getResultList();
      if (users.size() != 1)
         throw new Exception(userId);
      return users.get(0);
    }

Hibernate的SQL日志：

代码语言：javascript
运行
复制
Hibernate: select user0_.ID as 0_, user0_.LAST_CHANGE as JS2_0_, user0_.USERID as JS3_0_, user0_.DOMAIN as JS4_0_, user0_.EXPIRED_ON as JS5_0_, user0_.FIRSTNAME as JS6_0_, user0_.LASTNAME as JS7_0_, user0_.LANGUAGE as JS8_0_, user0_.ROLE as JS9_0_, user0_.POWERMANAGER as JS10_0_, user0_.DESCRIPTION as JS11_0_, user0_.COMPANY as JS12_0_, user0_.ORGANIZATION as JS13_0_ from USERS user0_ where (user0_.DOMAIN is null) and upper(user0_.USERID)=upper(?)
Hibernate: update USERS set LAST_CHANGE=?, USERID=?, DOMAIN=?, EXPIRED_ON=?, FIRSTNAME=?, LASTNAME=?, LANGUAGE=?, ROLE=?, POWERMANAGER=?, DESCRIPTION=?, COMPANY=?, ORGANIZATION=? where ID=? and LAST_CHANGE=?

异常日志：

代码语言：javascript
运行
复制
ERROR: HHH000346: Error during managed flush [Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect) : [....User#6]]
javax.persistence.OptimisticLockException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect) : [.....User#6]
    at org.hibernate.internal.ExceptionConverterImpl.wrapStaleStateException(ExceptionConverterImpl.java:226)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:93)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188)
    at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1460)
    at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:511)
    at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3283)
    at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2479)
    at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:473)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:178)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$300(JdbcResourceLocalTransactionCoordinatorImpl.java:39)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:271)
    at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:98)
    at ....BugTimestamp.test(BugTimestamp.java:43)
    at ....BugTimestamp.main(BugTimestamp.java:19)
Caused by: org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect) : [...User#6]
    at org.hibernate.persister.entity.AbstractEntityPersister.check(AbstractEntityPersister.java:2522)
    at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3355)
    at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:3229)
    at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3630)
    at org.hibernate.action.internal.EntityUpdateAction.execute(EntityUpdateAction.java:146)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:478)
    at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:356)
    at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39)
    at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1454)
    ... 10 more

如果我从切换到(带有一个数字数据库列)，它就可以工作。

有没有人有同样的问题？

谢谢,

大卫

sql-server-2016
java
sql-server
hibernate
EN

回答 2

Stack Overflow用户

发布于 2018-09-04 17:42:15

当驱动程序请求数据库的日期时，可能存在错误，这是默认的Hibernate行为，使用时间戳类型作为乐观锁定。尝试使用另一种方法，直接从JVM获取时间。这可以使用HBM文件中的预定义属性。

代码语言：javascript
运行
复制
<timestamp name="lastChange" column="JS1_LAST_CHANGE" source="vm"/>

试一试，这应该是可行的，但要意识到这个解决方案的缺点，正如官方Hibernate文档中强调的那样(集群，更多jvm，...)

票数 0
EN

Stack Overflow用户

发布于 2022-01-28 19:55:55

存在2016的change in datetime precision，这会导致update语句在任何行上都无法匹配。

通过在连接属性中设置sendTemporalDataTypesAsStringForBulkCopy=false，我取得了一些成功。这只适用于Microsoft's SQLServer driver, version 8.4+

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/52162678

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2025 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档