包含postgresql死鎖的詞條

SQL基礎教程的目錄

1.1　運行SQL程序

創(chuàng)新互聯堅持“要么做到，要么別承諾”的工作理念，服務領域包括：網站設計、網站制作、企業(yè)官網、英文網站、手機端網站、網站推廣等服務，滿足客戶于互聯網時代的西固網站設計、移動媒體設計的需求，幫助企業(yè)找到有效的互聯網解決方案。努力成為您成熟可靠的網絡建設合作伙伴！

1.2　Microsoft Access

1.3　Microsoft SQL Server

1.3.1　SQL Server 2000

1.3.2　SQL Server 2005/2008

1.4　Oracle

1.5　IBM DB2

1.6　MySQL

1.7　PostgreSQL 2.1　表、列和行

2.1.1　表

2.1.2　列

2.1.3　行

2.2　主鍵

2.3　外鍵

2.4　聯系

2.4.1　一對一

2.4.2　一對多

2.4.3　多對多

2.5　規(guī)范化

2.5.1　第一范式

2.5.2　第二范式

2.5.3　第三范式

2.5.4　其他范式

2.6　示例數據庫

2.6.1　表authors

2.6.2　表publishers

2.6.3　表titles

2.6.4　表titles_authors

2.6.5　表royalties

2.7　創(chuàng)建示例數據庫 3.1　SQL語法

3.2　SQL標準和一致性

3.3　標識符

3.4　數據類型

3.5　字符串類型

3.6　二進制大型對象類型

3.7　精確數字類型

3.8　近似數字類型

3.9　布爾類型

3.10　日期和時間類型

3.11　時間間隔類型

3.12　唯一標識符

3.13　其他數據類型

3.14　空值 4.1　使用SELECT和FROM檢索列

4.2　使用AS創(chuàng)建列的別名

4.3　使用DISTINCT消除重復的行

4.4　使用ORDER BY排序行

4.5　使用WHERE篩選行

4.6　使用AND、OR和NOT組合及求反條件

4.6.1　AND操作符

4.6.2　OR操作符

4.6.3　NOT操作符

4.6.4　AND、OR和NOT一起使用

4.7　使用LIKE匹配模式

4.8　使用BETWEEN進行范圍篩選

4.9　使用IN進行列表篩選

4.10　使用IS NULL測試空值 5.1　創(chuàng)建派生列

5.2　執(zhí)行算術運算

5.3　確定計算的順序

5.4　使用||連接串

5.5　使用SUBSTRING()提取子串

5.6　使用UPPER()和LOWER()更改串的大小寫

5.7　使用TRIM()修整字符

5.8　使用CHARACTER_LENGTH()得到串長度

5.9　使用POSITION()查找子串

5.10　執(zhí)行日期及時間間隔運算

5.11　獲得當前日期和時間

5.12　獲得用戶信息

5.13　使用CAST()轉換數據類型

5.14　使用CASE計算條件值

5.15　使用COALESCE()檢查空值

5.16　使用NULLIF()比較表達式 6.1　使用聚合函數

6.2　創(chuàng)建聚合表達式

6.3　使用MIN()查找最小值

6.4　使用MAX()查找最大值

6.5　使用SUM()計算總和

6.6　使用AVG()計算平均值

6.7　使用COUNT()統(tǒng)計行數

6.8　使用DISTINCT聚合不重復的值

6.9　使用GROUP BY分組行

6.10　使用HAVING篩選分組 7.1　限定列名

7.2　使用AS創(chuàng)建表的別名

7.3　使用聯結

7.4　使用JOIN或WHERE創(chuàng)建聯結

7.5　使用CROSS JOIN創(chuàng)建交叉聯結

7.6　使用NATURAL JOIN創(chuàng)建自然聯結

7.7　使用INNER JOIN創(chuàng)建內聯結

7.8　使用OUTER JOIN創(chuàng)建外聯結

7.9　創(chuàng)建自聯結 8.1　理解子查詢

8.2　子查詢語法

8.3　子查詢和聯結

8.4　簡單子查詢和相關子查詢

8.4.1　簡單子查詢

8.4.2　相關子查詢

8.5　在子查詢中限定列名

8.6　子查詢中的空值

8.7　使用子查詢作為列表達式

8.8　使用比較操作符比較子查詢的值

8.9　使用IN測試集合成員資格

8.10　使用ALL比較所有子查詢的值

8.11　使用ANY比較某些子查詢的值

8.12　使用EXISTS檢測存在性

8.13　比較等價查詢 9.1　使用UNION合并行

9.2　使用INTERSECT查找相同行

9.3　使用EXCEPT查找不同行 10.1　顯示表結構

10.2　使用INSERT插入行

10.3　使用UPDATE更新行

10.4　使用DELETE刪除行 11.1　創(chuàng)建表

11.2　理解約束

11.3　使用CREATE TABLE創(chuàng)建新表

11.4　使用NOT NULL禁止空值

11.5　使用DEFAULT確定默認值

11.6　使用PRIMARY KEY指定主鍵

11.7　使用FOREIGN KEY指定外鍵

11.8　使用UNIQUE確保值唯一

11.9　使用CHECK創(chuàng)建檢查約束

11.10　使用CREATE TEMPORARY TABLE創(chuàng)建臨時表

11.11　使用CREATE TABLE AS利用已存在表創(chuàng)建新表

11.12　使用ALTER TABLE修改表

11.13　使用DROP TABLE刪除表 12.1　使用CREATE INDEX創(chuàng)建索引

12.2　使用DROP INDEX刪除索引 13.1　使用CREATE VIEW創(chuàng)建視圖

13.2　通過視圖檢索數據

13.3　通過視圖修改數據

13.3.1　通過視圖插入行

13.3.2　通過視圖更新行

13.3.3　通過視圖刪除行

13.4　使用DROP VIEW刪除視圖 15.1　動態(tài)統(tǒng)計

15.2　產生序列

15.3　發(fā)現等差數列、遞增數列和等值數列

15.4　限定返回行的數量

15.4.1　Microsoft Access

15.4.2　Microsoft SQL Server

15.4.3　Oracle

15.4.4　IBM DB2

15.4.5　MySQL

15.4.6　PostgreSQL

15.5　分配排名

15.6　計算修整均值

15.7　隨機選取行

15.8　處理重復值

15.9　創(chuàng)建電話列表

15.10　檢索元數據

15.10.1　Microsoft Access

15.10.2　Microsoft SQL Server

15.10.3　Oracle

15.10.4　IBM DB2

15.10.5　MySQL

15.10.6　PostgreSQL

15.11　處理日期

15.11.1　Microsoft Access

15.11.2　Microsoft SQL Server

15.11.3　Oracle

15.11.4　IBM DB2

15.11.5　MySQL

15.11.6　PostgreSQL

15.12　計算中值

15.13　查詢極值

15.14　改變動態(tài)統(tǒng)計的中流

15.15　旋轉結果

15.16　處理層次結構

索引

分庫分表 VS newsql數據庫

最近與同行 科技 交流，經常被問到分庫分表與分布式數據庫如何選擇，網上也有很多關于中間件+傳統(tǒng)關系數據庫（分庫分表）與NewSQL分布式數據庫的文章，但有些觀點與判斷是我覺得是偏激的，脫離環(huán)境去評價方案好壞其實有失公允。

本文通過對兩種模式關鍵特性實現原理對比，希望可以盡可能客觀、中立的闡明各自真實的優(yōu)缺點以及適用場景。

