MySQL事務與鎖

事務處理

事務處理是數(shù)據(jù)庫中的一個大塊頭瘦陈，涉及到數(shù)據(jù)的完整性與一致性問題特纤，由于mysql存在多種數(shù)據(jù)存儲引擎提供給用戶選擇秩命，但不是所有的引擎都支持事務處理尉共，常見的引擎有：MyISAM和InnoDB，MyISAM是默認高速的引擎并不支持事務功能弃锐，InnoDB支持行鎖定和事務處理袄友，速度比MyISAM稍慢。事實上前面我們在創(chuàng)建表時都指明存儲引擎為InnoDB霹菊，本篇中我們也將采用InnoDB引擎進行分析剧蚣，畢竟InnoDB是支持事務功能的支竹。

事務的概念

先看一個經(jīng)典銀行轉賬案例，A向B的銀行卡轉賬1000元鸠按，這里分兩個主要事件礼搁，一個是A向B轉賬1000，那么A的銀行卡轉賬成功后必須在原來的數(shù)額上扣掉1000元目尖，另一個是B收到了A的轉款馒吴，B的銀行卡上數(shù)額必須增加1000元，這兩個步驟是必須都成功才算轉賬成功瑟曲，總不能A轉賬B后饮戳，A的數(shù)額沒有變化而B增加了1000元吧？這樣銀行不得虧死了洞拨？因此兩個步驟只要有一個失敗扯罐，此次轉賬的結果就是失敗。但我們在執(zhí)行sql語句時烦衣，兩個動作是分兩個語句執(zhí)行的歹河，萬一執(zhí)行完一個突然沒電了另外一個沒有執(zhí)行，那豈不出問題了琉挖？此時就需要事務來解決這個問題了，所謂的事物就是保證以上的兩個步驟在同一個環(huán)境中執(zhí)行涣脚，只要其中一個失敗示辈，事務就會撤銷之前的操作，回滾的沒轉賬前的狀態(tài)遣蚀，如果兩個都執(zhí)行成功矾麻，那么事務就認為轉成成功了。這就是事務的作用芭梯。

對事務有了初步理解后险耀，進一步了解事務的官方概念，事務是DBMS的執(zhí)行單位玖喘。它由有限個數(shù)據(jù)庫操作語句組成甩牺。但不是任意的數(shù)據(jù)庫操作序列都能成為事務。一般來說累奈，事務是必須滿足4個條件（ACID）

原子性（Autmic）：一個原子事務要么完整執(zhí)行贬派，要么干脆不執(zhí)行。也就是說澎媒，工作單元中的每項任務都必須正確執(zhí)行搞乏，如果有任一任務執(zhí)行失敗，則整個事務就會被終止并且此前對數(shù)據(jù)所作的任何修改都將被撤銷戒努。如果所有任務都被成功執(zhí)行请敦，事務就會被提交，那么對數(shù)據(jù)所作的修改將會是永久性的

一致性（Consistency）：一致性代表了底層數(shù)據(jù)存儲的完整性。它是由事務系統(tǒng)和應用開發(fā)人員共同來保證侍筛。事務系統(tǒng)通過保證事務的原子性萤皂，隔離性和持久性來滿足這一要求; 應用開發(fā)人員則需要保證數(shù)據(jù)庫有適當?shù)募s束(主鍵，引用完整性等)勾笆，并且工作單元中所實現(xiàn)的業(yè)務邏輯不會導致數(shù)據(jù)的不一致(數(shù)據(jù)預期所表達的現(xiàn)實業(yè)務情況不相一致)敌蚜。例如，在剛才的AB轉賬過程中窝爪，從A賬戶中扣除的金額必須與B賬戶中存入的金額相等弛车。

隔離性（Isolation）：隔離性是指事務必須在不干擾其他事務的前提下獨立執(zhí)行，也就是說蒲每，在事務執(zhí)行完畢之前纷跛，其所訪問的數(shù)據(jù)不能受系統(tǒng)其他部分的影響。

持久性（Durability）：持久性指明當系統(tǒng)或介質發(fā)生故障時邀杏，確保已提交事務的更新數(shù)據(jù)不能丟失贫奠，也就意味著一旦事務提交，DBMS保證它對數(shù)據(jù)庫中數(shù)據(jù)的改變應該是永久性的望蜡，耐得住任何系統(tǒng)故障唤崭，持久性可以通過數(shù)據(jù)庫備份和恢復來保證。

事務控制流程實戰(zhàn)

在使用事務處理可能涉及到以下命令：

-- 聲明事務的開始
BEGIN(或START TRANSACTION);

-- 提交整個事務
COMMIT;

-- 回滾到事務初始狀態(tài)
ROLLBACK;

下面通過刪除user表中的用戶數(shù)據(jù)脖律，然后再回滾來演示上述命令的作用：

