2019獨角獸企業重金招聘Python工程師標準>>>

一、RDBMS數據組織

RDBMS中數據組織涉及到兩個最基本的結構：表與索引。表中存儲的是完整記錄，一般有兩種組織形式：堆表(所有的記錄無序存儲，如Oracle/DB2/PostgreSQL)，或者是聚簇索引表(所有的記錄，按照記錄主鍵進行排序存儲，如InnoDB引擎)。索引中存儲的是完整記錄的一個子集，用于加速記錄的查詢速度，索引的組織形式，一般均為B+樹結構。

二、RDBMS鎖的原則

2PL（二階段鎖Two-Phase Locking）：傳統RDBMS加鎖的一個原則，就是2PL，說的是鎖操作分為兩個階段：加鎖階段與解鎖階段，并且保證加鎖階段與解鎖階段不相交。下面以MySQL為例，來簡單看看2PL在MySQL中的實現。

從上圖可以看出，2PL就是將加鎖/解鎖分為兩個完全不相交的階段。加鎖階段：只加鎖，不放鎖。解鎖階段：只放鎖，不加鎖。所以JDBC Connection需要通過commit/rollback來提交或者釋放鎖。

三、MVCC

MySQL是一個支持插件式存儲引擎的數據庫系統。不同引擎的表現，會有較大的區別。InnoDB存儲引擎實現的是基于多版本的并發控制協議——MVCC (Multi-Version Concurrency Control) (注：與MVCC相對的，是基于鎖的并發控制，Lock-Based Concurrency Control)。

MVCC最大的好處，讀不加鎖，讀寫不沖突。在讀多寫少的OLTP應用中，讀寫不沖突是非常重要的，極大的增加了系統的并發性能，這也是為什么現階段，幾乎所有的RDBMS，都支持了MVCC。

在MVCC并發控制中，讀操作可以分成兩類：快照讀 (snapshot read)與當前讀 (current read)。快照讀，讀取的是記錄的可見版本 (有可能是歷史版本)，不用加鎖。當前讀，讀取的是記錄的最新版本，并且，當前讀返回的記錄，都會加上鎖，保證其他事務不會再并發修改這條記錄。

四、InnoDB一級索引、二級索引

每個InnoDB表具有一個特殊的索引稱為聚簇索引（也叫聚集索引，聚類索引，簇集索引）。如果表上定義有主鍵，該主鍵索引就是聚簇索引。如果未定義主鍵，MySQL取第一個唯一索引（unique）而且只含非空列（NOT NULL）作為主鍵，InnoDB使用它作為聚簇索引。如果沒有這樣的列，InnoDB就自己產生一個這樣的ID值，它有六個字節，而且是隱藏的，使其作為聚簇索引。

表中的聚簇索引（clustered index ）就是一級索引，除此之外，表上的其他非聚簇索引都是二級索引，又叫輔助索引（secondary indexes）

五、Mysql鎖

5.1、鎖的分類：

1、共享鎖（S鎖、讀鎖）：允許并發的讀取同一資源, 但會阻塞其他寫鎖的sql請求；

2、排他鎖（X鎖、寫鎖）：一個寫鎖會阻塞其他的寫鎖或讀鎖，保證同一時刻只有一個連接可以寫入數據。

recordLock（行鎖）：直接加在索引記錄上面，鎖住的是key，而非記錄本身（Oracle是通過在數據塊中對相應數據行加鎖來實現的）。如果該表上沒有任何索引，那么Innodb會在后臺創建一個隱藏的聚簇索引，鎖住的就是這個隱藏的聚簇索引。即當一條sql沒有走任何索引時，那么將會在每一條聚簇索引后面加X鎖，這個類似于表鎖，但原理上和表鎖應該是不同的。為了效率考量，MySQL做了優化，對于不滿足條件的記錄，會在判斷后放鎖，最終持有的，是滿足條件的記錄上的鎖，但是不滿足條件的記錄上的加鎖/放鎖動作不會省略。

