Mysql數(shù)據(jù)庫(kù)中，事務(wù)是指什么？如何使用該功能？

MySQL 事務(wù)

平塘網(wǎng)站制作公司哪家好，找創(chuàng)新互聯(lián)公司！從網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)站建設(shè)、微信開(kāi)發(fā)、APP開(kāi)發(fā)、成都響應(yīng)式網(wǎng)站建設(shè)公司等網(wǎng)站項(xiàng)目制作，到程序開(kāi)發(fā)，運(yùn)營(yíng)維護(hù)。創(chuàng)新互聯(lián)公司從2013年成立到現(xiàn)在10年的時(shí)間，我們擁有了豐富的建站經(jīng)驗(yàn)和運(yùn)維經(jīng)驗(yàn)，來(lái)保證我們的工作的順利進(jìn)行。專注于網(wǎng)站建設(shè)就選創(chuàng)新互聯(lián)公司。

什么是事務(wù)？

MySQL 事務(wù)主要用于處理操作量大，復(fù)雜度高的數(shù)據(jù)。比如說(shuō)，在人員管理系統(tǒng)中，你刪除一個(gè)人員，你既需要?jiǎng)h除人員的基本資料，也要?jiǎng)h除和該人員相關(guān)的信息，如信箱，文章等等，這樣，這些數(shù)據(jù)庫(kù)操作語(yǔ)句就構(gòu)成一個(gè)事務(wù)！

在 MySQL 中只有使用了 Innodb 數(shù)據(jù)庫(kù)引擎的數(shù)據(jù)庫(kù)或表才支持事務(wù)。

事務(wù)處理可以用來(lái)維護(hù)數(shù)據(jù)庫(kù)的完整性，保證成批的 SQL 語(yǔ)句要么全部執(zhí)行，要么全部不執(zhí)行。

事務(wù)用來(lái)管理 insert,update,delete 語(yǔ)句

一般來(lái)說(shuō)，事務(wù)是必須滿足4個(gè)條件（ACID）：原子性（Atomicity，或稱不可分割性）、一致性（Consistency）、隔離性（Isolation，又稱獨(dú)立性）、持久性（Durability）。

原子性：一個(gè)事務(wù)（transaction）中的所有操作，要么全部完成，要么全部不完成，不會(huì)結(jié)束在中間某個(gè)環(huán)節(jié)。事務(wù)在執(zhí)行過(guò)程中發(fā)生錯(cuò)誤，會(huì)被回滾（Rollback）到事務(wù)開(kāi)始前的狀態(tài)，就像這個(gè)事務(wù)從來(lái)沒(méi)有執(zhí)行過(guò)一樣。

一致性：在事務(wù)開(kāi)始之前和事務(wù)結(jié)束以后，數(shù)據(jù)庫(kù)的完整性沒(méi)有被破壞。這表示寫入的資料必須完全符合所有的預(yù)設(shè)規(guī)則，這包含資料的精確度、串聯(lián)性以及后續(xù)數(shù)據(jù)庫(kù)可以自發(fā)性地完成預(yù)定的工作。

隔離性：數(shù)據(jù)庫(kù)允許多個(gè)并發(fā)事務(wù)同時(shí)對(duì)其數(shù)據(jù)進(jìn)行讀寫和修改的能力，隔離性可以防止多個(gè)事務(wù)并發(fā)執(zhí)行時(shí)由于交叉執(zhí)行而導(dǎo)致數(shù)據(jù)的不一致。事務(wù)隔離分為不同級(jí)別，包括讀未提交（Read uncommitted）、讀提交（read committed）、可重復(fù)讀（repeatable read）和串行化（Serializable）。

持久性：事務(wù)處理結(jié)束后，對(duì)數(shù)據(jù)的修改就是永久的，即便系統(tǒng)故障也不會(huì)丟失。

在 MySQL 命令行的默認(rèn)設(shè)置下，事務(wù)都是自動(dòng)提交的，即執(zhí)行 SQL 語(yǔ)句后就會(huì)馬上執(zhí)行 COMMIT 操作。因此要顯式地開(kāi)啟一個(gè)事務(wù)務(wù)須使用命令 BEGIN 或 START TRANSACTION，或者執(zhí)行命令 SET AUTOCOMMIT=0，用來(lái)禁止使用當(dāng)前會(huì)話的自動(dòng)提交。

from 樹(shù)懶學(xué)堂 - 一站式數(shù)據(jù)知識(shí)平臺(tái)

MYSQL使用基礎(chǔ)、進(jìn)階分享

