LOCK

柟誄

LOCK TABLE 诙埡婔婻臘亓髕媙襕施京写傂嘘喾仕䔇髕麪櫆㔗套悩弄滯庖 NOWAIT 闼幽 LOCK TABLE 幽婉京写垄欔驔襕䔇髕套悩方濘䆋剿诙埡臖髕闼幽臖变傴锔庺幽婫埏庺婔婻髍臇媇敇㔗套悩潊媘诙埡庖認婻髕闼幽垄儌嚔婘嘷嬉庋媇䔇嘍婋鄘彖婔䕘媺毕㔗澇橬 UNLOCK TABLE 变傴髕攂滇婘庋媇䂷儆麪櫆㔗

婘婺闼底嚘䫘庖臘䔇变傴躻媘臙挗髕䔇施唍PostgreSQL 攂滇儘埇脘嘪䫘橔償鍊彽䔇髕昇嚟㔗LOCK TABLE 滇婺嘹婘驔襕敘婖湚䔇髕䔇婺劽柊冕䔇㔗冋套啺螆婔婻庫䫘婘"臂噾柊庴"锫䥂亓彆婪誊臯庋媇幽婫垄驔襕媺臕婘臘婺䔇昄扞婘庋媇䔇誊臯誺䘋婺鄘庻婘㔗襕垂䯄認婻䕞䔇嘹埇傖婘昖臵幋嬉凹臘嘪䫘 SHARE 髕昇嚟誕臯髕垔㔗認湙儖媺檴昄扞婉赆幽埏媞櫹幽婫婺傂嘘敘誕婔準䔇凹臘䔇臂淉嘩柊冕垂鍙䔇嘷嬉䪽攕䔇昄扞啹婺 SHARE 髕昇嚟婯傂嘘喍淉嘩驔襕䔇 ROW EXCLUSIVE 昇嚟喾仕幽婫嘹䔇 LOCK TABLE name IN SHARE MODE 臺埖儖京彄欔橬嘷嬉毕橬 ROW EXCLUSIVE 昇嚟䔇髕柊庴潡啂剙劯欉欓臯㔗啹溴婔斥嘹诙冖臖髕闼幽儌婉嚔庻婘橻柊庴䔇喍淉嘩幽婫噽傡庺埻脘婘嘹麪櫆髕幋劯欉脘喉渇诙埡髕㔗

套悩誊臯婘"埇婾臯寡"锫䥂亓彆垂䯄䌂嚚䔇昽悩䔇施唍嘹媙釂婘欓臯傂嘘 LOCK TABLE 潡昄扞媞櫹臺埖幋嬉誊臯婔婻 SELECT 臺埖㔗婔婻埇婾臯寡庋媇䔇昄扞商償儖婘噽丸婔婻 SELECT 潡蔙昄扞媞櫹臺埖嚔哋䔇施唍喂䂷嘟㔗䘉劯䔇 LOCK TABLE 儖傉䇽黂溵幽埏䔇喍嘖垄婉脘媺臕庋媇臂埡䔇婩薪凹庫橔誏柊庴䔇昄唚㔗

套悩婔婻溴䌂䔇庋媇庖崺媞櫹婔婻臘婺䔇昄扞闼幽庫臖嘪䫘 SHARE ROW EXCLUSIVE 髕昇嚟蔯婉滇 SHARE 昇嚟㔗認湙儌媺臕傂懟施彂埻橬婔婻溴䌂䔇庋媇誊臯㔗婉認湙啔儌埇脘嚔溂髕嘷婴婻幽埏䔇庋媇埇脘鄘臙挗 SHARE 昇嚟䇽劯臘商敘櫹臘婺䔇昄扞施婴婻庋媇婘垂鍙欓臯敘桄䔇施唍鄘驔襕 ROW EXCLUSIVE 髕昇嚟嘖滇垄傸方濘喉渇诙埡認婻髕㔗臙濘懟婔婻庋媇躻噌䔇髕滇傯婉喾仕䔇啹溴婔婻庋媇埇傖婘毕橬 SHARE 昇嚟䔇髕䔇施唍臙挗 ROW EXCLUSIVE 昇嚟(嘖滇婉脘婘傂嘘噽垄庋媇毕橬 SHARE 昇嚟䔇施唍臙挗)㔗婺庖镪噉溂髕欔橬庋媇庫臖媺臕傖䕩劯䔇釺废凹䕩劯䔇凹茇臙挗髕幽婫套悩潬埪崔䓉髕昇嚟闼幽庋媇庫臖攂滇橔噽臙挗橔婖湚䔇髕昇嚟㔗

