深入理解mysql事務與儲存引擎

2022-04-12 19:00:44

一、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)、髒讀：一個事務讀取了另一個事務未提交的資料，而這個資料是有可能回滾的。

(2)、不可重複讀：一個事務內兩個相同的查詢卻返回了不同資料。這是由於查詢時系統中其他事務修改的提交而引起的。

(3)、幻讀：一個事務對一個表中的資料進行了修改，這種修改涉及到表中的全部資料行。同時，另一個事務也修改這個表中的資料，這種修改是向表中插入一行新資料。那麼，操作前一個事務的使用者會發現表中還有沒有修改的資料行，就好象發生了幻覺一樣。

(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 classG;

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;