What are the advantages and disadvantages of using transaction explicitly

Asked

Viewed 2,661 times

7

Beyond security what is the advantage of using explicit transaction? In which situations is recommended?

sql

2 answers

7


Implicit transactions

In this mode, the server will ensure that all operations are performed within a transaction.

In general they are used so that the developer does not have to worry about the logic of transactional handling and has to repeat it throughout the code.

However, the trend is the degradation of performance, because transactions are more "expensive" from the computational point of view since the database needs to perform several additional checks and there is a greater probability of occurring Locks and deadlocks where the developer can predict.

Finally, the advantage is in relation to the ease of development at the cost of performance and possible Locks.

Explicit Transactions

Leaves it to the developer to start and end transactions via commands:

  • BEGIN TRANS
  • COMMIT TRANS
  • ROLLBACK TRANS

The downside is that the developer needs to repeat the control logic at all points where transactions are needed.

On the other hand, this allows a fine-tuning of performance, minimizing use of transactions where it is not needed, Locks and deadlocks, and allows the developer to analyze cases of possible competition problems.

Recommendations

My recommendation is to always use explicit transactions manually delimiting where they will be needed.

When code alone performs atomic operations, transactions are not necessary. For example, when only one command is made INSERT or UPDATE or SELECT.

However, when there is a sequence of operations, then one must initiate a transaction always encompassing as little code as possible in order to increase the efficiency of the database system to process competing requests.

5

Perks:

  • Security in operations involving multiple changes to different data elements, preventing data from being entered incomplete due to systemic failures;
  • In case of physical equipment failure, it makes it possible to restore the database securely until the last checkpoint valid transactional;
  • Allows correct propagation of data, in case of multi-user systems, organizing concurrent changes.

Disadvantages:

  • Slower than a simple persistence operation;
  • Can cause deadlocks;
  • Exclusive transactions may make the database unavailable to other users, who are not within the scope of the transaction, hence making the system slow if transactions within a transaction are also.

Recommended to use:

  • In transactions involving persistence of multiple entities;
  • In systems that occur changes in the information in real time, and these changes are propagated to several users at the same time.

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.