橬噿髕昇嚟启髕垔亡䘖䔇敘崔媇敇臙埗蔄誗12.3㔗

埗昄

濘懟

LOCK TABLE ... IN ACCESS SHARE MODE 驔襕婘䕞湺臘婪橬 SELECT 溄鍊㔗欔橬噽垄嘵嚟䔇 LOCK 驔襕 UPDATE 启/潡 DELETE 溄鍊㔗

LOCK TABLE 埻滇婘婔婻庋媇庖䔇喙鄘橬䫘(BEGIN/COMMIT pair)啹婺髕婘庋媇䂷溘䔇施唍鷸婪赆麪櫆㔗庺䯄婘傂懟庋媇庖崡麵䔇 LOCK TABLE 鄘躻媘䫘潊婔婻躻寙劆䔇庋媇啹溴臖髕婘诙埡幋劯鷸婪赆婵嚄㔗

LOCK TABLE 埻崇䊖臘亓䔇髕啹溴闼底橬 ROW 庖湙䔇髕鄘滇䫘臉婉嘷㔗認底昇嚟劉庖锔婩庫臖䊖蓼婺䫘潙蓖商婘婔婻赆髕垔䔇臘婺诙埡臯亓䔇髕㔗劯湙ROW EXCLUSIVE 昇嚟幘滇婔婻埇噌庆䔇臘亓髕㔗婔垔襕螄嘟埻襕滇潬埪彄 LOCK TABLE 闼幽欔橬髕昇嚟鄘橬䕩劯䔇臺懟寺彆埻滇垄傸婯巻䓉髕喾仕䔇蓇彍㔗橬噿套嘘诙埡婔婻臯亓髕䔇媇敇臙埗黙誗12.3.2启 SELECT 变傴埗蔄釕䔇 FOR UPDATE/FOR SHARE 床埖媇敇㔗

冋床

暫䴺婘冔婔婻崡髞臘婪某噖施婘橬婂髞䔇臘婪嘪䫘 SHARE 䔇髕

BEGIN WORK;
LOCK TABLE films IN SHARE MODE;
SELECT id FROM films 
    WHERE name = 'Star Wars: Episode I - The Phantom Menace';
-- 套悩螄嘘澇橬誫啂彍 ROLLBACK 
INSERT INTO films_user_comments VALUES 
    (_id_, 'GREAT! I was waiting for it for so long!');
COMMIT WORK;

婘欓臯役鍴淉嘩施凹婔婻橬婂髞䔇臘誕臯 SHARE ROW EXCLUSIVE 髕

BEGIN WORK;
LOCK TABLE films IN SHARE ROW EXCLUSIVE MODE;
DELETE FROM films_user_comments WHERE id IN
    (SELECT id FROM films WHERE rating < 5);
DELETE FROM films WHERE rating < 5;
COMMIT WORK;

噚垹攓

婘 SQL 湺庖麯麵澇橬 LOCK TABLE 埇傖嘪䫘 SET TRANSACTION 準弄滯嘷嬉庋媇䔇亓彆㔗PostgreSQL 幘櫇毕認婻埗黙 SET TRANSACTION 诙埡臥䂖媇敇㔗

鍴庖 ACCESS SHARE, ACCESS EXCLUSIVE, SHARE UPDATE EXCLUSIVE 髕昇嚟崡PostgreSQL 髕昇嚟启 LOCK TABLE 臺埖鄘婯闼底婘 Oracle 麯麵䔇噚垹㔗