MySQL是一個(gè)關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，由瑞典MySQL AB公司開(kāi)發(fā)，屬于Oracle旗下產(chǎn)品，是最流行的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)之一。

端口是3306。

表很多時(shí)，使用linux腳本，需要根據(jù)需要修改一下：

和創(chuàng)建一樣，可以加上 if exists

可兩篇文章：

如：

用于在已有的表中添加、刪除或修改列。

添加 ADD

或

默認(rèn)是添加到最后，但可以指定位置。 FIRST ：添加最前

AFTER 字段名 ：添加指定字段之后

例子：

刪除 DROP

修改 MODIFY 主要修改原列的類型或約束條件 同樣可以用 FIRST 和 AFTER 字段名 ，代表的是修改到哪里。

修改字段名 CHANGE

可以把表2的數(shù)據(jù)復(fù)制到表1中，但 不能復(fù)制約束性條件 。

單行

多行，注意 只有一個(gè)VALUES ：

不寫 (行1, 行2...) 這一部分的話，默認(rèn)一一對(duì)應(yīng)

除了以上方法外，還可以用SET為每一行附上相應(yīng)的值。

假如沒(méi)有篩選的話，就給全部都修改了?？梢杂? WHERE 篩選。

假如 沒(méi)有篩選的話，就給全部刪除了 。相當(dāng)于清空。

清空

先把表刪除，然后再建一個(gè)。與 DELETE FROM 相比， TRUNCATE 的效率更快，因?yàn)? DELETE FROM 是把記錄逐條刪除的。

查詢執(zhí)行的順序

FROM -- WHERE -- SELECT -- GROUP BY -- HAVING -- ORDER BY -- LIMIT

注意

當(dāng)數(shù)據(jù)很大，上百萬(wàn)的時(shí)候，使用LIMIT ... OFFSET ..的方式進(jìn)行分頁(yè)十分浪費(fèi)資源且耗時(shí)長(zhǎng)。最好是結(jié)合WHERE使用，如：

REGEXP 使用正則表達(dá)進(jìn)行匹配。 查詢時(shí)，需要搭配WHERE或HAVING使用 。

兩個(gè)表之間有交集且要用到兩個(gè)表的數(shù)據(jù)時(shí)，可以使用內(nèi)連接查詢。

LEFT JOIN 關(guān)鍵字從左表（table1）返回所有的行，即使右表（table2）中沒(méi)有匹配。如果右表中沒(méi)有匹配，則結(jié)果為 NULL。

用法：

RIGHT JOIN 關(guān)鍵字從右表（table2）返回所有的行，即使左表（table1）中沒(méi)有匹配。如果左表中沒(méi)有匹配，則結(jié)果為 NULL。 把LEFT JOIN的表1、表2調(diào)換順序，就是REGHT JOIN 。

FULL OUTER JOIN 關(guān)鍵字只要左表（table1）和右表（table2）其中一個(gè)表中存在匹配，則返回行. 相當(dāng)于結(jié)合了 LEFT JOIN 和 RIGHT JOIN 的結(jié)果。

但 MySQL中不支持 FULL OUTER JOIN 。

即SELECT嵌套。

IN 一個(gè)查詢結(jié)果作為另一個(gè)查詢的條件。 如：

EXISTS 用于判斷查詢子句是否有記錄，如果有一條或多條記錄存在返回 True，否則返回 False。True時(shí)執(zhí)行。 如：

索引的本質(zhì)是一種排好序的數(shù)據(jù)結(jié)構(gòu)。利用索引可以提高查詢速度。

常見(jiàn)的索引有：

MySQL通過(guò)外鍵約束來(lái)保證表與表之間的數(shù)據(jù)的完整性和準(zhǔn)確性。 外鍵的使用條件：

外鍵的好處：可以使得兩張表關(guān)聯(lián)，保證數(shù)據(jù)的一致性和實(shí)現(xiàn)一些級(jí)聯(lián)操作。

對(duì)已有的兩個(gè)表增加外鍵 比如：主表為A，子表為B，外鍵為aid，外鍵約束名字為a_fk_b

為子表添加一個(gè)字段，當(dāng)做外鍵

為子表添加外鍵約束條件

假如刪除記錄報(bào)錯(cuò)： [Err] 1451 -Cannot deleteorupdatea parent row: aforeignkeyconstraintfails (...)

