1DELIMITER //
2
3CREATE PROCEDURE ProcessFundTransfer(
4 IN p_source_id INT,
5 IN p_dest_id INT,
6 IN p_amount DECIMAL(15, 2),
7 OUT p_transfer_id BIGINT,
8 OUT p_status_code VARCHAR(30),
9 OUT p_message VARCHAR(255)
10)
11proc_label: BEGIN
12 DECLARE v_first_id INT;
13 DECLARE v_second_id INT;
14 DECLARE v_src_bal DECIMAL(15, 2);
15 DECLARE v_src_frozen BOOLEAN;
16 DECLARE v_dst_frozen BOOLEAN;
17 DECLARE v_dst_bal DECIMAL(15, 2);
18 DECLARE v_tx_id BIGINT;
19
20 -- Exception Handler: Automatically rollback on any database error
21 DECLARE EXIT HANDLER FOR SQLEXCEPTION
22 BEGIN
23 ROLLBACK;
24 SET p_status_code = 'SYSTEM_ERROR';
25 SET p_message = 'An unexpected database error occurred. Transfer safely rolled back.';
26 END;
27
28 -- Validation 1: Prevent transferring to self
29 IF p_source_id = p_dest_id THEN
30 SET p_status_code = 'INVALID_DESTINATION';
31 SET p_message = 'Source and destination accounts must be distinct.';
32 LEAVE proc_label;
33 END IF;
34
35 -- Validation 2: Amount must be positive
36 IF p_amount <= 0.00 THEN
37 SET p_status_code = 'INVALID_AMOUNT';
38 SET p_message = 'Transfer amount must be strictly greater than zero.';
39 LEAVE proc_label;
40 END IF;
41
42 -- Deadlock Prevention: Determine locking order (Lower ID first!)
43 IF p_source_id < p_dest_id THEN
44 SET v_first_id = p_source_id;
45 SET v_second_id = p_dest_id;
46 ELSE
47 SET v_first_id = p_dest_id;
48 SET v_second_id = p_source_id;
49 END IF;
50
51 -- Begin Atomic Transaction Boundary
52 START TRANSACTION;
53
54 -- Deterministic Locking Read on Account 1
55 SELECT balance, is_frozen INTO v_src_bal, v_src_frozen
56 FROM accounts WHERE account_id = v_first_id FOR UPDATE;
57
58 -- Deterministic Locking Read on Account 2
59 SELECT balance, is_frozen INTO v_dst_bal, v_dst_frozen
60 FROM accounts WHERE account_id = v_second_id FOR UPDATE;
61
62 -- Re-assign balances to correct source/dest variables
63 IF p_source_id = v_first_id THEN
64 -- v_src_bal is already correct
65 ELSE
66 -- Swap variable values to map accurately
67 SET v_src_bal = v_dst_bal;
68 END IF;
69
70 -- Re-fetch actual source balance accurately
71 SELECT balance, is_frozen INTO v_src_bal, v_src_frozen
72 FROM accounts WHERE account_id = p_source_id;
73
74 SELECT balance, is_frozen INTO v_dst_bal, v_dst_frozen
75 FROM accounts WHERE account_id = p_dest_id;
76
77 -- Verification 3: Check frozen accounts
78 IF v_src_frozen OR v_dst_frozen THEN
79 ROLLBACK;
80 SET p_status_code = 'ACCOUNT_FROZEN';
81 SET p_message = 'One or both accounts are currently frozen from trading.';
82 LEAVE proc_label;
83 END IF;
84
85 -- Verification 4: Check Sufficient Funds
86 IF v_src_bal < p_amount THEN
87 ROLLBACK;
88 SET p_status_code = 'INSUFFICIENT_FUNDS';
89 SET p_message = 'Source account has insufficient balance to cover transfer.';
90 LEAVE proc_label;
91 END IF;
92
93 -- Step A: Record Transfer Audit Master
94 INSERT INTO transfers (transfer_uuid, source_acc_id, dest_acc_id, amount, status)
95 VALUES (UUID(), p_source_id, p_dest_id, p_amount, 'COMPLETED');
96
97 SET v_tx_id = LAST_INSERT_ID();
98 SET p_transfer_id = v_tx_id;
99
100 -- Step B: Update Account Balances
101 UPDATE accounts SET balance = balance - p_amount WHERE account_id = p_source_id;
102 UPDATE accounts SET balance = balance + p_amount WHERE account_id = p_dest_id;
103
104 -- Step C: Post Double-Entry Ledger Lines
105 -- 1. Debit Source Account
106 INSERT INTO general_ledger (transfer_id, account_id, entry_type, amount, running_balance)
107 VALUES (v_tx_id, p_source_id, 'DEBIT', p_amount, v_src_bal - p_amount);
108
109 -- 2. Credit Destination Account
110 INSERT INTO general_ledger (transfer_id, account_id, entry_type, amount, running_balance)
111 VALUES (v_tx_id, p_dest_id, 'CREDIT', p_amount, v_dst_bal + p_amount);
112
113 -- Commit Entire Atomic Transaction!
114 COMMIT;
115
116 SET p_status_code = 'SUCCESS';
117 SET p_message = 'Transfer processed and settled successfully.';
118END //
119
120DELIMITER ;