3

I saw this sentence not only in one place:

"A transaction should be kept as short as possible to avoid concurrency issues and to enable maximum number of positive commits."

What does this really mean?

It puzzles me now because I want to use transactions for my app which in normal use will deal with inserting of hundreds of rows from many clients, concurrently.

For example, I have a service which exposes a method: AddObjects(List<Objects>) and of course these object contain other nested different objects.

I was thinking to start a transaction for each call from the client performing the appropriate actions (bunch of insert/update/delete for each object with their nested objects). EDIT1: I meant a transaction for entire "AddObjects" call in order to prevent undefined states/behaviour.

Am I going in the wrong direction? If yes, how would you do that and what are your recommendations?

EDIT2: Also, I understood that transactions are fast for bulk oeprations, but it contradicts somehow with the quoted sentence. What is the conclusion?

Thanks in advance!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Learner
  • 3,297
  • 4
  • 37
  • 62

3 Answers3

3

That recommendation is best understood as Do not allow user interaction in a transaction. If you need to ask the user during a transaction, roll back, ask and run again.

Other than that, do use transaction whenever you need to ensure atomicity.
It is not a transactions' problem that they may cause "concurrency issues", it is the fact that the database might need some more thought, a better set of indices or a more standardized data access order.

GSerg
  • 76,472
  • 17
  • 159
  • 346
  • Thanks a lot for your answer. How would you comment my approach of implementing that "AddObjects" method? Would it be ok as I described? Would you do it another way? – Learner Jun 08 '11 at 16:38
3

A transaction has to cover a business specific unit of work. It has nothing to do with generic 'objects', it must always be expressed in domain specific terms: 'debit of account X and credit of account Y must be in a transaction', 'subtract of inventory item and sale must be in a transaction' etc etc. Everything that must either succeed together or fail together must be in a transaction. If you are down an abstract path of 'adding objects to a list is a transaction' then yes, you are on a wrong path. The fact that all inserts/updates/deletes triggered by a an object save are in a transaction is not a purpose, but a side effect. The correct semantics should be 'update of object X and update of object Y must be in a transaction'. Even a degenerate case of a single 'object' being updated should still be regarded in terms of domain specific terms.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Ok, so let's say X table is 1:n to Y table. X obj contains list obj. So at least inserting of X obj (with Y nested obj) needs to be transacted. But is also important to insert additionally into Z & T tables some records depending of the last insert of X & Y. If I don't have a transaction, I might have X & Y without Z & T which is problematic. Also, I was thinking for all the objects to have only one transaction in order to rollback if at least one operation would fail. Does it make sense? – Learner Jun 08 '11 at 16:52
  • Also, I understood that transactions are fast for bulk oeprations, but it contradicts somehow with the quoted sentence. – Learner Jun 08 '11 at 16:57
  • Transactions can be embedded. The functions that saves X and Y can wrap them in a transaction.The function that saves Z can use a transaction, same for the save of T. And the function that orchestrates these 3 operations can also wrap *all* 3 operations (the save of X:y, the save of Z, the save of T) in a transaction, thus effectively creating one single transactions that wraps all. – Remus Rusanu Jun 08 '11 at 16:58
  • 1
    There are many practical ways to achieve this, like using a TransactionScope in the function(s) (be aware of http://blogs.msdn.com/b/dbrowne/archive/2010/06/03/using-new-transactionscope-considered-harmful.aspx), and/or using a transaction safe procedure template like http://rusanu.com/2009/06/11/exception-handling-and-nested-transactions/ – Remus Rusanu Jun 08 '11 at 16:59
  • Thanks a lot. So, it's not so bad what I was thinking afterall? I can use them as you stated... What about the bulk? – Learner Jun 08 '11 at 17:00
  • 1
    As for bulk operations and transactions: besides their correctness role, on certain scenarios (like ETL, data upload etc) transactions are used not for reasons of correctness but for reasons of performance: if each individual statement has to wait for its own the transaction to commit (and the log to flush) then performance quickly degrades. See http://stackoverflow.com/questions/4598094/b-trees-databases-sequential-vs-random-inserts-and-speed-random-is-winning/4598310#4598310. – Remus Rusanu Jun 08 '11 at 17:02
  • 1
    the quoted sentence is still true, but from a different point of view: concurrency. By keeping transactions short there is a lower probability of conflict and blocking. In bulk scenarios usually there is no concurrency because they occur at 'maintenance' times and no other activity is allowed at the same time. – Remus Rusanu Jun 08 '11 at 17:05
  • 1
    To sum up: on normal operations keep transaction short to prevent concurrency blocking. On bulk operations use a lengthier transaction to prevent blocking on waiting for commit log flush. – Remus Rusanu Jun 08 '11 at 17:07
  • I think I got it now. -Multumesc mult- ;) ... Cheers! – Learner Jun 08 '11 at 17:18
  • @Remus- Could you please check this as well: http://stackoverflow.com/questions/6290891/net-tricky-sql-transaction-sql-server-but-oracle-in-mind-too-for-later ? – Learner Jun 09 '11 at 10:06
1

"A transaction should be kept as short as possible to avoid concurrency issues and to enable maximum number of positive commits."

The longer a transaction is kept open the more likely it will lock resources that are needed by other transactions. This blocking will cause other concurrent transactions to wait for the resources (or fail depending on the design).

Sql Server is usually setup in Auto Commit mode. This means that every sql statement is a distinct transaction. Many times you want to use a multi-statement transaction so you can commit or rollback multiple updates. The longer the updates take, the more likely other transactions will conflict.

Scott Bruns
  • 1,971
  • 12
  • 12