[CONJ-344] HibernateOptimisticLockingFailureException with Hibernate, Mariadb and Spring transactions Created: 2016-09-08  Updated: 2016-09-13  Resolved: 2016-09-13

Status: Closed
Project: MariaDB Connector/J
Component/s: Other
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: sunil k Assignee: Diego Dupin
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

We are migrating application from oracle to Mariadb. Using container transactions and Spring transaction management. I am getting error for code where the one record is saved and updated in same transaction(Service bean method). Same code is working fine for Oracle database.

I am the only one who is connected to the mariadb database. Help will be appreciated.

Error:

org.springframework.orm.hibernate4.HibernateOptimisticLockingFailureException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1; nested exception is org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1

JDK: 1.6, Tomcat: 7.0.65, Spring: 4.1, hibernate: 4.2.21

mariadb version: 10.1.13-MariaDB

driver: mariadb-java-client-1.1.9

Dialects: org.hibernate.dialect.MySQLInnoDBDialect

and i tried with org.hibernate.dialect.MySQLDialect also same error is coming.

@Entity
 
@Table(name = "DEAL")
 
public class Deal implements Serializable {
 
 
 
    private static final long serialVersionUID = 1196605299069938794L;
 
 
 
    @Id
 
    @Column(name = "SK_DEAL_ID")
 
    @GeneratedValue(strategy = GenerationType.TABLE, generator = "Deal_SEQ")
 
    @TableGenerator(name = "Deal_SEQ", pkColumnValue = "DEAL_SEQUENCE", allocationSize = 1)
 
    @Access(AccessType.PROPERTY)
 
    private Long id;
 
 
 
    @Column(name = "INT_DEAL_ID", length = 50)
 
    private String internalDealId;
 
 
 
    @Column(name = "DEAL_DESC", length = 100)
 
    private String description;
 
 
 
    @ManyToOne(fetch = FetchType.LAZY)
 
    @JoinColumn(name = "LAST_UPDATED_ID")
 
    private SysUser lastUpdatedId;
 
 
 
    @Version
 
    @Type(type = "timestamp")
 
    @Column(name = "LAST_UPDATED")
 
    private Date lastUpdated;
 
 
 
    @Type(type = "timestamp")
 
    @Column(name = "CREATION_DATE")
 
    private Date creationDate;
 
 
 
    @Type(type = "timestamp")
 
    @Column(name = "CREATION_DATE_USER_TZ")
 
    private Date creationDateUserTz;
 
 
 
    @Type(type = "date")
 
    @Column(name = "END_DATE")
 
    private Date endDate;
 
 
 
 
 
    public Long getId() {
 
        return id;
 
    }
 
 
 
    public void setId(Long id) {
 
        this.id = id;
 
    }
 
 
 
    public String getInternalDealId() {
 
        return this.internalDealId;
 
    }
 
 
 
    public void setInternalDealId(String internalDealId) {
 
        this.internalDealId = internalDealId;
 
    }
 
 
 
 
 
    public String getDescription() {
 
        return this.description;
 
    }
 
 
 
    public void setDescription(String description) {
 
        this.description = description;
 
    }
 
 
 
    public SysUser getLastUpdatedId() {
 
        return this.lastUpdatedId;
 
    }
 
 
 
    public void setLastUpdatedId(SysUser lastUpdatedId) {
 
        this.lastUpdatedId = lastUpdatedId;
 
    }
 
 
 
    public Date getLastUpdated() {
 
        return this.lastUpdated;
 
    }
 
 
 
    public void setLastUpdated(Date lastUpdated) {
 
        this.lastUpdated = lastUpdated;
 
    }
 
 
 
    public Date getCreationDate() {
 
        return this.creationDate;
 
    }
 
 
 
    public void setCreationDate(Date creationDate) {
 
        this.creationDate = creationDate;
 
    }
 
 
 
    public Date getCreationDateUserTz() {
 
        return creationDateUserTz;
 
    }
 
 
 
    public void setCreationDateUserTz(Date creationDateUserTz) {
 
        this.creationDateUserTz = creationDateUserTz;
 
    }
 
 
 
    public Date getEndDate() {
 
        return endDate;
 
    }
 
 
 
    public void setEndDate(Date endDate) {
 
        this.endDate = endDate;
 
    }
 
 
 
 
 
