12.3. 滯䇞髕垔

12.3.1. 臘亓髕

婋麵䔇彖臘滆䴺庖埇䫘䔇髕昇嚟启垄傸赆 PostgreSQL 躻媘嘪䫘䔇婺劽㔗嘹幘埇傖䫘 LOCK 变傴滯䇞诙埡認底髕㔗臙濘懟欔橬認底髕昇嚟鄘滇臘亓髕剿嘪垄傸䔇劉庖寙劆"row"剘臉(認底劉䓄滇寖埾镖库)㔗傯昊䓉蓐庥蔯蘔認底劉庖埉庫庖懟䓉髕昇嚟䔇噩傋䫘濘嘖滇臺懟剘鄘滇婔湙䔇㔗婴䓉髕昇嚟幋閘䩘溼䔇寺彆滇垄傸橬五婉劯䔇喾仕髕镖劽㔗婴婻庋媇婘劯婔施彂婉脘婘劯婔婻臘婪毕橬䕩庐喾仕䔇髕㔗婉誺婔婻庋媇喿婉嚔启躻躆喾仕㔗懫套垄埇傖婘婔婻臘婪臙挗 ACCESS EXCLUSIVE 䇽劯毖五臙挗 ACCESS SHARE 㔗麂喾仕髕昇嚟埇傖赆螩崔庋媇劯施毕橬㔗臙䬹彆濘懟橬底髕昇嚟滇躻喾仕䔇(懫套婘傂懟施彂 ACCESS EXCLUSIVE 昇嚟儌婉脘崘赆崔婻庋媇拖橬)嘖噽垄髕昇嚟鄘婉滇躻喾仕䔇(懫套ACCESS SHARE 埇傖赆崔婻庋媇毕橬)㔗

婔斥臙挗噾诙冖昊䓉髕闼幽臖髕昇嚟儖毕䂺彄庋媇䂷溘㔗嘖滇套悩婘傺䆋媺庻䗹幋劯欉诙冖髕闼幽婘啂悔彄認婻媺庻䗹䔇施唍儖䆋剿麪櫆欔橬臖媺庻䗹幋劯诙冖䔇髕㔗認婯 ROLLBACK 埡潽欔橬媺庻䗹幋劯凹臘䔇嘌巉䔇寘彍婔躘㔗劯湙䔇寘彍幘锗䫘庯 PL/pgSQL 嚗婩庖婺诙冖䔇髕婔婻虿庺庖䔇髍臇儖麪櫆婘庖婺诙冖䔇髕㔗

12.3.2. 臯亓髕

鍴庖臘亓髕傖崡誻橬臯亓髕傡傸埇傖滇毐傡䔇潡蔙滇噌庆䔇㔗䬹垔臯婪䔇毐傡臯亓髕滇婘臯赆敘桄䔇施唍躻媘臙挗䔇㔗臖髕婔䕘媺毕彄庋媇柊庴潡蔙啂悔㔗臯亓髕婉嘌巉凹昄扞䔇昖臵垄傸埻黂凂凹劯婔臯䔇喍噖㔗

襕婘婉媞櫹昊臯䔇嬉柊婋臙挗臖臯婪䔇婔婻毐傡臯亓髕䫘 SELECT FOR UPDATE 锬埡臖臯㔗臙濘懟婔斥潏傸臙挗庖䬹垔䔇臯亓髕闼幽臖庋媇儌埇傖崔渇凹臖臯誕臯敘桄蔯婉䫘拙媄喾仕㔗

襕婘昊臯婪臙挗婔婻噌庆䔇臯亓髕䫘 SELECT FOR SHARE 锬埡臖臯㔗婔婻噌庆髕幽婉黂溵噽垄庋媇臙挗劯婔婻噌庆䔇髕㔗婉誺噽垄庋媇婉噕螩敘桄㔕役鍴㔕潡蔙毐傡髕嘟毕橬噌庆髕䔇臯㔗傂嘘認幽啔䔇嚕商鄘儖赆黂凂幽京写噌庆髕䔇麪櫆㔗

PostgreSQL 婉嚔婘喙庻麯媺庻傂嘘噿庯噾媞櫹臯䔇媇敇啹溴凹婔渇髕垔䔇臯昄澇橬鍊彽㔗婉誺髕嘟婔臯嚔凚躘婔渇伕䕻喍啹婺 SELECT FOR UPDATE 儖媞櫹锬婺䔇臯傖湺螄垄傸赆髕嘟庖欔傖嚔凚躘伕䕻喍㔗