gapLock（間隙鎖）：鎖定索引記錄間隙，確保索引記錄的間隙不變。間隙鎖是針對事務隔離級別為可重復讀或以上的。

next key lock：recordLock+gapLock

5.2、鎖的策略

1、row lock(行鎖)：表中某些行被某個連接占用了寫鎖，但是其他行依然可以被其他連接請求讀鎖、寫鎖。

2、table lock(表鎖)：整個表被某一個連接占用了寫鎖，導致其他連接的讀鎖或者寫鎖都會阻塞；影響整個表的讀寫。通常發生在DDL語句\DML不走索引的語句中。

六、Sql分析

select * from table where xxx; （MVCC快照讀，一般不加鎖）

select * from table where xxx lock in share mode;? (讀鎖，當前讀，顯示鎖，會阻塞其他的寫鎖請求，但其他的讀鎖請求沒有影響）

select * from table where xxx for update;? (寫鎖，當前讀，顯示鎖，會阻塞其他的讀寫請求，）

update tableName set xxx (寫鎖，當前讀，隱式鎖）

insert （寫鎖，當前讀，隱式鎖）

delete （寫鎖，當前讀，隱式鎖）

說明：為什么將插入/更新/刪除操作，都歸為當前讀？可以看看下面這個更新操作，在數據庫中的執行流程：

一個Update操作的具體流程：當Update SQL被發給MySQL后，MySQL Server會根據where條件，讀取第一條滿足條件的記錄，然后InnoDB引擎會將第一條記錄返回，并加鎖 (current read)。待MySQL Server收到這條加鎖的記錄之后，會再發起一個Update請求，更新這條記錄。一條記錄操作完成，再讀取下一條記錄，直至沒有滿足條件的記錄為止。因此，Update操作內部，就包含了一個當前讀。同理，Delete操作也一樣。Insert操作會稍微有些不同，簡單來說，就是Insert操作可能會觸發Unique Key的沖突檢查，也會進行一個當前讀。

注：根據上圖的交互，針對一條當前讀的SQL語句，InnoDB與MySQL Server的交互，是一條一條進行的，因此，加鎖也是一條一條進行的。先對一條滿足條件的記錄加鎖，返回給MySQL Server，做一些DML操作；然后在讀取下一條加鎖，直至讀取完畢。

七、Mysql事務

ANSI/ISO SQL標準定義了4中事務隔離級別：讀未提交（read uncommitted），讀提交（read committed），重復讀（repeatable read），串行讀（serializable）。不同的隔離級別有不同的現象：

1、臟讀（dirty read）：一個事務可以讀取另一個尚未提交事務的修改數據。

2、不可重復讀（nonrepeatable read）：在同一個事務中，同一個查詢在T1時間讀取某一行，在T2時間重新讀取這一行時候，這一行的數據已經發生修改，可能被更新了（update），也可能被刪除了（delete）。

3、幻像讀（phantom read）：在同一事務中，同一查詢多次進行時候，由于其他插入操作（insert）的事務提交，導致每次返回不同的結果集。

不同的隔離級別有不同的現象，并有不同的鎖定/并發機制，隔離級別越高，數據庫的并發性就越差。在Oracle中默認的事務隔離級別是提交讀（read committed）。對于MySQL的Innodb的默認事務隔離級別是重復讀（repeatable read），Mysql中的RR不會臟讀、可重復讀（Innodb使用多版本一致性讀來實現），并不能完全避免幻讀（這點和ANSI/ISO SQL標準定義的有所區別），需要加next key locks，可以使顯示鎖(select * for update or lock in share mode）。

?

Ref：

http://hedengcheng.com/?p=771

http://hedengcheng.com/?p=577

https://blog.csdn.net/lemon89/article/details/51477497

https://juejin.im/post/5ab5e44a6fb9a028c97a013d

轉載于:https://my.oschina.net/u/3787772/blog/1932688

總結

以上是生活随笔為你收集整理的mysql锁与事务的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。