MySQL系列之十 MySQL事務隔離實現並發控制

Posted on by

一、並發訪問控制

實現的並發訪問的控制技術是基於鎖；

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

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

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

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

顯式鎖的使用方法：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原子性；整個事務中的所有操作要麼全部成功執行，要麼全部失敗後回滾
  • C：consistency一致性；數據庫總是從一個一致性狀態轉換為另一個一致性狀態
  • I：Isolation隔離性；一個事務所做出的操作在提交之前，是不能為其它事務所見；隔離有多種隔離級別，實現並發
  • D：durability持久性；一旦事務提交，其所做的修改會永久保存於數據庫中

2、事務的生命周期

顯式事務：明確的規定事務的開始

隱式事務：默認為隱式事務，每執行完一句語句後直接提交

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);  #查看一下，可以看到剛剛插入的數據
+-------+-------+-----+--------+---------+-----------+
| StuID | Name  | Age | Gender | ClassID | TeacherID |
+-------+-------+-----+--------+---------+-----------+
|    26 | Tom   |  22 | M      |    NULL |      NULL |
|    27 | Maria |  12 | F      |    NULL |      NULL |
+-------+-------+-----+--------+---------+-----------+
MariaDB [school]> ROLLBACK TO sp26;  #撤銷到sp26標簽之前的狀態
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);  #最終的數據
+-------+------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+------+-----+--------+---------+-----------+
|    26 | Tom  |  22 | M      |    NULL |      NULL |
+-------+------+-----+--------+---------+-----------+

3、事務的隔離級別

  • READ UNCOMMITTED 其他事務可以看到未提交的臟數據，產生臟讀
  • READ COMMITTED 提交後其他事務可以看到修改後的數據，每次讀取的數據可能不一致，不可重復讀
  • REPEATABLE READ 可重復讀，每次看到的數據都一致，數據被修改後看不到最新數據，會產生幻讀（默認設置）
  • SETIALIZABILE 未提交的讀事務阻塞修改事務，串行執行，並發性差

MVCC: 多版本並發控制，和事務級別相關

修改事務隔離級別：服務器變量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';

4、死鎖

​兩個或多個事務在同一資源相互占用，並請求鎖定對方占用的資源的狀態會發生死鎖

在A事務修改t1表的第3行，B事務修改t2表的第2行時；這時A事務去修改t2表的第2行，這時就把A事務阻塞瞭，然後B事務有剛剛好去修改t1表的第3行，這時B事務也被阻塞瞭，這時就產生瞭死鎖。

倆個事務同時去更改對方的修改的表，互相阻塞；系統會發現死鎖，會自動犧牲一個代價小的事務來解開死鎖。

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

查看進程列表：MariaDB [school]> SHOW PROCESSLIST;

殺死進程：MariaDB [school]> KILL 5;

到此這篇關於MySQL系列之十 MySQL事務隔離實現並發控制的文章就介紹到這瞭,更多相關mysql並發控制內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

到此這篇關於MySQL系列之十 MySQL事務隔離實現並發控制的文章就介紹到這瞭,更多相關mysql並發控制內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：