【資料庫】1、MySQL、事務、MVCC、LBCC

資料庫相關文章：

• 資料庫①：基礎、事務、鎖：https://blog.csdn.net/hancoder/article/details/105773038
• 資料庫②：索引、調優、explain（尚矽谷筆記）https://blog.csdn.net/hancoder/article/details/105773095
• 資料庫③：存儲結構、頁、聚簇索引 https://blog.csdn.net/hancoder/article/details/105773153

三範式

第一範式（1NF）：指表的列不可再分，資料庫中表的每一列都是不可分割的基本資料項，同一列中不能有多個值；

第二範式（2NF）：在 1NF 的基礎上，還包含兩部分的内容：一是表必須有一個主鍵；二是表中非主鍵列必須完全依賴于主鍵，不能隻依賴于主鍵的一部分；

例如：選課關系(學号,課程名稱,成績,學分),組合關鍵字(學号,課程名稱)作為主鍵，其不滿足2NF，
           

第三範式（3NF）：在 2NF 的基礎上，消除非主鍵列對主鍵的傳遞依賴，非主鍵列必須直接依賴于主鍵。

例如：學生表(學号,姓名,學院編号,學院名稱),學号是主鍵,姓名、學院編号、學院名稱都完全依賴于學号,
         滿足2NF,但不滿足3NF,因為學院名稱直接依賴的是學院編号 ,它是通過傳遞才依賴于主鍵.
           

BC範式（BCNF）：在 3NF 的基礎上，消除主屬性對于碼部分的傳遞依賴

限制：

• NOT NULL：非空，用于保證該字段的值不能為空。

  • -- 此時表示将第一個字段的值設為null, 取決于該字段是否允許為null
    insert into tab values (null, 'val'); 
               

• DEFAULT：預設值，用于保證該字段有預設值

  • -- 建立：
    create table tab ( add_time timestamp default current_timestamp );
    -- 表示将目前時間的時間戳設為預設值。選項：current_date, current_time
    
    -- 此時表示強制使用預設值。
    insert into tab values (default, 'val');    
               

• PRIMARY KEY：主鍵，用于保證該字段的值具有【唯一性】，一個表中有一個主鍵

  • -- 聲明方式：1 跟在聲明的列後 2 在列的最後`
    create table tab (
        id int,
        stu varchar(10),
        primary key (id));
    -- 主鍵字段非空
    -- 主鍵可以由多個字段組成`
    create table tab ( id int, stu varchar(10), age int, 
                      primary key (stu, age));
               

• UNIQUE：唯一，用于保證該字段的值具有唯一性，可以為空（對比主鍵）
• AUTO_INCREMENT： 自增限制。自動增長必須為索引（主鍵或unique）
  • 預設為1開始自動增長。可以通過表屬性 auto_increment = x進行設定，或 alter table tbl auto_increment = x;
• CHECK：檢查限制【mysql中不支援】
• FOREIGN KEY：外鍵，用于限制主從表的關系，用于保證該字段的值必須來自于主表的關聯列的值
  • 在從表添加外鍵限制，用于【引用主表】中某列的值
  • 比如學生表的專業編号，員工表的部門編号，員工表的工種編号
• COMMENT：注釋。

  • create table tab ( id int  comment '注釋内容' );
               

7. FOREIGN KEY 外鍵限制
    用于限制主表與從表資料完整性。
    alter table t1 add constraint `t1_t2_fk` foreign key (t1_id) references t2(id);
        -- 将表t1的t1_id外鍵關聯到表t2的id字段。
        -- 每個外鍵都有一個名字，可以通過 constraint 指定
    存在外鍵的表，稱之為從表（子表），外鍵指向的表，稱之為主表（父表）。
    作用：保持資料一緻性，完整性，主要目的是控制存儲在外鍵表（從表）中的資料。
    MySQL中，可以對InnoDB引擎使用外鍵限制：
    文法：
    foreign key (外鍵字段） references 主表名 (關聯字段) [主表記錄删除時的動作] [主表記錄更新時的動作]
    此時需要檢測一個從表的外鍵需要限制為主表的已存在的值。外鍵在沒有關聯的情況下，可以設定為null.前提是該外鍵列，沒有not null。
    可以不指定主表記錄更改或更新時的動作，那麼此時主表的操作被拒絕。
    如果指定了 on update 或 on delete：在删除或更新時，有如下幾個操作可以選擇：
    1. cascade，級聯操作。主表資料被更新（主鍵值更新），從表也被更新（外鍵值更新）。主表記錄被删除，從表相關記錄也被删除。
    2. set null，設定為null。主表資料被更新（主鍵值更新），從表的外鍵被設定為null。主表記錄被删除，從表相關記錄外鍵被設定成null。但注意，要求該外鍵列，沒有not null屬性限制。
    3. restrict，拒絕父表删除和更新。
    注意，外鍵隻被InnoDB存儲引擎所支援。其他引擎是不支援的。
           

# 外鍵例子---列級限制例子------
CREATE TABLE stuiInfo(
	id INT PRIMARY KEY,#主鍵
	stuName VARCHAR(20) NOT NULL UNIQUE,#非空
	gender CHAR(1) CHECK(gender='男' OR gender ='女'),#檢查
	seat INT UNIQUE,#唯一
	age INT DEFAULT  18,#預設限制
	majorId INT REFERENCES major(id)#外鍵-！！！
);

CREATE TABLE major(
	id INT PRIMARY KEY,
	majorName VARCHAR(20)
);

#檢視stuinfo中的所有索引，包括主鍵、外鍵、唯一
SHOW INDEX FROM stuinfo;
           

• 列級限制：六大限制文法上都支援，但外鍵限制沒有效果（外鍵限制寫了也白寫）
• 表級限制：除了非空、預設，其他的都支援

列級限制的例子即上個程式

表級限制文法：在各個字段的最下面
 【constraint 限制名】 限制類型(字段名)
 
# 表級限制例子--------
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT,
	
	CONSTRAINT pk PRIMARY KEY(id),#主鍵
	CONSTRAINT uq UNIQUE(seat),#唯一鍵
	CONSTRAINT ck CHECK(gender ='男' OR gender  = '女'),#檢查
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外鍵
);

SHOW INDEX FROM stuinfo;


#通用的寫法：★------------------

CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuname VARCHAR(20),
	sex CHAR(1),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorid INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);
           

主鍵和唯一的大對比：

保證唯一性	是否允許為空	一個表中可以有多少個	是否允許組合
主鍵	√	×	至多有1個	√，但不推薦
唯一	√	√	可以有多個	√，但不推薦

外鍵的特點：

1. 要求在【從表】設定外鍵關系
2. 從表的外鍵列的類型和主表的關聯列的類型要求一緻或相容，名稱無要求
3. 【主表的關聯列必須是一個key】（一般是主鍵或唯一）
4. 插入資料時，先插入主表，再插入從表
   1. 删除資料時，先删除從表，再删除主表

上面的例子是建立表時添加限制，下面還有修改表時添加限制。

添加限制

在執行CREATE TABLE語句時可以建立索引，也可以單獨用`CREATE INDEX`或`ALTER TABLE`來為表增加索引。

​    1、ALTER TABLE
ALTER TABLE用來建立普通索引、UNIQUE索引或PRIMARY KEY索引。
ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)
說明：其中table_name是要增加索引的表名，column_list指出對哪些列進行索引，多列時各列之間用逗号分隔。索引名index_name可選，預設時，MySQL将根據第一個索引列賦一個名稱。另外，ALTER TABLE允許在單個語句中更改多個表，是以可以在同時建立多個索引。

​    2、CREATE INDEX

CREATE INDEX可對表增加普通索引或UNIQUE索引。
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
​    說明：table_name、index_name和column_list具有與ALTER TABLE語句中相同的含義，索引名不可選。另外，不能用CREATE INDEX語句建立PRIMARY KEY索引。

/*
1、添加列級限制
alter table 表名 modify column 字段名 字段類型 新限制;

2、添加表級限制
alter table 表名 add 【constraint 限制名】 限制類型(字段名) 【外鍵的引用】;
*/
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT
)
DESC stuinfo;
#1.添加非空限制
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20)  NOT NULL;
#2.添加預設限制
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#3.添加主鍵
#①列級限制
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#②表級限制
ALTER TABLE stuinfo ADD PRIMARY KEY(id);

#4.添加唯一
#①列級限制
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#②表級限制
ALTER TABLE stuinfo ADD UNIQUE(seat);

#5.添加外鍵
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id); 
           

删除限制

DROP INDEX index_name ON talbe_name

ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE table_name DROP PRIMARY KEY
           

1.删除非空限制

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
           

2.删除預設限制

ALTER TABLE stuinfo MODIFY COLUMN age INT ;
           

3.删除主鍵

ALTER TABLE stuinfo DROP PRIMARY KEY;
           

4.删除唯一

ALTER TABLE stuinfo DROP INDEX seat;
           

5.删除外鍵

ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
           

SHOW INDEX FROM stuinfo;

辨別列：

又稱自增長列

含義：可以不用手動地插入值，系統提供預設的序列值。

AUTO_INCREMENT

SET AUTO_INCREMENT=2; # 重新設定步長。
需要更改起始值的時候，手動添加一個值即可。
其餘時候填入null即可
# 修改表時設定辨別列
alter table stu modify column id int primary key auto_increment;
# 修改表時删除辨別列
alter table stu modify column id int primary;
           

問：辨別列必須跟主鍵搭配嗎？

答：不一定，但要求是一個key。

問：一個表中可以有多少辨別列。

答：一個表中隻能有一個辨別列。

問：辨別列的類型？

答：必須是數值型

DQL語言

查哪些列 查哪張表 查哪些行

DQL（Data Query Language）：資料查詢語言

select

順序問題

寫的順序：

SELECT [ALL|DISTINCT] 要查詢的字段|表達式select_expr|常量值|函數 
FROM ->
WHERE -> 
GROUP BY [合計函數] -> 
HAVING -> 
ORDER BY -> 
LIMIT
順序的記法：LIMIT最後，HAVING在兩個by中間
           

執行順序

FROM
ON JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
UNION
ORDER BY
           

FROM

用于辨別查詢來源。
    -- 可以為表起别名。使用as關鍵字。
        SELECT * FROM tb1 AS tt, tb2 AS bb;
    -- from子句後，可以同時出現多個表。
        -- 多個表會橫向疊加到一起，而資料會形成一個笛卡爾積。
        SELECT * FROM tb1, tb2;
    -- 向優化符提示如何選擇索引
        USE INDEX、IGNORE INDEX、FORCE INDEX
        SELECT * FROM table1 USE INDEX (key1,key2) WHERE key1=1 AND key2=2 AND key3=3;
        SELECT * FROM table1 IGNORE INDEX (key3) WHERE key1=1 AND key2=2 AND key3=3;
           

c. WHERE 子句
    -- 從from獲得的資料源中進行篩選。
    -- 整型1表示真，0表示假。
    -- 表達式由運算符和運算數組成。
        -- 運算數：變量（字段）、值、函數傳回值
        -- 運算符：
            =, <=>, <>, !=, <=, <, >=, >, !, &&, ||,
            in (not) null, (not) like, (not) in, (not) between and, is (not), and, or, not, xor
            is/is not 加上ture/false/unknown，檢驗某個值的真假
            <=>與<>功能相同，<=>可用于null比較
            
d. GROUP BY 子句, 分組子句
    GROUP BY 字段/别名 [排序方式]
    分組後會進行排序。升序：ASC，降序：DESC
    
    以下[合計函數]需配合 GROUP BY 使用：
    count 傳回不同的非NULL值數目  count(*)、count(字段)
    sum 求和
    max 求最大值
    min 求最小值
    avg 求平均值
    group_concat 傳回帶有來自一個組的連接配接的非NULL值的字元串結果。組内字元串連接配接。
    
e. HAVING 子句，條件子句
    與 where 功能、用法相同，執行時機不同。
    where 在開始時執行檢測資料，對原資料進行過濾。
    having 對篩選出的結果再次進行過濾。
    having 字段必須是查詢出來的，where 字段必須是資料表存在的。【重要】
    where 不可以使用字段的别名，having 可以。因為執行WHERE代碼時，可能尚未确定列值。【重要】
    where 不可以使用合計函數。一般需用合計函數才會用 having
    SQL标準要求HAVING必須引用GROUP BY子句中的列或用于合計函數中的列。
    
f. ORDER BY 子句，排序子句
    order by 排序字段/别名 排序方式 [,排序字段/别名 排序方式]...
    升序：ASC，降序：DESC
    支援多個字段的排序。
    
g. LIMIT 子句，限制結果數量子句
    僅對處理好的結果進行數量限制。将處理好的結果的看作是一個集合，按照記錄出現的順序，索引從0開始。
    limit 起始位置, 擷取條數
    省略第一個參數，表示從索引0開始。limit 擷取條數
    
h. DISTINCT, ALL 選項
    distinct 去除重複記錄
    預設為 all, 全部記錄
           

select

①通過select查詢完的結果 ，是一個虛拟的表格，不是真實存在

② 要查詢的東西 可以是常量值、可以是表達式、可以是字段、可以是函數

數值型和日期型的常量值必須用單引号引起來，數值型不需要

-- 可以用 * 表示所有字段。
        select * from tb;
-- 可以使用表達式（計算公式、函數調用、字段也是個表達式）
        select stu, 29+25, now() from tb;
-- 可以為每個列使用别名。适用于簡化列辨別，避免多個列辨別符重複。
        - 使用 as 關鍵字，也可省略 as.
        select stu+10 as add10 from tb;
           

函數

#一、字元函數

#1.l lenth 擷取參數值的位元組個數
SELECT LENGTH('john');
SELECT LENGTH('張三豐hahaha');

SHOW VARIABLES LIKE '%char%'

#2.concat 拼接字元串
SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;

#3.upper、lower
SELECT UPPER('john');
SELECT LOWER('joHn');
#示例：将姓變大寫，名變小寫，然後拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name))  姓名 FROM employees;

#4.substr、substring 注意：索引從1開始
#截取從指定索引處後面所有字元
SELECT SUBSTR('李莫愁愛上了陸展元',7)  out_put;

#截取從指定索引處指定字元長度的字元
SELECT SUBSTR('李莫愁愛上了陸展元',1,3) out_put;

#案例：姓名中首字元大寫，其他字元小寫然後用_拼接，顯示出來
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2)))  out_put
FROM employees;

