3.4. 庋媇

庋媇滇欔橬昄扞康係䂘䔇婔婻嘺橸楗媕㔗婔渇庋媇䔇襕䗹儌滇檪崔婻準黴扖䂏潊婔婻剘婔䔇㔕婉潊媘彍潊傕䔇淉嘩㔗噽垄幽埏䔇庋媇滇䩋婉彄婘認底準黴幋閘䔇婺閘䪽攕䔇幽婫套悩埏䫘庖婔底閞鵻凚躘臖庋媇方濘垯潊闼幽欔橬認底準黴鄘垯噘婉嚔嘌巉昄扞康㔗

懫套啺螆婔婻鷽臯䔇昄扞康寙劆劇䓉垵潙婊潙䔇嘍鵺傖埪懟婻彖臯䔇攂嘍鵺㔗啺螆潏傸襕螄嘘婔渇傯 Alice 䔇婊潙彄 Bob 䔇婊潙䔇麏鵺婺 $100.00 䔇櫇傻媘嘩㔗闼幽垯潊認婻傂媇䔇䞔剘彄悕䗹䔇 SQL 变傴償婋麵認湙

UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
UPDATE branches SET balance = balance - 100.00
    WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice');
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Bob';
UPDATE branches SET balance = balance + 100.00
    WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');

認底变傴䔇䂖誗婘認凪幽婉麉襕麉襕䔇滇認麯䬕潬彄庖喘庹婻䋸䆋䔇敘桄準垯潊認婻䕩嘷䞔剘䔇淉嘩㔗鷽臯垻叻嚔婯橕襕幽欔橬認底敘桄噘鄘䫘昽襕幽噘鄘婉蕙嘩䫘㔗潏傸嘷䇽婉婯橕婔渇係䂘嘷溄儌凚躘 Bob 櫽彄 100 庖婉滇 Alice 櫇傻䔇鐌幘婉婯橕 Alice 蔕滇婉誌鐌傯 Bob 闼麯拪彄䬷巕㔗潏傸驔襕媺臕套悩婘淉嘩䔇誺䘋婺庺庖噞髍闼幽欔橬認底準黴鄘婉嚔埏䫘昽悩㔗檪認底敘桄䂇劽潊婔婻庋媇儌䂍庽潏傸認湙䔇媺臕㔗庋媇赆螴婺滇寘床䔇傯噽垄庋媇䔇蓐庥準䩋垄襕幽滇噘鄘埏䫘襕幽垯噘婉埏䫘㔗

潏傸誻驔襕媺臕婔斥婔婻庋媇垯潊幽婫冖彄昄扞康係䂘䔇螴埇闼幽垄媙釂赆䩘溼愩幙婄庻嗘幽婫婉嚔婘锟劯䔇嘷溄婺潽崌㔗懫套套悩潏傸螄嘘彄庖婔婻 Bob 搴剘䔇媘嘩闼幽潏傸婉婯橕備備婘傡蕄庺鷽臯崓閘幋劯䔇婔渇嘷溄儌嚔凚躘凹傡䔇婊潙䔇欼废媘嘩潽崌㔗婔婻庋媇傋昄扞康媺臕婔婻庋媇欔啔䔇欔橬敘桄婘庋媇埏庺垯潊巉庫幋嬉鄘螄嘘彄愩幙䔇庻嗘婺(幘儌滇伕䕻)㔗

庋媇傋昄扞康䔇埥崡婔婻麉襕䔇攓蘘启寘床敘桄䔇楗媕噿係凖彺嘷崔婻庋媇幽埏婄誊臯䔇施唍懟婻庋媇鄘婉庫䩋彄噽垄庋媇欔啔䔇橻垯潊䔇埻寡㔗懫套套悩婔婻庋媇溼媍五螇䞖欔橬彖臯䔇嘍鵺攂启闼幽垄婉庫臖寙拸準躻 Alice 䔇彖臯䔇欼婊启準躻 Bob 彖臯䔇噖婊埉幋庥䇽㔗欔傖庋媇媙釂滇邏䍘彖滯䔇婉備備嘷䯄婘垄傸婘昄扞康婪库䫘䔇愩幙嘌巉庺埏蔯婫嘷䯄婘垄傸誊蘸施䔇躻躆䔇埇蓖攓婪㔗婔婻欷嚔䔇庋媇啔䔇敘桄婘垄垯潊幋嬉滇噽垄庋媇方濘䩋彄䔇蔯彄柊庴䔇施唍欔橬敘桄劯施埇蓕㔗

