How to lock a transaction for reading a row and then inserting in Hibernate?

Question

I have a table with a name and a name_count. So when I insert a new record, I first check what the maximum name_count is for that name. I then insert the record with that maximum + 1. Works great... except with mysql 5.1 and hibernate 3.5, by default the reads don't respect transaction boundaries. 2 of these inserts for the same name could happen at the same time and end up with the same name_count, which completely screws my application!

Unfortunately, there are some specific situations where the above is actually fairly common. So what do I do? I assume I can do a pessimistic lock where any row I read is locked for further reading until I commit or roll-back my transaction. Or I can do an optimistic lock with a version column that automatically keeps trying until there are no conflicts?

What's the best approach for my situation and how do I specify it in Hibernate 3.5 and mysql 5.1? The above table is massive and accessed frequently.

Answer 1

This is why most people use a SEQUENCE to create unique numbers. That said, what you must do is lock the whole table (LOCK TABLES). The problem: You must lock all the tables you need (i.e. anything Hibernate may touch), or you will get errors. Next, you must unlock the tables and do both of these operations in sync with the rest of the transaction.

I hope this gives you an idea why people use sequences: They have some small drawbacks like gaps but everything else is much worse.

[EDIT] You can define a sequence and then use a native SQL query to get the next value. You can try to define a sequence generator (see the docs) but maybe the mapping is only allowed on Id fields.

Re "200 Million names": As long as the database can store the number, you can also define a sequence over it.

Re "row based locking": Which row do you plan to lock? The one with the max value? I'm not sure that the max() operator will stop if you lock it. What you could try is a trigger. Since triggers are atomic, no one can insert a row while it runs. But trigger are a bit hard to maintain.

Answer 2

MySQL don't support sequences, so they should be simulated. This is an interesting recipe for it: http://forums.mysql.com/read.php?61,143867,194058#msg-194058

Note that table type is MyISAM to not respect transaction concurrency and return next counter value on each request.

Answer 3

If you want to make it database agnostic (avoiding sequences) and not have to run a max() function before you insert try using a guid instead:

/**
 * The unique id. This id is generated this object is persited. The id is a 32 character UUID.
 * This gives each entity a completely unqique identifier. This is completely unique across all
 * databases, jvms and entities.
 */
@Id
@GeneratedValue(generator = "system-uuid")
@GenericGenerator(name = "system-uuid", strategy = "uuid")
@Column(length = 32, name = EntityObject.Columns.ID)
@DocumentId
private String id;

This also has the benefit that your primary keys are unique across every table in your database which lends well for distributed databases.

We've used this with many databases and we can move between them easily.