#5.instr 傳回子串第一次出現的索引，如果找不到傳回0
SELECT INSTR('楊不殷六俠悔愛上了殷六俠','殷八䩠 'AS out_put;

#6.trim去前後空格
SELECT LENGTH(TRIM('    張翠山    ')) AS out_put;
SELECT TRIM('aa' FROM 'aaaa張aaa翠山aaaaa')  AS out_put;

#7.lpad 用指定的字元實作左填充指定長度
SELECT LPAD('殷素素',2,'*') AS out_put;

#8.rpad 用指定的字元實作右填充指定長度
SELECT RPAD('殷素素',12,'ab') AS out_put;


#9.replace 替換
SELECT REPLACE('周芷若周芷若周芷若周芷若張無忌愛上了周芷若','周芷若','趙敏') AS out_put;
             
#--------------------------------------------------------------
#二、數學函數

#round 四舍五入
SELECT ROUND(-1.55);
SELECT ROUND(1.567,2);


#ceil 向上取整,傳回>=該參數的最小整數
SELECT CEIL(-1.02);

#floor 向下取整，傳回<=該參數的最大整數
SELECT FLOOR(-9.99);

#truncate 截斷
SELECT TRUNCATE(1.69999,1);

#mod取餘
/*
mod(a,b) ：  a-a/b*b

mod(-10,-3):-10- (-10)/(-3)*（-3）=-1
*/
SELECT MOD(10,-3);
SELECT 10%3;


#三、日期函數

#now 傳回目前系統旟+時間
SELECT NOW();

#curdate 傳回目前系統日期，不包含時間
SELECT CURDATE();

#curtime 傳回目前時間，不包含日期
SELECT CURTIME();


#可以擷取指定的部分，年、月、日、小時、分鐘、秒
SELECT YEAR(NOW()) 年;
SELECT YEAR('1998-1-1') 年;

SELECT  YEAR(hiredate) 年 FROM employees;

SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;

#str_to_date 将字元通過指定的格式轉換成日期,中間的符号要一緻
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;

#查詢入職日期為1992--4-3的員工資訊
SELECT * FROM employees WHERE hiredate = '1992-4-3';
或
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');
%Y四位的年份 %y二位的年份 %m二位的月份 %c月份 %d二位的日 %H二十四制小時 %h十二制小時 %i二位制分鐘 %s二位制秒

#date_format 将日期轉換成字元
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') AS out_put;

#查詢有獎金的員工名和入職日期(xx月/xx日 xx年)
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %Y年') 入職日期
FROM employees
WHERE commission_pct IS NOT NULL;


#四、其他函敍
SELECT VERSION();
SELECT DATABASE();
SELECT USER();
           

一、單行函數
1、字元函數
	length長度，中文是3B
	concat()拼接
	substr截取子串（字元串，開始位置從1開始，長度中文也是1個）
	instr 傳回子串第一次出現的索引，如果找不到傳回0
	upper轉換成大寫
	lower轉換成小寫
	trim去前後指定的空格和字元(字元串，去除内容' ')
	ltrim去左邊空格
	rtrim去右邊空格
	replace替換(字元串，被替換内容，用什麼替換)
	lpad左填充(字元串，填充後的長度，填空内容'')
	rpad右填充
	instr傳回子串第一次出現的索引
	length 擷取位元組個數
	
CONCAT('Hello', 'World') HelloWorld
SUBSTR('HelloWorld',1,5) Hello
LENGTH('HelloWorld') 10
INSTR('HelloWorld', 'W') 6
LPAD(salary,10,'*')  *****24000
RPAD(salary, 10, '*')  24000*****
TRIM('H' FROM 'HelloWorld')  elloWorld
REPLACE('abcd','b','m')  amcd


2、數學函數
	round 四舍五入(數，小數點後保留位數=0)
	rand 随機數
	floor向下取整（小于等于）
	ceil向上取整（大于等于）
	mod取餘MOD(10.3)
	truncate截斷
3、日期函數
	now()目前系統日期+時間
	curdate()目前系統日期
	curtime()目前系統時間
	str_to_date(,) 将字元轉換成日期
	date_format(,)将日期轉換成字元
           

where

where分類：
一、條件表達式
	示例：salary>10000
	條件運算符：
	> < >= <= = != <>不等于，注意等于是=

二、邏輯表達式
示例：salary>10000 && salary<20000
邏輯運算符：
	and（&&）:兩個條件如果同時成立，結果為true，否則為false
	or(||)：兩個條件隻要有一個成立，結果為true，否則為false
	not(!)：如果條件成立，則not後為false，否則為true
# SELECT * FROM employees WHERE name LIKE '%a%' OR salary>100;

三、模糊查詢
like
示例：last_name like 'a%'
# SELECT * FROM employees WHERE last_name LIKE '%a%';%代表通配符
通配符：
%任意多個字元
_任一個字元,可以用轉義符

between and
SELECT * FROM `employees` WHERE `employee_id` BETWEEN 100小值 AND 120大值;不可以換順序

in//NOT IN
SELECT * FROM `employees` WHERE job_id IN ['AD_VP','AD_PRES']不可以寫通配符，可以寫()


SELECT `last_name`,`commission_pct` FROM employees WHERE `commission_pct` IS NOT NULL;等号不能用于判斷null值
SELECT `last_name`,`commission_pct` FROM employees WHERE `commission_pct` <=> 120;安全等于。即可以判斷NULL，又可以判斷數值

           

group

分組函數作用于一組資料，并每組資料傳回一個值。

select 分組函數
from 表
where 條件
group by 
order by 排序的字段|表達式|函數|别名 【asc|desc】

分組函數：
• AVG()
• COUNT(expr) -- 計數,傳回expr不為空的記錄總數。,适用于任意資料類型。// select count(*) from customer where id=110;
• MAX()
• MIN()
• SUM()

# 在SELECT 清單中所有未包含在組函數中的列都應該包含在 GROUP BY 子句中。
SELECT ID,AVG(salary)
FROM employees
GROUP BY ID;
#包含在 GROUP BY 子句中的列不必包含在SELECT 清單中
SELECT AVG(salary)
FROM employees
GROUP BY ID;
           

• 不能在 WHERE 子句中使用分組函數。

• 可以在 HAVING 子句中使用分組函數。

分組前篩選： 原始表 group by的前面 where

分組後篩選： 分組後的結果集 group by的後面 having

sum 求和
	max 最大值（可以算字母順序，日期順序）
	min 最小值
	avg 平均值
	count 計數//非空的個數

	特點：
	1、以上五個分組函數都忽略null值，avg也隻計算非null，除了count(*)
	2、sum和avg一般用于處理數值型
		max、min、count可以處理任何資料類型
    3、都可以搭配distinct使用，用于統計去重後的結果
	4、count的參數可以支援：
		字段、*、常量值，一般放1

	   建議使用 count(*)統計行數，括号内任意數字或任意常量也可以統計行數
	   
select avg(score) from stu where class='1';
select avg(score) from stu group by class;

#1.查詢各job_id的員工工資的最大值，最小值，平均值，總和，并按job_id升序
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY job_id;


#2.查詢員工最高工資和最低工資的差距（DIFFERENCE）
SELECT MAX(salary)-MIN(salary) DIFFRENCE
FROM employees;
#3.查詢各個管理者手下員工的最低工資，其中最低工資不能低于6000，沒有管理者的員工不計算在内
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>=6000;

#4.查詢所有部門的編号，員工數量和工資平均值,并按平均工資降序
SELECT department_id,COUNT(*),AVG(salary) a
FROM employees
GROUP BY department_id
ORDER BY a DESC;
#5.選擇具有各個job_id的員勞工數
SELECT COUNT(*) 個數,job_id
FROM employees
GROUP BY job_id;
           

having

使用 HAVING 過濾分組前提：

1. 行已經被分組。
2. 使用了組函數。
3. 滿足HAVING 子句中條件的分組将被顯示。

不可以使用having的情況：篩選條件的列沒有出現在查詢select查詢字段中

順序：from on join where group having select（這裡順序是沒錯的，但我對having和select的關系存疑）

SELECT region, SUM(population), SUM(area)
FROM bbc
GROUP BY region
HAVING SUM(area)>1000 -- 在這裡，我們不能用where來篩選超過1000的地區，因為表中不存在這樣一條記錄。

select sum(score)
from student  
where sex='man'
group by name
having sum(score)>210
           

having和where的差別

• having與 where 功能、用法相同，執行時機不同。
• where 在開始時執行檢測資料，對原資料進行過濾。
• having 對篩選出的結果再次進行過濾。
• having 字段必須是查詢出來的，where 字段必須是資料表存在的。
• where 不可以使用字段的别名，having 可以。因為執行WHERE代碼時，可能尚未确定列值。
• where 不可以使用聚合函數。一般需用聚合函數才會用 having，where執行順序大于聚合函數。
• where 子句的作用是在對查詢結果進行分組前，将不符合where條件的行去掉，即在分組之前過濾資料，條件中不能包含聚組函數，使用where條件顯示特定的行。
• SQL标準要求HAVING必須引用GROUP BY子句中的列或用于合計函數中的列。
• 聚合函數是比較where、having 的關鍵。若須引入聚合函數來對group by 結果進行過濾 則隻能用having。
• having 子句的作用是篩選滿足條件的組，即在分組之後過濾資料，條件中經常包含聚組函數，使用having 條件顯示特定的組，也可以使用多個分組标準進行分組。

執行順序：

where >>> 聚合函數(sum,min,max,avg,count) >>> having
           

order

select 要查詢的東西
from 表
where 條件

order by 排序的字段|表達式|函數|别名 【asc|desc】
/*
- ORDER BY 子句在SELECT語句的【結尾】。 

- 【asc|desc】為升降序
- 多個順序時，寫多個即可，逗号分隔
*/
           

執行順序：from 、join、 on、 where 、

、group by(開始使用select中的别名，後面的語句中都可以使用)

、avg,sum… having、 select 、distinct 、order by、limit

limit

-- 分頁查詢

select 字段|表達式,...
from 表
【where 條件】
【group by 分組字段】
【having 條件】
【order by 排序的字段】
limit 【起始的條目索引,最大條目數】;-- 0開始  -- 第二個參數為-1代表列印後面所有
           

1.起始條目索引從0開始

2.limit子句放在查詢語句的最後

3.公式：select * from  表 limit （page-1）*sizePerPage,sizePerPage
假如:
每頁顯示條目數sizePerPage
要顯示的頁數 page

- select sname from score where degree=(select max(degree) from score);
這句的啟發是：括号裡的select的結果是一個值，這樣degree=值就是一個where語句，缺點是有可能傳回多個等大最大值。即先找到最大值再用where
- 可以用limit實作查取某列第一大的值
select name from stu order by degree limit 0,1; // 從0開始一個值
           

if case when

if 處理雙分支
case語句 處理多分支
	when情況1  then處理等值判斷
    when情況2  then處理條件判斷
    ELSE
    END
           

#五、流程控制函數
#1.if函數： if else 的效果IF(表達式，真的話，假的話)

SELECT IF(10<5,'大','小');
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'沒獎金，呵呵','有獎金，嘻嘻') 備注
FROM employees;


#2.case函數的使用一： switch case 的效果
/*
java中
switch(變量或表達式){
	case 常量1：語句1;break;
	...
	default:語句n;break;
}
mysql中：
case 要判斷的字段或表達式
when 常量1 then 要顯示的值1或語句1;
when 常量2 then 要顯示的值2或語句2;
...
else 要顯示的值n或語句n;
end
*/

/*案例：查詢員工的工資，要求
部門号=30，顯示的工資為1.1倍
部門号=40，顯示的工資為1.2倍
部門号=50，顯示的工資為1.3倍
其他部門，顯示的工資為原工資
*/
SELECT salary 原始工資,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary 
END AS 新工資
FROM employees;

#3.case 函數的使用二：類似于 多重if
/*
mysql中：
case[case後沒有語句直接when]
when 條件1 then 要顯示的值1或語句1
when 條件2 then 要顯示的值2或語句2
。。。
else 要顯示的值n或語句n
end
*/

#案例：查詢員工的工資的情況
如果工資>20000,顯示A級别
如果工資>15000,顯示B級别
如果工資>10000，顯示C級别
否則，顯示D級别


SELECT salary,
CASE 
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工資級别
FROM employees;
           

join

等值連接配接、非等值連接配接 （内連接配接）

外連接配接

交叉連接配接

文法：

select 字段，...
from 表1
【inner|left outer|right outer|cross】join 表2 on  連接配接條件
【inner|left outer|right outer|cross】join 表3 on  連接配接條件
【where 篩選條件】
【group by 分組字段】
【having 分組後的篩選條件】
【order by 排序的字段或表達式】

/* 連接配接查詢(join) */ ------------------
    将多個表的字段進行連接配接，可以指定連接配接條件。
-- 内連接配接(inner join)
    - 預設就是内連接配接，可省略inner。
    - 隻有資料存在時才能發送連接配接。即連接配接結果不能出現空行。
    on 表示連接配接條件。其條件表達式與where類似。也可以省略條件（表示條件永遠為真）
    也可用where表示連接配接條件。
    還有 using, 但需字段名相同。 using(字段名)
    -- 交叉連接配接 cross join
        即，沒有條件的内連接配接。
        select * from tb1 cross join tb2;
-- 外連接配接(outer join)
    - 如果資料不存在，也會出現在連接配接結果中。
    -- 左外連接配接 left join
        如果資料不存在，左表記錄會出現，而右表為null填充
    -- 右外連接配接 right join
        如果資料不存在，右表記錄會出現，而左表為null填充
-- 自然連接配接(natural join)
    自動判斷連接配接條件完成連接配接。
    相當于省略了using，會自動查找相同字段名。
    natural join
    natural left join
    natural right join
select info.id, info.name, info.stu_num, extra_info.hobby, extra_info.sex from info, extra_info where info.stu_num = extra_info.stu_id;
           

在使用

left join

時，on和where條件的差別如下：

1、on條件是在生成臨時表時使用的條件，它不管on中的條件是否為真，都會傳回左邊表中的記錄。

2、where條件是在臨時表生成好後，再對臨時表進行過濾的條件。這時已經沒有left join的含義（必須傳回左邊表的記錄）了，條件不為真的就全部過濾掉

left join的工作機制不在于on條件有幾個，也不在于on條件關聯的是哪個字段。而是重點在于【如果對于左面的一行沒有一個on條件滿足的話就自動填充一行null】

在工作中還遇到過這麼一個需求：查出來null要有預設值。你可以使用

IFNULL(字段,預設值)

或

coalesce(判斷式1,判斷式2...)

他倆的工作機制是不一樣的，但是可以達到一樣的效果

先進行on過濾，後進行where過濾，記住前面的順序是from on join where

假設有兩張表：

表1：tab2

id	size
1	10
2	20
3	30

表2：tab2

size	name
10	AAA
20	BBB
20	CCC

兩條SQL:

1、select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’
2、select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’)
           

1、第一條SQL的過程：select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’

1.1、中間表

on條件: tab1.size = tab2.size

ab1.id	tab1.size	tab2.size	tab2.name
1	10	10	AAA
2	20	20	BBB
2	20	20	CCC
3	30	(null)	(null)

1.2、再對中間表過濾

where 條件：tab2.name=’AAA’

tab1.id	tab1.size	tab2.size	tab2.name
1	10	10	AAA

2、第二條SQL的過程：select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’)

tab1.id	tab1.size	tab2.size	tab2.name
1	10	10	AAA
2	20	(null)	(null)
3	30	(null)	(null)

其實以上結果的關鍵原因就是left join,right join,full join的特殊性，不管on上的條件是否為真都會傳回left或right表中的記錄，full則具有left和right的特性的并集。 而inner jion沒這個特殊性，則條件放在on中和where中，傳回的結果集是相同的。

子查詢

/* 子查詢 */
    - 子查詢需用括号包裹。
-- from型
    from後要求是一個表，必須給子查詢結果取個别名。
    - 簡化每個查詢内的條件。
    - from型需将結果生成一個臨時表格，可用以原表的鎖定的釋放。
    - 子查詢傳回一個表，表型子查詢。
    select * from (select * from tb where id>0) as subfrom where id>1;
