柚子快報(bào)邀請(qǐng)碼778899分享：數(shù)據(jù)庫 mysql學(xué)習(xí)

http://yzkb.51969.com/

mysql基礎(chǔ)定義

mysql的鎖：

樂觀鎖和悲觀鎖

樂觀鎖

樂觀鎖不是數(shù)據(jù)庫自帶的，需要我們自己去實(shí)現(xiàn)。 樂觀鎖是指 更新數(shù)據(jù)庫時(shí)認(rèn)為操作不會(huì)導(dǎo)致沖突，在操作數(shù)據(jù)時(shí)不加鎖，而在進(jìn)行更新后，再去判斷是否有沖突了。 通常實(shí)現(xiàn)是這樣的：在表中的數(shù)據(jù)進(jìn)行更新時(shí)，先給數(shù)據(jù)表加一個(gè)版本(version)字段，每操作一次，將那條記錄的版本號(hào)加1。 先查詢出那條記錄，獲取出version字段。 如果要對(duì)那條記錄進(jìn)行更新操作，則先判斷此刻version的值是否與剛剛查詢出來時(shí)的version的值相等。 如果相等，則說明這段期間沒有其他程序?qū)ζ溥M(jìn)行操作，則可以執(zhí)行更新，將version字段的值加1。 如果version值與剛剛獲取出來的version的值不相等，則說明這段期間已經(jīng)有其他程序?qū)ζ溥M(jìn)行操作了，則不進(jìn)行更新操作。 舉例：下單操作包括3步驟： 1、查詢出商品信息 select (status,status,version) from t_goods where id=#{id} 2、根據(jù)商品信息生成訂單 3、修改商品status為2 update t_goods set status=2,version=version+1 where id=#{id} and version=#{version}; 除了自己手動(dòng)實(shí)現(xiàn)樂觀鎖之外，現(xiàn)在網(wǎng)上許多框架已經(jīng)封裝好了樂觀鎖的實(shí)現(xiàn)，如hibernate，需要時(shí)，可能自行搜索"hiberate 樂觀鎖"試試看。

悲觀鎖 悲觀鎖就是在操作數(shù)據(jù)時(shí)，認(rèn)為此操作會(huì)出現(xiàn)數(shù)據(jù)沖突，所以在進(jìn)行每次操作時(shí)都要通過獲取鎖才能進(jìn)行對(duì)相同數(shù)據(jù)的操作，這點(diǎn)跟java中的synchronized很相似，所以悲觀鎖需要耗費(fèi)較多的時(shí)間。 悲觀鎖是由數(shù)據(jù)庫自己實(shí)現(xiàn)了的，要用的時(shí)候，我們直接調(diào)用數(shù)據(jù)庫的相關(guān)語句就可以了。 悲觀鎖涉及到的另外兩個(gè)鎖：就是共享鎖與排它鎖。 共享鎖和排它鎖是悲觀鎖的不同的實(shí)現(xiàn)，它倆都屬于悲觀鎖的范疇。 共享鎖與排他鎖 數(shù)據(jù)庫的增刪改操作默認(rèn)都會(huì)加排他鎖，而查詢不會(huì)加任何鎖。

共享鎖和排他鎖

共享鎖 悲觀鎖一般數(shù)據(jù)庫已經(jīng)實(shí)現(xiàn)了，共享鎖也屬于悲觀鎖的一種，那么共享鎖在mysql中是通過什么命令來調(diào)用呢。 在執(zhí)行語句后面加上 lock in share mode就代表對(duì)某些資源加上共享鎖了。

排他鎖 update、insert、delete 語句會(huì)自動(dòng)加排它鎖。 在執(zhí)行的語句后面加上 for update select * from table where id=1 for update

行鎖與表鎖

行鎖 mysql鎖機(jī)制分為表級(jí)鎖和行級(jí)鎖。mysql行級(jí)鎖包括共享鎖與排他鎖： 共享鎖又稱為讀鎖，簡(jiǎn)稱S鎖 共享鎖：多個(gè)事務(wù)都可以加共享鎖讀同??數(shù)據(jù)，但是別的事務(wù)不能寫這?數(shù)據(jù)。 一個(gè)事務(wù)獲取了共享鎖，其他事務(wù)也只能加共享鎖或不加鎖查詢。 其他事務(wù)不能寫。 其他事務(wù)加排他鎖查不到，因?yàn)榕潘i與共享鎖不能存在同一數(shù)據(jù)上。 并發(fā)處理可以用讀鎖和寫鎖來控制 讀鎖是共享的，或者說是相互不阻塞的，多個(gè)客戶在同一時(shí)刻可以讀取同一個(gè)資源，而互不干擾，

