文章目錄
- 一、innodb核心特性事務
- 1、什麼是事務
- 2、如何使用事務
- 3、事務示範
- 4、事務通俗了解
- 5、一個成功事務的生命周期
- 6、一個失敗事務的生命周期
- 7、事務的特性 ACID
- 8、事務的控制語句
- 1）自動送出
- 2）事務的隐式送出
- 3）控制語句
- 二 、MySQL事務的3種運作模式
- 1、自動送出事務（隐式開啟、隐式送出）
- 2、隐式事務\\（隐式開啟、顯式送出）
- 3、顯式事務（ 顯式開啟、顯式送出）
- 4、 事務儲存點
- 三、 事務的使用原則
- 四、資料庫讀現象
- 1、四種隔離級别
- Mysql預設使用的資料隔離級别是REPEATABLE READ ，可重複讀，允許幻讀。
- 2、事務并發常見問題執行個體示範 --- 資料庫讀現象
- 2.1髒讀
- 1）修改自動送出事務開關
- 2）修改資料庫的事務隔離級别
- 3）終端1查詢使用者指定資訊（此處不送出事務）
- 4）終端2查詢使用者資訊
- 2.2不可重複讀
- 1）修改事務隔離級别
- 2）視窗1開啟事務，查詢指定使用者資料
- 3）視窗2同時執行事務
- 2.3 幻讀
- 2）視窗1
- 3）視窗2：此處，事務A中查詢指定id為5的記錄，沒有查詢到結果後，事務B進行插入了一條id為5的記錄并且送出事務；
- 3、修改隔離級别
- 4、解決方案
- 五、事務中的鎖
- 1.什麼是鎖？鎖的作用
- 2.示例
- 3.鎖的種類分類
- 4.鎖的粒度分類
- 1) 表級鎖 （偏向于讀）
- 2) 行級鎖
- 3) 頁級鎖
- 5.鎖的類别分類
- 1、鎖的使用
- 6、多版本并發控制（MVCC）
- 7、事務的隔離級别
- 8.髒讀，幻讀，不可重複讀
- 六、事務的日志
- 1.redo log
- 2.undo log
- 1）作用
- 七、 innodb存儲引擎的鎖機制
- 1、行級鎖有三種算法：
- 2、總結
- 3、死鎖
- 1、結論
- 2、有多種方法可以避免死鎖
- 4 什麼時候使用表鎖（了解）
- 5 行鎖優化建議
- 八、資料庫總結
一、innodb核心特性事務
1、什麼是事務
# 什麼是事務
顧名思義就是要做的事情，事務就相當于一個盛放sql的容器
事務中的sql要麼全部執行成功，要麼所有已經修改的操作都復原到原來的狀态，即一條sql也别想成功
開啟一個事務可以包含多條sql語句，這樣sql語句要麼全部成功，或者全部失敗，成為事務的原子性
# 作用
保證了資料操作的安全性，一緻性 2、如何使用事務
# 事務相關的關鍵字
# 1、開啟事務
start transaction
# 2、復原（回到事務執行之前的狀态）
rollback
# 3、确認（确認之後就無法復原了）
commit
# 總結：
當你想讓sql語句同時保證資料的一緻性，要麼同時成功，要麼同時失敗，那麼就可以考慮使用事務 3、事務示範
#1.建立一個表
mysql> create table jiaoyi(id int primary key auto_increment,name varchar(10),money int);
#2.插入兩條資料
mysql> insert into jiaoyi values(1,'qiudao',100);
Query OK, 1 row affected (0.01 sec)
mysql> insert into jiaoyi values(2,'oldboy',200);
Query OK, 1 row affected (0.00 sec)
#3.再打開一個視窗
mysql> select from jiaoyi;
資料與原視窗檢視結果一緻
#4.開啟一個事務（符合邏輯）
mysql> start transaction； #開啟事務
# begin 也是開啟事務
mysql> begin;
mysql> update jiaoyi set money=0 where name='qiudao';
mysql> select from jiaoyi;
mysql> update jiaoyi set money=400 where name='oldboy';
mysql> select from jiaoyi;
#在執行commit之前，另一個視窗是看不到資料變化的
mysql> commit;
#執行commit之後其他人都能看到資料的變化
#5.開啟一個事務（不符合邏輯）
mysql> start transaction；
mysql> update jiaoyi set money=-100 where name='qiudao';
mysql> select from jiaoyi;
+------+--------+-------+
| id | name | money |
+------+--------+-------+
| 1 | qiudao | -100 |
| 2 | oldboy | 400 |
+------+--------+-------+
2 rows in set (0.00 sec)
# 6、由于錢的值不能為負數，由邏輯判斷，操作失敗，復原
mysql> rollback; #復原事務
mysql> select from jiaoyi;
+------+--------+-------+
| id | name | money |
+------+--------+-------+
| 1 | qiudao | 0 |
| 2 | oldboy | 400 |
+------+--------+-------+
2 rows in set (0.00 sec) 4、事務通俗了解
#伴随着“交易”出現的資料庫概念。
我們了解的“交易”是什麼？
1）物與物的交換（古代）
2）貨币現金與實物的交換（現代1）
3）虛拟貨币與實物的交換（現代2）
4）虛拟貨币與虛拟實物交換（現代3）
資料庫中的“交易”是什麼？
1）事務又是如何保證“交易”的“和諧”？
2）ACID 5、一個成功事務的生命周期
start transaction； #begin 開啟事務
sql1 #真正事務處理的時候，是第一條sql語句執行完，才是開啟事務的
sql2
sql3
...
commit;
# 若用了begin手動開始編輯事務，編輯完後隻要commit手動送出，另一端則能查詢到資料 6、一個失敗事務的生命周期
start transaction；
sql1
sql2
sql3
...
rollback;
# 若用了begin手動開始編輯事務，編輯完後且隻要不commit手動送出，另一端則查詢不到資料 7、事務的特性 ACID
ACID 四大特性 #ACID
# A: 原子性 (Atomic)
一個事務是一個不可分割的機關，事務中包含的諸多操作，要麼同時失敗，要麼同時成功
所有語句作為一個單元全部成功執行或全部取消。
# C:一緻性 （Consistent）
事務必須是是資料庫一緻性的狀态變成另外一個一緻性的狀态，一緻性根原子性密切相關的,
如果資料庫在事務開始時處于一緻狀态，則在執行該事務期間将保留一緻狀态。
# I：隔離性 (Isolated)
一個事務的執行不能被其他事務幹擾,事務之間不互相影響。
# D：持久性 (Durable)
也可以稱為永久性，一個事務一旦送出成功執行成功，那麼它就是對資料庫中的資料修改是永久的
接下來的其他操作或者故障不應該對其任何的影響
事務成功完成後，所做的所有更改都會準确地記錄在資料庫中。所做的更改不會丢失。
我們可以分析一下，事務的四大特征中，所有的操作都會走向磁盤，是以持久性是事務操作的目的，而原子性是實作事務的基礎，隔離性是實作資料安全的一種政策、手段，而最終維護的，就是資料的一緻性，一緻性才是事務中最重要的。四大特征之間，隔離性是為了達到一緻性的手段。
ACID四大特征中，最難了解的不是一緻性，而是事務的隔離性，資料庫權威專家針對事務的隔離性研究出來了事務的隔離四種級别，四種事務隔離級别就是為了解決資料在高并發下産生的問題（髒讀、不可重複讀、幻讀）。 8、事務的控制語句
1）自動送出
# 0.手動送出
mysql> commit；
#1.檢視自動送出
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.01 sec)
#2.臨時關閉
set autocommit =0;
#3.永久關閉
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
autocommit=0 2）事務的隐式送出
1）現在版本在開啟事務時，不需要手工start transaction；，隻要你輸入的是DML語句，就會自動開啟事務。
2）有些情況下事務會被隐式送出
# 隐式送出觸發條件
1.執行事務沒有commit時，如果使用了DDL或者DCL會自動送出上一條事務
2.執行事務沒有commit時，如果你手動執行begin，會自動送出上一條事務
3.執行事務沒有commit時，如果執行鎖表（lock tables）或者解鎖（unlock tables），會自動送出上一條事務
4.load data infile（導資料）會自動送出上一條事務
5.select for update
6.在autocommit=1的時候，會自動送出上一條事務
update ....
commit;
insert into ....
begin;
update ....
create table ....
delete .... 3）控制語句
begin（或 start transaction）：顯式開始一個新事務，推薦begin #開啟事務
savepoint：配置設定事務過程中的一個位置，以供将來引用 #臨時存檔
commit：永久記錄目前事務所做的更改 #送出事務
rollback：取消目前事務所做的更改 #復原
roolback to savepoint：取消在 savepoint 之後執行的更改 #回到存檔點
release savepoint：删除 savepoint 辨別符 #删除臨時存檔
set autocommit：為目前連接配接禁用或啟用預設 autocommit 模式 #臨時開關自動送出
PS：永久開啟或關閉autocommit，則在配置檔案（my.cnf）插入一行：
autocommit=1 # 開啟狀态
autocommit=0 # 關閉狀态 二 、MySQL事務的3種運作模式
隐式 == 自動
顯式 == 手動
1、自動送出事務（隐式開啟、隐式送出）
此乃mysql預設的事務運作模式
mysql預設為每條sql開啟事務，并且會在本條sql執行完畢後自動執行commit送出 2、隐式事務\（隐式開啟、顯式送出）
既然mysql預設是為每條sql都開啟了事務并且在該sql運作完畢後會自動送出那麼我隻需要将自動送出關閉即可變成“隐式開啟、顯式送出”
#1.臨時關閉
set autocommit =0;
show variables like 'autocommit'; -- 檢視
#2.永久關閉
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
autocommit=0 3、顯式事務（ 顯式開啟、顯式送出）
手動開啟的事務裡預設不會自動送出是以我們可以将要執行的sql語句放在我們自己手動開啟的事務裡，如此便是顯式開啟、顯式送出
start transaction;
update test.t1 set id=33 where name = "jack";
commit;
# 注意，重要的事說三遍
這種方式在當你使用commit或者rollback後，事務就結束了
再次進入事務狀态需要再次start transaction
# 注意，重要的事說三遍
這種方式在當你使用commit或者rollback後，事務就結束了
再次進入事務狀态需要再次start transaction
# 注意，重要的事說三遍
這種方式在當你使用commit或者rollback後，事務就結束了
再次進入事務狀态需要再次start transaction 無論事務是顯式開啟還是隐式開啟，事務會在某些情況下被隐式送出
# 隐式送出觸發條件
1.執行事務沒有commit時，如果使用了DDL或者DCL會自動送出上一條事務
2.執行事務沒有commit時，如果你手動執行begin，會自動送出上一條事務
3.執行事務沒有commit時，如果執行鎖表（lock tables）或者解鎖（unlock tables），會自動送出上一條事務
4.load data infile（導資料）會自動送出上一條事務
5.select for update 加鎖
6.在autocommit=1的時候，會自動送出上一條事務
# 案例：
try:
# begin;
start transaction;
update user set balance=900 where name='wsb'; #買支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
update user set balance=1090 where name='ysb'; #賣家拿到90元
execpt 異常；
rollback；
else:
commit; 4、 事務儲存點
savepoint和虛拟機中的快照類似，用于事務中，沒設定一個savepoint就是一個儲存點，當事務結束時會自動删除定義的所有儲存點，在事務沒有結束前可以回退到任意儲存點
1、設定儲存點savepoint 儲存點名字
2、復原到某個儲存點，該儲存點之後的操作無效，rollback 某個儲存點名
3、取消全部事務，删除所有儲存點rollback
# 注意：rollback和commit都會結束掉事務，這之後無法再回退到某個儲存點 最後總結一下事務的控制語句
START TRANSACTION（或 BEGIN）：顯式開始一個新事務 #開啟事務
SAVEPOINT：配置設定事務過程中的一個位置，以供将來引用 #臨時存檔
COMMIT：永久記錄目前事務所做的更改 #送出
ROLLBACK：取消目前事務所做的更改 #復原
ROLLBACK TO SAVEPOINT：取消在 savepoint 之後執行的更改 #回到存檔點
RELEASE SAVEPOINT：删除 savepoint 辨別符 #删除臨時存檔
SET AUTOCOMMIT：為目前連接配接禁用或啟用預設 autocommit 模式 三、 事務的使用原則
# 1. 保持事務短小
# 2. 盡量避免事務中rollback
# 3. 盡量避免savepoint
# 4. 顯式聲明打開事務
# 5. 預設情況下，依賴于悲觀鎖，為吞吐量要求苛刻的事務考慮樂觀鎖
# 6. 鎖的行越少越好，鎖的時間越短越好 四、資料庫讀現象
# 讀現象 --》 在并發場景下，資料不安全的一種展現
# 讀現象 --》 在并發場景下，并發的多個事務操作同一份資料，而産生的一些奇怪的獨現象
前面講到了事務的隔離性，如果要提升系統的吞吐量，當有多個任務需要處理時，應當讓多個事務同時執行，這就是事務的并發。既然事務存在并發執行，那必然産生同一個資料操作時的沖突問題，來看一下都會出現什麼問題。
在高并發情況下，即多個并發的事務同時操作的一份資料，在沒有加鎖處理的情況下，會引發一些奇怪的讀現象？
# 1.更新丢失
Lost Update，當兩個事務更新同一行資料時，雙方都不知道對方的存在，就有可能覆寫對方的修改。比如兩個人同時編輯一個文檔，最後一個改完的人總會覆寫掉前面那個人的改動。
# 2.髒讀 --》原因 -- 設定安全級别低導緻的
Dirty Reads，一個事務在執行時修改了某條資料，另一個事務正好也讀取了這條資料，并基于這條資料做了其他操作，因為前一個事務還沒送出，如果基于修改後的資料進一步處理，就會産生無法挽回的損失。
# 3.不可重複讀
Non-Repeatable Reads，同樣是兩個事務在操作同一資料，如果在事務開始時讀了某資料，這時候另一個事務修改了這條資料，等事務再去讀這條資料的時候發現已經變了，這就是沒辦法重複讀一條資料。
# 4.幻讀
Phantom Read，與上方場景相同，事務一開始按某個查詢條件沒查出任何資料，結果因為另一個事務的影響，再去查時卻查到了資料，這種就像産生幻覺了一樣，被稱作幻讀。
# 1、當設定事務的安全隔離級别低的的時候，相應的安全級别低，處理效率就比較高
# 2、當設定事務的安全隔離級别高的的時候，相應的安全級别高，處理效率就比較低 1、四種隔離級别
首先，更新丢失這種問題應該是由應用層來解決的，因為資料庫沒有辦法控制使用者不去更新某條資料。但是另外三個問題是可以得到解決的，既然有方案解決解決它不就好了，幹嘛還要設定這麼多隔離級别呢？
剛才說了，如果我們要性能好、吞吐量提升，那就不得不付出一些代價，如果要做到完全沒有副作用，那麼就隻需要讓事務排隊執行就好了，一個一個執行絕對不會出現髒讀幻讀的問題，但是這樣會導緻資料庫處理的非常慢。那怎麼辦呢？官方唯一能做的就是給你提供各種級别的處理方式，由你根據具體業務場景選擇，于是就有了隔離級别。
# 1、讀未送出 Read uncommitted
讀未送出其實就是事務沒送出就可以讀，很顯然這種隔離級别會導緻讀到别的還沒送出的資料，一旦基于讀到的資料做了進一步處理，而另一個事務最終復原了操作，那麼資料就會錯亂，而且很難追蹤。總的來說說，讀未送出級别會導緻髒讀。
# 2、讀送出 Read committed
顧名思義就是事務送出後才能讀，假設你拿着銀行卡去消費，付錢之前你看到卡裡有2000元，這個時候你老婆在淘寶購物，趕在你前面完成了支付，這個時候你再支付的時候就提示餘額不足，但是分明你看到卡裡的錢是夠的啊。
這就是兩個事務在執行時，事務A一開始讀取了卡裡有2000元，這個時候事務B把卡裡的錢花完了，事務A最終再确認餘額的時候發現卡裡已經沒有錢了。很顯然，讀送出能解決髒讀問題，但是解決不了不可重複讀。
Sql Server，Oracle的預設隔離級别是Read committed。
# 3、可重複讀 Repeatable read
看名字就看出來了，它的出現就是為了解決不可重複讀問題，事務A一旦開始執行，無論事務B怎麼改資料，事務A永遠讀到的就是它剛開始讀的值。那麼問題就來了，假設事務B把id為1的資料改成了2，事務A并不知道id發生了變化，當事務A新增資料的時候卻發現為2的id已經存在了，這就是幻讀。
MySQL的預設隔離級别就是Repeatable read。
# 4、串行化 serializable
這個就是最無敵的存在了，所有的事務串起來一個個執行，因為沒有并發的場景出現了，什麼幻讀、髒讀、不可重複讀統統都不存在的。但是同樣的，基本并發能力會非常差。最終，到底什麼隔離級别完全要根據自己的業務場景選擇，沒有最好的，隻有最适合的。
事務隔離級别越嚴格，越消耗計算機性能，效率也越低，通常情況下，設定為允許不可重複讀就可以解決大多數的問題了。 
Mysql預設使用的資料隔離級别是REPEATABLE READ ，可重複讀，允許幻讀。
2、事務并發常見問題執行個體示範 — 資料庫讀現象
2.1髒讀
髒讀：比如有兩個事務并行執行操作同一條資料庫記錄，A事務能讀取到B事務未送出的資料。 1）修改自動送出事務開關
# 臨時有效
mysql資料庫事務開關
開啟自動送出事務：set autocommit = 1;
關閉自動送出事務：set autocommit = 0 ;
檢視事務開關：show variables like '%autocommit%'; 2）修改資料庫的事務隔離級别
# 全局的 ---此處示範我們設定全局的
mysql> set global transaction isolation level read uncommitted;
mysql> show variables like '%iso%';
+-----------------------+------------------+
| Variable_name | Value |
+-----------------------+------------------+
| transaction_isolation | READ-UNCOMMITTED |
| tx_isolation | READ-UNCOMMITTED |
# 退出重新進入
# 目前會話
set session transaction isolation level read uncommitted; 3）終端1查詢使用者指定資訊（此處不送出事務）
# 1、關閉自動送出事務
set autocommit = 0;
# 2、查詢指定使用者
select from jiaoyi where id=1;
# 3、修改指定使用者餘額
update jiaoyi set money = 500; 4）終端2查詢使用者資訊
---------- 視窗B ------------------------------------------
# 1、關閉自動送出事務
set autocommit = 0;
# 2、查詢指定使用者
select from jiaoyi where id=1;
+------+--------+-------+
| id | name | money |
+------+--------+-------+
| 1 | qiudao | 500 |
+------+--------+-------+
1 row in set (0.00 sec)
很顯然，事務A修改了資料後還沒有送出事務，此時事務B可以讀取到事務A沒有送出的事務的資料。這就是髒讀，髒讀情況下的資料是不可取的，是以一般沒有資料庫事務級别設定為允許髒讀。
# 總結：髒讀就是指事務A讀取到事務B修改但未送出事務的資料。
(事務B未執行commit的時候，但是事務A卻讀取到了)，硬碟的資料和讀取的資料不一樣 2.2不可重複讀
學習完髒讀後，我們再來看看什麼是不可重複讀。比如事務A在同一事務中多次讀取同一記錄，此時事務B修改了事務A正在讀的資料并且送出了事務，但是事務A讀取到了事務B所送出的資料，導緻兩次讀取資料不一緻。
# （每次讀取的資料不一樣，讀一次資料，資料每次不一樣） 1）修改事務隔離級别
# 全局的 ---此處示範我們設定全局的
set global transaction isolation level read committed;
# 目前會話
set session transaction isolation level read committed; 2）視窗1開啟事務，查詢指定使用者資料
---------- 視窗A ------------------------------------------
# 1、關閉自動送出事務
set autocommit = 0;
# 2、查詢指定使用者
select from jiaoyi where id = 1;
此時視窗2執行事務
# 1、查詢指定使用者
select from jiaoyi where id = 1;
# 2、送出事務
commit; 3）視窗2同時執行事務
視窗1先是開啟事務，查詢指定使用者資訊，然後視窗2開啟事務，查詢資料指定使用者，修改資料，送出事務，然後再回到視窗1，查詢指定使用者資訊；視窗2操作内容如下
# 1、關閉自動送出事務
set autocommit = 0;
# 2、查詢指定使用者
select from jiaoyi where id = 1;
# 3、修改指定使用者餘額
update money set money = 300 where id=1;
# 4、送出事務
commit;
事務A在兩次查詢中，查詢的資料不一樣，這就是不可重複讀。Mysql預設采用的就是不可重複讀的隔離級别，用一句話總結，不可重複讀就是事務A讀取到事務B已送出事務的資料，導緻兩次讀取資料資訊不一緻。 2.3 幻讀
上面我我們學習了一下什麼不可重複讀，在mysql資料庫中，不可重複讀是不被允許的。
# 全局的 ---此處示範我們設定全局的
set global transaction isolation level repeatable read;
# 目前會話
set session transaction isolation level repeatable read; 2）視窗1
# 1、關閉自動送出事務
set autocommit = 0;
select from jiaoyi where id = 5;
# 此時視窗1未查詢到id為5的資料，正準備進行插入時，視窗2插入了一條id為5的資料。
# 2、沒有查詢到結果，進行插入
insert into jiaoyi values (5, 'cm',200);
# 3、再查詢id為5的
select from money where id = 5;
commit; 3）視窗2：此處，事務A中查詢指定id為5的記錄，沒有查詢到結果後，事務B進行插入了一條id為5的記錄并且送出事務；
# 1、關閉自動送出事務
set autocommit = 0;
# 2、插入一條記錄
insert into jiaoyi values (5,'cm',200);
# 3、送出事務
commit;
在上述事務A中，不送出事務的情況下，插入id為5的記錄會一直報錯主鍵沖突，但是再怎麼查詢id為5的記錄都查詢不到；這是因為在MySql的設計中，事務中查詢的是被修改前的日志。即Undo log。 3、修改隔離級别
#檢視隔離級别
mysql> show variables like '%iso%';
+---------------+------------------+
| Variable_name | Value |
+---------------+------------------+
| tx_isolation | READ-UNCOMMITTED |
+---------------+------------------+
1 row in set (0.00 sec)
#配置隔離級别
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
transaction_isolation=read-uncommit
4、解決方案
其實，髒寫、髒讀、不可重複讀、幻讀，都是因為業務系統會多線程并發執行，每個線程可能都會開啟一個事務，每個事務都會執行增删改查操作。然後資料庫會并發執行多個事務，多個事務可能會并發地對緩存頁裡的同一批資料進行增删改查操作，于是這個并發增删改查同一批資料的問題，可能就會導緻我們說的髒寫、髒讀、不可重複讀、幻讀這些問題。
是以這些問題的本質，都是資料庫的多事務并發問題，那麼為了解決多事務并發帶來的髒讀、不可重複讀、幻讀等讀等問題，資料庫才設計了鎖機制、事務隔離機制、MVCC 多版本隔離機制，用一整套機制來解決多事務并發問題，下面我們來分别介紹一下它們 五、事務中的鎖
1.什麼是鎖？鎖的作用
鎖
官方連結： javascript:void(0)
# 什麼是鎖
顧名思義就是鎖定的意思，修改資料時鎖住資料，鎖是一種保障資料的機制，如何保障?
# 為什麼用鎖？
以互斥鎖為例,讓多個并發的任務同一時間隻有一個運作（注意這不是串行），犧牲了效率但換來資料安全
在事務ACID特性過程中，“鎖”和“隔離級别”一起來實作“I”隔離性的作用，并發寫同一份資料的時候安全，保證資料安全
# 鎖的優缺點
優點：保障并發場景下的資料安全
缺點：降低了效率
# 是以我們在使用鎖時盡可能縮小鎖的範圍，即鎖住的資料越少越好，并發能力越高 2.示例
資料 id=1
事務1 set id=2
事務2 set id=3
#1.建立一個表
create table test(id int);
#2.插入資料
insert into test values(1);
#3.開啟兩個視窗開啟事務
begin;
update test set id=2 where id=1;
begin;
update test set id=3 where id=1; 3.鎖的種類分類
# 1、按鎖的粒度劃分，可分為行級鎖、表級鎖、頁級鎖。（mysql支援）
# 2、按鎖級别劃分，可分為共享鎖、排他鎖
# 3、按使用方式劃分，可分為樂觀鎖、悲觀鎖
# 4、按加鎖方式劃分，可分為自動鎖、顯式鎖
# 5、按操作劃分，可分為DML鎖、DDL鎖 4.鎖的粒度分類
1) 表級鎖 （偏向于讀）
create table emp(
id int primary key auto_increment,
name varchar(20) not null,
age int(3) unsigned not null default 20
);
insert into emp(name) values
('egon'),
('alex'),
('wupeiqi'),
('yuanhao'),
('liwenzhou'),
('jingliyang'),
('jinxin'),
('成龍'),
('歪歪'),
('丫丫'),
('丁丁'),
('星星'),
('格格'),
('張野'),
('程咬金'),
('程咬銀'),
('程咬銅'),
('程咬鐵')
;
update emp set age = 18 where id <=3; # 表級鎖 --> 支援引擎：MyISAM、MEMORY、InNoDB
# 特點：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發出鎖沖突的機率最高，并發度最低。
lock table 表名 read(write),表名 read(write)，.....;
//給表加讀鎖或者寫鎖，例如
mysql> lock table emp write;
Query OK, 0 rows affected (0.00 sec)
mysql> show open tables where in_use>= 1;
+----------+----------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+----------+--------+-------------+
| ttt | emp | 1 | 0 |
+----------+----------+--------+-------------+
1 row in set (0.00 sec)
mysql> unlock tables; -- UNLOCK TABLES釋放被目前會話持有的任何鎖
Query OK, 0 rows affected (0.00 sec)
mysql> show open tables where in_use>= 1;
Empty set (0.00 sec)
mysql> 2) 行級鎖
# 行級鎖 -->支援引擎：InnoDB
行級鎖是Mysql中鎖定粒度最細的一種鎖，表示隻針對目前操作的行進行加鎖。行級鎖能大大減少資料庫操作的沖突。其加鎖粒度最小，但加鎖的開銷也最大。行級鎖分為共享鎖 和 排他鎖。
# 特點：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的機率最低，并發度也最高。
行級鎖定分為行共享讀鎖（共享鎖）與行獨占寫鎖（排他鎖）
# 共享鎖（S）：SELECT FROM table_name WHERE ... LOCK IN SHARE MODE
# 排他鎖（X)：SELECT FROM table_name WHERE ... FOR UPDATE
# 排它鎖======== 等于========互斥鎖 3) 頁級鎖
頁級鎖是MySQL中鎖定粒度介于行級鎖和表級鎖中間的一種鎖。表級鎖速度快，但沖突多，行級沖突少，但速度慢。是以取了折衷的頁級，一次鎖定相鄰的一組記錄。BDB支援頁級鎖
# 特點：開銷和加鎖時間界于表鎖和行鎖之間；會出現死鎖；鎖定粒度界于表鎖和行鎖之間，并發度一般。 5.鎖的類别分類
1、鎖的使用
事務一對id=3的行加了互斥鎖之後，其它事務對id=3行不能加任何鎖（寫不行，但是可以讀）
事務一對id=3的行加了共享鎖之後，其它事務對id=3行隻能加共享鎖，或者不加鎖（寫不行，但可以讀） 排他鎖：保證在多事務操作時，資料的一緻性。(在我修改資料時，其他人不得修改) # X 排查鎖
# 特點：
加過排他鎖的資料行在其他事務種是不能修改資料的，也不能通過for update和lock in share mode鎖的方式查詢資料，但可以直接通過select ...from...查詢資料，因為普通select查詢沒有任何鎖機制。
共享鎖：保證在多事務工作期間，資料查詢時不會被阻塞。 # S 共享鎖
# 特點：
如果事務T對資料A加上共享鎖後，則其他事務隻能對A再加共享鎖或不加鎖（在其他事務裡一定不能再加排他鎖，但是在事務T自己裡面是可以加的），反之亦然。 行級鎖分為共享鎖和排他鎖兩種。
與行處理相關的sql有：insert、update、delete、select，這四類sql在操作記錄行時，可以為行加上鎖，但需要知道的是：
# 1、對于insert、update、delete語句，InnoDB會自動給涉及的資料加鎖，而且是排他鎖（X）；
# 2、對于普通的select語句，InnoDB不會加任何鎖，需要我們手動自己加，可以加兩種類型的鎖.
# 共享鎖（S）：SELECT ... LOCK IN SHARE MODE; -- 查出的記錄行都會被鎖住
# 排他鎖（X)：SELECT ... FOR UPDATE; -- 查出的記錄行都會被鎖住
# 驗證insert、update、delete是預設加排他鎖的
# 共享鎖一般用于讀資料
# 互斥鎖一般用于改資料 案例
# 連結 javascript:void(0)
實驗一：事務二擷取了排他鎖，在事務一中驗證上述特例
實驗二：事務二擷取了共享鎖，在其他事務裡也隻能加共享鎖或不加鎖，在事務二中驗證
實驗三：事務二擷取了共享鎖，在其他事務裡也隻能加共享鎖或不加鎖，反之亦然，并驗證在多個事務加了共享鎖後，大家對加鎖的資料行隻能讀不能寫
=================================================================================================
解決髒讀 ---》 加一個排他鎖即可，别人就改不了了
解決不可重複讀 ---》 加一個共享鎖，别人隻能加共享鎖，或者不加速
解決幻讀 ---》 加一個排他鎖和間隙鎖
# 修改的範圍資料 id >3 id<1 别人正好插入了一個id=2，是以就造成id=2就沒有改 6、多版本并發控制（MVCC）
樂觀鎖：多事務操作時，資料可以被同時修改，誰先送出，誰修改成功。
悲觀鎖：多事務操作時，資料隻有一個人可以修改。
# 1）隻阻塞修改類操作（排它鎖），不阻塞查詢類操作（共享鎖）
# 2）樂觀鎖的機制（誰先送出誰為準）
在樂觀鎖與悲觀鎖的選擇上面，主要看下兩者的差別以及适用場景就可以了。
# 在MVCC并發控制中，讀操作可以分成兩類：
快照讀 (snapshot read)與目前讀 (current read)。
# 1、樂觀鎖并未真正加鎖，效率高。一旦鎖的粒度掌握不好，更新失敗的機率就會比較高，容易發生業務失敗。
# 2、悲觀鎖依賴資料庫鎖，效率低。更新失敗的機率比較低。
随着網際網路三高架構（高并發、高性能、高可用）的提出，悲觀鎖已經越來越少的被使用到生産環境中了，尤其是并發量比較大的業務場景。 7、事務的隔離級别
1）四種隔離級别：
1.RC: read committed 允許事務檢視其他事務所進行的已送出更改
2.RU: read uncommitted（獨立送出），未送出讀，允許事務檢視其他事務所進行的未送出更改；
3.RR: repeatable read 可重複讀 InnoDB 的預設級别 #commit送出以後可能看不到資料變化，必須重新連接配接
4.serializable：串行化:，将一個事務的結果與其他事務完全隔離 #如果一個事務沒有送出，查詢也不能查了
# 我改微信頭像時你不能看我的資訊，我看你朋友圈的時候你不能發朋友圈、不能看朋友圈
2）修改隔離級别
#檢視隔離級别
mysql> show variables like '%iso%';
#修改隔離級别為RU
[mysqld]
transaction_isolation=read-uncommit
mysql> use oldboy
mysql> select from stu;
mysql> insert into stu(id,name,sex,money) values(2,'li4','f',123);
#修改隔離級别為RC
[mysqld]
transaction_isolation=read-commit
=======================================================================================
# 1、加鎖
mysql> lock table t1 read ;
# 2、解鎖
mysql> unlock table;
Query OK, 0 rows affected (0.00 sec) 8.髒讀，幻讀，不可重複讀
1.髒讀：（RU級别會發生）
一個事務執行了，去沒有送出，被其他人讀取到了值，可是事務復原了，那剛被獨到的資料被稱為髒資料
2.幻讀：（RR級别可以解決這個問題） # 可了解為：操作端的目前狀态
事務将資料庫中所有資料都删除的時候，但是事務B就在這個時候新插入了一條記錄，當事務A删除結束後發現還有一條資料，就好像發生了幻覺一樣。這就叫幻讀。
# 說白了，就是目前使用者的目前資料還沒重新整理到最新，或者重新整理了一部分，且有殘留沒重新整理完全，導緻資料的不一緻
3.不可重複讀： # 被操作端的目前狀态
執行一個事務讀取兩次資料，在第一次讀取某一條資料後，有一個事務修改了讀到的資料并送出，第一個事務再次讀取該資料，兩次讀取便得到了不同的結果。
# 說白了，就是目前使用者的目前資料還沒重新整理到最新，導緻資料的不一緻
六、事務的日志
1.redo log
redo，顧名思義“重做日志”，是事務日志的一種。 # 1）作用
在事務ACID過程中，實作的是“ D ”持久化的作用。
REDO：記錄的是，記憶體資料頁的變化過程
特性:WAL(Write Ahead Log)日志優先寫
# 2）REDO工作過程
執行步驟：
update t1 set num=2 where num=1;
1）首先将t1表中num=1的行所在資料頁加載到記憶體中buffer page
2）MySQL執行個體在記憶體中将num=1的資料頁改成num=2
3）num=1變成num=2的變化過程會記錄到，redo記憶體區域，也就是redo buffer page中
送出事務執行步驟：
commit;
1）當敲下commit指令的瞬間，MySQL會将redo buffer page寫入磁盤區域redo log
2）當寫入成功之後，commit傳回ok 2.undo log
1）作用
undo，顧名思義“復原日志”，是事務日志的一種。
在事務ACID過程中，實作的是“A”原子性的作用。當然CI的特性也和undo有關
# PS：
undo buffer cache 會實時的将資料寫入磁盤，也就是 是否執行了 commit
事務id：txid
日志版本号：lsn 實體備份時可以看到 七、 innodb存儲引擎的鎖機制
innodb存儲引擎的鎖機制鎖定的是索引樹，因為所有的資料行都被索引樹組織起來了
# 連結 javascript:void(0) # 1、命中索引則通過索引鎖定行，簡稱為鎖索引
命中中的是聚集索引，就通過聚集索引本身來鎖定行即可
id為主鍵
select * from t1 where id = 3;
命中中的是輔助索引，會先鎖定輔助索引，然後再對應聚集索引中的節點
name為輔助索引
select * from t1 where name="egon"; -- "egon":10
# 2、如果沒有命中索引，會把所有的行都鎖住，相當于表鎖
強調：命中了索引才會鎖行，不是說有索引就鎖行
表:
1 xxxx
2 tom
3 egon
select * from t1 where id = 3 for update;
select * from t1 where name="egon for update"
強調：條件中引用的索引字段，也不一定會命中 1、行級鎖有三種算法：
InnoDB有三種行鎖的算法，都屬于排他鎖：
# 1、Record Lock：單個行記錄上的鎖。
# 2、Gap Lock：間隙鎖，鎖定一個範圍，但不包括記錄本身。GAP鎖的目的，是為了防止同一事務的兩次目前讀，出現幻讀的情況。
當我們用範圍條件而不是相等條件檢索資料，并請求共享或排他鎖時，InnoDB會給符合條件的已有資料記錄的索引項加鎖；
對于鍵值在條件範圍内但并不存在的記錄，叫做“間隙（GAP)”，InnoDB也會對這個“間隙”加鎖，這種鎖機制就是所謂的間隙鎖（Next-Key鎖）。
# 例如
例：假如employee表中隻有101條記錄，其depid的值分别是 1,2,...,100,101，下面的SQL：
mysql> select * from emp where depid > 100 for update;是一個範圍條件的檢索，并且命中了索引，InnoDB不僅會對符合條件的empid值為101的記錄加鎖，也會對empid大于101（這些記錄并不存在）的“間隙”加鎖。
複制代碼
# 3、Next-Key Lock：等于Record Lock結合Gap Lock，也就說Next-Key Lock既鎖定記錄本身也鎖定一個範圍，特别需要注意的是，InnoDB存儲引擎還會對輔助索引下一個鍵值加上gap lock。
對于行查詢，innodb采用的都是Next-Key Lock，主要目的是解決幻讀的問題，以滿足相關隔離級别以及恢複和複制的需要。
逾時時間的參數：innodb_lock_wait_timeout ，預設是50秒。
逾時是否復原參數：innodb_rollback_on_timeout 預設是OFF。 record Lock：命中索引才鎖行
Gap Lock：鎖間隙
Next-Key Lock：鎖定行以及間隙
如果命中的索引不是唯一索引，那麼采用的算法是Next-Key Lock，即鎖行又鎖定間隙
16 18 20
鎖定
(負無窮,16] (16,18] (18,20] (20,正無窮)
update employee set name=concat(name,"_SB") where age=18;
update employee set name=concat(name,"_SB") where age>3 and age < 19;
1，5，7，11
(負無窮,1] (1,5] (5,7] (7,11] (11,正無窮)
where 字段=7;
如果命中的是唯一索引
update employee set name=concat(name,"_SB") where id=5;
update employee set name=concat(name,"_SB") where id>3 and age < 10;
- 16] (16,18] (18,20] 2、總結
# 總結：
1、如果沒有命中索引，無論你篩選出哪一行，都會将整張表鎖住
1、如果命中了非唯一索引，并且是等值查詢，會鎖行還有間隙
1、如果命中了非唯一索引，但是是範圍查詢，會鎖行還有間隙
1、如果命中了唯一索引，并且是等值查詢，隻會鎖定行
1、如果命中了唯一索引，并且是範圍查詢，會鎖行還有間隙
create table t1(id int primary key,name varchar(20))engine =innodb;
insert into t1 values
(1,"egon1"),
(3,"egon2"),
(5,"egon3"),
(8,"egon4"),
(11,"egon5"); 3、死鎖
MyISAM中是不會産生死鎖的，因為MyISAM總是一次性獲得所需的全部鎖，要麼全部滿足，要麼全部等待。而在InnoDB中，鎖是逐漸獲得的，就造成了死鎖的可能。 
# 案例一：
第一個非常好了解，也是最常見的死鎖，每個事務執行兩條SQL，分别持有了一把鎖，然後加另一把鎖，産生死鎖。
# 案例二：
隻有多個事務同時運作的情況下才可能出現，但隐蔽性極強，雖然每個Session都隻有一條語句，仍舊會産生死鎖。要分析這個死鎖，首先必須用到本文前面提到的MySQL加鎖的規則。針對Session 1，從name索引出發，讀到的[hdc, 1]，[hdc, 6]均滿足條件，不僅會加name索引上的記錄X鎖，而且會加聚簇索引上的記錄X鎖，加鎖順序為先[1,hdc,100]，後[6,hdc,10]。而Session 2，從pubtime索引出發，[10,6],[100,1]均滿足過濾條件，同樣也會加聚簇索引上的記錄X鎖，加鎖順序為[6,hdc,10]，後[1,hdc,100]。發現沒有，跟Session 1的加鎖順序正好相反，如果兩個Session恰好都持有了第一把鎖，請求加第二把鎖，死鎖就發生了。 1、結論
# 1、關于死鎖問題需要儲備的知識
在MySQL中，行級鎖并不是直接鎖記錄，而是鎖索引。索引分為主鍵索引和非主鍵索引兩種，
如果一條sql語句操作了主鍵索引，MySQL就會鎖定這條主鍵索引；
如果一條語句操作了非主鍵索引，MySQL會先鎖定該非主鍵索引，再鎖定相關的主鍵索引。
在UPDATE、DELETE操作時，MySQL不僅鎖定WHERE條件掃描過的所有索引記錄，而且會鎖定相鄰的鍵值，即所謂的next-key locking。
# 2、死鎖産生的本質原理
死鎖的發生與否，并不在于事務中有多少條SQL語句，死鎖的關鍵在于：兩個(或以上)的Session加鎖的順序不一緻。
而使用本文上面提到的，分析MySQL每條SQL語句的加鎖規則，分析出每條語句的加鎖順序，然後檢查多個并發SQL間是否存在以相反的順序加鎖的情況，
就可以分析出各種潛在的死鎖情況，也可以分析出線上死鎖發生的原因 發生死鎖後，InnoDB一般都可以檢測到，并使一個事務釋放鎖回退，另一個擷取鎖完成事務。
死鎖現象2：高并發場景下，核心原理是：命中了輔助索引，會先鎖定輔助索引，再鎖定聚集索引
2、有多種方法可以避免死鎖
# 1、如果不同程式會并發存取多個表，盡量約定以相同的順序通路表，可以大大降低死鎖機會。
# 2、在同一個事務中，盡可能做到一次鎖定所需要的所有資源，減少死鎖産生機率；
# 3、對于非常容易産生死鎖的業務部分，可以嘗試使用更新鎖定顆粒度，通過表級鎖定來減少死鎖産生的機率；
# 4、在程式以批量方式處理資料的時候，如果事先對資料排序，保證每個線程按固定的順序來處理記錄，也可以大大降低出現死鎖的可能。 4 什麼時候使用表鎖（了解）
1、事務需要更新大部分資料，表又較大
若使用預設的行鎖,不僅該事務執行效率低(因為需要對較多行加鎖,加鎖是需要耗時的); 而且可能造成其他事務長時間鎖等待和鎖沖突; 這種情況下可以考慮使用表鎖來提高該事務的執行速度
2、事務涉及多個表，較複雜，很可能引起死鎖，造成大量事務復原
這種情況也可以考慮一次性鎖定事務涉及的表，進而避免死鎖、減少資料庫因事務復原帶來的開銷當然，應用中這兩種事務不能太多，否則，就應該考慮使用ＭyISAＭ。 5 行鎖優化建議
show status like 'innodb_row_lock%';//檢視行鎖的狀态
# 1、盡可能讓所有資料檢索都通過索引來完成， 進而避免無索引行鎖更新為表鎖
# 2、合理設計索引，盡量縮小鎖的範圍
# 3、盡可能減少檢索條件，避免間隙鎖
# 4、盡量控制事務大小，減少鎖定資源量和時間長度
# 5、盡可能低級别事務隔離 八、資料庫總結
資料庫索引
# 1、索引是什麼？
索引一種資料資料結構，既結構資料，大白話說：索引就是一種組織資料的方式
# 那麼索引到底如何組織資料的呢？
為表中的一條條記錄建立建立索引就跟為書的一頁頁内容建立目錄很類似，但不太一樣
不一樣的是，建立索引分為兩步：
1、以索引字段為key與資料對應，例如
create index idx_id on t1(id); -- key為id
建立索引
key
記錄1 <- 1
記錄2 <- 2
記錄3 <- 3
記錄4 <- 4
記錄5 <- 5
。。。
# 2、以key為基礎建構B+樹，那麼什麼是B+樹？？？
介紹下列樹的原理結構以及特點-》講明白下列樹是如何提速查詢的，以及每種樹的問題和演變更新過程
二叉樹
平衡二叉樹
B樹
B+樹
# 特點總結：
1、節點内放的key+value：二叉樹、平衡二叉樹、B樹
在葉子節點放value，其餘節點隻放key值：B+樹
2、放等量資料的前提下，B+樹的高度最低，查詢速度最高
# 2、索引分類及差別
hash索引
b+樹
# 3、 B+樹索引分類
聚簇索引：建構原理，隻能有一個
典型特點：葉子節點放key：一整條完整記錄
輔助索引：建構原理，可以有多個
典型特點：葉子節點放的是key:該記錄對應的主鍵id
# 4、覆寫索引與回表操作
命中輔助索引是否一定需要回表，如果不，解釋原因
# 5、聯合索引，索引的最左字首比對原則-》舉例說明
# 6、索引下推技術
# 7、舉例說明，命中索引之後是否一定對加速有明顯的提升------》等同于回答了如何正确使用索引!!!
範圍過大
索引字段占用空間過多
索引字段的區分度低
索引字段參與運算
索引字段放入函數
explain指令簡介
# 8、常識
單表300w條記錄-》硬碟空間200m
uv：user view單日累計使用者通路
2-5w
pv：page view單日累計頁面被點選的次數
20w-50w
最大并發（同時線上人數最大多少）：一天内，某一時刻的并發量
1000人同時線上
資料庫（讀多寫少）：
累計2w的UV，平均每人往資料庫中寫入一條資料，
那麼單日新增資料條數為2w條
2w條資料庫-》占用空間大概2M
# 結論：以2-5w uv為例，單日資料庫空間增長量從幾M到幾十M不等
# 資料庫事務
舉例說明事務特性ACID
事務開啟的三種方式
事務可以設定儲存點
# 資料庫讀現象
髒讀
不可重複
幻讀
# 資料庫鎖
粒度：行級< 表級 < 頁級
級别越高并發越低，考慮到性能，innodb預設支援行級鎖，但是隻有在命中索引的情況下才鎖行，否則鎖住所有行，本質還是行鎖，
但是此刻相當于鎖表了
行級鎖有三種算法：
Record lock
Gap lock
Next-key lock（預設）= Record lock+ Gap lock ------》 解決幻讀問題
級别：排他、共享
排他
共享
使用方式：悲觀、樂觀
# 事務隔離機制
解決：
髒讀
不可重複
幻讀
方案：
RR機制+innodb存儲引擎的Next-key lock行級鎖算法
# MVCC：解釋原理
快照讀
select 。。。
目前讀
其他都是