這是因?yàn)镸ySQL中設(shè)置了foreign key關(guān)聯(lián)，造成無(wú)法更新或刪除數(shù)據(jù)?？梢酝ㄟ^(guò)設(shè)置 FOREIGN_KEY_CHECKS 變量來(lái)避免這種情況。 第一步：禁用外鍵約束，我們可以使用： SETFOREIGN_KEY_CHECKS=0; 第二步：刪除數(shù)據(jù) 第三步：?jiǎn)?dòng)外鍵約束，我們可以使用： SETFOREIGN_KEY_CHECKS=1; 查看當(dāng)前FOREIGN_KEY_CHECKS的值，可用如下命令： SELECT @@FOREIGN_KEY_CHECKS;

使用 UNION 來(lái)組合兩個(gè)查詢，如果第一個(gè)查詢返回 M 行，第二個(gè)查詢返回 N 行，那么組合查詢的結(jié)果一般為 M+N 行。

每個(gè)查詢必須包含相同的列、表達(dá)式和聚集函數(shù)。

默認(rèn)會(huì)去除相同行，如果需要 保留 相同行，使用 UNION ALL 。

只能包含一個(gè) ORDER BY 子句，并且必須位于語(yǔ)句的最后 。

內(nèi)置函數(shù)很多, 見(jiàn): MySQL 函數(shù)

我們一般使用 START TRANSACTION 或 BEGIN 開(kāi)啟事務(wù), COMMIT 提交事務(wù)中的命令, SAVEPOINT : 相當(dāng)于設(shè)置一個(gè)還原點(diǎn), ROLLBACK TO : 回滾到某個(gè)還原點(diǎn)下

一般的使用格式如下:

開(kāi)啟事務(wù)時(shí), 默認(rèn)加鎖

根據(jù)類型可分為共享鎖（SHARED LOCK）和排他鎖（EXCLUSIVE LOCK）或者叫讀鎖（READ LOCK）和寫鎖（WRITE LOCK）。

根據(jù)粒度劃分又分表鎖和行鎖。表鎖由數(shù)據(jù)庫(kù)服務(wù)器實(shí)現(xiàn)，行鎖由存儲(chǔ)引擎實(shí)現(xiàn)。

除此之外,我們可以顯示加鎖

加鎖時(shí), 如果沒(méi)有索引，會(huì)鎖表，如果加了索引，就會(huì)鎖行

InnoDB默認(rèn)支持行鎖，獲取鎖是分步的，并不是一次性獲取所有的鎖，因此在鎖競(jìng)爭(zhēng)的時(shí)候就會(huì)出現(xiàn)死鎖的情況

解決方法:

即ACID特性:

由于并發(fā)事務(wù)會(huì)引發(fā)上面這些問(wèn)題, 我們可以設(shè)置事務(wù)的隔離級(jí)別解決上面的問(wèn)題.

MySQL的默認(rèn)隔離級(jí)別（可重復(fù)讀）

查看當(dāng)前會(huì)話隔離級(jí)別

方式1

方式2

設(shè)置隔離級(jí)別

主從集群的示意圖如下:

主要涉及三個(gè)線程： binlog 線程、 I/O 線程和 SQL 線程。

同步流程:

由于MySQL主從集群只會(huì)從主節(jié)點(diǎn)同步到從節(jié)點(diǎn), 不會(huì)反過(guò)來(lái)同步, 所以需要讀寫分離

讀寫分離需要在業(yè)務(wù)層面實(shí)現(xiàn) , 寫數(shù)據(jù)只能在主節(jié)點(diǎn)上完成, 而讀數(shù)據(jù)可以在主節(jié)點(diǎn)或從節(jié)點(diǎn)上完成

索引是幫助MySQL高效獲取數(shù)據(jù)的排好序的數(shù)據(jù)結(jié)構(gòu)

MySQL的索引有

推薦兩個(gè)在線工具:

簡(jiǎn)單來(lái)說(shuō), B樹(shù)是在紅黑樹(shù)(一個(gè)平衡二叉樹(shù))的基礎(chǔ)上將一個(gè)節(jié)點(diǎn)存放多個(gè)值, 實(shí)現(xiàn)的, 降低了樹(shù)的高度, 每個(gè)節(jié)點(diǎn)都存放索引及對(duì)應(yīng)數(shù)據(jù)指針, 同一層的節(jié)點(diǎn)是遞增的

而B(niǎo)+樹(shù)在B樹(shù)的基礎(chǔ)上進(jìn)行優(yōu)化, 非葉子節(jié)點(diǎn)存放 子節(jié)點(diǎn)的開(kāi)始的索引, 葉子節(jié)點(diǎn)存放索引和數(shù)據(jù)的指針, 且葉子節(jié)點(diǎn)之間有雙向的指針