首先關于“中間件+關系數據庫分庫分表”算不算NewSQL分布式數據庫問題，國外有篇論文pavlo-newsql-sigmodrec，如果根據該文中的分類，Spanner、TiDB、OB算是第一種新架構型，Sharding-Sphere、Mycat、DRDS等中間件方案算是第二種（文中還有第三種云數據庫，本文暫不詳細介紹）。

基于中間件（包括SDK和Proxy兩種形式）+傳統(tǒng)關系數據庫（分庫分表）模式是不是分布式架構？我覺得是的，因為存儲確實也分布式了，也能實現橫向擴展。但是不是"偽"分布式數據庫？從架構先進性來看，這么說也有一定道理。"偽"主要體現在中間件層與底層DB重復的SQL解析與執(zhí)行計劃生成、存儲引擎基于B+Tree等，這在分布式數據庫架構中實際上冗余低效的。為了避免引起真?zhèn)畏植际綌祿斓目谒畱?zhàn)，本文中NewSQL數據庫特指這種新架構NewSQL數據庫。

NewSQL數據庫相比中間件+分庫分表的先進在哪兒？畫一個簡單的架構對比圖：

這些大多也是NewSQL數據庫產品主要宣傳的點，不過這些看起來很美好的功能是否真的如此？接下來針對以上幾點分別闡述下的我的理解。

這是把雙刃劍。

CAP限制

想想更早些出現的NoSQL數據庫為何不支持分布式事務（最新版的mongoDB等也開始支持了），是缺乏理論與實踐支撐嗎？并不是，原因是CAP定理依然是分布式數據庫頭上的頸箍咒，在保證強一致的同時必然會犧牲可用性A或分區(qū)容忍性P。為什么大部分NoSQL不提供分布式事務？

那么NewSQL數據庫突破CAP定理限制了嗎？并沒有。NewSQL數據庫的鼻主Google Spanner（目前絕大部分分布式數據庫都是按照Spanner架構設計的）提供了一致性和大于5個9的可用性，宣稱是一個“實際上是CA”的，其真正的含義是 系統(tǒng)處于 CA 狀態(tài)的概率非常高，由于網絡分區(qū)導致的服務停用的概率非常小 ，究其真正原因是其打造私有全球網保證了不會出現網絡中斷引發(fā)的網絡分區(qū)，另外就是其高效的運維隊伍,這也是cloud spanner的賣點。詳細可見CAP提出者Eric Brewer寫的《Spanner, TrueTime 和CAP理論》。

完備性 ：

兩階段提交協議是否嚴格支持ACID，各種異常場景是不是都可以覆蓋？

2PC在commit階段發(fā)送異常，其實跟最大努力一階段提交類似也會有部分可見問題，嚴格講一段時間內并不能保證A原子性和C一致性（待故障恢復后recovery機制可以保證最終的A和C）。完備的分布式事務支持并不是一件簡單的事情，需要可以應對網絡以及各種硬件包括網卡、磁盤、CPU、內存、電源等各類異常，通過嚴格的測試。之前跟某友商交流，他們甚至說目前已知的NewSQL在分布式事務支持上都是不完整的，他們都有案例跑不過，圈內人士這么篤定，也說明了 分布式事務的支持完整程度其實是層次不齊的。

但分布式事務又是這些NewSQL數據庫的一個非常重要的底層機制，跨資源的DML、DDL等都依賴其實現，如果這塊的性能、完備性打折扣，上層跨分片SQL執(zhí)行的正確性會受到很大影響。

性能

傳統(tǒng)關系數據庫也支持分布式事務XA，但為何很少有高并發(fā)場景下用呢？ 因為XA的基礎兩階段提交協議存在網絡開銷大，阻塞時間長、死鎖等問題，這也導致了其實際上很少大規(guī)模用在基于傳統(tǒng)關系數據庫的OLTP系統(tǒng)中。

NewSQL數據庫的分布式事務實現也仍然多基于兩階段提交協議，例如google percolator分布式事務模型，

采用原子鐘+MVCC+ Snapshot Isolation（SI），這種方式通過TSO(Timestamp Oracle)保證了全局一致性，通過MVCC避免了鎖，另外通過primary lock和secondary lock將提交的一部分轉為異步，相比XA確實提高了分布式事務的性能。

但不管如何優(yōu)化，相比于1PC，2PC多出來的GID獲取、網絡開銷、prepare日志持久化還是會帶來很大的性能損失，尤其是跨節(jié)點的數量比較多時會更加顯著，例如在銀行場景做個批量扣款，一個文件可能上W個賬戶，這樣的場景無論怎么做還是吞吐都不會很高。

雖然NewSQL分布式數據庫產品都宣傳完備支持分布式事務，但這并不是說應用可以完全不用關心數據拆分，這些數據庫的最佳實踐中仍然會寫到，應用的大部分場景盡可能避免分布式事務。

既然強一致事務付出的性能代價太大，我們可以反思下是否真的需要這種強一致的分布式事務？尤其是在做微服務拆分后，很多系統(tǒng)也不太可能放在一個統(tǒng)一的數據庫中。嘗試將一致性要求弱化，便是柔性事務，放棄ACID(Atomicity,Consistency, Isolation, Durability)，轉投BASE(Basically Available,Soft state,Eventually consistent)，例如Saga、TCC、可靠消息保證最終一致等模型，對于大規(guī)模高并發(fā)OLTP場景，我個人更建議使用柔性事務而非強一致的分布式事務。關于柔性事務，筆者之前也寫過一個技術組件，最近幾年也涌現出了一些新的模型與框架（例如阿里剛開源的Fescar），限于篇幅不再贅述，有空再單獨寫篇文章。

HA與異地多活

主從模式并不是最優(yōu)的方式，就算是半同步復制，在極端情況下（半同步轉異步）也存在丟數問題，目前業(yè)界公認更好的方案是基于paxos分布式一致性協議或者其它類paxos如raft方式，Google Spanner、TiDB、cockcoachDB、OB都采用了這種方式，基于Paxos協議的多副本存儲，遵循過半寫原則，支持自動選主，解決了數據的高可靠，縮短了failover時間，提高了可用性，特別是減少了運維的工作量，這種方案技術上已經很成熟，也是NewSQL數據庫底層的標配。

當然這種方式其實也可以用在傳統(tǒng)關系數據庫，阿里、微信團隊等也有將MySQL存儲改造支持paxos多副本的，MySQL也推出了官方版MySQL Group Cluster，預計不遠的未來主從模式可能就成為 歷史 了。

需要注意的是很多NewSQL數據庫廠商宣傳基于paxos或raft協議可以實現【異地多活】，這個實際上是有前提的，那就是異地之間網絡延遲不能太高 。以銀行“兩地三中心”為例，異地之間多相隔數千里，延時達到數十毫秒，如果要多活，那便需異地副本也參與數據庫日志過半確認，這樣高的延時幾乎沒有OLTP系統(tǒng)可以接受的。

數據庫層面做異地多活是個美好的愿景，但距離導致的延時目前并沒有好的方案。 之前跟螞蟻團隊交流，螞蟻異地多活的方案是在應用層通過MQ同步雙寫交易信息，異地DC將交易信息保存在分布式緩存中，一旦發(fā)生異地切換，數據庫同步中間件會告之數據延遲時間，應用從緩存中讀取交易信息，將這段時間內涉及到的業(yè)務對象例如用戶、賬戶進行黑名單管理，等數據同步追上之后再將這些業(yè)務對象從黑名單中剔除。由于雙寫的不是所有數據庫操作日志而只是交易信息，數據延遲只影響一段時間內數據，這是目前我覺得比較靠譜的異地度多活方案。