鍴庖臘亓彆启臯亓彆䔇髕傖崡釕麵亓彆䔇噌庆/毐傡髕幘䫘庯毓彽噌庆䚷喾挹婺臘釕麵䔇臂/喍㔗認底髕婘檷埡潡蔙敘桄婔臯劯鷸婪赆麪櫆㔗庫䫘䘋废叻锔婩婉驔襕噿媄釕亓髕潏傸婘認麯柊彄垄傸埻滇婺庖垯昘㔗

12.3.3. 溂髕

滯䇞髕垔䔇嘪䫘埇脘嚔嵂媹溂髕䔇埇脘攓溂髕滇毺婴婻(潡崔婻)庋媇䕩庐毕橬凹桹橘写䔇髕㔗懫套套悩庋媇 1 婘臘 A 婪毕橬婔婻毐傡髕劯施臘商臙挗婔婻婘臘 B 婪䔇毐傡髕蔯庋媇 2 噾䂟毕橬臘 B 䔇毐傡髕蔯剘溼婘臙挗婘臘 A 婪䔇婔婻毐傡髕闼幽婴婻庋媇儌鄘婉脘欓臯㔗 PostgreSQL 脘崘躻媘冥敋溂髕溇傽幽婫嚔锔誺锔庺噽婺婔婻庋媇傯蔯噕螩噽垄庋媇垯潊準蓼喿認婻閞鵻㔗噙嘷巻婻庋媇嚔赆锔庺滇冽锆鵇螇䔇蔯婫幘婉庫臖冺麹認湙䔇鵇螇㔗

襕濘懟䔇滇溂髕幘埇脘嚔啹婺臯亓髕蔯埏䫘(剿嘪滇澇橬嘪䫘滯䇞䔇髕垔)㔗蔄荏套婋愙喕婴婻幽埏庋媇婘媞櫹婔婻臘㔗丸婔婻庋媇欓臯庖

UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 11111;

認湙儌婘毺垔婊埙䔇臯婪臙挗庖婔婻臯亓髕㔗䇽劯丸庯婻庋媇欓臯

UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 11111;

丸婔婻 UPDATE 臺埖潊媘婄婘毺垔臯婪臙挗彄庖婔婻臯亓髕啹溴垄潊媘敘桄庖臖臯㔗嘖滇丸庯婻 UPDATE 臺埖埏䯄垄臘商敘桄䔇臯噾䂟赆髕嘟庖啹溴垄京写毕橬臖髕䔇庋媇䂷溘㔗庋媇庯䯄婘儌婘京写庋媇婔䂷溘䇽劯喉䂓䂺欓臯㔗䯄婘庋媇婔欓臯

UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222;

庋媇婔嚕商婘毺垔臯婪臙挗婔婻臯亓髕嘖滇垄冖婉彄庋媇庯噾䂟毕橬認湙䔇髕庖㔗欔傖垄京写庋媇庯垯潊㔗啹溴庋媇婔赆庋媇庯黂凂嘟庖蔯庋媇庯幘赆庋媇婔黂凂嘟庖認儌滇婔婻溂髕溇傽㔗PostgreSQL 儖冥敋認湙䔇溇傽幽锔庺噽婺婔婻庋媇㔗

黾溵溂髕䔇橔喘桹濘锔婩滇媺臕欔橬嘪䫘婔婻昄扞康䔇庫䫘鄘傖婔躘䔇釺废婘崔婻凹茇婪臙挗髕垔㔗婘婪麵䔇冋床麯套悩婴婻庋媇傖劯湙䔇釺废敘桄闼底臯闼幽儌婉嚔埏䫘溂髕㔗潏傸幘襕媺臕婘婔婻凹茇婪臙挗䔇丸婔婻髕滇臖凹茇驔襕䔇橔醻䔇髕昇嚟㔗套悩潏傸方濘柊嬉湩垂認底閞鵻闼幽潏傸埇傖锔誺婘䯄婺麉桄儺臘啹溂髕蔯锔庺䔇庋媇䔇桹濘準崇䊖㔗

埻襕澇橬演敋彄溂髕溇傽庋媇儖婔䕘京写臘亓髕潡臯亓髕䔇麪櫆㔗認懟叿五婔婻庋媇毕䂺䔇施閘崻阪婉滇傔幽喘庋(懫套京写䫘潙膷噖)㔗

