These settings are same as SSMS settings. However I have read in an article and found that this situation is due to SQL Server Shared locks on table and resulted in transactional deadlocks. The query was hold because of deadlock and show result when deadlock was removed.
So I want that SQL Server should not use deadlock mechanism. I have read that from SQL Server 2005 , they give an SnapShot option Click here to read the article
from this I conclude that we have to apply "ALLOW_SNAPSHOT_ISOLATION " and "READ_COMMITTED_SNAPSHOT" options on our Database. and use "SET TRANSACTION ISOLATION LEVEL READ COMMITTED" in stored procedure rather than "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED".
Please give your recommendations on applying this strategy.