Hibernate: Disadvantages of not having id generator?

Question

Hi i have a table in which i am not using Hibernate ID generator but instead using a method to generate a random number(this will never be same as per logic). Is this a bad approach?

I have a method which first loads this table entity using select query and then updates some columns in the entity object returned and saves it. This is happening in the same session the load and update. In this case sometimes i get an exception:-

2014-05-20 11:31:16,341 | ERROR |   [http-10181-3] |org.hibernate.util.JDBCExceptionReporter:logExceptions(101) | Lock wait timeout exceeded; try restarting transaction 
2014-05-20 11:31:16,344 | ERROR |   [http-10181-3] |org.hibernate.event.def.AbstractFlushingEventListener:performExecutions(324) | Could not synchronize database state with session 
org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
    at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:266)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:168)
    at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
    at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:50)
    at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1028)

This comes when this method is called a lot of times. i.e my application is being used by multiple users. Any pointers on this? Is the approach being used my me for manually assiging ID incorrect? Or this has nothing to do with it?

Similarly in some cases i am also querying the table to get the next ID: like select max(id) and then set the id for the next row? Is this also a bad approach?

PS: The id column is a primary key and the DB being used is MySQL

The disadvantage I can see: how you ensure that generated id is unique? The primary key should be unique. — alexey28, May 22 '14 at 06:20
Its quite complex but has nothing to do with DB. But its unique — Harinder, May 22 '14 at 06:21
Did you use InnoDB tables in MySQL? This tables use row level locking for updates. Other tables use table/page level. — alexey28, May 22 '14 at 06:23
Why can't you make the ID auto_increment instead of asking select max(id) ? — StanislavL, May 22 '14 at 06:35
That i can change. Not sure why the old coder did that in such manner. But i can change this. — Harinder, May 22 '14 at 06:51
Any pointers on the error m getting? Does this have any relation to it? — Harinder, May 22 '14 at 06:51
I don't think it is related to id generation. Think one session locking your records while other session waiting and falls with timeout. What I can suggest: 1. Do update with select condition in single query; 2. If #1 is not possible select ids, close session and then do update by selected ids — alexey28, May 22 '14 at 14:01

Vlad Mihalcea · Accepted Answer · 2018-01-05T17:31:55.820

Due do the MySQL popularity, there's no wonder Lock wait timeout exceeded; try restarting transaction exception gets so much attention on SO.

MySQL as opposed to other popular DBS (Oracle, MSSQL, PostgreSQL, DB2) uses REPEATABLE_READ as the default isolation level.

If you want a good explanation of the difference between these two isolation levels, please read this first.

In REPEATABLE READ every lock acquired during a transaction is held for the duration of the transaction.

So the more contention you have the more dead locking will occur, which a db engine will resolve by time-outing one of the dead locked transactions. This more restrictive the isolation level (REPEATABLE_READ, SERIALIZABLE) the greater the chance of dead-lock. This is not an issue "per se", it's a trade-off.

As for your custom ID generation, I am quite intrigued by your endeavour. I see no reason in having a custom solution that very much behaves like a sequence.

If you use the default, REPEATABLE_READ and two transactions try to insert a row in this table of yours, the the select(max id) will yield the same result (e.g. 3567). The first transaction will try to increment it to 3568 and the second one will do the same. An AUTO_INCREMENT (sequence in Oracle) is guaranteed to work atomically no matter the isolation level you chose.

A custom ID generation makes sense for custom assigned ID, like some external unique ids or HI-LO generated keys or external UUIDs. If I were you, I'd drop it in favour of a AUTO_INCREMENT.

"*in favor of a sequence*" - since when does MySQL have sequences? — , May 22 '14 at 09:21
Good point, AUTO_INCREMENT instead. I haven't been using MySQL for the past 4 years, so I kinda forgot some of its traits. — Vlad Mihalcea, May 22 '14 at 09:30

Hibernate: Disadvantages of not having id generator?

1 Answers1