• 合集目錄
  • MySQL專題2: 事務和鎖

說說資料庫事務特性及存在的問題

這屬于資料庫事務的基礎概念了, 就是ACID

• Atomicity, 原子性, 事務包含的所有操作要么全部成功, 要么全部失敗回滾.
• Consistency, 一致性, 事務執行前后, 資料庫的資料要保持一致性.
• Isolation, 隔離性, 事務的執行不受其它事務的影響, 并發執行的幾個事務互不影響. 事務執行的中間結果對其它事務必須是透明的. 關于隔離性, 資料庫提供了多種隔離級別.
• Durability, 持久性, 是指一個事務一旦被提交了, 那么對資料庫中的資料的改變就是永久的, 不會丟失的.

ACID是一種理想狀態, 資料庫是通過一些機制來保證ACID的, 但是在實際使用中, 為了兼顧功能容量和性能會對其中的某一項或多項作出折衷.

談談對Innodb事務的理解

事務具有ACID四個特性: 原子性, 一致性, 隔離性, 持久性

InnoDB對ACD這三個特性是通過 Redo log 和 Undo log 實作的, 對I(隔離性)是通過鎖來實作的.

Redo Log

用來實作事務的持久性, 即D特性. 它由兩部分組成:

1. 記憶體中的重做日志緩沖
2. 重做日志檔案

InnoDB 在事務提交時, 必須先將該事務的所有日志寫入到 Redo Log 中, 待事務的 Commit 操作完成才算整個事務操作完成

Undo Log

Undo Log 可以實作如下兩個功能:

1. 實作事務回滾
2. 實作MVCC (Multiversion concurrency control, 多版本并行控制)

Undo Log 和 Redo Log 記錄物理日志不一樣, 它是邏輯日志: 當洗掉一條記錄時, Undo Log 中會記錄一條對應的 Insert 記錄, 反之亦然; 當 Update 一條記錄時, 它記錄一條對應反向的 Update 記錄. 當執行回滾時, 就可以從 Undo Log 中的邏輯記錄讀取到相應的內容并進行回滾.

什么是MySQL隔離級別?

InnoDB默認是可重復讀的 REPEATABLE READ. MySQL/InnoDB 提供SQL標準所描述的所有四個事務隔離級別, 可以在命令列用--transaction-isolation選項, 或在選項檔案里為所有連接設定默認隔離級別. 例如可以在my.inf檔案的[mysqld]節里類似如下設定該選項:

transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE}

隔離級別         臟讀(Dirty Read) 不可重復讀(NonRepeatable Read)     幻讀(Phantom Read)
未提交讀(Read uncommitted)  可能         可能                可能
已提交讀(Read committed)    不可能       可能                 可能
可重復讀(Repeatable read)   不可能       不可能               可能
可串行化(Serializable )     不可能       不可能               不可能

• 未提交讀(Read Uncommitted): 允許臟讀, 也就是可能讀取到其他會話中未提交事務修改的資料
• 提交讀(Read Committed): 只能讀取到已經提交的資料. Oracle等多數資料庫默認都是該級別 (不重復讀)
• 可重復讀(Repeated Read): 可重復讀. 在同一個事務內的查詢都是事務開始時刻一致的, InnoDB默認級別. 在SQL標準中, 該隔離級別消除了不可重復讀, 但是還存在幻象讀
• 串行讀(Serializable): 完全串行化的讀, 每次讀都需要獲得表級共享鎖, 讀寫相互都會阻塞

不同的讀:

• 臟讀: 臟讀就是指當一個事務正在訪問資料, 并且對資料進行了修改, 而這種修改還沒有提交到資料庫中, 這時, 另外一個事務也訪問這個資料, 然后使用了這個資料.
• 不可重復讀: 是指在一個事務內多次讀同一資料, 而在這個事務還沒有結束時另外一個事務也訪問并修改同一資料. 那么在第一個事務中的兩次讀資料之間, 由于第二個事務的修改, 讀到的的資料不一樣了. 這樣就發生了在一個事務內兩次讀到的資料不一致, 稱為不可重復讀.
• 幻讀: 第一個事務先開始, 其修改涉及到某個表的全部資料行, 在未提交時第二個事務向這個表插入一行新資料. 此時會發生操作第一個事務的用戶發現表中存在沒有修改的資料行, 這稱為幻讀.

有多少種事務失效的場景, 如何解決?