另外有些系統(tǒng)進行了單元化改造，這在paxos選主時也要結合考慮進去，這也是目前很多NewSQL數據庫欠缺的功能。

Scale橫向擴展與分片機制

paxos算法解決了高可用、高可靠問題，并沒有解決Scale橫向擴展的問題，所以分片是必須支持的。NewSQL數據庫都是天生內置分片機制的，而且會根據每個分片的數據負載(磁盤使用率、寫入速度等)自動識別熱點，然后進行分片的分裂、數據遷移、合并，這些過程應用是無感知的，這省去了DBA的很多運維工作量。以TiDB為例，它將數據切成region，如果region到64M時，數據自動進行遷移。

分庫分表模式下需要應用設計之初就要明確各表的拆分鍵、拆分方式（range、取模、一致性哈?；蛘咦远x路由表）、路由規(guī)則、拆分庫表數量、擴容方式等。相比NewSQL數據庫，這種模式給應用帶來了很大侵入和復雜度，這對大多數系統(tǒng)來說也是一大挑戰(zhàn)。

這里有個問題是NewSQL數據庫統(tǒng)一的內置分片策略（例如tidb基于range）可能并不是最高效的，因為與領域模型中的劃分要素并不一致，這導致的后果是很多交易會產生分布式事務。 舉個例子，銀行核心業(yè)務系統(tǒng)是以客戶為維度，也就是說客戶表、該客戶的賬戶表、流水表在絕大部分場景下是一起寫的，但如果按照各表主鍵range進行分片，這個交易并不能在一個分片上完成，這在高頻OLTP系統(tǒng)中會帶來性能問題。

分布式SQL支持

常見的單分片SQL，這兩者都能很好支持。NewSQL數據庫由于定位與目標是一個通用的數據庫，所以支持的SQL會更完整，包括跨分片的join、聚合等復雜SQL。中間件模式多面向應用需求設計，不過大部分也支持帶拆分鍵SQL、庫表遍歷、單庫join、聚合、排序、分頁等。但對跨庫的join以及聚合支持就不夠了。

NewSQL數據庫一般并不支持存儲過程、視圖、外鍵等功能，而中間件模式底層就是傳統(tǒng)關系數據庫，這些功能如果只是涉及單庫是比較容易支持的。

NewSQL數據庫往往選擇兼容MySQL或者PostgreSQL協議，所以SQL支持僅局限于這兩種，中間件例如驅動模式往往只需做簡單的SQL解析、計算路由、SQL重寫，所以可以支持更多種類的數據庫SQL。

SQL支持的差異主要在于分布式SQL執(zhí)行計劃生成器，由于NewSQL數據庫具有底層數據的分布、統(tǒng)計信息，因此可以做CBO，生成的執(zhí)行計劃效率更高，而中間件模式下沒有這些信息，往往只能基于規(guī)則RBO（Rule-Based-Opimization），這也是為什么中間件模式一般并不支持跨庫join，因為實現了效率也往往并不高，還不如交給應用去做。

存儲引擎

傳統(tǒng)關系數據庫的存儲引擎設計都是面向磁盤的，大多都基于B+樹。B+樹通過降低樹的高度減少隨機讀、進而減少磁盤尋道次數，提高讀的性能，但大量的隨機寫會導致樹的分裂，從而帶來隨機寫，導致寫性能下降。NewSQL的底層存儲引擎則多采用LSM，相比B+樹LSM將對磁盤的隨機寫變成順序寫，大大提高了寫的性能。不過LSM的的讀由于需要合并數據性能比B+樹差，一般來說LSM更適合應在寫大于讀的場景。當然這只是單純數據結構角度的對比，在數據庫實際實現時還會通過SSD、緩沖、bloom filter等方式優(yōu)化讀寫性能，所以讀性能基本不會下降太多。NewSQL數據由于多副本、分布式事務等開銷，相比單機關系數據庫SQL的響應時間并不占優(yōu)，但由于集群的彈性擴展，整體QPS提升還是很明顯的，這也是NewSQL數據庫廠商說分布式數據庫更看重的是吞吐，而不是單筆SQL響應時間的原因。

成熟度與生態(tài)

分布式數據庫是個新型通用底層軟件，準確的衡量與評價需要一個多維度的測試模型，需包括發(fā)展現狀、使用情況、社區(qū)生態(tài)、監(jiān)控運維、周邊配套工具、功能滿足度、DBA人才、SQL兼容性、性能測試、高可用測試、在線擴容、分布式事務、隔離級別、在線DDL等等，雖然NewSQL數據庫發(fā)展經過了一定時間檢驗，但多集中在互聯網以及傳統(tǒng)企業(yè)非核心交易系統(tǒng)中，目前還處于快速迭代、規(guī)模使用不斷優(yōu)化完善的階段。

相比而言，傳統(tǒng)關系數據庫則經過了多年的發(fā)展，通過完整的評測，在成熟度、功能、性能、周邊生態(tài)、風險把控、相關人才積累等多方面都具有明顯優(yōu)勢，同時對已建系統(tǒng)的兼容性也更好。

對于互聯網公司，數據量的增長壓力以及追求新技術的基因會更傾向于嘗試NewSQL數據庫，不用再考慮庫表拆分、應用改造、擴容、事務一致性等問題怎么看都是非常吸引人的方案。

對于傳統(tǒng)企業(yè)例如銀行這種風險意識較高的行業(yè)來說，NewSQL數據庫則可能在未來一段時間內仍處于 探索 、審慎試點的階段?；谥虚g件+分庫分表模式架構簡單，技術門檻更低，雖然沒有NewSQL數據庫功能全面，但大部分場景最核心的訴求也就是拆分后SQL的正確路由，而此功能中間件模式應對還是綽綽有余的，可以說在大多數OLTP場景是夠用的。

限于篇幅，其它特性例如在線DDL、數據遷移、運維工具等特性就不在本文展開對比。

總結

如果看完以上內容，您還不知道選哪種模式，那么結合以下幾個問題，先思考下NewSQL數據庫解決的點對于自身是不是真正的痛點：

如果以上有2到3個是肯定的，那么你可以考慮用NewSQL數據庫了，雖然前期可能需要一定的學習成本，但它是數據庫的發(fā)展方向，未來收益也會更高，尤其是互聯網行業(yè)，隨著數據量的突飛猛進，分庫分表帶來的痛苦會與日俱增。當然選擇NewSQL數據庫你也要做好承擔一定風險的準備。

如果你還未做出抉擇，不妨再想想下面幾個問題：

如果這些問題有多數是肯定的，那還是分庫分表吧。在軟件領域很少有完美的解決方案，NewSQL數據庫也不是數據分布式架構的銀彈。相比而言分庫分表是一個代價更低、風險更小的方案，它最大程度復用傳統(tǒng)關系數據庫生態(tài)，通過中間件也可以滿足分庫分表后的絕大多數功能，定制化能力更強。 在當前NewSQL數據庫還未完全成熟的階段，分庫分表可以說是一個上限低但下限高的方案，尤其傳統(tǒng)行業(yè)的核心系統(tǒng)，如果你仍然打算把數據庫當做一個黑盒產品來用，踏踏實實用好分庫分表會被認為是個穩(wěn)妥的選擇。

很多時候軟件選型取決于領域特征以及架構師風格，限于筆者知識與所屬行業(yè)特點所限，以上僅為個人粗淺的一些觀點，歡迎討論。

