MySQL系列之十 MySQL事務隔離實現(xiàn)并發(fā)控制

更新時間：2021年07月02日 16:13:40 作者：生生不息.連綿不絕

今天的內(nèi)容就和大家聊一聊MySQL數(shù)據(jù)庫中關于MySQL事務隔離實現(xiàn)并發(fā)控制的問題，主要是基于鎖實現(xiàn)控制技術

一、并發(fā)訪問控制

實現(xiàn)的并發(fā)訪問的控制技術是基于鎖；

鎖分為表級鎖和行級鎖，MyISAM存儲引擎不支持行級鎖；InnoDB支持表級鎖和行級鎖；

鎖的分類有讀鎖和寫鎖，讀鎖也被稱為共享鎖，加讀鎖的時候其他的人可以讀；寫鎖也稱為獨占鎖或排它鎖，一個寫鎖會阻塞其他讀操作和寫操作；

鎖還分為隱式鎖和顯式鎖，隱式鎖由存儲引擎自行管理，顯式鎖是用戶手動添加鎖；

鎖策略：在鎖粒度及數(shù)據(jù)安全性尋求的平衡機制。

顯式鎖的使用方法：LOCK TABLES tbl_name READ|WRITE

MariaDB [school]> LOCK TABLES students READ;  #加讀鎖

MariaDB [school]> UNLOCK TABLES;  #解鎖

讀鎖：任何人都不可寫

寫鎖：自己可以讀寫，但是其他人不可讀寫

FLUSH TABLES tb_name ：關閉正在打開的表（清除查詢緩存），通常在備份前加全局讀鎖

SELECT clause [FOR UPDATE | LOCK IN SHARE MODE] 查詢時加寫或讀鎖

二、事務Transactions

一組原子性的SQL語句，或一個獨立工作單元

1、事務遵循ACID原則：

A：atomicity原子性；整個事務中的所有操作要么全部成功執(zhí)行，要么全部失敗后回滾
C：consistency一致性；數(shù)據(jù)庫總是從一個一致性狀態(tài)轉(zhuǎn)換為另一個一致性狀態(tài)
I：Isolation隔離性；一個事務所做出的操作在提交之前，是不能為其它事務所見；隔離有多種隔離級別，實現(xiàn)并發(fā)
D：durability持久性；一旦事務提交，其所做的修改會永久保存于數(shù)據(jù)庫中

2、事務的生命周期

顯式事務：明確的規(guī)定事務的開始

隱式事務：默認為隱式事務，每執(zhí)行完一句語句后直接提交

autocommit = {OFF|ON} 開啟或關閉自動提交，建議使用顯式請求和提交事務，而不要使用“自動提交”功能

啟動事務：START TRANSACTION;

插入標簽：ROLLBACK TO ##;

撤銷回指定標簽：ROLLBACK TO ##;

全部撤銷：ROLLBACK;

提交事務：COMMIT；

刪除標簽：RELEASE SAVEPOINT;

MariaDB [school]> START TRANSACTION;  #明確指明啟動一個事務
MariaDB [school]> INSERT students(StuID,Name,Age,Gender) VALUES (26,'Tom',22,'M');  #添加一條記錄
MariaDB [school]> SAVEPOINT sp26;  #插入一個標簽
MariaDB [school]> INSERT students(StuID,Name,Age,Gender) VALUES (27,'Maria',12,'F');  #再加入一條記錄
MariaDB [school]> SELECT * FROM students WHERE stuid IN (26,27);  #查看一下，可以看到剛剛插入的數(shù)據(jù)
+-------+-------+-----+--------+---------+-----------+
| StuID | Name  | Age | Gender | ClassID | TeacherID |
+-------+-------+-----+--------+---------+-----------+
|    26 | Tom   |  22 | M      |    NULL |      NULL |
|    27 | Maria |  12 | F      |    NULL |      NULL |
+-------+-------+-----+--------+---------+-----------+
MariaDB [school]> ROLLBACK TO sp26;  #撤銷到sp26標簽之前的狀態(tài)
MariaDB [school]> SELECT * FROM students WHERE stuid IN (26,27);  #查看一下，剛剛maria的信息被撤回了
+-------+------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+------+-----+--------+---------+-----------+
|    26 | Tom  |  22 | M      |    NULL |      NULL |
+-------+------+-----+--------+---------+-----------+
MariaDB [school]> COMMIT;  #提交事務
MariaDB [school]> SELECT * FROM students WHERE stuid IN (26,27);  #最終的數(shù)據(jù)
+-------+------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+------+-----+--------+---------+-----------+
|    26 | Tom  |  22 | M      |    NULL |      NULL |
+-------+------+-----+--------+---------+-----------+

3、事務的隔離級別

READ UNCOMMITTED 其他事務可以看到未提交的臟數(shù)據(jù)，產(chǎn)生臟讀
READ COMMITTED 提交后其他事務可以看到修改后的數(shù)據(jù)，每次讀取的數(shù)據(jù)可能不一致，不可重復讀
REPEATABLE READ 可重復讀，每次看到的數(shù)據(jù)都一致，數(shù)據(jù)被修改后看不到最新數(shù)據(jù)，會產(chǎn)生幻讀（默認設置）
SETIALIZABILE 未提交的讀事務阻塞修改事務，串行執(zhí)行，并發(fā)性差

MVCC: 多版本并發(fā)控制，和事務級別相關

修改事務隔離級別：服務器變量tx_isolation指定，默認為REPEATABLE-READ，可在GLOBAL和SESSION級進行設置

tx_isolation

Description: The transaction isolation level. See also SET TRANSACTION ISOLATION LEVEL.
Commandline: --transaction-isolation=name
Scope: Global, Session
Dynamic: Yes
Type: enumeration
Default Value: REPEATABLE-READ
Valid Values: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE

MariaDB [school]> SELECT @@tx_isolation;   #默認為可重復讀級別
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
MariaDB [school]> SET tx_isolation='READ-UNCOMMITTED';
MariaDB [school]> set tx_isolation='READ-COMMITTED';
MariaDB [school]> set tx_isolation='REPEATABLE-READ';
MariaDB [school]> set tx_isolation='SERIALIZABLE';