    @Override
 
    public String toString() {
 
        return new StringBuilder("{")
 
        .append("id=").append(id)
 
        .append(",internalDealId=").append(internalDealId)
 
        .append(",description=").append(description)
 
        .append(",lastUpdatedId=").append(lastUpdatedId != null ? lastUpdatedId.getId() : null)
 
        .append(",lastUpdated=").append(lastUpdated)
 
        .append(",creationDate=").append(creationDate)
 
        .append(",creationDateUserTz=").append(creationDateUserTz)
 
        .append(",endDate=").append(endDate)
 
        .append("}")
 
        .toString();
 
    }
 
 
 
}

Service class method

@Override
 
    @Target({ ElementType.METHOD, ElementType.TYPE })
 
    @Retention(RetentionPolicy.RUNTIME)
 
    @Transactional(readOnly = false, rollbackFor = Exception.class)
 
    public DealVO addNew(DealVO vo) {
 
        Deal deal = new Deal();
 
        vo.setEndDate(new Date());
 
        deal = convertDealVOToDeal(vo, deal);
 
        dealDao.save(deal);
 
        //Some other logic based on creation date
 
        //setting some other deal properties
 
        dealDao.save(deal);
 
        //original logger is removed as it contains sensitive deal info
 
        vo.setCreationDate(deal.getCreationDate());
 
        vo.setCreationDateUserTz(deal.getCreationDateUserTz());
 
        vo.setId(deal.getId());
 
        return vo;
 
    }

Dao class methods:

public Session getSession() {
 
        return getSessionFactory().getCurrentSession();
 
    }
 
 
 
    @Override
 
    @Target({ ElementType.METHOD, ElementType.TYPE })
 
    @Retention(RetentionPolicy.RUNTIME)
 
    @Transactional(readOnly = false, rollbackFor = Exception.class)
 
    public void save(T domain) {
 
        getSession().saveOrUpdate(domain);
 
    }

Queries generated after control coming out of service method

insert into DEAL (INT_DEAL_ID, DEAL_DESC, LAST_UPDATED_ID, CREATION_DATE, CREATION_DATE_USER_TZ, END_DATE, SK_DEAL_ID) values (?, ?, ?, ?, ?, ?, ?)
 
 
 
update DEAL set INT_DEAL_ID=?, DEAL_DESC=?, LAST_UPDATED_ID=?, CREATION_DATE=?, CREATION_DATE_USER_TZ=?, END_DATE=? where SK_DEAL_ID=? and LAST_UPDATED=?



 Comments   
Comment by Diego Dupin [ 2016-09-09 ]

Hi,

Can you send the connection url you use to connect to connect mariadb server ?
First impressions are :

  • last GA driver version is 1.5.2, better to upgrade to last version.
  • since you are using timestamp "@version", check that database is defined with micro timestamp precision of (6) :
    for your example :

"insert into DEAL (INT_DEAL_ID, DEAL_DESC, LAST_UPDATED_ID, CREATION_DATE, CREATION_DATE_USER_TZ, END_DATE, SK_DEAL_ID) values (?, ?, ?, ?, ?, ?, ?)"
if LAST_UPDATED is "09/09/2016 15:18:20.123456", but save in timestamp(0) = with no microseconds, this will be stored as "09/09/2016 15:18:20.000000"

"update DEAL set INT_DEAL_ID=?, DEAL_DESC=?, LAST_UPDATED_ID=?, CREATION_DATE=?, CREATION_DATE_USER_TZ=?, END_DATE=? where SK_DEAL_ID=? and LAST_UPDATED=?"
update will use "LAST_UPDATED" parameter "09/09/2016 15:18:20.123456", but since only "09/09/2016 15:18:20.000000" is store in DB, no row will be updated.
That's one of the reasons why Timestamp @version is not recommanded, in favor of numeric @version.

changind DDL from timestamp(0) to timestamp(6) will correct this.

Comment by sunil k [ 2016-09-10 ]

Hi Diego,

Thank you for your help, it worked after changing the last_updated(@version) column type from datetime to datetime(6).

Thanks,
Suinil K

Comment by Diego Dupin [ 2016-09-13 ]

you're welcome.
Closing issue

Generated at Thu Feb 08 03:14:58 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.