It would be nice if we had "Read Uncommitted" transaction level for this,
hint ;-). In this scenario you will probably have to have your app check
for errors and retry if you get an abort.
I would do as follows:
create table widgets (
customer_id int references customers,
widget_id int
primary key (customer_id, widget_id)
);
Then I would have a trigger set to assign widget_id to (Select
max(widget_id) + 1 from widgets where customer_id = new.customer_id);
Then if by chance you have 2 duplicate submissions, you will get an error
you ned to handle in your app by retrying the insert.
Best Wishes,
Chris Travers