This usually happens when one transaction is started and either it is not committed or it is not rolledback.
If the error comes in your stored procedure then it will lock the database tables as the transaction is not completed due to some runtime errors occuring in the absence of exception handling. So to avoid getting such errors you can use the Exception handling as shown below.
SET XACT_ABORT ON
SET NoCount ON
//Insert ,update queries