...

declare @GOODS_ID int

select	@GOODS_ID = MAX(GOODS_ID)
from	GOODS
where	SHOP_ID = @SHOP_ID

set @GOODS_ID = ISNULL( @GOODS_ID, 1 )

insert GOODS
values ( @SHOP_ID, @GOODS_ID ... )

...

...

declare @GOODS_ID int

select	@GOODS_ID = MAX(GOODS_ID)
from	GOODS (nolock)
where	SHOP_ID = @SHOP_ID

set @GOODS_ID = ISNULL( @GOODS_ID, 1 )

INSERT GOODS
VALUES ( ... )

-- ---------------------
-- НОВОЕ УСЛОВИЕ:
-- ---------------------
WHERE NOT EXISTS ( select * from GOODS (nolock) where SHOP_ID = @SHOP_ID and GOODS_ID = @GOODS_ID )

if @@ROWCOUNT = 1
  RETURN 0
else
  RETURN 100     -- дублирование PK

...

declare @rc int = 100

while @rc = 100
begin
  exec @rc = GOODS_INSERT ( ... )
end

Set transaction isolation level serializable
Begin tran

declare @GOODS_ID int

select	@GOODS_ID = MAX(GOODS_ID)
from	GOODS with (updlock)
where	SHOP_ID = @SHOP_ID

set @GOODS_ID = ISNULL( @GOODS_ID, 1 )

insert GOODS
values ( @SHOP_ID, @GOODS_ID ... )
Commit tran
...