Geek asks and answers

Sql Server Transactions - Usage Recommendations

I have seen this offer not only in one place:

"The transaction should be as short as possible to avoid concurrency issues and include the maximum number of positive commits."

What does it mean?

Now it puzzles me, because I want to use transactions for my application, which in normal mode will deal with inserting hundreds of rows from many clients at the same time.

For example, I have a service that provides a method: AddObjects(List<Objects>)and, of course, this object contains other nested different objects.

I was thinking of starting a transaction for each call from the client performing the corresponding actions (insert / update / delete link for each object with their nested objects). EDIT1: I was referring to a transaction to fully invoke " AddObjects" to prevent undefined states / behavior.

Am I going in the wrong direction? If so, how do you do it and what are your recommendations?

EDIT2: In addition, I realized that transactions are fast for mass companies, but it somehow contradicts the quoted offer. What is the conclusion?

Thanks in advance!

+3
source share
3 answers

- . "", : " X Y ", " ' .. .. , , , . , , , . , //, , , , . " X, Y ". " " - .

+3

. , , .

, , .
, - "concurrency", , , .

+3

" , concurrency ".

The longer a transaction remains open, the more likely it is to block the resources needed for other transactions. This lock will cause other concurrent transactions to wait for resources (or failure depending on the design).

Sql server is usually configured in auto-commit mode. This means that each sql statement is a separate transaction. Many times you want to use a transaction with multiple statements so that you can commit or roll back multiple updates. The longer the updates take place, the more likely other transactions are.

+1
source

More articles:


All Articles