一、什么是事務(wù)？

事務(wù)就是把一組 SQL 打包成一個不可分割的整體：要么全部成功，要么全部失敗，允許只執(zhí)行一半。

舉個例子：銀行轉(zhuǎn)賬

張三給李四轉(zhuǎn) 100 元需要兩步：

• 張三余額 -100
• 李四余額 +100

如果第一步成功、第二步失敗，錢就“憑空消失”了。事務(wù)就是用來杜絕這種災(zāi)難的。

二、ACID 四大特性

事務(wù)之所以可靠，全靠 ACID 四大特性保駕護航：

1. Atomicity（原子性）

• 要么全成功，要么全回滾
• 中途出錯、宕機、異常，都像沒執(zhí)行過一樣
• 對應(yīng)操作：ROLLBACK

2. Consistency（一致性）

• 事務(wù)前后，數(shù)據(jù)庫完整性約束不被破壞
• 例如轉(zhuǎn)賬前后總金額不變、數(shù)據(jù)合法、約束有效

這是業(yè)務(wù)最終追求的目標(biāo)

3. Isolation（隔離性）

• 多個事務(wù)同時跑，互相不干擾
• 防止并發(fā)下的數(shù)據(jù)錯亂：臟讀、不可重復(fù)讀、幻讀
• 通過隔離級別控制安全與性能

4. Durability（持久性）

• 事務(wù)一旦提交，修改永久落盤
• 就算數(shù)據(jù)庫崩潰、重啟，數(shù)據(jù)也不會丟

原子性保證操作不半途而廢，一致性保證結(jié)果正確，隔離性保證并發(fā)不亂，持久性保證數(shù)據(jù)不丟。

三、為什么要使用事務(wù)

事物具備的ACID特性，就是使用事務(wù)的原因，在日常的業(yè)務(wù)場景中有大量的需求要用事務(wù)來保證。

支持事物的數(shù)據(jù)庫能夠簡化編程模型，不需要去考慮各種而樣的錢砸錯誤和并發(fā)問題，在使用事務(wù)的過程中，要么提交，成功修改，就可以安全的保存，要么回滾，恢復(fù)到事務(wù)之初。不需要考慮網(wǎng)絡(luò)異常，服務(wù)器宕機等其他因素。

我們經(jīng)常接觸的事務(wù)本質(zhì)上是數(shù)據(jù)庫對ACID模型的一個實現(xiàn)，是為應(yīng)用層服務(wù)的。

四、MySQL 事務(wù)應(yīng)用

MySQL 里只有 InnoDB 支持事務(wù)，MyISAM 不支持

show engines;

可以查看MySQL中支持的存儲引擎

transactions顯示yes，表明InnoDB支持事務(wù)

1. 基本語法

-- 開啟事務(wù)
start transaction;  -- 或 begin;

-- 執(zhí)行 SQL：insert/update/delete...

-- 提交：永久生效
commit;

-- 回滾：全部撤銷
rollback;

• 開啟一個事務(wù)之后，sql語句就包含在事務(wù)之中，這些sql語句都具有ACID特性。
• 提交后就會落盤，不允許回滾，但是可以再修改。
• 無論提交還是回滾事務(wù)都會關(guān)閉。

2. 轉(zhuǎn)賬案例

建表：

 create table account(
 id bigint primary key auto_increment,
 name varchar(20),
 balance decimal(5,2)
 );

insert account values
(1,"張三",1000.00),
(2,"李四",1000.00);

① 事務(wù)提交：數(shù)據(jù)永久修改

start transaction;

update account set balance = balance + 100 where name = "張三";
update account set balance = balance - 100 where name = "李四";

commite;

執(zhí)行后：張三 1100，李四 900，永久生效。

② 事務(wù)回滾：數(shù)據(jù)回到最初

start transaction;

update account set balance = balance + 100 where name = "張三";
update account set balance = balance - 100 where name = "李四";

rollback;