-- 先查看user表中的數(shù)據(jù)
mysql> select * from user;
+----+-----------+--------------+---------------------+------+--------------------+
| id | name      | pinyin       | birth               | sex  | address            |
+----+-----------+--------------+---------------------+------+--------------------+
|  1 | 王五      | wangwu       | NULL                | 1    | 北京市朝陽區(qū)       |
|  2 | 張曹宇    | zhangcaoyu   | 1990-08-05 00:00:00 | 1    | 廣東省汕頭市       |
|  3 | 李達康    | lidakang     | 1909-12-12 00:00:00 | 1    | 北京朝陽           |
| 10 | 張書記    | zhangshuji   | 1999-06-06 00:00:00 | 0    | 北京市朝陽區(qū)       |
| 16 | 任在明    | renzaiming   | 1996-12-01 00:00:00 | 1    | 廣東省廣州市       |
| 22 | 陳小明    | chenxiaoming | 1995-05-10 00:00:00 | 0    | 廣東省深圳市       |
| 24 | 任傳海    | renchuanhai  | 1992-03-08 00:00:00 | 1    | 海南三亞           |
+----+-----------+--------------+---------------------+------+--------------------+
7 rows in set (0.00 sec);

-- 開始事務
mysql> begin;
Query OK, 0 rows affected (0.00 sec);
-- 刪除ID為24的用戶
mysql> delete from user where id =24;
Query OK, 1 row affected (0.00 sec);

-- 刪除完成后再次查看user表數(shù)據(jù)谢肾，顯然ID為24的數(shù)據(jù)已被刪除
mysql> select * from user;
+----+-----------+--------------+---------------------+------+--------------------+
| id | name      | pinyin       | birth               | sex  | address            |
+----+-----------+--------------+---------------------+------+--------------------+
|  1 | 王五      | wangwu       | NULL                | 1    | 北京市朝陽區(qū)       |
|  2 | 張曹宇    | zhangcaoyu   | 1990-08-05 00:00:00 | 1    | 廣東省汕頭市       |
|  3 | 李達康    | lidakang     | 1909-12-12 00:00:00 | 1    | 北京朝陽           |
| 10 | 張書記    | zhangshuji   | 1999-06-06 00:00:00 | 0    | 北京市朝陽區(qū)       |
| 16 | 任在明    | renzaiming   | 1996-12-01 00:00:00 | 1    | 廣東省廣州市       |
| 22 | 陳小明    | chenxiaoming | 1995-05-10 00:00:00 | 0    | 廣東省深圳市       |
+----+-----------+--------------+---------------------+------+--------------------+
6 rows in set (0.00 sec);

-- 執(zhí)行回滾操作rollback
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

-- 再次查看數(shù)據(jù)，可見ID為24的用戶數(shù)據(jù)已恢復
mysql> select * from user;
+----+-----------+--------------+---------------------+------+--------------------+
| id | name      | pinyin       | birth               | sex  | address            |
+----+-----------+--------------+---------------------+------+--------------------+
|  1 | 王五      | wangwu       | NULL                | 1    | 北京市朝陽區(qū)       |
|  2 | 張曹宇    | zhangcaoyu   | 1990-08-05 00:00:00 | 1    | 廣東省汕頭市       |
|  3 | 李達康    | lidakang     | 1909-12-12 00:00:00 | 1    | 北京朝陽           |
| 10 | 張書記    | zhangshuji   | 1999-06-06 00:00:00 | 0    | 北京市朝陽區(qū)       |
| 16 | 任在明    | renzaiming   | 1996-12-01 00:00:00 | 1    | 廣東省廣州市       |
| 22 | 陳小明    | chenxiaoming | 1995-05-10 00:00:00 | 0    | 廣東省深圳市       |
| 24 | 任傳海    | renchuanhai  | 1992-03-08 00:00:00 | 1    | 海南三亞           |
+----+-----------+--------------+---------------------+------+--------------------+
7 rows in set (0.00 sec)

從上述一系列操作中小泉，從啟動事務到刪除用戶數(shù)據(jù)芦疏，再到回滾數(shù)據(jù)，體現(xiàn)了事務控制的過程微姊，這里我們還沒使用COMMIT酸茴，如果剛才把rollback改成commit，那么事務就提交了兢交，數(shù)據(jù)也就真的刪除了薪捍。下面我們再次來演示刪除數(shù)據(jù)的過程，并且這次使用commit提交事務.

-- 先添加一條要刪除數(shù)據(jù)
mysql> insert into user values(30,'要被刪除的數(shù)據(jù)',null,null,1,null);
Query OK, 1 row affected (0.01 sec);
-- 查看數(shù)據(jù)
mysql> select * from user;
+----+-----------------------+--------------+---------------------+------+--------------------+
| id | name                  | pinyin       | birth               | sex  | address            |
+----+-----------------------+--------------+---------------------+------+--------------------+
|  1 | 王五                  | wangwu       | NULL                | 1    | 北京市朝陽區(qū)       |
|  2 | 張曹宇                | zhangcaoyu   | 1990-08-05 00:00:00 | 1    | 廣東省汕頭市       |
|  3 | 李達康                | lidakang     | 1909-12-12 00:00:00 | 1    | 北京朝陽           |
| 10 | 張書記                | zhangshuji   | 1999-06-06 00:00:00 | 0    | 北京市朝陽區(qū)       |
| 16 | 任在明                | renzaiming   | 1996-12-01 00:00:00 | 1    | 廣東省廣州市       |
| 22 | 陳小明                | chenxiaoming | 1995-05-10 00:00:00 | 0    | 廣東省深圳市       |
| 24 | 任傳海                | renchuanhai  | 1992-03-08 00:00:00 | 1    | 海南三亞           |
| 30 | 要被刪除的數(shù)據(jù)        | NULL         | NULL                | 1    | NULL               |
+----+-----------------------+--------------+---------------------+------+--------------------+
8 rows in set (0.00 sec);