• 如使用MyISAM, 則事務會不起作用
• 如果使用了 SpringMVC, 則 context:component-scan 重復掃描問題可能會引起事務失敗.
• @Transactional 注解開啟配置, 必須放到 Listener 里加載, 如果放到 DispatcherServlet 的配置里, 事務也是不起作用的
• @Transactional 注解只能應用到 public 可見度的方法上. 如果你在 protected, private 或者 package-visible 的方法上使用 @Transactional 注解, 它不會報錯, 但是事務也無效
• 在具體的類(或類的方法)上使用 @Transactional 注解, 而不要使用在任何介面上. 在介面上使用 @Transactional 注解, 只在你設定了基于介面的代理時它才生效

一致性非鎖定讀和一致性鎖定讀是什么?

一致性非鎖定讀 consistent nonlocking read 是InnoDB存盤引擎通過多版本控制 multi versioning 的方式來讀取當前執行時間資料庫中的資料.

如果被讀的資料行被加了排他鎖, 在讀取這行資料的時候并不會等待鎖釋放, 而是讀取該行的一個快照資料. 之所以稱為非鎖定讀, 因為不需要等待被訪問行的X鎖的釋放. 快照資料是指修改行之前的資料版本, 該實作通過undo段來完成. 非鎖定讀的方式極大提高了資料庫的并發性. 在InnoDB存盤引擎中, 這是默認的讀取方式.

在默認情況下, InnoDB存盤引擎對資料采用的是一致性非鎖定讀. 但是有些情況下為了保證資料邏輯的一致性, 需要對SELECT的操作加鎖. InnoDB 存盤引擎對于 SELECT 陳述句支持兩種一致性的鎖定讀(locking read)操作

1. SELECT …… FOR UPDATE
2. SELECT …… LOCK IN SHARE MODE

Innodb如何解決幻讀?

幻讀問題是指一個事務的兩次不同時間的相同查詢回傳了不同的的結果集. 例如: 一個 select 陳述句執行了兩次, 但是在第二次回傳了第一次沒有回傳的行, 那么這些行就是 Phantom Row.

Read view(或者說 MVCC)實作了一致性非鎖定讀(Consistent Nonlocking Reads), 從而避免了幻讀.

一致性讀是通過 MVCC 為查詢提供了一個基于時間的點的快照, 這個查詢只能看到在自己之前提交的資料, 而在查詢開始之后提交的資料是不可以看到的. 一個特例是,這個查詢可以看到于自己開始之后的同一個事務產生的變化.

談談MySQL的鎖并發?

談談Innodb行鎖?

相對于串行處理來說, 并發事務處理能大大增加資料庫資源的利用率, 提高資料庫系統的事務吞吐量, 從而可以支持更多的用戶. 但并發事務處理也會帶來一些問題, 主要包括以下幾種情況

• 更新丟失(Lost Update): 當兩個或多個事務選擇同一行, 然后基于最初選定的值更新該行時, 由于每個事務都不知道其他事務的存在, 就會發生丟失更新問題 -- 最后的更新覆寫了由其他事務所做的更新. 例如, 兩個編輯人員制作了同一檔案的電子副本. 每個編輯人員獨立地更改其副本, 然后保存更改后的副本, 這樣就覆寫了原始檔案. 最后保存其更改副本的編輯人員覆寫另一個編輯人員所做的更改. 如果在一個編輯人員完成并提交事務之前, 另一個編輯人員不能訪問同一檔案, 則可避免此問題.
• 臟讀(Dirty Reads): 一個事務正在對一條記錄做修改, 在這個事務完成并提交前, 這條記錄的資料就處于不一致狀態；這時, 另一個事務也來讀取同一條記錄, 如果不加控制, 第二個事務讀取了這些“臟”資料, 并據此做進一步的處理, 就會產生未提交的資料依賴關系. 這種現象被形象地叫做"臟讀".
• 不可重復讀(Non-Repeatable Reads): 一個事務在讀取某些資料后的某個時間, 再次讀取以前讀過的資料, 卻發現其讀出的資料已經發生了改變、或某些記錄已經被洗掉了！這種現象就叫做“不可重復讀”.
• 幻讀(Phantom Reads): 一個事務按相同的查詢條件重新讀取以前檢索過的資料, 卻發現其他事務插入了滿足其查詢條件的新資料, 這種現象就稱為“幻讀”.

InnoDB實作了以下兩種型別的行鎖.

• 共享鎖(S): 允許一個事務去讀一行, 阻止其他事務獲得相同資料集的排他鎖.
• 排他鎖(X): 允許獲得排他鎖的事務更新資料, 阻止其他事務取得相同資料集的共享讀鎖和排他寫鎖.

