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

標籤：type   actions   set   glob   ase   commit   持久   session   release   

一、並發存取控制

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

鎖分為表級鎖和行級鎖，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、事務的隔離等級

1. READ UNCOMMITTED 其他事務可以看到未提交的髒資料，產生髒讀
2. READ COMMITTED 提交後其他事務可以看到修改後的資料，每次讀取的資料可能不一致，不可重複讀取
3. REPEATABLE READ 可重複讀，每次看到的資料都一致，資料被修改後看不到最新資料，會產生幻讀（預設設定）
4. 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系列之十