How can i release the Locks ON sql server tables

Transact-SQL
1

USE VTsourceData;
SELECT s.RefId,s.AddressRefId,s.POSTNAME,s.MNAME,s.FNAME,s.LNAME,s.LCLSTUNUM, s.GENDER,s.DOB,s.AmIndAKNat,s.Asian,s.Black,s.White,s.Hispanic,s.Hawaiian,s.Grade,s.MKSERVICE,s.DUAL_ENR,s.T1SERVICES,s.UNYOUTH,s.NTRESNO,s.HOMELESS,s.NSLELG,s.StudentId,s.SS504,s.EST,s.ETHNO,s.RACE_AMI,s.RACE_ASI,s.RACE_AFA,s.RACE_NAT,s.RACE_WHT,s.IEP,s.ELL,Disability,@BatchId,@PublicationsessionId,'Processed.' AS ErrorReason,s.ObjStudent_RecordId,s.CreatedBy,GETDATE(),s.CollectionId
FROM SIF.ObjStudent s WITH (UPDLOCK, ROWLOCK)
INNER JOIN SIF.ObjStudentSchoolAssociation ssa WITH (UPDLOCK, ROWLOCK) ON ssa.StudentRefId=s.RefId AND s.CollectionId=ssa.CollectionId AND s.CollectionId=@CollectionID

I have used this Select statement in Sp.
And Also using the Below update statements
update edFusionODS.SIF.SIFUploadSummary
set
SuccessCount=(
Select count(s.RefId) from SIF.ObjStudent_History s WITH (UPDLOCK, ROWLOCK)
--INNER JOIN SIF.ObjStudentSchoolAssociation ssa WITH (UPDLOCK, ROWLOCK) ON ssa.StudentRefId=s.RefId and s.CollectionId=ssa.CollectionId where s.BatchId=@BatchId)
where s.BatchId=@BatchId AND s.PublicationSessionId=@PublicationsessionId)
WHERE BatchId=@BatchId AND SIFObject ='Student'.

My Database Isolation levels;
VTsourceData;:isolation level:read committed
edFusionODS:isolation level:read committed

and
I have the lockes on SIF.ObjStudent,SIF.ObjStudentSchoolAssociation tables in sys.dm_tran_locks Tables.

Please find the below screen shot for Objstudent and Objstudentschoolassociation in

select l.request_mode,request_type,request_status,
object_name(p.object_id) as TableName,
resource_type, resource_description
from
sys.dm_tran_locks l
join sys.partitions p on l.resource_associated_entity_id = p.hobt_id

Due to this my Sp runnig from long time.

Can any one please help me.

2

Well you could do:

BEGIN TRANSACTION

SELECT ... WITH (UPDLOCK, ROWLOCK) ...

UPDATE ...

... Check for errors and ROLLBACK if there is any problem ...

... Otherwise COMMIT
3

Thanq Verymuch