Java Hibernate MySql Table Locking -

Java Hibernate MySql Table Locking -

i have table in mysql this

table accumulator have this.

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

the behavior of table 1 value year , key this.

id year key value 1 2014 stock 13 2 2014 quote 10 3 2014 invoice 20

this values not allowed.

id year key value 1 2014 stock 13 2 2014 quote 10 3 2014 stock 20

we can't have 2 records same year , key

i have dao method 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)); homecoming (acumulator)query.uniqueresult(); }

which called manager method

all happens in 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 new accumulator key , year newaccumulator.setkey(c01); newaccumulator.setyear(year); newaccumulator.setvalue(0); this.save(newaccumulator); homecoming newaccumulator.getvalue();//we homecoming new value of record 0 in case } else {//this working ok. accumulator.setc03(accumulator.getvalue()+1);//if exist add together 1 value... dao.incrementwithhql(accumulator);//update in db. homecoming accumulator.getvalue();//return new value } }

this routine phone call semi-low concurrency problem arise in case..

accumulator accumulator = this.dao.get(key,year); if (accumulator==null)//is not found { final accumulator newaccumulator = new accumulator();//create new accumulator key , year newaccumulator.setkey(c01); newaccumulator.setyear(year); newaccumulator.setvalue(0); this.save(newaccumulator);//we schedule save db. homecoming newaccumulator.getvalue();//we homecoming new value of record 0 in case }

the problem unique rows [key,year] values getting duplicates , later receiving nonuniqueresultexception

i think problem is.

* check if exist on db not found , schedule save on commit. * thread in different **hibernate session** reads , not found `accumulator` same key,year , schedule save db. * both records beingness save. * later thread seek read if exist accumulator [key,year] , got 2. , `nonuniqueresultexception` exception throw.

the upgrade lock works when exist record on bd freeze row until transaction commit wide open selects...

how can accomplish way accumulators unique [key,year] entry key.

any help hugely appreciate

best regards venezuela..

the best way accomplish this, create uniquekey constraint on db on [key, year].

java mysql hibernate locking