I am trying to improve what appears to be a deceptively simple SP. The issue is the SP causes deadlock errors. The sp is called, when a user logs in, to clean-up ungraceful exits from the app. The 3 tables are independent of each other. There will be at most 1 record in each table to update. There is no index on Locked_By and the DBAs are reluctant to add that index (the system is insert heavy). Does SQL Server lock each record during the table scan while it determines what records to update?

Thanks for any advice.

BEGIN
    SET NOCOUNT ON

    DECLARE @UserID AS CHAR(7)                               
    SET @UserID = CAST(@UID AS CHAR(7))   

	UPDATE
		PATIENT_CLAIM_837_2000C
	SET
		LOCKED_BY = NULL,
		LOCKED = 'N'
	WHERE
		Locked_By = @UserID

	
	if @@error > 0
	raiserror('Unable to Update Records',0,1)

	UPDATE
		Benefit_Enrollment_Maint_834
	SET
		LOCKED_BY = NULL,
		LOCKED = 'N'
	WHERE
		Locked_By = @UserID


	if @@error > 0
	raiserror('Unable to Update Records',0,1)
	
		UPDATE
		Health_Care_Services_Review_278
	SET
		LOCKED_BY = NULL,
		LOCKED = 'N'
	WHERE
		Locked_By = @UserID


	if @@error > 0
	raiserror('Unable to Update Records',0,1)
	
END