MySQL之并發控制和事務

并發控制

鎖粒度：

表級鎖

行級鎖

鎖：

讀鎖：共享鎖，只讀不可寫，多個讀互不阻塞，

寫鎖：獨占鎖,排它鎖，一個寫鎖會阻塞其它讀和寫鎖

實現

存儲引擎：自行實現其鎖策略和鎖粒度

服務器級：實現了鎖，表級鎖；用戶可顯式請求

分類：

隱式鎖：由存儲引擎自動施加鎖

顯式鎖：用戶手動請求

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

顯示使用鎖

LOCK TABLES

tbl_name [[AS] alias] lock_type

[, tbl_name [[AS] alias] lock_type] ...

lock_type: READ ， WRITE

UNLOCK TABLES 解鎖

FLUSH TABLES tb_name[,...] [WITH READ LOCK]

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

SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]

查詢時加寫或讀鎖

事務

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

事務日志：記錄事務信息，實現undo,redo等故障恢復功能

ACID特性：

A：atomicity原子性；整個事務中的所有操作要么全部成功執行，要么全部失敗后回滾

C：consistency一致性；數據庫總是從一個一致性狀態轉換為另一個一致性狀態

I：Isolation隔離性；一個事務所做出的操作在提交之前，是不能為其它事務所見；隔離有多種隔離級別，實現并發

D：durability持久性；一旦事務提交，其所做的修改會永久保存于數據庫中

例：A給B轉賬，不能A扣錢了而B不漲錢

Transaction生命周期

增刪改才算事務，查詢select不算在事務內

事務

啟動事務：START TRANSACTION

結束事務：

(1) COMMIT：提交

(2) ROLLBACK: 回滾

注意：只有事務型存儲引擎方能支持此類操作

建議：顯式請求和提交事務，而不要使用“自動提交”功能

set [global] autocommit={1|0}

默認為1自動提交，如果想永久保存的話要寫到配置文件中，autocommit=0

事務支持保存點：savepoint

SAVEPOINT identifier

ROLLBACK [WORK] TO [SAVEPOINT] identifier

RELEASE SAVEPOINT identifier

事務隔離級別

事務隔離級別：從上至下更加嚴格

?READ UNCOMMITTED 可讀取到未提交數據，產生臟讀

?READ COMMITTED 可讀提交數據，但未提交數據不可讀，產生不可重復讀，即可讀取到多個提交數據，導致每次讀取數據不一致

?REPEATABLE READ 可重復讀，多次讀取數據都一致，產生幻讀，即讀取過程中，即使有其它提交的事務修改數據，仍只能讀取到未修改前的舊數據。此為MySQL默認設置

?SERIALIZABILE 可串行化，未提交的讀事務阻塞修改事務，或者未提交的修改事務阻塞讀事務。導致并發性能差

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

事務隔離級別

指定事務隔離級別：

服務器變量tx_isolation指定，默認為REPEATABLE-READ，可在GLOBAL和SESSION級進行設置

SET tx_isolation=''

READ-UNCOMMITTED

READ-COMMITTED

REPEATABLE-READ

SERIALIZABLE

服務器選項中指定

vim /etc/my.cnf

[mysqld]

transaction-isolation=SERIALIZABLE 注：配置名跟變量名不一樣

事務的并發控制

死鎖：

兩個或多個事務在同一資源相互占用，并請求鎖定對方占用的資源的狀態

例：事務tr1鎖定table1 row1，然后事務transaction2鎖定table2 row2，兩者互不干擾，但如果此時tr1嘗試修改table2 row2則會卡住，此時如果tr2再嘗試修改table1 row1，則tr1和tr2都會卡住，發生死鎖

MySQL會自動檢測死鎖，并犧牲其中的一個事務以解開死鎖，通常是犧牲等待時間不長的那個

事務日志：

事務日志的寫入類型為“追加”，因此其操作為“順序IO”；通常也被稱為：預寫式日志 write ahead logging

日志文件： ib_logfile0， ib_logfile1