For example, a specific code case, procedure sys.sp_MSacquireSlotLock,

this is a procedure used in merge replication to limit the number of concurrent merge process. However, when the exclusive lock cannot be obtained, it is blocking due to waitfor because the resource obtained so far was never released. It actually creates blocking problem.

exec @retcode=sys.sp_getapplock @Resource=@slot_name,@LockMode=N'Exclusive',

@LockOwner='Session',@LockTimeout=0,@DbPrincipal=@DbPrincipal

I believe this is implemented because the developers were asked to come up a way to implement "the limit of maximum concurrent merge process". They fixed the leg but broke the toe.

The blocked users apps would not know what is going on and just complain it does not work.

sys.sp_getapplock in share lock mode makes no sense at all, if you want to share, why bother to lock.

Next time you intend to do "sys.sp_getapplock exclusive mode", think first what should happen to all the blocked waiting processes. If you can provide a good error handling, the damage is a lot less; otherwise you could have lots users complaining system is not working.

You could argue there is use for it. My counter-argument is why does rarely anybody put database into

SERIALIZABLE

isolation (because it is not practical). Maybe we change the word "stupid design" to "stupid implementation" (be more professional). The merge processes that exceeding the limit should not be started, not to be started and waited on concurrency blocking with no error handling.

In my view, allowing people to charge on credit card for the money they don't have is a stupid design. (good for bankers, not for commoners). Allowing sub-prime mortgage is a stupid design. (have people sign up the mortgage they have no potential to pay back). JPMorgan designed this risk-management scheme, they themselves pulled out later. That is a stupid design (for people who did not see it until later everybody did) There is no way to zero-out risk, they sweep into the carpet and convince someone else buy into it.