-- 開啟新事務
mysql> begin;
Query OK, 1 row affected (0.00 sec);
-- 刪除數(shù)據(jù)
mysql> delete from user where id =30;
Query OK, 1 row affected (0.00 sec);
-- 提交事務
mysql> commit;
Query OK, 0 rows affected (0.00 sec);
-- 回滾數(shù)據(jù)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec);
-- 查看數(shù)據(jù)
mysql> select * from user;
+----+-----------+--------------+---------------------+------+--------------------+
| id | name      | pinyin       | birth               | sex  | address            |
+----+-----------+--------------+---------------------+------+--------------------+
|  1 | 王五      | wangwu       | NULL                | 1    | 北京市朝陽區(qū)       |
|  2 | 張曹宇    | zhangcaoyu   | 1990-08-05 00:00:00 | 1    | 廣東省汕頭市       |
|  3 | 李達康    | lidakang     | 1909-12-12 00:00:00 | 1    | 北京朝陽           |
| 10 | 張書記    | zhangshuji   | 1999-06-06 00:00:00 | 0    | 北京市朝陽區(qū)       |
| 16 | 任在明    | renzaiming   | 1996-12-01 00:00:00 | 1    | 廣東省廣州市       |
| 22 | 陳小明    | chenxiaoming | 1995-05-10 00:00:00 | 0    | 廣東省深圳市       |
| 24 | 任傳海    | renchuanhai  | 1992-03-08 00:00:00 | 1    | 海南三亞           |
+----+-----------+--------------+---------------------+------+--------------------+
7 rows in set (0.00 sec)

可以發(fā)現(xiàn)當刪除完數(shù)據(jù)后配喳，使用commit提交了事務飘诗，此時數(shù)據(jù)就會被真正更新到數(shù)據(jù)庫了，即使使用rollback回滾也是沒有辦法恢復數(shù)據(jù)的界逛。ok~昆稿，這就是事務控制最簡化的流程，事實上除了上述的回滾到事務的初始狀態(tài)外息拜，還可以進行部分回滾溉潭，也就是我們可以自己控制事務發(fā)生錯誤時回滾到某個點净响，這需要利用以下命令來執(zhí)行：

- 定義保存點（回滾點）
SAVEPOINT savepoint_name（名稱）;

--回滾到指定保存點
ROLLBACK TO SAVEPOINT savepoint_name（名稱）;

演示案例如下：

