It's all about locking and blocking... The scenario you describe is probably fairly rare (ie two processes in perfect sync), and in most cases once the sequence is updated the db should lock and block thread B, but that may note be happening. For cases where two threads do manage to both get a query in to prevent the problem in the db thread A could do a select for update (causing a lock if the db cooperates) and then thread B would block until thread commits or rolls back.
-David On May 19, 2010, at 10:38 PM, Adrian Crum wrote: > I must be missing something - I don't see how transaction isolation will > solve this problem. For example: > > Thread A: Begin transaction > Thread B: Begin transaction > Thread A: Get last invoice number (10000) > Thread B: Get last invoice number (10000) > Thread A: Increment invoice number (10001) > Thread B: Increment invoice number (10001) > Thread A: Store new invoice number > Thread B: Store new invoice number > Thread A: Commit transaction > Thread B: Commit transaction > > That last step is going to fail no matter how you have your transaction > isolation set up. > > A single-server setup could solve the problem by putting the invoice number > generation code inside a synchronized method. In a multi-server setup, you > would have to have a test-and-retry method like you suggested. > > -Adrian > > --- On Wed, 5/19/10, David E Jones <[email protected]> wrote: > >> From: David E Jones <[email protected]> >> Subject: Re: QuoteId and OrderId sequence, possible race condition? >> To: [email protected] >> Date: Wednesday, May 19, 2010, 5:32 PM >> >> It would be nice if it were that easy, but not only could >> there be multiple threads in a single app server instance >> but there could be multiple app servers using this at the >> same time. >> >> This sounds like a transaction isolation problem, and >> really database transaction isolation is the only way to >> properly handle this. >> >> The place to look for this problem in a production system >> is what the database tx isolation is set to, and check if it >> is handling this right. >> >> Without handling it through the db the best option would be >> to write recovery code, ie call the save invoice in its own >> transaction and if it returns an error (and rolls back as >> part of that) then increment the ID (as James is doing >> manually) and try again. >> >> -David >> >> >> On May 19, 2010, at 4:42 PM, Adrian Crum wrote: >> >>> That looks like a bad design. There is no >> synchronization, so multiple threads can create invoices >> with the same number. >>> >>> I would recommend creating a Jira issue. >>> >>> -Adrian >>> >>> On 5/19/2010 3:30 PM, James McGill wrote: >>>> This happened again, this time with an >> InvoiceId. >>>> >>>> What could possibly cause an Invoice to be written >> with an InvoiceId >>>> (received via, >> InvoiceServices.xml#getNextInvoiceId) >>>> without getNextInvoiceId writing the incremented >> value to >>>> PartyAcctgPreference ? >>>> >>>> In my log I can see the last time an InvoiceId was >> created. >>>> getNextInvoiceId logs its context, and the method >> succeeds (We are using >>>> INVSQ_ENF_SEQ etc.). The invoice is created >> and everything seems fine. >>>> >>>> Then the next and subsequent invoices fail because >> the PartyAcctgPreference >>>> wasn't updated. I can't understand from the >> code how that is even possible. >>>> >>>> Could<store-value >> value-field="partyAcctgPreference"/> >>>> possibly not be writing, but also not throwing an >> error? >>>> >> >> > > >
