mysql事務與儲存引擎實例分析

一、MySQL交易

1、交易的概念

（1）交易是一種機制、一個操作序列，包含了一組資料庫操作指令，並且把所有的指令作為一個整體一起向系統提交或撤銷操作請求，即這組資料庫命令要么都執行，要么都不執行。

（2）事務是一個不可分割的工作邏輯單元，在資料庫系統上執行並發操作時，事務是最小的控制單元。

（3）多用戶同時操作的資料庫系統的場景，如銀行、保險公司和證券交易系統等等，適用於事務處理。 （4）事務透過事務的整體性以確保資料的一致性。

2、事務的ACID 特點

註：ACID，是指在可靠資料庫管理系統(DBMS) 中，事務(transaction) 應該具有的四個特性：原子性(Atomicity) 、一致性(Consistency )、隔離性(Isolation) 、持久性(Durability) 。這是可靠資料庫所應具備的幾個特性。

（1）事務具有原子性，也就是說，事務中的操作要麼全部執行，要麼全部不執行，不可分割。 a、事務是一個完整的操作，事務的各元素是不可分的。

b、事務中的所有元素必須作為一個整體提交或回滾。

c、如果交易中的任何元素失敗，則整個交易將會失敗。

（2）一致性：指在交易開始前和交易結束後，資料庫的完整性限制並沒有被破壞。

a、當交易完成時，資料必須處於一致狀態。

b、在交易開始前，資料庫中儲存的資料處於一致狀態。

c、在正在進行的事務中，資料可能處於不一致的狀態。

d、當交易成功完成時，資料必須再次回到已知的一致狀態。

（3）隔離性：當多個交易同時操作相同的資料時，在並發環境中，每個交易都能夠使用它自己獨立的完整資料區域。所有對資料進行修改的並發事務是彼此隔離的，表明事務必須是獨立的，它不應以任何方式依賴或影響其他事務。修改數據的事務可在另一個使用相同數據的事務開始之前存取這些數據，或在另一-個使用相同數據的事務結束之後存取這些數據。

（4）持久性：在交易完成以後，該交易對資料庫所做的更改便持久的保存在資料庫之中，並不會被回滾。

a、指不管系統是否發生故障，事務處理的結果都是永久的。

b、一旦交易被提交，交易的效果會永久地保留在資料庫中。

總結：在事務管理中，原子性是基礎，隔離性是手段，一致性是目的，持久性是結果。

3、事物之間的互相影響

(1)、髒讀：一個事務讀取了另一個事務未提交的數據，而這個數據是有可能回滾的。

在一個事務中連續執行兩個相同的查詢，卻得到了不同的結果，這種情況稱為不可重複讀取。這是由於查詢時系統中其他事務修改的提交而引起的。

重新敘述：幻讀是指當一個事務修改了一個表中的某些數據行，但是另一個事務同時插入了幾行新數據，導致第一個事務查詢時發現多了幾行數據。同時有另一個事務對該表進行了修改，並插入了一行新資料。操作前一個事務的使用者會驚訝地發現表中仍有未修改的資料行，就像出現了幻覺一樣。

(4)、遺失更新：兩個交易同時讀取同一筆記錄，A先修改記錄，B也修改記錄(B不知道A修改過)，B提交資料後B的修改結果覆蓋了A的修改結果。

二、Mysql及交易隔離等級

(1) 、read uncommitted：讀取尚未提交的數據，不解決髒讀

(2)、read committed：讀取己經提交的數據，可以解決髒讀

(3)、 repeatable read：重讀讀取，可以解決髒讀和不可重複讀------------- mysql預設

(4)、 serializable：串列化，可以解決髒讀不可重複讀取和虛讀----------------相當於鎖定表註： mysql 預設的交易處理等級是repeatable read，而Oracle 和SQL Server 是read committed

1、查詢全域事務隔離等級

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

2、查詢會話事務隔離級別

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

3、設定全域事務隔離級別

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

4、設定會話事務隔離級別

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;

以上是mysql事務與儲存引擎實例分析的詳細內容。更多資訊請關注PHP中文網其他相關文章！