-- 開啟事務
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into user values(31,'保存點1',null,null,1,null);
Query OK, 1 row affected (0.00 sec);
-- 創(chuàng)建保存點
mysql> savepoint sp;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into user values(32,'保存點2',null,null,1,null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into user values(33,'保存點3',null,null,1,null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into user values(34,'保存點4',null,null,1,null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----+------------+--------------+---------------------+------+--------------------+
| id | name       | pinyin       | birth               | sex  | address            |
+----+------------+--------------+---------------------+------+--------------------+
|  1 | 王五       | wangwu       | NULL                | 1    | 北京市朝陽區(qū)       |
|  2 | 張曹宇     | zhangcaoyu   | 1990-08-05 00:00:00 | 1    | 廣東省汕頭市       |
|  3 | 李達康     | lidakang     | 1909-12-12 00:00:00 | 1    | 北京朝陽           |
| 10 | 張書記     | zhangshuji   | 1999-06-06 00:00:00 | 0    | 北京市朝陽區(qū)       |
| 16 | 任在明     | renzaiming   | 1996-12-01 00:00:00 | 1    | 廣東省廣州市       |
| 22 | 陳小明     | chenxiaoming | 1995-05-10 00:00:00 | 0    | 廣東省深圳市       |
| 24 | 任傳海     | renchuanhai  | 1992-03-08 00:00:00 | 1    | 海南三亞           |
| 31 | 保存點1    | NULL         | NULL                | 1    | NULL               |
| 32 | 保存點2    | NULL         | NULL                | 1    | NULL               |
| 33 | 保存點3    | NULL         | NULL                | 1    | NULL               |
| 34 | 保存點4    | NULL         | NULL                | 1    | NULL               |
+----+------------+--------------+---------------------+------+--------------------+
11 rows in set (0.00 sec);
-- 回滾到保存點
mysql> rollback to savepoint sp;
Query OK, 0 rows affected (0.00 sec);
-- 查看數(shù)據(jù)
mysql> select * from user;
+----+------------+--------------+---------------------+------+--------------------+
| id | name       | pinyin       | birth               | sex  | address            |
+----+------------+--------------+---------------------+------+--------------------+
|  1 | 王五       | wangwu       | NULL                | 1    | 北京市朝陽區(qū)       |
|  2 | 張曹宇     | zhangcaoyu   | 1990-08-05 00:00:00 | 1    | 廣東省汕頭市       |
|  3 | 李達康     | lidakang     | 1909-12-12 00:00:00 | 1    | 北京朝陽           |
| 10 | 張書記     | zhangshuji   | 1999-06-06 00:00:00 | 0    | 北京市朝陽區(qū)       |
| 16 | 任在明     | renzaiming   | 1996-12-01 00:00:00 | 1    | 廣東省廣州市       |
| 22 | 陳小明     | chenxiaoming | 1995-05-10 00:00:00 | 0    | 廣東省深圳市       |
| 24 | 任傳海     | renchuanhai  | 1992-03-08 00:00:00 | 1    | 海南三亞           |
| 31 | 保存點1    | NULL         | NULL                | 1    | NULL               |
+----+------------+--------------+---------------------+------+--------------------+
8 rows in set (0.00 sec);
-- 提交事務
mysql> commit ;

關于commit有點需要知道的，在mysql中每條sql命令都會被自動commit喳瓣，這種功能稱為自動提交功能馋贤，是默認開啟的。前面我們在執(zhí)行事務使用了begin命令開啟了事務畏陕，這時自動提交在事務中就關閉了直到事務被手動commit配乓。當然我們也可以手動控制開啟或者關閉此功能，語法如下：

- 關閉自動提交功能
SET AUTOCOMMIT=0;
-- 開啟自動提交功能
SET AUTOCOMMIT=1;

事務隔離級別（分離水平）

在并發(fā)事務處理帶來的問題中惠毁，更新丟失通常應該是完全避免的犹芹。但防止更新丟失，并不能單靠數(shù)據(jù)庫事務控制器來解決鞠绰，需要應用程序對要更新的數(shù)據(jù)加必要的鎖來解決腰埂，因此，防止更新丟失應該是應用的責任蜈膨。
臟讀屿笼、不可重復讀和幻讀，其實都是數(shù)據(jù)庫讀一致性問題翁巍，必須由數(shù)據(jù)庫提供一定的事務隔離機制來解決驴一。數(shù)據(jù)庫實現(xiàn)事務隔離的方式，基本可以分為以下兩種灶壶。

一種是在讀取數(shù)據(jù)前肝断，對其加鎖，阻止其他事務對數(shù)據(jù)進行修改例朱。
另一種是不用加任何鎖孝情，通過一定機制生成一個數(shù)據(jù)請求時間點的一致性數(shù)據(jù)快照（Snapshot）鱼蝉，并用這個快照來提供一定級別（語句級或事務級）的一致性讀取洒嗤。從用戶的角度，好像是數(shù)據(jù)庫可以提供同一數(shù)據(jù)的多個版本魁亦，因此渔隶，這種技術叫做數(shù)據(jù)多版本并發(fā)控制（ＭultiVersion Concurrency Control，簡稱MVCC或MCC）洁奈，也經(jīng)常稱為多版本數(shù)據(jù)庫间唉。
數(shù)據(jù)庫的事務隔離級別越嚴格，并發(fā)副作用越小利术，但付出的代價也就越大呈野，因為事務隔離實質上就是使事務在一定程度上“串行化”進行，這顯然與“并發(fā)”是矛盾的印叁，同時被冒，不同的應用對讀一致性和事務隔離程度的要求也是不同的军掂，比如許多應用對“不可重復讀”和“幻讀”并不敏感，可能更關心數(shù)據(jù)并發(fā)訪問的能力昨悼。
為了解決“隔離”與“并發(fā)”的矛盾蝗锥，ISO/ANSI SQL92定義了４個事務隔離級別，每個級別的隔離程度不同率触，允許出現(xiàn)的副作用也不同终议，應用可以根據(jù)自己業(yè)務邏輯要求，通過選擇不同的隔離級別來平衡＂隔離＂與＂并發(fā)＂的矛盾
事務４種隔離級別比較

隔離級別/讀數(shù)據(jù)一致性及允許的并發(fā)副作用	讀數(shù)據(jù)一致性	臟讀	不可重復讀	幻讀
未提交讀（Read uncommitted）	最低級別葱蝗，只能保證不讀取物理上損壞的數(shù)據(jù)	是	是	是
已提交度（Read committed）	語句級	否	是	是
可重復讀（Repeatable read）	事務級	否	否	是
可序列化（Serializable）	最高級別穴张，事務級	否	否	否

四種分離水平(隔離級別)

READ_UNCOMMITTED：這是事務最低的分離水平(隔離級別)，它充許別外一個事務可以看到這個事務未提交的數(shù)據(jù)垒玲，會出現(xiàn)臟讀陆馁、不可重復讀、幻讀（分離水平最低合愈，并發(fā)性能高）

READ_COMMITTED：保證一個事務修改的數(shù)據(jù)提交后才能被另外一個事務讀取叮贩。另外一個事務不能讀取該事務未提交的數(shù)據(jù)》鹞觯可以避免臟讀益老，但會出現(xiàn)不可重復讀、幻讀問題（鎖定正在讀取的行寸莫，mysql默認隔離級別）

REPEATABLE_READ：可以防止臟讀捺萌、不可重復讀，但會出幻讀（鎖定所讀取的所有行）

SERIALIZABLE：這是花費最高代價但是最可靠的事務分離水平(隔離級別)膘茎，事務被處理為順序執(zhí)行桃纯。保證所有的情況不會發(fā)生（鎖表,并發(fā)性及其低）

讀未提交、不可重復讀披坏，幻讀

讀未提交态坦，也稱臟讀，臟讀發(fā)生在一個事務讀取了另一個事務改寫但尚未提交的數(shù)據(jù)時棒拂。如果改寫在稍后被回滾了伞梯，那么第一個事務獲取的數(shù)據(jù)就是無效的。

不可重復讀：不可重復讀發(fā)生在一個事務執(zhí)行相同的查詢兩次或兩次以上帚屉，但是每次都得到不同的數(shù)據(jù)時谜诫。這通常是因為另一個并發(fā)事務在兩次查詢期間進行了更新。請注意攻旦，不可重復讀重點是修改數(shù)據(jù)導致的(修改數(shù)據(jù)時排他讀)喻旷，例如：在事務1中，客戶管理人員在讀取了張曹宇的生日為1990-08-05,操作并沒有完成

select birth from user where name ='張曹宇' ;

在事務2中牢屋，這時張曹宇自己修改生日為1990-06-05,并提交了事務.

begin;
-- 其他操作省略
update user set birth='1990-06-05' where  name ='張曹宇' ;
commit;

在事務1中且预，客戶管理人員再次讀取了張曹宇的生日時牺陶，生日變?yōu)?990-06-05,從而導致在一個事務中前后兩次讀取的結果并不一致，導致了不可重復讀辣之。

幻讀：幻讀與不可重復讀類似掰伸。它發(fā)生在一個事務（T1）讀取了幾行數(shù)據(jù)，接著另一個并發(fā)事務（T2）插入了一些數(shù)據(jù)時怀估。在隨后的查詢中狮鸭，第一個事務（T1）就會發(fā)現(xiàn)多了一些原本不存在的記錄。請注意多搀，幻讀重點是插入或者刪除數(shù)據(jù)導致的（對滿足條件的數(shù)據(jù)行集進行鎖定）歧蕉，同樣的道理，在事務1中康铭，客戶管理查詢所有用戶生日在1990-06-05的人只有20個惯退，操作并沒有完成，此時事務2中从藤，剛好有一個新注冊的用戶催跪，其生日也1990-06-05，在事務2中插入新用戶并提交了事務夷野，此時在事務1中再次查詢時懊蒸，所有用戶生日在1990-06-05的人變?yōu)?1個了，從也就導致了幻讀悯搔。

在理解了讀未提交骑丸、不可重復的、幻讀后妒貌，再次看回表格通危，小結一下，可以發(fā)現(xiàn)在分離水平為READ UNCOMMITTED時灌曙，將會導致3種情況的出現(xiàn)菊碟，因此這樣的分離水平一般是不建議使用的。在分離水平為READ COMMITTED時平匈，不會導致臟讀框沟，但會導致不可重復讀和幻讀藏古，要回避這樣的現(xiàn)象增炭，必須采用分離水平為REPEATABLE READ，這樣就只會導致幻讀拧晕，而當分離水平為SERIALIZABLE時隙姿，3種現(xiàn)象都不復存在。但請注意這并不意味著所有情況下采用分離水平為SERIALIZABLE都是合理的坝咐，就如前面所分析的分離水平越高析命，數(shù)據(jù)的完整性也就越高，但同時運行性下降腹缩。在大多數(shù)情況下欲鹏，我們會在根據(jù)應用的實際情景選擇分離水平為REPEATABLE READ或者READ COMMITTED(MySQL默認的事務分離水平為REPEATABLE READ)机久，這樣既能一定程度上保證數(shù)據(jù)的完整性也同時提供了數(shù)據(jù)的同時運行性，在mysql中我們可以使用以下語法設置事務分離水平

-- 設置當前連接的事務分離水平
SET SESSION TRANSACTION ISOLATION LEVEL 事務分離水平;

--設置全部連接（包括新連接）的事務分離水平
SET GLOBAL TRANSACTION ISOLATION LEVEL 事務分離水平;

事務原理概要

最后我們來簡單了解一下事務內部實現(xiàn)的原理概要赔嚎，事實上事務的處理機制是通過記錄更新日志而實現(xiàn)的膘盖，其中與事務處理相關的日志是UNDO日志和REDO日志。

UNDO日志亦稱為回滾端尤误，在進行數(shù)據(jù)插入侠畔、更新、刪除的情景下损晤，保存變更前的數(shù)據(jù)软棺，原理圖如下：

image.png

在表中保存了指向UNDO日志的指針，rollback執(zhí)行時根據(jù)這個指針來獲取舊數(shù)據(jù)并覆蓋到表中尤勋，rollback執(zhí)行完成后或者commit后UNDO日志將被刪除喘落。UNDO還有另外一種作用，當A用戶正在更新數(shù)據(jù)時最冰，還沒提交揖盘，而B用戶也需要使用該數(shù)據(jù)，這時不可能讓B讀取未提交的數(shù)據(jù)锌奴，因此會將存在UNDO表中的數(shù)據(jù)提供給B用戶兽狭。這就是事務回滾的簡單模型。

REDO日志主要是事務提交后由于錯誤或者斷電停機等原因使數(shù)據(jù)無法更新到數(shù)據(jù)庫中時鹿蜀，REDO日志將提供數(shù)據(jù)恢復作用箕慧。其原理是通過數(shù)據(jù)庫中的一段緩沖的數(shù)據(jù)先實時更新到REDO日志再更新到數(shù)據(jù)庫，也就是說平常的更新操作并非一步執(zhí)行到位的茴恰，而是首選更新到REDO日志中颠焦，再更新到數(shù)據(jù)庫文件的。所以REDO日志才能用戶故障數(shù)據(jù)的恢復往枣。

MySQL中的鎖（表鎖伐庭、行鎖）

鎖是計算機協(xié)調多個進程或純線程并發(fā)訪問某一資源的機制。在數(shù)據(jù)庫中分冈，除傳統(tǒng)的計算資源（CPU圾另、RAM、I/O）的爭用以外雕沉，數(shù)據(jù)也是一種供許多用戶共享的資源集乔。如何保證數(shù)據(jù)并發(fā)訪問的一致性、有效性是所在有數(shù)據(jù)庫必須解決的一個問題坡椒，鎖沖突也是影響數(shù)據(jù)庫并發(fā)訪問性能的一個重要因素扰路。從這個角度來說尤溜，鎖對數(shù)據(jù)庫而言顯得尤其重要，也更加復雜汗唱。
相對其他數(shù)據(jù)庫而言宫莱，MySQL的鎖機制比較簡單，其最顯著的特點是不同的存儲引擎支持不同的鎖機制哩罪。
MySQL大致可歸納為以下3種鎖：

表級鎖：開銷小梢睛，加鎖快；不會出現(xiàn)死鎖识椰；鎖定粒度大绝葡，發(fā)生鎖沖突的概率最高，并發(fā)度最低腹鹉。

行級鎖：開銷大藏畅，加鎖慢；會出現(xiàn)死鎖功咒；鎖定粒度最小愉阎，發(fā)生鎖沖突的概率最低，并發(fā)度也最高力奋。

頁面鎖：開銷和加鎖時間界于表鎖和行鎖之間榜旦；會出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間景殷，并發(fā)度一般

悲觀鎖和樂觀鎖的概念

悲觀鎖：假設會發(fā)生并發(fā)沖突溅呢，回避一切可能違反數(shù)據(jù)完整性的操作。

樂觀鎖：假設不會發(fā)生并發(fā)沖突猿挚，只在提交操作時檢查是否違反數(shù)據(jù)完整性咐旧，注意樂觀鎖并不能解決臟讀的問題(關于臟讀稍后解析)。

在一般情況下绩蜻，悲觀鎖依靠數(shù)據(jù)庫的鎖機制實現(xiàn)铣墨，以保證操作最大程度的排他性和獨占性，因而會導致數(shù)據(jù)庫性能的大量開銷和并發(fā)性很低办绝，特別是對長事務而言伊约，這種開銷往往過于巨大而無法承受。為了解決這樣的問題孕蝉，樂觀鎖機制便出現(xiàn)了屡律。樂觀鎖，大多情況下是基于數(shù)據(jù)版本（ Version ）記錄機制實現(xiàn)昔驱。何謂數(shù)據(jù)版本疹尾？即為數(shù)據(jù)增加一個版本標識上忍，在基于數(shù)據(jù)庫表的版本解決方案中骤肛，一般是通過為數(shù)據(jù)庫表增加一個version 字段來實現(xiàn)纳本。讀取出數(shù)據(jù)時，將此版本號一同讀出腋颠，之后更新時繁成，對此版本號加一。此時淑玫，將提交數(shù)據(jù)的版本數(shù)據(jù)與數(shù)據(jù)庫表對應記錄的當前版本信息進行比對巾腕，如果提交的數(shù)據(jù)版本號大于數(shù)據(jù)庫表當前版本號，則給予更新絮蒿，否則認為是過期數(shù)據(jù)尊搬。

mysql中的共享鎖與排他鎖

在mysql中，為了保證數(shù)據(jù)一致性和防止數(shù)據(jù)處理沖突土涝，引入了加鎖和解鎖的技術佛寿，這樣可以使數(shù)據(jù)庫中特定的數(shù)據(jù)在使用時不讓其他用戶(進程或事務)操作而為該數(shù)據(jù)加鎖，直到該數(shù)據(jù)被處理完成后再進行解鎖但壮。根據(jù)使用目的不同把鎖分為共享鎖定(也稱為讀取鎖定)和排他鎖定(寫入鎖定)冀泻。InnoDB行鎖的實現(xiàn)就是依靠共享鎖和排他鎖。

共享鎖（s）：允許一個事務去讀一行蜡饵，將對象數(shù)據(jù)變?yōu)橹蛔x形式的鎖定弹渔，這樣就允許多方同時讀取一個數(shù)據(jù)，此時數(shù)據(jù)將無法修改（阻止其他事務獲得相同數(shù)據(jù)集的排他鎖）溯祸。
排他鎖（Ｘ）：允許獲取排他鎖的事務更新(insert/update/delete)數(shù)據(jù)肢专，阻止其他事務取得相同的數(shù)據(jù)集共享讀鎖和排他寫鎖。

