You might be suffering from parallelism. If you run this

sp_who2

And see several rows of the same SPID then there might be a parallel query issue

Then try this:

MERGE dbo.cart AS c_target

USING (SELECT

@sessionid as sessionid

,ISNULL(o.free_subsku,0) as subsku

,o.free_sku as mainsku

,ko.cartrsn

,o.qty_free * ko.qty as qty

,COALESCE(sp.price,p.price) as price ,COALESCE(sp.[weight],p.[weight]) as [weight]

FROM offer o

inner join @cart_offer ko on o.id = ko.offer_id

inner join products p on o.free_sku = p.sku

left join subproducts sp on o.free_subsku = sp.sku

WHERE o.active = 1 and o.offer_type = 'GWP'

)

as c_source

ON c_target.sessionid = c_source.sessionid

and c_target.mainsku = c_source.mainsku

and c_target.subsku = c_source.subsku

--Issue here?!?!

WHEN NOT MATCHED BY SOURCE AND c_target.sessionid = @sessionid

THEN DELETE

WHEN MATCHED AND c_target.qty <> c_source.qty THEN

UPDATE

SET c_target.qty = c_source.qty

,c_target.price = c_source.price

,c_target.[weight] = c_source.[weight]

WHEN NOT MATCHED BY TARGET THEN

INSERT (sessionid,subsku,mainsku,qty,price,[weight])

VALUES (@sessionid,c_source.subsku,c_source.mainsku,c_source.qty,c_source.price,c_source.[weight])

OPTION (MAXDOP 1) ;

And see if that helps.

Would still like to understand what you mean by "hanging" though.