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:
and any request which calls this goes through a servlet filter which looks like this:
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.)
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.
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.
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.