MySQL transaction with MySQL storage engine

Transaction concepts and Storage engines

1.0 Why business?

Let's take a look at a scenario, Bank transfer remittance:

Robin Li and Zhou Hongyi fight every day, now let Li give Zhou 祎 transfer money 1000 yuan

Design the following table

Account table

Number (ID) Username (user) amount (cash)

1 Robin Li 3000

2 weeks Hung 祎 2000

The traditional approach:

Mysql> Update account Set cash = cash-1000 where id = 2;

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

Defects:

Not secure. For example, the instructions to reduce Li's money were executed, but the instructions to increase Zhou 祎 money did not succeed.

At this point, we're going to use the MySQL thing mechanism.

1.1 Business

　　

1.2 Use of the transaction

Transaction-related statements DTL

　　

Four features of 1.3 transactions:

1. Atomicity is an indivisible logical unit, a set of SQL statements that are either executed or not executed.

2. Isolation, the execution process in a transaction is not visible.

3. Persistence, once a transaction is submitted, it cannot be revoked.

4. Consistency, the data is consistent before and after the transaction occurs. (Conservation of energy)

1.4 Principles of Business:

Traditional way:

　　

Transaction mechanism:

　　

The above example: Robin Li gives Zhou 祎 the code to transfer money with things:

#开启事物

Start transaction;

#执行的代码

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

Update account set cash+1000 where id=2;

#提交

Commit

# statement error can be rolled back

#rollback

1.5 Where the transaction is applied:

In general, transactions are recommended for businesses that require comparison of security requirements.

2.0 MySQL Storage engine

What is a storage engine?

We say that databases are warehouses that organize, store, and manage data. So, the way the database stores data is the storage engine.

In MySQL, the storage engine is loaded in the form of a plug-in. MySQL has a wide variety of storage engines, for us to be familiar with two kinds of storage engines, MyISAM and Inonodb.

MyISAM does not support transactions.

InnoDB support transactions.

Full-text indexing in previous versions of mysql5.5, InnoDB is not supported, but is supported in versions after 5.5.

Common storage engines

　　

MySQL default storage engine,

Prior to mysql5.5 version: MyISAM

After mysql5.5 version: InnoDB

If we want to declare the storage engine ourselves:

Create table () Engine=myisam default CharSet UTF8

MySQL transaction with MySQL storage engine