排他鎖又稱為寫鎖，簡(jiǎn)稱X鎖 排他鎖：?個(gè)事務(wù)可以讀/寫這?數(shù)據(jù)，別的事務(wù)只能讀不能寫。 一個(gè)事務(wù)獲取了一個(gè)數(shù)據(jù)行的排他鎖，其他事務(wù)就不能再獲取該行的其他鎖，包括共享鎖和排他鎖。 寫鎖則是排他的，也就是說一個(gè)寫鎖會(huì)阻塞其他的寫鎖和讀鎖，這是出于安全策略的考慮，只有這樣，才能確保在給定的時(shí)間里，只有一個(gè)用戶能執(zhí)行寫入，并防止其他用戶讀取正在寫入的同一資源 獲取排他鎖的事務(wù)可以對(duì)數(shù)據(jù)就行讀取和修改。 其他事務(wù)可以通過select …from…查詢數(shù)據(jù)，因?yàn)槠胀ú樵儧]有任何鎖機(jī)制。 其他事務(wù)不能寫。 mysql InnoDB引擎默認(rèn)的修改數(shù)據(jù)語句，update,delete,insert都會(huì)自動(dòng)給涉及到的數(shù)據(jù)加上排他鎖，select語句默認(rèn)不會(huì)加任何鎖類型。 加排他鎖可以使用select …for update語句 加共享鎖可以使用select … lock in share mode語句

表鎖

innodb的 表鎖，分成意向共享鎖 和意向排他鎖，表鎖是innodb引擎?動(dòng)加的，不?你??去加。 意向共享鎖：加共享?鎖時(shí)，必須先加共享表鎖； 意向排他鎖 ：給某?加排他鎖時(shí)，必須先給表加排他鎖。 mysql共享鎖與排他鎖 - java攻城獅 - 博客園 insert、update、delete，innodb會(huì)?動(dòng)給那??加?級(jí)排他鎖。 select，innodb啥鎖都不加。 innodb從來不會(huì)??主動(dòng)加個(gè)共享鎖的，除?你?下?的語句???動(dòng)加個(gè)鎖： ?動(dòng)加共享鎖：select * from table where id=1 lock in share mode，那你就給那??加了個(gè)共享鎖，其他事務(wù)就不能來修改這?數(shù)據(jù)了。 ?動(dòng)加排他鎖：select * from table where id=1 for update，那你就給那??加了個(gè)排他鎖，意思就是你準(zhǔn)備修改，別的事務(wù)就別修改了，別的事務(wù)的修改會(huì)hang住。這個(gè)要慎?，?般我們線上系統(tǒng)不?這個(gè)，容易搞出問題來。 對(duì)??數(shù)據(jù)，如果有?在修改會(huì)加個(gè)排他鎖，然后你不能修改只能等著獲取這把鎖，但這時(shí)可以隨便select，查詢你的事務(wù)開始之前那?數(shù)據(jù)的某個(gè)版本。然后如果你修改某?數(shù)據(jù)，會(huì)同時(shí)拿這個(gè)表的排他鎖，但是如果不同的事務(wù)修改不同的?，會(huì)拿不同?的?級(jí)排他鎖，但?家都會(huì)拿?個(gè)表的排他鎖，實(shí)際上 innodb 的表級(jí)排他鎖可以隨便拿，這個(gè)是沒沖突的。 mysql innodb 存儲(chǔ)引擎的默認(rèn)鎖模式，其實(shí)還挺不錯(cuò)的。相當(dāng)于就是??數(shù)據(jù)，同?個(gè)時(shí)刻只能?個(gè)?在修改，但是別?修改，你可以隨便讀，讀是讀某個(gè)版本的，?mvcc 機(jī)制。 其實(shí)innodb下的記錄鎖（也叫行鎖），間隙鎖，next-key鎖統(tǒng)統(tǒng)屬于排他鎖。

Record Lock — 單個(gè)行記錄上的鎖； Gap Lock — 間隙鎖，鎖定一個(gè)范圍，不包括記錄本身； Next-Key Lock — 鎖定一個(gè)范圍，包括記錄本身。