-- where型
    - 子查詢傳回一個值，标量子查詢。
    - 不需要給子查詢取别名。
    - where子查詢内的表，不能直接用以更新。
    select * from tb where money = (select max(money) from tb);
    -- 列子查詢
        如果子查詢結果傳回的是一列。
        使用 in 或 not in 完成查詢
        exists 和 not exists 條件
            如果子查詢傳回資料，則傳回1或0。常用于判斷條件。
            select column1 from t1 where exists (select * from t2);
    -- 行子查詢
        查詢條件是一個行。
        select * from t1 where (id, gender) in (select id, gender from t2);
        行構造符：(col1, col2, ...) 或 ROW(col1, col2, ...)
        行構造符通常用于與對能傳回兩個或兩個以上列的子查詢進行比較。
    -- 特殊運算符
    != all()    相當于 not in
    = some()    相當于 in。any 是 some 的别名
    != some()   不等同于 not in，不等于其中某一個。
    all, some 可以配合其他運算符一起使用。
           

子查詢：内層被嵌套的select

• 子查詢都放在小括号内
• 子查詢可以放在from後面、select後面、where後面、having後面，但一般放在條件的右側
• 查詢結果：
  • 結果集隻有一行：一般搭配單行操作符使用：> < = <> >= <=
  • 結果集有多行：一般搭配多行操作符使用：any、all、in、not in

主查詢：外層的select

union

1、多條查詢語句的查詢的列數必須是一緻的

2、多條查詢語句的查詢的列的類型幾乎相同

3、union代表去重，union all代表不去重

select 字段|常量|表達式|函數 【from 表】 【where 條件】 union 【all】

select 字段|常量|表達式|函數 【from 表】 【where 條件】 union 【all】
select 字段|常量|表達式|函數 【from 表】 【where 條件】 union  【all】
.....
select 字段|常量|表達式|函數 【from 表】 【where 條件】


/* UNION */ ------------------
    将多個select查詢的結果組合成一個結果集合。
    SELECT ... UNION [ALL|DISTINCT] SELECT ...
    預設 DISTINCT 方式，即所有傳回的行都是唯一的
    建議，對每個SELECT查詢加上小括号包裹。
    ORDER BY 排序時，需加上 LIMIT 進行結合。
    需要各select查詢的字段數量一樣。
    每個select查詢的字段清單(數量、類型)應一緻，因為結果中的字段名以第一條select語句為準。
           

TRUNCATE

/* TRUNCATE */ ------------------
TRUNCATE [TABLE] tbl_name
清空資料
删除重建表
差別：
1，truncate 是删除表再建立，delete 是逐條删除
2，truncate 重置auto_increment的值。而delete不會
3，truncate 不知道删除了幾條，而delete知道。
4，當被用于帶分區的表時，truncate 會保留分區
           

MyISAM和InnoDB差別

MyISAM是MySQL的預設資料庫引擎（5.5版之前）。雖然性能極佳，而且提供了大量的特性，包括全文索引、壓縮、空間函數等，但MyISAM不支援事務和行級鎖，而且最大的缺陷就是崩潰後無法安全恢複。不過，5.5版本之後，MySQL引入了InnoDB（事務性資料庫引擎），MySQL 5.5版本後預設的存儲引擎為InnoDB。

大多數時候我們使用的都是 InnoDB 存儲引擎，但是在某些情況下使用 MyISAM 也是合适的比如讀密集的情況下。（如果你不介意 MyISAM 崩潰恢複問題的話）。

兩者的對比：

1. 是否支援行級鎖 : MyISAM 隻有表級鎖，而InnoDB 支援行級鎖和表級鎖，預設為行級鎖。
2. 是否支援事務和崩潰後的安全恢複： MyISAM 強調的是性能，每次查詢具有原子性,其執行速度比InnoDB類型更快，但是不提供事務支援。但是InnoDB 提供事務支援事務，外部鍵等進階資料庫功能。 具有事務(commit)、復原(rollback)和崩潰修複能力(crash recovery capabilities)的事務安全(transaction-safe (ACID compliant))型表。
3. 是否支援外鍵： MyISAM不支援，而InnoDB支援。
4. 是否支援MVCC ：僅 InnoDB 支援。應對高并發事務, MVCC比單純的加鎖更高效；MVCC隻在

   讀已送出READ COMMITTED

   和

   可重複讀REPEATABLE READ

   兩個隔離級别下工作；MVCC可以使用 樂觀(optimistic)鎖 和 悲觀(pessimistic)鎖來實作；各資料庫中MVCC實作并不統一。推薦閱讀：MySQL-InnoDB-MVCC多版本并發控制

《MySQL高性能》：不要輕易相信“MyISAM比InnoDB快”之類的經驗之談，這個結論往往不是絕對的。在很多我們已知場景中，InnoDB的速度都可以讓MyISAM望塵莫及，尤其是用到了聚簇索引，或者需要通路的資料都可以放入記憶體的應用。

一般情況下我們選擇 InnoDB 都是沒有問題的，但是某些情況下你并不在乎可擴充能力和并發能力，也不需要事務支援，也不在乎崩潰後的安全恢複問題的話，選擇MyISAM也是一個不錯的選擇。但是一般情況下，我們都是需要考慮到這些問題的。

事務

Transaction Control Language 事務控制語言

事務是指邏輯上的一組操作，組成這組操作的各個單元，要不全成功要不全失敗。

• 支援連續SQL的集體成功或集體撤銷。
• 事務是資料庫在資料完整性方面的一個功能。
• 需要利用 InnoDB 或 BDB 存儲引擎，對自動送出的特性支援完成。
• InnoDB被稱為事務安全型引擎。

1 ACID

• 原子性Atomicity：要麼都執行，要麼都復原。（事務不可分割）
• 一緻性Consistency：保證資料的狀态操作前和操作後保持一緻。資料庫總是從一個一緻性的狀态轉換到另一個一緻性的狀态。事務前後資料的完整性必須保持一緻。
  • 事務前後資料的完整性必須保持一緻。
  • 在整個事務過程中，操作是連續的
• 隔離性Isolation：多個事務同時操作相同資料庫的同一個資料時，一個事務的執行不受另外一個事務的幹擾。“通常來說”（有隔離級别的差別），一個事務所做的修改在最終送出之前，對其他事務是不可見的。
• 持久性Durability：一個事務一旦送出，則資料将持久化到本地，除非其他事務對其進行修改

事務的分類：

• 隐式事務，沒有明顯的開啟和結束事務的标志：比如insert、update、delete語句本身就是一個事務
• 顯式事務，具有明顯的開啟和結束事務的标志。開啟事務、編寫事務邏輯、送出/復原事務

2 事務指令

-- 開啟事務有下面3種方式：
- START TRANSACTION; -- 臨時一次事務
- begin -- 臨時一次事務
- set autocommit=0 -- 永久變為事務
開啟事務後，所有被執行的SQL語句均被認作目前事務内的SQL語句。

set autocommit=0;
start transaction;
...
commit;-- 事務送出
rollback; -- 事務復原


savepoint  斷點 # 可以復原 # 在事務的過程中使用
commit to 斷點
rollback to 斷點

-- 事務的原理
    利用InnoDB的自動送出(autocommit)特性完成。
    普通的MySQL執行語句後，目前的資料送出操作均可被其他用戶端可見。
    而事務是暫時關閉“自動送出”機制，需要commit送出持久化資料操作。
-- 注意
    1. 資料定義語言（DDL）語句不能被復原，比如建立或取消資料庫的語句，和建立、取消或更改表或存儲的子程式的語句。
    2. 事務不能被嵌套
-- 儲存點
    SAVEPOINT 儲存點名稱 -- 設定一個事務儲存點
    ROLLBACK TO SAVEPOINT 儲存點名稱 -- 復原到儲存點
    RELEASE SAVEPOINT 儲存點名稱 -- 删除儲存點
-- InnoDB自動送出特性設定
    SET autocommit = 0|1;   0表示關閉自動送出，1表示開啟自動送出。
    - 如果關閉了，那普通操作的結果對其他用戶端也不可見，需要commit送出後才能持久化資料操作。
    - 也可以關閉自動送出來開啟事務。但與START TRANSACTION不同的是，
        SET autocommit是永久改變伺服器的設定，直到下次再次修改該設定。(針對目前連接配接)
        而START TRANSACTION記錄開啟前的狀态，而一旦事務送出或復原後就需要再次開啟事務。(針對目前事務)
           

3 事務隔離級别:

• READ UNCOMMITTED：讀未送出：這個事務修改了資料，即使沒有送出，其他事務也可以讀到修改後的值，這也被稱為髒讀。這個級别會導師制很多問題，從性能上來說也沒有比其他級别好太多，是以除非真的必要，實際中很少使用
  • 髒讀：T1讀取到了T2未送出的資料。【更新】
• READ COMMITTED：讀已送出。可以避免髒讀。這個級别也叫做不可重複度，因為兩次執行同樣的查詢，可能會得到不同的結果。
  • 不可重複讀：T1,T2讀取了同一字段，T2改後，T1再讀發現不一樣了。髒讀是某一事務讀取了另一個事務未送出的髒資料，而不可重複讀則是讀取了其他事務已送出的資料。需要注意的是在某些情況下不可重複讀并不是問題。
• REPEATABLE READ==（Mysql預設）==：可重複讀：可以避免髒讀、不可重複讀。但是有時可能出現幻讀資料。讀取資料的事務将會禁止寫事務(但允許讀事務)，寫事務則禁止任何其他事務。Mysql預設使用該隔離級别。這可以通過“共享讀鎖”和“排他寫鎖”實作，即事物需要對某些資料進行修改必須對這些資料加

  X 鎖

  ，讀資料時需要加上

  S 鎖

  ，當資料讀取完成并不立刻釋放 S 鎖，而是等到事物結束後再釋放。
  • 幻讀phantom read：T1打開事務後select隻發現3行，然後T1暫停，T2 insert後送出，T1再update table1 set '111’發現更新了4行。
  • 幻讀：（自己先全局修改，别人再添加，自己最後查詢）即例如事務T1對一個表中所有的行的某個資料項做了從“1”修改為“2”的操作，這時事務T2又對這個表中插入了一行資料項，而這個資料項的數值還是為“1”并且送出給資料庫。而操作事務T1的使用者如果再檢視剛剛修改的資料，會發現還有一行沒有修改，其實這行是從事務T2中添加的，就好像産生幻覺一樣，這就是發生了幻讀。幻讀和不可重複讀都是讀取了另一條已經送出的事務(這點就髒讀不同)，所不同的是不可重複讀可能發生在update,delete操作中，而幻讀發生在insert操作中。
  • 幻讀最根本的原因：發生了MVCC的"目前讀"
  • 如何防止幻讀：去看MVCC和間隙鎖吧
• SERIALIZABLE可以避免髒讀、不可重複讀和幻讀

鎖的類型

• 行鎖隻是一個比較泛的概念，在innodb存儲引擎中，行鎖的實作主要包含三種算法：
  1. Record-Key Lock：單個資料行的鎖，鎖住單條記錄；
  2. Gap Lock：間隙鎖，鎖住一個範圍，但是不包含資料行本身；
  3. Next-Key Lock：Record-Key Lock + Gap Lock，鎖住資料行本身和一個範圍的資料行。
• 是以innodb的行鎖不是簡單的鎖住某一個資料行這個單條記錄，而是根據更新條件，如WHERE中可能包含 > 等範圍條件，和事務隔離級别來确定是鎖住單條還是多條資料行。

事務隔離級别	髒讀	不可重複度	幻讀	第一類丢失更新	第二類丢失更新
READ UNCOMMITED	允許	允許	允許	不允許	允許
READ COMMITTED	不允許	允許	允許	不允許	允許
REPEATABLE READ	不允許	不允許	允許	不允許	不允許
SERIALIZABLE	不允許	不允許	不允許	不允許	不允許

隔離級别	髒讀	不可重複讀	幻讀
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

設定隔離級别：

# 設定隔離級别
set session|global  transaction isolation level 隔離級别名;
# 檢視隔離級别
select @@tx_isolation;
           

4 Mysql中的事務

自動送出

mysql預設采用自動送出auto commit的模式，即一條語句輸入完後立即執行，不等後一句輸入。

show variables lick 'autocommit'; -- 可以查詢自動送出狀态

-- 關閉自動送出
set autocommit=1
修改該值對非事務型的表，如MyISAM或者記憶體表，不會有任何影響。因為他們沒有COMMIT和ROLLBACK的概念，也可以說相當于一隻處于autocommit啟用的模式。
           

強制自動送出

還有一些指令，也會強制執行COMMIT送出目前的活動事務。如資料定義語言DDL中，如果是導緻大量資料改變的操作，如ALTER TABLE、LOCK TABLE，會自動送出之前的語句

隐式和顯式鎖定

InnoDB采用的是兩階段鎖定協定。在事務執行過程中，随時都可以執行鎖定，鎖隻有在執行COMMIT或者ROLLBACK的時候才會釋放，并且所有的鎖是在同一時刻釋放。前面描述的鎖定都是隐式鎖定，InnoDB會根據隔離級别在需要的時候自動加鎖。

另外InnoDB也支援通過特定的語句進行顯示鎖定，這些語句不屬于SQL規範

select ...LOCK IN SHARE MODE; -- 讀鎖

select ...FOR UPDATE; -- 寫鎖
           

LBCC

InnoDB

預設的事務隔離級别是

repeatable read

（後文中用簡稱 RR），它為了解決該隔離級别下的幻讀的并發問題，提出了

LBCC

和

MVCC

兩種方案。

• 其中

  LBCC

  解決的是目前讀情況下的幻讀，

• MVCC

  解決的是普通讀（快照讀）的幻讀。

至于什麼是目前讀，什麼是快照讀，将在下文中給出答案。

LBCC基于鎖

注意，LBCC是基于鎖的，這句話的意思是說，你使用了for update等語句時才會進入LBCC模式。

LBCC還有一個前提是走了索引，如果沒走索引那麼使用select …for update鎖的是表鎖

LBCC

是

Lock-Based Concurrent Control

的簡稱，意思是基于鎖的并發控制。

在

InnoDB

中按鎖的模式來分的話可以分為共享鎖（S）、排它鎖（X）和意向鎖，其中意向鎖又分為意向共享鎖（IS）和意向排它鎖（IX）（此處先不做介紹，後期會專門出篇文章講一下

InnoDB

和

Myisam

引擎的鎖）；

如果按照鎖的算法來分的話又分為記錄鎖（

Record Locks

）、間隙鎖（

Gap Locks

）和臨鍵鎖（

Next-key Locks

）。其中臨鍵鎖就可以用來解決 RR 下的幻讀問題。那麼什麼是臨鍵鎖呢？繼續往下看。

我們将資料庫中存儲的每一行資料稱為記錄。則上圖中

• 1、5、9、11 分别代表 id 為目前數的記錄。
• 對于鍵值在條件範圍内但不存在的記錄，叫做間隙（GAP）。則上圖中的（-∞，1）、（1，5）…（11，+∞）為資料庫中存在的間隙。
• 而（-∞，1]、（1，5]…（11，+∞）我們稱之為臨鍵，即左開右閉的集合。

1、記錄鎖（Record Locks）

對表中的行記錄加鎖，叫做記錄鎖，簡稱行鎖。可以使用

sql

語句

select ... for update

來開啟鎖，

select

語句必須為精準比對（=），不能為範圍比對，且比對列字段必須為唯一索引或者主鍵列。也可以通過對查詢條件為主鍵索引或唯一索引的資料行進行

UPDATE

操作來添加記錄鎖。

記錄鎖存在于包括主鍵索引在内的唯一索引中，鎖定單條索引記錄。

2、間隙鎖（GAP Locks）

