Hello DB/Hibernate experts

I'm not sure how to do this in Hibernate, or relational databases in general.

What I need is one of my persistent classes to have only a single instance in the database - a persistent singleton, in other words. I think I need to use pessimistic locking somehow...

I have the following mapping document:



I'm trying to use the DB to store the next number in a batch. So I want a table with a single record, the ID can just be 1. I want to check if it exists, and if not, create it. But obviously I don't want some concurrent process to create an instance if it also finds that it doesn't exist.

I'm thinking along the lines of:



but, I suspect this won't work. How should I do this?

Any help much appreciated...

Craig

Hi.
i have a suggestion, hope it fits:

This is a method that checks if an instance is already in the DB, else, it returns the same instance you passed it.


so you should use it like that:



it ensures that there is only one row with id = 1.

Hope it helps somehow,
Jus.

Hi Jus

Thanks for your reply, but won't that just leave a timing hole in that two concurrent processes could each discover that there was no existing row and thus both attempt to create the row. Then one will fail with an exception.

Is this really the only way to do it, or is there some more elegant way?

Craig

If you declare that ID column as the PK for the table, you can then simply insert a new row and flush() the session. If you get an exception, there's a duplicate row. In that case, select and update it.

Actually, I've experimented with this a bit, and learned a lot.

It seems that I really don't have a problem. From my experiments, in MySQL, at least, if I do a SELECT FOR UPDATE (in Hibernate, a load, with a LockMode.Upgrade) and don't find it, I can INSERT it without worry as MySQL will hold a lock on the index that prevents any other process from doing the insert.

As an aside, however, if there is a concurrent process that also does a SELECT FOR UPDATE then attempting to INSERT the row will fail with a deadlock exception - which could be retried, hopefully successfully second-time around.

So, I guess the general rule is that if I want to do an insert and not have to worry about duplicate key exceptions then I could do a SELECT first and be guaranteed of being able to INSERT if no row was found.