生活中的間隙鎖 編程的思想源于生活，生活中的例子能幫助我們更好的理解一些編程中的思想。生活中排隊(duì)的場(chǎng)景，小明，小紅，小花三個(gè)人依次站成一排，此時(shí)，如何讓新來的小剛不能站在小紅旁邊，這時(shí)候只要將小紅和她前面的小明之間的空隙封鎖，將小紅和她后面的小花之間的空隙封鎖，那么小剛就不能站到小紅的旁邊。這里的小紅，小明，小花，小剛就是數(shù)據(jù)庫的一條條記錄。 他們之間的空隙也就是間隙，而封鎖他們之間距離的鎖，叫做間隙鎖。 Mysql中的間隙鎖 表中id為主鍵，number字段上有非唯一索引的二級(jí)索引，有什么方式可以讓該表不能再插入number=5的記錄？注意：此時(shí)按照二級(jí)索引建索引表，即插入時(shí)按照number順序排序。 只要控制幾個(gè)點(diǎn)，number=5之前不能插入記錄，number=5現(xiàn)有的記錄之間不能再插入新的記錄，number=5之后不能插入新的記錄，那么新的number=5的記錄將不能被插入進(jìn)來。 那么，mysql是如何控制number=5之前，之中，之后不能有新的記錄插入呢（防止幻讀）？ 答案是用間隙鎖，在RR級(jí)別下，mysql通過間隙鎖可以實(shí)現(xiàn)鎖定number=5之前的間隙，number=5記錄之間的間隙，number=5之后的間隙，從而使的新的記錄無法被插入進(jìn)來。

間隙是怎么劃分的？ 注：我們規(guī)定（id=A,number=B）代表一條字段id=A，字段number=B的記錄，（C，D）代表一個(gè)區(qū)間，代表C-D這個(gè)區(qū)間范圍。 圖中根據(jù)number列，我們分為幾個(gè)區(qū)間：(無窮小，2)(2，4)(4，5)(5，5)(5,11)(11，無窮大)。 只要這些區(qū)間對(duì)應(yīng)的兩個(gè)臨界記錄中間可以插入記錄，就認(rèn)為區(qū)間對(duì)應(yīng)的記錄之間有間隙。 例如：區(qū)間（2，4）分別對(duì)應(yīng)的臨界記錄是（id=1,number=2），（id=3，number=4），這兩條記錄中間可以插入（id=2,number=3）等記錄，那么就認(rèn)為（id=1,number=2）與（id=3，number=4）之間存在間隙。 很多人會(huì)問，那記錄（id=6，number=5）與（id=8，number=5）之間有間隙嗎？ 答案是有的，（id=6，number=5）與（id=8，number=5）之間可以插入記錄（id=7，number=5），因此（id=6,number=5）與（id=8,number=5）之間有間隙的，間隙鎖鎖定的區(qū)域 根據(jù)檢索條件向左尋找最靠近檢索條件的記錄值A(chǔ)，作為左區(qū)間，向右尋找最靠近檢索條件的記錄值B作為右區(qū)間，即鎖定的間隙為（A，B）。 圖一中，where number=5的話，那么間隙鎖的區(qū)間范圍為（4,11）； 間隙鎖的目的是為了防止幻讀，其主要通過兩個(gè)方面實(shí)現(xiàn)這個(gè)目的： 防止間隙內(nèi)有新數(shù)據(jù)被插入。 防止已存在的數(shù)據(jù)，更新成間隙內(nèi)的數(shù)據(jù)（例如防止numer=3的記錄通過update變成number=5） innodb自動(dòng)使用間隙鎖的條件： 必須在RR級(jí)別下 檢索條件必須有索引（沒有索引的話，mysql會(huì)全表掃描，那樣會(huì)鎖定整張表所有的記錄，包括不存在的記錄，此時(shí)其他事務(wù)不能修改不能刪除不能添加）

