MySQL鎖的管理機制
**********************************
MySQL鎖的管理機制
**********************************
MySQL server層面的一些鎖
? table-level locking（表級鎖）
? page-level locking（頁級鎖）
? row-level locking（行級鎖）
————————————————————————————————————————————————————————————————————
一、表級鎖：直接鎖定整張表，在你鎖定期間，其它進程無法對該表進行寫操作。如果你是寫鎖，則其它進程則讀也不允許.
對MyISAM表進行表級鎖定
MyISAM表的鎖
? 讀鎖，LOCK TABLE GYJ_T1 READ，自身只讀，不能寫；其他線程仍可讀，不能寫。多個線程都可提交read lock。
? 寫鎖，LOCK TABLE GYJ_T1 [LOW_PRIORITY] WRITE ，自身可讀寫；其他線程完全不可讀寫。
? 釋放鎖，UNLOCK TABLES
? SELECT自動加讀鎖
? 其他DML、DDL自動加寫鎖
Innodb行級鎖升級表級鎖的三種情況。
1.Innodb auto-inc鎖
InnoDB處理具有auto increment字段的表的時候，會使用一種特殊的表鎖——AUTO-INC。
簡單來說就是innodb會在內存裡保存一個計數器用來記錄auto_increment的值，當插入數據時，就會用一個表鎖來鎖住這個計數器，
直到插入結束。一條一條插入問題不大，但是如果高並發插入，就會造成sql阻塞。
解決方法有兩種
A)不用auto increment字段，自己維護主鍵生成。該方法中選擇主鍵生成策略很重要, 要綜合考慮簡單和效率問題。假設使用uuid，
雖然簡單但是會造成該表的主鍵效率很低（innodb的主鍵是特殊的index，其他的index會引用主鍵，詳見mysql文檔）
B) 修改innodb_autoinc_lock_mode
innodb_autoinc_lock_mode = 0 (“traditional” lock mode：全部使用表鎖)
innodb_autoinc_lock_mode = 1 (“consecutive” lock mode：可預判行數時使用新方式，不可時使用表鎖)
innodb_autoinc_lock_mode = 2 (“interleaved” lock mode：全部使用新方式，不安全，不適合replication)
2.Innodb 全表更新、全索引更新
3.Innodb 使用SR事務隔離級別
二、頁級鎖:表級鎖速度快，但沖突多，行級沖突少，但速度慢。所以取了折衷的頁級，一次鎖定相鄰的一組記錄。
對BDB表進行頁級鎖定，BDB現在沒有了，很老的數據庫，4點幾的才有，現在從數據庫上刪除掉了
三、行級鎖:僅對指定的記錄進行加鎖，這樣其它進程還是可以對同一個表中的其它記錄進行操作。
對InnoDB表進行行級鎖定
Innodb加行鎖的方式
1.record lock（行/記錄鎖）
2.gap lock（間隙鎖）
3.next-key lock （record lock + gap lock）
InnoDB是通過給索引上的索引項加鎖來實現行鎖
InnoDB有幾種鎖：
? 共享鎖（S - LOCKING），允許一個事務去讀一行，阻止其他事務獲得相同數據集的排他鎖
? 排它鎖（X - LOCKING），允許獲得排他鎖的事務更新數據，阻止其他事務取得相同數據集的共享讀鎖和排他鎖
InnoDB還獨有的實現了2種鎖：
? 意向共享鎖（IS），事務打算給數據行加行共享鎖，事務在給一個數據行加共享鎖前必須先取得該表的IS鎖
? 意向獨占鎖（IX），事務打算給數據行加行排他鎖，事務在給一個數據行加排他鎖前必須先取得該表的IX鎖
注意：
（1）在不通過索引條件查詢的時候，InnoDB使用的是表鎖（默認地，全表所有行加鎖，和表級鎖相當，
例外條件是 RC + innodb_locks_unsafe_for_binlog 組合選項），而不是細粒度行鎖。
（2）由於MySQL的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果是使用相同的索引鍵，
是會出現鎖沖突的。
共享鎖：SELECT * FROM xx WHERE … LOCK IN SHARE MODE
加排他鎖：SELECT * FROM xx WHERE … FOR UPDATE
在5.1以前，只能通過SHOW FULL PROCESSLIST、SHOW ENGINE INOODB STATUS等命令查看鎖的狀態
在5.1之後：(使用了InnoDB plugin之後)
INFORMATION_SCHEMA：
INNODB_TRX
INNODB_LOCKS
InnoDB_LOCK_WAITS
show engine innodb mutex; #latch鎖
show engine innodb status\G; #lock鎖
INNODB_TRX
select * from information_schema.innodb_trx\G;
INNODB_LOCKS
select * from information_schema.innodb_locks\G; |
INNODB_LOCK_WAITS
select * from information_schema.innodb_lock_waits\G;
innodb_trx:
看下innodb_trx表中，幾個最常用的字段：
trx_id:InnoDB存儲引擎內部唯一的事務ID
trx_state:當前事務的狀態
trx_started:事務的開始時間。
trx_wait_started:事務等待開始的時間。
trx_mysql_thread_id:Mysql中的線程ID,SHOW PROCESSLIST顯示的結果。
trx_query：事務運行的sql語句。
innodb_locks
看下innodb_locks表中，幾個最常用的字段：
lock_id:鎖的ID。
lock_trx_id:事務ID。
lock_mode:鎖的模式。
lock_type:鎖的類型，表鎖還是行鎖。
lock_table:要加鎖的表。
lock_index:鎖的索引。
lock_space:InnoDB存儲引擎表空間的ID號。
lock_page:被鎖住的頁的數量。若是表鎖，則該值為NULL。
lock_rec:被鎖住的行的數量。若是表鎖，則該值為NULL。
lock_data:被鎖住的行的主鍵值。當是表鎖時，該值為NULL。
innodb_lock_waits
看下innodb_lock_waits表中，幾個最常用的字段：
requesting_trx_id:申請鎖資源的事務ID。
requesting_lock_id:申請的鎖的ID。
blocking_trx_id:阻塞的鎖的ID。
***************************************************************************************************************
實驗一：觀察INNODB_TRX、INNODB_LOCKS、InnoDB_LOCK_WAITS、processlist,status
**************************************************************************************************************
create table gyj_t1(id int primairy key,name varchar(10));
insert into gyj_t1 values(1,'AAAAA');
mysql> show variables like '%autocommit%';
mysql> select @@tx_isolation;
mysql> show variables like 'innodb_lock_wait_timeout';
mysql> set global innodb_lock_wait_timeout=600;
mysql> set innodb_lock_wait_timeout=600;
session 1
mysql> begin;
mysql> update gyj_t1 set name='BBBBB' where id=1;
session 2
mysql> begin;
mysql> update gyj_t1 set name='bbbbb' where id=1;
session 3
mysql> select * from information_schema.innodb_trx\G;
mysql> select * from information_schema.innodb_locks\G;
mysql> select * from information_schema.innodb_lock_waits\G;
mysql> show processlist;
mysql> show engine innodb status\G;
*********************************************
實驗二：鎖案例一，聚集索引上的鎖
**********************************************
1.默認RR隔離級別
2.自動提交
3.創建表
CREATE TABLE student
(
id int unsigned not null auto_increment,
xh int unsigned not null,
name varchar(10) not null,
bjmc varchar(20) not null,
primary key(id),
key xh(xh)
) engine =InnoDB;
3.插入兩條記錄
insert into student values (1, 1, 'guoyj', 'jsj01'), (2, 2, 'jfedu', 'jsj01');
4.場景一
set autocommit=0;
(1)session 1
select * from student where id=1 for update;
(2)session 2
select * from student where id=1; #一致性非鎖定讀，這時侯會阻塞嗎？（不會）
select * from student where id=1 lock in share mode; #這時侯會阻塞嗎？（會）
(3)session 1
commit;或 rollback;
總結：一致性非鎖定讀測試（不產生任何鎖，所以不會鎖等待）
意向排它鎖，意向共享鎖互斥測試（會發生鎖等待）
5.場景二
set autocommit=0;
(1)session 1
select * from student where name='guoyj' for update;
(2)session 2
select * from student where name='jfedu' for update; #這時侯會阻塞嗎？(會)
(3)session 1
commit;或 rollback;
總結：看表結構，name這列沒有索引，在RR隔離級別所有的記錄全部都會被鎖定，排它鎖。
6.場景三
set autocommit=0;
(1)session 1
select * from student where xh=1 and name='guoyj' for update;
(2)session 2
select * from student where xh=1 and name='jfedu' for update; #這時侯會阻塞嗎？(會)
(3)session 1
commit;或 rollback;
總結：xh是有索引的，xh=1，會話1會話2是同一行記錄，同一個索引會被鎖定的，出現沖突，發生等（name上沒有索引，范圍會擴大！）
7.場景四
那如果我把會話1的SQL，換成：select *from student where xh=2 and name='jfedu' for update;後會話2會發生鎖等待嗎？
set autocommit=0;
(1)session 1
select * from student where xh=1 and name='guoyj' for update;
(2)session 2
select * from student where xh=2 and name='jfedu' for update; #這時侯會阻塞嗎？(不會)
總結：
會話2：xh是有索引的，xh=2 會話1會話2是不同的行記錄，不是同一個索引，不會發生等待！
MySQL的行鎖是針對索引加的鎖，而不是記錄加的鎖！
由於MySQL的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果是使用相同的索引鍵，
是會出現鎖沖突的。應用設計的時侯要注意這點。