婘 PostgreSQL 麯婔婻庋媇滇锔誺檪 SQL 变傴䫘 BEGIN 启 COMMIT 变傴寙啘垂䯄䔇㔗啹溴潏傸䔇鷽臯庋媇垂鍙婪䩋蕙準償婋麵認湙

BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
-- 京京
COMMIT;

套悩婘臖庋媇䔇誺䘋婺潏傸喿垔婉啔柊庴(埇脘滇潏傸彔埏䯄 Alice 䔇嘍鵺滇蘘昄)闼幽潏傸埇傖埏庺 ROLLBACK 蔯婉滇 COMMIT 变傴闼幽彄䕞嬉婺溵潏傸䔇欔橬敘桄鄘嚔赆埡潽㔗

PostgreSQL 垂鍙婪檪懟婻 SQL 臺埖嘷啔婘婔婻庋媇婺欓臯準䩋写㔗套悩嘹澇橬埏庺 BEGIN 变傴闼幽懟婻䋸䆋䔇臺埖鄘赆婔婻锊劆䔇 BEGIN 启(套悩潊媘䔇臺) COMMIT 寙啘㔗婔䂇寙啘婘 BEGIN 启 COMMIT 幋閘䔇臺埖橬施唍赆䓄啔 庋媇庖㔗

潏傸埇傖锔誺嘪䫘媺庻䗹䔇桹濘婘婔婻庋媇麯敘媹來䂖婄毓彽噽婺䔇臺埖㔗媺庻䗹噕螩嘹锬拷攓婄檕嚄庋媇婺䔇昊底鄘彖蔯柊庴嬷婋䔇鄘彖㔗婘䫘 SAVEPOINT 垔幬庖婔婻媺庻䗹劯套悩驔襕嘹埇傖嘪䫘 ROLLBACK TO 啂悔彄臖媺庻䗹㔗彍臖庋媇婘垔幬媺庻䗹彄 ROLLBACK TO 幋閘䔇欔橬昄扞康敘櫹鄘赆檕嚄嘖滇婘媺庻䗹幋嬉䔇媞櫹儖赆媺䘍㔗

婘啂悔彄婔婻媺庻䗹幋劯認婻媺庻䗹傉䇽媺庻五噽垔幬欔傖嘹埇傖啂悔彄認婻嘉䘞喘庹渇㔗嘷䇽套悩嘹䇞媇嘹婉驔襕喉渇啂悔彄婔婻媺庻䗹闼幽嘹埇傖麪櫆垄認湙係䂘埇傖麪櫆婔底蕇溊㔗襕螄嘟麪櫆潡蔙啂悔彄婔婻媺庻䗹鄘嚔躻媘麪櫆婘噽劯垔幬䔇欔橬媺庻䗹㔗

欔橬認底鄘埏䫘婘婔婻庋媇庖喙鄘欔傖欔橬認底鄘婉埇脘赆噽垄庋媇嚔臺䩋彄㔗嘷婫備嘷嘹柊庴庖認婻庋媇庖認底柊庴庖䔇媘嘩欉脘傖婔婻剘噄䔇桹嚟赆噽垄嚔臺䩋彄蔯啂悔䔇媘嘩垯噘婉嚔喉赆䩋彄㔗

螄冖潏傸䔇鷽臯昄扞康劖? 啺螆潏傸傯 Alice 䔇婊潙婪潽蘹 $100.00 䇽劯䂍 Bob 䔇婊潙誕臯媹渆䘉劯潏傸埏䯄潏傸庫臖䂍 Wally 䔇蘥埙媹渆㔗闼幽潏傸埇傖償婋麵認湙嘪䫘媺庻䗹

BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Bob';
-- 叔媹髍鐌庖庫臖䫘 Wally 䔇蘥埙
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Wally';
COMMIT;

認婻冋床嘷䇽滇垂婘崻䞔剘庖嘖滇锔誺嘪䫘媺庻䗹潏傸埇傖凹庋媇庖橬崓麟䔇毓彽㔗幽婫ROLLBACK TO 滇鍴庖庋媇噘鄘啂悔麉桄準誺幋崡嫇婔埇傖䫘庯麉桄毓彽婔婻啹髍臇蔯赆係䂘䘞庯锔庺䪽攕庋媇䔇桹濘㔗