MyISAM 操作數(shù)據(jù)都是使用的表鎖，你更新一條記錄就要鎖整個(gè)表，導(dǎo)致性能較低，并發(fā)不高。當(dāng)然同時(shí)它也不會(huì)存在死鎖問題。 InnoDB 與 MyISAM 的最大不同有兩點(diǎn)： 一是 InnoDB 支持事務(wù)； 二是 InnoDB 采用了行級(jí)鎖。也就是你需要修改哪行，就可以只鎖定哪行。 悲觀鎖實(shí)際使用了排他鎖來實(shí)現(xiàn)（select **** for update）。文章開頭說到，innodb加行鎖的前提是：必須是通過索引條件來檢索數(shù)據(jù)，否則會(huì)切換為表鎖。 因此，悲觀鎖在未通過索引條件檢索數(shù)據(jù)時(shí)，會(huì)鎖定整張表。導(dǎo)致其他程序不允許“加鎖的查詢操作”，影響吞吐。故如果在查詢居多的情況下，推薦使用樂觀鎖。 “加鎖的查詢操作”：加過排他鎖的數(shù)據(jù)行在其他事務(wù)中是不能修改的，也不能通過for update或lock in share mode的加鎖方式查詢，但可以直接通過select …from…查詢數(shù)據(jù)，因?yàn)槠胀ú樵儧]有任何鎖機(jī)制。 樂觀鎖更新有可能會(huì)失敗，甚至是更新幾次都失敗，這是有風(fēng)險(xiǎn)的。所以如果寫入居多，對(duì)吞吐要求不高，可使用悲觀鎖。 也就是一句話：讀用樂觀鎖，寫用悲觀鎖。 鎖的弊端： 加鎖也是需要消耗資源，鎖的各種操作，包括獲得鎖和檢查所是否已解除，釋放鎖等，都會(huì)增加系統(tǒng)的開銷，如果消耗大量的時(shí)間來管理鎖，而不是存取書籍，那么系統(tǒng)的性能可能會(huì)因此收到影響 有表鎖和行級(jí)鎖，行級(jí)鎖可以最大程度的支持并發(fā)處理，同時(shí)也帶來了最大的鎖開銷

mysqll的事務(wù)

事務(wù) 事務(wù)是一個(gè)最小的不可在分的工作單元；通常一個(gè)事務(wù)對(duì)應(yīng)一個(gè)完整的業(yè)務(wù)(例如銀行賬戶轉(zhuǎn)賬業(yè)務(wù)，該業(yè)務(wù)是一個(gè)最小的工作單元) 一個(gè)完整的業(yè)務(wù)需要批量的DML(insert、update、delete)語句共同聯(lián)合完成。 事務(wù)只和DML語句有關(guān)，或者說DML語句才有事務(wù)。這個(gè)和業(yè)務(wù)邏輯有關(guān)，業(yè)務(wù)邏輯不同，DML語句的個(gè)數(shù)不同。

事務(wù)的四大特性：

原子性： 一個(gè)事務(wù)必須被視為一個(gè)不可分割的最小工作單元，整個(gè)事務(wù)中的所有操作要么全部提交成功，要么全部失敗回滾，對(duì)于一個(gè)事務(wù)來說，不可能只執(zhí)行其中的一個(gè)部分操作這就是事務(wù)的原子性。

一致性： 數(shù)據(jù)庫總是從一個(gè)一致性的狀態(tài)轉(zhuǎn)換到另外一個(gè)一致性的狀態(tài)，比如事務(wù)執(zhí)行結(jié)束以后數(shù)據(jù)庫的完整性約束沒有被破壞，開始是1000元，后面轉(zhuǎn)賬結(jié)束了也是1000元。

隔離性： 一個(gè)事務(wù)所做的修改在最終提交以前，對(duì)其他事務(wù)是不可見的，并發(fā)的各個(gè)事務(wù)之間不能相互干擾。

持久性： 指事務(wù)一旦提交，則其所做的修改就會(huì)永久保存在數(shù)據(jù)庫中，及時(shí)系統(tǒng)崩潰修改的數(shù)據(jù)也不會(huì)丟失。其他操作或者故障不應(yīng)該對(duì)其造成影響。

mysql的四種隔離級(jí)別

READ UNCOMMITTED(未提交讀)： 最低的事務(wù)隔離級(jí)別，事務(wù)中的修改，及時(shí)沒有提交，對(duì)其他的事務(wù)也都是可見的，事務(wù)可以讀取未提交的數(shù)據(jù)（可能造成臟讀、不可重復(fù)讀、幻讀）

READ COMMITTED (提交讀也叫不可重復(fù)讀)： 換句話說，一個(gè)事務(wù)開始時(shí)只能看到已經(jīng)提交的事務(wù)所做的修改，一個(gè)事務(wù)從開始到提交之前，所做的任何修改對(duì)其他事務(wù)都是不可見的，也就是允許讀取并發(fā)事務(wù)已經(jīng)提交的數(shù)據(jù)，可以避免臟讀，但是可能造成不可重復(fù)、幻讀，可重復(fù)讀是mysql默認(rèn)的事務(wù)隔離級(jí)別

