When not controlling creation , to update , read , When deleting , What will happen ?

  The data needs to be updated in time after purchase , Avoid errors .

What is a transaction ?

A transaction is a set of database operation statements (DML), One group or all succeed , Or all fail .

A transaction is a whole composed of multiple operations to realize a thing .

One MySQL database , It's definitely not a transaction running , So when there's a conflict , Those half implemented sql Transactions consisting of statements , What should I do ?

remarks :

sql sentence :

* DDL( Define database objects , Table and column ),
* DML( Used to manipulate record data in database tables ),
* DQL( Query data ),
* DCL( Define access rights and security levels )
therefore , Transactions cannot be just sql Combination of statements , The following attributes are also required .

* Atomicity
: A transaction (transaction) All operations in , Or finish it all , Or not all , It won't end in the middle . An error occurred during the execution of the transaction , Will be rolled back (Rollback) To the state before the start of the transaction , It's like this business has never been performed .
* uniformity
: Before the transaction starts and after the transaction ends , The integrity of the database was not compromised . This means that the written data must fully comply with all preset rules , This includes the accuracy of the data , Tandem and subsequent databases can spontaneously complete the predetermined work .
* Isolation
: The ability of a database to allow multiple concurrent transactions to read, write, and modify its data at the same time , Isolation can prevent data inconsistency caused by cross execution when multiple transactions are executed concurrently . Transaction isolation is divided into different levels , Include read uncommitted (
Read uncommitted ), Read commit ( read committed ), Repeatable reading ( repeatable read ) And serialization (
Serializable )
* persistence : After transaction , The modification of data is permanent , Even if the system fails, it will not be lost .

Why do transactions occur

Transactions are designed when an application accesses a database , Can simplify our programming model .

Version support for transactions

MySQL Only used in innodb Only the database or table of the database engine supports transactions ,myisam I won't support it .

Transaction submission method

There are two common transaction submission methods :

* Auto submit
* Manual submission
View transaction submission method

  use SET To change MySQL Automatic submission mode , Remember to change it back


  Common operation modes of transaction

Create test table

Proving the start and rollback of a transaction



Transaction isolation level

In database , In order to ensure that there is no interference in the execution of transactions , There is isolation .

In database , Allow transactions to be disturbed to varying degrees , There is an isolation level .

Isolation level

* Read uncommitted 【Read Uncommitted】: At this isolation level , All transactions can see the execution results of other transactions that are not committed .
* Read commit 【Read Committed】 : This isolation level is the default isolation level for most databases ( no MySQL
default ). It satisfies the simple definition of isolation : A transaction can only see the changes made by other committed transactions .
* Repeatable reading 【Repeatable Read】: This is MySQL
Default isolation level , It ensures the same transaction , In execution , When reading operation data multiple times , You will see the same data row . But there will be unreal reading problems .
* Serialization 【Serializable】: This is the highest isolation level for transactions , It does this by forcing transaction sequencing , Make it impossible to conflict with each other , Thus, the problem of unreal reading is solved .
Isolation is basically achieved through locks .

View global isolation level

  View the global isolation level of the current session

  Set current session or Global isolation level syntax


Set the current session isolation level to serialization

Let's look at the change of session isolation level


Read uncommitted

  Open transaction

Open another terminal B, adopt B visit

  A transaction is in progress , Read an update from another transaction in progress ( Or other operations ) But not commit Data , This phenomenon is called dirty reading (dirty read).

Read commit

Repeatable reading


Isolation level comparison