The article directories

  • Introduction of the transaction
  • The transaction operations
  • Transaction Characteristics (ACID)
  • Transaction security Risk

The basics of MySQL can be found in this blog post: Collectible MySQL Basics Summary

Introduction of the transaction

Transaction (Transaction)

  • A transaction is the smallest non-divisible unit of work. Usually one transaction corresponds to one complete transaction (such as a bank account transfer).
  • Transaction processing can be used to maintain database integrity by ensuring that batches of SQL statements are either all executed or none executed.

The transaction operations

Open the transaction

start transaction;

Commit transactions, which write data to disk

commit

Roll back the transaction to the original state

rollback

Check whether automatic commit is enabled for the transaction

show variables like 'autocommit';



As you can see in MySQL transactions are committed automatically by default. To demonstrate transactions, first we need to turn off auto-commit.

Turn off automatic transaction commits

set autocommit=off;



The following illustrates the transaction with a bank transfer example, where account A transfers 100 yuan to account B



The following two commands must both succeed or fail at the same time, which is a transaction

update bank set money=700 where id=1;
update bank set money=600 where id=2;
Copy the code

Start transaction; And then execute the transaction

update bank set money=700 where id=1;
update bank set money=600 where id=2;
Copy the code

Finally commit the transaction commit; If only the first operation succeeds, the data is stored in memory and not persisted to the database. If the second operation succeeds, the transaction can be committed to disk. If the second statement fails, the transaction can be persisted to disk. You can rollback to the original state with rollback, which requires transactions.

Transaction Characteristics (ACID)

  • Atomicity: A transaction is the smallest unit and cannot be divisible
  • Consistency: transactions require that all operations be guaranteed to succeed or fail simultaneously
  • Isolation: Transactions are isolated from each other
  • Durability: Transactions succeed, they persist to disk

Transaction security Risk

There is isolation between transactions, and there are four levels of isolation

Read uncommittedTransaction B reads uncommitted data from transaction A, also called:Reading dirty data

Read CommittedTransaction B reads the data committed by transaction A (resolves dirty reads, which is the default Isolation level of Oracle), that is, transaction A commits the data, then transaction B can read, commit once, read only once, this is also called:Unrepeatable read

Repeatable readThe default isolation level of MySQL is dirty reads and unrepeatable reads. The default isolation level of MySQL is dirty reads and unrepeatable reads.Phantom read

SerializableWhen transaction A processes data, transaction B can only queue (solve the dirty read, non-repeatable read and unreal read), that is, it is serialized, cannot be concurrent, low efficiency



View the isolation boundaries for the transaction

select @@transaction_isolation;MySQL is repeatable by default.



Set the isolation level to Read Uncommitted

set session transaction isolation level read uncommitted;

Set the quarantine sector to read committed

set session transaction isolation level read committed;

Set the isolation level to repeatable read

set session transaction isolation level repeatable read;

Sets the isolation boundary to serializable

set session transaction isolation level serializable;