Mysql transaction and storage engine instance analysis

1. MySQL transaction

1. The concept of transaction

(1) A transaction is a mechanism, an operation sequence, which includes a set of database operation commands and combines all commands Submit or revoke operation requests to the system as a whole, that is, this set of database commands will either be executed or none of them will be executed.

(2) A transaction is an indivisible logical unit of work. When performing concurrent operations on a database system, a transaction is the smallest control unit.

(3) Scenarios of database systems operated by multiple users at the same time, such as banks, insurance companies, securities trading systems, etc., suitable for transaction processing. (4) Transactions ensure data consistency through transaction integrity.

2. ACID characteristics of transactions

Note: ACID refers to the four characteristics that transactions should have in a reliable database management system (DBMS): Atomicity , Consistency, Isolation, Durability. These are several characteristics that a reliable database should have.

(1) Transactions are atomic, that is to say, the operations in the transaction are either all executed or not executed at all and are indivisible. a. A transaction is a complete operation, and the elements of the transaction are inseparable.

b. All elements in the transaction must be committed or rolled back as a whole.

c. If any element in the transaction fails, the entire transaction will fail.

(2) Consistency: means that the integrity constraints of the database are not destroyed before the transaction starts and after the transaction ends.

a. When the transaction is completed, the data must be in a consistent state.

b. Before the transaction starts, the data stored in the database is in a consistent state.

c. In ongoing transactions, data may be in an inconsistent state.

d. When the transaction completes successfully, the data must return to a known consistent state again.

(3) Isolation: When multiple transactions operate the same data at the same time, in a concurrent environment, each transaction can use its own independent complete data area. All concurrent transactions that modify data are isolated from each other, indicating that a transaction must be independent and that it should not depend on or affect other transactions in any way. A transaction that modifies data can access the data before another transaction that uses the same data begins, or after another transaction that uses the same data ends.

(4) Persistence: After the transaction is completed, the changes made to the database by the transaction are permanently stored in the database and will not be rolled back.

a, means that regardless of whether the system fails, the results of transaction processing are permanent.

b. Once a transaction is committed, the effects of the transaction will be permanently retained in the database.

Summary: In transaction management, atomicity is the foundation, isolation is the means, consistency is the purpose, and durability is the result.

3. The mutual influence between things

(1) Dirty reading: One transaction reads uncommitted data of another transaction, and this data may be rolled back.

When two identical queries are executed continuously in a transaction, but different results are obtained, this situation is called non-repeatable read. This is caused by the commit of modifications by other transactions in the system at query time.

Restatement: Phantom reading refers to when a transaction modifies certain data rows in a table, but another transaction inserts several new rows of data at the same time, causing the first transaction to find several more rows when querying. row data. At the same time, another transaction modified the table and inserted a new row of data. Users who operated on the previous transaction will be surprised to find that there are still unmodified data rows in the table, as if they were hallucinating.

(4). Lost update: Two transactions read the same record at the same time. A modifies the record first, and B also modifies the record (B does not know that A has modified it). After B submits the data, B's modification results are overwritten. The modification result of A.

2. Mysql and transaction isolation level

(1), read uncommitted: read uncommitted data, do not solve dirty reads

(2), read committed: Reading submitted data can solve dirty reads

(3), repeatable read: Rereading can solve dirty reads and non-repeatable reads------------- Mysql defaults to

(4), serializable: serialization, which can solve dirty reads, non-repeatable reads and virtual reads---------------- equivalent to a lock table Note: The default transaction processing level of mysql is repeatable read, while Oracle and SQL Server are read committed

1. Query global transaction isolation level

show global variables like '%isolation%';
或
select @@global.tx_isolation;

2. Query Session transaction isolation level

show session variables like '%isolation%';
SELECT @@session.tx_isolation; 
SELECT @@tx_isolation;

3. Set global transaction isolation level

set global transaction isolation level read committed;
show global variables like '%isolation%';

