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

Let's talk about the basics of MySQL transactions. The secret of the text is that it is relevant to the topic. So, no gossip, let's go straight to the following, I believe you will benefit from reading this article on the basics of MySQL affairs.

1. The basic syntax of transactions mysql > create table bank-> (- > name varchar (25),-> money float->); Query OK, 0 rows affected (0.00 sec) mysql > insert into bank values ('lu','1000'), (' qi','5000'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0mysql > begin; Query OK, 0 rows affected (0 sec) mysql > update bank set money=money-1000 where name='qi' Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > update bank set money=money+1000 where name = 'lu';Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > select * from bank +-+-+ | name | money | +-+-+ | lu | 2000 | | qi | 4000 | +-+-+ 2 rows in set (0.00 sec) mysql > rollback; Query OK, 0 rows affected (0.01 sec) mysql > select * from bank +-+-+ | name | money | +-+-+ | lu | 1000 | qi | 5000 | +-+-+ 2 rows in set (0.00 sec) mysql > commit; Query OK, 0 rows affected (0.00 sec) mysql > select * from bank +-+-+ | name | money | +-+-+ | lu | 1000 | | qi | 5000 | +-+-+ 2 rows in set (1000 sec)

The commands involved in a firm are as follows:

Transaction start: start transaction or begin Transaction commit: commit rollback: rollback to see if autocommit mode is automatic or manual mysql > show variables like 'AUTOCOMMIT' +-+-+ | Variable_name | Value | +-+-+ | autocommit | ON | +-+-+ 1 row in set (0.01sec) mysql > set AUTOCOMMIT=0; mysql > show variables like 'AUTOCOMMIT' +-+-+ | Variable_name | Value | +-+-+ | autocommit | OFF | +-+-+ 1 row in set (0.00 sec) 2, four isolation levels of transaction

Transactions are not visible to other transactions until they are committed.

Read unaommitted (uncommitted read) read committed (committed read) Repeatable read (repeatable read) seaializable (serializable) 1) uncommitted read

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

Mysql > create table student-> (- > id int not null auto_increment,-> name varchar (32) not null default',-> primary key (id)->) engine=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

Next, you need to open two MySQL session terminals, An and B, and execute the following command to set uncommitted reads.

Mysql > set session tx_isolation='read-uncommitted'

Client A:

Mysql > begin;Query OK, 0 rows affected (0.00 sec) mysql > select * from student;Empty set (0.00 sec) mysql > insert into student (name) values ('zhangyi'); mysql > set session tx_isolation='read-uncommitted'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > select * from student +-+-+ | id | name | +-+-+ | 2 | zhangyi | +-+-+ 1 row in set (0.00 sec)

Summary: the above can see that the uncommitted read isolation level is very dangerous, for one uncommitted transaction to make changes to another transaction is visible, there is a dirty read! This level is not recommended for non-special cases.

2) submitted for reading

Most database systems default to this level (MySQL is not). The committed read level is a modification made by a transaction that can only be made by a committed transaction, that is, the problem of uncommitted reads is resolved.

Client An insert data test:

Mysql > set session tx_isolation='read-committed';Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > begin;Query OK, 0 rows affected (0.00 sec) mysql > select * from student;+----+-+ | id | name | +-- +-+ | 2 | zhangyi | +-+ 1 row in set (0.00 sec) mysql > insert into student (name) values ('zhanger') Query OK, 1 row affected (0.00 sec) mysql > select * from student;+----+-+ | id | name | +-- +-+ | 2 | zhangyi | | 3 | zhanger | +-+-- + 2 rows in set (0.00 sec)

Client B view (will not see the data inserted by client A):

Mysql > select * from student;+----+-+ | id | name | +-+-+ | 2 | zhangyi | +-+-+ 1 row in set (0.00 sec)

Client A submits:

Mysql > commit;Query OK, 0 rows affected (0.01 sec)

Client B to view (you can see the data inserted by A):

Mysql > select * from student;+----+-+ | id | name | +-+-+ | 2 | zhangyi | | 3 | zhanger | +-+-- + 2 rows in set (0.00 sec)

Summary: from the above, we can see that committed reads have no problem with uncommitted reads, but we can see that client An executes the same SELECT statement twice in a transaction and gets different results, so committed reads are also called unrepeatable reads. The same screening criteria may get different results.

3) repeatable

Repeatable reading solves the problem of non-repeatable reading, and the database level does not solve the problem of phantom reading.

The following is the simultaneous operation of client An and client B (both set to repeatable, and then a transaction is opened on both sides):

Mysql > set session tx_isolation='repeatable-read';Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > begin;Query OK, 0 rows affected (0.00 sec)

Client A:

Mysql > select * from student;+----+-+ | id | name | 2 | zhangyi | 3 | zhangsi | +-+-+ 2 rows in set (0.00 sec) mysql > update student set name='zhanger' where id=3;Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > commit;Query OK, 0 rows affected (0.00 sec) mysql > select * from student +-+ | id | name | +-+-+ | 2 | zhangyi | | 3 | zhanger | +-+-- + 2 rows in set (0.00 sec)

Client B:

Mysql > select * from student;+----+-+ | id | name | +-+-+ | 2 | zhangyi | | 3 | zhangsi | +-+-- + 2 rows in set (0.00 sec) mysql > commit; Query OK, 0 rows affected (0.00 sec) mysql > select * from student +-+ | id | name | +-+-+ | 2 | zhangyi | | 3 | zhanger | +-+-- + 2 rows in set (0.00 sec)

Summary: as you can see above, the content that can be read twice is not the same. The problem of phantom reading in the database has not been solved. Phantom read-only lock the data inside, can not read the data outside the lock, solve the illusory read out of the mvcc mechanism Mvcc mechanism.

4) serializable

Is the highest isolation level, forcing the serial execution of transactions, the execution of serialization will solve the problem, which can only be used in cases where data consistency is very strict and there is no concurrency.

Do the following on client An and client B (set to serial readable):

Mysql > set session tx_isolation='serializable'

Client A:

Mysql > begin;Query OK, 0 rows affected (0.00 sec) mysql > select * from student where id

Insert into student (name) values ('zhangqi'); ERROR 1205 (HY000): the comparison of the above isolation levels of Lock wait timeout exceeded; try restarting transaction is as follows: isolation level, dirty read, non-repeatable phantom read, locked uncommitted read, whether committed read or not, repeatable read, no serial read, no no

Is there anything you don't understand about the basics of the above MySQL transactions? Or if you want to know more about it, you can continue to follow our industry information section.

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.