1 鎖概念

1.1 什么是鎖

鎖是數(shù)據(jù)庫系統(tǒng)區(qū)別于文件系統(tǒng)的一個關(guān)鍵特性。數(shù)據(jù)庫系統(tǒng)使用鎖是為了支持對共享資源進行并發(fā)訪問，提供數(shù)據(jù)的完整性和一致性。例如：操作緩沖池中的LRU列表，刪除、添加、移動LUR列表中的元素。?

對于任何一種數(shù)據(jù)庫來說都需要有相應的鎖定機制，所以MySQL自然也不能例外。MySQL數(shù)據(jù)庫由于其自身架構(gòu)的特點，存在多種數(shù)據(jù)存儲引擎，每種存儲引擎所針對的應用場景特點都不太一樣，為了滿足各自特定應用場景的需求，每種存儲引擎的鎖定機制都是為各自所面對的特定場景而優(yōu)化設(shè)計，所以各存儲引擎的鎖定機制也有較大區(qū)別。MySQL常用存儲引擎(MyISAM,InnoDB)用了兩種類型（級別）的鎖定機制：表級鎖定，行級鎖定。

1、表級鎖?
表級別的鎖定是MySQL各存儲引擎中最大顆粒度的鎖定機制。該鎖定機制最大的特點是實現(xiàn)邏輯非常簡單，帶來的系統(tǒng)負面影響最小。所以獲取鎖和釋放鎖的速度很快。由于表級鎖一次會將整個表鎖定，所以可以很好的避免困擾我們的死鎖問題。?

?當然，鎖定顆粒度大所帶來最大的負面影響就是出現(xiàn)鎖定資源爭用的概率也會最高，致使并大度大打折扣。

使用表級鎖定的主要是MyISAM，MEMORY，CSV等一些非事務性存儲引擎。

2、行級鎖?
行級鎖定最大的特點就是鎖定對象的顆粒度很小，也是目前各大數(shù)據(jù)庫管理軟件所實現(xiàn)的鎖定顆粒度最小的。由于鎖定顆粒度很小，所以發(fā)生鎖定資源爭用的概率也最小，能夠給予應用程序盡可能大的并發(fā)處理能力而提高一些需要高并發(fā)應用系統(tǒng)的整體性能。?

?雖然能夠在并發(fā)處理能力上面有較大的優(yōu)勢，但是行級鎖定也因此帶來了不少弊端。由于鎖定資源的顆粒度很小，所以每次獲取鎖和釋放鎖需要做的事情也更多，帶來的消耗自然也就更大了。此外，行級鎖定也最容易發(fā)生死鎖。?

?使用行級鎖定的主要是InnoDB存儲引擎。

總結(jié)如下：?
表級鎖：開銷小，加鎖快；不會出現(xiàn)死鎖；鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低；?
行級鎖：開銷大，加鎖慢；會出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高；

例如：

?

上面如圖只是對myisam 表修改一行記錄 ，其他insert 操作 就需要等待上個update 語句執(zhí)行完成，再執(zhí)行insert 操作，這時候就會產(chǎn)生表鎖。

1.2 InnoDB 鎖的類型

InnoDB 存儲引擎實現(xiàn)了如下兩種標準的行級鎖：?
1、共享鎖（S Lock），允許事務讀一行數(shù)據(jù)。但不能修改，增加，刪除數(shù)據(jù)。?
2、排他鎖 （X Lock），獲準排他鎖的事務既能讀數(shù)據(jù)，又能修改數(shù)據(jù)。?

如果一個事務 t1 已近獲得了行 r 的共享鎖，那么另外的事務 t2 可以獲得行 r 的共享鎖，因為讀取并沒有改變行 r 的數(shù)據(jù)，稱這種情況為鎖兼容（Lock Compatible）。但若有其他的事務想獲得行 r 的排它鎖，則必須等待事務t1,t2 釋放行 r 的共享鎖——這種情況稱為鎖不兼容(confilict)

此外，InnoDB存儲引擎支持多粒度（granular）鎖定，這種鎖定允許事務在行級上的鎖和表級上的鎖同時存在。為了支持在不同粒度上進行加鎖操作，InnoDB 存儲引擎支持了一種額外的鎖方式，稱為意向鎖（Intention Lock）。意向鎖是將鎖定的對象分為多個層次，意向鎖意味著事務希望在更細粒度上進行加鎖。

?

如上圖，若將上鎖的對象看成一顆樹，那么最下層的對象（行記錄）上鎖，也就是對最細粒度的對象進行上鎖，那么首先需要對粗粒度的對象上鎖。如果需要對頁上的記錄r進行上 X鎖，那么分別需要對數(shù)據(jù)庫A、表、頁上意向鎖，最后對記錄r上 X 鎖，若其中任何一個部分導致等待，那么該操作需要等待粗粒度鎖的完成。舉例來說，在對記錄r 加 X鎖之前，已近有事務對表1 進行了 S 表鎖，那么表1 上已存在 S 鎖，之后事務需要對記錄r 表1 上加 IX ， 由于不兼容，所以該事務，需要等待表鎖操作的完成。?

?InnoDB 存儲引擎支持意向鎖設(shè)計比較簡練，其意向鎖即為表級別的鎖，設(shè)計目的主要是為了在一個事務中揭示下一行將被請求的鎖類型。其支持兩種意向鎖：?

1、意向共享鎖（ intention shared lock, Is），事務有意向?qū)Ρ碇械哪承┬屑庸蚕礞i（S鎖）?
2、意向排它鎖（intention exclusive lock,IX），事務有意向?qū)Ρ碇械哪承┬屑优潘i（X鎖）?

意向鎖是有數(shù)據(jù)引擎自己維護的，用戶無法手動操作意向鎖，在為數(shù)據(jù)行加共享 / 排他鎖之前，InooDB 會先獲取該數(shù)據(jù)行所在在數(shù)據(jù)表的對應意向鎖。

由于InnoDB 存儲引擎支持的是行級別的鎖，因此意向鎖其實不會阻塞除全表掃以外的任何請求。

?表級意向鎖與行鎖的兼容性?
S：共享鎖?
X：排它鎖?
IS：意向共享鎖?
IX：意向排它鎖

-	IS	IX	S	X
IS	兼容(compatible)	兼容	兼容	不兼容(conflict)
IX	兼容	兼容	不兼容	不兼容
S	兼容	不兼容	兼容	不兼容
X	不兼容	不兼容	不兼容	不兼容

?

?排它鎖（X）：與任何鎖都不兼容。?
共享鎖（S）：只兼容共享鎖和意向共享鎖?
意向鎖（IS,IX）: 互相兼容，行級別的鎖只兼容共享鎖

1.3 一致性鎖定讀

用戶有時候需要顯示地對數(shù)據(jù)庫讀取操作進行加鎖以保證數(shù)據(jù)邏輯的一致性。而這要求數(shù)據(jù)庫支持加鎖語句，即使是對于select 的只讀操作。 InnoDB 存儲引擎對于 select 語句支持兩種一致性的鎖定讀 操作：

