Mysql transaction and mysql storage engine

Transaction concept and storage engine

1.0 why transaction?

Let's look at a scenario where bank transfers and remittances:

Li Yanhong and Zhou Hongyi fought every day and now asked Li Yanhong to transfer 1000 yuan to Zhou Hongyi

Design the following table

Account table

No. (id) user amount (cash)

1 Li Yanhong 3000

2 zhou Hongyi 2000

Traditional practices:

Mysql> update account set cash = cash-1000 where id = 2;

Update account set cash = cash + 1000 where id = 1;

Defects:

Insecure. For example, the command to reduce Li Yanhong's money was executed, but the command to increase Zhou Hongyi's money was not executed successfully.

At this time, we need to use the mysql transaction mechanism.

Transaction 1.1

　　

 

1.2 use of transactions

Transaction-related statement DTL

　　

 

1.3 four features of transactions:

1. Atomicity is an inseparable logical unit. A group of SQL statements are either executed or not executed.

2. Isolation, the execution process in the transaction is invisible.

3. durability. Once a transaction is committed, it cannot be undone.

4. Consistency. Data is consistent before and after a transaction occurs. (Conservation of energy)

1.4 principles of transactions:

Traditional methods:

　　

 

Transaction Mechanism:

　　

 

The preceding example shows how Li Yanhong transfers money to Zhou Hongyi:

# Enable things

Start transaction;

# Executed code

Update account set cash = cash-1000 where id = 1;

Update account set cash + 1000 where id = 2;

# Submit

Commit

# Statement errors can be rolled back

# Rollback

1.5 when transactions are used:

Generally, transactions are recommended for businesses with higher security requirements.

2.0 mysql storage engine

What is a storage engine?

We say that databases are warehouses that organize, store, and manage data. Then, the database stores data by means of a storage engine.

In mysql, the storage engine is loaded as a plug-in. Mysql has a wide variety of storage engines. For us, we need to be familiar with the two storage engines, MyISAM and inonoDB.

Myisam does not support transactions.

Innodb supports transactions.

Full-text indexing is not supported by innodb in versions earlier than MySQL 5.5, but is supported by Versions later than MySQL.

Common storage engines

　　

 

The default storage engine of mysql,

Versions earlier than mysql5.5: MyISAM

Versions later than mysql5.5: innodb

If we want to declare the storage engine ourselves:

Create table () engine = myisam default charset utf8