以上兩種鎖都屬于悲觀鎖的應用焦辅，還有一點鸟召，根據(jù)鎖定粒度的不同，可分為行鎖定(共享鎖和排他鎖使用應用的就是行鎖定)氨鹏，表鎖定欧募，數(shù)據(jù)庫鎖定，可見粒度的不同將影響用戶(進程或事務)對數(shù)據(jù)操作的并發(fā)性仆抵，目前mysql支持行鎖定和表鎖定跟继。

另外，為了允許行鎖和表鎖共存镣丑，實現(xiàn)多粒度鎖機制舔糖，InnoDB還有兩種內部使用的意向鎖（Intention Locks），這兩種意向鎖都是表鎖莺匠。

意向共享鎖（IS）：事務打算給數(shù)據(jù)行共享鎖金吗，事務在給一個數(shù)據(jù)行加共享鎖前必須先取得該表的IS鎖。
意向排他鎖（IX）：事務打算給數(shù)據(jù)行加排他鎖，事務在給一個數(shù)據(jù)行加排他鎖前必須先取得該表的IX鎖摇庙。

InnoDB行鎖模式兼容性列表

當前鎖模式/是否兼容/請求鎖模式	X	IX	S	IS
X	沖突	沖突	沖突	沖突
IX	沖突	兼容	沖突	兼容
S	沖突	沖突	兼容	兼容
IS	沖突	兼容	兼容	兼容