如下示意圖:

不同的引擎, 主鍵索引存放的數(shù)據(jù)也不一樣, 比如常見(jiàn)的 MyISAM 和 InnoDB

MyISAM 的B+樹(shù)葉子節(jié)點(diǎn)存放表數(shù)據(jù)的指針, InnoDB 的B+樹(shù)葉子節(jié)點(diǎn)存放處主鍵外的數(shù)據(jù)

其他的:

即多個(gè)列組成一個(gè)索引, 語(yǔ)法:

由于聯(lián)合索引的B+樹(shù)的結(jié)構(gòu), 根據(jù)列建立, 所以我們的查找條件也要根據(jù)索引列的順序( where column1=x, column2=y,columnN... ), 否則會(huì)全表掃描

如果你對(duì)列進(jìn)行了 （+，-，*，/，!） , 那么都將不會(huì)走索引。

OR 引起的索引失效

OR 導(dǎo)致索引是在特定情況下的，并不是所有的 OR 都是使索引失效，如果OR連接的是 同 一個(gè)字段，那么索引 不會(huì)失效 ， 反之索引失效 。

這個(gè)我相信大家都明白，模糊搜索如果你前綴也進(jìn)行模糊搜索，那么不會(huì)走索引。

這兩種用法，也將使索引失效。另 IN 會(huì)走索引，但是當(dāng)IN的取值范圍較大時(shí)會(huì)導(dǎo)致索引失效，走全表掃描, 見(jiàn): MySQL中使用IN會(huì)不會(huì)走索引

不走索引。

走索引。

所以設(shè)計(jì)表的時(shí)候, 建議不可為空, 而是將默認(rèn)值設(shè)置為 "" ( NOT NULL DEFAULT "" )

mysql的事務(wù)四個(gè)特性以及事務(wù)的四個(gè)隔離級(jí)別

分別是原子性、一致性、隔離性、持久性。

原子性是指事務(wù)包含的所有操作要么全部成功，要么全部失敗回滾，因此事務(wù)的操作如果成功就必須要完全應(yīng)用到數(shù)據(jù)庫(kù)，如果操作失敗則不能對(duì)數(shù)據(jù)庫(kù)有任何影響。

一致性是指事務(wù)必須使數(shù)據(jù)庫(kù)從一個(gè)一致性狀態(tài)變換到另一個(gè)一致性狀態(tài)，也就是說(shuō)一個(gè)事務(wù)執(zhí)行之前和執(zhí)行之后都必須處于一致性狀態(tài)。舉例來(lái)說(shuō)，假設(shè)用戶A和用戶B兩者的錢加起來(lái)一共是1000，那么不管A和B之間如何轉(zhuǎn)賬、轉(zhuǎn)幾次賬，事務(wù)結(jié)束后兩個(gè)用戶的錢相加起來(lái)應(yīng)該還得是1000，這就是事務(wù)的一致性。

隔離性是當(dāng)多個(gè)用戶并發(fā)訪問(wèn)數(shù)據(jù)庫(kù)時(shí)，比如同時(shí)操作同一張表時(shí)，數(shù)據(jù)庫(kù)為每一個(gè)用戶開(kāi)啟的事務(wù)，不能被其他事務(wù)的操作所干擾，多個(gè)并發(fā)事務(wù)之間要相互隔離。關(guān)于事務(wù)的隔離性數(shù)據(jù)庫(kù)提供了多種隔離級(jí)別，稍后會(huì)介紹到。

持久性是指一個(gè)事務(wù)一旦被提交了，那么對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)的改變就是永久性的，即便是在數(shù)據(jù)庫(kù)系統(tǒng)遇到故障的情況下也不會(huì)丟失提交事務(wù)的操作。例如我們?cè)谑褂肑DBC操作數(shù)據(jù)庫(kù)時(shí)，在提交事務(wù)方法后，提示用戶事務(wù)操作完成，當(dāng)我們程序執(zhí)行完成直到看到提示后，就可以認(rèn)定事務(wù)已經(jīng)正確提交，即使這時(shí)候數(shù)據(jù)庫(kù)出現(xiàn)了問(wèn)題，也必須要將我們的事務(wù)完全執(zhí)行完成。否則的話就會(huì)造成我們雖然看到提示事務(wù)處理完畢，但是數(shù)據(jù)庫(kù)因?yàn)楣收隙鴽](méi)有執(zhí)行事務(wù)的重大錯(cuò)誤。這是不允許的。

