That's an interesting perspective. It's been my understanding that you can read/write all you want inside a transaction, but no changes are actually made to the database until you commit the transaction. It's during the commit that any locking/blocking will occur. But I might be wrong.
-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, 9:53 PM > > 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? > >>>> > >> > >> > > > > > > > >
