It sounds like your application does not have proper hanlding of query timeouts.

First you should ask yourself, if you want these suckers at all, and in such case what timeout value you want. In some applications they make sense. In some not at all. The default of 30 seconds is very bad default. (0 = forever is the only reasonable.)

When a timeout occurs, it is the client API that grews tired of waiting and sends SQL Server an attention signal, which instructs SQL Server to abort execution of the current batch. SQL Server will roll back the current statement. However, as along as XACT_ABORT is OFF (the default), SQL Server will not rollback an open transaction.

Assume that your SP_GetNextValue (by the way, the sp_ prefix is reserved for system procedures, and SQL Server first looks in the master/resource database for these) is called in a nested transaction and this transaction runs for some time. The process will then hold a lock on the row in SeqTable for a longer time, blocking other process that want to access the same key.

Eventually, they will time out, and if you take no further action, that statement started in SP_GetNextValue will still be a alive. Meaning that next time they call the procedure, they will acquire a lock - and hold it. And then all this mess starts.

It's instrumental what when you get a query timeout (and any other unexpected error for that matter) that you submit IF @@trancount > 0 ROLLBACK TRANSACTION.

I also recommend that you make sure that you have XACT_ABORT ON by default, although if you have CATCH handlers where you take alternate actions, beware that the transaction will always be doomed when XACT_ABORT is ON.