執(zhí)行后：數(shù)據(jù)完全沒變，回到最初狀態(tài)。

3. 保存點 savepoint

事務(wù)太長，可以設(shè)置“檢查點”，回滾到指定位置：

start transaction;

-- 操作1
savepoint sp1;

-- 操作2
savepoint sp2;

-- 回滾到 sp1
rollback to sp1;

-- 回滾全部,關(guān)閉事務(wù)
rollback;

適合調(diào)試、批量操作、分步撤銷。

4. 自動提交 / 手動提交

MySQL 默認 自動提交（每條 SQL 自成事務(wù)）：

-- 查看
show variables like 'autocommit';

-- 關(guān)閉自動提交（手動控制）
set autocommit = 0;

-- 開啟自動提交
set autocommit = 0;

顯示on，表示自動提交已打開，可以手動設(shè)置為關(guān)閉。

注意：

1.只要使用 begin / start transaction開啟事務(wù)，就必須使用 commit / rollback 才能結(jié)束事務(wù)。

2.手動提交模式下，不會顯示開啟事務(wù)，但是需要手動 commit / rollback來結(jié)束事務(wù)

3.自動提交打開時，一個事務(wù)只包含一個DML語句

DML語句是指update，insert，delete等數(shù)據(jù)操作語言

4.DDL 語句會強制提交事務(wù)

DDL語句是對庫、表、結(jié)構(gòu)做定義、修改、刪除的數(shù)據(jù)定義語言

如果在事務(wù)里執(zhí)行 create database / create table / alter table/ drop table 這類 DDL，事務(wù)會被自動提交，后面的 SQL 就不在這個事務(wù)里了，也無法回滾前面的操作。

5.設(shè)置手動提交，如果重啟電腦會恢復(fù)成自動提交，如果想要永久設(shè)置成手動提交，可以在配置文件中修改

五、事務(wù)的隔離性與隔離級別

5.1 什么是隔離性

MySQL服務(wù)可以同時被多個客戶端訪問，每個客戶端執(zhí)行的DML語句是以事務(wù)為單位，那么不同的客戶端都同一張表的同一條數(shù)據(jù)進行修改的時候可能出現(xiàn)相互影響的情況。

為了保證不同的事務(wù)之間執(zhí)行過程不受影響，那么事物之間需要相互隔離，這種特性就是隔離性

5.2 隔離級別

事物的隔離級別描述了事務(wù)之間的隔離程度。

不同的隔離級別在性能和安全方面做了取舍，有的隔離界別注重并發(fā)性，有的注重安全性，有的則是安全與并發(fā)適中。

在MySQL的InnoDB引擎中事務(wù)的隔離界別有四種，安全性逐漸增強，性能逐漸減弱：

• read uncommitted，讀未提交
• read committed，讀已提交
• repeatable read，可重復(fù)讀（默認）
• serializable，串行化

5.3 不同隔離級別存在的問題

多個事務(wù)同時跑，會出現(xiàn) 3 種經(jīng)典數(shù)據(jù)錯亂：

1. 臟讀 Dirty Read

事務(wù)A讀到事務(wù)B沒提交的數(shù)據(jù)，如果事務(wù)B回滾，事務(wù)A讀到的就是“假數(shù)據(jù)”。

2. 不可重復(fù)讀 Non-Repeatable Read

在同一個事務(wù)A內(nèi)，多次讀取同一行數(shù)據(jù)，期間數(shù)據(jù)被另一個已提交的事務(wù)B修改，導(dǎo)致多次讀取結(jié)果不一致。這種現(xiàn)象就是不可重復(fù)讀

3. 幻讀 Phantom Read

同一個事務(wù)內(nèi)，兩次范圍查詢條數(shù)不一樣。InnoDB 用 Next-Key 鎖，鎖住了目標(biāo)行和之前的“間隙”，解除了部分幻讀問題

