12.2. 庋媇锫䥂

12.2.1. 臂噾柊庴锫䥂亓彆

臂噾柊庴滇 PostgreSQL 麯䔇䚺䩕锫䥂亓彆㔗嘷婔婻庋媇誊臯婘認婻锫䥂亓彆施SELECT 昖臵埻脘䩋彄昖臵嚔哋幋嬉噾柊庴䔇昄扞蔯方濘䩋彄橻柊庴䔇昄扞潡蔙婘昖臵欓臯橘閘噽垄庋媇噾柊庴䔇昄扞㔗婉誺 SELECT 䩋冖蓕噽躻躆欔婘庋媇婺嬉麵儔橻柊庴䔇敘桄䂷悩㔗垂鍙婪SELECT 昖臵䩋彄婔婻婘昖臵嚔哋誊臯䔇䂸閘臖昄扞康䔇婔婻媆䙓㔗臙濘懟婘劯婔婻庋媇麯婴婻䕩闂䔇 SELECT 变傴埇脘䩋彄婉劯䔇媆䙓啹婺噽垄庋媇嚔婘丸婔婻 SELECT 欓臯橘閘柊庴㔗

UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR SHARE 婘抩䘵䕞湺臯施䔇臯婺启 SELECT 婔湙垄傸埻脘欆彄婘变傴嚔哋䔇施唍噾䂟柊庴䔇臯㔗婉誺認湙䔇䕞湺臯婘赆欆彄䔇施唍埇脘噾䂟赆噽垄幽埏庋媇敘桄㔕役鍴㔕髕嘟㔗婘認䓉愙喕婋剿儖誕臯䔇敘桄儖京写丸婔婻庋媇柊庴潡蔙啂悔(套悩垄誻婘崇䊖)㔗套悩丸婔婻庋媇啂悔闼幽垄䔇嘩䫘儖赆媘䘖蔯丸庯婻庋媇儖䂓䂺敘桄橔彺埏䯄䔇臯㔗套悩丸婔婻庋媇柊庴闼幽套悩丸婔婻庋媇役鍴庖臖臯彍丸庯婻庋媇儖媘䘖臖臯劥彍垄儖臘商婘臖臯䔇噾敘桄䔇䬽橸婪桘媹垄䔇淉嘩㔗係䂘儖麉桄螇䞖变傴抩䘵溇傽(WHERE 床埖)䩋䩋臖臯噾敘桄䔇䬽橸滇劥傉䇽严劽抩䘵溇傽㔗套悩严劽彍丸庯婻庋媇傯臖臯䔇噾敘桄䬽橸嚔哋䂓䂺噽淉嘩㔗套悩滇 SELECT FOR UPDATE 启 SELECT FOR SHARE 彍懟叿五檪噾敘桄䔇臯䬽橸髕嘟幽誫啂䂍垵潙䆇㔗

啹婺婪麵䔇蓇彍溼婘敘桄䔇变傴埇脘嚔䩋彄婉婔躘䔇媆䙓垄傸埇傖䩋彄嘌巉垄傸敘桄䔇幽埏变傴䔇昽悩嘖滇剘䩋婉彄闼底变傴凹昄扞康麯噽垄臯䔇嘩䫘㔗認湙䔇臯婺傴臂噾柊庴昇嚟婉锗劽䫘庯巻䓉潬埪崉溗抩䘵溇傽䔇变傴㔗婉誺垄凹庯䞔剘䔇愙喕蔯蘔滇溼䇞䔇㔗懫套啺螆潏傸䫘䌂嚚婋麵認湙䔇变傴敘桄鷽臯嘍鵺

BEGIN;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534;
COMMIT;

套悩婴婻幽埏庋媇臘商劯施媞櫹婊埙12345䔇嘍鵺闼潏傸冽滯滆婯橕丸庯婻庋媇滇傯噾敘桄誺䔇臯䬽橸婪誕臯敘桄㔗啹婺懟婻变傴埻滇嘌巉婔婻噾䂟喿垔庖䔇臯啹溴螷垄䩋彄敘桄劯䔇䬽橸婉嚔凚躘傂嘘婉婔躘䔇閞鵻㔗

啹婺婘臂噾柊庴昇嚟麯懟婻桄䔇变傴鄘滇傯婔婻桄䔇媆䙓嚔哋䔇蔯認婻媆䙓寙劆欔橬彄臖施彂婺溵噾柊庴䔇庋媇啹溴劯婔庋媇婺劯麵䔇变傴儖䩋彄傂嘘噾柊庴䔇噽垄庋媇䔇昽悩㔗認麯噿媄䔇閞鵻滇婘剘婻变傴麯滇劥䩋彄昄扞康麯䂺凹婔躘䔇蓖商㔗

