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

Transaction commit and isolation levels for Mysql

First, what is the business?

In short, a transaction is a set of SQL implementations that either succeed or fail. MYSQL transactions are implemented at the storage engine layer.

1. All transactions have ACID features:

Atomicity: a transaction must be treated as an indivisible unit

Consistency: databases always switch from one state to another

Isolation: generally speaking, transactions are not visible to other transactions until they are committed

Durablity: once the transaction commits, the changes permanently save the database

The most common example of a transaction is a bank transfer. Suppose polo needs to transfer 1000 CNY to tom, as follows:

Confirm that the balance of polo account is higher than 1000 yuan.

Subtract 1000 yuan from polo's account balance

Increase the account balance of tom by 1000 yuan

Mysql > create table bank_accout (uid int not null,name varchar, balance decimal); mysql > insert into bank_accout values (10001); mysql > select * from bank_accout +-+ | uid | name | balance | +-+ | 10001 | polo | 5000.00 | | 10002 | tom | 3000.00 | +-+

The SQL statement is as follows:

Mysql > BEGIN; mysql > select * from bank_accout;+-+ | uid | name | balance | +-+ | 10001 | polo | 5000.00 | 10002 | tom | 3000.00 | +-+ mysql > UPDATE bank_account SET balance=balance-1000 WHERE uid=10001; mysql > UPDATE bank_account SET balance=balance+1000 WHERE uid=10002; mysql > COMMIT Mysql > select * from bank_accout;+-+ | uid | name | balance | +-+ | 10001 | polo | 4000.00 | | 10002 | tom | 4000.00 | +-+ mysql > BEGIN; or mysql > START TRANSACTION # mysql starts transaction mysql > Rollback; # rollback and returns before modification. Mysql > commit; # submit the data before you actually modify the data.

Performing the above steps in a single transaction ensures the integrity of the data, either all successful or all failed.

2. Mysql provides two transactional engines:

Innodb and NDBCluster. Auto-commit mode is used by default, and a statement is executed automatically COMMIT. Autocommit mode can be enabled or disabled through the AUTOCOMMIT variable:

Mysql > SHOW VARIABLES LIKE "AUTOCOMMIT"; +-+-+ | Variable_name | Value | +-+-+ | autocommit | ON | +-+-+ 1 row in set (0.00 sec) mysql > SET AUTOCOMMIT=1;AUTOCOMMIT=1 means default submission is enabled, 0 means manual submission is required to disable default submission.

II. Transaction isolation level

Explanation of transaction isolation: typically, transactions are not visible to other transactions until they are committed.

There are four isolation levels for databases, as does Mysql. They are:

READ UNCOMMITED (read not submitted)

READ COMMITED (read submitted)

EPEATABLE READ (repeatable)

SEAIALIZABLE (serializable)

Personal understanding: the isolation level is to determine the modification of one transaction and when another transaction is visible.

Book explanation: isolation levels specify changes made in a transaction and which are visible within and between transactions.

The difference between the above two paragraphs is whether there is a provision for visibility within the transaction.

To begin with the four isolation levels of Mysql, prepare a student table:

