あなたはショッピングモールサイトの商品購入モジュールを開発しました。
それは、単純な作りで予め登録されているproductを全ユーザに対してstock数の10個まで販売できるというシステムでした。stock数まで販売するとsold out状態になり、再販の予定はありません。

createtable.sql

CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    stock INT NOT NULL default 10
);

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY
);

CREATE TABLE user_products (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    order_count INT NOT NULL default 0,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

しかし、運用後すぐに問題にぶつかりました。default値のstock数10個以上購入されたproductが複数存在していたのです。

目的：カラムの値を購入契機ごとに減算する

購入毎にstock数を減算し、0以下になる場合はユーザに購入できないメッセージを出すことになります。

transaction.sql

begin;
--購入数分の在庫数があるか？
select stock from products where id = $1 and stock - $2 >=  0; [$1:1, $2:(ユーザが希望する購入数)]
+-------+
| stock |
+-------+
|  10   |
+-------+
-- ここでレコード結果が0行の場合は売り切れ又は、在庫数以上の購入を希望しているとみなし
-- ユーザへ在庫切れのメッセージを表示します (rollback;)
-- SQLのみのアンチパターンとして紹介したい為、あえてSQLのみで完結させています。
-- applicationでif( 10 - 購入希望数 >= 0 ) {} とすることと同義と捉えて下さい。

--在庫数の減算
update products set stock = $1 - $2 where id = $3; [$1:10, $2:(ユーザが希望する購入数), $3:1]

--購入情報の登録
insert into user_products (user_id, product_id, order_count)
    values( $1, $2, $3 ); [$1:(購入者ID), $2:1, $3:(ユーザが希望する購入数)]

commit;

アンチパターン：他処理で変更された可能性のある値をUPDATE SETで値として使用する

set stock = $1 - $2の\$1には在庫確認のSELECT SQLで取得した結果をbindしています。
そうこの手順ではselectからupdateまでに他のユーザがproductの購入を完了した時、productに記録されたstock数が変更されたにも関わらず、変更後の値をオブサーブしていない為、select時点でのstock数から減算を行ってしまいます。
stock数に記録される数字は必ずマイナスにはなりませんが、実際に注文されたorder_countのサマリは当初の販売数の10個を上回る可能性が出てきてしまいます。

この問題はいくらselectとupdateの間隔を限りなく0秒に近づけ、他購入者が割り込む隙間を与えないようなパフォーマンスを期待したとしても、適切な対応をしない限り発生する可能性があることに注意して下さい。

はい。所謂ロストアップデートという問題です。

アンチパターンの見つけ方

set stock = 10 - 1のように更新ソースが何であるか？そのソースは他の取引処理から変更される可能性のある値であるか？更新される可能性がある場合、アンチパターンの可能性があります。

解決策

トランザクション隔離レベルをSERIALIZABLEにする

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
select * from products where id = $1; [$1:1]
 id | stock 
----+-------
  1 |    10
(1 行)
update products set stock = $1 - $2 where id = $3; [$1:10, $2:(ユーザが希望する購入数), $3:1]
ERROR:  同時に発生した更新のためアクセスの直列化ができませんでした

SELECT 〜 UPDATEの間にSELECT対象のレコードに変更が入ると更新処理が失敗します。
stock以外のカラムに変更があった場合(例えば一般的なcreated_atやupdated_at)も変更は失敗します。
よって十分な在庫数があるにも関わらず、ユーザには正常に購入できないといった失敗体験を強いることになります。
またselect * from products where id = 1;は必ずSERIALIZABLEトランザクション内ブロックで発行する必要があります。BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLEを発行する前にSELECTした場合、更新は成功します。

select * from products where id = 1;
 id | stock |  
----+-------+
  1 |    10 |
(1 行)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
--この間、別トランザクションで変更があってもupdateは成功する
update products set stock = 10 - 1 where id = 1;
UPDATE 1
commit;

SELECT FOR UPDATEで他購入者からの変更をブロックする

在庫数の取得時に他購入者からの変更をブロックし、更新時に使用するstock数が必ず正しいstock数であることを担保します。仮に他購入者がすでにブロックしている場合は、他購入者の購入処理が完了するまで、参照を待機します。

transaction.sql

begin;
--購入数分の在庫数があるか？
select stock from products where id = $1 and stock - $2 >=  0 FOR UPDATE; [$1:1, $2:(ユーザが希望する購入数)]
+-------+
| stock |
+-------+
|  10   |
+-------+
-- ここで結果が返ってこない場合は他購入者がアクセスしている。
-- ここでレコード結果が0行の場合は売り切れ又は、在庫数以上の購入を希望しているとみなし
-- ユーザへ在庫切れのメッセージを表示します (rollback;)-- ユーザへ在庫切れのメッセージを表示します (rollback;)

--在庫数の減算
update products set stock = $1 - $2 where id = $3; [$1:10, $2:(ユーザが希望する購入数), $3:1]

--購入情報の登録
insert into user_products (user_id, product_id, order_count)
    values( $1, $2, $3 ); [$1:(購入者ID), $2:1, $3:(ユーザが希望する購入数)]
commit;

すでに記録されている値から更新を行う

ただし、SELECT FOR UPDATEには多少のオーバーヘッドがあります。それは、在庫数確認の為、一度SELECTクエリを実行しなければならないこと。そして、SELECT FOR UPDATEからUPDATE処理の間に止むを得ない事情で何らかの処理を挟まなければならず、長時間のロックを掴んでしまった場合、システム全体のパフォーマンスに影響を及ぼす可能性があります。

すでに記録されている値をソースに更新を行うという手法を紹介します。

begin;
update products set stock = stock - $1 where id = $2 and stock - $3 >=  0; [$1:(ユーザが希望する購入数), $2:1, $3:(ユーザが希望する購入数)]
 UPDATE 1
 -- UPDATE 0の場合は在庫不足。(rollback;)
insert into user_products (user_id, product_id, order_count)
    values( $1, $2, $3 ); [$1:(購入者ID), $2:1, $3:(ユーザが希望する購入数)]
commit;

ご覧の通り、在庫数確認の為のSELECTを発行する必要がなく、安全に在庫数へアクセスすることが可能です。在庫があったか否かの判断はUPDATE 1またはUPDATE 0から判断できるかと存じます。但し、お使いのフレームワークやライブラリ、DBドライバで実現できない場合は、他の手法を選択して下さい。
今回の主旨とは外れますが、条件付きUPDATEで影響があった行数(affected rows)を調べるのは良く使うテクニックなので是非ご活用下さい。

他の購入者がアップデートを行っているがcommit前にアップデートを行っていたらどうなるの？

他の購入者がcommitまたはrollbackするまでupdate処理が中断します。他の購入者がアップデートの為のロックを取っている状態です。他の購入者がcommit(またはrollback)後の結果を元にupdateが始まります。

アンチパターンを用いてもよい場合

別コミットの値を無視して良い場合。厳密な値として扱わなくて良い場合など。

user_productsのorder_countのsum数で在庫数を確認するのは？

order_countのsum後からupdateするまでの間に注文が入ってしまいますと、同様の現象が起こり得ます。insertをブロックするtableロックが必要になるかと存じます。この場合、同じ商品以外の取引もブロックされる可能性がある為、筋は良くないと思います。

あとがき

これ以上ロストアップデートは引っ張らないつもりなので、ロストアップデートおじさんと呼ばないで下さい。

二つのTBLをJOINする時の結合方向を指定する。
引用元

INNER JOIN：両方の表で一致する行だけを表示させる

SELECT 列名1,列名2,〜列名n ← どの列を表示させるか
FROM 「表名1」 INNER JOIN 「表名2」 ON 表名1.フィールド名 = 表名2.フィールド名
WHERE 抽出条件

　　→　表名１と表明２の一致する行のみ

LEFT JOIN：左側に指定された表のすべての行が表示される

SELECT 列名1,列名2,〜列名n
FROM 「表名1」 LEFT JOIN 「表名2」 ON 表名1.フィールド名 = 表名2.フィールド名
WHERE 抽出条件

　　→　左側の表名1のすべての行が表示される

RIGHT JOIN：右側に指定された表のすべての行が表示される

SELECT 列名1,列名2,〜列名n
FROM 「表名1」 RIGHT JOIN 「表名2」 ON 表名1.フィールド名 = 表名2.フィールド名
WHERE 抽出条件

　　　→　右側の表名2のすべての行が表示される

参考：INNER JOIN

SELECT　*
FROM　売上表,顧客表
WHERE　売上表.顧客CD=顧客表.顧客CD

ORACLE-Refs

javaのstream
で特定のフィールドが一致するインスタンス毎の処理

List<Hoge> hogeList = 初期化;
Map<Integer,List<Hoge>> hogeMap = hogeList.stream().collect(Collectors.groupingBy(hoge -> hoge.getId()));
for(Integer id : hogeMap.keySet()){
    for(Hoge hoge : hogeMap.get(id)){
        //hogeに対する処理
    }
}

Primaryキーが複数の場合は(伝われ)Keyを文字列結合等すればよいと思う。

SQLのin句
複合主キー(カラムA, B)が設定されたテーブルから、
カラムA をin句での取得条件とし、
in句内すべての値が一致する、カラムB を取得したいときのクエリ。
※適当に考えたシチュエーション

SELECT
    カラムB,
    count(*)
FROM
    テーブル
WHERE
    カラムA in (?)
GROUP BY
    カラムB
HAVING count(*) = 条件の個数(?の数)

カラムC(bool) があったとして、
カラムA in (1,2,3) の場合 カラムC = true 、
カラムA in (4,5,6) の場合 カラムC = false　
といった条件全てに当てはまる カラムB を取得したいみたいな時は、
上記クエリと、上記クエリをサブクエリとしたものとを内部結合し、
それぞれの取得条件内に カラムC の条件を追加すればいける気がする。

パフォーマンスは度外視

---

自宅に環境がないため、すべて妄想。
適当にチャチャっと書いたのでおそらくミスが多い。
とりあえず投稿のテストです。内容はないよぅ。
お試し投稿なので多めに見てください。
今後書くことがあれば真面目に書きます。