select ... for update; select ... lock in share mode;

　　

select … for update 對讀取的行記錄加一個 X 鎖，其他事務不能對已鎖定的行加上任何鎖。?
select … lock in share mode 對讀取的行記錄加一個S 鎖，其他事務可以向被鎖定的加S 鎖，但是如果加X鎖，則會組賽。?
此外 select ... for update , select ... lock in share mode 必須在一個事務中，當事務提交了，鎖也就釋放了。因此在使用上訴兩句select 鎖定語句時，務必加上BEGIN, START TRANSACTION 或者 SET AUTOCOMMIT=0.

?

1.4 一致性非鎖定讀

在默認的隔離級別下一致讀是指InnoDB在多版本控制中在事務的首次讀時產(chǎn)生一個鏡像，在首次讀時間點之前其他事務提交的修改可以讀取到，而首次讀時間點之后其他事務提交的修改或者是未提交的修改都讀取不到

?

唯一例外的情況是在首次讀時間點之前的本事務未提交的修改數(shù)據(jù)可以讀取到

在讀取提交數(shù)據(jù)隔離級別下，一致讀的每個讀取操作都會有自己的鏡像

一致讀操作不會施加任何的鎖，所以就不會阻止其他事務的修改動作

比如最經(jīng)典的mysqldump --single-transaction備份的時候就是把當前的事務隔離級別改變?yōu)榭芍貜妥x并開啟一個一致性事務的快照 , 就是一致性非鎖定讀

?

一致讀在某些DDL語句下不生效：?
1、碰到drop table語句時，由于InnoDB不能使用被drop的表，所以無法實現(xiàn)一致讀?
2、碰到alter table語句時，也無法實現(xiàn)一致讀?
3、當碰到insert into… select, update … select和create table … select語句時，在默認的事務隔離級別下，語句的執(zhí)行更類似于在讀取提交數(shù)據(jù)的隔離級別下

1.5 自增長與鎖

自增長在數(shù)據(jù)庫中非常常見的一種屬性，也是很多DBA或開發(fā)人員首選主鍵方式。在InnoDB 存儲引擎的內(nèi)存結(jié)構(gòu)中，對每個含有自增長值的表都有一個自增長計數(shù)器。

插入操作會依據(jù)這個自增長的計數(shù)器加1 賦予自增長列。這個實現(xiàn)方式稱作AUTO-INC Locking（自增鎖）。 這種自增鎖是采用一種特殊的表鎖機制，為了提高插入的性能，鎖不是在一個事務完成后才釋放，而是在完成對自增長值插入的sql 語句后立即釋放。?
AUTO-INC Locking 從一定程度上提高了并發(fā)插入的效率，但還是存在一些性能上的問題。

1、 首先，對于有自增長值的列的并發(fā)插入性能較差，事務必須等待前一個插入完成。?
2、其次，對于insert …select 的大數(shù)據(jù)量的插入會影響插入的性能，因為另一個事務中插入會被阻塞。?
Innodb_autoinc_lock_mode 來控制自增長的模式，改參數(shù)的默認值為1

InnoDB提供了一種輕量級互斥量的自增長實現(xiàn)機制，大大提高了自增長值插入的性能。提供參數(shù)innodb_autoinc_lock_mode來控制自增長鎖使用的算法，默認值為1。他允許你在可預測的自增長值和最大化并發(fā)插入操作之間進行權(quán)衡。

插入類型的分類：

插入類型	說明
insert-like	指所有的插入語句，例如：insert、replace、insert … select、replace… select、load data
simple inserts	指再插入前就確定插入行數(shù)的語句。例如：insert、replace等。注意：simple inserts不包含 insert … on duplicate key update 這類sql語句
bulk inserts	指在插入前不能確定得到插入行數(shù)的語句，例如：insert … select、 replace … select、load data
mixed-mode inserts	指插入中有一部分的值是自增長的，一部分是確定的。例如：insert into t1(c1, c2) values (1, ‘a(chǎn)’), (NULL, ‘b’), (5, ‘c’), (NULL,’d’); 也可以指 insert … on duplicate key update 這類sql語句

innodb_autoinc_lock_mode 在不同設(shè)置下對自增長的影響：?

innodb_autoinc_lock_mode = 0 ： MySQL 5.1.22版本之前自增長的實現(xiàn)方式，通過表鎖的AUTO-INC Locking方式

innodb_autoinc_lock_mode = 1（默認值）：?
對于『simple inserts』，該值會用互斥量（mutex）對內(nèi)存中的計數(shù)器進行累加操作。對于『bulk inserts』會用傳統(tǒng)的AUTO-INC Locking方式。這種配置下，如果不考慮回滾，自增長列的增長還是連續(xù)的。需要注意的是：如果已經(jīng)使用AUTO-INC Locking方式去產(chǎn)生自增長的值，而此時需要『simple inserts』操作時，還需要等待AUTO-INC Locking的釋放

innodb_autoinc_lock_mode = 2?
對于所有『insert-like』自增長的產(chǎn)生都是通過互斥量，而不是AUTO-INC Locking方式。這是性能最高的方式。但會帶來一些問題：

?

因為并發(fā)插入的存在，每次插入時，自增長的值是不連續(xù)的基于statement-base replication會出現(xiàn)問題?

因此，使用這種方式，任何情況下都需要使用row-base replication，這樣才能保證最大并發(fā)性能和replication的主從數(shù)據(jù)的一致?

2 行鎖的幾種算法

1、Record Lock ：單個行記錄上的鎖?
2、Gap Lock ：間隙鎖，鎖定一個范圍，但不包含記錄本身?
3、Next-Key Lock：Gap Lock + Record Lock ，鎖定一個范圍，并且鎖定記錄本身?
4、Insert Intention Locks：插入意向鎖

2.1 Record Lock

Record Lock 總是會去鎖住索引記錄， 如果InnoDB 存儲引擎表在建立的時候沒有設(shè)置任何一個索引，那么這是InnoDB 存儲引擎會使用隱式的主鍵來進行鎖定。?

行級鎖是施加在索引行數(shù)據(jù)上的鎖，比如SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE語句是在t.c1=10的索引行上增加鎖，來阻止其他事務對對應索引行的insert/update/delete操作。

行鎖總是在索引記錄上面加鎖，即使一張表沒有設(shè)置任何索引，InnoDB會創(chuàng)建一個隱藏的聚簇索引，然后在這個索引上加上行鎖。?

例如:

create table t (c1 int primary key); insert into t select 1; insert into t select 3; insert into t select 10;

　　

　

# 會話A start transaction; update t set c1=12 where c1 = 10 ;# 會話B: mysql> update t set c1=11 where c1=10; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 會阻止該事務對索引行上的修改

　　