在數(shù)據(jù)庫(kù)操作中，在并發(fā)的情況下可能出現(xiàn)如下問(wèn)題：

正是為了解決以上情況，數(shù)據(jù)庫(kù)提供了幾種隔離級(jí)別。

數(shù)據(jù)庫(kù)事務(wù)的隔離級(jí)別有4個(gè)，由低到高依次為Read uncommitted(未授權(quán)讀取、讀未提交)、Read committed（授權(quán)讀取、讀提交）、Repeatable read（可重復(fù)讀?。erializable（序列化），這四個(gè)級(jí)別可以逐個(gè)解決臟讀、不可重復(fù)讀、幻象讀這幾類問(wèn)題。

雖然數(shù)據(jù)庫(kù)的隔離級(jí)別可以解決大多數(shù)問(wèn)題，但是靈活度較差，為此又提出了悲觀鎖和樂(lè)觀鎖的概念。

悲觀鎖，它指的是對(duì)數(shù)據(jù)被外界（包括本系統(tǒng)當(dāng)前的其他事務(wù)，以及來(lái)自外部系統(tǒng)的事務(wù)處理）修改持保守態(tài)度。因此，在整個(gè)數(shù)據(jù)處理過(guò)程中，將數(shù)據(jù)處于鎖定狀態(tài)。悲觀鎖的實(shí)現(xiàn)，往往依靠數(shù)據(jù)庫(kù)提供的鎖機(jī)制。也只有數(shù)據(jù)庫(kù)層提供的鎖機(jī)制才能真正保證數(shù)據(jù)訪問(wèn)的排他性，否則，即使在本系統(tǒng)的數(shù)據(jù)訪問(wèn)層中實(shí)現(xiàn)了加鎖機(jī)制，也無(wú)法保證外部系統(tǒng)不會(huì)修改數(shù)據(jù)。

商品t_items表中有一個(gè)字段status，status為1代表商品未被下單，status為2代表商品已經(jīng)被下單（此時(shí)該商品無(wú)法再次下單），那么我們對(duì)某個(gè)商品下單時(shí)必須確保該商品status為1。假設(shè)商品的id為1。

如果不采用鎖，那么操作方法如下：

但是上面這種場(chǎng)景在高并發(fā)訪問(wèn)的情況下很可能會(huì)出現(xiàn)問(wèn)題。例如當(dāng)?shù)谝徊讲僮髦?，查詢出?lái)的商品status為1。但是當(dāng)我們執(zhí)行第三步Update操作的時(shí)候，有可能出現(xiàn)其他人先一步對(duì)商品下單把t_items中的status修改為2了，但是我們并不知道數(shù)據(jù)已經(jīng)被修改了，這樣就可能造成同一個(gè)商品被下單2次，使得數(shù)據(jù)不一致。所以說(shuō)這種方式是不安全的。

在上面的場(chǎng)景中，商品信息從查詢出來(lái)到修改，中間有一個(gè)處理訂單的過(guò)程，使用悲觀鎖的原理就是，當(dāng)我們?cè)诓樵兂鰐_items信息后就把當(dāng)前的數(shù)據(jù)鎖定，直到我們修改完畢后再解鎖。那么在這個(gè)過(guò)程中，因?yàn)閠_items被鎖定了，就不會(huì)出現(xiàn)有第三者來(lái)對(duì)其進(jìn)行修改了。需要注意的是，要使用悲觀鎖，我們必須關(guān)閉mysql數(shù)據(jù)庫(kù)的自動(dòng)提交屬性，因?yàn)镸ySQL默認(rèn)使用autocommit模式，也就是說(shuō)，當(dāng)你執(zhí)行一個(gè)更新操作后，MySQL會(huì)立刻將結(jié)果進(jìn)行提交。我們可以使用命令設(shè)置MySQL為非autocommit模式： set autocommit=0;

設(shè)置完autocommit后，我們就可以執(zhí)行我們的正常業(yè)務(wù)了。具體如下：

上面的begin/commit為事務(wù)的開(kāi)始和結(jié)束，因?yàn)樵谇耙徊轿覀冴P(guān)閉了mysql的autocommit，所以需要手動(dòng)控制事務(wù)的提交。

