Hibernate + concurrent inserts (MySQL INSERT IGNORE)

Question

I am learning to use Hibernate. I am wondering how to correctly execute parallel inserts in sleep mode.

I have a URL table defined as:

CREATE TABLE `urls` ( id INT PRIMARY KEY AUTO_INCREMENT, md5 CHAR(32) UNIQUE, url TEXT ); 

The purpose of the table is to support the display identifier ↔ url. The md5 field is the sum of md5 from the url, since usually the url can be longer than 1024 bytes, which is the limit for UNIQUE restrictions in mysql.

My question is about a request for handling urls with concurrency id. In the JDBC implementation, I take the following steps:

• SELECT id WHERE md5 = md5 (url);
• if exists, return id, else:
• INSERT IGNORE INTO urls (md5, url) VALUES (MD5 (url), url);
• repeat step 1.

It works well even if the requested url is inserted between steps 2 and 3. How do I do this with Hibernate?

Answer 1

You can play around with the @SQLInsert annotation. It allows you to specify which SQL statement to use during insertions. The problem is that Hibernate does a lot more than just inserting / updating / selecting data from a database. However, I’m quite sure that simply using @SQLInsert can work first, but I'm not sure how Hibernate will behave when you click on the script you described (parallel insertion between steps 2 and 3), especially because this does not complete step 4. Instead, it calls JDBC "getGeneratedKeys" to retrieve which identifier was generated (which, I suspect, will be null if the insert was ignored).

In short: the only solution I see is using @SQLInsert, but you'll want to play around with it and make sure that Hibernate behaves correctly when parallel insertion occurs.