如果一個事務請求的鎖模式與當前的鎖兼容旱物，InnoDB就請求的鎖授予該事務；反之卫袒，如果兩者兩者不兼容宵呛，該事務就要等待鎖釋放。
意向鎖是InnoDB自動加的夕凝，不需用戶干預宝穗。對于UPDATE、DELETE和INSERT語句码秉，InnoDB會自動給涉及及數(shù)據(jù)集加排他鎖（Ｘ）逮矛；而普通SELECT語句，InnoDB不會任何鎖转砖；事務可以通過以下語句顯示給記錄集加共享鎖或排鎖橱鹏。

-- 共享鎖（S）
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE;

-- 排他鎖（X)
SELECT * FROM table_name WHERE ... FOR UPDATE;

用SELECT .. IN SHARE MODE獲得共享鎖，主要用在需要數(shù)據(jù)依存關系時確認某行記錄是否存在堪藐，并確保沒有人對這個記錄進行UPDATE或者DELETE操作莉兰。但是如果當前事務(獲得共享鎖事務)也需要對該記錄進行更新操作，則很有可能造成死鎖礁竞，對于鎖定行記錄后需要進行更新操作的應用糖荒，應該使用SELECT ... FOR UPDATE方式獲取排他鎖。

InnoDB行鎖實現(xiàn)方式
InnoDB行鎖是通過索引上的索引項來實現(xiàn)的模捂，這一點ＭySQL與Oracle不同捶朵，后者是通過在數(shù)據(jù)中對相應數(shù)據(jù)行加鎖來實現(xiàn)的。InnoDB這種行鎖實現(xiàn)特點意味者：只有通過索引條件檢索數(shù)據(jù)狂男，InnoDB才會使用行級鎖综看，否則，InnoDB將使用表鎖岖食！
在實際應用中红碑，要特別注意InnoDB行鎖的這一特性，不然的話泡垃，可能導致大量的鎖沖突析珊，從而影響并發(fā)性能。

