What are Begin, Commit and Rollback transactions?

Question

What are Begin, Commit, and Rollback Transactions? And how to use them?

Example in practice will help a lot in understanding.

Answer 1

Actually transaction is the whole process of query or manipulation of the database. It is a way to establish that something should be done atomically, I mean, you do everything or do nothing, you can’t do it in half. It’s all done on a journey from the app to the database. Under normal conditions while the transaction does not end other transactions cannot see what it is doing. Understand what ACID is.

These 3 SQL commands are to control this.

The BEGIN TRANSACTION indicates where it should start, then the following SQL commands will be within this transaction.

The COMMIT TRANSACTION indicates the normal end of the transaction, the one you have to command after will no longer be part of this transaction. At this moment everything that was manipulated becomes part of the database normally and diverse operations come to see what was done.

The ROLLBACK TRANSACTION also closes the transaction block and is the indication that the transaction should be terminated, but everything you tried to do should be discarded because something wrong has happened and it cannot end normally. Nothing accomplished within it will be missed in the database.

Contrary to what many people believe rollback in the context of database does not mean revert but return to the original state. A reversal process would be from complicated to impossible. A disposal process is simple and can be atomic.

Most SQL commands are transactional implicitly, i.e., it itself is already a transaction. You only need to use these quoted commands when you need to use multiple commands and all of these must run atomically. That is, they work like the keys to a code, they create a block. Actually it’s more like the using since there is a guaranteed consequence at the end of the execution.

CREATE TABLE ValueTable (id int);  
BEGIN TRANSACTION;              -- aqui começa a transação
       INSERT INTO ValueTable VALUES(1);  
       INSERT INTO ValueTable VALUES(2);  
COMMIT;                         -- aqui termina e "grava" tudo

I put in the Github for future reference.

Until the COMMIT these inserts are not actually in the database. A ROLLBACK would discard everything done before.

The documentation has more information and the subject is interesting, pity not to have more questions about this.

Although it is Mysql it is possible to understand more about transactions.

Answer 2

In the book Sistemas de banco de dados, from Elmasri & Navathe, chapters 17 to 19 (pages 395 to 452, 4th edition) deal with transaction processing theory. On the question "What are transactions", the text informs that "A transaction includes one or more database access operations - they include insertion, deletion, modification or recovery operations" and also cites that "A transaction is an atomic unit of work that is either complete or not performed".

Another part of your question mentions BEGIN TRANSACTION, COMMIT and ROLLBACK: "One way to specify the limits of a transaction is to explicitly establish transaction start and end-of-transaction declarations". In the case of the SQL statements you quoted, BEGIN TRANSACTION refers to the initiation of the transaction and COMMIT at the end of the transaction.

When using the pair BEGIN TRANSACTION/COMMIT define the limits of the transaction: "... all database access transactions between the two shall be considered as part of the transaction".

As an example, the classic case of value transfer between two bank accounts. The schedule must be such that it is not possible to withdraw the account value and, for any fault, the process is stopped before the value is deposited in the recipient’s account.

-- código #1
declare @contaDe char(6), @contaPara char(6), @Valor money;

set @contaDe= '029820';
set @contaPara= '407302';
set @Valor= 1200.00;

BEGIN TRANSACTION;

UPDATE CONTA_CORRENTE
  set saldoConta= saldoConta - @Valor
  where numConta = @contaDe;

UPDATE CONTA_CORRENTE
  set saldoConta= saldoConta + @Valor
  where numConta = @contaPara;

COMMIT;
go

In the case of bank transfer there are several additional procedures, but the purpose here is to exemplify the use of the instructions BEGIN TRANSACTION and COMMIT.

Already the instruction ROLLBACK "reverts an explicit or implied transaction to the beginning of the transaction or to a rescue point within the transaction", as shown in the documentation of the instruction. It is usually used when something unexpected occurs in the processing and it is then necessary to undo the processing carried out in the transaction.

The concept of transaction is closely linked to competition of proceedings and also to properties ACID (atomicity, consistency, isolation and durability).

Documentation in Portuguese for SQL Server:

• BEGIN TRANSACTION
• COMMIT TRANSACTION
• ROLLBACK TRANSACTION
• SAVE TRANSACTION