Transactions in the database

Time:2020-7-31

Transactions in the database

1、 What is business?

Database transaction is a sequence of database operations that can access and operate various data items. These operations are either all executed or not executed. It is an indivisible work unit. A transaction consists of all database operations performed between the beginning and the end of a transaction.

For example: transfer from account a to account B corresponds to the following two SQL statements
update from account set money=money+100 where name=‘b’;
update from account set money=money-100 where name=‘a’;

These two update statements should be run as a whole to ensure simultaneous success or failure; there should be no one success or one failure.

2、 Database open transaction command

We can use the following statement to start or end a transaction.

  • start transaction Open transaction
  • rollbackRollback transaction
  • commitCommit transaction

For example, we can put the above bank transfer UPDATE statement into a transaction for execution.

start transaction;
update from account set money=money+100 where name=‘b’;
update from account set money=money-100 where name=‘a’;
commit;

3、 Characteristics of transaction: Acid

  • Atomicity: atomicity refers to the fact that a transaction is an indivisible unit of work, and the operations in the transaction either occur or do not occur.
  • Consistency: a transaction must transform a database from one consistent state to another.
  • Isolation: when multiple users access the database concurrently, the transaction opened by the database for each user cannot be interfered by the operation data of other transactions. Multiple concurrent transactions should be isolated from each other.
  • Durability: persistence refers to that once a transaction is committed, its changes to the data in the database are permanent. Then, even if the database fails, it should not have any impact on it.

4、 Isolation level of the transaction

When multiple threads open their own transactions to operate the data in the database, the database system should be responsible for isolating operations to ensure the accuracy of each thread in obtaining data.

If isolation is not considered, the following problems may arise:

  • Dirty read: refers to one transaction reading uncommitted data of another transaction.
  • Non repeatable read: a row of data in a table is read in a transaction, and the results are different when read multiple times.
  • Virtual read (phantom read): refers to that the data inserted by another transaction is read in a transaction, resulting in inconsistent read before and after.

There are four isolation levels defined in the database

Serializable: it can avoid dirty reading, non repeatable reading and virtual reading. (serialization)
Repeatable read (default value of MySQL): it can avoid dirty read and non repeatable read. (repeatable)
Commit: committed read.
Read uncommitted: the lowest level. None of the above can be guaranteed. (read not submitted)

We can view the transaction isolation level of MySQL and set the isolation level through the following statements:

Set (global | session) transaction isolation level
select @@tx_ Isolation queries the current transaction isolation level

The following statement looks at the current transaction isolation level of the MySQL database.

Transactions in the database

The following statement sets the transaction isolation level of Mysql to read committed. Under this isolation level, dirty read will occur.

Transactions in the database

Transactions in the database