Transaction control and isolation level of the database

1 Overview

A transaction is a set of atomic SQL queries, or an independent unit of work that consists of one or more SQL statements, and MyISAM is not popular because it does not support transactional processing.

2 transaction log

The transaction log defines properties, some parameters can be modified at run time, written in the configuration section, the transaction log is equivalent to intermediate accessibility, and is critical.

The transaction log indicates that the operation process is recorded in a single stroke. If a thread is to operate on a row, the database will first keep the old version in the transaction log, write the file, the new version of the content is written to the log, before committing, the data in the log file, not in the data file, after the submission operation executes, the old and new versions of the log purged.

Each operation needs to be read from memory into the log when the transaction is executed

The transaction log is primarily intended to speed up operations, while converting random writes to sequential writes in order to facilitate the ability to submit data

The transaction log must be a fixed size, typically with two spaces, and once a space is full, it is written to disk and the other space begins to write. Rotation work.

In order to avoid the disk on which the transaction log is located, as a result of hard disk failure, you can write two files synchronously with raid or mirror group to ensure redundancy and data security.

The following are the parameters related to the transaction log in the configuration file:

Innodb_log_files_in_group: Indicates that there are several files within a group, typically two

Innodb_log_group_home_dir: Specify home directory

Innodb_log_file_size: Specifies the size of the log file, default 5M

Innodb_mirrored_log_groups: There are several log groups

3 acid Test

If a storage engine supports transactions, then the relational database must meet the acid test: Instead of the relational database is base (the basic word)

A:automicity, atomicity; all operations in the entire transaction are either successfully executed or rolled back to the beginning at all failures;

C:consistency, consistency; the database should always transition from one consistent state to another;

I:isolation, isolation; whether an action made by an office can be visible to other transactions before it is committed, and for the purpose of guaranteeing concurrent operations, there are multiple levels of isolation, which, if done rigorously, can lead to serial execution and lose the meaning of concurrent execution. There are four levels, the lowest isolation, the highest concurrency, the highest isolation, the highest security, but the least concurrency. A suitable scenario can be chosen based on security and concurrency of the server.

D:durability, persistence; Once a transaction is submitted, the changes it makes are permanently saved;

4 Transaction control

Autocommit: Single statement transaction, impacting performance

mysql> SELECT @ @autocommit; #查看自动提交功能参数

Mysql> SET @ @session. autocommit=0; #关闭自动提交功能后需要手动控制事务

Manually control transactions, and once a transaction is committed, it cannot be rolled back:

Start transaction: Start TRANSACTION

Commit TRANSACTION: Commit

ROLLBACK TRANSACTION: ROLLBACK

Transaction support savepoints: SavePoint, equivalent to a snapshot of a virtual machine

SavePoint identifier# Creating a Save point

ROLLBACK [Work] to [savepoint] identifier #还原到某个保存点

RELEASE SavePoint identifier# Destroy SavePoint

Example

MariaDB [sunny]> INSERT INTO classlist values ("su", "Ten", "the"), ("Chen", 18, "88"); MariaDB [sunny]>

#创建保存点first

MariaDB [sunny]> SavePoint First;

MariaDB [sunny]> Update classlist set name=ghbsunny where nu=1;

#创建保存点second

MariaDB [sunny]> Saveponit Second;

If you do not modify the name value of the nu=1, this

MariaDB [sunny]> rollback to first;

#注意, the rollback to first save point second does not exist because second was not created at the point of saving. Once the commint has been executed, it cannot be rollback

#销毁保存点first

MariaDB [sunny]> release savepoint first;

5 Transaction ISOLATION LEVEL

The level is low and high, and the highest level is serialization. Isolation is getting better, but concurrency is decreasing. There must be a high-level problem with low-level business

MySQL database default is the third isolation level repeatable-read, other databases generally default to the second level of read-committed, so MySQL recommendation is also modified to the second level read-committed

To view the transaction isolation level defined by the current session level

MariaDB [sunny]> SELECT @ @session. tx_isolation;

MySQL has four isolation levels, as follows:

Read-uncommitted:

READ UNCOMMITTED-easy to cause dirty reads, unconfirmed results can also be read, i.e. uncommitted updates are also visible to others without committing

