How to avoid SQL query timeout error when open transaction is there?

Question

I have used below query to retrieve customer details from database.

Method 1:

BEGIN TRAN
IF EXISTS(SELECT TOP 1 'X' From CUSTOMER Where CustId = @Code AND Status = 'D')
BEGIN
    UPDATE CUSTOMER 
    SET Status = 'L' 
    WHERE CustId = @BorrowerCode AND ISNULL(Borrower,'') = 'Y'

    SELECT CustId, MobileNo, PlaceDesc
    FROM CUSTOMER C 
    JOIN PLACE P ON C.FKID = P.Pk_Id
    WHERE Cust_Id = @Code AND C.Status = 'L'
END
COMMIT TRAN

Method 2:

BEGIN TRAN
IF EXISTS(SELECT TOP 1 'X' From CUSTOMER Where CustId = @Code AND Status = 'D')
BEGIN
    UPDATE CUSTOMER 
    SET Status = 'L' 
    WHERE CustId = @BorrowerCode AND ISNULL(Borrower,'') = 'Y'

    SELECT CustId, MobileNo, PlaceDesc
    FROM CUSTOMER C With(NoLock) 
    JOIN PLACE P With(NoLock) ON C.FKID = P.Pk_Id
    WHERE Cust_Id = @Code AND C.Status = 'L'
 END
 COMMIT TRAN

If there is an open transaction in database, the query will fail with a timeout for method 1. Is it good practice to use NoLock inside transaction?

Answer 1

Few things..

1.First of all your update is not sargable,you can try to rewrite it as below

UPDATE CUSTOMER 
SET Status = 'L' 
WHERE CustId = @BorrowerCode AND Borrower = 'Y' 

2.Time out has pretty huge limit,25 times query cost.so even with this limit,if you are getting time out ,then there must be some thing wrong and we are trying to apply bandage with nolock.setting isolation level to snapshot ,will not result in blockings of select ,but it comes with a cost of tempDB usage,scan issues(see link below for more..).Further isolation level wont apply to DDL/DML statements ,they are just for select statements and in your case ,an open transaction may mean some DDL/DML running for so long.

In summary,i wont use nolock,but i would rather try to see why timeout happens and also changing isolation level requires some testing as well

References: http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/