mysql小記--基礎知識

qingyezhu發表於2016-02-17
MySql

一、事務

事務是由一組SQL語句組成的邏輯處理單元。
事務的特徵ACID，即原子性、一致性、隔離性和永續性。
原子性(Atomicity)事務作為整體執行，操作要麼全部執行、要麼全部不執行。
一致性(Consistent)事務應該確保資料庫狀態從一個一致狀態轉變為另一個一致狀態。
隔離性(Isolation)多個事務併發執行時，一個事務執行不影響其他事務執行（隔離級別可設定）。
永續性(Durable)事務提交後，對資料庫的修改應該永久儲存在資料庫中。

 

二、事務的隔離級別

1、讀未提交(Read Uncommited,RU)
這種隔離級別下，事務間完全不隔離，會產生髒讀，可以讀取未提交的記錄，實際情況下不會使用。
讀取未提交的資料，又稱為“髒讀”。
 
2、讀提交(Read commited,RC)
僅能讀取到已提交的記錄，這種隔離級別下，會存在幻讀現象，所謂幻讀是指在同一個事務中，多次執行同一個查詢，返回的記錄不完全相同的現象。幻讀產生的根本原因是，在RC隔離級別下，每條語句都會讀取已提交事務的更新，若兩次查詢之間有其他事務提交，則會導致兩次查詢結果不一致。雖然如此，讀提交隔離級別在生產環境中使用很廣泛。
 
3、可重複讀(Repeatable Read,RR)
可重複讀解決了幻讀問題。不是所有的資料庫都實現了該隔離級別。
 
4、序列化(Serializable)
在序列化隔離模式下，消除了髒讀、幻讀，但事務併發度急劇下降，事務的隔離級別與事務的併發度成反比，隔離級別越高，事務的併發度越低。實際生產環境下，dba會在併發和滿足業務需求之間做權衡，選擇合適的隔離級別。
 
 
三、關於鎖
1、MySQL常用儲存引擎的鎖機制：
 
   MyISAM和MEMORY採用表級鎖(table-level locking)
   BDB採用頁面鎖(page-level locking)或表級鎖，預設為頁面鎖
   InnoDB支援行級鎖(row-level locking)和表級鎖,預設為行級鎖
 
2、各種鎖特點：
   表級鎖：開銷小,加鎖快;不會出現死鎖;鎖定粒度大,發生鎖衝突的概率最高,併發度最低
   行級鎖：開銷大,加鎖慢;會出現死鎖;鎖定粒度最小,發生鎖衝突的概率最低,併發度也最高
   頁面鎖：開銷和加鎖時間界於表鎖和行鎖之間;會出現死鎖;鎖定粒度界於表鎖和行鎖之間,併發度一般
 
3、各種鎖的適用場景:
  表級鎖更適合於以查詢為主,只有少量按索引條件更新資料的應用,如Web應用
  行級鎖則更適合於有大量按索引條件併發更新資料,同時又有併發查詢的應用,如一些線上事務處理系統
 
4、死鎖：
  是指兩個或兩個以上的程式在執行過程中,因爭奪資源而造成的一種互相等待的現象,若無外力作用,它們都將無法推進下去。
  表級鎖不會產生死鎖.所以解決死鎖主要還是針對於最常用的InnoDB.
 
5.1 InnoDB鎖型別
    共享鎖（S）：允許事務讀一行資料
    排他鎖（X）：允許事務刪除或更新一行資料
5.2 InnoDB意向鎖（表級別）
    意向共享鎖（IS）：事務想要獲得一個表中某幾行的共享鎖
    意向排他鎖（IX）：事務想要獲得一個表中某幾行的排他鎖
5.3、對讀操作進行枷鎖
select .... lock in share mode：加S鎖
select ... for update：加X鎖
5.4、InnoDB鎖演算法
Record Lock：單個行記錄上的鎖
Gap Lock：間隙鎖，鎖定一個範圍，但不包括記錄本身
Next-Key Lock：鎖定一個範圍的記錄，並且包括記錄本身（在Repeatable Read事務級別下，使用該演算法，可避免幻讀的產生）
 
6、InnoDB鎖效能監控
 
mysql> show status like '%innodb_row_lock_%';
+-------------------------------+---------+
| Variable_name                 | Value   |
+-------------------------------+---------+
| Innodb_row_lock_current_waits | 0       |
| Innodb_row_lock_time          | 4517738 |
| Innodb_row_lock_time_avg      | 2673    |
| Innodb_row_lock_time_max      | 51386   |
| Innodb_row_lock_waits         | 1690    |
+-------------------------------+---------+
 
Innodb_row_lock_current_waits ：當前等待鎖的數量
Innodb_row_lock_time          ：系統啟動到現在鎖定的總時間長度
Innodb_row_lock_time_avg      ：每次平均鎖定的時間
Innodb_row_lock_time_max      ：最長一次鎖定時間
Innodb_row_lock_waits         ：系統啟動到現在總共鎖定次數
 