當一個InnoDB表沒有任何索引時， 則行級鎖會施加在隱含創(chuàng)建的聚簇索引上，所以說當一條sql沒有走任何索引時，那么將會在每一條聚集索引后面加X鎖，這個類似于表鎖，但原理上和表鎖應該是完全不同的

例：

# 刪除表t的主鍵索引 alter table t drop primary key; 開啟會話1： start transaction; update t set c1=11 where c1=10;開啟會話2： start transaction; update t set c1=8 where c1=10; 這個時候發(fā)生了鎖等待， 這時候開啟會話3，鎖等待發(fā)生了什么： mysql> select * from sys.innodb_lock_waits\G; 如下截圖吧：

?

2.2 Gap Lock

當 我們用范圍條件而不是相等條件檢索數(shù)據(jù)，并請求共享或排他鎖時，InnoDB會給符合條件 的已有數(shù)據(jù)記錄的索引項加鎖；對于鍵值在條件范圍內(nèi)但并不存在的記錄，叫做“間隙（GAP)”， InnoDB也會對這個"間隙"加鎖

間隔鎖是施加在索引記錄之間的間隔上的鎖， 鎖定一個范圍的記錄、但不包括記錄本身,比如SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE語句，盡管有可能對c1字段來說當前表里沒有=15的值，但還是會阻止=15的數(shù)據(jù)的插入操作，是因為間隔鎖已經(jīng)把索引查詢范圍內(nèi)的間隔數(shù)據(jù)也都鎖住了?
間隔鎖的使用只在部分事務隔離級(可重復讀級)別才是生效的?

間隔鎖只會阻止其他事務的插入操作，就是只有insert 操作會產(chǎn)生GAP鎖， update 操作不會參數(shù)GAP 鎖

例:

# 創(chuàng)建keme1 測試數(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'); # 開啟三個session 窗口，兩個窗口模擬兩個事務， 另外一個窗口看 兩個事務發(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: 0session2: start transaction; mysql> insert into keme1 values (2,'bb'); # 這時候就有鎖等待了 select * from sys.innodb_lock_waits\G;

使用gap lock的前置條件：?

1、 事務隔離級別為REPEATABLE-READ， innodb_locks_unsafe_for_binlog參數(shù)為0，且sql走的索引為非唯一索引（無論是等值檢索還是范圍檢索）?
2 、事務隔離級別為REPEATABLE-READ， innodb_locks_unsafe_for_binlog參數(shù)為0，且sql是一個范圍的當前讀操作，這時即使不是非唯一索引也會加gap lock

Gap Lock 的作用是為了阻止多個事務將記錄插入到同一范圍內(nèi)，而這會導致幻讀問題的產(chǎn)生。?

可以通過兩種方式來關(guān)閉Gap Lock：?
1、將事務的隔離級別設(shè)置為READ COMMITTED?
2、將參數(shù)innodb_locks_unsafe_for_binlog 設(shè)置為1

2.3 Next-Key Lock

在默認情況下， mysql的事務隔離級別是可重復讀，并且innodb_locks_unsafe_for_binlog?
參數(shù)為0，這時默認采用next-key locks。?
所謂Next-Key Locks， 就是記錄鎖和間隔鎖的結(jié)合，即除了鎖住記錄本身，還要再鎖住索引之間的間隙。　　

?當掃描表的索引時，InnoDB以這種形式實現(xiàn)行級的鎖：遇到匹配的的索引記錄，在上面加上對應的 S 鎖或 X 鎖。因此，行級鎖實際上是索引記錄鎖。如果一個事務擁有索引上記錄 r 的一個 S 鎖或 X 鎖，另外的事務無法立即在 r 記錄索引順序之前的間隙上插入一條新的記錄。

假設(shè)有一個索引包含值：10，11，13和20。下列的間隔上都可能加上一個Next-Key 鎖（左開右閉）

?

(negative infinity, 10] (10, 11] (11, 13] (13, 20] (20, positive infinity)

在最后一個區(qū)間中，Next-Key鎖 鎖定了索引中的最大值到 正無窮。?
默認情況下，InnoDB啟用 RR 事務隔離級別。此時，InnoDB在查找和掃描索引時會使用Next-Key 鎖，其設(shè)計的目的是為了解決『幻讀』的出現(xiàn)。　　

當查詢的索引含有唯一(主鍵索引和唯一索引)屬性是，InnoDB 存儲引擎會對 Next-Key Lock 進行優(yōu)化， 將其降級為Record Lock ，即僅鎖住索引本身，而不是范圍。

2.4 Insert Intention Lock

插入意向鎖是一種在數(shù)據(jù)行插入前設(shè)置的gap鎖。這種鎖用于在多事務插入同一索引間隙時，如果這些事務不是往這段gap的同一位置插入數(shù)據(jù)，那么就不用互相等待。

create table keme2 (a int primary key); insert into keme2 values (10),(11),(13),(20);開啟三個會話窗口 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;

?

　

?

客戶端A創(chuàng)建了一個keme2表，包含10,11,13,20四條索引記錄，然后去設(shè)置一個互斥鎖在大于18的所有索引記錄上。這個互斥鎖包含了在20記錄前的gap鎖。

?

3 鎖問題

通過鎖機制可以實現(xiàn)事務的隔離性要求，使得事務可以并發(fā)地工作。鎖提高了并發(fā)，但是也有有潛在的問題。不過好在因為事務隔離性的要求，鎖只會帶來三種問題，如果可以防止這三種情況的發(fā)生，哪將不會產(chǎn)生并發(fā)異常。?

3.1 臟讀

先了解臟數(shù)據(jù)，臟頁，臟讀。?
臟頁指的是在緩沖池中已近被修改的頁，但是還沒有刷新到磁盤中,即數(shù)據(jù)庫實例內(nèi)存中的頁和磁盤中的頁數(shù)據(jù)是不一致的，當然在刷新到磁盤之前，日志都已經(jīng)被寫入到了重做日志文件中。?

臟數(shù)據(jù)：是指事務對緩沖池中行記錄的修改，并且還沒有被提交。

對于臟頁的讀取，是非常正常的。臟頁是因為數(shù)據(jù)庫實例內(nèi)存和磁盤的異步造成的，這并不影響數(shù)據(jù)的一致性（或者說兩者最終會達到一致性，即當臟頁都刷到磁盤）。并且因為臟頁的刷新是異步的，不影響數(shù)據(jù)庫的可用性，因此可以帶來性能的提高。

臟數(shù)據(jù)是指未提交的數(shù)據(jù)，如果讀到臟數(shù)據(jù)，即一個事務可以讀到另外一個事務中未提交的數(shù)據(jù)，則顯然違反了數(shù)據(jù)庫的隔離性。?

臟讀：指的就是在不同的事務下，當前事務可以讀到另外事務未提交的數(shù)據(jù)，簡單來說就是可以讀到臟數(shù)據(jù)。

