|
重磅干貨，第一時(shí)間送達(dá) ?500g+超全學(xué)習(xí)資源免費(fèi)領(lǐng)取，干貨來(lái)襲！ 一、鎖定機(jī)制最常討論的話題 鎖是數(shù)據(jù)庫(kù)系統(tǒng)區(qū)別于文件系統(tǒng)的一個(gè)關(guān)鍵特性。數(shù)據(jù)庫(kù)系統(tǒng)使用鎖是為了支持對(duì)共享資源進(jìn)行并發(fā)訪問(wèn)，提供數(shù)據(jù)的完整性和一致性。例如：操作緩沖池中的 LRU 列表，刪除、添加、移動(dòng) LUR 列表中的元素。 對(duì)于任何一種數(shù)據(jù)庫(kù)來(lái)說(shuō)都需要有相應(yīng)的鎖定機(jī)制，所以 MySQL 自然也不能例外。 MySQL 數(shù)據(jù)庫(kù)由于其自身架構(gòu)的特點(diǎn)，存在多種數(shù)據(jù)存儲(chǔ)引擎，每種存儲(chǔ)引擎所針對(duì)的應(yīng)用場(chǎng)景特點(diǎn)都不太一樣，為了滿(mǎn)足各自特定應(yīng)用場(chǎng)景的需求，每種存儲(chǔ)引擎的鎖定機(jī)制都是為各自所面對(duì)的特定場(chǎng)景而優(yōu)化設(shè)計(jì)，所以各存儲(chǔ)引擎的鎖定機(jī)制也有較大區(qū)別。 MySQL 常用存儲(chǔ)引擎(MyISAM，InnoDB)用了兩種類(lèi)型（級(jí)別）的鎖定機(jī)制：表級(jí)鎖定，行級(jí)鎖定。 1）表級(jí)鎖 表級(jí)別的鎖定是 MySQL 各存儲(chǔ)引擎中最大顆粒度的鎖定機(jī)制。該鎖定機(jī)制最大的特點(diǎn)是實(shí)現(xiàn)邏輯非常簡(jiǎn)單，帶來(lái)的系統(tǒng)負(fù)面影響最小。所以獲取鎖和釋放鎖的速度很快。由于表級(jí)鎖一次會(huì)將整個(gè)表鎖定，所以可以很好的避免困擾我們的死鎖問(wèn)題。 當(dāng)然，鎖定顆粒度大所帶來(lái)最大的負(fù)面影響就是出現(xiàn)鎖定資源爭(zhēng)用的概率也會(huì)最高，致使并大度大打折扣。 使用表級(jí)鎖定的主要是 MyISAM、MEMORY、CSV 等一些非事務(wù)性存儲(chǔ)引擎。 2）行級(jí)鎖 行級(jí)鎖定最大的特點(diǎn)就是鎖定對(duì)象的顆粒度很小，也是目前各大數(shù)據(jù)庫(kù)管理軟件所實(shí)現(xiàn)的鎖定顆粒度最小的。由于鎖定顆粒度很小，所以發(fā)生鎖定資源爭(zhēng)用的概率也最小，能夠給予應(yīng)用程序盡可能大的并發(fā)處理能力而提高一些需要高并發(fā)應(yīng)用系統(tǒng)的整體性能。 雖然能夠在并發(fā)處理能力上面有較大的優(yōu)勢(shì)，但是行級(jí)鎖定也因此帶來(lái)了不少弊端。由于鎖定資源的顆粒度很小，所以每次獲取鎖和釋放鎖需要做的事情也更多，帶來(lái)的消耗自然也就更大了。此外，行級(jí)鎖定也最容易發(fā)生死鎖。 使用行級(jí)鎖定的主要是 InnoDB 存儲(chǔ)引擎。 總結(jié)如下：
例如，下圖只是對(duì) myisam 表修改一行記錄： 其他 insert 操作就需要等待上個(gè) update 語(yǔ)句執(zhí)行完成，再執(zhí)行 insert 操作，這時(shí)候就會(huì)產(chǎn)生表鎖。 InnoDB 存儲(chǔ)引擎實(shí)現(xiàn)了如下兩種標(biāo)準(zhǔn)的行級(jí)鎖：
如果一個(gè)事務(wù) t1 已近獲得了行 r 的共享鎖，那么另外的事務(wù) t2 可以獲得行 r 的共享鎖，因?yàn)樽x取并沒(méi)有改變行 r 的數(shù)據(jù)，稱(chēng)這種情況為鎖兼容（Lock Compatible）。但若有其他的事務(wù)想獲得行 r 的排它鎖，則必須等待事務(wù) t1，t2 釋放行 r 的共享鎖——這種情況稱(chēng)為鎖不兼容(confilict)。 此外，InnoDB 存儲(chǔ)引擎支持多粒度（granular）鎖定，這種鎖定允許事務(wù)在行級(jí)上的鎖和表級(jí)上的鎖同時(shí)存在。為了支持在不同粒度上進(jìn)行加鎖操作，InnoDB 存儲(chǔ)引擎支持了一種額外的鎖方式，稱(chēng)為意向鎖（Intention Lock）。 意向鎖是將鎖定的對(duì)象分為多個(gè)層次，意向鎖意味著事務(wù)希望在更細(xì)粒度上進(jìn)行加鎖。 如下圖，若將上鎖的對(duì)象看成一顆樹(shù)： 那么最下層的對(duì)象（行記錄）上鎖，也就是對(duì)最細(xì)粒度的對(duì)象進(jìn)行上鎖，那么首先需要對(duì)粗粒度的對(duì)象上鎖。 如果需要對(duì)頁(yè)上的記錄 r 進(jìn)行上 X 鎖，那么分別需要對(duì)數(shù)據(jù)庫(kù) A、表、頁(yè)上意向鎖，最后對(duì)記錄 r 上 X 鎖，若其中任何一個(gè)部分導(dǎo)致等待，那么該操作需要等待粗粒度鎖的完成。 舉例來(lái)說(shuō)，在對(duì)記錄 r 加 X 鎖之前，已近有事務(wù)對(duì)表 1 進(jìn)行了 S 表鎖，那么表 1 上已存在 S 鎖，之后事務(wù)需要對(duì)記錄 r 表 1 上加 IX ， 由于不兼容，所以該事務(wù)，需要等待表鎖操作的完成。 InnoDB 存儲(chǔ)引擎支持意向鎖設(shè)計(jì)比較簡(jiǎn)練，其意向鎖即為表級(jí)別的鎖，設(shè)計(jì)目的主要是為了在一個(gè)事務(wù)中揭示下一行將被請(qǐng)求的鎖類(lèi)型。其支持兩種意向鎖：
意向鎖是有數(shù)據(jù)引擎自己維護(hù)的，用戶(hù)無(wú)法手動(dòng)操作意向鎖，在為數(shù)據(jù)行加共享/排他鎖之前，InooDB 會(huì)先獲取該數(shù)據(jù)行所在在數(shù)據(jù)表的對(duì)應(yīng)意向鎖。 由于 InnoDB 存儲(chǔ)引擎支持的是行級(jí)別的鎖，因此意向鎖其實(shí)不會(huì)阻塞除全表掃以外的任何請(qǐng)求。 表級(jí)意向鎖與行鎖的兼容性：
用戶(hù)有時(shí)候需要顯示地對(duì)數(shù)據(jù)庫(kù)讀取操作進(jìn)行加鎖以保證數(shù)據(jù)邏輯的一致性。而這要求數(shù)據(jù)庫(kù)支持加鎖語(yǔ)句，即使是對(duì)于 select 的只讀操作。InnoDB 存儲(chǔ)引擎對(duì)于 select 語(yǔ)句支持兩種一致性的鎖定讀操作： select ... for update; select ... lock in share mode; select … for update 對(duì)讀取的行記錄加一個(gè) X 鎖，其他事務(wù)不能對(duì)已鎖定的行加上任何鎖。 select … lock in share mode 對(duì)讀取的行記錄加一個(gè) S 鎖，其他事務(wù)可以向被鎖定的加 S 鎖，但是如果加 X 鎖，則會(huì)組賽。 此外 select ... for update , select ... lock in share mode 必須在一個(gè)事務(wù)中，當(dāng)事務(wù)提交了，鎖也就釋放了。因此在使用上訴兩句select 鎖定語(yǔ)句時(shí)，務(wù)必加上BEGIN，START TRANSACTION 或者 SET AUTOCOMMIT=0。 在默認(rèn)的隔離級(jí)別下，一致讀是指 InnoDB 在多版本控制中在事務(wù)的首次讀時(shí)產(chǎn)生一個(gè)鏡像，在首次讀時(shí)間點(diǎn)之前，其他事務(wù)提交的修改可以讀取到，而首次讀時(shí)間點(diǎn)之后，其他事務(wù)提交的修改或者是未提交的修改，都讀取不到。 唯一例外的情況，是在首次讀時(shí)間點(diǎn)之前的本事務(wù)未提交的修改數(shù)據(jù)可以讀取到。 在讀取提交數(shù)據(jù)隔離級(jí)別下，一致讀的每個(gè)讀取操作都會(huì)有自己的鏡像。一致讀操作不會(huì)施加任何的鎖，所以就不會(huì)阻止其他事務(wù)的修改動(dòng)作。 比如最經(jīng)典的 mysqldump --single-transaction 備份的時(shí)候就是把當(dāng)前的事務(wù)隔離級(jí)別改變?yōu)榭芍貜?fù)讀并開(kāi)啟一個(gè)一致性事務(wù)的快照 , 就是一致性非鎖定讀。 一致讀在某些 DDL 語(yǔ)句下不生效：
自增長(zhǎng)在數(shù)據(jù)庫(kù)中非常常見(jiàn)的一種屬性，也是很多 DBA 或開(kāi)發(fā)人員首選主鍵方式。在 InnoDB 存儲(chǔ)引擎的內(nèi)存結(jié)構(gòu)中，對(duì)每個(gè)含有自增長(zhǎng)值的表都有一個(gè)自增長(zhǎng)計(jì)數(shù)器。 插入操作會(huì)依據(jù)這個(gè)自增長(zhǎng)的計(jì)數(shù)器加 1 賦予自增長(zhǎng)列。這個(gè)實(shí)現(xiàn)方式稱(chēng)作 AUTO-INC Locking（自增鎖）。這種自增鎖是采用一種特殊的表鎖機(jī)制，為了提高插入的性能，鎖不是在一個(gè)事務(wù)完成后才釋放，而是在完成對(duì)自增長(zhǎng)值插入的sql 語(yǔ)句后立即釋放。 AUTO-INC Locking 從一定程度上提高了并發(fā)插入的效率，但還是存在一些性能上的問(wèn)題。
Innodb_autoinc_lock_mode 來(lái)控制自增長(zhǎng)的模式，改參數(shù)的默認(rèn)值為1。 InnoDB 提供了一種輕量級(jí)互斥量的自增長(zhǎng)實(shí)現(xiàn)機(jī)制，大大提高了自增長(zhǎng)值插入的性能。提供參數(shù) innodb_autoinc_lock_mode 來(lái)控制自增長(zhǎng)鎖使用的算法，默認(rèn)值為 1。他允許你在可預(yù)測(cè)的自增長(zhǎng)值和最大化并發(fā)插入操作之間進(jìn)行權(quán)衡。 插入類(lèi)型的分類(lèi)： innodb_autoinc_lock_mode 在不同設(shè)置下對(duì)自增長(zhǎng)的影響：
因此，使用這種方式，任何情況下都需要使用row-base replication，這樣才能保證最大并發(fā)性能和replication的主從數(shù)據(jù)的一致。 二、行鎖的幾種算法
Record Lock 總是會(huì)去鎖住索引記錄， 如果 InnoDB 存儲(chǔ)引擎表在建立的時(shí)候沒(méi)有設(shè)置任何一個(gè)索引，那么這是 InnoDB 存儲(chǔ)引擎會(huì)使用隱式的主鍵來(lái)進(jìn)行鎖定。 行級(jí)鎖是施加在索引行數(shù)據(jù)上的鎖，比如 SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE 語(yǔ)句是在 t.c1=10 的索引行上增加鎖，來(lái)阻止其他事務(wù)對(duì)對(duì)應(yīng)索引行的insert/update/delete操作。 行鎖總是在索引記錄上面加鎖，即使一張表沒(méi)有設(shè)置任何索引，InnoDB 會(huì)創(chuàng)建一個(gè)隱藏的聚簇索引，然后在這個(gè)索引上加上行鎖。例如: create table t (c1 int primary key); insert into t select 1; insert into t select 3; insert into t select 10; # 會(huì)話A start transaction; update t set c1=12 where c1 = 10 ; # 會(huì)話B: mysql> update t set c1=11 where c1=10; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 會(huì)阻止該事務(wù)對(duì)索引行上的修改 當(dāng)一個(gè) InnoDB 表沒(méi)有任何索引時(shí)， 則行級(jí)鎖會(huì)施加在隱含創(chuàng)建的聚簇索引上，所以說(shuō)當(dāng)一條 SQL 沒(méi)有走任何索引時(shí)，那么將會(huì)在每一條聚集索引后面加 X 鎖，這個(gè)類(lèi)似于表鎖，但原理上和表鎖應(yīng)該是完全不同的。例： # 刪除表t的主鍵索引 alter table t drop primary key; 開(kāi)啟會(huì)話1： start transaction; update t set c1=11 where c1=10; 開(kāi)啟會(huì)話2： start transaction; update t set c1=8 where c1=10; 這個(gè)時(shí)候發(fā)生了鎖等待， 這時(shí)候開(kāi)啟會(huì)話3，鎖等待發(fā)生了什么： mysql> select * from sys.innodb_lock_waits\G; 如下截圖： 當(dāng)我們用范圍條件而不是相等條件檢索數(shù)據(jù)，并請(qǐng)求共享或排他鎖時(shí)，InnoDB 會(huì)給符合條件 的已有數(shù)據(jù)記錄的索引項(xiàng)加鎖；對(duì)于鍵值在條件范圍內(nèi)但并不存在的記錄，叫做“間隙（GAP)”，InnoDB 也會(huì)對(duì)這個(gè)'間隙'加鎖。 間隔鎖是施加在索引記錄之間的間隔上的鎖，鎖定一個(gè)范圍的記錄、但不包括記錄本身，比如 SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE 語(yǔ)句，盡管有可能對(duì) c1 字段來(lái)說(shuō)當(dāng)前表里沒(méi)有=15的值，但還是會(huì)阻止=15的數(shù)據(jù)的插入操作，是因?yàn)殚g隔鎖已經(jīng)把索引查詢(xún)范圍內(nèi)的間隔數(shù)據(jù)也都鎖住了，間隔鎖的使用只在部分事務(wù)隔離級(jí)(可重復(fù)讀級(jí))別才是生效的 。 間隔鎖只會(huì)阻止其他事務(wù)的插入操作，就是只有 insert 操作會(huì)產(chǎn)生 GAP 鎖，update 操作不會(huì)參數(shù) GAP 鎖。例: # 創(chuàng)建keme1 測(cè)試數(shù)據(jù)， 插入模擬數(shù)據(jù) create table keme1 (id int primary key,name varchar(10)); insert into keme1 values (1,'a'),(3,'c'), (4,'d'), (5,'e'), (6,'f'); # 開(kāi)啟三個(gè)session 窗口，兩個(gè)窗口模擬兩個(gè)事務(wù)， 另外一個(gè)窗口看 兩個(gè)事務(wù)發(fā)生一些間隔鎖的信息 session1： start transaction; mysql> update keme1 set name='bb' where id between 1 and 3; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 session2: start transaction; mysql> insert into keme1 values (2,'bb'); # 這時(shí)候就有鎖等待了 select * from sys.innodb_lock_waits\G; 使用gap lock的前置條件：
Gap Lock 的作用是為了阻止多個(gè)事務(wù)將記錄插入到同一范圍內(nèi)，而這會(huì)導(dǎo)致幻讀問(wèn)題的產(chǎn)生。 可以通過(guò)兩種方式來(lái)關(guān)閉 Gap Lock：
在默認(rèn)情況下，MySQL 的事務(wù)隔離級(jí)別是可重復(fù)讀，并且 innodb_locks_unsafe_for_binlog 參數(shù)為 0，這時(shí)默認(rèn)采用 next-key locks。 所謂 Next-Key Locks，就是記錄鎖和間隔鎖的結(jié)合，即除了鎖住記錄本身，還要再鎖住索引之間的間隙。 當(dāng)掃描表的索引時(shí)，InnoDB 以這種形式實(shí)現(xiàn)行級(jí)的鎖：遇到匹配的的索引記錄，在上面加上對(duì)應(yīng)的 S 鎖或 X 鎖。 因此，行級(jí)鎖實(shí)際上是索引記錄鎖。如果一個(gè)事務(wù)擁有索引上記錄 r 的一個(gè) S 鎖或 X 鎖，另外的事務(wù)無(wú)法立即在 r 記錄索引順序之前的間隙上插入一條新的記錄。 假設(shè)有一個(gè)索引包含值：10，11，13和20。下列的間隔上都可能加上一個(gè) Next-Key 鎖（左開(kāi)右閉）。 (negative infinity, 10] (10, 11] (11, 13] (13, 20] (20, positive infinity) 在最后一個(gè)區(qū)間中，Next-Key 鎖鎖定了索引中的最大值到正無(wú)窮。 默認(rèn)情況下，InnoDB 啟用 RR 事務(wù)隔離級(jí)別。此時(shí)，InnoDB 在查找和掃描索引時(shí)會(huì)使用 Next-Key 鎖，其設(shè)計(jì)的目的是為了解決『幻讀』的出現(xiàn)?！ ?/span> 當(dāng)查詢(xún)的索引含有唯一(主鍵索引和唯一索引)屬性是，InnoDB 存儲(chǔ)引擎會(huì)對(duì) Next-Key Lock 進(jìn)行優(yōu)化，將其降級(jí)為 Record Lock ，即僅鎖住索引本身，而不是范圍。 插入意向鎖是一種在數(shù)據(jù)行插入前設(shè)置的 gap 鎖。這種鎖用于在多事務(wù)插入同一索引間隙時(shí)，如果這些事務(wù)不是往這段 gap 的同一位置插入數(shù)據(jù)，那么就不用互相等待。 create table keme2 (a int primary key); insert into keme2 values (10),(11),(13),(20); 開(kāi)啟三個(gè)會(huì)話窗口 session1： start transaction; mysql> select * from keme2 where a > 18 for update; +----+ | a | +----+ | 20 | +----+ 1 row in set (0.00 sec) session2; start transaction; mysql> insert into keme2 select 19; 客戶(hù)端 A 創(chuàng)建了一個(gè) keme2 表，包含 10,11,13,20 四條索引記錄，然后去設(shè)置一個(gè)互斥鎖在大于 18 的所有索引記錄上。這個(gè)互斥鎖包含了在 20 記錄前的 gap 鎖。 三、鎖問(wèn)題 通過(guò)鎖機(jī)制可以實(shí)現(xiàn)事務(wù)的隔離性要求，使得事務(wù)可以并發(fā)地工作。鎖提高了并發(fā)，但是也有有潛在的問(wèn)題。不過(guò)好在因?yàn)槭聞?wù)隔離性的要求，鎖只會(huì)帶來(lái)三種問(wèn)題，如果可以防止這三種情況的發(fā)生，哪將不會(huì)產(chǎn)生并發(fā)異常。 先了解臟數(shù)據(jù)、臟頁(yè)、臟讀。 臟頁(yè)：指的是在緩沖池中已近被修改的頁(yè)，但是還沒(méi)有刷新到磁盤(pán)中，即數(shù)據(jù)庫(kù)實(shí)例內(nèi)存中的頁(yè)和磁盤(pán)中的頁(yè)數(shù)據(jù)是不一致的，當(dāng)然在刷新到磁盤(pán)之前，日志都已經(jīng)被寫(xiě)入到了重做日志文件中。 臟數(shù)據(jù)：是指事務(wù)對(duì)緩沖池中行記錄的修改，并且還沒(méi)有被提交。 對(duì)于臟頁(yè)的讀取，是非常正常的。臟頁(yè)是因?yàn)閿?shù)據(jù)庫(kù)實(shí)例內(nèi)存和磁盤(pán)的異步造成的，這并不影響數(shù)據(jù)的一致性（或者說(shuō)兩者最終會(huì)達(dá)到一致性，即當(dāng)臟頁(yè)都刷到磁盤(pán)）。并且因?yàn)榕K頁(yè)的刷新是異步的，不影響數(shù)據(jù)庫(kù)的可用性，因此可以帶來(lái)性能的提高。 臟數(shù)據(jù)：是指未提交的數(shù)據(jù)，如果讀到臟數(shù)據(jù)，即一個(gè)事務(wù)可以讀到另外一個(gè)事務(wù)中未提交的數(shù)據(jù)，則顯然違反了數(shù)據(jù)庫(kù)的隔離性。 臟讀：指的就是在不同的事務(wù)下，當(dāng)前事務(wù)可以讀到另外事務(wù)未提交的數(shù)據(jù)，簡(jiǎn)單來(lái)說(shuō)就是可以讀到臟數(shù)據(jù)。 臟讀示例： create table t (a int primary key); insert into t values (1);
會(huì)話 A 并沒(méi)有主動(dòng)提交 2 這條插入事務(wù)，但是在會(huì)話 B 讀取到了，這就是臟讀。 不可重讀是在一個(gè)事務(wù)內(nèi)讀取同一數(shù)據(jù)集合。在這個(gè)事務(wù)還沒(méi)有結(jié)束時(shí)，另外一個(gè)事務(wù)也訪問(wèn)同一數(shù)據(jù)集合，并做了一些 DML 操作。因此在第一個(gè)事務(wù)中的兩次讀取數(shù)據(jù)之間，由于第二個(gè)事務(wù)的修改，那么第一個(gè)事務(wù)兩次讀到的數(shù)據(jù)可能是不一樣的。這樣就發(fā)生了在一個(gè)事務(wù)內(nèi)兩次讀到的數(shù)據(jù)是不一樣的情況，這種情況稱(chēng)為不可重復(fù)讀。 不可重復(fù)讀和臟讀的區(qū)別是：臟讀示讀到未提交的數(shù)據(jù)，而不可重復(fù)讀讀到確實(shí)已近提交的數(shù)據(jù)。 雖然數(shù)據(jù)庫(kù)能阻止更新問(wèn)題的產(chǎn)生，但是在生產(chǎn)應(yīng)用還有另一個(gè)邏輯意義丟失更新問(wèn)題，而導(dǎo)致該問(wèn)題的并不是因?yàn)閿?shù)據(jù)庫(kù)本身的問(wèn)題。實(shí)際上，在所有多用戶(hù)計(jì)算機(jī)系統(tǒng)環(huán)境下都有可能產(chǎn)生這個(gè)問(wèn)題。比如下面的情況： 比如一個(gè)用戶(hù)賬號(hào)中有 10000 元，他用兩個(gè)網(wǎng)上銀行的客戶(hù)端分別進(jìn)行轉(zhuǎn)賬操作，第一次轉(zhuǎn)賬 9000 人民幣，因?yàn)榫W(wǎng)絡(luò)和數(shù)據(jù)的關(guān)系，這時(shí)需要等待。 但是這時(shí)用戶(hù)操作另一個(gè)網(wǎng)上銀行客戶(hù)端，轉(zhuǎn)賬 1 元，如果最終兩筆操作都成功了，用戶(hù)賬號(hào)的余款是 9999 元，第一次轉(zhuǎn)的 9000 人民幣并沒(méi)有得到更新，但是在轉(zhuǎn)賬的另一個(gè)賬號(hào)卻會(huì)收到這 9000 元，這導(dǎo)致了結(jié)果就是錢(qián)變多，而賬不平。 但是銀行了也很聰明啊，個(gè)人網(wǎng)銀綁定 usb key 的，不會(huì)發(fā)生這種情況的。是的，通過(guò) usb key 登錄也許可以解決這個(gè)問(wèn)題。但是更重要的是在數(shù)據(jù)庫(kù)層解決這個(gè)問(wèn)題，避免任何可能發(fā)生丟失更新的情況。 要避免丟失更新發(fā)生 ，需要讓事務(wù)在這種情況下的操作變成串行化，而不是并行的操作。 四、鎖阻塞 因?yàn)椴煌i之間的兼容性關(guān)系，在有些時(shí)刻一個(gè)事務(wù)中的鎖需要等待另一個(gè)事務(wù)中的鎖釋放它所占用的資源，這就是阻塞。阻塞并不是一件壞事，其實(shí)為了確保事務(wù)可以并發(fā)正常地運(yùn)行。 在 InnoDB 存儲(chǔ)引擎中，參數(shù) innodb_lock_wait_timeout 用來(lái)控制等待的時(shí)間(默認(rèn)是50秒)， innodb_rollback_on_timeout 用來(lái)設(shè)定是否在等待超時(shí)時(shí)對(duì)進(jìn)行中的事務(wù)進(jìn)行回滾操作（默認(rèn)是off，不回滾）。參數(shù) innodb_lock_wait_timeout 可以在 MySQL 數(shù)據(jù)庫(kù)運(yùn)行時(shí)進(jìn)行調(diào)整： 在默認(rèn)情況下 InnoDB 存儲(chǔ)引擎不會(huì)回滾超時(shí)引發(fā)的錯(cuò)誤異常。其實(shí) InnoDB 存儲(chǔ)引擎在大部分情況下都不會(huì)對(duì)異常進(jìn)行回滾。 查看鎖阻塞的信息： select * from information_schema.innodb_trx\G; # 查看當(dāng)前的事務(wù)信息 select * from information_schema.innodb_locks\G; # 查看當(dāng)前的鎖信息 select * from information_schema.innodb_lock_waits\G; # 查看當(dāng)前的鎖等待信息 可以聯(lián)表查，查找自己想要的結(jié)果。 select * from sys.innodb_lock_waits\G; # 查看當(dāng)前的鎖等待信息 show engine innodb status\G; 還可以通過(guò)當(dāng)前執(zhí)行了執(zhí)行了什么語(yǔ)句 select * from performance_schema.events_statements_current\G; show full processlist; 五、死鎖 死鎖是指兩個(gè)或兩個(gè)以上的事務(wù)在執(zhí)行過(guò)程中，因爭(zhēng)奪鎖資源而造成的一種互相等待的現(xiàn)象。 ①解決死鎖的問(wèn)題最簡(jiǎn)單的方式是不要有等待，將任何的等待都轉(zhuǎn)化為回滾，并且事務(wù)重新開(kāi)始。 這種沒(méi)有死鎖問(wèn)題的產(chǎn)生。在線上環(huán)境中，可能導(dǎo)致并發(fā)性能的下降，甚至任何一個(gè)事務(wù)都不能進(jìn)行。而這鎖帶來(lái)的問(wèn)題遠(yuǎn)比死鎖問(wèn)題更為嚴(yán)重，而這鎖帶來(lái)的問(wèn)題原題遠(yuǎn)比死鎖問(wèn)題更為嚴(yán)重，因?yàn)檫@很難被發(fā)現(xiàn)并且浪費(fèi)資源。 ②解決死鎖的問(wèn)題最簡(jiǎn)單的一種方法時(shí)超時(shí)，即當(dāng)兩個(gè)事務(wù)互相等待是，當(dāng)一個(gè)等待時(shí)超過(guò)設(shè)置的某一閾值是，其中一個(gè)事務(wù)進(jìn)行回滾，另一個(gè)等待的事務(wù)就能繼續(xù)進(jìn)行。用 innodb_lock_wait_timeout 用來(lái)設(shè)置超時(shí)的時(shí)間。 超時(shí)機(jī)制雖然簡(jiǎn)單，僅通過(guò)超時(shí)后對(duì)事務(wù)進(jìn)行回滾的方式來(lái)處理，或者說(shuō)其根據(jù) FIFO 的順序選擇回滾對(duì)象。但若超時(shí)的事務(wù)所占權(quán)重比較大，如事務(wù)操作更新很多行(比如某程序猿用死循環(huán)來(lái)執(zhí)行一些事務(wù))，占用了較多的 undo log，這是采用 FIFO 的方式，就顯得不合適了，因?yàn)榛貪L這個(gè)事務(wù)的時(shí)間相對(duì)另一個(gè)事務(wù)所占用的時(shí)間可能會(huì)更多。 在 mysql 5.7.x 和 mysql 5.6.x 對(duì)死鎖采用的方式： mysql 5.6.x 是用鎖等待（超時(shí)）的方式來(lái)解決， 沒(méi)有自動(dòng)解決死鎖的問(wèn)題。
mysql 5.7.x 默認(rèn)開(kāi)啟了死鎖保護(hù)機(jī)制：
如果程序是串行的，那么不可能發(fā)生死鎖。死鎖只存在于并發(fā)的情況，而數(shù)據(jù)庫(kù)本身就是一個(gè)并發(fā)運(yùn)行的程序，因此可能會(huì)發(fā)生死鎖。 死鎖示例： a :創(chuàng)建表 create table temp(id int primary key ,name varchar(10)); insert into temp values(1,'a'),(2,'b'),(3,'c'); 此時(shí)表里只有3條數(shù)據(jù) 執(zhí)行步驟根據(jù)數(shù)據(jù)順序來(lái)： 1. 事務(wù)1: start transaction; update temp set name='aa' where id=1; 2. 事務(wù)2: start transaction; update temp set name='bb' where id=2; 3. 事務(wù)1:update temp set name='aaa' where id=2; 這時(shí)候3的步驟會(huì)有鎖等待， 立馬執(zhí)行4，就會(huì)馬上產(chǎn)生死鎖 4. 事務(wù)2: update temp set name='bbb' where id=1;
在事務(wù)性數(shù)據(jù)庫(kù)中，死鎖是個(gè)經(jīng)典的問(wèn)題，但只要發(fā)生的頻率不高，則死鎖問(wèn)題不需要太過(guò)擔(dān)心。死鎖應(yīng)該非常少發(fā)生，若經(jīng)常發(fā)生，則系統(tǒng)是不可用。 查看死鎖的方法有兩種：
減少死鎖發(fā)生的方法：
六、事務(wù) 事務(wù)的主要目的了：事務(wù)會(huì)把數(shù)據(jù)庫(kù)從一種一致?tīng)顟B(tài)轉(zhuǎn)換為另一種一致?tīng)顟B(tài)。在數(shù)據(jù)庫(kù)提交工作是，可以確保要么所有修改都已近保存了，要么所有修改都不保存。 InnoDB 存儲(chǔ)引擎中的事務(wù)完全符合 ACID 的特性：
事務(wù)可由一條非常簡(jiǎn)單的 SQL 語(yǔ)句組成，也可以有一組復(fù)雜的 SQL 組成。事務(wù)是訪問(wèn)并更新數(shù)據(jù)庫(kù)中各種數(shù)據(jù)項(xiàng)的一個(gè)程序執(zhí)行單元，在事務(wù)中的操作，要么都做修改，要么都不做這就是事務(wù)的目的。 事務(wù) ACID 的特性： A (Atomicity)，原子性： 指整個(gè)數(shù)據(jù)庫(kù)事務(wù)是不可分割的工作單位。只有使事務(wù)中所有的數(shù)據(jù)庫(kù)操作都執(zhí)行成功，才算整個(gè)事務(wù)成功。事務(wù)中任何一個(gè) SQL 語(yǔ)句執(zhí)行失敗，已近執(zhí)行成功的 SQL 語(yǔ)句也必須撤銷(xiāo)。數(shù)據(jù)庫(kù)狀態(tài)應(yīng)該退回到執(zhí)行事務(wù)前的狀態(tài)。 比如 ATM 取款流程：
整個(gè)過(guò)程都視為原子操作，某一個(gè)步驟失敗了，都不能進(jìn)行下一步。 C （consistency），一致性： 一致性定義基本可以理解為是事務(wù)對(duì)數(shù)據(jù)完整性約束的遵循。這些約束可能包括主鍵約束、外鍵約束或是一些用戶(hù)自定義約束。事務(wù)執(zhí)行的前后都是合法的數(shù)據(jù)狀態(tài)，不會(huì)違背任何的數(shù)據(jù)完整性，這就是“一致”的意思。事務(wù)是一致性的單位，如果事務(wù)中某個(gè)動(dòng)作失敗了，系統(tǒng)就可以自動(dòng)撤銷(xiāo)事務(wù)——返回事務(wù)初始化的狀態(tài)。 I (isolation)，隔離性： 隔離性還有其他的稱(chēng)呼，如并發(fā)控制、可串行化、鎖等。事務(wù)的隔離性要求每個(gè)讀寫(xiě)事務(wù)的對(duì)象對(duì)其他事務(wù)的操作對(duì)象能相互分離，即該事務(wù)提交前對(duì)其他事務(wù)都不可見(jiàn)。 D（durability），持久性： 事務(wù)一旦提交，其結(jié)果就是永久性的(寫(xiě)入了磁盤(pán))，即使發(fā)生宕機(jī)等故障，數(shù)據(jù)庫(kù)也能將數(shù)據(jù)恢復(fù)。需要注意的是，只能從事務(wù)本身的角度來(lái)保證結(jié)果的永久性。 例如：在事務(wù)提交后，所有的變化都是永久的，即使當(dāng)數(shù)據(jù)庫(kù)因?yàn)楸罎⒍枰謴?fù)時(shí)，也能保證恢復(fù)后提交的數(shù)據(jù)都不會(huì)丟失。 但若不是數(shù)據(jù)庫(kù)本身發(fā)生故障，而是一些外部的原因，如 RAID 卡損壞，自然災(zāi)害等原因?qū)е聰?shù)據(jù)庫(kù)發(fā)生問(wèn)題，那么所有提交的數(shù)據(jù)可能都會(huì)丟失。 因此持久性保證事務(wù)系統(tǒng)的高可靠性，而不是高可用性。對(duì)于高可用性的實(shí)現(xiàn)，事務(wù)本身并不能保證，需要一些系統(tǒng)來(lái)共同配合來(lái)完成。 事務(wù)的隔離性由鎖來(lái)實(shí)現(xiàn)。原子性，一致性，持久性通過(guò)數(shù)據(jù)庫(kù)的 redo log 和 undo log 來(lái)完成，redo log 成為重做日志，用來(lái)保證事務(wù)的原子性和持久性。undo log 用來(lái)保證事務(wù)的一致性。 redo 和 undo 的作用都可以視為是一種恢復(fù)操作，redo 恢復(fù)提交事務(wù)修改的頁(yè)操作，而 undo 回滾行記錄到某個(gè)特定版本。因此兩者記錄的內(nèi)容不同，redo 通常是物理日志，記錄的是頁(yè)的物理修改操作，undo 是邏輯日志，根據(jù)每行記錄進(jìn)行記錄。 1）redo 重做日志(redo log)用來(lái)實(shí)現(xiàn)事務(wù)的持久性，即事務(wù) ACID 中的 D。其中兩部分組成：
InnoDB 是事務(wù)的存儲(chǔ)引擎，其通過(guò) Force Log at Commit 機(jī)制實(shí)現(xiàn)事務(wù)的持久性，即當(dāng)事務(wù)提交(commit)時(shí)，必須先將該事務(wù)的所有日志寫(xiě)入到重做日志文件進(jìn)行持久化，待事務(wù)的 commit 操作完成才算完成。 這里的日志是指重做日志，在 InnoDB 存儲(chǔ)引擎中，由兩部分組成，即 redo log 和 undo log。 redo log 用來(lái)保證事務(wù)的持久性，undo log 用來(lái)幫助事務(wù)回滾及多版本控制（mvcc）的功能，redo log 基本上都是順序?qū)懙?，在?shù)據(jù)庫(kù)運(yùn)行不需要對(duì) redo log 的文件進(jìn)行讀取操作。而 undo log 是需要進(jìn)行隨機(jī)讀寫(xiě)的。 為了確保每次日志都寫(xiě)入重做日志文件，在每次都將重做日志緩沖寫(xiě)入重做日志文件后，InnoDB 存儲(chǔ)引擎都需要調(diào)用一次 fsync 操作。 由于重做日志文件打開(kāi)并沒(méi)有使用 O_DIRECT 選項(xiàng)，因此重做日志緩沖先寫(xiě)入文件系統(tǒng)緩沖。為了確保重做日志寫(xiě)入磁盤(pán)，必須進(jìn)行一次 fsync 操作。由于 fsync 的效率取決于磁盤(pán)的性能，因此磁盤(pán)的性能決定了事務(wù)的提交的性能，也就是數(shù)據(jù)庫(kù)的性能。 InnoDB 存儲(chǔ)引擎允許用戶(hù)手工非持久性的情況發(fā)生，以此提高數(shù)據(jù)庫(kù)的性能。即當(dāng)事務(wù)提交時(shí)，日志不寫(xiě)入重做日志文件，而是等待一個(gè)時(shí)間周期后再執(zhí)行 fsync 操作。 用參數(shù) innodb_flush_log_at_trx_commit 用來(lái)控制重做日志刷新到磁盤(pán)的策略。該參數(shù)默認(rèn)值為1。 改參數(shù)可以設(shè)置值為 0、1、2
2）undo 重做日志記錄了事務(wù)的行為，可以很好地通過(guò)其對(duì)頁(yè)進(jìn)行“重做”操作，但是事務(wù)有時(shí)還需要進(jìn)行回滾操作，這時(shí)就需要 undo。因此在對(duì)數(shù)據(jù)庫(kù)進(jìn)行修改時(shí)，InnoDB 存儲(chǔ)引擎不但會(huì)產(chǎn)生 redo，還會(huì)產(chǎn)生一定量的 undo。這樣如果用戶(hù)執(zhí)行的事務(wù)或語(yǔ)句由于原因失敗了，又或者用戶(hù)用一條 rollback 語(yǔ)句請(qǐng)求回滾，就可以利用這些 undo 信息將數(shù)據(jù)回滾到修改之前的樣子。 redo 存放在重做日志文件中，與 redo 不同，undo 存放在數(shù)據(jù)庫(kù)內(nèi)部的一個(gè)特殊段（segment）中，這個(gè)段稱(chēng)為 undo 段 。undo 段位于共享表空間內(nèi)。 undo 是邏輯日志，因此只是將數(shù)據(jù)庫(kù)邏輯地恢復(fù)到原來(lái)的樣子。所有修改都被邏輯地取消了，但是數(shù)據(jù)結(jié)構(gòu)和頁(yè)本身在回滾之后可能大不相同。這是因?yàn)樵诙嘤脩?hù)并發(fā)系統(tǒng)中，可能會(huì)有數(shù)十，數(shù)百甚至數(shù)千個(gè)并發(fā)事務(wù)。數(shù)據(jù)庫(kù)的主要任務(wù)就是協(xié)調(diào)對(duì)數(shù)據(jù)記錄的并發(fā)訪問(wèn)。比如，一個(gè)事務(wù)在修改當(dāng)前一個(gè)頁(yè)中某幾條記錄，同時(shí)還有別的事務(wù)在對(duì)同一個(gè)頁(yè)中另幾條記錄進(jìn)行修改。因此，不能將一個(gè)頁(yè)回滾到事務(wù)開(kāi)始的樣子，因?yàn)檫@樣會(huì)影響其他事務(wù)正在進(jìn)行的工作。 undo 除了回滾操作，undo 的另一個(gè)作用是mvcc，即在InnoDB 存儲(chǔ)引擎中mvcc 的實(shí)現(xiàn)是通過(guò)undo 來(lái)完成。當(dāng)用戶(hù)讀取一行記錄時(shí)，若該記錄已近被其他事務(wù)占用，當(dāng)前事務(wù)可以通過(guò)undo 讀取之前的行版本信息，以此實(shí)現(xiàn) 非鎖定讀取。 最重要的一點(diǎn)是，undo log 會(huì)產(chǎn)生redo log ，也就是undo log 的產(chǎn)生會(huì)伴隨著redo log 的產(chǎn)生，這是因?yàn)閡ndo log 也需要持久性的保護(hù)。 undo 存儲(chǔ)管理 InnoDB 存儲(chǔ)引擎有 rollback segment ，每個(gè)回滾段中記錄了 1024 個(gè)undo log segment , 而在每個(gè) undo log segment 段中進(jìn)行 undo 頁(yè)的申請(qǐng)。 InnoDB 支持最大128 個(gè)(回滾段)rollback segment ，故其支持同時(shí)在線的事務(wù) 128 * 1024，但是這些 rollback segment 都存儲(chǔ)于共享表空間中?？梢酝ㄟ^(guò)參數(shù)對(duì) rollback segment 做進(jìn)一步的設(shè)置。這些參數(shù)包括：
innodb_undo_directory innodb_undo_logs innodb_undo_tablespaces innodb_undo_directory 用于設(shè)置 rollback segment 文件所在的路徑。這意味著 rollback segment 可以放在共享表空間以外的位置，即可以設(shè)置為獨(dú)立表空間。該參數(shù)的默認(rèn)值為”.”，表示當(dāng)前 InnoDB 存儲(chǔ)引擎的目錄。 innodb_undo_logs 用來(lái)設(shè)置 rollback segment 的個(gè)數(shù)，默認(rèn)值為 128。 innodb_undo_tablespaces 用來(lái)設(shè)置構(gòu)成 rollback segment 文件的數(shù)量，這樣 rollback segment 可以較為平均地分布在多個(gè)文件。設(shè)置改參數(shù)后，會(huì)在路勁innodb_undo_directory 看到 undo 為前綴的文件，該文件就代表 rollback segment 文件。 數(shù)據(jù)庫(kù)初始化后，innodb_undo_tablespaces 就再也不能被改動(dòng)了；默認(rèn)值為0，表示不獨(dú)立設(shè)置undo的tablespace，默認(rèn)記錄到ibdata中；否則，則在undo目錄下創(chuàng)建這么多個(gè)undo文件，例如假定設(shè)置該值為4，那么就會(huì)創(chuàng)建命名為undo001~undo004的undo tablespace文件，每個(gè)文件的默認(rèn)大小為10M。修改該值會(huì)導(dǎo)致Innodb無(wú)法完成初始化，數(shù)據(jù)庫(kù)無(wú)法啟動(dòng)，但是另兩個(gè)參數(shù)可以修改。 3）purge delete 和 update 操作可能并不直接刪除原有的數(shù)據(jù)。
例如執(zhí)行： 表z 上列a 有聚集索引，列表上有輔助索引，對(duì)于上述的delete 操作，在undo log 將主鍵列等于1 的記錄delete flag 設(shè)置為1 ，記錄并沒(méi)有立即刪除，記錄還是存在B+樹(shù)種，其次，對(duì)輔助索引上a 等于1 ，b等于1 的記錄同樣沒(méi)有做任何處理，甚至沒(méi)有產(chǎn)生undo log 。而真正刪除這行記錄的刪除操作其實(shí)被“延時(shí)”了，最終在purge 操作中完成?！?/span> purge 用于最終完成delete 和 update 操作。因?yàn)镮nnoDB 存儲(chǔ)引擎支持MVCC，所以記錄不能再事務(wù)提交時(shí)立即進(jìn)行處理。這時(shí)其他事務(wù)可能正在引用這行，故InnoDB 存儲(chǔ)引擎需要保持記錄之前的版本。而是否可以刪除該條記錄通過(guò)purge 來(lái)進(jìn)行判斷。若該行記錄已不被任何其他事務(wù)引用，那么就可以進(jìn)行真正的delete 操作。可見(jiàn)，purge 操作是清理之前的delete 和 update 操作， 將上述操作 “最終” 完成。而實(shí)際執(zhí)行的操作為delete 操作，清理之前行記錄的版本。 4）group commit 5.6 版本之前的兩次提交 ： 若事務(wù)為非只讀事務(wù)，則每次事務(wù)提交時(shí)需要進(jìn)行一次fsync 操作，以此保證重做日志都已近寫(xiě)入磁盤(pán)。當(dāng)數(shù)據(jù)庫(kù)發(fā)生宕機(jī)時(shí)，可以通過(guò)重做日志進(jìn)行恢復(fù)。雖然固態(tài)硬盤(pán)的出現(xiàn)提高了磁盤(pán)的性能，然后磁盤(pán)的rsync 性能是有限的。為了提高磁盤(pán)fsync 的效率，數(shù)據(jù)庫(kù)提供了group commit 的功能，即一次fsync 可以刷新確保多個(gè)事務(wù)日志被寫(xiě)入文件?！?/span> 對(duì)于InnoDB 存儲(chǔ)引擎來(lái)說(shuō)， 事務(wù)提交時(shí)會(huì)進(jìn)行兩個(gè)階段的操作：
步驟 1 相對(duì)步驟 2 是一個(gè)較慢的過(guò)程，這是因?yàn)榇鎯?chǔ)引擎需要與磁盤(pán)打交道。但當(dāng)有事務(wù)進(jìn)行這個(gè)過(guò)程是，其他事務(wù)可以進(jìn)行步驟 1 的 操作，正在提交的事務(wù)完成提交操作，再次進(jìn)行步驟 2 時(shí)，可以將多個(gè)事務(wù)的重做日志通過(guò)一次fsync 刷新到磁盤(pán)，這樣就大大減少了磁盤(pán)的壓力，從而提高了數(shù)據(jù)庫(kù)的整體性能。對(duì)于寫(xiě)入或更新較為頻繁的操作，group commit 的效果尤為明顯。 二段提交流程：
組提交 ： 5.6 引入了組提交，并將提交過(guò)程分成 Flush stage、Sync stage、Commit stage 三個(gè)階段。
每個(gè) Stage 階段都有各自的隊(duì)列，從而使每個(gè)會(huì)話的事務(wù)進(jìn)行排隊(duì)，提高并發(fā)性能。 如果當(dāng)一個(gè)線程注冊(cè)到一個(gè)空隊(duì)列時(shí)，該線程就做為該隊(duì)列的 leader，后注冊(cè)到該隊(duì)列的線程均為 follower，后續(xù)的操作，都由 leader 控制隊(duì)列中 follower 行為。 參考網(wǎng)址：https://www./Linux/2018-01/150187.htm 參數(shù) binlog_max_flush_queue_time 用來(lái)控制 flush 階段中等待的時(shí)間，即使之前的一組事務(wù)完成提交，當(dāng)前一組的事務(wù)也不馬上進(jìn)去 sync 階段，而是至少需要等待一段時(shí)間。 這樣做的好處是 group commit 的數(shù)量更多，然而這也可能會(huì)導(dǎo)致事務(wù)的相應(yīng)時(shí)間變慢。該參數(shù)的默認(rèn)值為 0，且推薦設(shè)置依然為 0。除非用戶(hù)的 MySQL 數(shù)據(jù)庫(kù)系統(tǒng)中有著大量的連接，并且不斷地在進(jìn)行事務(wù)的寫(xiě)入或更新操作。
注：任何參數(shù)都不要隨意設(shè)置，看到別人設(shè)置參數(shù)能解決，為什么我的環(huán)境設(shè)置就報(bào)錯(cuò)了，看官方的改參數(shù)注意事項(xiàng)，各種版本的注意事項(xiàng)，在去相應(yīng)測(cè)試環(huán)境實(shí)驗(yàn)一下。 在 MySQLl 命令行的默認(rèn)設(shè)置下，事務(wù)都是自動(dòng)提交（auto commit）的，即執(zhí)行 SQL 語(yǔ)句就會(huì)馬上執(zhí)行 commit 操作。 用戶(hù)可以使用那些事務(wù)控制語(yǔ)句：
例： mysql> create table u (a int primary key); Query OK, 0 rows affected (0.01 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into u select 1; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> savepoint u1; Query OK, 0 rows affected (0.00 sec) mysql> insert into u select 2; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> savepoint u2; Query OK, 0 rows affected (0.00 sec) mysql> select * from u\G; ****** 1. row ****** a: 1 ****** 2. row ****** a: 2 2 rows in set (0.00 sec) mysql> release savepoint u1; Query OK, 0 rows affected (0.00 sec) # 回到了第一次插入數(shù)據(jù)的時(shí)候 mysql> insert into u select 2; ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY' mysql> rollback to savepoint u2; ERROR 1305 (42000): SAVEPOINT u2 does not exist mysql> select * from u; +---+ | a | +---+ | 1 | | 2 | +---+ 2 rows in set (0.00 sec) # 這時(shí)候發(fā)現(xiàn)了，rollback to savepoint u1了， 后面的u2 的 事務(wù)已近不存在了， 但是兩條記錄的數(shù)據(jù)還在。 mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from u; Empty set (0.00 sec) 在上面的列子中，雖然在發(fā)生重復(fù)錯(cuò)誤后用戶(hù)通過(guò) rollback to save point u1 命令回滾到了保存點(diǎn) u1，但是事務(wù)此時(shí)沒(méi)有結(jié)束。在運(yùn)行命令 rollback 后，事務(wù)才會(huì)完整地回滾?！ ?/span> InnoDB 存儲(chǔ)引擎中的事務(wù)都是原子的，這說(shuō)明下兩種情況： 構(gòu)成事務(wù)的每天語(yǔ)句都會(huì)提交（成為永久），或者所有語(yǔ)句都回滾。這種保護(hù)還延伸到單個(gè)的語(yǔ)句。一條語(yǔ)句要么完全成功。要么完全回滾（注意，這里說(shuō)的是語(yǔ)句回滾）。 因此一條語(yǔ)句失敗并拋出異常時(shí)，并不會(huì)導(dǎo)致先前已近執(zhí)行的語(yǔ)句自動(dòng)回滾。所有的執(zhí)行都會(huì)得到保留，必須由用戶(hù)自己來(lái)決定是否對(duì)其進(jìn)行提交或回滾的操作。 rollback to savepoint 命令并不真正地結(jié)束事務(wù)。 commit 和 rollback 才是真正的結(jié)束一個(gè)事務(wù) 以下這些 SQL 語(yǔ)句會(huì)產(chǎn)品一個(gè)隱式的提交操作即執(zhí)行完這些語(yǔ)句后，會(huì)有一個(gè)隱式的 commit 操作： DDL 語(yǔ)句： 用來(lái)隱式修改 MySQL 架構(gòu)的操作： 管理語(yǔ)句： 注: 我發(fā)現(xiàn) sql server 的數(shù)據(jù)庫(kù)有些 ddl 也是可以回滾的。這和 InnoDB 存儲(chǔ)引擎，oracle 這些數(shù)據(jù)庫(kù)完全不同。 truncate table 演示： mysql> insert into u select 1; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into u select 2; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from u; +---+ | a | +---+ | 1 | | 2 | +---+ 2 rows in set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> truncate table u; Query OK, 0 rows affected (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from u; Empty set (0.00 sec) 由于 InnoDB 存儲(chǔ)引擎是支持事務(wù)的，因此 InnoDB 存儲(chǔ)引擎的應(yīng)用需要在考慮每秒請(qǐng)求數(shù)（transaction per second ，TPS） 計(jì)算 TPS 的方法時(shí)( com_commit + com_rollback）/time 。但是利用這種方法進(jìn)行計(jì)算的前提是： 所有的事務(wù)必須都是顯示提交的，如果存在隱式提交和回滾（默認(rèn)autocommit =1 ），不會(huì)計(jì)算到com_commit 和 com_rollback 變量中。如：
MySQL 數(shù)據(jù)庫(kù)中另外還有兩個(gè)參數(shù) handler_commit 和 handler_rollback 用于事務(wù)的統(tǒng)計(jì)操作。可以很好的用來(lái)統(tǒng)計(jì) InnoDB 存儲(chǔ)引擎顯式和隱式的事務(wù)提交操作。 在 InnoDB Plugin 中這兩個(gè)參數(shù)的表現(xiàn)有些“怪異”，如果用戶(hù)的程序都是顯示控制事務(wù)的提交和回滾，那么可以通過(guò)com_commit 和 com_rollback 進(jìn)行統(tǒng)計(jì)。 SQL 標(biāo)準(zhǔn)定義的四個(gè)隔離級(jí)別為:
sql server 和oracle 默認(rèn)的隔離級(jí)別是 READ COMMITED。
不可能重復(fù)讀和幻讀的區(qū)別： 很多人容易搞混不可重復(fù)讀和幻讀，確實(shí)這兩者有些相似。但不可重復(fù)讀重點(diǎn)在于 update 和 delete，而幻讀的重點(diǎn)在于 insert。 在 InnoDB 引擎中，可以使用一下命令來(lái)設(shè)置當(dāng)前會(huì)話和全局的事務(wù)的隔離級(jí)別： mysql> help isolation; Name: 'ISOLATION' Description: Syntax: SET [GLOBAL | SESSION] TRANSACTION transaction_characteristic [, transaction_characteristic] ... transaction_characteristic: { ISOLATION LEVEL level | READ WRITE | READ ONLY } level: { REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE } 如果想在 MySQL 數(shù)據(jù)啟動(dòng)時(shí)就設(shè)置事務(wù)的默認(rèn)隔離級(jí)別，那就需要修改 MySQL 的配置文件 my.cnf 在 [mysqld] 中添加如下行： [mysqld] transaction-isolation = REPEATABLE-READ 查看當(dāng)前會(huì)話的事務(wù)隔離級(jí)別，可以使用： mysql> select @@tx_isolation\G; ********** 1. row ********** @@tx_isolation: REPEATABLE-READ 1 row in set, 1 warning (0.00 sec) 查看全局的事務(wù)隔離級(jí)別，可以使用： mysql> select @@global.tx_isolation\G; ******** 1. row ******** @@global.tx_isolation: REPEATABLE-READ 1 row in set, 1 warning (0.00 sec) 在循環(huán)中提交 用存儲(chǔ)過(guò)程模擬一下： create table t1 (a int ,b char(100)); 創(chuàng)建load1 delimiter // create procedure load1 (count INT UNSIGNED) begin declare s int unsigned default 1; declare c char(80) default repeat('a',80); while s <= count do insert into t1 select null,c; commit; set s = s+1; end while; end // delimiter ; 創(chuàng)建load2 delimiter // create procedure load2 (count int unsigned) begin declare s int unsigned default 1; declare c char(80) default repeat('a',80); while s <= count do insert into t1 select null,c; set s = s+1; end while; end // delimiter ; 創(chuàng)建load3 delimiter // create procedure load3(count int unsigned) begin declare s int unsigned default 1; declare c char(80) default repeat('a',80); start transaction; while s <= count do insert into t1 select null,c; set s = s+1; end while; commit; end // delimiter ; 比較這三個(gè)存儲(chǔ)過(guò)程執(zhí)行時(shí)間： mysql> call load1(20000); Query OK, 0 rows affected (16.12 sec) mysql> truncate table t1; Query OK, 0 rows affected (0.01 sec) mysql> call load2(20000); Query OK, 1 row affected (16.06 sec) mysql> truncate table t1; Query OK, 0 rows affected (0.01 sec) mysql> call load3(20000); Query OK, 0 rows affected (0.51 sec)
注：mysql 默認(rèn)是自動(dòng)提交的，load1 和 load2 沒(méi)執(zhí)行一次都會(huì)自動(dòng)提交。 顯然，load3 方法要快的多，這是因?yàn)槊恳淮翁峤欢家獙?xiě)一次重做日志，存儲(chǔ)過(guò)程 load1 和 load2 實(shí)際寫(xiě)了 20000 次重做日志文件，而對(duì)于存儲(chǔ)過(guò)程 load3 來(lái)說(shuō)，實(shí)際只寫(xiě)了一次。 長(zhǎng)事務(wù)就是執(zhí)行時(shí)間較長(zhǎng)的事務(wù)。比如對(duì)于銀行系統(tǒng)的數(shù)據(jù)庫(kù)，沒(méi)過(guò)一個(gè)階段可能需要更新對(duì)應(yīng)賬戶(hù)的利息。如果對(duì)應(yīng)賬號(hào)的數(shù)量非常大，例如對(duì)有 1 億用戶(hù)的表 account ，需要執(zhí)行以下列語(yǔ)句; 這是這個(gè)事務(wù)可能需要非常長(zhǎng)的時(shí)間來(lái)完成。可能需要 1 個(gè)小時(shí)，也可能 4、5 個(gè)小時(shí)，這取決于數(shù)據(jù)庫(kù)的硬件配置。DBA 和開(kāi)發(fā)人員本身能做的事情非常少。 然而，由于事務(wù) ACID 的特性，這個(gè)操作被封裝在一個(gè)事務(wù)中完成。這就產(chǎn)生了一個(gè)問(wèn)題，在執(zhí)行過(guò)程中，當(dāng)數(shù)據(jù)庫(kù)或操作系統(tǒng)，硬件等發(fā)生問(wèn)題是，重新開(kāi)始事務(wù)的代價(jià)變得不可接受。 數(shù)據(jù)庫(kù)需要回滾所有已近發(fā)生的變化，而這個(gè)過(guò)程可能比產(chǎn)生這些變化的時(shí)間還要長(zhǎng)。因此，對(duì)于長(zhǎng)事務(wù)的問(wèn)題，有時(shí)可以通過(guò)轉(zhuǎn)化為小批量的事務(wù)來(lái)進(jìn)行處理。當(dāng)事務(wù)發(fā)生錯(cuò)誤是，只需要回滾一部分?jǐn)?shù)據(jù)，然后接著上次已完成的事務(wù)繼續(xù)進(jìn)行。 注：以上所有操作全是在 MySQL 5.7.24 版本 作者：keme |
|
|