上面的第一步我們執(zhí)行了一次查詢操作： select status from t_items where id=1 for update; 與普通查詢不一樣的是，我們使用了 select…for update 的方式，這樣就通過(guò)數(shù)據(jù)庫(kù)實(shí)現(xiàn)了悲觀鎖。此時(shí)在t_items表中，id為1的那條數(shù)據(jù)就被我們鎖定了，其它的事務(wù)必須等本次事務(wù)提交之后才能執(zhí)行。這樣我們可以保證當(dāng)前的數(shù)據(jù)不會(huì)被其它事務(wù)修改。需要注意的是，在事務(wù)中，只有 SELECT ... FOR UPDATE 或 LOCK IN SHARE MODE 操作同一個(gè)數(shù)據(jù)時(shí)才會(huì)等待其它事務(wù)結(jié)束后才執(zhí)行，一般 SELECT ... 則不受此影響。拿上面的實(shí)例來(lái)說(shuō)，當(dāng)我執(zhí)行 select status from t_items where id=1 for update; 后。我在另外的事務(wù)中如果再次執(zhí)行 select status from t_items where id=1 for update; 則第二個(gè)事務(wù)會(huì)一直等待第一個(gè)事務(wù)的提交，此時(shí)第二個(gè)查詢處于阻塞的狀態(tài)，但是如果我是在第二個(gè)事務(wù)中執(zhí)行 select status from t_items where id=1; 則能正常查詢出數(shù)據(jù)，不會(huì)受第一個(gè)事務(wù)的影響。

使用 select…for update 會(huì)把數(shù)據(jù)給鎖住，不過(guò)我們需要注意一些鎖的級(jí)別，MySQL InnoDB默認(rèn)Row-Level Lock，所以只有「明確」地指定主鍵或者索引，MySQL 才會(huì)執(zhí)行Row lock (只鎖住被選取的數(shù)據(jù)) ，否則MySQL 將會(huì)執(zhí)行Table Lock (將整個(gè)數(shù)據(jù)表單給鎖住)。舉例如下：

1、 select * from t_items where id=1 for update;

這條語(yǔ)句明確指定主鍵（id=1），并且有此數(shù)據(jù)（id=1的數(shù)據(jù)存在），則采用row lock。只鎖定當(dāng)前這條數(shù)據(jù)。

2、 select * from t_items where id=3 for update;

這條語(yǔ)句明確指定主鍵，但是卻查無(wú)此數(shù)據(jù)，此時(shí)不會(huì)產(chǎn)生lock（沒(méi)有元數(shù)據(jù)，又去lock誰(shuí)呢？）。

3、 select * from t_items where name='手機(jī)' for update;

這條語(yǔ)句沒(méi)有指定數(shù)據(jù)的主鍵，那么此時(shí)產(chǎn)生table lock，即在當(dāng)前事務(wù)提交前整張數(shù)據(jù)表的所有字段將無(wú)法被查詢。

4、 select * from t_items where id0 for update; 或者 select * from t_items where id1 for update; （注：在SQL中表示不等于）

上述兩條語(yǔ)句的主鍵都不明確，也會(huì)產(chǎn)生table lock。

5、 select * from t_items where status=1 for update; （假設(shè)為status字段添加了索引）

這條語(yǔ)句明確指定了索引，并且有此數(shù)據(jù)，則產(chǎn)生row lock。

6、 select * from t_items where status=3 for update; （假設(shè)為status字段添加了索引）

這條語(yǔ)句明確指定索引，但是根據(jù)索引查無(wú)此數(shù)據(jù)，也就不會(huì)產(chǎn)生lock。

樂(lè)觀鎖（ Optimistic Locking ） 相對(duì)悲觀鎖而言，樂(lè)觀鎖假設(shè)認(rèn)為數(shù)據(jù)一般情況下不會(huì)造成沖突，所以只會(huì)在數(shù)據(jù)進(jìn)行提交更新的時(shí)候，才會(huì)正式對(duì)數(shù)據(jù)的沖突與否進(jìn)行檢測(cè)，如果發(fā)現(xiàn)沖突了，則返回用戶錯(cuò)誤的信息，讓用戶決定如何去做。實(shí)現(xiàn)樂(lè)觀鎖一般來(lái)說(shuō)有以下2種方式：

一文詳解-MySQL 事務(wù)和鎖

當(dāng)多個(gè)用戶訪問(wèn)同一份數(shù)據(jù)時(shí)，一個(gè)用戶在更改數(shù)據(jù)的過(guò)程中，可能有其他用戶同時(shí)發(fā)起更改請(qǐng)求，為保證數(shù)據(jù)庫(kù)記錄的更新從一個(gè)一致性狀態(tài)變?yōu)榱硗庖粋€(gè)一致性狀態(tài)，使用事務(wù)處理是非常必要的，事務(wù)具有以下四個(gè)特性：