間隙鎖（Gap Lock）是Innodb在[外鍊圖檔轉存失敗,源站可能有防盜鍊機制,建議将圖檔儲存下來直接上傳(img-z0kA1Coa-1629816198886)(https://math.jianshu.com/math?formula=%5Ccolor%7Bred%7D%7B%E5%8F%AF%E9%87%8D%E5%A4%8D%E8%AF%BB%7D)]送出下為了解決幻讀問題時引入的鎖機制，（下面的所有案例沒有特意強調都使用可重複讀隔離級别）幻讀的問題存在是因為新增或者更新操作，這時如果進行範圍查詢的時候（加鎖查詢），會出現不一緻的問題，這時使用不同的行鎖已經沒有辦法滿足要求，需要對一定範圍内的資料進行加鎖，間隙鎖就是解決這類問題的。在可重複讀隔離級别下，資料庫是通過行鎖和間隙鎖共同組成的（next-key lock），來實作的。

間隙鎖是對範圍加鎖，但不包括已存在的索引項。可以使用sql語句

select ... for update

來開啟鎖，

總結：使用for update就禁止其他線程操作該記錄了

GAP Locks

隻存在于 RR 隔離級别下，它鎖住的是間隙内的資料。加完鎖之後，間隙中無法插入其他記錄，并且鎖的是記錄間隙，而非

sql

語句。間隙鎖之間都不存在沖突關系。

下面情況會加上間隙鎖：

• select

  語句為範圍查詢，比對列字段為索引項，且沒有資料傳回；
• 或者

  select

  語句為等值查詢，比對字段為唯一索引，也沒有資料傳回。

為什麼這麼做：前面說了是為了防止目前讀産生的幻讀，是以就自己加鎖不讓别人通路，如果有指定的記錄那麼直接就鎖住了沒問題，但是沒有記錄呢？不能因為目前讀就産生幻讀讀到别事務插入的内容吧，是以就鎖住區間，“我沒查到你也别插入，省得我目前讀後疑惑”，但是加鎖的範圍多大呢？這個就是間隙鎖要關注的

間隙鎖有一個比較緻命的弱點，就是當鎖定一個範圍鍵值之後，即使某些不存在的鍵值也會被無辜的鎖定，而造成在鎖定的時候無法插入鎖定鍵值範圍内的任何資料。在某些場景下這可能會對性能造成很大的危害。以下是加鎖之後，插入操作的例子：

select * from user where id > 15 for update;

# 其他事務執行
# 插入失敗，因為id=20大于15，不難了解
insert into user values(20,'20');
# 插入失敗，原因是間隙鎖鎖的是記錄間隙，而不是sql，也就是說`select`語句的鎖範圍是（11，+∞），而13在這個區間中，是以也失敗。
insert into user values(13,'13');
           

打開間隙鎖設定： 以通過指令

show variables like 'innodb_locks_unsafe_for_binlog';

來檢視

innodb_locks_unsafe_for_binlog

是否禁用。

innodb_locks_unsafe_for_binlog

預設值為 OFF，即啟用間隙鎖。因為此參數是隻讀模式，如果想要禁用間隙鎖，需要修改

my.cnf

（windows 是

my.ini

） 重新啟動才行。

#在 my.cnf 裡面的[mysqld]添加
[mysqld]
innodb_locks_unsafe_for_binlog = 1
           

3、臨鍵鎖（Next-Key Locks）

當我們對上面的記錄和間隙共同加鎖時，添加的便是臨鍵鎖（左開右閉的集合加鎖）。為了防止幻讀，臨鍵鎖阻止特定條件的新記錄的插入，因為插入時要擷取插入意向鎖，與已持有的臨鍵鎖沖突。可以使用sql語句

select ... for update

來開啟鎖，

• select

  語句為範圍查詢，比對列字段為索引項，且有資料傳回；
• 或者

  select

  語句為等值查詢，比對列字段為索引項，不管有沒有資料傳回。

插入意向鎖并非意向鎖，而是一種特殊的間隙鎖。

總結

下面分為3個情況考慮：沒有命中索引、走普通索引、走唯一索引

下面最簡單的是唯一索引，沒有命中鎖間隙所就行，用不到臨建鎖

• 如果查詢沒有命中索引，則退化為表鎖;
• 唯一索引
  • 如果等值查詢唯一索引且命中唯一一條記錄，則退化為行鎖;
  • 如果等值查詢唯一索引且沒有命中記錄，則退化為臨近結點的間隙鎖;
  • 如果範圍查詢唯一索引或查詢非唯一索引且命中記錄，則鎖定所有命中行的臨鍵鎖 ，并同時鎖定最大記錄行下一個區間的間隙鎖。（說得比較複雜，其實就是說左右找到确定不包含的唯一值，然後把中間的都鎖住）
• 如果等值查詢非唯一索引且沒有命中記錄，退化為臨近結點的間隙鎖(包括結點也被鎖定)；如果命中記錄，則鎖定所有命中行的臨鍵鎖，并同時鎖定最大記錄行下一個區間的間隙鎖。
• 如果範圍查詢索引且沒有命中記錄，退化為臨近結點的間隙鎖(包括結點也被鎖定)。

MVCC

參考：（推薦）https://blog.csdn.net/SnailMann/article/details/94724197

https://juejin.im/post/6844903799534911496

https://segmentfault.com/a/1190000012650596

多版本并發控制(Multi-Version Concurrency Control, MVCC)是MySQL中基于樂觀鎖理論實作隔離級别的方式，用于實作讀已送出和可重複讀隔離級别的實作。可以認為MVCC是行級鎖的一個變種。

MySQL就利用了MVCC來判斷在一個事務中，哪個資料可以被讀出來，哪個資料不能被讀出來。實作對資料庫的并發通路。

多版本控制: 指的是一種提高并發的技術。最早的資料庫系統，隻有讀讀之間可以并發，讀寫、寫讀、寫寫都要阻塞。引入多版本之後，隻有寫寫之間互相阻塞，其他三種操作都可以并行，這樣大幅度提高了InnoDB的并發度。在内部實作中，與Postgres在資料行上實作多版本不同，InnoDB是在undo log中實作的，通過undo log可以找回資料的曆史版本。找回的資料曆史版本可以提供給使用者讀(按照隔離級别的定義，有些讀請求隻能看到比較老的資料版本)，也可以在復原的時候覆寫資料頁上的資料。在InnoDB内部中，會記錄一個全局的活躍讀寫事務數組，其主要用來判斷事務的可見性。

LBCC

是基于鎖的并發控制，因為鎖的粒度過大，會導緻性能的下降，是以提出了比

LBCC

性能更優越的方法

MVCC

。

MVCC

是

Multi-Version Concurremt Control

的簡稱，意思是基于多版本的并發控制協定，通過版本号，避免同一資料在不同僚務間的競争，隻存在于

InnoDB

引擎下。它主要是為了提高資料庫的并發讀寫性能，不用加鎖就能讓多個事務并發讀寫。

MVCC

的實作依賴于：三個隐藏字段、

Undo log

和

Read View

，其核心思想就是：隻能查找事務 id 小于等于目前事務 ID 的行；隻能查找删除時間大于等于目前事務 ID 的行，或未删除的行。接下來讓我們從源碼級别來分析下

MVCC

MVCC

隻在

READ COMMITTED

和

REPEATABLE READ

兩個隔離級别下工作，其他兩個隔離級别不和

MVCC

不相容。

因為

READ UNCOMMITTED

總是讀取最新的資料行，而不是符合目前事務版本的資料行，

而

SERIALIZABLE

則會對所有讀取的行都加鎖。事務的快照時間點（即下文中說到的

Read View

的生成時間）是以第一個

select

來确認的。是以即便事務先開始，但是

select

在後面的事務的

update

之類的語句後進行，那麼它是可以擷取前面的事務的對應的資料。

快照讀和目前讀

提醒：閱讀MVCC和快照讀的時候要注意區分是在上面隔離級别下

MySQL InnoDB下的

目前讀

和

快照讀

?

說白了MVCC就是為了實作讀-寫沖突不加鎖，而這個讀指的就是

快照讀

, 而非目前讀，目前讀實際上是一種加鎖的操作，是悲觀鎖的實作

出現了上面的情況我們需要知道為什麼會出現這種情況。通過MVCC機制，雖然讓資料變得可重複讀，但我們讀到的資料可能是曆史資料，不是資料庫最新的資料。

• 這種讀取曆史資料的方式，我們叫它快照讀 (snapshot read)，
• 而讀取資料庫最新版本資料的方式，叫目前讀 (current read)。

RC 和 RR 隔離級别下的快照讀和目前讀：

RC 隔離級别下，快照讀和目前讀結果一樣，都是讀取已送出的最新；

RR 隔離級别下，目前讀結果是其他事務已經送出的最新結果，快照讀是讀目前事務之前讀到的結果。RR 下建立快照讀的時機決定了讀到的版本。

快照讀select

當執行select操作（不加鎖時）是innodb預設會執行快照讀，會記錄下這次select後的結果，之後select 的時候就會傳回這次快照的資料，即使其他事務送出了不會影響目前select的資料，這就實作了可重複讀了。

像

不加鎖

的select操作就是快照讀，即不加鎖的非阻塞讀；快照讀的前提是隔離級别不是串行級别，串行級别下的快照讀會退化成目前讀；之是以出現快照讀的情況，是基于提高并發性能的考慮，快照讀的實作是基于多版本并發控制，即MVCC,可以認為MVCC是行鎖的一個變種，但它在很多情況下，避免了加鎖操作，降低了開銷；既然是基于多版本，即快照讀可能讀到的并不一定是資料的最新版本，而有可能是之前的曆史版本

快照的生成是在第一次執行select的時候，也就是說假設當開啟了事務A，然後沒有執行任何操作，這時候事務B insert了一條資料然後commit，這時候A執行 select，那麼傳回的資料中就會有B添加的那條資料。之後無論再有其他事務commit都沒有關系，因為快照已經生成了，後面的select都是根據快照來的。

事務1	事務2	說明
begin;	begin;
insert into test values(null,‘D’)
commit;
select * from test;	-	事務1能檢視插入的D，此時生成快照
commit;

如何讓select讀的時候别的事務無法更改這些行：

select的目前讀需要手動地加鎖：（不加鎖就是快照讀）

select * from table where ? lock in share mode;
select * from table where ? for update;
# 其實上面這些操作依據是目前讀了
           

目前讀

目前讀（

Locking Read

）也稱鎖定讀，讀取目前資料的最新版本，而且讀取到這個資料之後會對這個資料加鎖，防止别的事務更改即通過

next-key

鎖（行鎖+gap 鎖）來解決目前讀的問題。在進行寫操作的時候就需要進行“目前讀”，讀取資料記錄的最新版本，包含以下

SQL

類型：

select ... lock in share mode

、

select ... for update

、

update

、

delete

、

insert

。

對于會對資料修改的操作(

update

、

insert

、

delete

)都是采用

目前讀

的模式。在執行這幾個操作時會讀取最新的記錄，即使是别的事務送出的資料也可以查詢到。假設要update一條記錄，但是在另一個事務中已經delete掉這條資料并且commit了，如果update就會産生沖突，是以在update的時候需要知道最新的資料。也正是因為這樣是以才導緻上面（可重複讀級别下幻讀）我們測試的那種情況。

• 在可重複讀隔離級别下，普通查詢select是快照讀，是不會看到别的事務插入的資料的，幻讀隻有發生過目前讀才會出現。
• 幻讀專指新插入的行，讀到原本存在行的更新結果不算。因為目前讀的作用就是能讀到所有已經送出記錄的最新值。

如下操作是目前讀：

• select lock in share mode(

  共享鎖

  ),
• select for update; (

  排他鎖

  )
• update, insert ,delete(

  排他鎖

  )

為什麼叫目前讀？就是它讀取的是記錄的最新版本，讀取時還要保證其他并發事務不能修改目前記錄（要擷取寫鎖），會對讀取的記錄進行加鎖。如果别的事務已經修改了但沒送出，目前事務就會卡住阻塞等别的事務commit

在資料庫的增、删、改、查中，隻有

增、删、改

才會加上排它鎖，而隻是

查詢

并不會加鎖，隻能通過在select語句後顯式加lock

in share mode

或者

for update

來加共享鎖或者排它鎖

假設要update一條記錄，但是在另一個事務中已經delete掉這條資料并且commit了，如果update就會産生沖突，是以在update的時候需要知道最新的資料。

事務 1	事務 2	說明
begin	begin
-	select * from test	1還沒開啟事務
-	insert into test(name) values(“E”)	2生成寫鎖
select * from test	-	1沒卡住，快照讀不卡
delete from test where name=‘E’;	-	1卡住了，目前讀是沖突的
阻塞放行	commit	1放行，2送出後2的寫鎖就取消了，1就拿到了寫鎖
commit

利用MVCC解讀幻讀

https://blog.csdn.net/weixin_33755554/article/details/93881494

幻讀

CREATE TABLE `author` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

INSERT into author VALUES (1,'g1',20),(5,'g5',20),(15,'g15',30),(20,'g20',30);
# 不可重複讀級别
           

查找年齡為20歲的作者，并把姓名改成G0

來分析下情形：

• T1時刻 讀取年齡為20的資料，

  Session1

  拿到了2條記錄。
• T2時刻 另一個程序

  Session2

  插入了一條新的記錄，年齡也為20
• T3時刻，

  Session1

  再次讀取年齡為20的資料，發現還是2條資料，貌似 Session2新插入的資料并未影響到Session1的事務讀取。

對于T1 – T3 時刻的情形，從結果來看，在可重複度讀隔離級别下似乎解決了幻讀的問題。

• T4時刻，Session1 修改年齡為20的資料， 發現影響行數為3條。 為什麼T3時候隻能查到2條資料，但現在修改确修改了3條資料？
• T5時刻，Session1 再次讀取年齡為20的資料，發現結果變成了3條,我們知道被修改的第三條就是Session2在T2時刻新增的一條。

T4,T5 的結果來看，Session1 讀到了 Session2 新插入的資料。産生了幻讀現象

總結：按理說我應該隻會更新2行，卻更新了3行。

到底可重複讀隔離級别下，解決了幻讀問題沒有？

了解過MVCC的同學，肯定知道或聽說過目前讀，和快照讀。首先要知道的是MVCC 就InnoDB 秒級建立資料快照的能力。 快照讀就是讀取資料的時候會根據一定規則讀取事務可見版本的資料。 而目前讀就是讀取最新版本的資料。什麼情況下使用的是快照讀：（快照讀，不會加鎖）

一般的 select * from … where … 語句都是快照讀

什麼情況下使用的是目前讀：（目前讀，會在搜尋的時候加鎖）

select * from … where … for update

select * from … where … lock in share mode

update … set … where …

delete from. . where …

如果事務中都使用快照讀，那麼就不會産生幻讀現象，但是快照讀和目前讀混用就會産生幻讀。

如果都是使用目前讀，能不能解決幻讀問題？

先讓我們資料恢複到初始狀态

TRUNCATE TABLE author;
INSERT into author VALUES (1,'g1',20),(5,'g5',20),(15,'g15',30),(20,'g20',30);
           

可以看到Session 2 被阻塞了。需要等到Session1 送出事務後才能完成。當我們在事務中每次讀取都使用目前讀，也就是人工把InnoDB變成了串行化。一定程度上降低了并發性，但是也同樣避免了幻讀的情況。

目前讀為什麼會阻塞新資料的插入，主要是間隙鎖的加鎖機制。

幻讀前提

前提：InnoDB引擎，可重複讀隔離級别下，使用過目前讀時。

• 在可重複讀隔離級别下，普通查詢是快照讀，是不會看到别的事務插入的資料的，幻讀隻在目前讀下才會出現。
• 幻讀專指新插入的行，讀到原本存在行的更新結果不算。因為目前讀的作用就是能讀到所有已經送出記錄的最新值。

事務 1	事務 2	說明
begin	begin	假設現有資料一行A
select * from test
-	insert into test(name) values(“B”)
-	commit	2送出後1還是隻有1條
update test set name=“C”	此時1再查詢就已經2條C了
commit	儲存了C的記錄
如果把上個commit換成rollback	查詢結果還是B

如下，我們永遠鎖的是目前d=5的資料，而無法鎖新插入d=5的資料，是以再次目前讀 讀資料 的時候會發現跟原來讀的不一樣

幻讀的影響：

• 會造成一個事務中先産生的鎖，無法鎖住後加入的滿足條件的行
• 産生資料一緻性問題，在一個事務中，先對符合條件的目标行做變更，而在事務送出前有新的符合目标條件的行加入，這樣通過binlog恢複的資料是會将所有符合條件的目标行都進行變更的。

如何解決幻讀：

• 将兩行記錄間的空隙加上鎖，組合新記錄的插入；這個鎖稱為間隙鎖
• 間隙鎖和間隙鎖之間沒有沖突。跟間隙鎖存在沖突關系的是"往這個間隙鎖中插入一個記錄"這個操作

原值
id  name
1   A
-----------
理論值
id  name
1   C
2   B
-----------
實際值
id  name
1   C
2   C
-----------
本來我們希望得到的結果隻是第一條資料的改為C，但是結果卻是兩條資料都被修改C了。
這種結果告訴我們其實在MySQL可重複讀的隔離級别中并不是完全解決了幻讀的問題，而是解決了讀資料情況下的幻讀問題。而對于修改的操作依舊存在幻讀問題，就是說MVCC對于幻讀的解決時不徹底的。

怎麼用READVIEW的理論解釋：
           

先介紹兩個概念：

• 系統版本号：一個遞增的數字，每開始一個新的事務，系統版本号就會自動遞增。
• 事務版本号：事務開始時的系統版本号。

MVCC的實作原理

MVCC的目的就是多版本并發控制，在資料庫中的實作，就是為了解決

讀寫沖突

，MVCC的實作原理主要是依賴記錄中的

3個隐式字段

，

undo日志

，

Read View

來實作的。是以我們先來看看這個三個關鍵點的概念

快照讀就是MySQL為我們實作MVCC理想模型的其中一個具體非阻塞讀功能。而相對而言，目前讀就是悲觀鎖的具體功能實作

①行格式簡介

主要說行格式裡的隐式字段

每行記錄除了我們自定義的字段外，還有資料庫隐式定義的

DB_TRX_ID

,

DB_ROLL_PTR

,

DB_ROW_ID

等字段。（在InnoDB引擎表中，它的聚簇索引記錄中。）

• DB_TRX_ID

  6byte，最近修改(

  修改/插入

  )事務ID：記錄建立這條記錄/或最後一次修改該記錄的事務ID

• DB_ROLL_PTR

  7byte，復原指針，指向這條記錄的上一個版本（存儲于rollback segment裡）。roll_pointer。每次對哪條聚簇索引記錄有修改的時候，都會把老版本寫入

  undo

  日志中。這個roll_pointer就是存了一個指針，它指向這條聚簇索引記錄的上一個版本的位置，通過它來獲得上一個版本的記錄資訊。(注意插入操作的undo日志沒有這個屬性，因為它沒有老版本)

• DB_ROW_ID

  6byte，隐含的自增ID（隐藏主鍵），如果資料表沒有主鍵和非空唯一主鍵，InnoDB會自動以

  DB_ROW_ID

  産生一個聚簇索引。
  • 如果沒有定義主鍵，InnoDB會選擇一個唯一的非空索引代替。如果沒有這樣的索引，InnoDB會隐式地定義一個主鍵來作為聚簇索引。InnoDB隻聚集在同一個頁面中的記錄。包含相鄰鍵值的頁面可能會相距甚遠。
• 實際還有一個删除flag隐藏字段, 既記錄被更新或删除并不代表真的删除，而是删除flag變了

id	name	建立時間(事務ID)	删除時間(事務ID)
1	yang	1	undefined
2	long	1	undefined
3	fei	1	undefined

如上圖，

DB_ROW_ID

是資料庫預設為該行記錄生成的唯一隐式主鍵，

DB_TRX_ID

是目前操作該記錄的事務ID,而

DB_ROLL_PTR

是一個復原指針，用于配合undo日志，指向上一個舊版本

比如我們原有資料

id	name	transaction_ID	roll_pointer
1	A	5	上一個版本的位址

比如執行了一條更新操作

# 事務id=6
update test set name='B' where id=1;
           

id	name	transaction_id	roll_pointer
1	B	6	指向本條記錄的舊版本id=5
1	A	5

具體的執行過程

begin->用排他鎖鎖定該行->記錄redo log->記錄undo log->修改目前行的值，寫事務編号，復原指針指向undo log中的修改前的行

上述過程确切地說是描述了UPDATE的事務過程，其實undo log分insert和update undo log，因為insert時，原始的資料并不存在，是以復原時把insert undo log丢棄即可，而update undo log則必須遵守上述過程

②undo日志

每當我們要對一條記錄做改動時（這裡的改動可以指 INSERT、DELETE、UPDATE），都需要把復原時所需的東西記錄下來, 比如:

undo log主要分為兩種：

• insert undo log
  • 代表事務在

    insert

    新記錄時産生的

    undo log

    , 隻在事務復原時需要，并且在事務送出後可以被立即丢棄
  • 插入一條記錄時，至少要把這條記錄的主鍵值記下來，之後復原的時候隻需要把這個主鍵值對應的記錄删掉就好了
• Delete undo log：删除一條記錄時，至少要把這條記錄中的内容都記下來，這樣之後復原時再把由這些内容組成的記錄插入到表中就好了。
• update undo log
  • 事務在進行

    update

    或

    delete

    時産生的

    undo log

    ; 不僅在事務復原時需要，在快照讀時也需要；是以不能随便删除，隻有在快速讀或事務復原不涉及該日志時，對應的日志才會被

    purge

    線程統一清除
  • 修改一條記錄時，至少要把修改這條記錄前的舊值都記錄下來，這樣之後復原時再把這條記錄更新為舊值就好了。

    InnoDB

    把這些為了復原而記錄的這些東西稱之為

    undo log

    。這裡需要注意的一點是，由于查詢操作（

    SELECT

    ）并不會修改任何使用者記錄，是以在查詢操作執行時，并不需要記錄相應的

    undo log

    。

每次對記錄進行改動都會記錄一條 undo 日志，每條 undo 日志也都有一個

DB_ROLL_PTR

屬性，可以将這些 undo 日志都連起來，串成一個連結清單，形成版本鍊。版本鍊的頭節點就是目前記錄最新的值。

例

先插入一條記錄，假設該記錄的事務 id 為 80，那麼此刻該條記錄的示意圖如下所示

實際上

insert undo

隻在事務復原時起作用，當事務送出後，該類型的 undo 日志就沒用了，它占用的

Undo Log Segment

也會被系統回收。接着繼續執行 sql 操作

其版本鍊如下

很多人以為

undo log

用于将資料庫實體的恢複到執行語句或者事務之前的樣子，其實并非如此，

undo log

是邏輯日志，隻是将資料庫邏輯的恢複到原來的樣子。因為在多并發系統中，你把一個頁中的資料實體的恢複到原來的樣子，可能會影響其他的事務。

purge線程

• 從前面的分析可以看出，為了實作InnoDB的MVCC機制，更新或者删除操作都隻是設定一下老記錄的deleted_bit，并不真正将過時的記錄删除。
• 為了節省磁盤空間，InnoDB有專門的purge線程來清理deleted_bit為true的記錄。為了不影響MVCC的正常工作，purge線程自己還維護了一個

  read view

  （這個read view相當于系統中最老活躍事務的read view）;
• 如果某個記錄的deleted_bit為true，并且DB_TRX_ID相對于purge線程的read view可見(這裡應該是說事務送出了就是可見的)，那麼這條記錄一定是可以被安全清除的。

• 大多數對資料的變更操作包括INSERT/DELETE/UPDATE，其中INSERT操作在事務送出前隻對目前事務可見，是以産生的Undo日志可以在事務送出後直接删除（誰會對剛插入的資料有可見性需求呢！！），而對于UPDATE/DELETE則需要維護多版本資訊，在InnoDB裡，UPDATE和DELETE操作産生的Undo日志被歸成一類，即update_undo
• 另外, 在復原段中的undo logs分為:

  insert undo log

  和

  update undo log

  • insert undo log : 事務對insert新記錄時産生的undolog, 隻在事務復原時需要, 并且在事務送出後就可以立即丢棄。
  • update undo log : 事務對記錄進行delete和update操作時産生的undo log, 不僅在事務復原時需要, 一緻性讀也需要，是以不能随便删除，隻有當資料庫所使用的快照中不涉及該日志記錄，對應的復原日志才會被purge線程删除。

https://segmentfault.com/a/1190000012650596

InnoDB存儲引擎在資料庫每行資料的後面添加了三個字段

• 6位元組的

  事務ID

  (

  DB_TRX_ID

  )字段: 用來辨別最近一次對本行記錄做修改(insert|update)的事務的辨別符, 即最後一次修改(insert|update)本行記錄的事務id。

  至于delete操作，在innodb看來也不過是一次update操作，更新行中的一個特殊位将行表示為deleted, 并非真正删除。

• 7位元組的

  復原指針

  (

  DB_ROLL_PTR

  )字段: 指寫入復原段(rollback segment)的

  undo log

  record (撤銷日志記錄記錄)。

  如果一行記錄被更新, 則

  undo log

  record 包含 ‘重建該行記錄被更新之前内容’ 所必須的資訊。
• 6位元組的

  DB_ROW_ID

  字段: 包含一個随着新行插入而單調遞增的行ID, 當由innodb自動産生聚集索引時，聚集索引會包括這個行ID的值，否則這個行ID不會出現在任何索引中。

  結合聚簇索引的相關知識點, 我的了解是, 如果我們的表中沒有主鍵或合适的唯一索引, 也就是無法生成聚簇索引的時候, InnoDB會幫我們自動生成聚集索引, 但聚簇索引會使用DB_ROW_ID的值來作為主鍵; 如果我們有自己的主鍵或者合适的唯一索引, 那麼聚簇索引中也就不會包含 DB_ROW_ID 了 。

  關于聚簇索引, 《高性能MySQL》中的篇幅對我來說已經夠用了, 稍後會整理一下以前的學習筆記, 然後更新上來。

對MVCC有幫助的實質是

update undo log

，

undo log

實際上就是存在

rollback segment

中舊記錄鍊，它的執行流程如下：

一、 比如一個有個事務插入person表插入了一條新記錄，記錄如下，

name

為Jerry,

age

為24歲，

隐式主鍵

是1，

事務ID

和

復原指針

，我們假設為NULL（這裡應該會産生一條insert undo log）

二、 現在來了一個

事務1

對該記錄的

name

做出了修改，改為Tom

• 在

  事務1

  修改該行(記錄)資料時，資料庫會先對該行加

  排他鎖

  （自動加，事務送出後時釋放）
• 然後把該行資料拷貝到

  undo log

  中(應該是update undo log)，作為舊記錄，既在

  undo log

  中有目前行的拷貝副本（修改前的）。（unlog是每行有一個log還是全部行共享一個log無所謂，反正有指針串聯）
• 拷貝完畢後，修改該行

  name

  為Tom(資料庫中的)，并且修改隐藏字段的事務ID為目前

  事務1

  的ID, 我們預設從

  1

  開始，之後遞增，復原指針指向拷貝到

  undo log

  的副本記錄，既表示我的上一個版本就是它
• 事務送出後，釋放鎖

三、 又來了個

事務2

修改

person表

的同一個記錄，将

age

修改為30歲

• 在

  事務2

  修改該行資料時，資料庫也先為該行加鎖
• 然後把該行資料拷貝到

  undo log

  中(update undo log)，作為舊記錄，發現該行記錄已經有

  undo log

  了，那麼最新的舊資料作為連結清單的表頭（頭插），插在該行舊記錄的

  undo log

  最前面
• 修改該行

  age

  為30歲，并且修改隐藏字段的事務ID為目前

  事務2

  的ID, 那就是

  2

  ，復原指針指向剛剛拷貝到

  undo log

  的副本記錄
• 事務送出，釋放鎖

從上面，我們就可以看出，不同僚務或者相同僚務的對同一記錄的修改，會導緻該記錄的

undo log

成為一條記錄版本線性表，既連結清單，

undo log

的鍊首就是最新的舊記錄，鍊尾就是最早的舊記錄（當然就像之前說的該undo log的節點可能是會purge線程清除掉，向圖中的第一條insert undo log，其實在事務送出之後可能就被删除丢失了，不過這裡為了示範，是以還放在這裡）

③ReadView

已送出讀和可重複讀的差別就在于它們生成ReadView的政策不同。

我覺得每個事務都有自己的read view

ReadView：ReadView中主要就是有個清單來存儲我們系統中目前活躍着的讀寫事務，也就是begin了還未送出的事務。

什麼是Read View?

什麼是Read View，說白了Read View就是事務進行

快照讀

操作的時候生産的

讀視圖

(Read View)，在該事務執行的快照讀的那一刻，會生成資料庫系統目前的一個快照，記錄并維護系統目前活躍事務的ID(當每個事務開啟時，都會被配置設定一個ID, 這個ID是遞增的，是以最新的事務，ID值越大)

是以我們知道

Read View

主要是用來做可見性判斷的, 即當我們某個事務執行快照讀的時候，對該記錄建立一個

Read View

讀視圖，把它比作條件用來判斷目前事務能夠看到哪個版本的資料，既可能是目前最新的資料，也有可能是該行記錄的

undo log

裡面的某個版本的資料。

Read View

遵循一個可見性算法，主要是将

要被修改的資料

的最新記錄中的

DB_TRX_ID

（即目前行事務ID）取出來，與系統目前其他活躍事務的ID去對比（由Read View維護），如果

DB_TRX_ID

跟Read View的屬性做了某些比較，不符合可見性，那就通過

DB_ROLL_PTR

復原指針去取出

Undo Log

中的

DB_TRX_ID

再比較，即周遊連結清單的

DB_TRX_ID

（從鍊首到鍊尾，即從最近的一次修改查起），直到找到滿足特定條件的

DB_TRX_ID

, 那麼這個DB_TRX_ID所在的舊記錄就是目前事務能看見的最新

老版本

前面說了purge線程用來清理沒有用的記錄，為了能讓這個功能工作，它維護着read view。

拿着目前事務id可以追蹤要查詢行的undo log連結清單，從首到尾找到小于等于該事務id的記錄即可（其實這麼說不對，後面我們再探究）。

purge線程

• 從前面的分析可以看出，為了實作InnoDB的MVCC機制，更新或者删除操作都隻是設定一下老記錄的deleted_bit，并不真正将過時的記錄删除。
• 為了節省磁盤空間，InnoDB有專門的purge線程來清理deleted_bit為true的記錄。為了不影響MVCC的正常工作，purge線程自己還維護了一個

  read view

  （這個read view相當于系統中最老活躍事務的read view）;
• 如果某個記錄的deleted_bit為true，并且DB_TRX_ID相對于purge線程的read view可見(這裡應該是說事務送出了就是可見的)，那麼這條記錄一定是可以被安全清除的。

通過這個清單來判斷記錄的某個版本是否對目前事務可見，“我到底可以讀取這個資料的哪個版本”。

那麼這個判斷條件是什麼呢？

我們這裡盜竊@呵呵一笑百媚生一張源碼圖，如上，它是一段MySQL判斷可見性的一段源碼，即

changes_visible

方法（不完全哈，但能看出大緻邏輯），該方法展示了我們拿DB_TRX_ID去跟Read View某些屬性進行怎麼樣的比較

ReadView組成

ReadView包含四個比較重要的内容：

• m_ids

  ：生成ReadView時，系統中活躍的事務

  {id}集合

  。

  trx_list

  （名字我随便取的）。
  • 這個的意義在于，如果目前事務建立時别的事務還沒送出，即使目前事務的id大，也不能讀取那些沒有送出的事務（事務id小）

• up_limit_id

  ：上面{id}中的最小值。
  • 小于這個事務id的undo log肯定能看到

• low_limit_id

  ：生成ReadView時，系統應該配置設定給下一個事務的id。也就是

  目前已出現過的事務ID的最大值+1

  • 大于這個事務id的undo log肯定看不到

• creator_trx_id

  ：生成該ReadView的事務id。

1. 如果落在綠色部分（trx_id<min_id），表示這個版本是已經送出的事務生成的，這個資料是可見的；
2. 如果落在紅色部分（trx_id>max_id），表示這個版本是由将來啟動的事務生成的，是肯定不可見的；
3. 如果落在黃色部分（min_id<=trx_id<=max_id），那就包含兩種情況：a.若 row 的 trx_id 在數組中，表示這個版本是由還沒送出的事務生成的，不可見；如果是自己的事務，則是可見的；b.若 row 的 trx_id 不在數組中，表示這個版本是已經送出了的事務生成的，可見。

檢視目前所有的未送出并活躍的事務，存儲在數組中

選取未送出并活躍的事務中最小的XID，記錄在快照的xmin中

選取所有已送出事務中最大的XID，加1後記錄在xmax中

使用方法：

• 比較目前行的事務id

  DB_TRX_ID

  和READVIEW的内容
• 首先比較目前行事務号

  DB_TRX_ID < up_limit_id

  或

  id==m_createot_trx_id

  （滿足則看得見）, 如果小于，則目前事務能看到

  DB_TRX_ID

  所在的記錄，如果大于等于進入下一個判斷。（該行沒有在事務中）
• 接下來判斷

  DB_TRX_ID >= low_limit_id

  （滿足也看不見） , 如果大于等于則代表

  DB_TRX_ID

  所在的記錄在

  Read View

  生成後才出現的，那對目前事務肯定不可見，如果小于則進入下一個判斷
• 判斷

  DB_TRX_ID

  是否在活躍事務之中，

  trx_list.contains(DB_TRX_ID)

  ，如果在，則代表我

  Read View

  生成時刻，你這個事務還在活躍，還沒有Commit，你修改的資料，我目前事務也是看不見的；如果不在，則說明，你這個事務在

  Read View

  生成之前就已經Commit了，你修改的結果，我目前事務是能看見的

• 如果被通路的版本的trx_id和ReadView中的creator_trx_id相同，就意味着目前版本就是由你“造成”的，可以讀出來。
• 如果被通路的版本的trx_id小于ReadView中的low_limit_id，表示生成該版本的事務在建立ReadView的時候，已經送出了，是以該版本可以讀出來。（在ReadView裡的檢查過了不符合條件才讀不在ReadView裡的）
• 如果被通路版本的trx_id大于或等于ReadView中的up_limit_id值，說明生成該版本的事務在目前事務生成ReadView後才開啟，是以該版本不可以被讀出來。（等于讀不出來是因為最大值不是活躍的事務，而是不存在的下個版本）
• 如果生成被通路版本的trx_id在low_limit_id和up_limit_id之間，那就需要判斷下trx_id在不在m_ids中：如果在，說明建立ReadView的時候，生成該版本的事務還是活躍的（沒有被送出），該版本不可以被讀出來；如果不在，說明建立ReadView的時候，生成該版本的事務已經被送出了，該版本可以被讀出來。

如果某個資料的最新版本不可以被讀出來，就順着roll_pointer找到該資料的上一個版本，繼續做如上的判斷，以此類推，如果第一個版本也不可見的話，代表該資料對目前事務完全不可見，查詢結果就不包含這條記錄了。

兩種隔離級别下的ReadView

①REPEATABLE READ可重複讀 隔離級别

關鍵：隻有首次讀取資料會建立ReadView（更新事務後會變為未讀取狀态）

原始版本号0，原始id=1的name為小明

事務1：事務版本号 #1，要修改id=1的name為小明1

事務2：事務版本号#2，要查詢id=1的name

事務1	事務2	說明
begin;	begin;
update test set name=‘AA’ where id=1	-	1先改，2再查
select * from test where id=1;

此時事務2生成的READVIEW-2如下：

活躍事務号	row-id	name	復原指針roll_ponit
#1	1	小明1	指向0，該行不在READVIEW中
事務前版本#0	1	小明	本行不在READVIEW中

ReadView-2的其他資訊：

• m_ids是{1}，。生成ReadView時，系統中活躍的事務id集合。
• low_limit_id是1， 。生成ReadView時，系統中活躍的最小事務id，也就是 m_ids中的最小值。
• up_limit_id是3，。生成ReadView時，系統應該配置設定給下一個事務的id。
• creator_trx_id是2。生成該ReadView的事務id。

那麼A事務執行的select語句會讀到什麼資料呢？

1. 判斷最新的資料版本，name是“小明1”，對應的trx_id是#1，trx_id在m_ids裡面，說明目前事務是活躍事務，這個資料版本是由還沒有送出的事務建立的，是以這個版本不可見。
2. 順着roll_ponit找到這個資料的上一個版本，name是“小明”，對應的trx_id是0，而ReadView中的low_limit_id是1，trx_id<low_limit_id，代表目前資料版本是由已經送出的事務建立的，該版本可見。

是以讀到的資料的name是“小明”。

接着往下執行上面的事務

事務1	事務2	說明
begin;	begin;
update test set name=‘AA’ where id=1	-	1先改，2再查
select * from test where id=1;
commit;
select * from test where id=1;	還是查到小明

随後，事務#1送出了事務，由于REPEATABLE READ是首次讀取資料才會建立ReadView，是以事務#2再次執行select語句，不會再建立ReadView，用的還是上一次的ReadView，是以判斷流程和上面也是一樣的，是以讀到的name還是“小明”。

• 其他知識點：開啟事務後，查詢id=1後（算是事務開始了），其他會話改了id=2，然後事務1再查id=2，事務1查不到其他會話更改commit後的結果，說明事務1的READVIEW是所有行的

②READ COMMITTED隔離級别

這個隔離級别：每次select讀取資料會重新建立ReadView

和上面一樣，id=1的對應的原始name為小明，事務1要改為小明1，事務2要查

假設，現在系統隻有一個活躍的事務#1，事務id是1，事務中修改了資料，但是還沒有送出，形成的版本鍊是這樣的：

事務1	事務2	說明
begin;	begin;
update test set name=‘AA’ where id=1	-	1先改，2再查
select * from test where id=1;	查到的是小明
commit

現在事務2啟動，并且執行了select語句，此時會建立出一個ReadView-2，

• m_ids是{1}
• up_limit_id是1，
• low_limit_id是3，
• creator_trx_id是2。

那麼A事務執行的select語句會讀到什麼資料呢？

1. 判斷最新的資料版本，name是“夢境地底王”，對應的trx_id是100，trx_id在m_ids裡面，說明目前事務是活躍事務，這個資料版本是由還沒有送出的事務建立的，是以這個版本不可見。
2. 順着roll_pointer找到這個資料的上一個版本，name是“地底王”，對應的trx_id是99，而ReadView中的low_limit_id是100，trx_id<low_limit_id，代表目前資料版本是由已經送出的事務建立的，該版本可見。

是以讀到的資料的name是“地底王”。

事務1送出後事務2再次查

事務1	事務2	說明
begin;	begin;
update test set name=‘AA’ where id=1	-	1先改，2再查
select * from test where id=1;	查到小明
commit;
select * from test where id=1;	查到小明1

因為在"讀已送出"隔離級别下，每次查會重新建立READVIEW，是以新的參數為：

• m_ids是{空}，
• up_limit_id是null，
• low_limit_id是3，
• creator_trx_id是2。

查的内容不在m_ids裡面，說明這個資料版本是由已經送出的事務建立的，該版本可見。是以查到小明1

③④其他隔離級别

• 對于READ UNCOMMITTED來說，可以讀取到其他事務還沒有送出的資料，是以直接把這個資料的最新版本讀出來就可以了。讀未送出是沒有加任何鎖的，是以對于它來說也就是沒有隔離的效果，是以它的性能也是最好的。
• 對于SERIALIZABLE來說，是用加鎖的方式來通路記錄。 對于串行化加的是一把大鎖，讀的時候加共享鎖，不能寫，寫的時候，加的是排它鎖，阻塞其它事務的寫入和讀取，若是其它的事務長時間不能寫入就會直接報逾時，是以它的性能也是最差的，對于它來就沒有什麼并發性可言。

④整體流程

我們在了解了

隐式字段

，

undo log

， 以及

Read View

的概念之後，就可以來看看MVCC實作的整體流程是怎麼樣了

整體的流程是怎麼樣的呢？我們可以模拟一下

• 當

  事務2

  對某行資料執行了

  快照讀

  ，資料庫為該行資料生成一個

  Read View

  讀視圖，假設目前事務ID為

  2

  ，此時還有

  事務1

  和

  事務3

  在活躍中，

  事務4

  在

  事務2

  快照讀前一刻送出更新了，是以Read View記錄了系統目前活躍事務1，3的ID，維護在一個清單上，假設我們稱為

  trx_list

事務1	事務2	事務3	事務4
事務開始	事務開始	事務開始	事務開始
…	…	…	修改且已送出
進行中	快照讀	進行中
…	…	…

• Read View不僅僅會通過一個清單

  trx_list

  來維護

  事務2

  執行

  快照讀

  那刻系統正活躍的事務ID，還會有兩個屬性

  up_limit_id

  （記錄trx_list清單中事務ID最小的ID），

  low_limit_id

  (記錄trx_list清單中事務ID最大的ID，也有人說快照讀那刻系統尚未配置設定的下一個事務ID也就是

  目前已出現過的事務ID的最大值+1

  ，我更傾向于後者 >>>資料傳送門 | 呵呵一笑百媚生的回答) ；是以在這裡例子中

  up_limit_id

  就是1，

  low_limit_id

  就是4 + 1 = 5（這兩個命名是反的，真是個坑），trx_list集合的值是1,3，

  Read View

  如下圖

• 我們的例子中，隻有

  事務4

  修改過該行記錄，并在

  事務2

  執行

  快照讀

  前，就送出了事務，是以目前該行目前資料的

  undo log

  如下圖所示；我們的事務2在快照讀該行記錄的時候，就會拿該行記錄的

  DB_TRX_ID

  去跟

  up_limit_id

  ,

  low_limit_id

  和

  活躍事務ID清單(trx_list)

  進行比較，判斷目前

  事務2

  能看到該記錄的版本是哪個。

• 是以先拿該記錄

  DB_TRX_ID

  字段記錄的事務ID

  4

  去跟

  Read View

  的的

  up_limit_id

  比較，看

  4

  是否小于

  up_limit_id

  (1)，是以不符合條件，繼續判斷

  4

  是否大于等于

  low_limit_id

  (5)，也不符合條件，最後判斷

  4

  是否處于

  trx_list

  中的活躍事務, 最後發現事務ID為

  4

  的事務不在目前活躍事務清單中, 符合可見性條件，是以

  事務4

  修改後送出的最新結果對

  事務2

  快照讀時是可見的，是以

  事務2

  能讀到的最新資料記錄是

  事務4

  所送出的版本，而事務4送出的版本也是全局角度上最新的版本

• 也正是Read View生成時機的不同，進而造成RC,RR級别下快照讀的結果的不同

MVCC的術語總結

<高性能MySQL>中對MVCC的部分介紹

• MySQL的大多數事務型存儲引擎實作的其實都不是簡單的行級鎖。基于提升并發性能的考慮, 它們一般都同時實作了多版本并發控制(MVCC)。不僅是MySQL, 包括Oracle,PostgreSQL等其他資料庫系統也都實作了MVCC, 但各自的實作機制不盡相同, 因為MVCC沒有一個統一的實作标準。
• 可以認為MVCC是行級鎖的一個變種, 但是它在很多情況下避免了加鎖操作, 是以開銷更低。雖然實作機制有所不同, 但大都實作了非阻塞的讀操作，寫操作也隻鎖定必要的行。
• MVCC的實作方式有多種，典型的有樂觀(optimistic)并發控制 和 悲觀(pessimistic)并發控制。
• MVCC隻在

  READ COMMITTED

  和

  REPEATABLE READ

  兩個隔離級别下工作。其他兩個隔離級别夠和MVCC不相容, 因為

  READ UNCOMMITTED

  總是讀取最新的資料行, 而不是符合目前事務版本的資料行。而

  SERIALIZABLE

  則會對所有讀取的行都加鎖。

從書中可以了解到:

• MVCC是被Mysql中

  事務型存儲引擎InnoDB

  所支援的;
• 應對高并發事務, MVCC比

  單純的加鎖

  更高效;
• MVCC隻在

  READ COMMITTED

  和

  REPEATABLE READ

  兩個隔離級别下工作;
• MVCC可以使用

  樂觀(optimistic)鎖

  和

  悲觀(pessimistic)鎖

  來實作;
• 各資料庫中MVCC實作并不統一

下面的MVCC内容是高性能Mysql中的内容，但我覺得有很多地方跟上面對不上号，覺得他說的是不開啟事務的情況的理論，我覺得可以不看。

在MySQL中，會在表中每一條資料後面添加兩個字段：

• 建立版本号：建立一行資料時，将目前系統版本号作為建立版本号指派
• 删除版本号：删除一行資料時，将目前系統版本号作為删除版本号指派

前提：可重複讀隔離級别下,MVCC的具體：

SELECT

select時讀取資料的規則為：建立版本号<=目前事務版本号，删除版本号為空或>目前事務版本号。

• ①建立版本号<=目前事務版本号：保證取出的資料不會有後啟動的事務中建立的資料。這也是為什麼在開始的示例中我們不會查出後來添加的資料的原因。
• ②删除版本号為空或>目前事務版本号：保證了在該事物開啟之前該資料沒有被删除，是應該被查出來的資料。
• InnoDB隻查找版本早于目前事務版本的資料行（也就是，行的系統版本号小于或等于事務的系統版本号），這樣可以確定事務讀取的行，要麼是事務開始前已經存在，要麼是事務自身插入或修改過的。
• 行的删除版本要麼未定義，要麼大于目前事務版本号。這可以確定事務讀取到的行，在事務開始之前未被删除。
• 符合上述兩個條件的記錄，才能傳回作為查詢結果。

注意：如果隻是執行

begin

語句實際上并沒有開啟一個事務。對資料進行了增删改查等操作後才開啟了一個事務。

事務 1	事務 2	說明
begin	begin
-	select * from test
-	insert into test(name) values(“E”)	插入表示的是系統版本号
-	commit
select * from test	能檢視E了。執行增删改查後事務才真正開始，而不是begin時就開始。是以能查到
commit

事務 1	事務 2	說明
begin	begin
-	select * from test
-	insert into test(name) values(“E”)	插入辨別的是系統版本号
select * from test	查不到新資料，事務2在事務1查詢前還沒送出
commit
commit

幻讀：

事務 1	事務 2	說明
begin	begin	假設現有資料一行A
select * from test
-	insert into test(name) values(“B”)
-	commit	2送出後1還是隻有1條
update test set name=“C”	此時1再查詢就已經2條C了
commit

如何解決幻讀：

很明顯可重複讀的隔離級别沒有辦法徹底的解決幻讀的問題，如果我們的項目中需要解決幻讀的話也有兩個辦法：

• 使用串行化讀的隔離級别
• MVCC+next-key locks：next-key locks由record locks(索引加鎖) 和 gap locks(間隙鎖，每次鎖住的不光是需要使用的資料，還會鎖住這些資料附近的資料)

實際上很多的項目中是不會使用到上面的兩種方法的，串行化讀的性能太差，而且其實幻讀很多時候是我們完全可以接受的。

INSERT

insert時将目前的事務版本号指派給建立版本号字段。

insert into testmvcc values(1,“test”);

我覺得是隐藏列裡的事務版本号變化，隐藏列裡并沒有create version和delete這一說，是以我覺得網上很多說法并不正确，即上圖也不正确，但好了解，實際上事務id隻是表現為建立版本号和删除版本号，本質都是隻有一個事務id，細節一會挨個講吧

UPDATE

在更新操作的時候，采用的是先标記舊的那行記錄為已删除，并且删除版本号是事務版本号，然後插入一行新的記錄的方式。

比如，針對上面那行記錄，事務Id為2 要把name字段更新

update table set name= ‘new_value’ where id=1;

修改的時候會先delete後insert。

delete後把行記錄裡頭資訊的删除位置為1，然後把版本号寫入事務id位置（我覺得細節這麼說也不完全正确，下節再說）。

然後再建立一個行，事務id為目前事務id，删除位為空，復原指針指向剛才删除的那行

DELETE

删除操作的時候，就把删除版本号位填入目前事務版本号。比如

delete from table where id=1;

前面說了delete會把行記錄裡頭資訊的删除位置為1，但我又在想，如果删除後復原呢？怎麼還原到原來的事務号？是以我覺得delete操作也會建立一行然後把原來的行标記一下。

MVCC總結

1. 一般我們認為MVCC有下面幾個特點：
   • 每行資料都存在一個版本，每次資料更新時都更新該版本
   • 修改時Copy出目前版本, 然後随意修改，各個事務之間無幹擾
   • 儲存時比較版本号，如果成功(commit)，則覆寫原記錄, 失敗則放棄copy(rollback)
   • 就是每行都有版本号，儲存時根據版本号決定是否成功，聽起來含有樂觀鎖的味道, 因為這看起來正是，在送出的時候才能知道到底能否送出成功
2. 而InnoDB實作MVCC的方式是:
   • 事務以排他鎖的形式修改原始資料
   • 把修改前的資料存放于undo log，通過復原指針與主資料關聯
   • 修改成功（commit）啥都不做，失敗則恢複undo log中的資料（rollback）
3. 二者最本質的差別是: 當修改資料時是否要

   排他鎖定

   ，如果鎖定了還算不算是MVCC？

• Innodb的實作真算不上MVCC, 因為并沒有實作核心的多版本共存,

  undo log

  中的内容隻是串行化的結果, 記錄了多個事務的過程, 不屬于多版本共存。但理想的MVCC是難以實作的, 當事務僅修改一行記錄使用理想的MVCC模式是沒有問題的, 可以通過比較版本号進行復原, 但當事務影響到多行資料時, 理想的MVCC就無能為力了。
• 比如, 如果事務A執行理想的MVCC, 修改Row1成功, 而修改Row2失敗, 此時需要復原Row1, 但因為Row1沒有被鎖定, 其資料可能又被事務B所修改, 如果此時復原Row1的内容，則會破壞事務B的修改結果，導緻事務B違反ACID。 這也正是所謂的

  第一類更新丢失

  的情況。
• 也正是因為InnoDB使用的MVCC中結合了排他鎖, 不是純的MVCC, 是以第一類更新丢失是不會出現了, 一般說更新丢失都是指第二類丢失更新。

鎖

鎖機制：

解決因資源共享 而造成的并發問題。

示例：買最後一件衣服X

A: X 買 ： X加鎖 ->試衣服…下單…付款…打包 ->X解鎖

B: X 買 ： 發現X已被加鎖，等待X解鎖， X已售空

分類：
根據操作類型分類：
	a.讀鎖（共享鎖）： 對同一個資料（衣服），多個讀操作可以同時進行，互不幹擾。
	b.寫鎖（互斥鎖）： 如果目前寫操作沒有完畢（買衣服的一系列操作），則無法進行其他的讀操作、寫操作

操作範圍：
	a.表鎖 ：一次性對一張表整體加鎖。如MyISAM存儲引擎使用表鎖，開銷小、加鎖快；無死鎖；但鎖的範圍大，容易發生鎖沖突、并發度低。
	b.行鎖 ：一次性對一條資料加鎖。如InnoDB存儲引擎使用行鎖，開銷大，加鎖慢；容易出現死鎖；鎖的範圍較小，不易發生鎖沖突，并發度高（很小機率 發生高并發問題：髒讀、幻讀、不可重複度、丢失更新等問題）。
	c.頁鎖	
    
    MyISAM不支援事務，InnoDB支援事務
           

加鎖解鎖文法：

-- 增加鎖：
lock table 表1  read/ write  ,表2  read/ write   ,...
unlock tables;

-- 檢視加鎖的表：
show open tables ; #InUse=0代表沒有鎖
           

按鎖定的對象的不同，一般可以分為表鎖定和行鎖定，前者對整個表進行鎖定，而後者對表中特定行進行鎖定。

從并發事務鎖定的關系上看，可以分為共享鎖定和獨占鎖定。共享鎖定會防止獨占鎖定，但允許其他的共享鎖定。而獨占鎖定既防止其他的獨占鎖定也防止其他的共享鎖定。

為了更改資料，資料庫必須在進行更改的行上施加行獨占鎖定，INSERT、 UPDATE、 DELETE和 SELECT FOR UPDATE語句都會隐式采用必要的行鎖定。下面我們介紹一下 Oracle資料庫常用的5種鎖定。

• 行共享鎖定：一般通過SELECT FOR UPDATE語句隐式獲得行共享鎖定，在 Oracle中使用者也可以通過 LOCK TABLE IN ROW SHARE MODE語句顯式獲得行共享鎖定。行共享鎖定并不防止對資料行進行更改的操作，但是可以防止其他會話擷取獨占性資料表鎖定。允許進行多個并發的行共享和行獨占性鎖定，還允許進行資料表的共享或者采用共享行獨占鎖定
• 行獨占鎖定：通過一條 INSERT、 UPDATE或 DELETE語句隐式擷取，或者通過條LOCK TABLE IN ROW EXCLUSMVE MODE語句顯式擷取。這個鎖定可以防止其他會話擷取一個共享鎖定、共享行獨占鎖定或獨占鎖定。
• 表共享鎖定：通過LOCK TABLE IN SHARE MODE語句顯式獲得。這種鎖定可以防止其他會話擷取行獨占鎖定（ INSERT、 UPDATE或 DELETB），或者防止其他表共享行獨占鎖定或表獨占鎖定，它允許在表中擁有多個行共享和表共享鎖定。該鎖定可以讓會話具有對表事務級一緻性通路，因為其他會話在使用者送出或者復原該事務并釋放對該表的鎖定之前不能更改這個被鎖定的表。
• 表共享行獨占：通過 LOCK TABLE IN SHARE ROW EXCLUSTVE MODE語句顯式獲得。這種鎖定可以防止其他會話擷取一個表共享、行獨占或者表獨占鎖定，它允許其他行共享鎖定。這種鎖定類似于表共享鎖定，隻是一次隻能對一個表放置一個表共享行獨占鎖定。如果A會話擁有該鎖定，則B會話可以執行 SELECT FOR UPDATE操作，但如果B會話試圖更新選擇的行，則需要等待
• 表獨占：通過LOCK TABLE IN EXCLUSIVE MODE顯式獲得。這個鎖定防止其他會話對該表的任何其他鎖定。

(1)表鎖(MyISAM)

MySQL的預設鎖:

MyISAM在執行【查詢】語句（SELECT）前，會自動給涉及的所有表加讀鎖，

在執行【增删改】操作（DML）前，會自動給涉及的表加寫鎖。

是以對MyISAM表進行操作，會有以下情況：

• a、對MyISAM表的讀操作（加讀鎖），不會阻塞其他程序（會話）對同一表的讀請求，

  但會阻塞對同一表的寫請求。隻有當讀鎖釋放後，才會執行其它程序的寫操作。

• b、對MyISAM表的寫操作（加寫鎖），會阻塞其他程序（會話）對同一表的讀和寫操作，

  隻有當寫鎖釋放後，才會執行其它程序的讀寫操作。

create table tablelock(
	id int primary key auto_increment , 
	name varchar(20)
)engine myisam;#預設InnoDB

表鎖定隻用于防止其它用戶端進行不正當地讀取和寫入
MyISAM 支援表鎖，InnoDB 支援行鎖
-- 鎖定
    LOCK TABLES tbl_name [AS alias]
-- 解鎖
    UNLOCK TABLES

會話：session :每一個通路資料的dos指令行、資料庫用戶端工具  都是一個會話
           

myISAM讀取時會對需要讀到的所有表加共享鎖，寫入時對表加排它鎖。

1.1 表鎖讀鎖（共享鎖）

讀鎖不限制任何對話讀，但限制任何對話寫

讀鎖，鎖表的目前會話隻能讀該表，不能寫該表，而且不能讀/寫其他表。其他會話可以讀寫任何表，但是寫該表的時候會等價鎖表的會話解鎖後才繼續執行。

• – 如果某一個會話 對A表加了read鎖，則 該會話 可以對A表進行讀操作、不能進行寫操作； 且 該會話不能對其他表進行讀、寫操作。

  – 即如果給A表加了讀鎖，則目前會話隻能對A表進行讀操作。

• 其他會話：a.可以對其他表（A表以外的表）進行讀、寫操作

  b.對A表：讀-可以； 寫-需要等待釋放鎖。

lock table tablelock read

會話1	會話2	會話3	說明
lock table tablelock read;
select * from tablelock; – 讀(查)，可以
delete from tablelock where id =1 ; – 寫(增删改)，不可以
select * from emp ; – 不可以讀其他表 # 意思是先把讀鎖去掉再幹别的事
delete from emp where eid = 1; – 不可以寫其他表
select * from tablelock; – 讀（查），可以	select * from emp ; – 讀（查），可以
delete from tablelock where id =1 ; – 寫，會“等待”會話0将鎖釋放再執行	delete from emp where eno = 1; – 寫，可以

1.2 表鎖寫鎖(排他鎖)

lock table table1 write

鎖寫鎖的會話隻能寫該表，不能增删改查其他表。

其他會話可以任意增删改查，但涉及該表的指令會等待原來會話釋放。

分析表鎖定：

檢視哪些表加了鎖：   show open tables ;  In_use=1代表被加了鎖

可以通過檢查table_locks_waited和table_locks_immediate狀态變量來分析系統上的表鎖定；
show status like 'table%';#分析表鎖定的嚴重程度

	Table_locks_immediate :産生表級鎖定的次數，立刻能擷取到的鎖數，每立即鎖值加1
	Table_locks_waited：需要等待的表鎖數(如果該值越大，說明存在越大的鎖競争)，每等待一次鎖值加1
	一般建議：
		Table_locks_immediate/Table_locks_waited > 5000， 建議采用InnoDB引擎，否則MyISAM引擎

差別是一個是鎖定的次數，一個是等待的次數
           

（2）行鎖（InnoDB）

create table test(
    id int(5) primary key auto_increment,#主鍵，有索引
    name varchar(20)
)engine=innodb ;


-- mysql預設自動commit;	oracle預設不會自動commit ;

為了研究行鎖，暫時将自動commit關閉:  
set autocommit =0 ; -- 以後記得恢複，不然永久關閉自動送出了
以後需要通過commit
           

InnoDB的行鎖是針對索引加的鎖，不是針對記錄加的鎖。并且該索引不能失效，否則都會從行鎖更新為表鎖。

一寫一讀、相同行：不阻塞，拿到舊資料

事務1	事務2	說明
BEGIN;	BEGIN;
update test set name=‘A’ where id=1;
select * from test where id=1;–	2查到舊資料，且沒有被1阻塞
select * from test where id=1; – name=A
commit
commit;

兩寫、相同行：後寫的阻塞

事務1	事務2	說明
BEGIN;
update test set name=‘C’ where id=1;	id=1被事務1加寫鎖了
select * from test where id=1;-- name=2
update linelock set name=‘D’ where id=1;-- 阻塞到會話0 commit
commit;	放行

兩寫：相同行，也阻塞，即使是新插入資料

事務1	事務2	說明
BEGIN;	BEGIN
insert into test values(null,‘E’);	此時自增吓一跳是3
update test set name=‘f’ where id = 3; 阻塞
commit;	放行
rollback;

兩寫、不同行：不會阻塞

事務1	事務2	說明
BEGIN;
update linelock set name=‘2’ where id=1;
select * from linelock where id=1;-- name=2	update linelock set name=‘4’ where id=2;-- 不阻塞不報錯

對行鎖情況：
	1.如果會話x對某條資料a進行 DML操作（研究時：關閉了自動commit的情況下），則其他會話必須等待會話x結束事務(commit/rollback)後  才能對資料a進行操作。
	2.表鎖 是通過unlock tables，也可以通過事務解鎖 ; 而行鎖 是通過事務解鎖。
	
	
行鎖的注意事項：
a.如果沒有索引，則行鎖會轉為表鎖
show index from linelock ;
alter table linelock add index idx_linelock_name(name);
           

無索引行鎖更新為表鎖，類型轉換會使索引失效，進而轉成行鎖

varchar必須加單引号，否則是重罪。不加引号會引起類型轉換，造成索引失效，轉為表鎖

兩個會話，id和name都有索引，那麼操作不同行，一個where id=一個where name=''，不會互相阻塞

兩個會話，id和name都有索引，那麼操作不同行，一個where id=一個where name=，其中name不加引号
name那個因為類型轉換是以可以執行成功，但是因為做了類型轉換，那一列索引失效。
此時會話0還沒送出，拿id查的那個會話就阻塞了，因為索引失效就轉化為表鎖了
           

類型轉換造成失效例子：

操作不是索引的列，或索引類 發生了類型轉換，則索引失效。 是以 此次操作，會從行鎖 轉為表鎖。

事務1	事務2	說明
begin;	begin;
select * from test ;	select * from test ;
update test set name = ‘AA’ where name = ‘A’ ;
update test set name = ‘BB’ where name = ‘B’ ; 阻塞
commit	放行

b.行鎖的一種特殊情況：間隙鎖：值在範圍内，但卻不存在，也會同樣加鎖，其他會話插入不了id=7
 -- 此時linelock表中 沒有id=7的資料
 update linelock set name ='x' where id >1 and id<9 ;   -- 即在此where範圍中，沒有id=7的資料，則id=7的資料成為間隙。
間隙：Mysql會自動給間隙加鎖 ->加的鎖稱為間隙鎖。即 本題 會自動給id=7的資料加 間隙鎖（行鎖）。
行鎖：如果有where，則實際加索的範圍 就是where後面的範圍（不是實際的值）

此時另外一個會話要在id=7插入資料，不能插入，需要先在原來update的會話中commit，然後第二個會話就能插入成功了。
           

有了上面的模拟操作，結果和理論又驚奇的一緻，似乎可以放心大膽的實戰。。。。。。但現實真的很殘酷。

現實：當執行批量修改資料腳本的時候，行鎖更新為表鎖。其他對訂單的操作都處于等待中，，，

原因：==InnoDB隻有在通過索引條件檢索資料時使用行級鎖，否則使用表鎖！==而模拟操作正是通過id去作為檢索條件，而id又是MySQL自動建立的唯一索引，是以才忽略了行鎖變表鎖的情況。

是以：給需要作為查詢條件的字段添加索引。用完後可以删掉。

總結：InnoDB的行鎖是針對索引加的鎖，不是針對記錄加的鎖。并且該索引不能失效，否則都會從行鎖更新為表鎖。

從上面的案例看出，行鎖變表鎖似乎是一個坑，可MySQL沒有這麼無聊給你挖坑。這是因為MySQL有自己的執行計劃。

當你需要更新一張較大表的大部分甚至全表的資料時。而你又傻乎乎地用索引作為檢索條件。一不小心開啟了行鎖(沒毛病啊！保證資料的一緻性！)。可MySQL卻認為大量對一張表使用行鎖，會導緻事務執行效率低，進而可能造成其他事務長時間鎖等待和更多的鎖沖突問題，性能嚴重下降。是以MySQL會将行鎖更新為表鎖，即實際上并沒有使用索引。

我們仔細想想也能了解，既然整張表的大部分資料都要更新資料，在一行一行地加鎖效率則更低。其實我們可以通過explain指令檢視MySQL的執行計劃，你會發現key為null。表明MySQL實際上并沒有使用索引，行鎖更新為表鎖也和上面的結論一緻。

行鎖分析：

行鎖分析：
  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 ：	等待次數。從系統啟到現在一共等待的次數
           

加鎖的方式：自動加鎖。對于UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及資料集加排他鎖；對于普通SELECT語句，InnoDB不會加任何鎖；當然我們也可以顯示的加鎖：

共享鎖：select * from tableName where … + lock in share more

排他鎖：select * from tableName where … + for update

間隙鎖

https://blog.csdn.net/andyxm/article/details/44810417

當id值為1,3,4,5,6…

當我們用範圍條件檢索資料，并請求共享或排他鎖時，InnoDB會給符合條件的已有資料記錄的索引項加鎖；對于鍵值在條件範圍内但并不存在的記錄，叫做”間隙(GAP)”。InnoDB也會對這個”間隙”加鎖，這種鎖機制就是所謂的間隙鎖(Next-Key鎖)。

# 此時沒有id=2哪一行，
    # 會話0進行
    update linelock set name='3' where id>1 and id<6;
    或
    select ... for update;# 即使沒有id=2，也會鎖住鍵值在條件範圍内的

    # 會話1：
    insert into linelock values(2,'22');-- 阻塞了，等到會話0 commit才執行

    甯可錯殺，不可放過，這個範圍的行都鎖了，即使沒有這個行
           

間隙鎖的主要作用是為了防止出現幻讀，但是它會把鎖定範圍擴大，有時候也會給我們帶來麻煩，我們就遇到了。 在資料庫參數中， 控制間隙鎖的參數是：

innodb_locks_unsafe_for_binlog

， 這個參數預設值是OFF， 也就是啟用間隙鎖， 他是一個bool值， 當值為true時表示disable間隙鎖。那為了防止間隙鎖是不是直接将

innodb_locaks_unsafe_for_binlog

設定為true就可以了呢？ 不一定！而且這個參數會影響到主從複制及災難恢複， 這個方法還尚待商量。

間隙鎖的出現主要集中在同一個事務中先delete 後 insert的情況下， 當我們通過一個參數去删除一條記錄的時候，

• 如果參數在資料庫中存在， 那麼這個時候産生的是普通行鎖， 鎖住這個記錄， 然後删除， 然後釋放鎖。
• 如果這條記錄不存在，問題就來了， 資料庫會掃描索引，發現這個記錄不存在， 這個時候的delete語句擷取到的就是一個間隙鎖，然後資料庫會向左掃描掃到第一個比給定參數小的值， 向右掃描掃描到第一個比給定參數大的值， 然後以此為界，建構一個區間， 鎖住整個區間内的資料， 一個特别容易出現死鎖的間隙鎖誕生了。

舉個例子：
表task_queue
Id           taskId
1              2
3              9
10            20
40            41

開啟一個會話： session 1
sql> set autocommit=0;##取消自動送出
sql> delete from task_queue where taskId = 20;
sql> insert into task_queue values(20, 20);

在開啟一個會話： session 2
sql> set autocommit=0; ##取消自動送出
sql> delete from task_queue where taskId = 25;
sql> insert into task_queue values(30, 25);

在沒有并發，或是極少并發的情況下， 這樣會可能會正常執行，在Mysql中，事務最終都是穿行執行， 但是在高并發的情況下， 執行的順序就極有可能發生改變， 變成下面這個樣子：
sql> delete from task_queue where taskId = 20;
sql> delete from task_queue where taskId = 25;
sql> insert into task_queue values(20, 20);
sql> insert into task_queue values(30, 25)

這個時候最後一條語句：insert into task_queue values(30, 25); 執行時就會爆出死鎖錯誤。因為删除taskId = 20這條記錄的時候，20 --  41 都被鎖住了， 他們都取得了這一個資料段的共享鎖， 是以在擷取這個資料段的排它鎖時出現死鎖。

這種問題的解決辦法：前面說了， 通過修改innodb_locaks_unsafe_for_binlog參數來取消間隙鎖進而達到避免這種情況的死鎖的方式尚待商量， 那就隻有修改代碼邏輯， 存在才删除，盡量不去删除不存在的記錄。
           

• 可重複讀 隔離級别：存在間隙鎖，可以鎖住(2,5)這個間隙，防止其他事務插入資料！
• 讀已送出 隔離級别：不存在間隙鎖，其他事務是可以插入資料！

比如我們執行下面的語句

update test set color = 'blue' where color = 'red'; 
           

展現到聚簇索引上為：

讀已送出時，會先走聚簇索引，進行全部掃描。加鎖如下：

但在實際中，MySQL做了優化，在MySQL Server過濾條件，發現不滿足後，會調用unlock_row方法，把不滿足條件的記錄放鎖。

讀已送出下，實際加鎖如下

然而，在可重複讀隔離級别下，走聚簇索引，進行全部掃描，最後會将整個表鎖上，如下所示

如何具體鎖定某一行：

select for update;
一讀一寫，寫阻塞
           

樂觀鎖

樂觀鎖不是資料庫自帶的，需要我們自己去實作。樂觀鎖是指操作資料庫時(更新操作)，想法很樂觀，認為這次的操作不會導緻沖突，在操作資料時，并不進行任何其他的特殊處理（也就是不加鎖），而在進行更新後，再去判斷是否有沖突了。

通常實作是這樣的：在表中的資料進行操作時(更新)，先給資料表加一個版本(version)字段，每操作一次，将那條記錄的版本号加1。也就是先查詢出那條記錄，擷取出version字段,如果要對那條記錄進行操作(更新),則先判斷此刻version的值是否與剛剛查詢出來時的version的值相等，如果相等，則說明這段期間，沒有其他程式對其進行操作，則可以執行更新，将version字段的值加1；如果更新時發現此刻的version值與剛剛擷取出來的version的值不相等，則說明這段期間已經有其他程式對其進行操作了，則不進行更新操作。

樂觀鎖的概念中其實已經闡述了它的具體實作細節。主要就是兩個步驟：沖突檢測和資料更新。其實作方式有一種比較典型的就是CAS(Compare and Swap)。

CAS是項樂觀鎖技術，當多個線程嘗試使用CAS同時更新同一個變量時，隻有其中一個線程能更新變量的值，而其它線程都失敗，失敗的線程并不會被挂起，而是被告知這次競争中失敗，并可以再次嘗試。

// 查詢出商品庫存資訊
select number from items where id=1; # number=3
#修改庫存為2
update items set number=2 where id=1 and number =3;
           

以上，我們在更新之前，先查詢一下庫存表中目前庫存數（quantity），然後在做update的時候，以庫存數作為一個修改條件。當我們送出更新的時候，判斷資料庫表對應記錄的目前庫存數與第一次取出來的庫存數進行比對，如果資料庫表目前庫存數與第一次取出來的庫存數相等，則予以更新，否則認為是過期資料。

以上更新語句存在一個比較重要的問題，即傳說中的ABA問題。

比如說一個線程one從資料庫中取出庫存數3，這時候另一個線程two也從資料庫中取出庫存數3，并且two進行了一些操作變成了2，然後two又将庫存數變成3，這時候線程one進行CAS操作發現資料庫中仍然是3，然後one操作成功。盡管線程one的CAS操作成功，但是不代表這個過程就是沒有問題的。

有一個比較好的辦法可以解決ABA問題，那就是通過一個單獨的可以順序遞增的version字段。改為以下方式即可：

下單操作包括3步驟：
1.查詢出商品資訊

select (status,status,version) from t_goods where id=#{id}

2.根據商品資訊生成訂單
3.修改商品status為2

update t_goods 
set status=2,version=version+1
where id=#{id} and version=#{version};
           

樂觀鎖每次在執行資料的修改操作時，都會帶上一個版本号(或時間戳)，一旦版本号和資料的版本号一緻就可以執行修改操作并對版本号執行+1操作，否則就執行失敗。因為每次操作的版本号都會随之增加，是以不會出現ABA問題，因為版本号隻會增加不會減少。

以上SQL其實還是有一定的問題的，就是一旦遇上高并發的時候，就隻有一個線程可以修改成功，那麼就會存在大量的失敗。

有一條比較好的建議，可以減小樂觀鎖力度，最大程度的提升吞吐率，提高并發能力！如下：

# 超賣
update item set number=number-1
where id=1 and number-1 > 0  # 自減之前要確定還有庫存  # 因為對該行記錄加了些鎖，是以相當于是線程同步的
           

以上SQL語句中，如果使用者下單數為1，則通過quantity - 1 > 0的方式進行樂觀鎖控制。

以上update語句，在執行過程中，會在一次原子操作中自己查詢一遍quantity的值，并将其扣減掉1。

悲觀鎖

與樂觀鎖相對應的就是悲觀鎖了。悲觀鎖就是在操作資料時，認為此操作會出現資料沖突，是以在進行每次操作時都要通過擷取鎖才能進行對相同資料的操作，這點跟java中的synchronized很相似，是以悲觀鎖需要耗費較多的時間。另外與樂觀鎖相對應的，悲觀鎖是由資料庫自己實作了的，要用的時候，我們直接調用資料庫的相關語句就可以了。

說到這裡，由悲觀鎖涉及到的另外兩個鎖概念就出來了，它們就是共享鎖與排它鎖。共享鎖和排它鎖是悲觀鎖的不同的實作，它倆都屬于悲觀鎖的範疇。

悲觀鎖的實作，往往依靠資料庫提供的鎖機制。在資料庫中，悲觀鎖的流程如下：

1. 在對記錄進行修改前，先嘗試為該記錄加上排他鎖（exclusive locking）。
2. 如果加鎖失敗，說明該記錄正在被修改，那麼目前查詢可能要等待或者抛出異常。具體響應方式由開發者根據實際需要決定。
3. 如果成功加鎖，那麼就可以對記錄做修改，事務完成後就會解鎖了。
4. 期間如果有其他對該記錄做修改或加排他鎖的操作，都會等待我們解鎖或直接抛出異常。

在樂觀鎖與悲觀鎖的選擇上面，主要看下兩者的差別以及适用場景就可以了。

1. 樂觀鎖并未真正加鎖，效率高。一旦鎖的粒度掌握不好，更新失敗的機率就會比較高，容易發生業務失敗。
2. 悲觀鎖依賴資料庫鎖，效率低。更新失敗的機率比較低。

排他鎖-寫鎖X

排它鎖（Exclusive），又稱為X 鎖，寫鎖。

查詢該表會鎖住該表，禁止其他事務讀寫

排他鎖S鎖，也稱寫鎖，獨占鎖，目前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖。

A先查了指定表，B再查該表就阻塞，直到A送出後才執行。

如果事務T對資料A加上排他鎖後，則其他事務不能再對A加任任何類型的封鎖。獲準排他鎖的事務既能讀資料，又能修改資料。

在查詢語句後面增加

...FOR UPDATE;

，Mysql會對查詢結果中的每行都加排他鎖，當沒有其他線程對查詢結果集中的任何一行使用排他鎖時，可以成功申請排他鎖，否則會被阻塞。

事務 1	事務 2	說明
begin	-
select * from test where id=1 for update	-
-	select * from test where id=1	沒問題
-	select * from test where id=1 for update	2阻塞
update test set name=‘F’ where id=1;	-
commit;	-
-	放行出來結果

共享鎖-讀鎖S

共享鎖（Shared），又稱為S 鎖，讀鎖。

查詢該表不會造成鎖表

讀鎖多用于判斷資料是否存在，多個讀操作可以同時進行而不會互相影響。當如果事務對讀鎖進行修改操作，很可能會造成死鎖。

讀鎖隻有一種使用方式：

SELECT ... LOCK IN SHARE MODE;

在事務當中，讀操作SELECT不需要加鎖，而是讀取undo日志中的最新快照。其中undo日志為用來實作事務復原，本身沒有帶來額外的開銷

如果事務T對資料A加上共享鎖後，則其他事務隻能對A再加共享鎖，不能加排他鎖。獲準共享鎖的事務隻能讀資料，不能修改資料。

在查詢語句後面增加，Mysql會對查詢結果中的每行都加共享鎖，當沒有其他線程對查詢結果集中的任何一行使用排他鎖時，可以成功申請共享鎖，否則會被阻塞。其他線程也可以讀取使用了共享鎖的表，而且這些線程讀取的是同一個版本的資料。

共享鎖和排它鎖是悲觀鎖的不同的實作，它倆都屬于悲觀鎖的範疇。

比如，我這裡通過mysql打開兩個查詢編輯器，在其中開啟一個事務，并不執行commit語句

city表DDL如下：

CREATE TABLE `city` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`state` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

 

begin;
SELECT * from city where id = "1"    lock in share mode;
           

然後在另一個查詢視窗中，對id為1的資料進行更新

update  city set name="666" where id ="1";
           

此時，操作界面進入了卡頓狀态，過幾秒後，也提示錯誤資訊

[SQL]update  city set name="666" where id ="1";
[Err] 1205 - Lock wait timeout exceeded; try restarting transaction
           

那麼證明，對于id=1的記錄加鎖成功了，在上一條記錄還沒有commit之前，這條id=1的記錄被鎖住了，隻有在上一個事務釋放掉鎖後才能進行操作，或用共享鎖才能對此資料進行操作。

再實驗一下：

update city set name="666" where id ="1" lock in share mode;
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'lock in share mode' at line 1
           

加上共享鎖後，也提示錯誤資訊了，通過查詢資料才知道，對于update,insert,delete語句會自動加排它鎖的原因

于是，我又試了試

SELECT * from city where id = "1" lock in share mode;