7、案例1
建立表
CREATE TABLE `class_teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `class_name` varchar(100) DEFAULT NULL,
  `teacher_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_teacher_id` (`teacher_id`)
) ENGINE=InnoDB;

插入資料
insert into class_teacher(class_name, teacher_id) values('class301', 5),('class201', 30);

根據二級索引進行更新(update或delete)，之後進行插入[資料庫隔離級別：REPEATABLE-READ]
1）、
事務0： update class_teacher set class_name='class202' where teacher_id=31;

事務1：insert into class_teacher(class_name,teacher_id) values('class229', 29);
[插入OK]
事務2：insert into class_teacher(class_name,teacher_id) values('class230', 30);
[插入WAIT]
事務3：insert into class_teacher(class_name,teacher_id) values('class231', 31);
[插入WAIT]
事務4：insert into class_teacher(class_name,teacher_id) values('class232', 32);
[插入WAIT]
事務5：insert into class_teacher(class_name,teacher_id) values('class250', 50);
[插入WAIT]

結論：
鎖定[30,無窮大)

2）、
事務0： update class_teacher set class_name='class202' where teacher_id=3;

事務1：insert into class_teacher(class_name,teacher_id) values('class210', 1);
[插入WAIT]
事務2：insert into class_teacher(class_name,teacher_id) values('class220', 2);
[插入WAIT]
事務3：insert into class_teacher(class_name,teacher_id) values('class230', 3);
[插入WAIT]
事務4：insert into class_teacher(class_name,teacher_id) values('class250', 5);
[插入OK]
事務5：insert into class_teacher(class_name,teacher_id) values('class260', 6);
[插入OK]

結論：
鎖定(無窮小,5)

3）、
事務0： update class_teacher set class_name='class202' where teacher_id=5;

事務1：insert into class_teacher(class_name,teacher_id) values('class240', 4);
[插入WAIT]
事務2：insert into class_teacher(class_name,teacher_id) values('class250', 5);
[插入WAIT]
事務3：insert into class_teacher(class_name,teacher_id) values('class260', 6);
[插入WAIT]
事務4：insert into class_teacher(class_name,teacher_id) values('class215', 15);
[插入WAIT]
事務5：insert into class_teacher(class_name,teacher_id) values('class229', 29);
[插入WAIT]
事務6：insert into class_teacher(class_name,teacher_id) values('class230', 30);
[插入OK]
事務7：insert into class_teacher(class_name,teacher_id) values('class231', 31);
[插入OK]

結論：
鎖定(無窮小,5]和[5,30)

4)、
事務0：update class_teacher set class_name='class202' where teacher_id=30;

事務1：insert into class_teacher(class_name,teacher_id) values('class240', 4);
[插入OK]
事務2：insert into class_teacher(class_name,teacher_id) values('class250', 5);
[插入WAIT]
事務3：insert into class_teacher(class_name,teacher_id) values('class260', 6);
[插入WAIT]
事務4：insert into class_teacher(class_name,teacher_id) values('class215', 15);
[插入WAIT]
事務5：insert into class_teacher(class_name,teacher_id) values('class229', 29);
[插入WAIT]
事務6：insert into class_teacher(class_name,teacher_id) values('class230', 30);
[插入WAIT]
事務7：insert into class_teacher(class_name,teacher_id) values('class231', 31);
[插入WAIT]
事務8：insert into class_teacher(class_name,teacher_id) values('class250', 50);
[插入WAIT]

結論：
鎖定(5,30]和[30,無窮大)

5）、
事務0： update class_teacher set class_name='class202' where teacher_id=29;

事務1：insert into class_teacher(class_name,teacher_id) values('class240', 4);
[插入OK]
事務2：insert into class_teacher(class_name,teacher_id) values('class250', 5);
[插入WAIT]
事務3：insert into class_teacher(class_name,teacher_id) values('class260', 6);
[插入WAIT]
事務4：insert into class_teacher(class_name,teacher_id) values('class215', 15);
[插入WAIT]
事務5：insert into class_teacher(class_name,teacher_id) values('class229', 29);
[插入WAIT]
事務6：insert into class_teacher(class_name,teacher_id) values('class230', 30);
[插入OK]
事務7：insert into class_teacher(class_name,teacher_id) values('class231', 31);
[插入OK]

結論：
鎖定[5,30)

　總結：當是根據二級索引進行更新，如update或delete時，在當前隔離級別下，其都會使用Next-Key鎖[a,b)[行鎖 + gap鎖]前閉後開的，無法進行插入。



　備註：
　Repeatable Read，當使用非索引欄位進行更新時，則會進行表鎖。
　Read Uncommitted，資料庫一般不用，且在其上的任何操作都不會加鎖。
　Read Committed，有Record鎖，沒有Next-Key鎖，即Next-Key鎖變成了Record鎖。


 




 








 


 




相關文章