事務和鎖機制

事務的使用

1. 什麼是事務
    (事務)transactions, 顧名思義就是要做或所做的事情, 資料庫事務指的是作為單個工作單位執行的一系列操作(sql語句).
    這些操作要麼全部執行. 要麼全部都不執行


2. 為什麼要有事務
    將一系列sql語句放到一個事務裡面有兩個目的:
        1. 為資料庫操作提供一個從失敗中恢復到正常狀態的方法. 同時提供了資料庫即使在異常狀態下仍能保持一致性的方法
        2. 當多個應用該程式在並發訪問資料庫時. 可以在這些應用程式之間你提供一個隔離方法. 以防止彼此的操作互相干擾

事務有四大特性(ACID)

   1. **原子性(atomicity)**
      事務的一個整體被執行. 包含在其中的對資料庫的操作要麼全部被執行. 要麼都不執行
   2. **一致性(consistency)**
      事務應該確保資料庫的狀態從一個一直狀態到另一個一直狀態.例如轉賬行為中.一個人減了50元. 另外一個人就應該加上50元 而不是40元
      其他一致狀態的含義是資料庫中的數據應該滿足完整性約束. 例如欄位約束不能為負數. 事務執行完畢後的該欄位頁同樣同樣不能是負數
   3. **隔離性(Isolation)**
      多個事務並發執行時. 一個事務的執行不應影響其他事務執行
   4. **持久性(Durability)**
      一個事務一旦提交. 他對資料庫的修改應該永久保存在資料庫中

mysql事務的三種運行模式

  1. 自動提交事務(隱式開啟. 隱式提交)
  2. 隱式事務(隱式開啟. 顯示提交)
  3. 顯示事務(顯示開啟. 顯示提交)

開啟事務:
start transaction; -- 開啟事務
update test set age = 32312 where id = 1; -- 修改數據
commit; -- 提交
rollback; -- 回滾

commit 和 rollback事務都結束了

pymysql中隱式開啟. 顯示提交

try:
cursor.execute(sql_1)
cursor.execute(sql_2)
cursor.execute(sql_3)
except Exception as e:
connect.rollback() # 事務回滾
print("事務處理失敗", e)
else:
connect.commit() # 事務提交
print('事務處理成功', cursor.rowcount) # 關閉連接
cursor.close()
connect.close()

事務的控制語句

start transaction（或 begin）：顯式開始一個新事務 #開啟事務
savepoint：分配事務過程中的一個位置，以供將來引用 #臨時存檔
commit：永久記錄當前事務所做的更改 #提交
rollback：取消當前事務所做的更改 #回滾
rollback to savepoint：取消在 savepoint 之後執行的更改 #回到存檔點
release savepoint：刪除 savepoint 標識符 #刪除臨時存檔
set autocommit：為當前連接禁用或啟用默認 autocommit 模式

事務的使用原則

  1. 保持事務短小
  2. 盡量避免事務中rollback
  3. 盡量避免savepoint
  4. 顯式聲明打開事務
  5. 默認情況下，依賴於悲觀鎖，為吞吐量要求苛刻的事務考慮樂觀鎖
  6. 鎖的行越少越好，鎖的時間越短越好

資料庫讀現象

資料庫讀現象: 多個事務並發執行的時候. 在讀數據方面可能會遇到各種各樣的問題: 臟讀. 不可重複讀. 幻讀

臟讀(dirty read)

​ 事務T2更新了一行記錄的內容，但是並沒有提交所做的修改。
事務T1讀取更新後的行，然後T1執行回滾操作，取消了剛才所做的修改。此時T1所讀取的行就無效了，稱之為臟數據

不可重複讀(nonrepeatable read)

​ 事務T1讀取一行記錄，緊接著事務T2修改了T1剛才讀取的那一行記錄並且提交了。
然後T1又再次讀取這行記錄，發現與剛才讀取的結果不同。這就稱為「不可重複」讀，因為T1原來讀取的那行記錄已經發生了變化。

幻讀(phantom read)

​ 事務T1讀取或修改了指定的where子句所返回的結果集。
然後事務T2新插入一行記錄，這行記錄恰好可以滿足T1所使用的查詢條件中的WHERE 子句的條件。
然後T1又使用相同的查詢再次對錶進行檢索，但是此時卻看到了事務T2剛才插入的新行或者發現了處於where子句範圍內，但卻未曾修改過的記錄。
就好像「幻覺」一樣，因為對T1來說這一行就像突然出現的一樣。
一般解決幻讀的方法是增加範圍鎖RangeS，鎖定檢鎖範圍為只讀，這樣就避免了幻讀。

解決方案

​ 其實，臟寫、臟讀、不可重複讀、幻讀，都是因為業務系統會多執行緒並發執行，每個執行緒可能都會開啟一個事務，每個事務都會執行增刪改查操作。
​ 然後資料庫會並發執行多個事務，多個事務可能會並發地對快取頁里的同一批數據進行增刪改查操作，於是這個並發增刪改查同一批數據的問題，可能就會導致我們說的臟寫、臟讀、不可重複讀、幻讀這些問題。
​ 所以這些問題的本質，都是資料庫的多事務並發問題，那麼為了解決多事務並髮帶來的臟讀、不可重複讀、幻讀等讀等問題，
​ 資料庫才設計了鎖機制、事務隔離機制、MVCC 多版本隔離機制，用一整套機制來解決多事務並發問題

