Shulou(Shulou.com)06/01 Report--

What is a transaction?

A transaction is a series of operations performed as a logical unit. A logical unit of work must have four attributes, called ACID (atomicity, consistency, isolation, and persistence) attributes. Only in this way can it become a transaction.

First, the ACID of the transaction (Atomicity) A set of operations are either all successful or all failed. Consistency ensures the correctness, reasonableness and integrity of the data. When the transaction is completed, all data must be kept consistent, for example, during a transfer, the amount deducted from one account must be equal to the amount deposited in another account. Isolation (Isolation) A session cannot modify data that another user has modified but not submitted. Durability) once a thing is committed, the change in the data in the database is permanent.

II. Isolation levels of transactions in general databases, there are four isolation levels

As shown in the figure above, the four isolation levels achieve different functions. The higher the isolation level, the more locks are needed and the blocking is more likely to occur.

Next, we introduce these kinds of reading through experiments. (1) adjust the isolation level to READ-UNCOMMITTED root@localhost:mysql.sock 01:30:05 [(none)] > set global tx_isolation = 'READ-UNCOMMITTED'; root@localhost:mysql.sock 01:30:22 [(none)] > show global variables like'% iso%'. +-+-+ | Variable_name | Value | +-+-+ | tx_isolation | READ-UNCOMMITTED | +-- -+ 1 row in set (0.00 sec) session 1: root@localhost:mysql.sock 01:34:06 [lala] > select * from score Empty set (0.00 sec) root@localhost:mysql.sock 01:34:53 [lala] > begin; Query OK, 0 rows affected (0.00 sec) root@localhost:mysql.sock 01:35:23 [lala] > insert into score values; Query OK, 1 row affected (0.00 sec) has not yet submitted session 2: root@localhost:mysql.sock 01:37:08 [lala] > select * from score +-+ | id | name | score | +-+ | 1 | xiaohong | 99 | +-+ 1 row in set (0.00 sec) session 2 can directly read the data that has not been submitted by session 1. (2) do not repeat root@localhost:mysql.sock 01:30:05 [(none)] > set global tx_isolation = 'READ-COMMITTED'; root@localhost:mysql.sock 01:30:22 [(none)] > show global variables like'% iso%' +-+-+ | Variable_name | Value | +-+-+ | tx_isolation | READ-COMMITTED | +-- -+ 1 row in set (0.00 sec) session 1: root@localhost:mysql.sock 01:49:00 [lala] > begin Query OK, 0 rows affected (0.00 sec)

Root@localhost:mysql.sock 01:49:08 [lala] > update score set id=3; Query OK, 1 row affected (0.00 sec)

Session 2: root@localhost:mysql.sock 01:49:14 [lala] > select * from score +-+ | id | name | score | +-+ | 1 | xiaohong | 99 | +-+ 1 row in set (0.00 sec) session 1: root@localhost:mysql.sock 01:49:20 [lala] > commit Query OK, 0 rows affected (0.00 sec) session 2: root@localhost:mysql.sock 01:49:47 [lala] > select * from score +-+ | id | name | score | +-+ | 3 | xiaohong | 99 | +-+ 1 row in set (0.00 sec) session 2 the first read-only id value is 1, but it is submitted as session 1. Session 2 reads id for the second time is 3. (3) Phantom Reading session 1: root@localhost:mysql.sock 01:49:00 [lala] > begin Query OK, 0 rows affected (0.00 sec)

Root@localhost:mysql.sock 01:49:08 [lala] > insert into score values (2 Query OK, 1 row affected (0.00 sec)

Session 2: root@localhost:mysql.sock 01:49:14 [lala] > select * from score +-+ | id | name | score | +-+ | 1 | xiaohong | 99 | +-+ 1 row in set (0.00 sec) session 1: root@localhost:mysql.sock 01:49:20 [lala] > commit Query OK, 0 rows affected (0.00 sec) session 2: root@localhost:mysql.sock 01:50:27 [lala] > select * from score +-+ | id | name | score | +-+ | 3 | xiaohong | 99 | | 2 | xiaoming | 65 | +-+ 2 rows in set (0.00 sec) session 2 only read one piece of data for the first time However, session 1 submitted and session 2 read two records for the second time.

Ps: unrepeatable reading is caused by the update of the data, while phantom reading is caused by the delete or insert of the data

3. Transaction mysql in mysql enables automatic transaction commit by default, that is, it commits automatically every time a sql statement is executed. So how do you disable transaction autocommit? As follows: session level: set autocommit=off; Bureau level: method 1: set global init_connect='set autocommit=0'; method 2: modify the parameter file my.cnf [mysqld] init_connect='set autocommit=0'; method 3: when starting the mysql service, add the parameter-init_connect='set autocommit=0'; to start a transaction through begin, and then submit it through commit The default isolation level for transactions of innodb in mysql's default isolation level mysql is Repeatable read (repeatable), but it is not an ordinary Repeatable read and avoids phantom reading on a repeatable basis. Non-repeatable reading is achieved through the infamous gop lock. Myql determines the transaction isolation level through tx_isolation. You can view the current parameter root@localhost:mysql.sock 01:17:04 [(none)] > show global variables like'% iso%'. +-+-+ | Variable_name | Value | +-+-+ | tx_isolation | REPEATABLE-READ | +-+-+ 1 row In set (0.00 sec), so to modify the transaction isolation level of mysql Just modify this parameter directly.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.