I have an exceptional circumstance that made me use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. (I am using sp_getapplock to ensure single process access to a given row.)
I am trying my best to avoid any kind of lock escalation. I know clearly what ISOLATION LEVEL READ UNCOMMITTED does for reads, but I am not sure about deletes.
I saw some example code that was doing a delete using ROWLOCK and READPAST, along with ISOLATION LEVEL READ UNCOMMITTED but I was not clear on what that would do, so I have not been using it for now.
Is that needed to prevent lock escalation of my deletes or is ISOLATION LEVEL READ UNCOMMITTED sufficient?
Note:
In case there is interest, here and here are the relevant sprocs for this question.
Best Answer
It does nothing. You can't modify the database without proper locking. That would produce not just the failures and nonsense results that dirty reads produces, but would corrupt the database.