[Multiple Database Banking Simulation] 5. DB
ERD : https://www.erdcloud.com/d/vMRfCE3LhquWpC8kE
그 전의 내용을 기반으로 ERD를 대충 그렸다. 이제 이것을 각 DB에 설정을 해주면된다.
Table 생성
MySQL
CREATE TABLE accounts (
account_id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
status varchar(1) NOT NULL,
balance DECIMAL(19,4) NOT NULL DEFAULT 0,
hold_amount DECIMAL(19,4) NOT NULL DEFAULT 0,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE transactions (
txn_id BIGINT AUTO_INCREMENT PRIMARY KEY,
type VARCHAR(1) NOT NULL,
status VARCHAR(1) NOT NULL,
src_account_id BIGINT,
dst_account_id BIGINT,
dst_bank VARCHAR(2),
amount DECIMAL(19,4) NOT NULL,
idempotency_key VARCHAR(100) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY ux_txn_idemp (idempotency_key),
CONSTRAINT fk_txn_src FOREIGN KEY (src_account_id) REFERENCES accounts(account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE ledger_entries (
entry_id BIGINT AUTO_INCREMENT PRIMARY KEY,
txn_id BIGINT NOT NULL,
account_id BIGINT NOT NULL,
amount DECIMAL(19,4) NOT NULL, -- +입금, -출금
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
CONSTRAINT fk_ledger_txn FOREIGN KEY (txn_id) REFERENCES transactions(txn_id),
CONSTRAINT fk_ledger_account FOREIGN KEY (account_id) REFERENCES accounts(account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE holds (
hold_id BIGINT AUTO_INCREMENT PRIMARY KEY,
account_id BIGINT NOT NULL,
amount DECIMAL(19,4) NOT NULL,
status VARCHAR(1) NOT NULL,
idempotency_key VARCHAR(100) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY ux_hold_idemp (idempotency_key),
CONSTRAINT fk_hold_account FOREIGN KEY (account_id) REFERENCES accounts(account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;Oracle
CREATE TABLE accounts (
account_id NUMBER(19) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name VARCHAR2(20) NOT NULL,
status VARCHAR2(1) NOT NULL,
balance NUMBER(19,4) DEFAULT 0 NOT NULL,
hold_amount NUMBER(19,4) DEFAULT 0 NOT NULL,
created_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
updated_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
);
CREATE TABLE transactions (
txn_id NUMBER(19) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
type VARCHAR2(1) NOT NULL,
status VARCHAR2(1) NOT NULL,
src_account_id NUMBER(19),
dst_account_id NUMBER(19),
dst_bank VARCHAR2(2),
amount NUMBER(19,4) NOT NULL,
idempotency_key VARCHAR2(100) NOT NULL,
created_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
updated_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
CONSTRAINT fk_txn_src FOREIGN KEY (src_account_id) REFERENCES accounts(account_id),
CONSTRAINT ux_txn_idemp UNIQUE (idempotency_key)
);
CREATE TABLE ledger_entries (
entry_id NUMBER(19) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
txn_id NUMBER(19) NOT NULL,
account_id NUMBER(19) NOT NULL,
amount NUMBER(19,4) NOT NULL,
created_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
updated_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
CONSTRAINT fk_ledger_txn FOREIGN KEY (txn_id) REFERENCES transactions(txn_id),
CONSTRAINT fk_ledger_account FOREIGN KEY (account_id) REFERENCES accounts(account_id)
);
CREATE TABLE holds (
hold_id NUMBER(19) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
account_id NUMBER(19) NOT NULL,
amount NUMBER(19,4) NOT NULL,
status VARCHAR2(1) DEFAULT 'O' NOT NULL,
idempotency_key VARCHAR2(100) NOT NULL,
created_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
updated_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
CONSTRAINT fk_hold_account FOREIGN KEY (account_id) REFERENCES accounts(account_id),
CONSTRAINT ux_hold_idemp UNIQUE (idempotency_key)
);MongoDB
db.createCollection("accounts", {
validator: { $jsonSchema: {
bsonType: "object",
required: ["_id","name","status","balance","hold_amount","created_at"],
properties: {
_id: { bsonType: "string", minLength: 3 }, // 계좌번호
name: { bsonType: "string", minLength: 1, maxLength: 20 },
status: { bsonType: "string", minLength: 1, maxLength: 1 }, // 한 글자 코드
balance: { bsonType: "decimal" }, // Decimal128
hold_amount: { bsonType: "decimal" }, // Decimal128
created_at: { bsonType: "date" },
updated_at: { bsonType: ["date","null"] }
}
}}, validationLevel: "strict"
});
db.createCollection("transactions", {
validator: { $jsonSchema: {
bsonType: "object",
required: ["type","status","amount","idempotency_key","created_at"],
properties: {
type: { bsonType: "string", minLength: 1, maxLength: 1 },
status: { bsonType: "string", minLength: 1, maxLength: 1 }, // 기본값은 애플리케이션에서 셋
src_account_id: { bsonType: ["objectId","null"] },
dst_account_id: { bsonType: ["objectId","null"] },
dst_bank: { bsonType: ["string","null"], maxLength: 2 },
amount: { bsonType: "decimal" },
idempotency_key: { bsonType: "string", minLength: 1, maxLength: 100 },
created_at: { bsonType: "date" },
updated_at: { bsonType: ["date","null"] }
}
}}, validationLevel: "strict"
});
db.createCollection("ledger_entries", {
validator: { $jsonSchema: {
bsonType: "object",
required: ["txn_id","account_id","amount","created_at"],
properties: {
txn_id: { bsonType: "objectId" },
account_id: { bsonType: "objectId" },
amount: { bsonType: "decimal" },
created_at: { bsonType: "date" },
updated_at: { bsonType: ["date","null"] }
}
}}, validationLevel: "strict"
});
db.createCollection("holds", {
validator: { $jsonSchema: {
bsonType: "object",
required: ["account_id","amount","status","idempotency_key","created_at"],
properties: {
account_id: { bsonType: "objectId" },
amount: { bsonType: "decimal" },
status: { bsonType: "string", minLength: 1, maxLength: 1, },
idempotency_key: { bsonType: "string", minLength: 1, maxLength: 100 },
created_at: { bsonType: "date" },
updated_at: { bsonType: ["date","null"] }
}
}}, validationLevel: "strict"
});첫 MONGO라 조금 헷갈렸는데 GPT랑 대화하면서 작성하다보니 MongoDB가 좀 신기하고 재밌어보인다.
PostgreSQL
CREATE TABLE accounts (
account_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name VARCHAR(20) NOT NULL,
status VARCHAR(1) NOT NULL,
balance NUMERIC(19,4) NOT NULL DEFAULT 0,
hold_amount NUMERIC(19,4) NOT NULL DEFAULT 0,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE transactions (
txn_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
type VARCHAR(1) NOT NULL,
status VARCHAR(1) NOT NULL,
src_account_id BIGINT,
dst_account_id BIGINT,
dst_bank VARCHAR(2),
amount NUMERIC(19,4) NOT NULL,
idempotency_key VARCHAR(100) NOT NULL UNIQUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
FOREIGN KEY (src_account_id) REFERENCES accounts(account_id)
);
CREATE TABLE ledger_entries (
entry_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
txn_id BIGINT NOT NULL REFERENCES transactions(txn_id),
account_id BIGINT NOT NULL REFERENCES accounts(account_id),
amount NUMERIC(19,4) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE holds (
hold_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
account_id BIGINT NOT NULL REFERENCES accounts(account_id),
amount NUMERIC(19,4) NOT NULL,
status VARCHAR(1) NOT NULL,
idempotency_key VARCHAR(100) NOT NULL UNIQUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
데이터 생성
우선 각 은행에 계좌정보를 넣어야한다. 물론 가상의 데이터지만 그럴싸한 데이터를 만들고 싶어서 최근 작명이름(성씨 제거) 데이터와 우리나라 성씨분포를 기준으로 작성하였다. 그래서 필요한게 성씨+이름으로 완전한 이름을 만들어줄 프로그램이 필요했다.
그래서 만든 파이썬 프로그램의 코드는 아래와 같다.
import pandas as pd
import random
first_name=[]
first_probability=[]
#확률로 성씨 return
def sample_from_probabilities():
global first_name
global first_probability
samples = random.choices(first_name, weights=first_probability, k=1)
return samples[0]
# 엑셀 파일 읽기 (첫 번째 시트 자동 선택)
name = pd.read_excel("people_name.xlsx", usecols=["A"], engine="openpyxl")
first = pd.read_excel("people_name.xlsx", sheet_name=1, usecols=["c","d"], engine="openpyxl")
# 데이터 출력
for item in first.values:
first_name.append(item[0])
first_probability.append(item[1]/100)
# 데이터 생성
data = {
"계좌번호": [],
"이름": [],
"잔여금액": []
}
start=1
index=0
accid="001"
#900단위로 아래에 넣음 구분은
# oracle mysql postgresql mongodb
for item in name.values:
if index ==900:
accid ="002"
start=1
elif index ==1800:
accid ="003"
start=1
elif index ==2700:
accid ="004"
start=1
data["계좌번호"].append(accid+str(start).zfill(10))
data["이름"].append(sample_from_probabilities()+item[0])
data["잔여금액"].append(0)
start+=1
index+=1
# DataFrame 생성
df = pd.DataFrame(data)
# CSV 파일 저장
df.to_csv("data.csv", index=False, encoding="utf-8-sig")
print("CSV 파일이 생성되었습니다!")
- 계좌번호가 쓸데없이 긴 것 같아서 엑셀로 조금 수정해주었다.
순서대로 계좌번호,이름,금액이다. RDG를 돌리면 여기서 랜덤으로 값을 뽑아서 계좌이체를 진행한다. 누가 선택될지 얼마를 누구한테 보낼지 다 랜덤값이다. 각 은행에는 795명의 계좌가 들어있고 각 은행에 한명에게 1억이 들어있다.
- Mongo DB에는 내 이름이 들어있고 1억원이 들어있음.ㅎ
그래서 이제 csv로 되어있는 데이터를 각 DB에 Insert해주면된다. 그냥 vsc에서 한번에 해줄거라 따로 적지는 않는다.
Mongo
Mysql
Oracle
PostgreSQL
이체 Procedure / Function
MYSQL
일차적으로 MYSQL로 이체에 필요한 프로시저를 생성했다.
-- ---------------------------------------
-- PROCEDURE --
-- ---------------------------------------
-- DBMS :MySQL
-- Title : 입금 확정
-- Detail : 입금 확정 프로시저
CREATE PROCEDURE sp_confirm_credit_local(
IN p_idempotency_key VARCHAR(100),
OUT p_txn_id BIGINT,
OUT p_status VARCHAR(1),
OUT p_result VARCHAR(32)
)
proc:BEGIN
DECLARE v_txn_id BIGINT;
DECLARE v_dst BIGINT;
DECLARE v_amt DECIMAL(19,4);
START TRANSACTION;
-- 거래 조회(입금 계좌/금액)
SELECT txn_id, dst_account_id, amount
INTO v_txn_id, v_dst, v_amt
FROM transactions
WHERE idempotency_key = p_idempotency_key
FOR UPDATE;
-- 멱등: 이미 해당 분개가 존재하면 이미 처리된 것으로 간주 (선택적으로 UNIQUE(txn_id,account_id,amount) 인덱스 권장)
IF EXISTS (
SELECT 1 FROM ledger_entries
WHERE txn_id = v_txn_id AND account_id = v_dst AND amount = v_amt
) THEN
COMMIT;
SET p_txn_id = v_txn_id;
SET p_status = '2';
SET p_result = 'ALREADY_POSTED';
LEAVE proc;
END IF;
-- 입금 계좌 잠금
SELECT account_id FROM accounts WHERE account_id = v_dst FOR UPDATE;
-- 입금 확정
UPDATE accounts
SET balance = balance + v_amt
WHERE account_id = v_dst;
-- 분개(입금 +)
INSERT INTO ledger_entries(txn_id, account_id, amount)
VALUES (v_txn_id, v_dst, v_amt);
-- 입금만 끝나도 POSTED
UPDATE transactions SET status = 2 WHERE txn_id = v_txn_id;
COMMIT;
SET p_txn_id = v_txn_id;
SET p_status = '2';
SET p_result = 'OK';
END
-- ---------------------------------------
-- PROCEDURE --
-- ---------------------------------------
-- DBMS :MySQL
-- Title : 출금 확정
-- Detail : 출금 확정 프로시저
CREATE PROCEDURE sp_confirm_debit_local(
IN p_idempotency_key VARCHAR(100),
OUT p_txn_id BIGINT,
OUT p_status VARCHAR(1),
OUT p_result VARCHAR(32)
)
proc:BEGIN
DECLARE v_txn_id BIGINT;
DECLARE v_src BIGINT;
DECLARE v_amt DECIMAL(19,4);
DECLARE v_hstat TINYINT;
START TRANSACTION;
-- 거래 조회(출금 계좌/금액 확보)
SELECT txn_id, src_account_id, amount
INTO v_txn_id, v_src, v_amt
FROM transactions
WHERE idempotency_key = p_idempotency_key
FOR UPDATE;
-- hold 확인
SELECT status INTO v_hstat
FROM holds
WHERE idempotency_key = p_idempotency_key
FOR UPDATE;
IF v_hstat IS NULL THEN-- hold 없음
ROLLBACK;
SET p_txn_id = v_txn_id;
SET p_status = 1; -- 거래 상태는 POSTED 전이므로 PENDING 등 정책에 맞게
SET p_result = 'HOLD_NOT_FOUND';
LEAVE proc;
ELSEIF v_hstat = 3 THEN-- RELEASED: 확정 전 취소됨 → 확정 불가
ROLLBACK;
SET p_txn_id = v_txn_id;
SET p_status = 1; -- 또는 CANCELED=7을 도입해 7 반환
SET p_result = 'HOLD_RELEASED';
LEAVE proc;
ELSEIF v_hstat = 2 THEN-- CAPTURED: 이미 확정(멱등)
COMMIT; -- 변경 없이 잠금만 해제
SET p_txn_id = v_txn_id;
SET p_status = 2; -- POSTED로 간주(정책에 맞게 조정)
SET p_result = 'ALREADY_CONFIRMED';
LEAVE proc;
ELSEIF v_hstat = 1 THEN
-- OPEN: 정상 진행 (여기서는 빠져나가지 않고 아래 확정 로직으로 계속)
-- no-op: 아래 단계에서 계좌 잠금 → hold↓, balance 이동 → ledger 기록 → 상태 전이
-- 출금 계좌 잠금
SELECT account_id FROM accounts WHERE account_id = v_src FOR UPDATE;
-- 출금 확정
UPDATE accounts
SET hold_amount = hold_amount - v_amt,
balance = balance - v_amt
WHERE account_id = v_src;
-- 분개(출금 −)
INSERT INTO ledger_entries(txn_id, account_id, amount)
VALUES (v_txn_id, v_src, -v_amt);
-- 상태 전이
UPDATE holds SET status = 2 WHERE idempotency_key = p_idempotency_key; -- CAPTURED
-- 출금만 끝나도 POSTED
UPDATE transactions SET status = 2 WHERE txn_id = v_txn_id; -- 2=POSTED
COMMIT;
SET p_txn_id = v_txn_id;
SET p_status = '2';
SET p_result = 'OK';
END IF;
END
-- ---------------------------------------
-- PROCEDURE --
-- ---------------------------------------
-- DBMS :MySQL
-- Title : 수금(보류)
-- Detail : 수금(보류) 프로시저
CREATE PROCEDURE sp_receive_prepare(
IN p_src_account_id BIGINT,
IN p_dst_account_id BIGINT,
IN p_dst_bank VARCHAR(2),
IN p_amount DECIMAL(19,4),
IN p_idempotency_key VARCHAR(100),
IN p_type VARCHAR(1),
OUT p_txn_id BIGINT,
OUT p_status VARCHAR(1)
)
PROC:BEGIN
DECLARE v_txn_id BIGINT;
DECLARE v_exists INT;
START TRANSACTION;
SET p_status = '1';
INSERT INTO transactions(
type,
status,
src_account_id,
dst_account_id,
dst_bank,
amount,
idempotency_key)
VALUES (
p_type,
p_status,
p_src_account_id,
p_dst_account_id,
p_dst_bank,
p_amount,
p_idempotency_key)
ON DUPLICATE KEY UPDATE txn_id = LAST_INSERT_ID(txn_id);
SET v_txn_id = LAST_INSERT_ID();
-- 수취 계좌 조회 없을시 status = 6으로 update
SELECT COUNT(*) INTO v_exists
FROM accounts
WHERE account_id = p_dst_account_id;
IF v_exists = 0 THEN
-- 실패 기록 남기고 커밋, 에러는 던지지 않음
SET p_status = '6';
UPDATE transactions
SET status = p_status
WHERE txn_id = v_txn_id;
COMMIT;
SET p_txn_id = v_txn_id;
LEAVE proc; -- 끝
END IF;
COMMIT;
SET p_txn_id = v_txn_id;
END
-- ---------------------------------------
-- PROCEDURE --
-- ---------------------------------------
-- DBMS :MySQL
-- Title : 송금(보류)
-- Detail : 송금(보류) 프로시저
CREATE PROCEDURE sp_remittance_hold(
IN p_src_account_id BIGINT,
IN p_dst_account_id BIGINT,
IN p_dst_bank VARCHAR(2),
IN p_amount DECIMAL(19,4),
IN p_idempotency_key VARCHAR(100),
IN p_type VARCHAR(1),
OUT p_txn_id BIGINT,
OUT p_status VARCHAR(1)
)
PROC:BEGIN
DECLARE v_balance DECIMAL(19,4);
DECLARE v_hold DECIMAL(19,4);
DECLARE v_txn_id BIGINT;
START TRANSACTION;
-- 1) transactions 멱등 생성/재사용
SET p_status = "1";
INSERT INTO transactions(
type,-- 1: 내부이체 2: 외부로 보냄 3: 외부에서 들어옴
status,-- 1: 생성 완료 2: 원장 기장 완료 3: 정산 확정 4: 역분개 5: 잔액부족 6: 상대계좌x
src_account_id,
dst_account_id,
dst_bank,
amount,
idempotency_key
)
VALUES (
p_type,
p_status,
p_src_account_id,
p_dst_account_id,
p_dst_bank,
p_amount,
p_idempotency_key
)
ON DUPLICATE KEY UPDATE txn_id = LAST_INSERT_ID(txn_id);
SET v_txn_id = LAST_INSERT_ID();
-- 2) 출금 계좌 잠금 + 가용 금액 확인
SELECT balance, hold_amount INTO v_balance, v_hold
FROM accounts
WHERE account_id = p_src_account_id
FOR UPDATE;-- 조건에 맞는 ROW LOCK -> COMMIT; 시 UNLOCK
-- 가용 금액 확인 후 없으면 status 5로 return
IF v_balance - v_hold < p_amount THEN
SET p_status = "5";
UPDATE transactions SET status = p_status , updated_at=NOW() WHERE txn_id=v_txn_id; -- status='5' 실패
COMMIT;
LEAVE proc; -- 끝
END IF;
-- 3) holds 멱등 생성
INSERT INTO holds(
account_id,
amount,
status,
idempotency_key
)
VALUES (
p_src_account_id,
p_amount,
'1', -- OPEN (대기상태)
p_idempotency_key
)
ON DUPLICATE KEY UPDATE hold_id = LAST_INSERT_ID(hold_id);
-- 4) hold_amount 반영
UPDATE accounts
SET hold_amount = hold_amount + p_amount
WHERE account_id = p_src_account_id;
COMMIT;
SET p_txn_id = v_txn_id;
END
-- ---------------------------------------
-- PROCEDURE --
-- ---------------------------------------
-- DBMS :MySQL
-- Title : 이체 확정
-- Detail : 이체 확정(입/출 같은은행) 프로시저
CREATE PROCEDURE sp_transfer_confirm_internal(
IN p_idempotency_key VARCHAR(100),
OUT p_status VARCHAR(1),
OUT p_result VARCHAR(32)
)
PROC:BEGIN
DECLARE v_txn_id BIGINT;
DECLARE v_src BIGINT;
DECLARE v_dst BIGINT;
DECLARE v_amt DECIMAL(19,4);
DECLARE v_hstat VARCHAR(1);
DECLARE v_a BIGINT;
DECLARE v_b BIGINT;
START TRANSACTION;
-- 0) txn 조회
SELECT txn_id, src_account_id, dst_account_id, amount
INTO v_txn_id, v_src, v_dst, v_amt
FROM transactions
WHERE idempotency_key = p_idempotency_key
FOR UPDATE;
-- 1) holds 상태 확인 (멱등 제어)
SELECT status INTO v_hstat
FROM holds
WHERE idempotency_key = p_idempotency_key
FOR UPDATE;
IF v_hstat = '2' THEN -- 2: 확정정
-- 이미 확정 완료
COMMIT;
LEAVE proc;
SET p_status = '2';
SET p_result = 'ALREADY_CONFIRMED';
END IF;
-- 2) 잠금 순서 고정(교착 회피)
SET v_a = LEAST(v_src, v_dst);-- LEAST로 더 작은 값
SET v_b = GREATEST(v_src, v_dst);-- GREATEST로 더 큰 값
-- FOR UPDATE로 해당 ROW LOCK
SELECT account_id FROM accounts WHERE account_id IN (v_a, v_b) FOR UPDATE;
-- 3) 출금 확정: hold_amount↓, balance↓
UPDATE accounts
SET hold_amount = hold_amount - v_amt,
balance = balance - v_amt
WHERE account_id = v_src;
-- 4) 입금 확정: balance↑
UPDATE accounts
SET balance = balance + v_amt
WHERE account_id = v_dst;
-- 5) 분개(불변 로그) 2줄
INSERT INTO ledger_entries(txn_id, account_id, amount)
VALUES (v_txn_id, v_src, -v_amt);
INSERT INTO ledger_entries(txn_id, account_id, amount)
VALUES (v_txn_id, v_dst, v_amt);
-- 6) 상태 전이
UPDATE holds SET status='2' WHERE idempotency_key = p_idempotency_key;
UPDATE transactions SET status='2' WHERE txn_id = v_txn_id;
SET p_status = '2';
SET p_result = 'OK';
COMMIT;
END이 프로시저를 기준으로 Oracle, PostgreSQL 로 변환을하여 적용했다.
https://github.com/minu0897/MDBS/tree/main/DB
MONGO는 Application-Level(Flask,BE)에서 하나의 함수로 처리를 진행할 예정이다.
DB관련 공부를 합니다.