PostgreSQL中怎么關閉死鎖的進程

今天在進行測試的時候，發(fā)現一個數據表無法進行更新。一旦 運行更新sql，就進行等待。無法執(zhí)行下去。感覺數據庫進行了死鎖。由于使用的PostgreSQL數據庫，沒有資料。只好進行谷歌。最終在一個英文論壇中發(fā)現了解決方法。如下：1.檢索出死鎖進程的ID。SELECT * FROM pg_stat_activity WHERE datname='死鎖的數據庫ID ';檢索出來的字段中，【wating 】字段，數據為t的那條，就是死鎖的進程。找到對應的【procpid 】列的值。2.將進程殺掉。

數據庫為什么要分庫分表

1 基本思想之什么是分庫分表？

從字面上簡單理解，就是把原本存儲于一個庫的數據分塊存儲到多個庫上，把原本存儲于一個表的數據分塊存儲到多個表上。

2 基本思想之為什么要分庫分表？

數

據庫中的數據量不一定是可控的，在未進行分庫分表的情況下，隨著時間和業(yè)務的發(fā)展，庫中的表會越來越多，表中的數據量也會越來越大，相應地，數據操作，增

刪改查的開銷也會越來越大；另外，由于無法進行分布式式部署，而一臺服務器的資源（CPU、磁盤、內存、IO等）是有限的，最終數據庫所能承載的數據量、

數據處理能力都將遭遇瓶頸。

3 分庫分表的實施策略。

分庫分表有垂直切分和水平切分兩種。

3.1

何謂垂直切分，即將表按照功能模塊、關系密切程度劃分出來，部署到不同的庫上。例如，我們會建立定義數據庫workDB、商品數據庫payDB、用戶數據

庫userDB、日志數據庫logDB等，分別用于存儲項目數據定義表、商品定義表、用戶數據表、日志數據表等。

3.2

何謂水平切分，當一個表中的數據量過大時，我們可以把該表的數據按照某種規(guī)則，例如userID散列，進行劃分，然后存儲到多個結構相同的表，和不同的庫

上。例如，我們的userDB中的用戶數據表中，每一個表的數據量都很大，就可以把userDB切分為結構相同的多個userDB：part0DB、

part1DB等，再將userDB上的用戶數據表userTable，切分為很多userTable：userTable0、userTable1等，

然后將這些表按照一定的規(guī)則存儲到多個userDB上。

3.3 應該使用哪一種方式來實施數據庫分庫分表，這要看數據庫中數據量的瓶頸所在，并綜合項目的業(yè)務類型進行考慮。

如果數據庫是因為表太多而造成海量數據，并且項目的各項業(yè)務邏輯劃分清晰、低耦合，那么規(guī)則簡單明了、容易實施的垂直切分必是首選。

而

如果數據庫中的表并不多，但單表的數據量很大、或數據熱度很高，這種情況之下就應該選擇水平切分，水平切分比垂直切分要復雜一些，它將原本邏輯上屬于一體

的數據進行了物理分割，除了在分割時要對分割的粒度做好評估，考慮數據平均和負載平均，后期也將對項目人員及應用程序產生額外的數據管理負擔。

在現實項目中，往往是這兩種情況兼而有之，這就需要做出權衡，甚至既需要垂直切分，又需要水平切分。我們的游戲項目便綜合使用了垂直與水平切分，我們首先對數據庫進行垂直切分，然后，再針對一部分表，通常是用戶數據表，進行水平切分。

4 分庫分表存在的問題。

4.1 事務問題。

在執(zhí)行分庫分表之后，由于數據存儲到了不同的庫上，數據庫事務管理出現了困難。如果依賴數據庫本身的分布式事務管理功能去執(zhí)行事務，將付出高昂的性能代價；如果由應用程序去協助控制，形成程序邏輯上的事務，又會造成編程方面的負擔。

4.2 跨庫跨表的join問題。

在執(zhí)行了分庫分表之后，難以避免會將原本邏輯關聯性很強的數據劃分到不同的表、不同的庫上，這時，表的關聯操作將受到限制，我們無法join位于不同分庫的表，也無法join分表粒度不同的表，結果原本一次查詢能夠完成的業(yè)務，可能需要多次查詢才能完成。

4.3 額外的數據管理負擔和數據運算壓力。

額

外的數據管理負擔，最顯而易見的就是數據的定位問題和數據的增刪改查的重復執(zhí)行問題，這些都可以通過應用程序解決，但必然引起額外的邏輯運算，例如，對于

一個記錄用戶成績的用戶數據表userTable，業(yè)務要求查出成績最好的100位，在進行分表之前，只需一個order

by語句就可以搞定，但是在進行分表之后，將需要n個order

by語句，分別查出每一個分表的前100名用戶數據，然后再對這些數據進行合并計算，才能得出結果。

如何解決postgresql 錯誤：08p01：無效的信息格式

Appendix A. PostgreSQL錯誤代碼

PostgreSQL服務器發(fā)出的所有消息都賦予 了五個字符的錯誤代碼，這些代碼遵循 SQL 的"SQLSTATE" 代碼的習慣。需要知道發(fā)生了什么錯誤條件的應用通常應該測試錯誤代碼， 而不是查看文本錯誤信息。這些錯誤代碼輕易不會隨著PostgreSQL 的版本更新而修改，并且一般也不會隨著錯誤信息的本地化而發(fā)生修改。 請注意有些(但不是全部)PostgreSQL生成的錯誤代碼是 由 SQL 標準定義的；有些標準沒有定義的錯誤條件是發(fā)明的或者是從其它數據庫借來的。

根據標準，錯誤代碼的頭兩個字符表示錯誤類別， 而后三個字符表示在該類別內特定的條件。因此， 那些不能識別特定錯誤代碼的應用仍然可以從錯誤類別中推斷要做什么。

Table A-1里面列出了PostgreSQL 8.2.3 定義的所有錯誤代碼(有些實際上目前并沒有使用， 但是 SQL 標準定義了)。錯誤類別也列出在此。對于每個錯誤類別都有個 "標準"的錯誤代碼，它的最后三個字符是000。 這個代碼只用于那些落在該類別內，但是沒有賦予任何更準確的代碼的錯誤條件。

PL/pgSQL用于每個錯誤代碼的條件名和表中顯示的措辭相同， 只是用下劃線代替了空白。比如，代碼22012, DIVISION BY ZERO 的條件名是DIVISION_BY_ZERO。 條件名大小寫無關。(請注意PL/pgSQL并不識別警告，這一點和錯誤、條件名正相反；那些是 00, 01, 02 類別。)

Table A-1. PostgreSQL 錯誤代碼

錯誤代碼

含義

常量名

Class 00 — 成功完成

00000 成功完成 successful_completion

Class 01 — 警告

01000 警告 warning

0100C 返回了動態(tài)結果 dynamic_result_sets_returned

01008 警告，隱含補齊了零比特位 implicit_zero_bit_padding

01003 在集合函數里消除null null_value_eliminated_in_set_function

01007 沒有賦予權限 privilege_not_granted

01006 沒有撤銷權限 privilege_not_revoked

01004 字符串數據在右端截斷 string_data_right_truncation

01P01 廢棄的特性 deprecated_feature

Class 02 — 沒有數據(按照 SQL 標準的要求，這也是警告類)

02000 沒有數據 no_data

02001 返回了沒有附加動態(tài)結果集 no_additional_dynamic_result_sets_returned

Class 03 — SQL語句尚未結束