MySQL 提供了多種事務(wù)型存儲(chǔ)引擎，如 InnoDB 和 BDB 等，而 MyISAM 不支持事務(wù)。為了支持事務(wù)，InnoDB 存儲(chǔ)引擎引入了與事務(wù)處理相關(guān)的 REDO 日志和 UNDO 日志，同時(shí)事務(wù)依賴于 MySQL 提供的鎖機(jī)制

事務(wù)執(zhí)行時(shí)需要將執(zhí)行的事務(wù)日志寫入日志文件，對(duì)應(yīng)的文件為 REDO 日志。當(dāng)每條 SQL 進(jìn)行數(shù)據(jù)更新操作時(shí)，首先將 REDO 日志寫進(jìn)日志緩沖區(qū)。當(dāng)客戶端執(zhí)行 COMMIT 命令提交時(shí)，日志緩沖區(qū)的內(nèi)容將被刷新到磁盤，日志緩沖區(qū)的刷新方式或者時(shí)間間隔可以通過(guò)參數(shù) innodb_flush_log_at_trx_commit 控制

REDO 日志對(duì)應(yīng)磁盤上的 ib_logifleN 文件，該文件默認(rèn)為 5MB，建議設(shè)置為 512MB，以便容納較大的事務(wù)。MySQL 崩潰恢復(fù)時(shí)會(huì)重新執(zhí)行 REDO 日志的記錄，恢復(fù)最新數(shù)據(jù)，保證已提交事務(wù)的持久性

與 REDO 日志相反，UNDO 日志主要用于事務(wù)異常時(shí)的數(shù)據(jù)回滾，具體內(nèi)容就是記錄數(shù)據(jù)被修改前的信息到 UNDO 緩沖區(qū)，然后在合適的時(shí)間將內(nèi)容刷新到磁盤

假如由于系統(tǒng)錯(cuò)誤或者 rollback 操作而導(dǎo)致事務(wù)回滾，可以根據(jù) undo 日志回滾到?jīng)]修改前的狀態(tài)，保證未提交事務(wù)的原子性

與 REDO 日志不同的是，磁盤上不存在單獨(dú)的 UNDO 日志文件，所有的 UNDO 日志均存在表空間對(duì)應(yīng)的 .ibd 數(shù)據(jù)文件中，即使 MySQL 服務(wù)啟動(dòng)了獨(dú)立表空間

在 MySQL 中，可以使用 BEGIN 開(kāi)始事務(wù)，使用 COMMIT 結(jié)束事務(wù)，中間可以使用 ROLLBACK 回滾事務(wù)。MySQL 通過(guò) SET AUTOCOMMIT、START TRANSACTION、COMMIT 和 ROLLBACK 等語(yǔ)句支持本地事務(wù)

MySQL 定義了四種隔離級(jí)別，指定事務(wù)中哪些數(shù)據(jù)改變其他事務(wù)可見(jiàn)、哪些數(shù)據(jù)該表其他事務(wù)不可見(jiàn)。低級(jí)別的隔離級(jí)別可以支持更高的并發(fā)處理，同時(shí)占用的系統(tǒng)資源更少

InnoDB 系統(tǒng)級(jí)事務(wù)隔離級(jí)別可以使用以下語(yǔ)句設(shè)置：

查看系統(tǒng)級(jí)事務(wù)隔離級(jí)別：

InnoDB 會(huì)話級(jí)事務(wù)隔離級(jí)別可以使用以下語(yǔ)句設(shè)置：

查看會(huì)話級(jí)事務(wù)隔離級(jí)別：

在該隔離級(jí)別，所有事務(wù)都可以看到其他未提交事務(wù)的執(zhí)行結(jié)果。讀取未提交的數(shù)據(jù)稱為臟讀（Dirty Read），即是：首先開(kāi)啟 A 和 B 兩個(gè)事務(wù)，在 B 事務(wù)更新但未提交之前，A 事務(wù)讀取到了更新后的數(shù)據(jù)，但由于 B 事務(wù)回滾，導(dǎo)致 A 事務(wù)出現(xiàn)了臟讀現(xiàn)象

所有事務(wù)只能看見(jiàn)已經(jīng)提交事務(wù)所做的改變，此級(jí)別可以解決臟讀，但也會(huì)導(dǎo)致不可重復(fù)讀（Nonrepeatable Read）：首先開(kāi)啟 A 和 B 兩個(gè)事務(wù)，A事務(wù)讀取了 B 事務(wù)的數(shù)據(jù)，在 B 事務(wù)更新并提交后，A 事務(wù)又讀取到了更新后的數(shù)據(jù)，此時(shí)就出現(xiàn)了同一 A 事務(wù)中的查詢出現(xiàn)了不同的查詢結(jié)果

