iT邦幫忙

2023 iThome 鐵人賽
DAY 10
0
自我挑戰組

Techschool Goalng Backend Master Class 的學習記錄系列 第 10 篇

[Day 10] DB transaction lock & How to handle deadlock in Golang Part 1

15th鐵人賽 deadlock sql golang transaction
312 瀏覽

  • 分享至 

  • xImage
    •  

DB Transaction Dead Lock

Test Driven Development (TDD)

  • 在實現accounts的balance前，我們先編寫出transaction的Unit Test，方便我們在開發中持續測試，這被稱測Test Driven。
  • 改進程式碼直到測試通過。
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)
}

Q & A:

  1. TDD有什麼優缺點? 合適的情境為何? 請舉例

    優點：

    1. 提高軟體質量：由於開發前就明確了測試標準，這有助於確保功能正確性，減少缺陷的數量和嚴重性。
    2. 支持重構：擁有一套完整的測試作為保護網，開發人員可以更自信地進行重構，不必擔心破壞已有功能。
    3. 即時反饋：開發者在開發的過程中就能得到反饋，可早期發現並修復錯誤。
    4. 確保需求的完整性：因為開發是基於測試的，所以更容易確保需求被完整實現。
    5. 文檔的替代：測試可以被視為一種「活」文件，說明功能如何運作。

    缺點：

    1. 初期開發速度較慢：必須先撰寫測試再開發功能，可能會使初期的開發速度降低。
    2. 需要經驗：有效的TDD需要經驗，否則可能會寫出大量低質量的測試。
    3. 增加開發成本：撰寫和維護測試會增加開發成本。
    4. 可能不適合所有專案：對於某些小型或短期專案，TDD的投入可能不值得。

    合適的情境：

    1. 大型、長期專案：對於大型或長期的專案，TDD能夠確保軟體的質量和維護性。
    2. 頻繁變動的專案：當需求和功能經常變更時，TDD可以確保這些變更不會破壞已有功能。
    3. 重構和優化：當你需要對代碼進行大規模重構或優化時，有一套完善的測試可以確保功能不會被破壞。

    舉例：

    假設你正在開發一個銀行系統，該系統需要支持轉賬、存款和取款等功能。由於這是關於金錢的重要操作，所以必須確保每一個功能都是正確的。在這種情境下，使用TDD開發可以先定義好測試案例（例如轉賬金額必須正確，不得出現负数等），然後再進行功能開發，這樣可以保障每一步開發都是基於清晰和正確的需求。

  2. 在For loop中已使用k := int(diff1 / amount)來確保每次的交易都被正確執行了，為何最後還需要透過require.Equal(t, account1.Balance-int64(n)*amount, updatedAccount1.Balance) 來進行二次驗證呢?

    1. 最後的那兩行驗證：

      require.Equal(t, account1.Balance-int64(n)*amount, updatedAccount1.Balance)
require.Equal(t, account2.Balance+int64(n)*amount, updatedAccount2.Balance)

      是用來確保在所有**n**筆交易完成後，兩個帳戶的終極餘額是正確的。這個檢查更為全面，因為它考慮到了所有交易的累計效果。

      舉例來說，即使每筆交易都被正確地執行，但如果其中有一筆交易被執行了兩次，或者遺漏了一筆交易，那麼最終的餘額將不會正確。透過這種驗證，可以確保每筆交易都只被正確地執行一次，並且沒有遺漏。

    2. 總結:
      **for**迴圈內部的檢查可以保證每筆交易的正確性，但最後的整體餘額檢查則確保了所有交易的完整性和一致性。

Update account balances [the wrong way]

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
}

Where Will You Go Wrong?

// 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

GetAccount Query:

只是一個普通的SELECT操作，所以它不會阻止其他交易讀取相同的Account記錄。

因此，2個concurrent的交易可以獲得account 1的相同值，原始餘額為236。這就解釋了為什麼它們兩者在執行後都有更新的餘額為226。

-- name: GetAccount :one
SELECT *
FROM accounts
WHERE id = $1
LIMIT 1;

  • Query without lock:

    • Normal SELECT not block other transactions，Terminal2 still returned immediately without being blocked!!
    • 這是因為預設情況下，PostgreSQL使用**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=*#
    • It is blocked and has to wait for the first transaction to 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,
		})

Debug a deadlock

  • 隨然修改了GetAccount Query with Lock 但同時也發生了deadlock: 
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
    • Transaction 2 ran its first 2 operations: create transfer and create entry 1 and 2.
    • Then transaction 1 jumped in to run its create transfer operation.
    • Transaction 2 came back and continued running  get account 1.
    • Finally the transaction 1 took turn and ran its next 4 operations: create entry 1, create entry 2, get account 1, and update account 1.
    • At this point, we got a deadlock.

Replicate deadlock in psql console

  1. 文章使用了兩個獨立的PostgreSQL終端機實例進行模擬。在第一個終端機中，它先鎖定了帳戶1，然後嘗試鎖定帳戶2。而在第二個終端機中，則先鎖定了帳戶2，然後嘗試鎖定帳戶1。
  2. 由於這兩個終端機的操作存在循環依賴，形成了死鎖。具體來說，第一個終端機持有帳戶1的鎖並等待帳戶2的鎖，而第二個終端機持有帳戶2的鎖並等待帳戶1的鎖。
BEGIN;

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;
  1. 在DeadLock 時再透過以下SQL 來查看發生的Queries為何 (Postgres Wiki page about lock monitoring)
    1. 會顯示被阻塞的語句是「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;

  1. 再來根據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;

  2. 現在我們可以看到，只有一個鎖尚未被授予。它來自於進程ID 3053的**SELECT FROM accounts**查詢:

    1. 它未被授予的原因是它試圖獲得一個**transactionid類型的ShareLock，其中交易ID為2442。而這個交易ID鎖正在被另一個進程ID 3047以INSERT INTO transfers**查詢獨占持有

    https://ithelp.ithome.com.tw/upload/images/20230925/201217468EGYGAmwxo.png

  3. 為什麼當對**accounts表的SELECT查詢時，為何它會受到另一個正在進行INSERT INTO transfers**操作的交易的鎖的影響或限制?

    1. 在查看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");

    2. 當進行**INSERT INTO transfers操作時，資料庫可能需要檢查accounts表以確保參考完整性。為了進行此檢查，它可能會鎖定一些accounts表的資源。同時，SELECT查詢試圖鎖定accounts**表的相同資源以進行更新，

    3. 因此，兩個交易都在等待另一個交易釋放鎖，從而導致Dead Lock。


上一篇
[Day 09] A clean way to implement database transaction in Golang Part 3
下一篇
[Day 11] How to avoid deadlock in DB transaction?
系列文
Techschool Goalng Backend Master Class 的學習記錄 共 31 篇
  1. 27
    [Day 27] Add users table with unique & foreign key constraints in PostgreSQL
  2. 28
    [Day 28] How to handle DB errors in Golang correctly
  3. 29
    [Day 29] How to securely store passwords?
  4. 30
    [Day 30] How to write stronger unit tests with a custom go-mock matcher
  5. 31
    [Day 31] Why PASETO is better than JWT for token-based authentication?
完整目錄
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言
iThome鐵人賽
參賽組數
1123 組
團體組數
52 組
累計文章數
23096 篇
完賽人數
656 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 2018鐵人賽 javascript 2017鐵人賽 python windows php c# windows server linux css 程式設計 react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