Mysql > CREATE TABLE `student` (`id` int (11) NOT NULL AUTO_INCREMENT, `name` varchar (32) NOT NULL DEFAULT'', PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

Only id (primary key self-increment) and name fields

1. READ UNCOMMITTED (not submitted for reading)

Changes in transactions that are not committed are also visible to other transactions, commonly known as dirty reads. It is very deprecated.

Example:

Clients An and B set the isolation level to uncommitted read

Mysql > SET SESSION TX_ISOLATION='READ-UNCOMMITTED'

Clients An and B start the transaction and query student

Mysql > BEGIN; mysql > SELECT * FROM student; Empty set (0.00 sec)

Client An and B are both empty data

Customer server B inserts a new piece of data

Mysql > INSERT INTO student (name) VALUES ("polo"); Query OK, 1 row affected (0.00 sec)

At this time, the transaction is not committed, and customer server A looks at the student table.

Mysql > SELECT * FROM student; +-+-+ | id | name | +-+-+ | 1 | polo | +-+-+

Client A sees B's uncommitted changes

Client B performs a rollback operation

Mysql > ROLLBACK

After success, client A looks at the student table

Mysql > SELECT * FROM student; Empty set (0.00 sec)

Client A view data is empty

The above can see the danger of uncommitted read isolation level. Making changes to one uncommitted transaction is visible to another transaction, which is easy to cause dirty reading. This level may not be used in non-special circumstances

2. READ COMMITTED (submit for reading)

Most database systems default to this level (Mysql is not). The committed read level is a modification that a transaction can only make by a committed transaction, which solves the problem of uncommitted reading, that is, the problem of dirty reading.

Example:

Clients An and B set the isolation level to committed read

Mysql > SET SESSION TX_ISOLATION='READ-COMMITTED'

Clients An and B start the transaction and query student

Mysql > BEGIN; mysql > SELECT * FROM student; Empty set (0.00 sec)

Both client An and B are empty

Customer server B inserts a new piece of data and does not submit it

Mysql > INSERT INTO student (name) VALUES ('polo')

Client A View student

Mysql > SELECT * FROM student; Empty set (0.00 sec)

Note that this is different from the above, where there is no data when client B does not commit the transaction

The following client B commits the transaction

Mysql > COMMIT

Client A looks at the student table again.

Mysql > select * from student;+----+-+ | id | name | +-+-+ | 2 | polo | +-+-+

Successfully read to the customer

From the above example, we can see that committed reads have no problem with uncommitted reads, but we can see that executing the same SELECT statement twice in a transaction in client A results in different results, so committed reads are also called unrepeatable reads. The same screening criteria may get different results.

3. REPEATABLE READ (repeatable)

Resolve the problem that committed reads are not repeatable.

Example:

Clients An and B set the isolation level to repeatable

Mysql > SET SESSION tx_isolation='REPEATABLE-READ'

Clients An and B open the transaction and view

Mysql > BEGIN; mysql > select * from student;+----+-+ | id | name | +-+-+ | 2 | polo | +-+-+

Customer server B updates polo to tom and commits the transaction

Mysql > UPDATE student SET name='tom' WHERE id=2; mysql > COMMIT

Client A views the student table

Mysql > select * from student;+----+-+ | id | name | +-+-+ | 2 | polo | +-+-+

Note that the data checked by client A has not changed, and there is no unrepeatable read problem.

Client A commits the transaction and looks at the student table

Mysql > COMMIT; mysql > select * from student;+----+-+ | id | name | +-+-+ | 2 | tom | +-+-+

As can be seen from the above example, the content can be read twice. The database level does not solve the problem of phantom reading. However, MYSQL solves this problem by adding MVCC mechanism on the basis of repeatable reading, and the example can not demonstrate the effect of phantom reading.

What is phantom reading?

First of all, the scope of the lock can be repeated to the contents of the current query, such as executing

Mysql > SELECT * FROM student WHERE id > = 1

The row that is locked is the row found by id > = 1, which is a row-level lock. If another transaction executes and the following statement is submitted by default

Mysql > INSERT INTO student (name) VALUES ('polo')

The new line is not locked, so read student at this time

Mysql > SELECT * FROM student WHERE id > = 1 * id | id | name | +-+-- + | 2 | tom | | 3 | polo | +-+-- +

There was a phantom reading.

In addition to using MYSQL's MVCC mechanism, serializable isolation levels can also be used to solve this problem.

4. SEAIALIZABLE (serializable)

Serializability is the highest isolation level that forces transactions to execute serially. Performing serialization solves all the problems, and this level can only be used when data consistency is very strict and concurrency is not used.

Example:

Clients An and B set the isolation level to serializable

Mysql > SET SESSION tx_isolation='SERIALIZABLE'

Client An executes query

Mysql > BEGIN;mysql > SELECT * FROM student WHERE id INSERT INTO student (name) VALUES ('yy')

At this point, we will find that the INSERT statement is blocked because An executes the query table student and satisfies id.

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.