For that you'd need to manually reimplement SQL's locking mechanism, probably with a locking table, manual checks, manual notifications in all code that you have. You then need to consider all the complications of rows getting locked but never unlocked, etc. It's a hell of a lot of work and very easy to get wrong.

iirc there's a chapter on implementing something like this in Expert SQL Server 2005 Development (yes, the 2005 version)