Example: Open two CRT windows, log in to MySQL, turn off the auto-commit feature separately, and set the isolation level to read-uncommitted

MariaDB [sunny]> SET @ @session. autocommit=0;

MariaDB [sunny]> SET @ @session. tx_isolation= "read-uncommitted";

MariaDB [sunny]> start transaction;

Execute on Window 1

MariaDB [sunny]> update classlist set name= "BF" where nu=10;

At this point, window 1 does not perform a commit, but in window 2 can see the updated data, that is, the execution of the following statement on Window 2, see the name of nu=10 has changed to BF, that is, window 2 can be seen at any time window 1 any modifications

MariaDB [sunny]> SELECT * from Classlist where nu=10;

+------+----+-------+

| name | Nu | Score |

+------+----+-------+

| BF | 10 | 91.00 |

+------+----+-------+

Read-committed:

Read Submit-read other confirmed data, may lead to non-repeatable reading problems, may be repeated reading results are different;

Example: Open two CRT windows, log in to MySQL, turn off the auto-commit feature separately, and set the isolation level to read-committed

MariaDB [sunny]> SET @ @session. autocommit=0;

MariaDB [sunny]> SET @ @session. tx_isolation= "read-committed";

MariaDB [sunny]> start transaction;

Execute on Window 1

MariaDB [sunny]> Delete from classlist where nu=1;

At this point, window 1 does not perform a commit operation, Windows 1 see the nu=1 has been deleted, but view 2 still can see the record of nu=1

Windows 2 performs the following

MariaDB [sunny]> SELECT * from Classlist where nu=1;

+-------+----+--------+

| name | Nu | Score |

+-------+----+--------+

|  Sunny | 1 | 100.00 |

+-------+----+--------+

1 row in Set (0.00 sec)

No record is visible on page 2 after a commit is executed on window 1

Repeatable-read:

Repeatable read, the default isolation level of MySQL database and the problem of Phantom reads, such as other transactions have been modified data, but still see the old data;

Example: Open two CRT windows, log in to MySQL, turn off the auto-commit feature separately, and set the isolation level to read-committed

MariaDB [sunny]> SET @ @session. autocommit=0;

MariaDB [sunny]> SET @ @session. tx_isolation= "Repeatable-read";

MariaDB [sunny]> start transaction;

Execute on Window 1

MariaDB [sunny]> INSERT INTO classlist values ("Mei", 5,99);

There is no commit at this time, you can see the new Data on window 1, but the newly added data is not visible on Windows 2.

Execute on Window 1

MariaDB [sunny]> commit;

At this point, Windows 1 can see the newly added data, but Windows 2 does not see the new nu=5 data,

Window 2 thought that there is no nu=5 data, but window 2 to insert nu=5 data, there will be an error repeating the data hint

MariaDB [sunny]> INSERT INTO classlist values ("Mei2", 5,98);

ERROR 1062 (23000): Duplicate entry ' 5 ' for key ' PRIMARY '

MariaDB [sunny]> SELECT * from Classlist;

+----------+----+-------+

| name | Nu | Score |

+----------+----+-------+

|  Sunny | 1 | 98.00 |

|  Chao | 3 | 98.00 |

|  Tracy Su | 6 | 95.00 |

+----------+----+-------+

If you want to see the new data, Windows 2 can exit the SQL window and log in again to see the new nu=5 data

SERIALIZABLE:

serialization, the highest degree of isolation; only the other side's transaction ends (either commit or rollback), and another window can perform operations on the same table

Example: Open two CRT windows, log in to MySQL, turn off the auto-commit feature separately, and set the isolation level to read-committed

MariaDB [sunny]> SET @ @session. autocommit=0;

MariaDB [sunny]> SET @ @session. tx_isolation= "SERIALIZABLE";

MariaDB [sunny]> start transaction;

The following statement is executed on window 1, but no commit is executed, and without confirmation, no one else can view the table because the table is dead. The results can only be queried when the submission is rolled back.

Delete from classlist where nu=8;

At this point in the window 2 executes the following statement, then window 2 query results will not come out, waiting time expires, there will be an error

MariaDB [sunny]> SELECT * from Classlist;

ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction

Transaction control and isolation level of the database