Beckham _ mysql storage engine and transaction concept, Beckham _ mysql

Beckham _ mysql storage engine and transaction concept, Beckham _ mysql
Storage engine and transaction conceptsBrief:
1. What is a storage engine?
2. What is a transaction?

 

I. Storage Engine

In general, it is the way to store and manage data.

Image example:

Bicycle administrators in a certain area: Li, Zhang. Many people access bicycles every day. Li's management method is: the car owner stores the car on his own, and Li does not record what car the other car stores. The administration fee is required when the car is picked up. It does not check whether it is the other party's own car. On the contrary, Zhang records the characteristics of the person and information of the car when the car owner stores the car. When a person comes to pick up a car, Zhang also needs to carefully check whether the characteristics of the person and the car correspond.

We can see from the above example:

A. From the perspective of efficiency: Li is faster than Zhang because he does not need to proofread

B. from the security point of view: Zhang is more secure than Li, because he has proofread.

Conclusion: in fact, the storage engine of mysql works like this. Each storage engine has its own access method. Therefore, you should choose based on the actual situation.

1. How to view mysql Engines

Mysql> show engines;

2. Differences between common storage engines myisam and innodb

2.1. myisam: Batch insertion is fast, and transactions and table locks are not supported.

Innodb: Batch insert is relatively slow and supports transactions and row locks.

 

Ii. Transactions

Generally speaking, a group of operations are either successfully executed or not executed.

For example:

User A transfers 500 yuan to user B

First, analyze the process:

1. Determine whether the card balance of user A is greater than 500 yuan. Otherwise, the transfer operation cannot be performed.

2. User A transfers money to user B based on the requirements of more than 500 RMB

3. If the transfer operation is successful, the balance of user A's card Minus 500 yuan, and user B plus 500 yuan

Use a program to describe this process

</Pre> <p> <pre name = "code" class = "php"> <? Php $ userA = $ momeyA; $ userB = $ momeyB; if ($ moneyA <500) {return false ;} $ sqlA = "update user set money = money-500 where name = 'usera '"; // step A $ resultA = $ db-> query ($ sqlA ); if ($ resultA) {$ sqlB = "update user set money = money + 500 where name = 'userb '"; // step B $ resultB = $ db-> query ($ sqlB); if ($ resultB) {return true ;} else {$ sqlA = "update user set money = money + 500 where name = 'usera '"; // Step C $ resultA = $ db-> query ($ sq LA); return false ;}}$ sqlA = "update user set money = money + 500 where name = 'usera '"; // step D $ resultA = $ db-> query ($ sqlA); return false;?>

From the transaction perspective:

1. if an error occurs during the transfer, the error does not affect both parties. That is to say, it is impossible for user A to reduce user A by 500 due to A certain error, and user B has 500 more while user B does not.

2. During the transfer process, user A cannot see that his card balance is reduced by 500, and user B cannot see that he has increased by 500 yuan, because the transfer operation has not ended.

3. In this process alone, the total amount of user A and user B remains unchanged. Only one Party decreases, and the other party increases.

Summarize the features of transactions

A. atomicity: a group of operations are either successfully executed or not executed.

B. Isolation: before all operations are completed, other sessions cannot see the intermediate change process.

C. Consistency: the total amount of data still matches before and after a transaction occurs.

D. Durability: the impact of the transaction cannot be undone.

The quieter you become, the more you are able to hear!

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.