4. Set session transaction isolation level

set session transaction isolation level read committed;
show session variables like '%isolation%';

三、事务控制语句

1、相关语句

begin;               开启事务
commit;              提交事务，使已对数据库进行的所有修改变为永久性的
rollback;            回滚事务，撤销正在进行的所有未提交的修改
savepoint s1;        建立回滚点，s1为回滚点名称，一个事务中可以有多个
rollback to s1;      回滚到s1回滚点

2、案例

①、创建表

create database school;
use school;
create table Fmoney(
id int(10) primary key not null,  
name varchar(20),  
money decimal(5,2));

insert into Fmoney values ('1','srs1','100');
insert into Fmoney values ('2','srs2','200');
select * from Fmoney;

②、测试提交事务

begin;
update Fmoney set money= money - 100 where name='srs2';
commit;
quit

mysql -u root -p
use school;
select * from Fmoney;

③、测试回滚事务

begin;
update Fmoney set money= money + 100 where name='srs2';
select * from Fmoney;
rollback;

select * from Fmoney;

④、测试多点回滚

begin;
update Fmoney set money= money + 100 where name='srs2';
select * from Fmoney;
savepoint a;
update Fmoney set money= money + 100 where name='srs1';
select * from Fmoney;
savepoint b;
insert into Fmoney values ('3','srs3','300');
select * from Fmoney;

rollback to b;
select * from Fmoney;

3、使用 set 设置控制事务

SET AUTOCOMMIT=0;                        #禁止自动提交
SET AUTOCOMMIT=1;                        #开启自动提交，Mysql默认为1
SHOW VARIABLES LIKE 'AUTOCOMMIT';        #查看Mysql中的AUTOCOMMIT值

如果没有开启自动提交，当前会话连接的mysql的所有操作都会当成一个事务直到你输入rollback|commit;当前事务才算结束。当前事务结束前新的mysql连接时无法读取到任何当前会话的操作结果。
如果开起了自动提交，mysql会把每个sql语句当成一个事务，然后自动的commit。
当然无论开启与否，begin; commit|rollback; 都是独立的事务。

四、MySQL 存储引擎

1、存储引擎概念介绍

（1）MySQL中的数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制、索引技巧、锁定水平，并最终提供不同的功能和能力，这些不同的技术以及配套的功能在MySQL中称为存储引擎。

（2）存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式

（3）MySQL 常用的存储引擎有： a、MylSAM b、InnoDB

（4）MySQL数据库中的组件，负责执行实际的数据I/O操作

（5）MySQL系统中，存储引擎处于文件系统之.上，在数据保存到数据文件之前会传输到存储引擎，之后按照各个存储引擎的存储格式进行存储。

2、查看系统支持的存储引擎

show engines;

3、查看表使用的存储引擎

（1）方法一：直接查看
show table status from 库名 where name='表名'\G;
例：
show table status from school where name='class'\G;

（2）方法二：进入数据库查看
use 库名;
show create table 表名\G;

例：
use school;
show create table class\G;

4、修改存储引擎

（1） 方法一：通过 alter table 修改
use 库名;
alter table 表名 engine=MyISAM;

例：
use school;
alter table class engine=MYISAM;

（2）方法二：通过修改 /etc/my.cnf 配置文件，指定默认存储引擎并重启服务
注意：此方法只对修改了配置文件并重启mysql服务后新创建的表有效，已经存在的表不会有变更。
vim /etc/my.cnf
......
[mysqld]
......
default-storage-engine=INNODB

systemctl restart mysql.service


（3）方法三：通过 create table 创建表时指定存储引擎
use 库名;
create table 表名(字段1 数据类型,...) engine=MyISAM;

例：
mysql -u root -p
use school;
create table test7(id int(10) not null,name varchar(20) not null) engine=MyISAM;

The above is the detailed content of Mysql transaction and storage engine instance analysis. For more information, please follow other related articles on the PHP Chinese website!