資料庫MySQL/mariadb知識點——事務Transactions

標籤：ola   變數   添加   ble   zab   end   teacher   使用   The   

案例：銀行的資料庫裡面儲存著使用者的賬戶資訊表，當使用者 A 想使用者 B 轉賬的時候，正常情況下，A 賬戶的餘額減少，B 賬戶的餘額增加；但是由於某種原因（例如突然斷電），當 A 賬戶的餘額減少之後，B 賬戶的餘額並沒有增加，這就造成了資料庫資料的安全隱患。解決方案：當 A 賬戶的餘額減少之後，不要立即修改資料表，而是在確認 B 賬戶的餘額增加之後，同時修改資料表。

事務Transactions

?通過前面的案例及解決方案，我們就引出了一個全新的概念，那就是：事務，即一系列將要發生或正在發生的連續操作；

而事務安全，是一種保護連續操作同時實現（完成）的機制。事務安全的意義就是，保證資料操作的完整性。

遵循ACID原則：

• A：atomicity原子性；整個事務中的所有操作要麼全部成功執行，要麼全部失敗後復原
• C：consistency一致性；資料庫總是從一個一致性狀態轉換為另一個一致性狀態
• I：Isolation隔離性；一個事務所做出的操作在提交之前，是不能為其它事務所見；隔離有多種隔離等級，實現並發
• D：durability持久性；一旦事務提交，其所做的修改會永久儲存於資料庫中

生命週期

明確交易：明確的規定事務的開始

隱含交易：預設為隱含交易，每執行完一句語句後直接提交

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 |+-------+------+-----+--------+---------+-----------+

隔離等級

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

查看tx_isolation

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‘;

死結

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

在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;

並發存取控制

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

鎖分為表級鎖和行級鎖，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] 查詢時加寫或讀鎖

　　

資料庫MySQL/mariadb知識點——事務Transactions