func TestTransferTx(t *testing.T) {
// check the results
existed := make(map[int]bool)
// Create two accounts.
account1 := createRandomAccount(t)
account2 := createRandomAccount(t)
fmt.Println(">> before:", account1.Balance, account2.Balance)
for i := 0; i < n; i++ {
...
// check account
fromAccount := result.FromAccount
require.NotEmpty(t, fromAccount)
require.Equal(t, account1.ID, fromAccount.ID)
toAccount := result.ToAccount
require.NotEmpty(t, toAccount)
require.Equal(t, account2.ID, toAccount.ID)
// check account's balances
// diff1 is the difference between the account's balance before the transfer and after the transfer
// diff2 is the difference between the entry's amount and the transfer's amount
fmt.Println(">> transfer", fromAccount.Balance, toAccount.Balance)
diff1 := account1.Balance - fromAccount.Balance
diff2 := toAccount.Balance - account2.Balance
require.Equal(t, diff1, diff2)
require.True(t, diff1 >= 0)
// check if the amount is a multiple of n
// 1 * amount, 2 * amount, 3 * amount, ...
require.True(t, diff1%amount == 0)
k := int(diff1 / amount)
require.True(t, k >= 1 && k <= n)
// check if the transfer has been processed
// if the transfer has been processed, the map will contain the key k
require.NotContains(t, existed, k) //
existed[k] = true
}
// check the final updated balance
updatedAccount1, err := store.GetAccount(context.Background(), account1.ID)
require.NoError(t, err)
updatedAccount2, err := store.GetAccount(context.Background(), account2.ID)
require.NoError(t, err)
fmt.Println(">> after:", updatedAccount1.Balance, updatedAccount2.Balance)
require.Equal(t, account1.Balance-int64(n)*amount, updatedAccount1.Balance)
require.Equal(t, account2.Balance+int64(n)*amount, updatedAccount2.Balance)
}
TDD有什麼優缺點? 合適的情境為何? 請舉例
優點
:
缺點
:
合適的情境
:
舉例
:
假設你正在開發一個銀行系統,該系統需要支持轉賬、存款和取款等功能。由於這是關於金錢的重要操作,所以必須確保每一個功能都是正確的。在這種情境下,使用TDD開發可以先定義好測試案例(例如轉賬金額必須正確,不得出現负数等),然後再進行功能開發,這樣可以保障每一步開發都是基於清晰和正確的需求。
在For loop中已使用k := int(diff1 / amount)
來確保每次的交易都被正確執行了,為何最後還需要透過require.Equal(t, account1.Balance-int64(n)*amount, updatedAccount1.Balance)
來進行二次驗證呢?
最後的那兩行驗證:
require.Equal(t, account1.Balance-int64(n)*amount, updatedAccount1.Balance)
require.Equal(t, account2.Balance+int64(n)*amount, updatedAccount2.Balance)
是用來確保在所有**n
**筆交易完成後,兩個帳戶的終極餘額是正確的。這個檢查更為全面,因為它考慮到了所有交易的累計效果。
舉例來說,即使每筆交易都被正確地執行,但如果其中有一筆交易被執行了兩次,或者遺漏了一筆交易,那麼最終的餘額將不會正確。透過這種驗證,可以確保每筆交易都只被正確地執行一次,並且沒有遺漏。
總結:
**for
**迴圈內部的檢查可以保證每筆交易的正確性
,但最後的整體餘額檢查則確保了所有交易的完整性
和一致性
。
store.go
func (store *Store) TransferTx(ctx context.Context, arg TransferTxParams) (TransferTxResult, error) {
var result TransferTxResult
err := store.execTx(ctx, func(q *Queries) error {
var err error
...
// TODO: Before implement update accounts' balance we have to solve db lock problem.
// move money out of account1
account1, err := q.GetAccount(ctx, arg.FromAccountID)
if err != nil {
return err
}
result.FromAccount, err = q.UpdateAccount(ctx, UpdateAccountParams{
ID: arg.FromAccountID,
Balance: account1.Balance - arg.Amount,
})
if err != nil {
return err
}
// move money into account2
account2, err := q.GetAccount(ctx, arg.ToAccountID)
if err != nil {
return err
}
result.ToAccount, err = q.UpdateAccount(ctx, UpdateAccountParams{
ID: arg.ToAccountID,
Balance: account2.Balance + arg.Amount,
})
if err != nil {
return err
}
return nil
})
return result, err
}
// Get account -> update its balance
// This is incorrectly without a proper locking mechanism
// make test:
// >> before transfer 236 632
// >> transfer 226 642
// >> transfer 226 652
// store_test.go:93:
// Error Trace: /Users/avery_yang/Workplace/go/src/simpleBank/db/sqlc/store_test.go:93
// Error: Not equal:
// expected: 10
// actual : 20
// Test: TestTransferTX
只是一個普通的SELECT操作,所以它不會阻止其他交易讀取相同的Account記錄。
因此,2個concurrent的交易可以獲得account 1的相同值,原始餘額為236
。這就解釋了為什麼它們兩者在執行後都有更新的餘額為226
。
-- name: GetAccount :one
SELECT *
FROM accounts
WHERE id = $1
LIMIT 1;
Query without lock:
blocked!!
READ COMMITTED
**的隔離級別。在此隔離級別下,一個交易不會看到其他並行交易所做的修改,除非那些交易已經提交了。但是,這不會阻止其他交易讀取相同的行。Terminal 1
docker exec -it postgres psql -U root simple_bank
simple_bank=# BEGIN;
BEGIN
simple_bank=*# SELECT * FROM accounts WHERE id=26;
id | owner | balance | currency | created_at
----+--------+---------+----------+-------------------------------
26 | dioctt | 408 | USD | 2023-08-14 07:34:24.675396+00
(1 row)
simple_bank=*# ROLLBACK;
ROLLBACK
Terminal 2
docker exec -it postgres psql -U root simple_bank
simple_bank=# BEGIN;
BEGIN
simple_bank=*# SELECT * FROM accounts WHERE id=26;
id | owner | balance | currency | created_at
----+--------+---------+----------+-------------------------------
26 | dioctt | 408 | USD | 2023-08-14 07:34:24.675396+00
(1 row)
simple_bank=*# ROLLBACK;
ROLLBACK
Query with lock
SELECT FOR UPDATE
**語句。lock
,直到原始交易完成(提交或回滾)。Terminal 1
simple_bank=# BEGIN;
BEGIN
simple_bank=*# SELECT * FROM accounts WHERE id=26 FOR UPDATE;
id | owner | balance | currency | created_at
----+--------+---------+----------+-------------------------------
26 | dioctt | 408 | USD | 2023-08-14 07:34:24.675396+00
(1 row)
simple_bank=*#
COMMIT
or ROLLBACK
.Terminal 2
simple_bank=# BEGIN;
BEGIN
simple_bank=*# SELECT * FROM accounts WHERE id=26 FOR UPDATE;
修正account.sql
-- name: GetAccountForUpdate :one
SELECT * FROM accounts
WHERE id = $1 LIMIT 1
FOR UPDATE;
make sqlc
修正GetAccount
→ GetAccountForUpdate
account1, err := q.GetAccountForUpdate(ctx, arg.FromAccountID)
if err != nil {
return err
}
result.FromAccount, err = q.UpdateAccount(ctx, UpdateAccountParams{
ID: arg.FromAccountID,
Balance: account1.Balance - arg.Amount,
})
account2, err := q.GetAccountForUpdate(ctx, arg.ToAccountID)
if err != nil {
return err
}
result.ToAccount, err = q.UpdateAccount(ctx, UpdateAccountParams{
ID: arg.ToAccountID,
Balance: account2.Balance + arg.Amount,
})
make test
--- PASS: TestListEntries (0.02s)
=== RUN TestTransferTX
>> before transfer 333 595
>> transfer 323 605
store_test.go:41:
Error Trace: /Users/avery_yang/Workplace/go/src/simpleBank/db/sqlc/store_test.go:41
Error: Received unexpected error:
pq: deadlock detected
Test: TestTransferTX
Modify to print transaction name
這裡使用context.WithValue
透過key:value的方式來存儲在context中。
store_test.go
func TestTransferTx(t *testing.T) {
...
for i := 0; i < n; i++ {
txName := fmt.Sprintf("tx %d", i+1)
// 匿名函數的 goroutine
go func() {
ctx := context.WithValue(context.Background(), txKey, txName)
result, err := store.TransferTx(ctx, TransferTxParams{
FromAccountID: account1.ID,
ToAccountID: account2.ID,
Amount: amount,
})
errs <- err
results <- result
}()
}
...
}
這裡透過txKey
取回存儲在**context
**中特定的數據(txName
)
store.go
var txKey = struct{}{}
func (store *Store) TransferTx(ctx context.Context, arg TransferTxParams) (TransferTxResult, error) {
err := store.execTx(ctx, func(q *Queries) error {
...
txName := ctx.Value(txKey)
fmt.Println(txName, "create transfer")
...
fmt.Println(txName, "create entry 1")
result.FromEntry, err = q.CreateEntry(ctx, CreateEntryParams{...})
fmt.Println(txName, "create entry 2")
result.ToEntry, err = q.CreateEntry(ctx, CreateEntryParams{...})
fmt.Println(txName, "get account 1")
account1, err := q.GetAccountForUpdate(ctx, arg.FromAccountID)
fmt.Println(txName, "update account 1")
result.FromAccount, err = q.UpdateAccount(ctx, UpdateAccountParams{...})
fmt.Println(txName, "get account 2")
account2, err := q.GetAccountForUpdate(ctx, arg.ToAccountID)
fmt.Println(txName, "update account 2")
result.ToAccount, err = q.UpdateAccount(ctx, UpdateAccountParams{...})
}
}
make test
>> before transfer 23 341
tx 2 create transfer
tx 2 create entry 1
tx 2 create entry 2
tx 1 create transfer
tx 2 get account 1
tx 1 create entry 1
tx 1 create entry 2
tx 1 get account 1
tx 1 update account 1
store_test.go:46:
Error Trace: /Users/avery_yang/Workplace/go/src/simpleBank/db/sqlc/store_test.go:46
Error: Received unexpected error:
pq: deadlock detected
Test: TestTransferTX
--- FAIL: TestTransferTX (1.02s)
=== RUN TestCreateTransfer
create transfer
and create entry 1 and 2
.create transfer
operation.get account 1
.create entry 1
, create entry 2
, get account 1
, and update account 1
.psql
consoleBEGIN;
SELECT * FROM accounts WHERE id = 1;
INSERT INTO transfers (from_account_id, to_account_id, amount) VALUES (1, 2, 10) RETURNING *;
INSERT INTO entries (account_id, amount) VALUES (1, -10) RETURNING *;
INSERT INTO entries (account_id, amount) VALUES (2, 10) RETURNING *;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = 90 WHERE id = 1 RETURNING *;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
UPDATE accounts SET balance = 110 WHERE id = 2 RETURNING *;
ROLLBACK;
SELECT FROM accounts FOR UPDATE
」,而導致阻塞的語句是「INSERT INTO transfers
」SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;
再來根據Postgres Wiki的內容,將所有當前活動的交易和它們所持有的lock的相關資訊顯示出來:
SELECT
a.application_name,
l.relation::regclass,
l.transactionid,
l.mode,
l.locktype,
l.GRANTED,
a.usename,
a.query,
a.pid
FROM
pg_stat_activity a
JOIN pg_locks l ON
l.pid = a.pid
WHERE
a.application_name = 'psql'
ORDER BY
a.pid;
現在我們可以看到,只有一個鎖尚未被授予。它來自於進程ID 3053的**SELECT FROM accounts
**查詢:
transactionid
類型的ShareLock
,其中交易ID為2442。而這個交易ID鎖正在被另一個進程ID 3047以INSERT INTO transfers
**查詢獨占持有為什麼當對**accounts
表的SELECT
查詢時,為何它會受到另一個正在進行INSERT INTO transfers
**操作的交易的鎖的影響或限制?
在查看database
schema時,**transfers
表和accounts
**表之間存在foreign key:
ALTER TABLE "entries" ADD FOREIGN KEY ("account_id") REFERENCES "accounts" ("id");
ALTER TABLE "transfers" ADD FOREIGN KEY ("from_account_id") REFERENCES "accounts" ("id");
ALTER TABLE "transfers" ADD FOREIGN KEY ("to_account_id") REFERENCES "accounts" ("id");
當進行**INSERT INTO transfers
操作時,資料庫可能需要檢查accounts
表以確保參考完整性。為了進行此檢查,它可能會鎖定一些accounts
表的資源。同時,SELECT
查詢試圖鎖定accounts
**表的相同資源以進行更新,
因此,兩個交易都在等待另一個交易釋放鎖,從而導致Dead Lock
。