mysql鎖機制即其他知識點

1. 什麼是鎖
    鎖是電腦協調多個進程或執行緒並發訪問某一資源的機制

2. 為什麼要加入鎖機制
    當並發事務訪問一個共享資源的時. 有時候可能導致數據不一致.數據無效等問題
    而鎖機制可以將並發的數據訪問順序化，以保證資料庫中數據的一致性與有效性
    此外，鎖衝突也是影響資料庫並發訪問性能的一個重要因素，鎖對資料庫而言顯得尤其重要，也更加複雜。

3. 怎麼加鎖
    對於insert. update. delete語句. innodb會自動給涉及的數據加鎖. 而且是排他鎖.
    對於普通的select語句. innodb不會加任和鎖. 需要我們手動自己加. 可以加兩種類型的鎖
    1. 共享鎖: select ... lock in share mode;  -- 查出的記錄行都會被鎖住
    2. 排他鎖: select ... for update;  -- 查出的記錄行都會被鎖住

鎖介紹

共享鎖(Share lock): 共享鎖就是多個事務對於同一數據可以共享一把鎖，獲准共享鎖的事務只能讀數據，
不能修改數據直到已釋放所有共享鎖，所以共享鎖可以支援並發讀
如果事務T對數據A加上共享鎖後，則其他事務只能對A再加共享鎖或不加鎖（在其他事務里一定不能再加排他鎖，但是在事務T自己裡面是可以加的）
排它鎖(eXclusive Lock): 排他鎖就是不能與其他所並存，如一個事務獲取了一個數據行的排他鎖，
其他事務就不能再對該行加任何類型的其他他鎖（共享鎖和排他鎖），但是獲取排他鎖的事務是可以對數據就行讀取和修改。

鎖中出現的一些問題

innodb行鎖的特性:

  1. 如果沒有命中索引，InnoDB 會通過隱藏的聚簇索引來對記錄加鎖。
    也就是說：如果不通過索引條件檢索數據，那麼InnoDB將對表中所有數據加鎖，實際效果跟表級鎖一樣。
    例如: id沒有primary key主鍵索引. 鎖行相當於鎖表
  2. 如果一條sql語句操作了主鍵索引. mysql就會鎖住這條語句就會命中這條主鍵索引
  3. 如果一條sql語句操作了非主鍵索引. mysql會先鎖住該非主鍵索引. 在鎖定相關的主鍵索引

在實際應用中要非常注意innodb行鎖這個特性. 不然的話. 可能出現各種鎖衝突. 從而影響並發性能
1、在不通過索引條件查詢的時候,InnoDB 的效果就相當於表鎖
2、當表有多個索引的時候,不同的事務可以使用不同的索引鎖定不同的行,另外,不論 是使用主鍵索引、唯一索引或普通索引,InnoDB 都會使用行鎖來對數據加鎖。
3、由於 MySQL 的行鎖是針對索引加的鎖,不是針對記錄加的鎖,所以即便你的sql語句訪問的是不同的記錄行,但如果命中的是相同的被鎖住的索引鍵，也還是會出現鎖衝突的。
4、即便在條件中使用了索引欄位,但是否使用索引來檢索數據是由 MySQL 通過判斷不同 執行計劃的代價來決定的,如果 MySQL 認為全表掃 效率更高,比如對一些很小的表,它 就不會使用索引,這種情況下 InnoDB 將鎖住所有行，相當於表鎖。因此,在分析鎖衝突時, 別忘了檢查 SQL 的執行計劃,以確認是否真正使用了索引

死鎖問題

  1. 簡單情況
    事務一:
    begin;
    select * from employee where id = 1 for update; -- 第一步(加上了排它鎖)

    update employee set name = "xxx" where id = 3; -- 第三步, 阻塞

    事務二:
    begin;
    delete from employee where id = 3; -- 第二步(默認排它鎖)

    update employee set name = "yyy" where id = 1; -- 第四部, 阻塞

  2. 複雜情況: 有innodb行鎖特性導致的 -> 如果命中輔助索引. 則先鎖住輔助索引在鎖住主鍵索引
    兩個事務. 每個事務就一個sql語句. 但是每個事務都是命中輔助索引. 交叉將對方鎖住. 導致出現了死鎖現象

三種行鎖演算法

  1. Record lock : 單個行記錄上的鎖
  2. Gap lock: 間隙鎖. 鎖定一個範圍. 但是不包括記錄本身.gap鎖的目的是為了防止同一事務的兩次當前讀. 出現幻讀現象
  3. Next-lock: 等於Record Lock結合Gap Lock. 注意: Innodb儲存引擎還會對輔助索引下一個鍵值加上GAP lock

​ innodb採用的都是next-key-lock. 主要目的是解決幻讀的問題.

事務隔離機制

​ mysql默認使用的是Repeatable read機制 + next-lock解決臟讀. 幻讀等各種問題
​ django框架中採用的Read committed機制. 其自身框架會解決不可重複讀和幻讀問題

多版本並發控制MVCC

​ 快照讀: 不加鎖讀
​ 當前讀: 加鎖讀取

​ MVCC好處: 讀不加鎖，讀寫不衝突
​ MVCC原理分析: //www.cnblogs.com/linhaifeng/articles/14424181.html

Tags:

VirMach 便宜 VPS