YanoRyuichi.com/
Wiki
Blog
GitHub
Sandbox
開始行:
* 外部キーなカラムに対するFOR UPDATE [#h9cd667c]
** 前提 [#dd6dfed9]
=> SELECT * FROM t1;
t1_id | n | t2_id
-------+----+-------
1 | 10 | 1
2 | 20 | 2
=> SELECT * FROM t2;
t2_id | n
-------+-----
1 | 100
2 | 200
- t1テーブルのt2_idカラムは外部キー制約が設定されている
** SQL [#p7021ee9]
Session A | Session B ...
========================================================...
=> BEGIN; SELECT * FROM t1 WHER | => ...
E t1_id = 1 FOR UPDATE; | => ...
t1_id | n | t2_id | => ...
-------+----+------- | => ...
1 | 10 | 1 | => ...
| => ...
=*> | => UPDATE t2 SET n = 1...
=*> | _id = 1; ...
=*> | => ...
=*> | => UPDATE t2 SET t2_id...
=*> | RE t2_id = 1; ...
=*> | ...
=*> | ...
=*> COMMIT; | ...
| ERROR: update or dele...
=> | le "t2" violates forei...
=> | nstraint "t1_t2_id_fke...
| le "t1" ...
| DETAIL: Key (t2_id)=(...
| ll referenced from tab...
| ...
| => SELECT * FROM t2; ...
| t2_id | n ...
| -------+----- ...
| 2 | 200 ...
| 1 | 1 ...
| ...
終了行:
* 外部キーなカラムに対するFOR UPDATE [#h9cd667c]
** 前提 [#dd6dfed9]
=> SELECT * FROM t1;
t1_id | n | t2_id
-------+----+-------
1 | 10 | 1
2 | 20 | 2
=> SELECT * FROM t2;
t2_id | n
-------+-----
1 | 100
2 | 200
- t1テーブルのt2_idカラムは外部キー制約が設定されている
** SQL [#p7021ee9]
Session A | Session B ...
========================================================...
=> BEGIN; SELECT * FROM t1 WHER | => ...
E t1_id = 1 FOR UPDATE; | => ...
t1_id | n | t2_id | => ...
-------+----+------- | => ...
1 | 10 | 1 | => ...
| => ...
=*> | => UPDATE t2 SET n = 1...
=*> | _id = 1; ...
=*> | => ...
=*> | => UPDATE t2 SET t2_id...
=*> | RE t2_id = 1; ...
=*> | ...
=*> | ...
=*> COMMIT; | ...
| ERROR: update or dele...
=> | le "t2" violates forei...
=> | nstraint "t1_t2_id_fke...
| le "t1" ...
| DETAIL: Key (t2_id)=(...
| ll referenced from tab...
| ...
| => SELECT * FROM t2; ...
| t2_id | n ...
| -------+----- ...
| 2 | 200 ...
| 1 | 1 ...
| ...
ページ名:
