行ロック - FOR UPDATEFOR UPDATE Session A | Session B |
========================================================================================================================================
=> BEGIN; SELECT * FROM t1 WHERE id = 1 FOR UPDATE; | | 1. AのSELECTの結果はすぐに表示
| | される
id | n | |
----+----- | |
1 | 200 | |
| => BEGIN; SELECT * FROM t1 WHERE id = 1 FOR UPDA | 2. BのSELECTはブロックされ、すぐ
| TE; | には表示されない
| |
=*> UPDATE t1 SET n = 100 WHERE id = 1; | | 3. AのUPDATEがCOMMITされると、
=*> COMMIT; | |
| id | n | 4. ブロックが解除されて
| ----+----- | BのSELECTの結果が表示される
| 1 | 100 |
FOR UPDATE NOWAIT Session A | Session B |
==========================================================================================================================================
=> BEGIN; SELECT * FROM t1 WHERE id = 1 FOR UPDATE NOWAIT;| | 1. NOWAITでSELECTすると、
id | n | |
----+----- | |
1 | 100 | |
| |
| |
| => BEGIN; SELECT * FROM t1 WHERE id = 1 FOR UPDAT | 2. BのSELECTはブロックされずに
| E NOWAIT; | 即時に失敗する
| |
| ERROR: could not obtain lock on row in relation |
| "t1" |
=*> UPDATE t1 SET n = 200 WHERE id = 1; | |
=*> COMMIT; | |
| =!> ROLLBACK; | 3. BでROLLBACKすると、
| => SELECT * FROM t1 WHERE id = 1; | AのUPDATEの結果は反映され
| id | n | ている
| ----+----- |
| 1 | 200 |
| |
FOR UPDATE SKIP LOCKED Session A | Session B |
======================================================================================================
=> BEGIN; SELECT * FROM t1 WHER | => | 1. SKIP LOCKEDでSELECTすると、
E id = 1 FOR UPDATE SKIP LOCKED | => |
; | => |
id | n | => |
----+----- | => |
1 | 200 | => |
| => |
=*> | => BEGIN; SELECT * FROM t1 WHER | 2. BのSELECTはAでロックされた行を
=*> | E id = 1 OR id = 2 FOR UPDATE S | SKIPして結果が表示される
=*> | KIP LOCKED; |
=*> | id | n |
=*> | ----+----- |
=*> | 2 | 100 |
=*> | |
=*> UPDATE t1 SET n = 200 WHERE | =*> | 3. AでUPDATE、COMMITすると、
id = 1; | =*> |
=*> COMMIT; | =*> |
=> | =*> SELECT * FROM t1; | 4. ロックが解除されて、BのSELECTに
| id | n | 結果として表示される
| ----+----- |
| 2 | 100 |
| 3 | 100 |
| 1 | 200 |
| |
|
|
