CODEWITHSUNDEEP
javamysqlhibernatelocking
chiperortiz
chiperortiz

Reputation: 4991

Java Hibernate MySQL table locking

I have a table in MySQL like this

Table Accumulator which have something like this.

id-> autoIncrement.
year->int(11)
key->varchar(255)
value(int 11)

The behavior of this table is only one value for year and for key something like this.

 ID   YEAR  KEY      VALUE
 1    2014  STOCK    13
 2    2014  QUOTE    10
 3    2014  INVOICE  20

this values are not allowed.

 ID   YEAR  KEY      VALUE
 1    2014  STOCK    13
 2    2014  QUOTE    10
 3    2014  STOCK    20

We can't have two records with the same YEAR and KEY

I have a DAO method like this.

public Accumulator get(final String key,final Integer year)
{   
    final Session session = currentSession();
    final org.hibernate.Query query= session
            .getNamedQuery("findAccumulatorByKeyAndYear")
            .setParameter("key",key)
            .setParameter("year",year)
    query.setLockOptions(LockOptions.UPGRADE)
         .setResultTransformer(transformer(clazz));
    return (Acumulator)query.uniqueResult();
}

Which is called from a Manager method:

all this happens in the same Hibernate Session
public int getAccumulator(final String key,final Integer year)
{
    Accumulator accumulator = this.dao.get(key,year);
    if (accumulator==null)//is not found
    {                       
        final Accumulator newAccumulator = new Accumulator();//create the new Accumulator with key and year
        newAccumulator.setKey(c01);
        newAccumulator.setYear(year);
        newAccumulator.setValue(0);         
        this.save(newAccumulator);      
        return newAccumulator.getValue();//we return the new Value of the record which is 0 in this case
    } else
    {//this is working OK.
        accumulator.setC03(accumulator.getValue()+1);//if exist we add one to value...
        dao.incrementWithHQL(accumulator);//update in DB.
        return accumulator.getValue();//return new Value
    }       
}

This routine is called from semi-low concurrency; the problem arises in this case:

    Accumulator accumulator = this.dao.get(key,year);
    if (accumulator==null)//is not found
    {                       
        final Accumulator newAccumulator = new Accumulator();//create the new Accumulator with key and year
        newAccumulator.setKey(c01);
        newAccumulator.setYear(year);
        newAccumulator.setValue(0);         
        this.save(newAccumulator);//we schedule to save into the DB.        
        return newAccumulator.getValue();//we return the new Value of the record which is 0 in this case
    }

The problem is unique rows those which [KEY,YEAR] values are getting duplicates and later we are receiving NONUNIQUERESULTEXCEPTION

I think the problem is:

The UPGRADE lock works when exist a record on the BD freeze the row until the transaction are commit but is WIDE OPEN for selects.

How can I accomplish the way my ACCUMULATORS are unique for my [KEY,YEAR] entry key.

Upvotes: 1

Views: 510

Answers (2)

Karthik Prasad
Karthik Prasad

Reputation: 10034

You can create a composite key for year and key Something like below

@Embeddable
public class AccumaltorCompositeKey {

    @Column(name = "ACC_YEAR")
    private int year;

    @Column(name = "ACC_KEY")
    private String key;

    //getter, setter methods
}

 @Entity
 @Table(name = "Accumalator")
 public class Accumalator {

     @EmbeddedId
     private AccumalatorCompositeKey accumaltorCompositeKey;

    /*setter getter methods */
 }

Upvotes: 2

Pavan
Pavan

Reputation: 121

The best way to achieve this, is to make UniqueKey Constraint on DB on [Key, Year].

Upvotes: 1

Related Questions