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 transactional handling logic and has to repeat it throughout the code.

However, the trend is performance degradation, since 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 locks and deadlocks that the developer can anticipate.

Finally, the advantage is in ease of development at the cost of performance and of possible locks .

Explicit Transactions

It is up to the developer to start and stop 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 the use of transactions where it is not necessary, locks and deadlocks , as well as allowing the developer to analyze cases of possible competition problems.

Recommendations

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

When the code itself performs atomic operations, transactions are not required. For example, when you do only one command INSERT or UPDATE or SELECT .

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

Advantages:

• Security in operations that involve multiple changes in different data elements, preventing data from being inserted incomplete due to systemic failures;
• In case of physical failure of the device, it makes it possible to safely restore the database to the last valid transactional checkpoint
• Allows correct propagation of data, in case of multiuser systems, organizing concurrent changes.

Disadvantages:

• Slower than a simple persistence operation;
• Can cause deadlocks;
• Unique transactions can make the database unavailable to other users, which are not within the scope of the transaction, thus slowing the system if operations within a transaction are as well.

Recommended to use:

• In operations that involve persistence of multiple entities;
• On systems that experience changes in real-time information, and these changes propagate to multiple users at the same time.