REPEATABLE READ (可重復(fù)讀)： 同一個(gè)事務(wù)中多次讀取同樣記錄的結(jié)果都是一致的，對(duì)同一字段多次讀取的結(jié)果都是一致的，除非本身事務(wù)修改，可以避免臟讀和不可重復(fù)讀，但是可能造成幻讀。 可重復(fù)讀的重點(diǎn)是修改，幻讀的重點(diǎn)在于新增或者刪除。

SERIALIZABLE(可串行化)： 是mysql最高的隔離級(jí)別，在通過強(qiáng)制事務(wù)串行執(zhí)行，避免了幻讀的問題，簡(jiǎn)單的來說，它會(huì)在每一行數(shù)據(jù)上都加鎖，所以可能導(dǎo)致大量的超時(shí)和鎖爭(zhēng)用的問題，實(shí)際應(yīng)用中也很少用收到這個(gè)事務(wù)隔離級(jí)別，只有在非常需要確保數(shù)據(jù)的一致性而且可以接受沒有并發(fā)的情況下才會(huì)考慮用該級(jí)別

幻讀 所為幻讀，就是指當(dāng)某個(gè)事務(wù)在讀取某個(gè)范圍的記錄時(shí)，另外一個(gè)事務(wù)又在該范圍內(nèi)插入了新的記錄，當(dāng)之前的事務(wù)再次讀取該范圍內(nèi)的記錄時(shí)就會(huì)產(chǎn)生換行），innodb和xtradb存儲(chǔ)引擎通過多版本并發(fā)控制MVCC解決了幻讀的問題。

贓讀 臟讀是指事務(wù)讀取到其他事務(wù)未提交的數(shù)據(jù) 例如：有事務(wù)A、B和一條記錄：id為1，name為張三 B首先進(jìn)行更新操作，將name的值由張三改為張老三，但還未提交事務(wù) begin; update stu set name = ‘張老三’ where id = 1 然后A進(jìn)行查詢操作，查詢姓名為張老三 select name from stu where id = 1 然后B由于某種原因被回滾，name自然從張老三恢復(fù)到了張三 A就會(huì)出現(xiàn)一個(gè)問題，讀取的是張老三，但數(shù)據(jù)庫中實(shí)際存儲(chǔ)的是張三，我到底是用哪一個(gè)呢？ 這就是臟讀，事務(wù)A讀取到事務(wù)B未提交的數(shù)據(jù)

死鎖： 倆個(gè)或者多個(gè)事務(wù)在同一資源上相互占用，并請(qǐng)求鎖定對(duì)方占用的資源，從而導(dǎo)致惡性循環(huán)的現(xiàn)象，當(dāng)多個(gè)事務(wù)試圖以不同的順序鎖定資源時(shí)就可能會(huì)產(chǎn)生死鎖，鎖客事務(wù)同時(shí)鎖定同一個(gè)資源時(shí)，也會(huì)產(chǎn)生死鎖。

innodb目前處理死鎖的方法是，將持有最少行級(jí)排他鎖的事務(wù)進(jìn)行回滾。大多數(shù)情況下只需要重新執(zhí)行因死鎖回滾的事務(wù)即可。

自動(dòng)提交AUTOCOMMIT SET AUTOCOMMIT = 1; 開啟 0 是禁用；

多版本并發(fā)控制： MVCC是行級(jí)鎖的一個(gè)變種，但是它在很多情況下避免了加鎖的操作，因此開銷更低，雖然實(shí)現(xiàn)的機(jī)制有所不同，但大都實(shí)現(xiàn)了非阻塞的讀操作，寫操作也只是鎖定了必要的行，他只在可重復(fù)讀和提交讀倆個(gè)隔離級(jí)別下工作其他的倆個(gè)隔離級(jí)別懟他不兼容，因?yàn)镽EAD UNCOMMITTED 總是讀取最新的數(shù)據(jù)行，而不是符合當(dāng)前事務(wù)版本的數(shù)據(jù)行，而SERIALIZABLE則會(huì)對(duì)所有讀取的行加鎖。

grant all privileges on . to ‘root‘@‘%‘ identified by ‘123456‘ with grant optio 復(fù)制 原因： mysql的版本問題，這條語句適用于MySQL8.0之前

解決： mysql8.0及以上版本需要使用以下語句：

create user root@‘%’ identified by ‘123456’;

grant all privileges on . to root@‘%’ with grant option;

查看mysql是否開啟：status mysqld.service 鏈接數(shù)據(jù)庫：mysql -h127.0.0.1 -uroot -p password

柚子快報(bào)邀請(qǐng)碼778899分享：數(shù)據(jù)庫 mysql學(xué)習(xí)

http://yzkb.51969.com/

推薦鏈接