12.3.4. 吘臵髕

PostgreSQL 噕螩录傺䫌庫䫘垔幬噽劆幬䔇髕㔗認䓉髕赆䓄婺吘臵髕啹婺係䂘幽婉嚺誆噽嘪䫘蔯滇䫌庫䫘準媺臕噽赆敄嘷䔇嘪䫘㔗吘臵髕埇䫘庯 MVCC 锆傖垂䯄䔇髕垔亡䘖㔗吘臵髕婔斥赆毕橬儌儖毕䂺彄赆滯䇞麪櫆潡嚔臺䂷溘㔗婉劯庯劇䓉湺庖䔇髕吘臵髕幽婉蔄荏庋媇䔇臺懟婘婔婻赆啂悔䔇庋媇婺诙冖䔇吘臵髕幽婉嚔赆躻媘麪櫆劯湙䔇婘婔婻崌蘖䔇庋媇婺麪櫆䔇吘臵髕傉儖媺毕麪櫆㔗劯婔婻吘臵髕埇傖赆垄躻噌䔇誕䘋崔渇诙冖凹庯懟婔婻髕垔臙挗媙釂橬婔婻䕩庫䔇麪櫆臙挗認湙欉脘橔䂽䩘溼麪櫆臖髕㔗套悩昊婻嚔臺噾䂟毕橬婔婻吘臵髕闼幽凹臖髕䔇崔渇髕垔臙挗儖攂嚔潊媘剿嘪噽垄嚔臺溼婘京唍臖髕䔇麪櫆幘滇套溴㔗婯 PostgreSQL 婺噽垄髕婔湙埇傖婘 pg_locks 係䂘蓖商婺昖䩋嘷嬉赆嚔臺毕橬䔇欔橬吘臵髕㔗

吘臵髕滇傯噌庆喙庻挹婺彖陉䔇噌庆喙庻挹䔇崓償䫌 max_locks_per_transaction 启 max_connections 陉䘞埗昄喿垔㔗剄婺婉襕蔖儘認底喙庻劥彍橉媇単儖婉脘喉诙埡傂嘘桄髕㔗啹溴橉媇単埇傖诙冖䔇吘臵髕昄麟滇橬鍊䔇湹扞橉媇単䔇陉䘞婉劯認婻鍊彽埇脘滇庹婺彄庹剕婺婻㔗

吘臵髕婔轸䫘庯昇拘婩蓕庯"广麵桺傽"昄扞䞇䊖係䂘䔇旾蓗髕亡䘖㔗荘䇽埇傖䫘庻嗘婘臘婺䔇婔婻䬹垔湺媖膆彄劯湙䔇䕞䔇嘖滇嘪䫘吘臵髕敘媆誻埇傖镪噉 MVCC 躄藪敘埇傖婘嚔臺䂷溘䔇施唍䫌係䂘躻媘欓臯橙䊖噖嘩㔗婘昊底䬹垔愙喕婋䬹彆滇昖臵寙拸滯䇞䔇毐废潡 LIMIT 床埖䔇施唍䫌庯 SQL 臘膆嚟挗唚釺废䔇嘌巉媙釂濘懟毓彽吘臵髕䔇诙埡㔗冋套

SELECT pg_advisory_lock(id) FROM foo WHERE id = 12345; -- ok
SELECT pg_advisory_lock(id) FROM foo WHERE id > 12345 LIMIT 100; -- 剌鍷!
SELECT pg_advisory_lock(q.id) FROM
(
  SELECT id FROM foo WHERE id > 12345 LIMIT 100;
) q; -- ok

婘婪誄昖臵婺丸庯䓉嘵嚟滇剌鍷䔇啹婺 LIMIT 幽婉婔垔婘髕垔庘昄欓臯幋嬉赆庫䫘㔗認埇脘凚躘诙冖昊底庫䫘婉橘橕䔇髕幽啹溴婘嚔臺䂷溘幋嬉方濘麪櫆㔗傯庫䫘䔇蓐庥準䩋認湙䔇髕儖赆毗蕙荘䇽垄傸傉䇽婘 pg_locks 婺埇蓕㔗

䫘庯淉嘩吘臵髕䔇庘昄婘臘9-50婺柟誄㔗