另外, 為了允許行鎖和表鎖共存, 實作多粒度鎖機制, InnoDB還有兩種內部使用的意向鎖(Intention Locks), 這兩種意向鎖都是表鎖.

• 意向共享鎖(IS): 事務打算給資料行加行共享鎖, 事務在給一個資料行加共享鎖前必須先取得該表的IS鎖.
• 意向排他鎖(IX): 事務打算給資料行加行排他鎖, 事務在給一個資料行加排他鎖前必須先取得該表的IX鎖.

死鎖及監控是什么?

死鎖是由于兩個或兩個以上會話鎖等待產生回路造成

死鎖監控及處理方法: 對于死鎖的監控, 各個版本都提供了innodb_print_all_deadlocks選項, 打開該選項即會將死鎖的日志輸出到MySQL的錯誤日志當中, 因此可以通過監控錯誤日志來達到監控死鎖的目的

如何降低死鎖發生的概率

1. 盡量使用短小事務, 避免大事務
2. 加FOR UPDATE/LOCK IN SHARE MODE鎖時, 最好降低事務隔離級別, 例如用RC(Read Committed, 已提交讀)級別, 降低死鎖發生概率, 也可以降低鎖定粒度. 默認是Repeatable Read, 可重復讀.
3. 事務中涉及多個表, 或者涉及多行記錄時, 每個事務的操作順序都要保持一致
4. 通過索引優化SQL效率, 降低死鎖概率, 避免全表掃描導致鎖定所有資料
5. 程式中應有事務失敗檢測及自動重復提交機制
6. 高并發(例如秒殺)場景中, 關閉innodb_deadlock_detect選項, 降低死鎖檢測開銷, 提高并發效率

自增長與鎖 , 鎖的演算法, 鎖問題, 鎖升級是什么?

首先, mysql的記錄插入有三種情況:

• Simple inserts, 簡單插入, 執行前就能確定插入的記錄個數
• Bulk inserts, 批量插入, 像inset select這種, 在插入前不確定記錄個數
• Mixed inserts, 一次插入多條資料, 但是其中部分資料指定了自增ID的值的情況.

5.1.22之前InnoDB自增值是通過其本身的自增長計數器來獲取值, 該實作方式是通過表鎖機制來完成的AUTO-INC LOCKING, 鎖不是在每次事務完成后釋放, 而是在完成對自增長值插入的SQL陳述句后釋放, 要等待其釋放才能進行后續操作. 比如說當表里有一個auto_increment欄位的時候, innoDB會在記憶體里保存一個計數器用來記錄auto_increment的值, 當插入一個新行資料時, 就會用一個表鎖來鎖住這個計數器, 直到插入結束. 如果大量的并發插入, 表鎖會引起SQL堵塞.

5.1.22之后InnoDB為了解決自增主鍵鎖表的問題, 引入了引數innodb_autoinc_lock_mode, 該實作方式是通過輕量級互斥量的增長機制完成的, 專門用來在使用auto_increment的情況下調整鎖策略. innodb_autoinc_lock_mode可以設定3個值: 0, 1, 2, 默認使用的是1

• 0: traditonal傳統的, 通過表鎖的方式進行, 所有型別的insert都用 auto-inc locking
• 1: consecutive連續的, 默認值, 這種模式下insert陳述句在開始時會獲得一個表鎖autoinc_lock, simple insert在獲取到需要增加的ID的量后autoinc_lock就會被釋放, 不必等到陳述句執行結束. 但對于bulk insert, 自增鎖會被一直持有直到陳述句執行結束才會被釋放.
• 2: interleaved插入式的隔行掃描的, 這種模式下沒有auto_inc 鎖了, 不會鎖表, 并發性能最高. 這時候產生的自增ID, 是跨陳述句分布的, 并發執行的插入陳述句所產生的記錄的ID可能并不連續. 如果是simple inserts, 那么單個陳述句內ID還是連續的, 但是像bulk insert單個陳述句內就未必是連續了. 這種模式可能會導致Statement 的 Replication 出現不一致, 使用該模式, 需要用 Row Replication的模式

樂觀鎖的執行緒如何做失敗補償?

失敗后自動重試, 設定好次數限制

高并發場景如何防止死鎖, 保證資料一致性?

資料庫使用version做樂觀鎖避免更新被覆寫和超賣
使用佇列串行化請求, 避免峰值
使用計數器切換活動狀態, 達到一定量后立即關倍訓動, 降低服務器負載

標籤：其他

上一篇：MySQL資料庫索引介紹

下一篇：SQL與資料庫編程學習筆記-day2