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

Viewed 1405

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?

1 Answers
Related