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?