什么時候使用表鎖
對于InnoDB表蔑穴，在絕大部分情況下都應該使用行級鎖忠寻，因為事務和行鎖往往是我們之所以選擇InnoDB表的理由。但在特殊事務中存和，也可以考慮使用表級鎖奕剃。

第一種情況是：事務需要更新大部分或全部數(shù)據(jù)衷旅，表又比較大，如果使用默認的行鎖纵朋，不僅這個事務執(zhí)行效率低柿顶，而且可能造成其他事務長時間鎖等待和鎖沖突，這種情況下可以考慮使用表鎖來提高該事務的執(zhí)行速度倡蝙。
第二種情況是：事務涉及多個表九串，比較復雜绞佩，很可能引起死鎖寺鸥，造成大量事務回滾。這種情況也可以考慮一次性鎖定事務涉及的表品山，從而避免死鎖胆建、減少數(shù)據(jù)庫因事務回滾帶來的開銷。

當然肘交，應用中這兩種事務不能太多笆载，否則，就應該考慮使用ＭyISAＭ表涯呻。

例如凉驻，如果需要寫表t1并從表t讀，可以按如下做：

SET AUTOCOMMIT=0;
LOCAK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and here];
COMMIT;
UNLOCK TABLES;

鎖釋放時機

image.png

在事務執(zhí)行過程中复罐，如果有加鎖操作涝登，這個鎖需要等事務提交或者回滾時釋放。

時間線	事務1 (T1)	事務2(T2)
t1	BEGIN;	BEGIN;
t2	UPDATE lockdemo SET state = '666' WHERE id = 2;
t3		UPDATE lockdemo SET state = '22' WHERE id = 2;
t..	SELECT * FROM lockdemo;	SELECT * FROM lockdemo;
t..	commit;	commit;

事務1在t2時刻先執(zhí)行更新操作效诅，它就會一直持有id=2的鎖直到commit胀滚；事務2在t3時刻獲取會失敗。

BEGIN;
UPDATE lockdemo SET state = '666' WHERE id = 2;
SELECT * FROM lockdemo;
COMMIT;

BEGIN;
UPDATE lockdemo SET state = '22' WHERE id = 2;
SELECT * FROM lockdemo;
COMMIT;

死鎖

死鎖是指兩個或多個事務在同一個資源上相互占用,并請求鎖定對方占用的資源,從而導致惡性循環(huán)的現(xiàn)象.當多個事務試圖以不同的順序鎖定資源時,就可能會產(chǎn)生死鎖,多個事務同時鎖定同一個資源時,也會產(chǎn)生死鎖乱投。
例如:
事務1:

start transaction;
update stock_price set close = 45.50 where stock_id = 4 and date = '2017-4-26';
update stock_price set close = 19.80 where stock_id = 3 and date = '2017-4-27';
commit;

事務2:

start transaction;
update stock_price set high = 20.10 where stock_id = 3 and date = '2017-4-27';
update stock_price set high = 47.20 where stock_id = 4 and date = '2017-4-26';
commit;

