MySQL存儲引擎、事務日志并發訪問以及隔離級別 · php開發筆記

#### MySQL存儲引擎 * MySQL是插件式存儲存儲引擎，支持多種存儲引擎 * 常見的存儲引擎有：MyISAM, Aria, InnoDB, MRG\_MYISAM, CSV, BLACKHOLE, MEMORY, PERFORMANCE\_SCHEMA, ARCHIVE, FEDERATED ~~~objectivec mysql> SHOW ENGINES; #查看支持多少種存儲引擎 *************************** 1. row *************************** Engine: FEDERATED #引擎名稱 Support: NO #是否支持 Comment: Federated MySQL storage engine #簡要注釋 Transactions: NULL #是否支持事務功能 XA: NULL #是否支持分布式事務功能 Savepoints: NULL # 是否支持保存點功能 *************************** 2. row *************************** ~~~ ... * 存儲引擎是表級別的概念，因此又稱為“表類型”；不建議在同一個庫中的表上使用不同的ENGINE； ~~~css CREATE TABLE ... ENGINE[=]STORAGE_ENGINE_NAME ... SHOW TABLE STATUS ~~~ #### 1\. InnoDB存儲引擎 InnoDB存儲引擎，把數據存儲在一個文件中，是個黑盒，根varnish是一樣，varnish中的數據緩存都存儲在一個文件中，但其內部完全有自組織有序的結果從外部看來就是一個文件，內部可以有自己的元數據，數據是構建在文件系統之上的文件系統；而InnoDB也是這樣，默認情況下可以將n張表放在一個表空間里，在外部看來就是一個表，其內部存放了n張表甚至存放了n張表的索引；不過把n張表的索引放在同一表中很難實現高級功能；比如像單表導入導出等；因此，這就是為什么在安裝MariaDB時，都要求使用innodb\_file\_per\_table這表示每張表使用一個表空間； InnoDB的表空間為了能跨文件系統（分區），InnoDB的表空間背后所對應的文件可以不止一個，而且還可以自動增長；表空間剛創建時，就占用固定大小的空間，如果再許愿空間時，就又占用固定大小的空間，用不用都占用；可理解為是基于步進的方式進行劃分；InnoDB將所有的表和索引放在一個表空間中；如果所有表和數據都存儲在一個表空間中時，表空間文件是存儲在datadir定義的目錄中的；如果每張表單獨存儲在一個表空間中，則表空間文件是放置在數據庫目錄下，而不是數據目錄下；例如有一個數據庫叫mydb就相當于在數據庫目錄下創建一個mydb的目錄而已；當然數據庫目錄有可能在數據目錄下的子目錄； ~~~objectivec MariaDB [(none)]> SHOW ENGINES\G; ... *************************** 6. row *************************** Engine: InnoDB Support: DEFAULT Comment: Percona-XtraDB, Supports transactions, row-level locking, and foreign keys Transactions: YES XA: YES Savepoints: YES ~~~ * 數據存儲于“表空間（table space）”中，是個黑盒：有兩種管理方式：（1）所有數據庫中的所有類型為InnoDB的表的數據和索引存儲于同一個表空間中； * 表空間文件：存儲在datadir定義的目錄下； * 表現出的文件為：ibddata1，ibddata2，... （2）每個表單獨使用一個表空間存儲表的數據和索引；使用`innodb_file_per_table=ON`開啟； * 數據文件（存儲數據和索引）名：tb1\_name.frm * 每張表由2個文件組成：tb1\_name.ldb和tb1\_name.frm * 文件存儲于數據庫目錄中注：基于第一種方式管理時，所有數據庫的InnoDB表將使用同一個表空間，例如有10個庫，每個庫中有100張表，都放在同一個表空間中，于后期管理無益。 * InnoDB存儲引擎是事務性存儲引擎，適合處理大量的短期任務； * 基于MVCC（Mutli Version Concurrenvy Contro，多版本并發控制）支持高并發，支持所有的四個隔離級別；默認支持間隙鎖防止幻讀； * 使用聚簇索引（一般是主鍵索引）； * 一張表的聚簇索引只能有一個；索引在哪數據就在哪里 * 支持自適應hash索引； * 鎖粒度：行級鎖，間隙鎖；注意：級別越高事務發生沖突性越小，事務安全性越高但并發性能就越低； > 注：MVCC，multi version concurrency control：多版本并發訪問控制，它就是類似于lvm的快照功能；也就意味著，當啟動n個事務時，每一個事務，都針對于mysql當中的InnoDB存儲引擎現有數據做一個快照；每一個快照，通過快照過去訪問時間點是一致的，所以現在要啟動一個事務，就對InnoDB數據集做個快照，就在快照基礎上做操作；因此有50個事務為了彼此間不受影響，每個事務啟動一次做一個快照。 > 但是，這病不意味著就沒有沖突，想象以下，如果第一個快照和第二個也快照了，兩個操作同一個數據集，一個刪了一行，而第二個事務把這個行改了，此時就會出現問題，所以，必須有隔離機制來實現所謂的事務隔離； > 而事務隔離中最核心組件就是鎖；這么一來事務也沒法并發了，既然鎖了，看上去事務同時啟動，但是還得等待一個個完成；所以，隔離級別至關重要了； * * * > 另：percona公司對mysql做了二次開發，服務器版本為percona-server，把InnoDB改進為XtraDB存儲引擎，mariadb使用了XtraDB引擎，但是名稱依然叫InnoDB ##### 總結InnoDB引擎的特性： * 存儲數據：表空間 * 并發：MVCC，間隙鎖 * 索引：聚集索引、輔助索引 * 性能：預讀操作、自適應hash索引、插入操作緩存區 * 備份：支持熱備 `SHOW ENGINE INNODB STATUS;` #### 2\. MyISAM存儲引擎 MyISAM內部的復雜機制很少，特別適應于讀多寫少的應用；但是，mysql自己的表，內部的元數據的庫還是使用的MyISAM存儲引擎； MyISAM存儲引擎最大特性是支持全文索引，全文索引指的是全文中的每一個關鍵字都可以被搜索； MyISAM在崩潰后無法安全恢復，有可能會導致某些數據丟失，比如要插入一行數據，剛插入一般數據庫崩潰，只能把剛插入的半行刪了，保證數據是一致性的；解決辦法是使用MyISAM表修復工具，會對全表掃描，一行行掃描，看哪一行中的數據不是完整的給它刪了，所以這就是崩潰后的恢復，如果表要大的話，恢復時間會非常長；索引說可以接受較長時間的修復操作才能使用MyISAM;這個較長時間還是指的較小的表，如果表答可能修復一天都有可能。 MariaDB雖然支持MyISAM存儲引擎但是，默認對于MyISAM而言使用的是Aria存儲引擎，其實就是MyISAM的增強版，支持崩潰后的安全恢復； MyISAM存儲引擎的文件，表和索引是獨立存放的；索引可看出來使用的是非聚集索引；因此，每一個索引的葉子節點，都是個指針，指向了真正數據所在的位置；這就類似于書中的目錄和后面正文的關系；對于InnoDB，每張表使用兩個文件一個是用來存儲數據和索引的.ibd文件，一個是用來存儲表格式的.frm格式的文件；而對于MyISAM，每張表有三個文件且都放在數據庫目錄下而不是datadir=指定的目錄下，也就是說的datadir指定的目錄下的子目錄里； * tbl\_name.frm：表格式定義； * tbl\_name.MYD：數據文件； * tbl\_name.MYI：索引文件； ~~~objectivec MariaDB [(none)]> SHOW ENGINES\G; *************************** 5. row *************************** Engine: MyISAM Support: YES Comment: MyISAM storage engine Transactions: NO XA: NO Savepoints: NO ~~~ ##### 總結MyISAM存儲引擎特性： * 加鎖好并發：支持表級鎖 * 修復：只能手動或自動修復（使用工具）、但可能會丟失數據；并不是安全恢復 * 索引：使用的是非聚集索引 * 支持延遲更新索引：只是為了提高性能 * 支持表壓縮：壓縮后表數據不能修改 ##### 行格式： `{DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}` * `dynamic:` 動態行 * `fixed`:固定行 * `compressed：`壓縮行 * `compact：`緊致行 * `redundent：`冗余行態還是固定行取決于表中字段的類型，例如字段的類型是varchar類型長度是可變的，如果某一字段使用varchar類型，每行可以不同的長度；行長度不一樣管理很麻煩，性能比較低；行的長度一樣只需根據距離來判斷下一行在什么位置就可以了； **以上InnoDB和MyISAM是mysql中最為經典的兩種存儲引擎；** ~~~ruby MariaDB [hellodb]> SHOW ENGINES; #查看所有支持的存儲引擎 +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | FEDERATED | YES | FederatedX pluggable storage engine | MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE '%STORAGE%'; #查看默認存儲引擎 +------------------------+--------+ | Variable_name | Value | +------------------------+--------+ | default_storage_engine | InnoDB | | storage_engine | InnoDB | +------------------------+--------+ ~~~ #### 3\. 其他存儲引擎 * CSV：將普通的CSV文件（字段基于逗號分隔）作為MySQL表使用； * 所謂CSV文件：一個文件中也是有很多行組成，每一行有很多字段組成，而字段用逗號分隔，把整個文件當表使用； * CVS文件好處在于可以使用grep、awk、sed工具直接處理，但是由于基于文本存儲的，浮點型數據轉換為文本字符串存儲后會丟失精度 * CVS特別適合用于在多個不同的數據庫之間共享數據時使用 * MRG\_MYISAM：由多個MyISAM表合并成的虛擬表 * 表可以分割成小表，就像zabbix中數據一樣，把表分成多個小標按天存放，一天一張表，要想刪除時，直接刪除某天的表即可；每一天的表其實就是一個表分區table partion * 要查詢多張表是合并即可 * BLACKHOLE：類似于/dev/null，不真正存儲任何數據，只會在特定場景中使用 * 對mysql而言blachole stroage engine相當于linux上的/dev/null，索引某張表的存儲引擎是該類型，這個表存儲的所有數據都不會產生任何IO，因為數據被丟棄了 * 只在特定的場景中使用：比如多級架構執行復制操作時； * MEMORY：基于內存存儲，支持hash索引，表級鎖，常用語臨時表； * PERFORMANCE\_SCHEMA：類似于Linux/proc目錄，用于存儲mysql的性能數據； * 是關于mysql性能各類統計的收集存儲的表，只用來收集mysql運行中的統計數據，做性能評估，或者是有助于優化器優化； * 表中的統計數據只對當前有效，一旦mysql停機，這個表就作廢變為空的，啟動后就會不斷的填入很多收集到的與性能相關的數據 * 此存儲引擎用處在于mysql為了達到一切皆sql表的目的，索引把它內部的統計數據基于sql接口進行輸出了 * ARCHIVE：支持SELECT 和INSERT 操作，支持行級鎖和專用緩存區； * 用于數據永久存儲并且當做數據倉庫使用；不支持修改操作 * FEDERATED：類似于MRG\_MYISAM，不用支出在于MRG\_MYISAM所合并的多張表一定是同一個mysql服務器上的，甚至是同一個數據庫中的多張表；但是FEDERATED能把兩個數據庫服務器上的表聯合在一起使用 * 用于訪問其它的遠程的MySQL Server的代理接口，它通過創建一個到遠程MySQL Server的客戶端連接，并將查詢語句傳輸至遠程服務器執行； * 自己本地沒有表，但可以把這個當做接口，所有發給FEDERATED存儲引擎表的所有語句，都會基于mysql連接發給另一個數據庫服務器上另外一張表，而后完成數據存取；注：FEDERATED也僅支持mariadb；是夸數據庫服務器的角色 #### 4\. MariaDB支持的其他存儲引擎 * OQGraph：支持實體存儲 * Sphinx SE：搜索引擎 * 而mariadb支持sphinxSE存儲引擎，意味著，這個表存儲的所有數據都直接基于sphinxSE接口導入到sphinx存儲里了； * TokuDB：支持海量數據的存儲引擎，支持原生分布式存儲 * Cassandra：由facebook研發，分布式無中心的存儲； * mariadb支持把表存儲Cassandra以后，沒有在mysql本地存儲，而是導入到Cassandra表中； * CONNECT存儲引擎 * SQUENCE存儲引擎 #### MySQL事務 ##### 并發控制：鎖是mysql實現并發訪問控制的重要組件； * 為什么要有鎖機制：任何文件或數據集只要支持并發訪問模型，就必須基于鎖機制對其機制進行控制；對mysql服務器，允許多個線程同時連進來支持做讀寫操作請求，這樣多個查詢語句就有可能只對同一個數據集進行，例如第一個線程對數據集中的某行做修改，第二個線程對同一個數據集中的同一行做查詢，這樣二者同時操作就會帶來混亂；所以，mysql為了能夠實現并發訪問中避免數據讀寫沖突，就必須施加所機制。 * 鎖類型： * 讀鎖：共享鎖，可以共享給其他的讀操作 * 寫鎖：獨占所，其他操作不可讀寫 * 鎖粒度： * 表級鎖：在表級別施加鎖，并發性較低； * 行級鎖：在行級別施加鎖，并發性較高；維持鎖狀態的成本較大； * 頁級鎖（早些時候支持） * 所策略：在鎖粒度及數據安全性之間尋求一種平衡機制； * 存儲引擎：每種存儲引擎都可以自行實現其所策略和鎖粒度 * MySQL Server：表級別，可自行決定，也允許顯示請求； * 根據所本身根據用戶手動施加還是由服務器自動實現，鎖又可以分兩種類型： * 顯式鎖：用火狐手動請求施加的鎖 * 隱式鎖：有存儲引擎（或mysql服務器）自行根據需要施加的鎖；顯示鎖使用： (1) LOCK TABLES 命令：鎖定整張表或多張表用法：LOCK TABLES tbl\_name \[\[AS\] alias\] lock\_type \[,tbl\_name \[\[AS\] alias \] lock\_type\] ... lock\_type : 所類型 READ\[ LOCAL\] | \[LOW\_PRIORITY\] WRITE tbl\_name: 鎖名稱 ~~~css UNLOCK TABLES; 解鎖； (2) FLUSH TABLES 命令：作用在于如果一張表被打開了，需要把內存中的所有數據都清到磁盤上，在把表關閉；用法：FLUSH TABLES tbl_name[,...] [WITH READ LOCK] [FOR UPDATE] WITH READ LOCK: 施加讀鎖 FOR UPDATE: 請求修改，即施加寫鎖 (3) SELECT 語句：鎖定表中的某些行用法：SELECT clause [FOR UPDATE] [WITH READ LOCK] ~~~ * * * ~~~csharp 示例： MariaDB [(none)]> CREATE DATABASE testdb; #創建數據庫 Query OK, 1 row affected (0.01 sec) MariaDB [(none)]> USE testdb Database changed MariaDB [testdb]> CREATE TABLE tbl; #創建表 ERROR 1113 (42000): A table must have at least 1 column MariaDB [testdb]> CREATE TABLE tbl (id INT NOT NULL ,name VARCHAR(50)); #插入數據 Query OK, 0 rows affected (0.05 sec) MariaDB [testdb]> INSERT INTO tbl (id,name) VALUE (1,'TOM'); Query OK, 1 row affected (0.01 sec) MariaDB [testdb]> SELECT * FROM tbl; +----+------+ | id | name | +----+------+ | 1 | TOM | +----+------+ 1 row in set (0.01 sec) MariaDB [testdb]> LOCK TABLE tbl READ; #施加讀鎖 Query OK, 0 rows affected (0.00 sec) 另外啟動一個線程： MariaDB [(none)]> use testdb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [testdb]> SELECT * FROM tbl; +----+------+ | id | name | +----+------+ | 1 | TOM | +----+------+ 1 row in set (0.00 sec) MariaDB [testdb]> INSERT INTO tbl (id,name) VALUE (2,‘JERRY’); #插入數據被阻塞線程1釋放鎖： MariaDB [testdb]> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec) 線程2插入數據成功： MariaDB [testdb]> INSERT INTO tbl (id,name) VALUE (2,'JERRY'); Query OK, 1 row affected (0.01 sec) ~~~ ##### 事務日志：事務日志就是mysql數據文件之外的另外一個存儲區域，這個存儲區域帶來的作用是：當一個事務型存儲引擎在運行過程中，需要啟動一個事務并完成數據修改時，所有的修改操作（由多個sql語句組成），每一次的操作所涉及到的數據修改，這個修改的操作要轉換成底層存儲引擎所支持的相關操作的操作過程，它會把每一次的sql語句所涉及的操作步驟，具體的過程記錄在事務日志文件中。注意，事務日志記錄的是每一步的具體操作；例如，插入一行的數據是什么、插入什么位置、什么時候插入得等都記錄下來；再例如，如果是修改幾行，先修改哪行后修改哪行、幾點開始修改、幾點結束等等都記錄下來；而且這個日志要能夠重現操作就叫做事務日志。一個事務型存儲引擎它的操作借助于事務日志來保證其對應的事務特性；所以，任何事務型存儲引擎的相關操作，默認不會直接寫在數據庫文件上，而是先寫在事務日志中，并且事務日志為保證足夠可靠，基本上很少在內存中緩沖，寫完建基本比較差，都會先寫在內存上，然后等過一會再同步到磁盤上；事務日志也有這段緩沖區，但這個緩沖區不能太大時間也不能太久，因為如果允許緩存5秒鐘，系統崩潰最大會丟失5秒的數據，所以建議這個時間要足夠短，一般為1秒鐘同步到磁盤上一次；但是，同步越頻繁性能就越差，數據可靠性就越高。事務日志的每一步在記錄時，還會把修改之前的原始數據內容也記錄下來，這是為了支持undo和redo機制而記錄的；即當記錄的是修改數據時會把又該的數據之前的數據記錄下來。假如，操作進行到一半服務器崩潰了，把修改的操作日志撤銷了即可，因為還沒有同步到磁盤上；如為例如保證數據的穩定性，一個大事務中間有可能事務還沒完成，已經在事務日志記錄了30個，再記錄事務日志會先把一部分信息往磁盤上同步，開始真正修改原始數據了，這時如果崩潰，則只能把已經同步到表中的數據撤銷，這就是undo機制；還有一種情況是，比如一個事務有60個sql語句都寫完了且都記錄在事務日志中了，但只有氣筒30個同步到了磁盤上，如果此時崩潰，則此時數據是不一致的；因為有些事務已經提交，但是沒有存儲到磁盤上，這樣就必須把事務日志中為完成的語句同步到磁盤上，這就是redo機制；同步的要做提交，沒能完成的要做回滾，這就是崩潰后恢復。如果日志文件非常大，恰好寫滿了崩潰，下次啟動mysql時，必須把里面的所有語句統統同步到磁盤才能正常啟動，；如果事務日志文件大道2G，為了能夠把2G的語句同步到磁盤上，有可能mysql服務器啟動半小時，所以為了避免崩潰后啟動時間太長，把日志文件設置小一點；但是，有事會出現日志填滿了，但是日志內容還沒來的急同步到磁盤上，還有新的日志需要寫進來，那么久在啟動一個文件；也就是說日志文件一般啟動2個或3個是輪轉使用的，所謂輪轉指的是第一個日志文件填滿了就用第二個，同時把第一個日志文件同步到磁盤上，等第二個日志寫滿了就可以再使用第一個了，這樣輪轉使用，由多個事務日志文件組成的叫做事務日志組，日志組內至少應該有2個文件，但是多了同樣不好。事務日志文件和數據文件不應該放在同一磁盤上，因為會對磁盤寫IO操作帶來很大壓力，影響其性能；分開存放比較理想，但是有些場景又要必須放在一起，例如基于邏輯卷操作時；如果事務日志所在磁盤崩潰，則數據庫數據無法保持一致；所以，要把事務日志磁盤做鏡像；建議使用raid1；數據文件也很重要建議使用raid10； * 事務：一組原子性的SQL查詢，或者多個SQL語句組成了一個獨立的工作單元 * 事務日志：將隨機寫轉換為順序寫 `innodb_log_files_in_group`：一個日志組內有多少個日志文件，默認2個； `innodb_log_group_home_dir`：日志組目錄路徑，當前目錄就是datadir指定的路徑 `innodb_log_file_size`：每一個InnoDB事務日志的大小，默認5M `innodb_mirrored_log_groups` 修改配置文件，重啟生效；這些參數，建議在mysql初始化時就要設置好再啟動，而不能在啟動后再修改參數是不理想的做法； * ACID測試：從四個角度判斷存儲引擎是否支持事務 * A：automicity，原子性；整個事務中的所有操作要么全部成功執行，要么全部失敗后回滾； * C：consistency，一致性；數據庫總是從一個一致性狀態轉換為另一個一致性狀態； * I：isolation，隔離性；一個事務所做出的操作在提交之前，是不能為其它事務所見的，隔離有多種級別，主要是為了并發，隔離級別一個有4個； * D：durability，持久性；事務一旦提交，其所做的修改就會永久保存于數據庫中；注意：MyISAM存儲引擎是不支持事務的，InnoDB支持事務，所以要想使用事務得確保使用的是InnoDB等支持事務的存儲引擎； * mysql默認把每個語句當做一個事務提交，可以手動關閉自動提交功能，手動來啟動事務； ~~~css 啟動事務：START TRANSACTION 結束事務： (1)完成，提交：COMMIT (2)未完成，回滾：ROLLBACK 回滾時如果做過時間點保存可以一點點回滾；事務支持savepoint SAVEPINIT identifier；設置保存點 ROLLBACK TO [SAVEPOINT] identifier；回滾到指定保存點，默認回滾到最開始處 RELEASE SAVEPOINT identifier；釋放保存點 ~~~ * * * ~~~ruby 示例：查看是否開啟自動提交： MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%commit%'; +-------------------------------------------+-------+ | Variable_name | Value | +-------------------------------------------+-------+ | aria_group_commit | none | | aria_group_commit_interval | 0 | | autocommit | ON | | innodb_commit_concurrency | 0 | | innodb_flush_log_at_trx_commit | 1 | | innodb_use_global_flush_log_at_trx_commit | ON | +-------------------------------------------+-------+ autocommit=ON:自動提交，把每一個執行的語句當做一個事務，執行語句就提交，因為每一次事務提交都會導致數據要從事務日志緩沖區寫到事務日志，隨后還要從事務日志寫到數據文件；這很可能會影響性能，而且很多時候多個語句才是一個事務；因此可以使用set修改變量，關閉自動提交功能； MariaDB [(none)]> SET @@SESSION.autocommit=OFF; #修改session級別的參數，關閉自動提交 Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SHOW VARIABLES LIKE 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | OFF | +---------------+-------+ 示例：手動啟動事務： MariaDB [(none)]> START TRANSACTION; MariaDB [(none)]> use testdb; Database changed MariaDB [testdb]> SELECT * FROM tbl; +----+-------+ | id | name | +----+-------+ | 1 | TOM | | 2 | JERRY | +----+-------+ 2 rows in set (0.00 sec) MariaDB [testdb]> INSERT INTO tbl (id,name) VALUE (3,'LUCY'); Query OK, 1 row affected (0.01 sec) MariaDB [testdb]> UPDATE tbl SET name='guojing' WHERE id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [testdb]> SELECT * FROM tbl; +----+---------+ | id | name | +----+---------+ | 1 | guojing | | 2 | JERRY | | 3 | LUCY | +----+---------+ 3 rows in set (0.00 sec) MariaDB [testdb]> ROLLBACK; #手動回滾，結束事務 Query OK, 0 rows affected (0.01 sec) MariaDB [testdb]> SELECT * FROM tbl; +----+-------+ | id | name | +----+-------+ | 1 | TOM | | 2 | JERRY | +----+-------+ 2 rows in set (0.00 sec) 示例2: MariaDB [testdb]> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) MariaDB [testdb]> INSERT INTO tbl VALUE (3,'LUCK'),(4,'MURNN'); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [testdb]> SELECT * FROM tbl; +----+-------+ | id | name | +----+-------+ | 1 | TOM | | 2 | JERRY | | 3 | LUCK | | 4 | MURNN | +----+-------+ 4 rows in set (0.00 sec) MariaDB [testdb]> SAVEPOINT first; #做時間點，名稱為first Query OK, 0 rows affected (0.00 sec) MariaDB [testdb]> UPDATE tbl SET name='guojing' WHERE id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [testdb]> SAVEPOINT second; #做第二個時間保存點，名稱為second Query OK, 0 rows affected (0.00 sec) MariaDB [testdb]> INSERT INTO tbl VALUE (5,'HAHA'); Query OK, 1 row affected (0.00 sec) MariaDB [testdb]> SELECT * FROM tbl; +----+---------+ | id | name | +----+---------+ | 1 | guojing | | 2 | JERRY | | 3 | LUCK | | 4 | MURNN | | 5 | HAHA | +----+---------+ 5 rows in set (0.00 sec) MariaDB [testdb]> ROLLBACK TO second; #回滾到第二個保存點 Query OK, 0 rows affected (0.00 sec) MariaDB [testdb]> SELECT * FROM tbl; +----+---------+ | id | name | +----+---------+ | 1 | guojing | | 2 | JERRY | | 3 | LUCK | | 4 | MURNN | +----+---------+ 4 rows in set (0.00 sec) MariaDB [testdb]> ROLLBACK TO first; #回滾到第一個保存點 Query OK, 0 rows affected (0.00 sec) MariaDB [testdb]> SELECT * FROM tbl; +----+-------+ | id | name | +----+-------+ | 1 | TOM | | 2 | JERRY | | 3 | LUCK | | 4 | MURNN | +----+-------+ 4 rows in set (0.00 sec) MariaDB [testdb]> COMMIT; 手動提交，結束事務 Query OK, 0 rows affected (0.01 sec) ~~~ * * * **建議：手動顯示請求和提交事務，不要使用自動提交功能** #### 事務的隔離級別：由低到高，分為4個級別： * 第一級別：read-uncommitted；讀未提交--> 會存在臟讀；看到數據可能是未提交的，別人回滾后，看到的數據不準確； * 第二級別：read-committed；讀提交--> 會產生不可重復讀；只有別人提交后才能看到數據，解決了臟讀；不可重復讀，指的是同一事務中兩次讀取同一數據得到內容不一樣；就是別人沒提交時讀到的內容，別人修改數據提交后又讀到的，兩次讀取的結果不同；這就叫不可重復讀； * 第三級別：repeatable-read；可重讀--> 會產生幻讀；默認級別; 兩次讀取數據，只要自己沒提交，不管別人提沒提交，自己做一快照，如果啟動事務時有三行數據，讀的過程中只要自己不提交只有三行數據，但是，外衣別的事務提交了數據，例如新增了幾個行，那幾行明明在，但自己假裝看不見，產生幻讀； * 第四級別：serializable；可串行化--> 性能較差；如果別人沒提交，讀的數據是別人沒提交的，要等待別人的事務完成才能讀取到，有先有后，避免了幻讀； ~~~ruby 查看默認事務隔離級別： MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%tx%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | +---------------+-----------------+ 設置隔離級別： MariaDB [(none)]> SET @@global.tx_isolation='READ-COMMITTED'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%tx%'; +---------------+----------------+ | Variable_name | Value | +---------------+----------------+ | tx_isolation | READ-COMMITTED | +---------------+----------------+ 1 row in set (0.00 sec) 查看InnoDB存儲引擎的狀態信息： MariaDB [testdb]> SHOW ENGINE innodb STATUS\G; ~~~ MySQL是通過MVCC機制來實現事務隔離級別控制的；為什么啟動2個事務，基于PREPEATABLE-READ 隔離級別修改數據但還能看到之前的數據，就是因為自己啟動了一個快照，而快照功能的創建，刪除等等都是由事務功能mysql服務器自動在服務器級別進行管理的 ~~~ruby 示例：兩個線程，分別驗證各個事務隔離級別的效果 (1)演示讀未提交,兩個線程控制臺均設置事務隔離級別為讀未提交，并關閉事務自動提交功能 MariaDB [testdb]> SET @@SESSION.tx_isolation='READ-UNCOMMITTED'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SET @@SESSION.autocommit=OFF; Query OK, 0 rows affected (0.00 sec) 兩邊同時啟動事務： MariaDB [(none)]> START TRANSACTION; 線程1查看： MariaDB [testdb]> SELECT * FROM tbl; +----+-------+ | id | name | +----+-------+ | 1 | TOM | | 2 | JERRY | | 3 | LUCK | | 4 | MURNN | +----+-------+ 4 rows in set (0.00 sec) 線程2插入數據： MariaDB [testdb]> INSERT INTO tbl VALUE (5,'haha'); Query OK, 1 row affected (0.00 sec) 線程1再查看： MariaDB [testdb]> SELECT * FROM tbl; +----+-------+ | id | name | +----+-------+ | 1 | TOM | | 2 | JERRY | | 3 | LUCK | | 4 | MURNN | | 5 | haha | +----+-------+ 5 rows in set (0.00 sec) 線程2：回滾，結束事務： MariaDB [testdb]> ROLLBACK; Query OK, 0 rows affected (0.01 sec) 線程1再查看： MariaDB [testdb]> SELECT * FROM tbl; +----+-------+ | id | name | +----+-------+ | 1 | TOM | | 2 | JERRY | | 3 | LUCK | | 4 | MURNN | +----+-------+ 4 rows in set (0.00 sec) MariaDB [testdb]> COMMIT; #提交結束事務 Query OK, 0 rows affected (0.00 sec) ~~~ * * * ~~~ruby 示例：演示讀提交兩個線程都改為讀提交，并啟動事務： MariaDB [testdb]> SET @@SESSION.tx_isolation='READ-COMMITTED'; MariaDB [testdb]> START TRANSACTION; 線程1啟動查詢： MariaDB [testdb]> SELECT * FROM tbl; +----+-------+ | id | name | +----+-------+ | 1 | TOM | | 2 | JERRY | | 3 | LUCK | | 4 | MURNN | +----+-------+ 4 rows in set (0.01 sec) 線程2插入數據未提交： MariaDB [testdb]> INSERT INTO tbl VALUE (5,'haha'); Query OK, 1 row affected (0.02 sec) 線程1再查看數據未變化： MariaDB [testdb]> SELECT * FROM tbl; +----+-------+ | id | name | +----+-------+ | 1 | TOM | | 2 | JERRY | | 3 | LUCK | | 4 | MURNN | +----+-------+ 4 rows in set (0.00 sec) 線程2提交事務： MariaDB [testdb]> COMMIT; Query OK, 0 rows affected (0.02 sec) 線程1再查看，數據發生變化 MariaDB [testdb]> SELECT * FROM tbl; +----+-------+ | id | name | +----+-------+ | 1 | TOM | | 2 | JERRY | | 3 | LUCK | | 4 | MURNN | | 5 | haha | +----+-------+ 5 rows in set (0.00 sec) MariaDB [testdb]> COMMIT; #提交事務 ~~~ * * * ~~~ruby 示例：演示幻讀，可重復讀兩個線程都修改事務級別為可重復讀，并啟動事務： mysql> SHOW GLOBAL VARIABLES LIKE '%iso%'; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | +-----------------------+-----------------+ 1 row in set (0.00 sec) mysql> SET @@SESSION.autocommit=OFF; Query OK, 0 rows affected (0.02 sec) 線程1查看： mysql> SELECT * FROM tbl; +----+-------+ | id | name | +----+-------+ | 1 | TOM | | 2 | JERRY | | 3 | LUCK | | 4 | HAHA | +----+-------+ 4 rows in set (0.00 sec) 線程2刪除1行（未提交）： mysql> DELETE FROM tbl WHERE id=2; Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM tbl; +----+------+ | id | name | +----+------+ | 1 | TOM | | 3 | LUCK | | 4 | HAHA | +----+------+ 3 rows in set (0.00 sec) 線程1查看，未發生變化： mysql> SELECT * FROM tbl; +----+-------+ | id | name | +----+-------+ | 1 | TOM | | 2 | JERRY | | 3 | LUCK | | 4 | HAHA | +----+-------+ 4 rows in set (0.00 sec) 線程2提交： mysql> COMMIT; Query OK, 0 rows affected (0.07 sec) 線程1再查看，扔然未變化： mysql> SELECT * FROM tbl; +----+-------+ | id | name | +----+-------+ | 1 | TOM | | 2 | JERRY | | 3 | LUCK | | 4 | HAHA | +----+-------+ 4 rows in set (0.00 sec) 線程1提交事務后再查看數據發生變化： mysql> COMMIT; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM tbl; +----+------+ | id | name | +----+------+ | 1 | TOM | | 3 | LUCK | | 4 | HAHA | +----+------+ 3 rows in set (0.00 sec) ~~~ * * * ~~~ruby 示例：演示可串行化 2個線程都設置事務隔離級別可串行化： mysql> SET @@SESSION.transaction_isolation='SERIALIZABLE'; 線程1查看： mysql> SELECT * FROM tbl; +----+-------+ | id | name | +----+-------+ | 1 | TOM | | 3 | LUCK | | 4 | HAHA | | 2 | JERRY | +----+-------+ 4 rows in set (0.00 sec) 線程2啟動事務，并插入數據： mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO tbl VALUE (5,'HUANG RONG'); Query OK, 1 row affected (18.31 sec) 線程1啟動事務并查看，被阻塞： mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM tbl; 線程2提交事務： mysql> COMMIT; Query OK, 0 rows affected (0.03 sec) 線程1查看： mysql> SELECT * FROM tbl; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> SELECT * FROM tbl; +----+------------+ | id | name | +----+------------+ | 1 | TOM | | 3 | LUCK | | 4 | HAHA | | 2 | JERRY | | 5 | HUANG RONG | +----+------------+ 5 rows in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) ~~~