臂噾柊庴昇嚟柊冕䔇鄘彖庋媇锫䥂凹庯螩崔庫䫘蔯蘔滇轿崘䔇幽婫認婻昇嚟锘庥媆嘪䫘䞔剘㔗婉誺凹庯啔崉溗昖臵启敘桄䔇庫䫘埇脘驔襕媺臕昄扞康橬懫臂噾柊庴昇嚟敘媹婖湚䔇婔躘攓蓖商㔗

12.2.2. 埇婾臯寡锫䥂亓彆

埇婾臯寡亓彆柊冕橔婖湚䔇庋媇锫䥂㔗認婻亓彆昇拘婾臯䔇庋媇欓臯儌喘償庋媇儖赆婔婻毖五婔婻闼湙婾臯(蔯婉滇幽臯)䔇欓臯㔗婉誺嘪䫘認婻亓彆䔇庫䫘媙釂庖崺婘婾臯寡崌蘖䔇施唍麉桄劇媘庋媇㔗

嘷婔婻庋媇崇庯埇婾臯寡亓彆施SELECT 昖臵埻脘䩋彄婘庋媇嚔哋幋嬉噾柊庴䔇昄扞蔯䩋婉彄橻柊庴䔇昄扞潡庋媇欓臯橘閘噽垄幽埏庋媇噾柊庴䔇媞櫹㔗婉誺SELECT 䩋冖蓕噽躻躆欔婘庋媇婺嬉麵儔橻柊庴䔇敘桄䂷悩㔗埇婾臯寡䔇 SELECT 䩋彄䔇滇臖庋媇嚔哋施䔇媆䙓蔯婉滇臖庋媇喙鄘嘷嬉昖臵嚔哋施䔇媆䙓認婻臯婺启臂噾柊庴亓彆滇婉婔湙䔇㔗認湙劯婔婻庋媇喙鄘劯麵䔇 SELECT 变傴攂滇䩋彄劯湙䔇昄扞㔗

UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR SHARE 婘抩䘵䕞湺臯施䔇臯婺启 SELECT 婔湙垄傸儖埻凂欆婘庋媇嚔哋䔇施唍噾䂟柊庴䔇䕞湺臯㔗嘖滇認湙䔇䕞湺臯婘赆埏䯄䔇施唍埇脘噾䂟赆埥崡婔婻幽埏䔇庋媇敘桄㔕役鍴㔕髕嘟㔗婘認䓉愙喕婋埇婾臯寡䔇庋媇儖京写丸婔婻溼婘敘桄䔇庋媇柊庴潡蔙啂悔(套悩垄傉䇽婘崇䊖婺)㔗套悩丸婔婻庋媇啂悔闼幽垄䔇嘌巉儖赆媘䘖蔯認婻埇婾臯寡䔇儌埇傖䂓䂺敘桄垄橔彺埏䯄䔇臯㔗嘖滇套悩丸婔婻庋媇赆柊庴庖(幽婫垂鍙婪敘桄潡蔙役鍴庖臖臯蔯婉埻滇髕嘟垄)闼幽埇婾臯寡庋媇儖啂悔幽誫啂婋麵媇敇

ERROR:  could not serialize access due to concurrent update

啹婺婔婻埇婾臯寡䔇庋媇婘嚔哋幋劯婉脘敘櫹潡蔙髕嘟赆噽垄庋媇敘櫹誺䔇臯㔗

嘷庫䫘櫽彄認湙䔇髍臇媇敇施垄庫臖锔庺嘷嬉䔇庋媇䇽劯傯桄嚔哋誕臯昘婻庋媇㔗丸庯渇誊臯施臖庋媇䩋彄䔇媆䙓儖寙劆嬉婔渇噾柊庴䔇媞櫹欔傖婉嚔橬锂膏喾仕㔗

臙濘懟埻橬敘桄庋媇欉驔襕麉臘埻臂庋媇傯準澇橬婾臯寡喾仕㔗

埇婾臯寡庋媇亓彆柊冕庖婖湚䔇媺臕懟婻庋媇鄘䩋彄婔婻垯噘婔躘䔇昄扞康蓖商㔗婉誺套悩幽埏敘桄傴昄扞康婉脘䂘毕婾臯欓臯䔇湙床闼幽庫䫘媙釂庖崺麉臘庋媇㔗啹婺麉啔崉溗庋媇䔇嚔體埇脘滇麂婩埇蓗䔇欔傖潏傸埻傺螞嘷敘桄变傴婺寙劆崉溗锂膏幽婫婘臂噾柊庴亓彆婺埇脘凚躘髍臇䂷悩䔇施唍欉嘪䫘埇婾臯寡亓彆㔗埇婾臯寡昇嚟埻滇婘認湙䔇愙喕婋滇媙襕䔇婔婻庋媇誂䂺啔苖幾婻变傴蔯認庹婻变傴媙釂䩋彄垯噘䕩劯䔇昄扞康蓖商㔗

 class | value 
-------+-------
     1 |    10
     1 |    20
     2 |   100
     2 |   200

SELECT SUM(value) FROM mytab WHERE class = 1;

SELECT SUM(value) FROM mytab WHERE class = 2;