SQL Server long running transaction

Question

I am wondering how resource expensive it is to perform a begin transaction on a connection and immediately update/insert a row into a database and leave 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 presses 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, it will read using NOLOCK hint so that they will see my pending inserted value so that they 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 which I allocated my series matches the current one. If not, then I would allocate another one. There is not a problem that a user loses 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 idea 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!

Stefan Steinegger · Accepted Answer · 2015-12-08 08:01:19Z

0

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.

answered Dec 8, 2015 at 8:01

Stefan Steinegger

64.8k17 gold badges132 silver badges193 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vinay Pandey · Accepted Answer · 2015-12-08 08:15:10Z

0

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.

answered Dec 8, 2015 at 8:15

Vinay Pandey

8,9639 gold badges38 silver badges57 bronze badges

Collectives™ on Stack Overflow

SQL Server long running transaction

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related