臟讀示例：

create table t (a int primary key); insert into t values (1);

　

?

-	會話A	會話B
1	set @@tx_isolation=’read-uncommitted’;? set autocommit=0;	?
2	?	set @@tx_isolation=’read-uncommitted’;
3	?	begin;
4	?	mysql> select * from t\G;? a: 1
5	insert into t select 2;	?
6	?	mysql> select * from t\G; *** 1. row ***? a: 1 *** 2. row *** a: 2

　

會話A 并沒有主動提交2這條插入事務 ， 但是在會話B 讀取到了， 這就是臟讀。

3.2 不可重復讀

不可重讀是在一個事務內(nèi)讀取同一數(shù)據(jù)集合。在這個事務還沒有結(jié)束時，另外一個事務也訪問同一數(shù)據(jù)集合，并做了一些DML操作。因此在第一個事務中的兩次讀取數(shù)據(jù)之間，由于第二個事務的修改，那么第一個事務兩次讀到的數(shù)據(jù)可能是不一樣的。這樣就發(fā)生了在一個事務內(nèi)兩次讀到的數(shù)據(jù)是不一樣的情況，這種情況稱為不可重復讀。

不可重復讀和臟讀的區(qū)別是：`臟讀示讀到未提交的數(shù)據(jù)，而不可重復讀讀到確實已近提交的數(shù)據(jù)。

3.3 丟失更新

?雖然數(shù)據(jù)庫能阻止更新問題的產(chǎn)生，但是在生產(chǎn)應用還有另一個邏輯意義丟失更新問題，而導致該問題的并不是因為數(shù)據(jù)庫本身的問題。實際上，在所有多用戶計算機系統(tǒng)環(huán)境下都有可能產(chǎn)生這個問題。比如下面的情況：?
比如一個用戶賬號中有10000元，他用兩個網(wǎng)上銀行的客戶端分別進行轉(zhuǎn)賬操作，第一次轉(zhuǎn)賬9000人民幣，因為網(wǎng)絡(luò)和數(shù)據(jù)的關(guān)系，這時需要等待。但是這時用戶操作另一個網(wǎng)上銀行客戶端，轉(zhuǎn)賬1元，如果最終兩筆操作都成功了，用戶賬號的余款是9999 元，第一次轉(zhuǎn)的9000人民幣并沒有得到更新，但是在轉(zhuǎn)賬的另一個賬號卻會收到這9000元，這導致了結(jié)果就是錢變多，而賬不平。但是銀行了也很聰明啊，個人網(wǎng)銀綁定usb key的，不會發(fā)生這種情況的。是的，通過usb key 登錄也許可以解決這個問題。但是更重要的是在數(shù)據(jù)庫層解決這個問題，避免任何可能發(fā)生丟失更新的情況.?
要避免丟失更新發(fā)生 ，需要讓事務在這種情況下的操作變成串行化，而不是并行的操作。

4 鎖阻塞

因為不同鎖之間的兼容性關(guān)系，在有些時刻一個事務中的鎖需要等待另一個事務中的鎖釋放它所占用的資源，這就是阻塞 。?阻塞并不是一件壞事，其實為了確保事務可以并發(fā)正常地運行。?
在InnoDB 存儲引擎中，參數(shù)innodb_lock_wait_timeout用來控制等待的時間(默認是50秒)，?innodb_rollback_on_timeout用來設(shè)定是否在等待超時時對進行中的事務進行回滾操作（默認是off，不回滾）。參數(shù)innodb_lock_wait_timeout可以在mysql 數(shù)據(jù)庫運行時進行調(diào)整：

在默認情況下InnoDB 存儲引擎不會回滾超時引發(fā)的錯誤異常。其實InnoDB 存儲引擎在大部分情況下都不會對異常進行回滾。?

?

?

查看鎖阻塞的信息：

select * from information_schema.innodb_trx\G; # 查看當前的事務信息 select * from information_schema.innodb_locks\G; # 查看當前的鎖信息 select * from information_schema.innodb_lock_waits\G; # 查看當前的鎖等待信息 可以聯(lián)表查，查找自己想要的結(jié)果。 select * from sys.innodb_lock_waits\G; # 查看當前的鎖等待信息 show engine innodb status\G; 還可以通過當前執(zhí)行了執(zhí)行了什么語句 select * from performance_schema.events_statements_current\G; show full processlist;

　　

5 死鎖

死鎖是指兩個或兩個以上的事務在執(zhí)行過程中，因爭奪鎖資源而造成的一種互相等待的現(xiàn)象。

5.1 數(shù)據(jù)庫層面解決死鎖的兩種方式

1、解決死鎖的問題最簡單的方式是不要有等待,將任何的等待都轉(zhuǎn)化為回滾，并且事務重新開始。?
這種沒有死鎖問題的產(chǎn)生。在線上環(huán)境中，可能導致并發(fā)性能的下降，甚至任何一個事務都不能進行。而這鎖帶來的問題遠比死鎖問題更為嚴重，而這鎖帶來的問題原題遠比死鎖問題更為嚴重，因為這很難被發(fā)現(xiàn)并且浪費資源。

2、解決死鎖的問題最簡單的一種方法時超時，即當兩個事務互相等待是，當一個等待時超過設(shè)置的某一閾值是，其中一個事務進行回滾，另一個等待的事務就能繼續(xù)進行。用innodb_lock_wait_timeout用來設(shè)置超時的時間。

超時機制雖然簡單，僅通過超時后對事務進行回滾的方式來處理，或者說其根據(jù)FIFO的順序選擇回滾對象。但若超時的事務所占權(quán)重比較大，如事務操作更新很多行(比如某程序猿用死循環(huán)來執(zhí)行一些事務)，占用了較多的undo log，這是采用FIFO 的方式，就顯得不合適了，因為回滾這個事務的時間相對另一個事務所占用的時間可能會更多。

?

在mysql 5.7.x 和 mysql 5.6.x 對死鎖采用的方式：?
mysql 5.6.x 是用鎖等待（超時）的方式來解決， 沒有自動解決死鎖的問題：

?

?

?mysql 5.7.x 默認開啟了死鎖保護機制：

?

?

5.2 死鎖演示

如果程序是串行的，那么不可能發(fā)生死鎖。死鎖只存在于并發(fā)的情況，而數(shù)據(jù)庫本身就是一個并發(fā)運行的程序，因此可能會發(fā)生死鎖。

死鎖示例：

a :創(chuàng)建表 create table temp(id int primary key ,name varchar(10)); insert into temp values(1,'a'),(2,'b'),(3,'c'); 此時表里只有3條數(shù)據(jù)執(zhí)行步驟根據(jù)數(shù)據(jù)順序來： 1. 事務1: start transaction; update temp set name='aa' where id=1;2. 事務2: start transaction; update temp set name='bb' where id=2;3. 事務1:update temp set name='aaa' where id=2;這時候3的步驟會有鎖等待， 立馬執(zhí)行4，就會馬上產(chǎn)生死鎖 4. 事務2: update temp set name='bbb' where id=1;

　　

?

5.3 避免死鎖發(fā)生的方法

在事務性數(shù)據(jù)庫中，死鎖是個經(jīng)典的問題，但只要發(fā)生的頻率不高則死鎖問題不需要太過擔心?
死鎖應該非常少發(fā)生，若經(jīng)常發(fā)生，則系統(tǒng)是不可用。

查看死鎖的方法有兩種：?
通過show engine innodb status命令可以查看最后一個死鎖的情況?
通過innodb_print_all_deadlocks參數(shù)配置可以將所有死鎖的信息都打印到MySQL的錯誤日志中

減少死鎖發(fā)生的方法：?

1、盡可能的保持事務小型化，減少事務執(zhí)行的時間可以減少發(fā)生影響的概率?
2、及時執(zhí)行commit或者rollback，來盡快的釋放鎖?
3、當要訪問多個表數(shù)據(jù)或者要訪問相同表的不同行集合時，盡可能的保證每次訪問的順序是相同的。比如可以將多個語句封裝在存儲過程中，通過調(diào)用同一個存儲過程的方法可以減少死鎖的發(fā)生?
4、增加合適的索引以便語句執(zhí)行所掃描的數(shù)據(jù)范圍足夠小?
5、盡可能的少使用鎖，比如如果可以承擔幻讀的情況，則直接使用select語句，而不要使用select…for update語句?
6、如果沒有其他更好的選擇，則可以通過施加表級鎖將事務執(zhí)行串行化，最大限度的限制死鎖發(fā)生

6 事務

事務的主要目的了：事務會把數(shù)據(jù)庫從一種一致狀態(tài)轉(zhuǎn)換為另一種一致狀態(tài)。在數(shù)據(jù)庫提交工作是，可以確保要么所有修改都已近保存了，要么所有修改都不保存。

InnoDB 存儲引擎中的事務完全符合ACID 的特性。?
原子性 （atomicity）?
一致性（consistency）?
隔離性（isolation）?
持久性（durability）

6.1 了解事務

事務可由一條非常簡單的sql 語句組成，也可以有一組復雜的sql 組成。事務是訪問并更新數(shù)據(jù)庫中各種數(shù)據(jù)項的一個程序執(zhí)行單元，在事務中的操作，要么都做修改，要么都不做這就是事務的目的。?
事務ACID 的特性

6.1.1 原子性

A (Atomicity)，原子性。指整個數(shù)據(jù)庫事務是不可分割的工作單位。只有使事務中所有的數(shù)據(jù)庫操作都執(zhí)行成功，才算整個事務成功。事務中任何一個SQL 語句執(zhí)行失敗，已近執(zhí)行?
成功的sql 語句也必須撤銷。數(shù)據(jù)庫狀態(tài)應該退回到執(zhí)行事務前的狀態(tài)。?

比如ATM 取款流程：?
1、登錄ATM 機平臺，驗證密碼。?
2、從遠程銀行數(shù)據(jù)庫中，取得賬戶的信息。?
3、用戶在ATM 輸入提取的金額。?
4、從遠程銀行的數(shù)據(jù)庫中，更新賬戶信息。?
5、ATM 機出款。?
6、用戶取錢。?
整個過程都視為原子操作，某一個步驟失敗了， 都不能進行下一步。

?

6.1.2 一致性

C （consistency），一致性。一致性定義基本可以理解為是事務對數(shù)據(jù)完整性約束的遵循。這些約束可能包括主鍵約束、外鍵約束或是一些用戶自定義約束。事務執(zhí)行的前后都是合法的數(shù)據(jù)狀態(tài)，不會違背任何的數(shù)據(jù)完整性，這就是“一致”的意思。事務是一致性的單位，如果事務中某個動作失敗了，系統(tǒng)就可以自動撤銷事務——返回事務初始化的狀態(tài)。

6.1.3 隔離性

I (isolation)，隔離性。隔離性還有其他的稱呼，如并發(fā)控制，可串行化，鎖 等。事務的隔離性要求每個讀寫事務的對象對其他事務的操作對象能相互分離，即該事務提交前對其他事務都不可見。

6.1.4 持久性

D（durability），持久性。事務一旦提交，其結(jié)果就是永久性的(寫入了磁盤)，即使發(fā)生宕機等故障，數(shù)據(jù)庫也能將數(shù)據(jù)恢復。需要注意的是，只能從事務本身的角度來保證結(jié)果的永久性。?

?

例如：在事務提交后，所有的變化都是永久的，即使當數(shù)據(jù)庫因為崩潰而需要恢復時，也能保證恢復后提交的數(shù)據(jù)都不會丟失。但若不是數(shù)據(jù)庫本身發(fā)生故障，而是一些外部的原因，如RAID卡損壞，自然災害等原因?qū)е聰?shù)據(jù)庫發(fā)生問題，那么所有提交的數(shù)據(jù)可能都會丟失。因此持久性保證事務系統(tǒng)的高可靠性，而不是高可用性。對于高可用性的實現(xiàn)，事務本身并不能保證，需要一些系統(tǒng)來共同配合來完成。

6.2 事務的實現(xiàn)

事務的隔離性由鎖來實現(xiàn)。原子性，一致性，持久性通過數(shù)據(jù)庫的redo log 和undo log 來完成，redo log 成為重做日志，用來保證事務的原子性和持久性。 undo log 用來保證事務的一致性。

redo 和 undo 的作用都可以視為是一種恢復操作，redo 恢復提交事務修改的頁操作，而undo 回滾行記錄到某個特定版本。因此兩者記錄的內(nèi)容不同，redo 通常是物理日志，記錄的是頁的物理修改操作，undo 是邏輯日志，根據(jù)每行記錄進行記錄。

6.2.1 redo

重做日志(redo log)用來實現(xiàn)事務的持久性，即事務ACID 中的 D。 其中兩部分組成： 一是內(nèi)存中的重做日志緩沖（redo log buffer），其實容易丟失的；二是重做日志文件（redo log file）,其是持久的。

InnoDB 是事務的存儲引擎，其通過Force Log at Commit 機制實現(xiàn)事務的持久性，即當事務提交(commit)時，必須先將該事務的所有日志寫入到重做日志文件進行持久化，待事務的commit 操作完成才算完成。這里的日志是指重做日志，在InnoDB 存儲引擎中，由兩部分組成，即redo log 和 undo log 。redo log 用來保證事務的持久性，undo log 用來幫助事務回滾及多版本控制（mvcc）的功能，redo log 基本上都是順序?qū)懙?#xff0c;在數(shù)據(jù)庫運行不需要對redo log 的文件進行讀取操作。而undo log 是需要進行隨機讀寫的。

為了確保每次日志都寫入重做日志文件，在每次都將重做日志緩沖寫入重做日志文件后，InnoDB存儲引擎都需要調(diào)用一次fsync 操作。由于重做日志文件打開并沒有使用O_DIRECT選項，因此重做日志緩沖先寫入文件系統(tǒng)緩沖。為了確保重做日志寫入磁盤，必須進行一次fsync 操作。由于fsync 的效率取決于磁盤的性能，因此磁盤的性能決定了事務的提交的性能，也就是數(shù)據(jù)庫的性能。

InnoDB 存儲引擎允許用戶手工非持久性的情況發(fā)生，以此提高數(shù)據(jù)庫的性能。?
即當事務提交時，日志不寫入重做日志文件，而是等待一個時間周期后再執(zhí)行fsync 操作。?

InnoDB 存儲引擎允許用戶手工非持久性的情況發(fā)生，以此提高數(shù)據(jù)庫的性能。?

即當事務提交時，日志不寫入重做日志文件，而是等待一個時間周期后再執(zhí)行fsync 操作。

用參數(shù)?innodb_flush_log_at_trx_commit用來控制重做日志刷新到磁盤的策略。該參數(shù)默認值為1?

改參數(shù)可以設(shè)置值為?0、1、2

0 ： 表示事務提交時不進行寫入重做日志操作，這個操作僅在master thread 中完成，而在master thread 中每1秒會進行一次重做日志的fsync 操作。

1 ：? 表示每個事務提交時進行寫入到重做日志。

2 ：? 表示事務提交時將重做日志寫入重做日志文件，但僅寫入文件系統(tǒng)的緩存中，不進行fsync操作。 在這個設(shè)置下，當mysql 數(shù)據(jù)庫發(fā)生宕機(就是數(shù)據(jù)庫服務意外停止)而操作系統(tǒng)不發(fā)生宕機是，不會導致事務的丟失。而當操作系統(tǒng)宕機時，重啟數(shù)據(jù)庫后會丟失未從文件系統(tǒng)緩存刷新到重做日志文件那部分事務。

6.2.2 undo

1 、基本概念?
重做日志記錄了事務的行為，可以很好地通過其對頁進行”重做”操作，但是事務有時還需要進行回滾操作，這時就需要undo。 因此在對數(shù)據(jù)庫進行修改時，InnoDB 存儲引擎不但會產(chǎn)生redo，還會產(chǎn)生一定量的undo。這樣如果用戶執(zhí)行的事務或語句由于原因失敗了，又或者用戶用一條rollback 語句請求回滾，就可以利用這些undo 信息將數(shù)據(jù)回滾到修改之前的樣子。

redo 存放在重做日志文件中，與redo 不同，undo 存放在數(shù)據(jù)庫內(nèi)部的一個特殊段（segment）中，這個段稱為undo 段 。undo 段位于共享表空間內(nèi)。

undo 是 邏輯日志，因此只是將數(shù)據(jù)庫邏輯地恢復到原來的樣子。所有修改都被邏輯地取消了，但是數(shù)據(jù)結(jié)構(gòu)和頁本身在回滾之后可能大不相同。 這是因為在多用戶并發(fā)系統(tǒng)中，可能會有數(shù)十，數(shù)百甚至數(shù)千個并發(fā)事務。數(shù)據(jù)庫的主要任務就是協(xié)調(diào)對數(shù)據(jù)記錄的并發(fā)訪問。比如，一個事務在修改當前一個頁中某幾條記錄，同時還有別的事務在對同一個頁中另幾條記錄進行修改。因此，不能將一個頁回滾到事務開始的樣子，因為這樣會影響其他事務正在進行的工作。

?undo 除了回滾操作，undo 的另一個作用是mvcc，即在InnoDB 存儲引擎中mvcc 的實現(xiàn)是通過undo 來完成。當用戶讀取一行記錄時，若該記錄已近被其他事務占用，當前事務可以通過undo 讀取之前的行版本信息，以此實現(xiàn) 非鎖定讀取。

最重要的一點是，undo log 會產(chǎn)生redo log ，也就是undo log 的產(chǎn)生會伴隨著redo log 的產(chǎn)生，這是因為undo log 也需要持久性的保護。

2、undo 存儲管理

InnoDB 存儲引擎有rollback segment ，每個回滾段中記錄了1024 個undo log segment , 而在每個 undo log segment 段中進行undo 頁的申請。?

InnoDB 支持最大128 個(回滾段)rollback segment ，故其支持同時在線的事務 128 * 1024， 但是這些 rollback segment 都存儲于共享表空間中。可以通過參數(shù)對rollback segment 做 進一步的設(shè)置。這些參數(shù)包括：

?

innodb_undo_directory innodb_undo_logs innodb_undo_tablespaces

　　

?

innodb_undo_directory 用于設(shè)置rollback segment 文件所在的路徑。這意味著rollback segment 可以放在共享表空間以外的位置，即可以設(shè)置為獨立表空間。該參數(shù)的默認值為”.”，表示當前InnoDB存儲引擎的目錄。

innodb_undo_logs 用來設(shè)置rollback segment 的個數(shù)，默認值為128

innodb_undo_tablespaces 用來設(shè)置構(gòu)成rollback segment 文件的數(shù)量，這樣rollback segment 可以較為平均地分布在多個文件。設(shè)置改參數(shù)后，會在路勁innodb_undo_directory 看到undo 為前綴的文件，該文件就代表rollback segment 文件

數(shù)據(jù)庫初始化后，innodb_undo_tablespaces 就再也不能被改動了；默認值為0，表示不獨立設(shè)置undo的tablespace，默認記錄到ibdata中；否則，則在undo目錄下創(chuàng)建這么多個undo文件，例如假定設(shè)置該值為4，那么就會創(chuàng)建命名為undo001~undo004的undo tablespace文件，每個文件的默認大小為10M。修改該值會導致Innodb無法完成初始化，數(shù)據(jù)庫無法啟動，但是另兩個參數(shù)可以修改；

6.2.3 purge

delete 和 update 操作可能并不直接刪除原有的數(shù)據(jù)。

?

?例如執(zhí)行

delete from z where a=1;

表z 上列a 有聚集索引，列表上有輔助索引，對于上述的delete 操作，在undo log 將主鍵列等于1 的記錄delete flag 設(shè)置為1 ，記錄并沒有立即刪除，記錄還是存在B+樹種，其次，對輔助索引上a 等于1 ，b等于1 的記錄同樣沒有做任何處理，甚至沒有產(chǎn)生undo log 。 而真正刪除這行記錄的刪除操作其實被“延時”了，最終在purge 操作中完成。　

purge 用于最終完成delete 和 update 操作。 因為InnoDB 存儲引擎支持MVCC，所以記錄不能再事務提交時立即進行處理。這時其他事務可能正在引用這行，故InnoDB 存儲引擎需要保持記錄之前的版本。而是否可以刪除該條記錄通過purge 來進行判斷。若該行記錄已不被任何其他事務引用，那么就可以進行真正的delete 操作。可見，purge 操作是清理之前的delete 和 update 操作， 將上述操作 “最終” 完成。 而實際執(zhí)行的操作為delete 操作，清理之前行記錄的版本。

6.2.4 group commit

5.6 版本之前的兩次提交?

若事務為非只讀事務，則每次事務提交時需要進行一次fsync 操作，以此保證重做日志都已近寫入磁盤。當數(shù)據(jù)庫發(fā)生宕機時，可以通過重做日志進行恢復。雖然固態(tài)硬盤的出現(xiàn)提高了磁盤的性能，然后磁盤的rsync 性能是有限的。為了提高磁盤fsync 的效率，數(shù)據(jù)庫提供了group commit 的功能，即一次fsync 可以刷新確保多個事務日志被寫入文件。?　

對于InnoDB 存儲引擎來說， 事務提交時會進行兩個階段的操作：?

1、修改內(nèi)存中事務對應的信息，并且將日志寫入重做日志緩沖。?
2、調(diào)用fsync 將確保日志都從重做日志緩沖寫入磁盤。

?步驟1 相對 步驟2 是一個較慢的過程，這是因為存儲引擎需要與磁盤打交道。但當有事務進行這個過程是，其他事務可以進行 步驟1 的 操作，正在提交的事務完成提交操作， 再次進行步驟 2 時，可以將多個事務的重做日志通過一次fsync 刷新到磁盤，這樣就大大減少了磁盤的壓力，從而提高了數(shù)據(jù)庫的整體性能。對于寫入或更新較為頻繁的操作，group commit 的效果 尤為明顯。

二段提交流程

1 、InnoDB 的事務 Prepare 階段，即 SQL 已經(jīng)成功執(zhí)行并生成 redo 和 undo 的內(nèi)存日志；

2、binlog 提交，通過 write() 將 binlog 內(nèi)存日志數(shù)據(jù)寫入文件系統(tǒng)緩存；

3、fsync() 將 binlog 文件系統(tǒng)緩存日志數(shù)據(jù)永久寫入磁盤；

4、InnoDB 內(nèi)部提交，commit 階段在存儲引擎內(nèi)提交，通過 innodb_flush_log_at_trx_commit 參數(shù)控制，使 undo 和 redo 永久寫入磁盤。

組提交?

5.6 引入了組提交，并將提交過程分成 Flush stage、Sync stage、Commit stage 三個階段。

1、InnoDB, Prepare ： SQL已經(jīng)成功執(zhí)行并生成了相應的redo和undo內(nèi)存日志；?
2、Binlog, Flush Stage ：所有已經(jīng)注冊線程都將寫入binlog緩存；?
3、Binlog, Sync Stage ：binlog緩存將sync到磁盤，sync_binlog=1時該隊列中所有事務的binlog將永久寫入磁盤；?
4、 InnoDB, Commit stage： leader根據(jù)順序調(diào)用存儲引擎提交事務；

每個 Stage 階段都有各自的隊列，從而使每個會話的事務進行排隊，提高并發(fā)性能。?
如果當一個線程注冊到一個空隊列時，該線程就做為該隊列的 leader，后注冊到該隊列的線程均為 follower，后續(xù)的操作，都由 leader 控制隊列中 follower 行為。

https://www.linuxidc.com/Linux/2018-01/150187.htm

參數(shù)binlog_max_flush_queue_time 用來控制flush 階段中等待的時間，即使之前的一組事務完成提交，當前一組的事務也不馬上進去sync階段，而是至少需要等待一段時間。這樣做的好處是group commit 的數(shù)量更多，然而這也可能會導致事務的相應時間變慢。該參數(shù)的默認值為0，且推薦設(shè)置依然為0。除非用戶的mysql 數(shù)據(jù)庫系統(tǒng)中 有著大量的連接，并且不斷地在進行事務的寫入或更新操作。

注：任何參數(shù)都不要隨意設(shè)置，看到別人設(shè)置參數(shù)能解決，為什么我的環(huán)境設(shè)置就報錯了，看官方的改參數(shù)注意事項，各種版本的注意事項，在去相應測試環(huán)境實驗一下。

6.3 事務控制語句

在 mysql 命令行的默認設(shè)置下，事務都是自動提交（auto commit）的，即執(zhí)行sql 語句就會馬上執(zhí)行commit 操作。?

用戶可以使用那些事務控制語句。

start transaction | begin ：顯示地開啟一個事務（推薦start transaction）commit：會提交事務，并使得已對數(shù)據(jù)庫做的所有修改成為永久性的rollback：回滾用戶當前鎖執(zhí)行的事務，并撤銷正在進行的所有未提交的修改。savepoint identifer ： savepoint 允許在事務中創(chuàng)建一個保存點，一個事務中可以有多個savepoint。release savepoint identifier: 刪除一個事務的保存點，當沒有一個保存點執(zhí)行這句語句時，會拋出一個異常。rollback to[savepoint] identifer: 這個語句與savepoint 命令一起使用。可以把事務回滾到標記點，而不會滾在此標記點之前的任何工作。set transaction： 這個語句用來設(shè)置事務的隔離級別。InnoDB 存儲引擎的事務隔離級別有： READ UNCOMMITED、READ COMMITED、REPEATABLE READ、SERIALIZABLE.

　　

例：

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ù)的時候 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)# 這時候發(fā)現(xiàn)了，rollback to savepoint u1了， 后面的u2 的 事務已近不存在了， 但是兩條記錄的數(shù)據(jù)還在。 mysql> rollback; Query OK, 0 rows affected (0.00 sec)mysql> select * from u; Empty set (0.00 sec)

在上面的列子中，雖然在發(fā)生重復錯誤后用戶通過rollback to save point u1命令回滾到了保存點u1，但是事務此時沒有結(jié)束。在運行命令rollback后，事務才會完整地回滾。　　

InnoDB 存儲引擎中的事務都是原子的，這說明下兩種情況：構(gòu)成事務的每天語句都會提交（成為永久），或者所有語句都回滾。這種保護還延伸到單個的語句。一條語句要么完全成功。要么完全回滾（注意，這里說的是語句回滾）。因此一條語句失敗并拋出異常時，并不會導致先前已近執(zhí)行的語句自動回滾。所有的執(zhí)行都會得到保留，必須由用戶自己來決定是否對其進行提交或回滾的操作。?

rollback to savepoint 命令并不真正地結(jié)束事務。?
commit 和 rollback 才是真正的結(jié)束一個事務

?

6.4 隱式提交的 SQL 語句

以下這些sql 語句 會產(chǎn)品一個隱式的提交操作即執(zhí)行完這些語句后，會有一個隱式的commit 操作：
1 、DDL 語句

ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME,
ALTER EVENT,ALTER PROCEDURE,ALTER TABLE ,ALTER VIEW,
CREATE DATABASE, CREATE EVENT, CREATE TRIGGER , CREATE VIEW,
DROP DATABASE ,DROP EVENT , DROP INDEX , DROP PROCEDURE , DROP TABLE , DROP TRIGGER , DROP VIEW ,
RENAME TABLE , TRUNCATE TABLE .

2、用來隱式修改 MYSQL 架構(gòu)的操作　　

CREATE USER,DROP USER ,GRANT , RENAME USER ,REVOKE , SET PASSWORD.

3、管理語句　　

ANALYZE TABLE,CACHE INDEX, CHECK TABLE ,
LOAD INDEX INTO CACHE,OPTIMEIZE TABLE ,REPAIR TABLE

　　

注: 我發(fā)現(xiàn) sql server 的數(shù)據(jù)庫有些 ddl 也是可以回滾的。這和 InnoDB 存儲引擎，oracle 這些數(shù)據(jù)庫完全不同。

truncate table 演示：

mysql> insert into u select 1; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0mysql> insert into u select 2; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0mysql> 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)

　　

6.5 對于事務的操作的統(tǒng)計

由于InnoDB 存儲引擎是支持事務的，因此InnoDB 存儲引擎的應用需要在考慮每秒請求數(shù)（transaction per second ，TPS）?

計算 TPS 的方法時( com_commit + com_rollback）/time 。但是利用這種方法進行計算的前提是：所有的事務必須都是顯示提交的，如果存在隱式提交和回滾（默認autocommit =1 ），不會計算到com_commit 和 com_rollback 變量中。 如;

mysql 數(shù)據(jù)庫中另外還有兩個參數(shù)handler_commit 和 handler_rollback 用于事務的統(tǒng)計操作。可以很好的用來統(tǒng)計InnoDB 存儲引擎顯式和隱式的事務提交操作。?

在InnoDB Plugin 中這兩個參數(shù)的表現(xiàn)有些“怪異”?
如果用戶的程序都是顯示控制事務的提交和回滾，那么可以通過com_commit 和 com_rollback 進行統(tǒng)計。

6.6 事務的隔離級別

SQL 標準定義的四個隔離級別為:?
READ UNCOMMITTED?
READ COMMITTED?
REPEATABLE READ?
SERIALIZABLE?

sql server 和oracle 默認的隔離級別是READ COMMITED

?

隔離級別	臟讀	不可重復讀	幻讀
READ UNCOMMITTED	可能	可能	可能
READ COMMITED	不可能	可能	可能
REPEATABLE READ	不可能	不可能	可能
SERIALIZABLE	不可能	不可能	不可能

?

?臟讀:?又稱無效數(shù)據(jù)的讀出，是指在數(shù)據(jù)庫訪問中，事務T1將某一值修改，然后事務T2讀取該值，此后T1因為某種原因撤銷對該值的修改，這就導致了T2所讀取到的數(shù)據(jù)是無效的。

?不可重復讀：是指在數(shù)據(jù)庫訪問中，一個事務范圍內(nèi)兩個相同的查詢卻返回了不同數(shù)據(jù)

?幻讀：?是指當事務不是獨立執(zhí)行時發(fā)生的一種現(xiàn)象，例如第一個事務對一個表中的數(shù)據(jù)進行了修改，比如這種修改涉及到表中的“全部數(shù)據(jù)行”。同時，第二個事務也修改這個表中的數(shù)據(jù)，這種修改是向表中插入“一行新數(shù)據(jù)”。

不可能重復讀和幻讀的區(qū)別：?
很多人容易搞混不可重復讀和幻讀，確實這兩者有些相似。但不可重復讀重點在于update和delete，而幻讀的重點在于insert。?

在InnoDB 引擎中，可以使用一下命令來設(shè)置當前會話和全局的事務的隔離級別：

mysql> help isolation; Name: 'ISOLATION' Description: Syntax: SET [GLOBAL | SESSION] TRANSACTIONtransaction_characteristic [, transaction_characteristic] ...transaction_characteristic: {ISOLATION LEVEL level| READ WRITE| READ ONLY }level: {REPEATABLE READ| READ COMMITTED| READ UNCOMMITTED| SERIALIZABLE }

　　

如果想在MySQL 數(shù)據(jù)啟動時就設(shè)置事務的默認隔離級別，那就需要修改mysql 的配置文件 my.cnf 在 [mysqld] 中添加如下行：

[mysqld] transaction-isolation = REPEATABLE-READ

　　

查看當前會話的事務隔離級別，可以使用：

mysql> select @@tx_isolation\G; ********** 1. row ********** @@tx_isolation: REPEATABLE-READ 1 row in set, 1 warning (0.00 sec)

　　

查看全局的事務隔離級別，可以使用：

mysql> select @@global.tx_isolation\G; ******** 1. row ******** @@global.tx_isolation: REPEATABLE-READ 1 row in set, 1 warning (0.00 sec)

　　

6.7 不好的事務的習慣

6.7.1 在循環(huán)中提交

用存儲過程模擬一下

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 ;

　　

比較這三個存儲過程執(zhí)行時間：

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 默認是自動提交的，load1 和 load2 沒執(zhí)行一次都會自動提交

顯然，load3 方法要快的多，這是因為每一次提交都要寫一次重做日志，存儲過程load1 和 load2 實際寫了20000 次重做日志文件，而對于存儲過程load3 來說，實際只寫了一次。

6.8 長事務

長事務就是執(zhí)行時間較長的事務。比如對于銀行系統(tǒng)的數(shù)據(jù)庫，沒過一個階段可能需要更新對應賬戶的利息。如果對應賬號的數(shù)量非常大，例如對有1億用戶的表account ，需要執(zhí)行以下列語句;

update accout set account_total= accoutn_total + （1+inerset_rate）

這是這個事務可能需要非常長的時間來完成。可能需要1個小時，也可能乣4,5個小時，這取決于數(shù)據(jù)庫的硬件配置。DBA和 開發(fā)人員本身能做的事情非常少。然而，由于事務ACID 的特性，這個操作被封裝在一個事務中完成。這就產(chǎn)生了一個問題，在執(zhí)行過程中，當數(shù)據(jù)庫或操作系統(tǒng)，硬件等發(fā)生問題是，重新開始事務的代價變得不可接受。數(shù)據(jù)庫需要回滾所有已近發(fā)生的變化，而這個過程可能比產(chǎn)生這些變化的時間還要長。因此，對于長事務的問題，有時可以通過轉(zhuǎn)化為小批量的事務來進行處理。當事務發(fā)生錯誤是，只需要回滾一部分數(shù)據(jù)，然后接著上次已完成的事務繼續(xù)進行。

?

注：以上所有操作全是在mysql 5.7.24 版本

轉(zhuǎn)載于:https://www.cnblogs.com/keme/p/10731177.html

總結(jié)

以上是生活随笔為你收集整理的MySQL 锁信息和事务的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。