MySQL之并發(fā)控制和事務(wù)

并發(fā)控制

鎖粒度：

表級(jí)鎖

行級(jí)鎖

鎖：

讀鎖：共享鎖，只讀不可寫(xiě)，多個(gè)讀互不阻塞，

寫(xiě)鎖：獨(dú)占鎖,排它鎖，一個(gè)寫(xiě)鎖會(huì)阻塞其它讀和寫(xiě)鎖

實(shí)現(xiàn)

存儲(chǔ)引擎：自行實(shí)現(xiàn)其鎖策略和鎖粒度

服務(wù)器級(jí)：實(shí)現(xiàn)了鎖，表級(jí)鎖；用戶可顯式請(qǐng)求

分類：

隱式鎖：由存儲(chǔ)引擎自動(dòng)施加鎖

顯式鎖：用戶手動(dòng)請(qǐng)求

鎖策略：在鎖粒度及數(shù)據(jù)安全性尋求的平衡機(jī)制

顯示使用鎖

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]

關(guān)閉正在打開(kāi)的表（清除查詢緩存），通常在備份前加全局讀鎖

SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]

查詢時(shí)加寫(xiě)或讀鎖

事務(wù)

事務(wù)Transactions：一組原子性的SQL語(yǔ)句，或一個(gè)獨(dú)立工作單元

事務(wù)日志：記錄事務(wù)信息，實(shí)現(xiàn)undo,redo等故障恢復(fù)功能

ACID特性：

A：atomicity原子性；整個(gè)事務(wù)中的所有操作要么全部成功執(zhí)行，要么全部失敗后回滾

C：consistency一致性；數(shù)據(jù)庫(kù)總是從一個(gè)一致性狀態(tài)轉(zhuǎn)換為另一個(gè)一致性狀態(tài)

I：Isolation隔離性；一個(gè)事務(wù)所做出的操作在提交之前，是不能為其它事務(wù)所見(jiàn)；隔離有多種隔離級(jí)別，實(shí)現(xiàn)并發(fā)

D：durability持久性；一旦事務(wù)提交，其所做的修改會(huì)永久保存于數(shù)據(jù)庫(kù)中

例：A給B轉(zhuǎn)賬，不能A扣錢(qián)了而B不漲錢(qián)

Transaction生命周期

增刪改才算事務(wù)，查詢select不算在事務(wù)內(nèi)

事務(wù)

啟動(dòng)事務(wù)：START TRANSACTION

結(jié)束事務(wù)：

(1) COMMIT：提交

(2) ROLLBACK: 回滾

注意：只有事務(wù)型存儲(chǔ)引擎方能支持此類操作

建議：顯式請(qǐng)求和提交事務(wù)，而不要使用“自動(dòng)提交”功能

set [global] autocommit={1|0}

默認(rèn)為1自動(dòng)提交，如果想永久保存的話要寫(xiě)到配置文件中，autocommit=0

事務(wù)支持保存點(diǎn)：savepoint

SAVEPOINT identifier

ROLLBACK [WORK] TO [SAVEPOINT] identifier

RELEASE SAVEPOINT identifier

事務(wù)隔離級(jí)別

事務(wù)隔離級(jí)別：從上至下更加嚴(yán)格

?READ UNCOMMITTED 可讀取到未提交數(shù)據(jù)，產(chǎn)生臟讀

?READ COMMITTED 可讀提交數(shù)據(jù)，但未提交數(shù)據(jù)不可讀，產(chǎn)生不可重復(fù)讀，即可讀取到多個(gè)提交數(shù)據(jù)，導(dǎo)致每次讀取數(shù)據(jù)不一致

?REPEATABLE READ 可重復(fù)讀，多次讀取數(shù)據(jù)都一致，產(chǎn)生幻讀，即讀取過(guò)程中，即使有其它提交的事務(wù)修改數(shù)據(jù)，仍只能讀取到未修改前的舊數(shù)據(jù)。此為MySQL默認(rèn)設(shè)置

?SERIALIZABILE 可串行化，未提交的讀事務(wù)阻塞修改事務(wù)，或者未提交的修改事務(wù)阻塞讀事務(wù)。導(dǎo)致并發(fā)性能差

MVCC: 多版本并發(fā)控制，和事務(wù)級(jí)別相關(guān)

事務(wù)隔離級(jí)別

指定事務(wù)隔離級(jí)別：

服務(wù)器變量tx_isolation指定，默認(rèn)為REPEATABLE-READ，可在GLOBAL和SESSION級(jí)進(jìn)行設(shè)置

SET tx_isolation=''

READ-UNCOMMITTED

READ-COMMITTED

REPEATABLE-READ

SERIALIZABLE

服務(wù)器選項(xiàng)中指定

vim /etc/my.cnf

[mysqld]

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

事務(wù)的并發(fā)控制

死鎖：

兩個(gè)或多個(gè)事務(wù)在同一資源相互占用，并請(qǐng)求鎖定對(duì)方占用的資源的狀態(tài)

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

MySQL會(huì)自動(dòng)檢測(cè)死鎖，并犧牲其中的一個(gè)事務(wù)以解開(kāi)死鎖，通常是犧牲等待時(shí)間不長(zhǎng)的那個(gè)

事務(wù)日志：

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

日志文件： ib_logfile0， ib_logfile1