[沫忘錄]MySQL 鎖

鎖能夠協(xié)調(diào)多線程或多進(jìn)程并發(fā)訪問某資源產(chǎn)生的數(shù)據(jù)沖突與錯(cuò)亂。而在數(shù)據(jù)庫中，鎖也是協(xié)調(diào)數(shù)據(jù)庫訪問的有效工具。

全局鎖

能夠鎖住當(dāng)前服務(wù)器所有數(shù)據(jù)庫及其表。后續(xù)所有事務(wù)都只能進(jìn)行讀操作，而不能進(jìn)行寫操作或表屬性更改。

典型使用場景是數(shù)據(jù)庫的邏輯備份。

在數(shù)據(jù)庫備份時(shí)，如果中途對還未來得及備份的表進(jìn)行了一次修改，那么此時(shí)備份的數(shù)據(jù)庫是修改之后的數(shù)據(jù)庫，而非修改之前的數(shù)據(jù)庫。更甚的是，如果該次修改還涉及已經(jīng)備份了的數(shù)據(jù)，那么這次備份既包含修改后的數(shù)據(jù)，也包含修改前的數(shù)據(jù)，照成了數(shù)據(jù)的不一致性。

#數(shù)據(jù)庫備份(命令行命令)
mysqldump -uroot(用戶) -p1234(密碼) 待備份數(shù)據(jù)庫名>itcast.sql(數(shù)據(jù)庫文件)#添加全局鎖
flush tables with read lock;#關(guān)閉全局鎖
unlock tables;

存在的缺點(diǎn)

• 當(dāng)全局鎖存在時(shí)，許多諸如插入數(shù)據(jù)的操作都會(huì)阻塞停擺。大幅影響并發(fā)效率。
• 當(dāng)全局鎖存在時(shí)，如果存在主從數(shù)據(jù)庫，則對從屬數(shù)據(jù)庫備份時(shí)，從屬數(shù)據(jù)庫無法接受主數(shù)據(jù)庫發(fā)來的數(shù)據(jù)，造成主從數(shù)據(jù)不同步。

表級(jí)鎖

表級(jí)鎖的使用頻次比全局鎖大，但表鎖鎖住整張表，粒度比較大，所以發(fā)生鎖沖突的概率最高。

表鎖

表鎖分為表共享讀鎖和表獨(dú)占寫鎖。

• 表共享讀鎖

  所有客戶端連接一律只讀，其他客戶端寫操作阻塞。

• 表獨(dú)占寫鎖

  只允許加鎖客戶端讀寫，其他客戶端操作阻塞。

#加鎖
lock tables 表名 read|write;#解鎖
unlock tables;#或者直接斷開客戶端連接

元數(shù)據(jù)鎖

元數(shù)據(jù)鎖(meta data lock, MDL)加鎖過程是系統(tǒng)自動(dòng)控制，無需顯示使用，主要用于避免DML和DDL沖突，保證數(shù)據(jù)讀寫正確性。

當(dāng)對一張表進(jìn)行增刪改查時(shí)，加的MDL讀寫鎖是共享的(共享即存在一種鎖時(shí)是否能夠添加另一種鎖)；當(dāng)對表結(jié)構(gòu)進(jìn)行變更操作的時(shí)候，加MDL寫鎖是排他互斥的。

#查看元數(shù)據(jù)鎖
SELECT object_type, object_schema, object_name, lock_type, lock_duration FROM performance_schema.metadata_locks;

元數(shù)據(jù)鎖中的shared_write和shared_read不同于表鎖，會(huì)對其他客戶端的事務(wù)產(chǎn)生影響，而更多的是一種標(biāo)記，主要與EXCLUSIVE的進(jìn)行互斥。故不同客戶端攜帶shared_write和shared_read并不會(huì)同表鎖一樣可能相會(huì)之間阻塞。

意向鎖

當(dāng)表中存在行級(jí)鎖，再加表鎖可能因表鎖類型而無法加鎖。而如果全表掃描是否有行鎖，那么效率較低。因此我們引入意向鎖來標(biāo)記行鎖類型，避免全表查詢。

notice: 元數(shù)據(jù)鎖中加鎖等行為也會(huì)產(chǎn)生意向鎖，如lock in share mode就會(huì)對查詢的元組加意向共享鎖和行共享鎖，數(shù)據(jù)修改則會(huì)加意向排他鎖和行排他鎖，這與元數(shù)據(jù)鎖中的讀寫鎖兼容有所區(qū)別。

• 意向共享鎖(IS): 與表鎖共享鎖(read)兼容，與表鎖排他鎖(write)互斥。
• 意向排他鎖(IX): 與表鎖共享鎖(read)和表鎖排他鎖(write)都互斥。意向鎖間不互斥。

#查看意向鎖
select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks;

行級(jí)鎖

行鎖基于聚集索引的索引項(xiàng)進(jìn)行上鎖，而非對表中行直接上鎖。

行級(jí)鎖的特點(diǎn)是粒度最小，并發(fā)最高。

• 行鎖

  • 共享鎖(s): 允許一個(gè)事務(wù)讀一行，阻止其他事務(wù)讀此行。
  • 排他鎖(x): 允許加排他鎖事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)所有行為。

  事務(wù)在默認(rèn)隔離級(jí)別下，對已操作的單行加行鎖。

  行鎖是對索引進(jìn)行加鎖，如若對沒有索引的字段進(jìn)行匹配，那么行鎖將會(huì)升級(jí)成表鎖。

• 間隙鎖

  在兩個(gè)元組之間加鎖，防止其他事務(wù)將數(shù)據(jù)插入到這兩個(gè)元組之間。

  間隙鎖是能夠共存的，多個(gè)事務(wù)可以對同一個(gè)間隙添加間隙鎖。

• 臨鍵鎖

  在對元組加行鎖的同時(shí)，對元組前面的加間隙鎖。

notice：

1. 等值查詢(含索引)時(shí)，如無法查找到該元組，則會(huì)對最后一個(gè)不滿足查詢的元組前面加間隙鎖。
2. 范圍查詢時(shí)，如會(huì)對最后一個(gè)不滿足查詢的元組前面加間隙鎖，所有滿足查詢的元組加臨鍵鎖。

間隙鎖和臨鍵鎖能夠有效避免幻讀現(xiàn)象，即其他事務(wù)新增了當(dāng)前事務(wù)未曾讀取的數(shù)據(jù)，如果其他事務(wù)再回滾刪除該數(shù)據(jù)，則當(dāng)前事務(wù)又無法查看該數(shù)據(jù)，造成數(shù)據(jù)時(shí)有時(shí)無，如同幻覺。

元組前面加間隙鎖，所有滿足查詢的元組加臨鍵鎖。

間隙鎖和臨鍵鎖能夠有效避免幻讀現(xiàn)象，即其他事務(wù)新增了當(dāng)前事務(wù)未曾讀取的數(shù)據(jù)，如果其他事務(wù)再回滾刪除該數(shù)據(jù)，則當(dāng)前事務(wù)又無法查看該數(shù)據(jù)，造成數(shù)據(jù)時(shí)有時(shí)無，如同幻覺。