MySQL 默認(rèn)的事務(wù)隔離級(jí)別，能確保同一事務(wù)的多個(gè)實(shí)例在并發(fā)讀取數(shù)據(jù)時(shí)看到同樣的數(shù)據(jù)行，理論上會(huì)導(dǎo)致一個(gè)問(wèn)題，幻讀（Phontom Read）。例如，第一個(gè)事務(wù)對(duì)一個(gè)表中的數(shù)據(jù)做了修改，這種修改會(huì)涉及表中的全部數(shù)據(jù)行，同時(shí)第二個(gè)事務(wù)也修改這個(gè)表中的數(shù)據(jù)，這次的修改是向表中插入一行新數(shù)據(jù)，此時(shí)就會(huì)發(fā)生操作第一個(gè)事務(wù)的用戶發(fā)現(xiàn)表中還有沒(méi)有修改的數(shù)據(jù)行

InnoDB 通過(guò)多版本并發(fā)控制機(jī)制（MVCC）解決了該問(wèn)題：InnoDB 通過(guò)為每個(gè)數(shù)據(jù)行增加兩個(gè)隱含值的方式來(lái)實(shí)現(xiàn)，這兩個(gè)隱含值記錄了行的創(chuàng)建時(shí)間、過(guò)期時(shí)間以及每一行存儲(chǔ)時(shí)間發(fā)生時(shí)的系統(tǒng)版本號(hào)，每個(gè)查詢根據(jù)事務(wù)的版本號(hào)來(lái)查詢結(jié)果

通過(guò)強(qiáng)制事務(wù)排序，使其不可能相互沖突，從而解決幻讀問(wèn)題。簡(jiǎn)而言之，就是在每個(gè)讀的數(shù)據(jù)行上加上共享鎖實(shí)現(xiàn)，這個(gè)級(jí)別會(huì)導(dǎo)致大量的超時(shí)現(xiàn)象和鎖競(jìng)爭(zhēng)，一般不推薦使用

為了解決數(shù)據(jù)庫(kù)并發(fā)控制問(wèn)題，如走到同一時(shí)刻客戶端對(duì)同一張表做更新或者查詢操作，需要對(duì)并發(fā)操作進(jìn)行控制，因此產(chǎn)生了鎖

共享鎖的粒度是行或者元組（多個(gè)行），一個(gè)事務(wù)獲取了共享鎖以后，可以對(duì)鎖定范圍內(nèi)的數(shù)據(jù)執(zhí)行讀操作

排他鎖的粒度與共享鎖相同，一個(gè)事務(wù)獲取排他鎖以后，可以對(duì)鎖定范圍內(nèi)的數(shù)據(jù)執(zhí)行寫操作

有兩個(gè)事務(wù) A 和 B，如果事務(wù) A 獲取了一個(gè)元組的共享鎖，事務(wù) B 還可以立即獲取這個(gè)元組的共享鎖，但不能獲取這個(gè)元組的排他鎖，必須等到事務(wù) A 釋放共享鎖之后。如果事務(wù) A 獲取了一個(gè)元組的排他鎖，事務(wù) B 不能立即獲取這個(gè)元組的共享鎖，也不能立即獲取這個(gè)元組的排他鎖，必須等到 A 釋放排他鎖之后

意向鎖是一種表鎖，鎖定的粒度是整張表，分為意向共享鎖和意向排他鎖。意向共享鎖表示一個(gè)事務(wù)有意對(duì)數(shù)據(jù)上共享鎖或者排他鎖。有意表示事務(wù)想執(zhí)行操作但還沒(méi)真正執(zhí)行

鎖的粒度主要分為表鎖和行鎖

表鎖的開(kāi)銷最小，同時(shí)允許的并發(fā)量也是最小。MyISAM 存儲(chǔ)引擎使用該鎖機(jī)制。當(dāng)要寫入數(shù)據(jù)時(shí)，整個(gè)表記錄被鎖，此時(shí)其他讀/寫動(dòng)作一律等待。一些特定的動(dòng)作，如 ALTER TABLE 執(zhí)行時(shí)使用的也是表鎖

行鎖可以支持最大的并發(fā)，InnoDB 存儲(chǔ)引擎使用該鎖機(jī)制。如果要支持并發(fā)讀/寫，建議采用 InnoDB 存儲(chǔ)引擎