Hibernate: how to get unique order numbers?

[Paul Clapham]

Here's my requirement: my web application needs to generate unique confirmation numbers for its order entry application. The numbers have to be between 1 and 99999 and generally should be sequential, although if there are gaps in the sequence that doesn't really matter. Eventually the number sequence will wrap around, which is okay, but giving out the same number to two requests in the same second is a no-no. And that's what is happening (at least, it happened once).

The orders generated by this application aren't stored in the application's database, they are shipped off to a back-end application to be stored. So what I have so far is a database table with a single row. One column is a key with a known value and the other is the last assigned confirmation number. My code for updating that table and extracting a confirmationnumber from it is:

	synchronized public static int getNextConfNumber() {

		boolean isNew = false;

		// Get the record from the database if it exists
		ConfNumber conf = (ConfNumber) HibernateUtil.currentSession()
			.createQuery("from ConfNumber conf where conf.key = ?")
			.setString(0, UNIQUE_KEY)
			.uniqueResult();
		// Create it if it doesn't
		if (conf == null) {
			conf = new ConfNumber();
			conf.setKey(UNIQUE_KEY);
			conf.setConfNumber(0);
			isNew = true;
		}

		// Get the last confirmation number used
		int result = conf.getConfNumber();
		// Calculate the next available one
		if (result == 99999) {
			result = 1;
		} else {
			result += 1;
		}
		conf.setConfNumber(result);

		// Update the database
		if (isNew) {
			HibernateUtil.currentSession().save(conf);
		} else {
			HibernateUtil.currentSession().update(conf);
		}

		return result;
	}

and any request which calls this goes through a servlet filter which looks like this:

			Session session = HibernateUtil.currentSession();
			Transaction tx = null;
			try {
				tx = session.beginTransaction();
				chain.doFilter(req, resp);
				tx.commit();
			} catch (Throwable t) {
				if (tx != null) {
					tx.rollback();
				}
				throw new ServletException(t);
			} finally {
				HibernateUtil.closeSession();
			}

I was expecting that this would produce a transaction which (naively speaking) prevented two requests from getting the same confirmation number, but it doesn't. Does anybody know how I can fix this?

(It looks to me like perhaps when two requests get the same number, one of their transactions might fail and be rolled back. But the application doesn't know anything about that, and as far as I can see it can't know anything about it, so it just uses the data even though it's going to be a failed transaction.)

[David Newton]

If it's not being put into the DB would an application-level class that does the generation (possibly storing the "last generated" in a persistent store somewhere) be enough?

[Paul Clapham]

I considered changing the code to write a record into a (new) file and use the generated primary key as the basis for the number returned, but given the failure of my transactional code, I'm not convinced that would work reliably either.

[David Newton]

No no, I'm just saying have a synchronized sequence generator completely disconnected from the DB (except maybe to get something from startup).

But the filter code should be making a transaction--I'd first check to see if there's an active transaction, and if it's the same one as in the filter. There are a few other things that can affect how sessions/transactions work--also make sure you're getting the same session.

[Paul Clapham]

I never liked the idea of having the transaction managed by a servlet filter (I got that from some Hibernate document, I'm sure). And now that I have this problem I recall that it gave me problems before, just not serious problems. My instinct would be to make the scope of the transaction as short as possible.

So I'm thinking of getting rid of that filter and just replicating its transaction-managing code wherever I do a database update.

[David Newton]

IMO transactions shouldn't be handled by a filter--but if you're in an environment that allows nested transactions it's not a big deal, if you want/need transactions for read access.

[Paul Clapham]

David Newton wrote:IMO transactions shouldn't be handled by a filter...

I'm going with this quote. That filter just isn't doing anything for me except making things inconvenient when I really do need a transaction.

[David Newton]

Bad things invariably happen when people listen to me... abandon hope all ye etc.