SQL Server long running transaction

Question

I am wondering how resource expensive is to perform a begin transaction on a connection and imediatelly update/insert a row into a database and letting this transaction hanging for several hours. Basically I just want to perform a "series number" reservation for my document management system. My series are something that are very custom and I want that whenever a user press the "Add new document" button, the next value will be allocated into my series allocation table. To allocate it I would insert a row into the allocation table. Next time, when a new user asks for the next value, will read using NOLOCK hint so that he will see my pending inserted value so that he will know the next value also. If the user cancels the form that adds a new document, I would simply perform a rollback over my opened connection. If the connection is lost and I am in "add" mode, then I would check if current transaction id on wich I allocated my series matches the current one. If not, then I would allocate another one. There is not a problem that a user lose a series due to connection lost. What do you think? I feel like it's a very bad practice because it is in contradiction with the ideea that I learned in my several years of software development: Open connection as late as possible and close it as soon as possible.

Thank you in advance!

Answer 1

I would considering using sequences. If they do not fit, I would do something like the following:

• Have separate transactions to manage your "series numbers".
• These transactions are very short and only do e.g. "get next number".
• Have a "state" column to know whether something is in progress.
• Lock the whole table to manage its contents.

Avoid NOLOCK. Avoid long running transactions.

Answer 2

Try to keep your transactions small as possible, get sequence number in a different transaction and then you can start your actual process this way there would be less connections waiting for your transactions in process.

You can also consider using Read Uncommitted or other isolation level for certain cases, like last week last month or yearly sale where data needed is already present or minor mimor error is acceptable.

Consider having proper indexes and properly sequenced joins in order to lower execution time.