serializable 串行化解決了所有數(shù)據(jù)安全問題，所有事務(wù)一個挨一個執(zhí)行，同時效率也是最低的。

5.4 查看 / 設(shè)置 隔離界別

-- 全局作用域
select @@global.transaction_isolation;

-- 局部作用域
select @@session.transaction_isolation

-- 設(shè)置會話級別
set session transaction isolation level read committed;

六、用戶權(quán)限管理

在實際開發(fā)中，數(shù)據(jù)庫絕對不能所有人都用 root 賬號亂操作。用戶隔離 + 權(quán)限控制是安全的第一道防線。

6.1 為什么要做用戶與權(quán)限管理？

• root 是超級管理員，權(quán)限太大，不安全
• 一個項目對應(yīng)一個庫、一個專用用戶
• 有的用戶只能讀，有的能讀寫，互不干擾
• 防止誤刪庫、誤改數(shù)據(jù)、越權(quán)訪問

6.2 用戶基礎(chǔ)操作

MySQL 的用戶信息存在 mysql 庫的 user 表中。

1. 查看所有用戶

use mysql;
select host, user, authentication_string from user;

• host：允許登錄的地址（白名單）
• user：用戶名
• authentication_string：加密密碼

2. 創(chuàng)建用戶

CREATE USER '用戶名'@'允許登錄地址' IDENTIFIED BY '密碼';

常用示例：

-- 只能本機登錄
CREATE USER 'test'@'localhost' IDENTIFIED BY '123456';

-- 允許 192.168.1.x 網(wǎng)段登錄
CREATE USER 'test'@'192.168.1.1/24' IDENTIFIED BY '123456';

注意：

• localhost：只能本機連
• %：任何地址都能連（生產(chǎn)環(huán)境禁止用）

3. 修改密碼

-- root 給別人改密
ALTER USER 'bit'@'localhost' IDENTIFIED BY '987654';

-- 自己改自己密碼
SET PASSWORD = '111111';

4. 刪除用戶

DROP USER 'bit1'@'192.168.1.1/24';

6.3 權(quán)限管理：授權(quán)與回收

新建用戶默認沒有任何權(quán)限，只能看見系統(tǒng)庫，必須手動授權(quán)。

1. 常用權(quán)限

• SELECT：查詢
• INSERT：插入
• UPDATE：更新
• DELETE：刪除
• ALL [PRIVILEGES]：所有權(quán)限
• USAGE：沒權(quán)限（默認）

2. 授權(quán)語法

GRANT 權(quán)限 ON 庫.表 TO '用戶'@'地址';

常用示例：

-- 給 test 用戶授權(quán) java01 庫所有表的 查詢權(quán)限
GRANT SELECT ON java01.* TO 'test'@'localhost';

-- 給 test 用戶授權(quán) java01 庫所有權(quán)限（增刪改查）
GRANT ALL ON java01.* TO 'test'@'localhost';

授權(quán)后需要刷新：

FLUSH PRIVILEGES;
``
### 3. 查看用戶權(quán)限
```sql
SHOW GRANTS FOR 'test'@'localhost';

4. 回收權(quán)限

-- 回收所有權(quán)限
REVOKE ALL ON *.* FROM 'test'@'localhost';

FLUSH PRIVILEGES;

6.4 標(biāo)準流程

-- 1. 創(chuàng)建專用用戶（本機登錄）
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'App@123456';

-- 2. 授權(quán)指定庫的所有權(quán)限
GRANT ALL ON mydb.* TO 'appuser'@'localhost';

-- 3. 刷新權(quán)限
FLUSH PRIVILEGES;

-- 4. 查看權(quán)限
SHOW GRANTS FOR 'appuser'@'localhost';

-- 5. 回收（不需要時）
REVOKE ALL ON *.* FROM 'appuser'@'localhost';
FLUSH PRIVILEGES;

-- 6. 刪除用戶
DROP USER 'appuser'@'localhost';

總結(jié)

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論