MySQL transaction analysis

Transaction

A transaction is a basic unit of business logic.

Each transaction consists of a series of SQL statements.

DML statements related to transactions ( insert , delete , update )

The existence of transactions ensures the security of data.

Transaction mechanism:

Each execution of a DML statement will record the operation, but will not modify the data.

Finally, commit the transaction (delete records, modify hard disk data) or roll back the transaction (delete records, do not modify data).

Transactions have four major characteristics : ACID

• A: Atomicity, a transaction is the smallest unit of work
• C: Consistency, DML statements in a transaction either all succeed or all fail
• I: Isolation, isolation between transactions.
• D: Persistence, the data is finally persisted to the hard disk before it ends.

Transaction isolation level:

1. read uncommitted means that a transaction can read data that another transaction has not committed

This level has dirty read phenomenon

2. Read ead committed , you can read the committed data

This level solves the dirty read problem, but it cannot be read repeatedly.

3. repeatable read : the data read by a transaction is independent of the data submitted by other transactions. The data at the beginning of the transaction can be read repeatedly.

The problem of non-repeatable reads is solved, but phantom reads still exist and the data read is not real.

4. Serialized read/serialized read. All problems are solved, similar to thread safety in multithreading. But there are inefficiencies. Because transactions need to be queued.

The default isolation level of the mysql database is level 3. Repeatable read.

mysql transactions are automatically committed by default. Execute a DML to directly modify the data on the hard disk.

Want to commit the transaction manually. Before executing DML. First start transaction ; then execute DML , and finally commit or rollback.

Demonstrate manual rollback of a transaction:

drop table if exists t_user1;

create table t_user1(

id int(3) primary key auto_increment,

username varchar(10)

);

mysql> create table t_user1(

-> id int(3) primary key auto_increment,

-> username varchar(10)

-> );

Query OK, 0 rows affected (0.02 sec)

mysql> insert into t_user1(username) values('h1');

Query OK, 1 row affected (0.01 sec)

mysql> select * from t_user1;

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

| id | username |

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

| 1 | h1 |

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

1 row in set (0.00 sec)

mysql> rollback; //Rollback transaction Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user1; //After rollback, it is still the same as before, because mysql automatically submits +----+----------+

| id | username |

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

| 1 | h1 |

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

1 row in set (0.00 sec)

mysql> start transaction; //Manually start transaction, turn off automatic transaction submission Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_user1(username) values('h2');

Query OK, 1 row affected (0.01 sec)

mysql> insert into t_user1(username) values('h3');

Query OK, 1 row affected (0.00 sec)

mysql> insert into t_user1(username) values('h4');

Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user1;

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

| id | username |

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

| 1 | h1 |

| 2 | h2 |

| 3 | h3 |

| 4 | h4 |

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

4 rows in set (0.00 sec)

mysql> rollback; //Rollback Query OK, 0 rows affected (0.01 sec)

mysql> select * from t_user1;

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

| id | username | In the end, the data on the hard disk is still the same as before.

+----+----------+ Roll back the deletion record without modifying the data on the hard disk.

| 1 | h1 |

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

1 row in set (0.00 sec)

This is the end of this article about MySQL transaction analysis. For more relevant MySQL transaction content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!