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:
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]
Starting a transaction is done with the following command.
Starting a new transaction deactivates the mode
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
commitmay fail, for example if constraints are not verified at the exit of the transaction. In this case, the whole transaction is canceled.
In general, the DDL (
drop) trigger automatic validation of the transaction in which they are executed. They are said to work in auto-commit .
DML commands (
delete) 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.