03000 SQL語句尚未結束 sql_statement_not_yet_complete

Class 08 — 連接異常

08000 連接異常 connection_exception

08003 連接不存在 connection_does_not_exist

08006 連接失敗 connection_failure

08001 SQL 客戶端不能建立 SQL 連接 sqlclient_unable_to_establish_sqlconnection

08004 SQL 服務器拒絕建立 SQL 連接 sqlserver_rejected_establishment_of_sqlconnection

08007 未知的事務解析 transaction_resolution_unknown

08P01 違反協議 protocol_violation

Class 09 — Triggered Action Exception觸發(fā)器動作異常

09000 觸發(fā)器動作異常 triggered_action_exception

Class 0A — 不支持特性

0A000 不支持此特性 feature_not_supported

Class 0B — 非法事務初始化

0B000 非法事務初始化 invalid_transaction_initiation

Class 0F — 定位器異常

0F000 定位器異常 locator_exception

0F001 非法的定位器聲明 invalid_locator_specification

Class 0L — 非法賦權者

0L000 非法賦權者 invalid_grantor

0LP01 非法賦權操作 invalid_grant_operation

Class 0P — 非法角色聲明

0P000 非法角色聲明 invalid_role_specification

Class 20 — 未發(fā)現情況

20000 未發(fā)現情況 case_not_found

Class 21 — 勢違例

21000 勢違例 cardinality_violation

Class 22 — 數據異常

22000 數據異常 data_exception

2202E 數組下標錯誤 array_subscript_error

22021 字符不在規(guī)定范圍內 character_not_in_repertoire

22008 日期時間字段溢出 datetime_field_overflow

22012 被零除 division_by_zero

22005 賦值中出錯 error_in_assignment

2200B 逃逸字符沖突 escape_character_conflict

22022 INDICATOR OVERFLOW指示器溢出 indicator_overflow

22015 內部字段溢出 interval_field_overflow

2201E 對數運算的非法參數 invalid_argument_for_logarithm

22014 NTILE函數的無效參數 invalid_argument_for_ntile_function

22016 N倍函數的無效參數 invalid_argument_for_nth_value_function

2201F 指數函數的無效參數 invalid_argument_for_power_function

2201G BUCKET函數的非法參數 invalid_argument_for_width_bucket_function

22018 類型轉換時非法的字符值 invalid_character_value_for_cast

22007 非法日期時間格式 invalid_datetime_format

22019 非法的逃逸字符 invalid_escape_character

2200D 非法的逃逸字節(jié) invalid_escape_octet

22025 非法逃逸序列 invalid_escape_sequence

22P06 非標準使用逃逸字符 nonstandard_use_of_escape_character

22010 非法指示器參數值 invalid_indicator_parameter_value

22023 非法參數值 invalid_parameter_value

2201B 非法正則表達式 invalid_regular_expression

2201W 無效的行數限制 invalid_row_count_in_limit_clause

2201X 在結果抵消子句中無效的行數 invalid_row_count_in_result_offset_clause

22009 非法時區(qū)顯示值 invalid_time_zone_displacement_value

2200C 逃逸字符的非法使用 invalid_use_of_escape_character

2200G 最相關類型不匹配 most_specific_type_mismatch

22004 不允許 NULL 值 null_value_not_allowed

22002 NULL 值不能做指示器參數 null_value_no_indicator_parameter

22003 數字值超出范圍 numeric_value_out_of_range

22026 字符串數據長度不匹配 string_data_length_mismatch

22001 字符串數據右邊被截斷 string_data_right_truncation

22011 抽取子字符串錯誤 substring_error

22027 截斷錯誤 trim_error

22024 未結束的 C 字符串 unterminated_c_string

2200F 零長度的字符串 zero_length_character_string

22P01 浮點異常 floating_point_exception

22P02 非法文本表現形式 invalid_text_representation

22P03 非法二進制表現形式 invalid_binary_representation

22P04 錯誤的COPY文件格式 bad_copy_file_format

22P05 不可翻譯字符 untranslatable_character

2200L 不是一個XML文檔 not_an_xml_document

2200M 無效的XML文檔 invalid_xml_document

2200N 無效的XML內容 invalid_xml_content

2200S 無效的XML評論 invalid_xml_comment

2200T 無效的XML處理指令 invalid_xml_processing_instruction

Class 23 — 違反完整性約束

23000 違反完整性約束 integrity_constraint_violation

23001 約束限制 restrict_violation

23502 NOT NULL VIOLATION違反非空 not_null_violation

23503 違反外鍵約束 foreign_key_violation

23505 違反唯一約束 unique_violation

23514 違反檢查 check_violation

23P01 違反排除 exclusion_violation

Class 24 — 非法游標狀態(tài)

24000 非法游標狀態(tài) invalid_cursor_state

Class 25 — 非法事務狀態(tài)

25000 非法事務狀態(tài) invalid_transaction_state

25001 活躍的SQL狀態(tài) active_sql_transaction

25002 分支事務已經激活 branch_transaction_already_active

25008 持有的指針要求同樣的隔離級別 held_cursor_requires_same_isolation_level

25003 對分支事務的不恰當的訪問方式 inappropriate_access_mode_for_branch_transaction

25004 對分支事務的不恰當的隔離級別 inappropriate_isolation_level_for_branch_transaction

25005 分支事務沒有活躍的SQL事務 no_active_sql_transaction_for_branch_transaction

25006 只讀的SQL事務 read_only_sql_transaction

25007 不支持混和的模式和數據語句 schema_and_data_statement_mixing_not_supported

25P01 沒有活躍的SQL事務 no_active_sql_transaction

25P02 在失敗的SQL事務中 in_failed_sql_transaction

Class 26 — 非法SQL語句名

26000 非法SQL語句名 invalid_sql_statement_name

Class 27 — 觸發(fā)數據更改違規(guī)

27000 觸發(fā)數據更改違規(guī) triggered_data_change_violation

Class 28 — 非法授權聲明

28000 非法授權聲明 invalid_authorization_specification

28P01 非法密碼 invalid_password

Class 2B — 依然存在依賴的優(yōu)先級描述符

2B000 依然存在依賴的優(yōu)先級描述符 dependent_privilege_descriptors_still_exist

2BP01 依賴性對象仍然存在 dependent_objects_still_exist

Class 2D — 非法的事務終止

2D000 非法的事務終止 invalid_transaction_termination

Class 2F — SQL過程異常

2F000 SQL過程異常 sql_routine_exception

2F005 執(zhí)行的函數沒有返回語句 function_executed_no_return_statement

2F002 不允許修改SQL數據 modifying_sql_data_not_permitted

2F003 企圖使用禁止的SQL語句 prohibited_sql_statement_attempted

2F004 不允許讀取SQL數據 reading_sql_data_not_permitted

Class 34 — 非法指針名

34000 非法指針名 invalid_cursor_name

Class 38 — 外部過程異常

38000 外部過程異常 external_routine_exception

38001 不允許包含的SQL containing_sql_not_permitted

38002 不允許修改SQL數據 modifying_sql_data_not_permitted

38003 企圖使用禁止的SQL語句 prohibited_sql_statement_attempted

38004 不允許讀取SQL數據 reading_sql_data_not_permitted

Class 39 — 外部過程調用異常

39000 外部過程調用異常 external_routine_invocation_exception

39001 返回了非法的SQL狀態(tài) invalid_sqlstate_returned

39004 不允許使用NULL null_value_not_allowed

39P01 違反觸發(fā)器協議 trigger_protocol_violated

