Mysql Tutorial: mysql transaction and mysql 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.

Transaction Concept and storage engine

1.0 why transaction?

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

Li Yanhong and Zhou Hong? Fighting every day, now let Li Yanhong give Zhou Hong? Transfer 1000 yuan

Design the following table

Account table

Id)	User)	Amount (cash)
1	Li Yanhong	3000
2	Zhou Hong?	2000

Traditional practices:

mysql> update account set cash = cash -1000 where id =2;       update account set cash =cash +1000 where id=1;

Insecure. For example, Li Yanhong's instruction execution has been reduced, but Zhou Hong has been added? The money instruction was not executed successfully. Defects:

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 above example: Li Yanhong gave Zhou Hong? Code for money transfer:

# Start transaction; # run the code Update account set cash = cash-1000 where id = 1; Update account set cash + 1000 where id = 2; # submit commit # roll back if a statement error occurs # rollback

1.5 when transactions are used:

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

# P # mysql transaction and storage engine-mysql storage engine # e #

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