On Thu, Jul 2, 2009 at 10:35 AM, Scott Marlowe<scott.marlowe@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