39P02 違反 SRF 協議 srf_protocol_violated

Class 3B — 保存點異常

3B000 保存點異常 savepoint_exception

3B001 無效的保存點聲明 invalid_savepoint_specification

Class 3D — 非法目錄名

3D000 非法目錄名 invalid_catalog_name

Class 3F — 非法模式名

3F000 非法模式名 invalid_schema_name

Class 40 — 事務回滾

40000 事務回滾 transaction_rollback

40002 違反事務完整性約束 transaction_integrity_constraint_violation

40001 串行化失敗 serialization_failure

40003 不知道語句是否結束 statement_completion_unknown

40P01 偵測到死鎖 deadlock_detected

Class 42 — 語法錯誤或者違反訪問規(guī)則

42000 語法錯誤或者違反訪問規(guī)則 syntax_error_or_access_rule_violation

42601 語法錯誤 syntax_error

42501 權限不夠 insufficient_privilege

42846 無法進行類型轉換 cannot_coerce

42803 分組錯誤 grouping_error

42P20 開窗口錯誤 windowing_error

42P19 非法遞歸 invalid_recursion

42830 非法的外鍵 invalid_foreign_key

42602 非法名稱 invalid_name

42622 名稱過長 name_too_long

42939 保留名稱 reserved_name

42804 數據類型不匹配 datatype_mismatch

42P18 模糊數據類型 indeterminate_datatype

42809 錯誤的對象類型 wrong_object_type

42703 未定義的字段 undefined_column

42883 未定義的函數 undefined_function

42P01 未定義的表 undefined_table

42P02 未定義的參數 undefined_parameter

42704 未定義對象 undefined_object

42701 重復的字段 duplicate_column

42P03 重復的游標 duplicate_cursor

42P04 重復的數據庫 duplicate_database

42723 重復的函數 duplicate_function

42P05 重復的預備語句 duplicate_prepared_statement

42P06 重復的模式 duplicate_schema

42P07 重復的表 duplicate_table

42712 重復的別名 duplicate_alias

42710 重復的對象 duplicate_object

42702 模糊的字段 ambiguous_column

42725 模糊的函數 ambiguous_function

42P08 模糊的參數 ambiguous_parameter

42P09 模糊的別名 ambiguous_alias

42P10 非法字段引用 invalid_column_reference

42611 非法字段定義 invalid_column_definition

42P11 非法游標定義 invalid_cursor_definition

42P12 非法數據庫定義 invalid_database_definition

42P13 非法函數定義 invalid_function_definition

42P14 非法預備語句定義 invalid_prepared_statement_definition

42P15 非法模式定義 invalid_schema_definition

42P16 非法表定義 invalid_table_definition

42P17 非法對象定義 invalid_object_definition

Class 44 — 違反 WITH CHECK 選項

44000 違反 WITH CHECK 選項 with_check_option_violation

事務的隔離級別 全部都是共享鎖嗎