如果湊巧,兩個事務均執(zhí)行了第一條update語句,同時鎖定了該資源,當嘗試執(zhí)行第二條update語句的時候,去發(fā)現(xiàn)資源已經(jīng)被鎖定,兩個事務都等待對方釋放鎖,則陷入死循環(huán),形成死鎖咽笼。
為了解決這種問題,數(shù)據(jù)庫系統(tǒng)實現(xiàn)了各種死鎖檢測和死鎖超時機制.比如InnoDB存儲引擎目前的處理方法是將持有最少級排他鎖的事務進行回滾.

問題

這些問題我還沒有時間證明
1.是不是所有的sql操作都需要事務？（我個人理解從事務的定義上來說不需要）
2.是不是只有在事務里才可能用到行鎖和表鎖（我個人理解不是戚炫，理由是事務的定義）

參考書籍

https://blog.csdn.net/javazejian/article/details/69857949
https://www.cnblogs.com/chenqionghe/p/4845693.html

最后編輯于：2019.01.18 11:11:58

?著作權歸作者所有,轉載或內容合作請聯(lián)系作者

人面猴
序言：七十年代末剑刑，一起剝皮案震驚了整個濱河市，隨后出現(xiàn)的幾起案子双肤，更是在濱河造成了極大的恐慌叛甫，老刑警劉巖，帶你破解...
沈念sama閱讀 221,198評論 6贊 514
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件杨伙，死亡現(xiàn)場離奇詭異其监，居然都是意外死亡，警方通過查閱死者的電腦和手機限匣，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 94,334評論 3贊 398
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進店門抖苦，熙熙樓的掌柜王于貴愁眉苦臉地迎上來毁菱，“玉大人，你說我怎么就攤上這事锌历≈樱” “怎么了？”我有些...
開封第一講書人閱讀 167,643評論 0贊 360
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵究西，是天一觀的道長窗慎。經(jīng)常有香客問我，道長卤材，這世上最難降的妖魔是什么遮斥？我笑而不...
開封第一講書人閱讀 59,495評論 1贊 296
?港島之戀（遺憾婚禮）
正文為了忘掉前任，我火速辦了婚禮扇丛，結果婚禮上术吗，老公的妹妹穿的比我還像新娘。我一直安慰自己帆精，他們只是感情好较屿，可當我...
茶點故事閱讀 68,502評論 6贊 397
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布。她就那樣靜靜地躺著卓练，像睡著了一般隘蝎。火紅的嫁衣襯著肌膚如雪。梳的紋絲不亂的頭發(fā)上襟企，一...
開封第一講書人閱讀 52,156評論 1贊 308
城市分裂傳說
那天嘱么，我揣著相機與錄音，去河邊找鬼整吆。笑死拱撵，一個胖子當著我的面吹牛，可吹牛的內容都是我干的表蝙。我是一名探鬼主播拴测，決...
沈念sama閱讀 40,743評論 3贊 421
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼，長吁一口氣：“原來是場噩夢啊……” “哼府蛇！你這毒婦竟也來了集索？” 一聲冷哼從身側響起，我...
開封第一講書人閱讀 39,659評論 0贊 276
萬榮殺人案實錄
序言：老撾萬榮一對情侶失蹤汇跨，失蹤者是張志新（化名）和其女友劉穎务荆，沒想到半個月后，有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體穷遂，經(jīng)...
沈念sama閱讀 46,200評論 1贊 319
?護林員之死
正文獨居荒郊野嶺守林人離奇死亡函匕，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內容為張勛視角年9月15日...
茶點故事閱讀 38,282評論 3贊 340
?白月光啟示錄
正文我和宋清朗相戀三年，在試婚紗的時候發(fā)現(xiàn)自己被綠了蚪黑。大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片盅惜。...
茶點故事閱讀 40,424評論 1贊 352
活死人
序言：一個原本活蹦亂跳的男人離奇死亡中剩，死狀恐怖，靈堂內的尸體忽然破棺而出抒寂，到底是詐尸還是另有隱情结啼，我是刑警寧澤，帶...
沈念sama閱讀 36,107評論 5贊 349
?日本核電站爆炸內幕
正文年R本政府宣布屈芜，位于F島的核電站郊愧，受9級特大地震影響，放射性物質發(fā)生泄漏井佑。R本人自食惡果不足惜属铁，卻給世界環(huán)境...
茶點故事閱讀 41,789評論 3贊 333
男人毒藥：我在死后第九天來索命
文/蒙蒙一、第九天我趴在偏房一處隱蔽的房頂上張望毅糟。院中可真熱鬧红选，春花似錦澜公、人聲如沸姆另。這莊子的主人今日做“春日...
開封第一講書人閱讀 32,264評論 0贊 23
一樁弒父案坟乾，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽迹辐。三九已至，卻和暖如春甚侣，著一層夾襖步出監(jiān)牢的瞬間明吩，已是汗流浹背。一陣腳步聲響...
開封第一講書人閱讀 33,390評論 1贊 271
情欲美人皮
我被黑心中介騙來泰國打工殷费，沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留印荔，地道東北人。一個月前我還...
沈念sama閱讀 48,798評論 3贊 376
代替公主和親
正文我出身青樓详羡，卻偏偏與公主長得像仍律，于是被迫代替她去往敵國和親。傳聞我的和親對象是個殘疾皇子实柠，可洞房花燭夜當晚...
茶點故事閱讀 45,435評論 2贊 359