Examples: Managing a Transaction in SQL

Oct 3, 2021
Examples: Managing a Transaction in SQL

3. Managing a transaction



3.1. Fashion auto-commit

The default mode of interaction when typing SQL commands is mode auto-commit. This means that the orders are taken into account and validated when they are executed. In this case there is no real transaction, or rather, each transaction begins and ends when we enter an order.

On MySQL, to deactivate this mode, you must enter the command:

Example 76. Exiting autocommit mode

set autocommit = 0 ;

Replacing the 0 by a 1 reactivates this mode. Still under MySQL =, you can also deactivate the referential constraints with the following command.

Example 77. Disable foreign key checking

set foreign_key_checks = 0 ;

Once this mode has been deactivated, it becomes possible to manage the start and end of transactions manually.

3.2. Set the insulation level

Setting the isolation level of a transaction is done with the following command.

Example 78. Set the transaction isolation level

set transaction isolation level 
   [read uncommitted |  read committed | repeatable  read | serializable]

3.3. Start a transaction

Starting a transaction is done with the following command.

Example 79. Start a transaction

start transaction;

All subsequent commands are performed in the context of this transaction, depending on the isolation level that has been chosen.

Starting a new transaction deactivates the mode autocommit.

3.4. Complete a transaction

A transaction can end with the validation or cancellation of orders that have been placed. In the first case, you must enter the command commit, in the second the command rollback.

An operation commitmay fail, for example if constraints are not verified at the exit of the transaction. In this case, the whole transaction is canceled.

3.5. Important Notes

In general, the DDL ( createalterdrop) trigger automatic validation of the transaction in which they are executed. They are said to work in auto-commit .

DML commands ( selectinsertupdatedelete) can not be canceled if they were automatically validated by executing a DDL command.

It is therefore important to avoid entering DML and DDL commands in the same transaction.

Leave a Reply

Your email address will not be published. Required fields are marked *