Re: 1 Sequence per Row i.e. each customer's first order starts at 1

On Thu, Jul 2, 2009 at 10:35 AM, Scott Marlowe<scott(dot)marlowe(at)gmail(dot)com> wrote:
> Actually, since
> you're only incrementing from the highest one, you could just lock the
> id from a select max(orderid) where custid=xyz and you'd only have to
> lock one row.

Not really because you would have a race condition between selecting
the max() and then locking that record (you can't do FOR UPDATE
directly on the aggregate and in any case if you could it would have
the same problem).

Actually any scheme involving locking the orders would have the same
problems. Two transactions can start and try to lock some or all the
records. One will wait behind the other and only one transaction will
go ahead at a time but when the waiting transaction proceeds it still
won't see the newly inserted record and will get the same maximum. You
could get it to work as long as you're prepared to retry if you get
that race condition.

But then if you're prepared to retry you don't need locks at all. Just
"select max(id) from customer where customer_id = :0" and try to
insert -- if you get a unique violation start over and try again. As
long as you have an index on <customer_id,order_id> -- which would
presumably be your primary key anyways -- that should actually perform
just fine.

--
greg
http://mit.edu/~gsstark/resume.pdf