數(shù)據(jù)庫(kù)操作遇到的問題
- 臟讀:一個(gè)事務(wù)讀取了另一個(gè)未提交事務(wù)寫入的數(shù)據(jù);
- 不可重復(fù)讀:一個(gè)事務(wù)重新讀取前面讀取過的數(shù)據(jù)显晶，發(fā)現(xiàn)該數(shù)據(jù)已經(jīng)被另一個(gè)已經(jīng)提交的事務(wù)修改;
- 幻讀:一個(gè)事務(wù)重新執(zhí)行一個(gè)查詢垢袱，返回符合查詢條件的行的集合，發(fā)現(xiàn)滿足查詢條件的行的集合因?yàn)槠渌罱峤坏氖聞?wù)而發(fā)生了改變艘狭。
事務(wù)特性
- 原子性（Atomicity）：事務(wù)作為一個(gè)整體被執(zhí)行，包含在其中的對(duì)數(shù)據(jù)庫(kù)的操作要么全部被執(zhí)行哥纫，要么都不執(zhí)行;
- 一致性（Consistency）：事務(wù)應(yīng)確保數(shù)據(jù)庫(kù)的狀態(tài)從一個(gè)一致狀態(tài)轉(zhuǎn)變?yōu)榱硪粋€(gè)一致狀態(tài)悬秉。一致狀態(tài)的含義是數(shù)據(jù)庫(kù)中的數(shù)據(jù)應(yīng)滿足完整性約束;
- 隔離性（Isolation）：多個(gè)事務(wù)并發(fā)執(zhí)行時(shí)，一個(gè)事務(wù)的執(zhí)行不應(yīng)影響其他事務(wù)的執(zhí)行;
- 持久性（Durability）：一個(gè)事務(wù)一旦提交械媒，他對(duì)數(shù)據(jù)庫(kù)的修改應(yīng)該永久保存在數(shù)據(jù)庫(kù)中目锭。
數(shù)據(jù)庫(kù)事務(wù)隔離級(jí)別
- 讀未提交(READ UNCOMMITTED)
- 讀已提交(READ COMMITTED)
- 可重復(fù)讀(REPEATABLE READS)
- 可序列化(SERIALIZABLE)
數(shù)據(jù)庫(kù)事務(wù)隔離級(jí)別&&解決問題
參考：http://www.postgres.cn/docs/9.5/transaction-iso.html
樂觀鎖與悲觀鎖
參考：http://www.hollischuang.com/archives/934
MVCC(multiversion concurrency control,多版本并發(fā)控制)
MVCC(多版本并發(fā)控制)與使用鎖的優(yōu)缺點(diǎn)：
在MVCC里评汰，對(duì)檢索(讀)數(shù)據(jù)的鎖請(qǐng)求與寫數(shù)據(jù)的鎖請(qǐng)求不沖突，所以讀不會(huì)阻塞寫痢虹，而寫也從不阻塞讀被去。甚至當(dāng)通過創(chuàng)新的序列化快照隔離(SSI)級(jí)別提供事務(wù)隔離的嚴(yán)格等級(jí)時(shí)，PostgreSQL維持這樣的保證奖唯。
在PostgreSQL里也有表和行級(jí)別的鎖定機(jī)制惨缆，用于給那些無法輕松接受MVCC行為的應(yīng)用。 不過丰捷，恰當(dāng)?shù)厥褂肕VCC總會(huì)提供比鎖更好的性能坯墨。另外，由應(yīng)用定義的咨詢鎖提供了一個(gè)獲得不依賴于單獨(dú)事務(wù)的鎖的機(jī)制病往。
問題:PG什么時(shí)候選擇MVCC什么時(shí)候選擇使用鎖?
在PostgreSQL里也有表和行級(jí)別的鎖功能捣染，用于那些通常不需要完整事務(wù)隔離并且想要顯式管理特定沖突點(diǎn)的應(yīng)用。不過荣恐，恰當(dāng)?shù)厥褂肕VCC通常會(huì)提供比鎖更好的性能液斜。MVCC的兩種實(shí)現(xiàn)方法
寫新數(shù)據(jù)時(shí)，把舊數(shù)據(jù)移到一個(gè)單獨(dú)的地方叠穆，如回滾段中少漆，其他人讀數(shù)據(jù)時(shí)，從回滾段中把舊的數(shù)據(jù)讀出來;
2.寫數(shù)據(jù)時(shí)硼被，舊數(shù)據(jù)不刪除示损，而是把新數(shù)據(jù)插入。PostgreSQL數(shù)據(jù)庫(kù)使用第二種方法嚷硫，而Oracle數(shù)據(jù)庫(kù)和MySQL中的innodb引擎使用的是第一種方法;-
與oracle數(shù)據(jù)庫(kù)和MySQL中的innodb引擎相比較检访，PostgreSQL的MVCC實(shí)現(xiàn)方式的優(yōu)缺點(diǎn)如下:
- 優(yōu)點(diǎn):
- 事務(wù)回滾可以立即完成，無論事務(wù)進(jìn)行了多少操作仔掸；
- 數(shù)據(jù)可以進(jìn)行很多更新脆贵，不必像Oracle和MySQL的Innodb引擎那樣需要經(jīng)常保證回滾段不會(huì)被用完，也不會(huì)像oracle數(shù)據(jù)庫(kù)那樣經(jīng)常遇到“ORA-1555”錯(cuò)誤的困擾起暮；
- 缺點(diǎn):
- 舊版本數(shù)據(jù)需要清理卖氨。PostgreSQL清理舊版本的命令成為Vacuum；
- 舊版本的數(shù)據(jù)會(huì)導(dǎo)致查詢更慢一些负懦，因?yàn)榕f版本的數(shù)據(jù)存在于數(shù)據(jù)文件中筒捺，查詢時(shí)需要掃描更多的數(shù)據(jù)塊。
- 優(yōu)點(diǎn):
PG中delete和update語句機(jī)制
在PostgreSQL中纸厉，使用delete和update語句刪除或更新的數(shù)據(jù)行并沒有被實(shí)際刪除系吭，而只是在舊版本數(shù)據(jù)行的物理地址上將該行的狀態(tài)置為已刪除或已過期。因此當(dāng)數(shù)據(jù)表中的數(shù)據(jù)變化極為頻繁時(shí)颗品，那么在一段時(shí)間之后該表所占用的空間將會(huì)變得很大肯尺，然而數(shù)據(jù)量卻可能變化不大沃缘。要解決該問題，需要定期對(duì)數(shù)據(jù)變化頻繁的數(shù)據(jù)表執(zhí)行VACUUM操作
- 無VACUUM:只是將刪除的數(shù)據(jù)狀態(tài)置為已刪除,該空間不能記錄被重新使用.
- VACUUM:刪除的記錄位于末端,占用的空間會(huì)被物理釋放歸還操作系統(tǒng),如果不是位于末端,會(huì)將刪除數(shù)據(jù)鎖占用的空間置為可用狀態(tài).
- VACUUM FULL:不論被刪除的數(shù)據(jù)是否處于數(shù)據(jù)表末端,這些數(shù)據(jù)鎖占用的空間都將被物理釋放并歸還操作系統(tǒng).
下面的blog詳細(xì)分析了vacuum和vacuum full的區(qū)別,以及如何恢復(fù)索引和delete后的磁盤空間:
http://www.cnblogs.com/stephen-liu74/archive/2011/12/27/2304155.html
PG的MVCC實(shí)現(xiàn)機(jī)制(以insert形式展開說明)
在Postgres中蟆盹，每一個(gè)事務(wù)都會(huì)得到一個(gè)被稱作為 XID 的事務(wù)ID孩灯。這里說的事務(wù)不僅僅是被 BEGIN - COMMIT 包裹的一組語句闺金，還包括單條的insert逾滥、update或者delete語句。當(dāng)一個(gè)事務(wù)開始時(shí)败匹，Postgrel遞增XID寨昙，然后把它賦給這個(gè)事務(wù)。Postgres還在系統(tǒng)里的每一行記錄上都存儲(chǔ)了事務(wù)相關(guān)的信息掀亩，這被用來判斷某一行記錄對(duì)于當(dāng)前事務(wù)是否可見舔哪。
insert的MVCC說明:
- 當(dāng)插入一行記錄時(shí),PG會(huì)把當(dāng)前事務(wù)的XID存儲(chǔ)在這行數(shù)據(jù)中的xmin;
- 當(dāng)插入的數(shù)據(jù)未COMMIT之前,這行數(shù)據(jù)對(duì)其他數(shù)據(jù)是不可見的;
- 當(dāng)插入的數(shù)據(jù)COMMIT后,這行數(shù)據(jù)對(duì)其他數(shù)據(jù)是可見的;
- 資料說只有xmin<XID條件,才能查看這條新記錄,但測(cè)試發(fā)現(xiàn)可能存在可重復(fù)讀;
- 事務(wù)1:
事務(wù)ID:x
查詢數(shù)據(jù)1;
阻塞;
查詢數(shù)據(jù)2; - 事務(wù)2:
事務(wù)ID:x+1
插入數(shù)據(jù)
上面2個(gè)示例,如果事務(wù)2在事務(wù)1阻塞的時(shí)候提交,在事務(wù)1中的查詢數(shù)據(jù)2是能查看到這條新數(shù)據(jù)的,因?yàn)镻G默認(rèn)的事務(wù)隔離級(jí)別是讀已提交.如果更改事務(wù)隔離級(jí)別為SERIALIZABLE后,則不會(huì)發(fā)生可重復(fù)讀的情況(即查詢1和查詢2兩次的結(jié)果一致).
自己測(cè)試查看語句:
--獲取行xmin,xmax值
select xmin, xmax,* from tablename;
--獲取當(dāng)前事務(wù)的XID
select txid_current();
--改變事務(wù)隔離級(jí)別
BEGIN TRANSACTION ISOLATION LEVEl [READ COMMITTED/REPEATABLE READ/SERIALIZABLE]; --一次啟動(dòng)事務(wù)并指定事務(wù)隔離級(jí)別
對(duì)于delete和update來說,機(jī)制也是類似的,當(dāng)對(duì)于他們來說PG使用xmax來判斷數(shù)據(jù)的可見性.
自己測(cè)試思路可以參考:
http://www.zlovezl.cn/articles/postgresql-concurrency-with-mvcc/
原理也可參考:
說明:https://my.oschina.net/Kenyon/blog/108850
示例:https://my.oschina.net/Kenyon/blog/63668
PG中的事務(wù)隔離
- 兩個(gè)事務(wù)，更新的同一行槽棍，一次只有一個(gè)事務(wù)能更新．
- 兩個(gè)事務(wù)捉蚤，更新不同行，能同時(shí)更新．
參考:
PG文檔事務(wù)隔離:http://www.postgres.cn/docs/9.5/transaction-iso.html
PG的表鎖
PG文檔表鎖機(jī)制:http://www.postgres.cn/docs/9.5/explicit-locking.html#LOCKING-TABLES
通過實(shí)例較好的說明了PG的鎖機(jī)制:http://www.oschina.net/translate/postgresql-locking-revealed
PG的行鎖
PG文檔行級(jí)鎖:http://www.postgres.cn/docs/9.5/explicit-locking.html#LOCKING-ROWS
pg行鎖解讀：http://blog.itpub.net/30088583/viewspace-1699315/
PG事務(wù)常用操作
- 開啟事務(wù)
BEGIN;
START TRANSACTION [ transaction_mode [, ...] ]
-這里的 transaction_mode是下列之一：
- ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
- READ WRITE | READ ONLY
- [ NOT ] DEFERRABLE
- 設(shè)置事務(wù)模式
SET TRANSACTION transaction_mode [, ...]
SET TRANSACTION SNAPSHOT snapshot_idSET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, ...]
-這里的 transaction_mode是下列之一:
- ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
- READ WRITE | READ ONLY
- [ NOT ] DEFERRABLE
--事務(wù)隔離級(jí)別炼七，定義多個(gè)事務(wù)時(shí)間的隔離級(jí)別
BEGIN TRANSACTION ISOLATION LEVEl [READ COMMITTED/REPEATABLE READ/SERIALIZABLE]; --一次啟動(dòng)事務(wù)并指定事務(wù)隔離級(jí)別
BEGIN;
TRANSACTION ISOLATION LEVEl [READ COMMITTED/REPEATABLE READ/SERIALIZABLE]; --先啟動(dòng)事務(wù)缆巧，再設(shè)置事務(wù)隔離級(jí)別
- 結(jié)束事務(wù)
commit
rollback
- 預(yù)備事務(wù)
--預(yù)備事務(wù)，使得事務(wù)分階段可以提交
PREPARE TRANSACTION 'foobar';
......
COMMIT PREPARE TRANSACTION 'foobar';
ROLLBACK PREPARE TRANSACTION 'foobar';
- 保存點(diǎn)
--保存點(diǎn)savepoint,可以支持事務(wù)的部分回滾
insert into lyy values(1,'nn');
savepoint svp1;
insert into lyy values(2,'ff');
rollback to savepoint svp1;
--此時(shí)提交的話豌拙，第二個(gè)insert未被插入陕悬，但是第一個(gè)插入成功。
- 查看當(dāng)前事務(wù)的事務(wù)id
select txid_current();
詳情參考：https://my.oschina.net/liuyuanyuangogo/blog/415395
其他參考
pg鎖機(jī)制理解：http://francs3.blog.163.com/blog/static/40576727201082134343604/
mysql鎖機(jī)制理解： http://hedengcheng.com/?p=771
pg中mvcc實(shí)現(xiàn)機(jī)制:http://www.zlovezl.cn/articles/postgresql-concurrency-with-mvcc/
http://blog.itpub.net/30088583/viewspace-1585695/
較好的闡釋了pg中mvcc的原理:https://my.oschina.net/Kenyon/blog/108850
pg事務(wù)級(jí)別查看與變更:http://blog.csdn.net/scugxl/article/details/51126433
mysql與pg對(duì)比:https://www.sdk.cn/news/4587
pg維護(hù)vacuum解析：http://www.cnblogs.com/stephen-liu74/archive/2011/12/27/2304155.html
Ubuntu安裝PG與使用:http://wenzhixin.net.cn/2014/01/12/hello_postgresql
