Mysql事務與鎖

數據庫并發事務

????????數據庫一般都會并發執行多個事務，多個事務可能會并發的對相同的一批數據進行增刪改查操作，可能就會導致我們說的臟寫、臟讀、不可重復讀、幻讀這些問題。為了解決這些并發事務的問題，數據庫設計了事務隔離機制、鎖機制、MVCC多版本并發控制隔離機制，用一整套機制來解決多事務并發問題。

事務特性：ACID

原子性（Atomicity）:
一致性（Consistent）:
隔離性（Isolation）:
持久性（Durable）:

事務并發可能帶來的問題

丟失更新或者臟寫：兩個事務同時更新一個數據，后提交的事務會把先提交的結果
臟讀：A事務讀到了B事務未提交的數據。?如果B事務回滾，A則讀到了一個無效數據。
不可重復讀：在一個事務中連續對同一數據讀多次，讀到的結果不一致。（其他事務做了修改）
幻讀：在一個事務中，相同的條件多次統計記錄條數，個數不一致。（其他事務新增了滿足其條件的數據）

事務隔離級別

-- 看當前數據庫的事務隔離級別
show variables like 'tx_isolation';
-- 設置事務隔離級別
set tx_isolation='REPEATABLE-READ';

????????Mysql默認的事務隔離級別是可重復讀，用Spring開發程序時，如果不設置隔離級別默認用Mysql設置的隔離級別，如果Spring設置了就用已經設置的隔離級別。

讀未提交：
讀已提交（RC）:
可重復讀（RR）：使用了MVCC機制， select是讀的事務開始時的快照版，update則是當前讀，讀的是數據庫中最新的值。
串行化：

在一個事務中，讀到的內容都是事務開始時的一個快照，即讀取的內容不變；但如果當前事務對一個記錄做修改（排他鎖，其他事務無法對這個記錄做修改），修改的記錄再重新讀取，讀到的是最新的。

問題：隔離級別底層是怎么實現的呢？

樂觀鎖不要用在可重復讀級別，要在更小的兩個隔離級別用
通過快照讀取，但并不是每個事務都創建一個對應的快照
RC對并發要求高；RR對同一時間維度要求高

鎖

間隙鎖：鎖的是兩個值之間的空隙。

臨鍵鎖（Next-key Locks）：行鎖和間隙鎖的組合，包含范圍的邊界。

樂觀鎖：java中用版本號實現。update tableA set amount = 200 where id = 1 and version=1;

悲觀鎖：java在sql中直接執行。update tableA set amount = amount + 500 where id=1

頁鎖：只有BDB存儲引擎支持葉鎖，鎖的資源比行鎖多，比表鎖少。考慮索引樹種的一個節點，就是對應一頁，相對于對這一頁加鎖。

讀/寫鎖/意向鎖

-- 手動增加表鎖
lock table 表名稱 read(write), 表名稱2 read(write);
-- 查看表上加過的鎖
show open tables;
-- 刪除表鎖
unlock tables;
-- 共享鎖/讀鎖
select * from T where id=1 lock in share mode
-- 排他鎖/寫鎖
select * from T where id=1 for update

讀鎖：其他事務可讀，不可寫（阻塞）；當前事務可讀，不可寫
寫鎖：其他事務不可讀，不可寫（阻塞）；當前事務可讀，可寫
意向鎖（Intention Lock）：當有事務給表的數據行加了共享鎖或排他鎖，同時會給表設置一個標識，代表已經有行鎖了，其他事務要想對表加表鎖時，就不必逐行判斷有沒有行鎖可能跟表鎖沖突了，直接讀這個標識就可以確定自己該不該加表鎖。這個標識就是意向鎖。相當于對表加鎖。分為意向共享鎖（IS）和意向排他鎖（IX）

行/表鎖

行鎖添加方式

1. 更新操作where條件使用索引列

????????>update account set balance = 800 where name ='lilei';

2. lock in share mode(共享鎖)?

? ? ? ? >select * from T where id = 1 lock in share mode;

3. 排他鎖for update

????????>select * from test_innodb_lock where a=2 for update

無索引行鎖會升級為表鎖

????????鎖主要是加在索引上，如果對非索引字段更新，行鎖可能會變表鎖。

????????session1 執行：update account set balance = 800 where name ='lilei';

????????session2 對該表任一行操作都會阻塞住

????????InnoDB的行鎖是針對索引加的鎖，不是針對記錄加的鎖。并且該索引不能失效，否則都會從行鎖升級為表鎖。注意：并不是直接將整張表加上表鎖，因為有的記錄可能被其他事務鎖住。（那這種情況是否能加鎖成功呢？）

????????RR級別會升級為表鎖，RC級別不會升級為表鎖。（為什么？）

行鎖競爭分析

show status like 'innodb_row_lock%'

對各個狀態量的說明如下：

Innodb_row_lock_current_waits: 當前正在等待鎖定的數量

Innodb_row_lock_time: 從系統啟動到現在鎖定總時間長度

Innodb_row_lock_time_avg: 每次等待所花平均時間

Innodb_row_lock_time_max：從系統啟動到現在等待最長的一次所花時間

Innodb_row_lock_waits:系統啟動后到現在總共等待的次數

對于這幾個狀態變量，比較重要的主要是：

Innodb_row_lock_time_avg （等待平均時長）

Innodb_row_lock_waits （等待總次數）

Innodb_row_lock_time（等待總時長）

尤其是當等待次數很高，而且每次等待時長也不小的時候，我們就需要分析系統中為什么會有如此多的等待，然后根據分析結果著手制定優化計劃。

InnoDB與MYISAM的最大不同

InnoDB支持事務（TRANSACTION）
InnoDB支持行級鎖

問題：為啥MyISAM不支持行級鎖？

MyISAM在執行查詢語句SELECT前，會自動給涉及的所有表加讀鎖，在執行update、insert、delete操作會自動給涉及的表加寫鎖。
InnoDB在執行查詢語句SELECT時(非串行隔離級別)，不會加鎖。但是update、insert、delete操作會加行鎖。

鎖相關幾個問題：

1、Mysql默認的隔離級別是可重復讀，有辦法可以解決幻讀嗎？

答：間隙鎖在某些情況下可以解決幻讀

那么間隙就有 id 為 (3,10)，(10,20)，(20,正無窮) 這三個區間，

在Session_1下面執行 update account set name ='zhuge' where id > 8 and id <18;，則其他Session沒法在這個范圍所包含的所有行記錄(包括間隙行記錄)以及行記錄所在的間隙里插入或修改任何數據，即id在(3,20]區間都無法修改數據，注意最后那個20也是包含在內的。(3,20]整個區間可以叫做臨鍵鎖。

間隙鎖是在可重復讀隔離級別下才會生效。間隙鎖是為了解決幻讀問題，所以作用在可重復讀級別。實際測試下來確實可可重復讀級別間隙鎖才生效。

2、多個查詢操作，是否要放在事務中？

答：這個問題主要是考察事務并發問題，以及解決這些問題需要的隔離級別。

如果要保證讀取數據的一致性，那必須放在事務中，且這個事務是可重復讀的隔離級別。

3、事務中，有讀和寫操作，哪個放前？

答：更新等涉及到加鎖的操作盡可能放在事務靠后的位置。目的是減少鎖住的時間。