前言:我們都知道事務的幾種性質，數據庫為了維護這些性質，尤其是一致性和隔離性，一般使用加鎖這種方式。同時數據庫又是個高并發(fā)的應用，同一時間會有大量的并發(fā)訪問，如果加鎖過度，會極大的降低并發(fā)處理能力。所以對于加鎖的處理，可以說就是數據庫對于事務處理的精髓所在。這里通過分析MySQL中InnoDB引擎的加鎖機制，來拋磚引玉，讓讀者更好的理解，在事務處理中數據庫到底做了什么。一次封鎖or兩段鎖？因為有大量的并發(fā)訪問，為了預防死鎖，一般應用中推薦使用一次封鎖法，就是在方法的開始階段，已經預先知道會用到哪些數據，然后全部鎖住，在方法運行之后，再全部解鎖。這種方式可以有效的避免循環(huán)死鎖，但在數據庫中卻不適用，因為在事務開始階段，數據庫并不知道會用到哪些數據。數據庫遵循的是兩段鎖協議，將事務分成兩個階段，加鎖階段和解鎖階段（所以叫兩段鎖）加鎖階段：在該階段可以進行加鎖操作。在對任何數據進行讀操作之前要申請并獲得S鎖（共享鎖，其它事務可以繼續(xù)加共享鎖，但不能加排它鎖），在進行寫操作之前要申請并獲得X鎖（排它鎖，其它事務不能再獲得任何鎖）。加鎖不成功，則事務進入等待狀態(tài)，直到加鎖成功才繼續(xù)執(zhí)行。解鎖階段：當事務釋放了一個封鎖以后，事務進入解鎖階段，在該階段只能進行解鎖操作不能再進行加鎖操作。事務 加鎖/解鎖處理begin； insert into test ..... 加insert對應的鎖update test set... 加update對應的鎖delete from test .... 加delete對應的鎖commit; 事務提交時，同時釋放insert、update、delete對應的鎖這種方式雖然無法避免死鎖，但是兩段鎖協議可以保證事務的并發(fā)調度是串行化（串行化很重要，尤其是在數據恢復和備份的時候）的。事務中的加鎖方式事務的四種隔離級別在數據庫操作中，為了有效保證并發(fā)讀取數據的正確性，提出的事務隔離級別。我們的數據庫鎖，也是為了構建這些隔離級別存在的。隔離級別 臟讀（Dirty Read） 不可重復讀（NonRepeatable Read） 幻讀（Phantom Read）未提交讀（Read uncommitted） 可能 可能 可能 已提交讀（Read committed） 不可能 可能 可能 可重復讀（Repeatable read） 不可能 不可能 可能 可串行化（Serializable ） 不可能 不可能 不可能 未提交讀(Read Uncommitted)：允許臟讀，也就是可能讀取到其他會話中未提交事務修改的數據提交讀(Read Committed)：只能讀取到已經提交的數據。Oracle等多數數據庫默認都是該級別 (不重復讀)可重復讀(Repeated Read)：可重復讀。在同一個事務內的查詢都是事務開始時刻一致的，InnoDB默認級別。在SQL標準中，該隔離級別消除了不可重復讀，但是還存在幻象讀串行讀(Serializable)：完全串行化的讀，每次讀都需要獲得表級共享鎖，讀寫相互都會阻塞Read Uncommitted這種級別，數據庫一般都不會用，而且任何操作都不會加鎖，這里就不討論了。MySQL中鎖的種類MySQL中鎖的種類很多，有常見的表鎖和行鎖，也有新加入的Metadata Lock等等,表鎖是對一整張表加鎖，雖然可分為讀鎖和寫鎖，但畢竟是鎖住整張表，會導致并發(fā)能力下降，一般是做ddl處理時使用。行鎖則是鎖住數據行，這種加鎖方法比較復雜，但是由于只鎖住有限的數據，對于其它數據不加限制，所以并發(fā)能力強，MySQL一般都是用行鎖來處理并發(fā)事務。這里主要討論的也就是行鎖。Read Committed（讀取提交內容）在RC級別中，數據的讀取都是不加鎖的，但是數據的寫入、修改和刪除是需要加鎖的。效果如下MySQL show create table class_teacher \G\ Table: class_teacher Create Table: CREATE TABLE `class_teacher` ( `id` int(11) NOT NULL AUTO_INCREMENT, `class_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `teacher_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `idx_teacher_id` (`teacher_id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 1 row in set (0.02 sec) MySQL select * from class_teacher; +----+--------------+------------+ id class_name teacher_id +----+--------------+------------+ 1 初三一班 1 3 初二一班 2 4 初二二班 2 +----+--------------+------------+ 由于MySQL的InnoDB默認是使用的RR級別，所以我們先要將該session開啟成RC級別，并且設置binlog的模式SET session transaction isolation level read committed; SET SESSION binlog_format = 'ROW'; （或者是MIXED）事務A 事務Bbegin; begin;update class_teacher set class_name='初三二班' where teacher_id=1; update class_teacher set class_name='初三三班' where teacher_id=1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction commit; 為了防止并發(fā)過程中的修改沖突，事務A中MySQL給teacher_id=1的數據行加鎖，并一直不commit（釋放鎖），那么事務B也就一直拿不到該行鎖，wait直到超時。這時我們要注意到，teacher_id是有索引的，如果是沒有索引的class_name呢？update class_teacher set teacher_id=3 where class_name = '初三一班';那么MySQL會給整張表的所有數據行的加行鎖。這里聽起來有點不可思議，但是當sql運行的過程中，MySQL并不知道哪些數據行是 class_name = '初三一班'的（沒有索引嘛），如果一個條件無法通過索引快速過濾，存儲引擎層面就會將所有記錄加鎖后返回，再由MySQL Server層進行過濾。但在實際使用過程當中，MySQL做了一些改進，在MySQL Server過濾條件，發(fā)現不滿足后，會調用unlock_row方法，把不滿足條件的記錄釋放鎖 (違背了二段鎖協議的約束)。這樣做，保證了最后只會持有滿足條件記錄上的鎖，但是每條記錄的加鎖操作還是不能省略的。可見即使是MySQL，為了效率也是會違反規(guī)范的。（參見《高性能MySQL》中文第三版p181）這種情況同樣適用于MySQL的默認隔離級別RR。所以對一個數據量很大的表做批量修改的時候，如果無法使用相應的索引，MySQL Server過濾數據的的時候特別慢，就會出現雖然沒有修改某些行的數據，但是它們還是被鎖住了的現象。Repeatable Read（可重讀）這是MySQL中InnoDB默認的隔離級別。我們姑且分“讀”和“寫”兩個模塊來講解。讀讀就是可重讀，可重讀這個概念是一事務的多個實例在并發(fā)讀取數據時，會看到同樣的數據行，有點抽象，我們來看一下效果。RC（不可重讀）模式下的展現事務A 事務Bbegin; begin; select id,class_name,teacher_id from class_teacher where teacher_id=1; id class_name teacher_id 1 初三二班 1 2 初三一班 1 update class_teacher set class_name='初三三班' where id=1; commit; select id,class_name,teacher_id from class_teacher where teacher_id=1; id class_name teacher_id 1 初三三班 1 2 初三一班 1 讀到了事務B修改的數據，和第一次查詢的結果不一樣，是不可重讀的。commit; 事務B修改id=1的數據提交之后，事務A同樣的查詢，后一次和前一次的結果不一樣，這就是不可重讀（重新讀取產生的結果不一樣）。這就很可能帶來一些問題，那么我們來看看在RR級別中MySQL的表現：事務A 事務B 事務Cbegin; begin; begin; select id,class_name,teacher_id from class_teacher where teacher_id=1; id class_name teacher_id 1 初三二班 1 2 初三一班 1 update class_teacher set class_name='初三三班' where id=1; commit; insert into class_teacher values (null,'初三三班',1); commit; select id,class_name,teacher_id from class_teacher where teacher_id=1; id class_name teacher_id 1 初三二班 1 2 初三一班 1 沒有讀到事務B修改的數據，和第一次sql讀取的一樣，是可重復讀的。沒有讀到事務C新添加的數據。commit; 我們注意到，當teacher_id=1時，事務A先做了一次讀取，事務B中間修改了id=1的數據，并commit之后，事務A第二次讀到的數據和第一次完全相同。所以說它是可重讀的。那么MySQL是怎么做到的呢？這里姑且賣個關子，我們往下看。不可重復讀和幻讀的區(qū)別很多人容易搞混不可重復讀和幻讀，確實這兩者有些相似。但不可重復讀重點在于update和delete，而幻讀的重點在于insert。如果使用鎖機制來實現這兩種隔離級別，在可重復讀中，該sql第一次讀取到數據后，就將這些數據加鎖，其它事務無法修改這些數據，就可以實現可重復讀了。但這種方法卻無法鎖住insert的數據，所以當事務A先前讀取了數據，或者修改了全部數據，事務B還是可以insert數據提交，這時事務A就會發(fā)現莫名其妙多了一條之前沒有的數據，這就是幻讀，不能通過行鎖來避免。需要Serializable隔離級別 ，讀用讀鎖，寫用寫鎖，讀鎖和寫鎖互斥，這么做可以有效的避免幻讀、不可重復讀、臟讀等問題，但會極大的降低數據庫的并發(fā)能力。所以說不可重復讀和幻讀最大的區(qū)別，就在于如何通過鎖機制來解決他們產生的問題。上文說的，是使用悲觀鎖機制來處理這兩種問題，但是MySQL、ORACLE、PostgreSQL等成熟的數據庫，出于性能考慮，都是使用了以樂觀鎖為理論基礎的MVCC（多版本并發(fā)控制）來避免這兩種問題。悲觀鎖和樂觀鎖悲觀鎖正如其名，它指的是對數據被外界（包括本系統(tǒng)當前的其他事務，以及來自外部系統(tǒng)的事務處理）修改持保守態(tài)度，因此，在整個數據處理過程中，將數據處于鎖定狀態(tài)。悲觀鎖的實現，往往依靠數據庫提供的鎖機制（也只有數據庫層提供的鎖機制才能真正保證數據訪問的排他性，否則，即使在本系統(tǒng)中實現了加鎖機制，也無法保證外部系統(tǒng)不會修改數據）。在悲觀鎖的情況下，為了保證事務的隔離性，就需要一致性鎖定讀。讀取數據時給加鎖，其它事務無法修改這些數據。修改刪除數據時也要加鎖，其它事務無法讀取這些數據。樂觀鎖相對悲觀鎖而言，樂觀鎖機制采取了更加寬松的加鎖機制。悲觀鎖大多數情況下依靠數據庫的鎖機制實現，以保證操作最大程度的獨占性。但隨之而來的就是數據庫性能的大量開銷，特別是對長事務而言，這樣的開銷往往無法承受。而樂觀鎖機制在一定程度上解決了這個問題。樂觀鎖，大多是基于數據版本（ Version ）記錄機制實現。何謂數據版本？即為數據增加一個版本標識，在基于數據庫表的版本解決方案中，一般是通過為數據庫表增加一個 “version” 字段來實現。讀取出數據時，將此版本號一同讀出，之后更新時，對此版本號加一。此時，將提交數據的版本數據與數據庫表對應記錄的當前版本信息進行比對，如果提交的數據版本號大于數據庫表當前版本號，則予以更新，否則認為是過期數據。要說明的是，MVCC的實現沒有固定的規(guī)范，每個數據庫都會有不同的實現方式，這里討論的是InnoDB的MVCC。MVCC在MySQL的InnoDB中的實現在InnoDB中，會在每行數據后添加兩個額外的隱藏的值來實現MVCC，這兩個值一個記錄這行數據何時被創(chuàng)建，另外一個記錄這行數據何時過期（或者被刪除）。 在實際操作中，存儲的并不是時間，而是事務的版本號，每開啟一個新事務，事務的版本號就會遞增。 在可重讀Repeatable reads事務隔離級別下：SELECT時，讀取創(chuàng)建版本號=當前事務版本號，刪除版本號為空或當前事務版本號。 INSERT時，保存當前事務版本號為行的創(chuàng)建版本號 DELETE時，保存當前事務版本號為行的刪除版本號 UPDATE時，插入一條新紀錄，保存當前事務版本號為行創(chuàng)建版本號，同時保存當前事務版本號到原來刪除的行 通過MVCC，雖然每行記錄都需要額外的存儲空間，更多的行檢查工作以及一些額外的維護工作，但可以減少鎖的使用，大多數讀操作都不用加鎖，讀數據操作很簡單，性能很好，并且也能保證只會讀取到符合標準的行，也只鎖住必要行。 我們不管從數據庫方面的教課書中學到，還是從網絡上看到，大都是上文中事務的四種隔離級別這一模塊列出的意思，RR級別是可重復讀的，但無法解決幻讀，而只有在Serializable級別才能解決幻讀。于是我就加了一個事務C來展示效果。在事務C中添加了一條teacher_id=1的數據commit，RR級別中應該會有幻讀現象，事務A在查詢teacher_id=1的數據時會讀到事務C新加的數據。但是測試后發(fā)現，在MySQL中是不存在這種情況的，在事務C提交后，事務A還是不會讀到這條數據?？梢娫贛ySQL的RR級別中，是解決了幻讀的讀問題的。參見下圖讀問題解決了，根據MVCC的定義，并發(fā)提交數據時會出現沖突，那么沖突時如何解決呢？我們再來看看InnoDB中RR級別對于寫數據的處理?！白x”與“讀”的區(qū)別可能有讀者會疑惑，事務的隔離級別其實都是對于讀數據的定義，但到了這里，就被拆成了讀和寫兩個模塊來講解。這主要是因為MySQL中的讀，和事務隔離級別中的讀，是不一樣的。我們且看，在RR級別中，通過MVCC機制，雖然讓數據變得可重復讀，但我們讀到的數據可能是歷史數據，是不及時的數據，不是數據庫當前的數據！這在一些對于數據的時效特別敏感的業(yè)務中，就很可能出問題。對于這種讀取歷史數據的方式，我們叫它快照讀 (snapshot read)，而讀取數據庫當前版本數據的方式，叫當前讀 (current read)。很顯然，在MVCC中：快照讀：就是selectselect * from table ....; 當前讀：特殊的讀操作，插入/更新/刪除操作，屬于當前讀，處理的都是當前的數據，需要加鎖。select * from table where ? lock in share mode; select * from table where ? for update; insert; update ; delete; 事務的隔離級別實際上都是定義了當前讀的級別，MySQL為了減少鎖處理（包括等待其它鎖）的時間，提升并發(fā)能力，引入了快照讀的概念，使得select不用加鎖。而update、insert這些“當前讀”，就需要另外的模塊來解決了。寫（"當前讀"）事務的隔離級別中雖然只定義了讀數據的要求，實際上這也可以說是寫數據的要求。上文的“讀”，實際是講的快照讀；而這里說的“寫”就是當前讀了。為了解決當前讀中的幻讀問題，MySQL事務使用了Next-Key鎖。Next-Key鎖Next-Key鎖是行鎖和GAP（間隙鎖）的合并，行鎖上文已經介紹了，接下來說下GAP間隙鎖。行鎖可以防止不同事務版本的數據修改提交時造成數據沖突的情況。但如何避免別的事務插入數據就成了問題。我們可以看看RR級別和RC級別的對比RC級別：事務A 事務Bbegin; begin; select id,class_name,teacher_id from class_teacher where teacher_id=30; id class_name teacher_id 2 初三二班 30 update class_teacher set class_name='初三四班' where teacher_id=30; insert into class_teacher values (null,'初三二班',30); commit; select id,class_name,teacher_id from class_teacher where teacher_id=30; id class_name teacher_id 2 初三四班 30 10 初三二班 30 RR級別： 事務A 事務B begin; begin; select id,class_name,teacher_id from class_teacher where teacher_id=30; id class_name teacher_id 2 初三二班 30 update class_teacher set class_name='初三四班' where teacher_id=30; insert into class_teacher values (null,'初三二班',30); waiting.... select id,class_name,teacher_id from class_teacher where teacher_id=30; id class_name teacher_id 2 初三四班 30 commit; 事務Acommit后，事務B的insert執(zhí)行。通過對比我們可以發(fā)現，在RC級別中，事務A修改了所有teacher_id=30的數據，但是當事務Binsert進新數據后，事務A發(fā)現莫名其妙多了一行teacher_id=30的數據，而且沒有被之前的update語句所修改，這就是“當前讀”的幻讀。RR級別中，事務A在update后加鎖，事務B無法插入新數據，這樣事務A在update前后讀的數據保持一致，避免了幻讀。這個鎖，就是Gap鎖。MySQL是這么實現的：在class_teacher這張表中，teacher_id是個索引，那么它就會維護一套B+樹的數據關系，為了簡化，我們用鏈表結構來表達（實際上是個樹形結構，但原理相同）如圖所示，InnoDB使用的是聚集索引，teacher_id身為二級索引，就要維護一個索引字段和主鍵id的樹狀結構（這里用鏈表形式表現），并保持順序排列。Innodb將這段數據分成幾個個區(qū)間(negative infinity, 5], (5,30], (30,positive infinity)； update class_teacher set class_name='初三四班' where teacher_id=30; 不僅用行鎖，鎖住了相應的數據行；同時也在兩邊的區(qū)間，（5,30]和（30，positive infinity），都加入了gap鎖。這樣事務B就無法在這個兩個區(qū)間insert進新數據。受限于這種實現方式，Innodb很多時候會鎖住不需要鎖的區(qū)間。如下所示：事務A 事務B 事務Cbegin; begin; begin; select id,class_name,teacher_id from class_teacher; id class_name teacher_id 1 初三一班 5 2 初三二班 30 update class_teacher set class_name='初一一班' where teacher_id=20; insert into class_teacher values (null,'初三五班',10); waiting ..... insert into class_teacher values (null,'初三五班',40); commit; 事務A commit之后，這條語句才插入成功 commit; commit; update的teacher_id=20是在(5，30]區(qū)間，即使沒有修改任何數據，Innodb也會在這個區(qū)間加gap鎖，而其它區(qū)間不會影響，事務C正常插入。如果使用的是沒有索引的字段，比如update class_teacher set teacher_id=7 where class_name='初三八班（即使沒有匹配到任何數據）',那么會給全表加入gap鎖。同時，它不能像上文中行鎖一樣經過MySQL Server過濾自動解除不滿足條件的鎖，因為沒有索引，則這些字段也就沒有排序，也就沒有區(qū)間。除非該事務提交，否則其它事務無法插入任何數據。行鎖防止別的事務修改或刪除，GAP鎖防止別的事務新增，行鎖和GAP鎖結合形成的的Next-Key鎖共同解決了RR級別在寫數據時的幻讀問題。Serializable這個級別很簡單，讀加共享鎖，寫加排他鎖，讀寫互斥。使用的悲觀鎖的理論，實現簡單，數據更加安全，但是并發(fā)能力非常差。如果你的業(yè)務并發(fā)的特別少或者沒有并發(fā)，同時又要求數據及時可靠的話，可以使用這種模式。這里要吐槽一句，不要看到select就說不會加鎖了，在Serializable這個級別，還是會加鎖的！

網站欄目：包含postgresql死鎖的詞條
轉載來于：http://www.jiaotiyi.com/article/dsdpjip.html