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

On Thu, Jul 2, 2009 at 3:28 AM, Greg Stark<gsstark(at)mit(dot)edu> wrote:
>> On Wed, Jul 1, 2009 at 6:01 PM, Scott Marlowe<scott(dot)marlowe(at)gmail(dot)com> wrote:
>>> The simplest method is to do something like:
>>>
>>> begin;
>>> select * from sometable where cust_id=99 order by order_id desc for update;
>>>
>>> to lock all the customer records for cust_id 99, then take the first
>>> record, which should have the highest order_id, grab that increment it
>>> and then insert the new record  and commit; the transaction.  Assuming
>>> your customers aren't ordering dozens of things a second, this should
>>> work with minimal locking contention.
>
> I don't like the idea of locking all the order records. That sounds
> like it would lead to even more contention than locking just the
> customer record. There could be thousands of order records to lock all
> over the order table.

True. Then again, if you're only locking it long enough to get the
next sequence, creating an empty record with that sequence, then
committing the transaction, it's a short lived lock. 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.

> It seems to me that locking all the order records here is really just
> a proxy for locking their parent customer record and that would work
> better anyways.
>
> You could avoid the update to the customer record by combining these
> two strategies though. Instead of updating a last_order_num field in
> customer do something like this:
>
> begin;
> select * from customer where customer_id=:0 for update;
> select max(order_num) from orders where customer_id = :0
> insert into orders (order_num,...) values (:0 + 1, ...)
> commit;
>
> This uses the lock on customer to protect your insert against someone
> else inserting the same order_num but doesn't actually update the
> customer table.

Good point.