DB transaction lock & How to handle deadlock

Locking is not easy as you think!

In the last lecture, weve learned how to implement a simple money transfer transaction. However, we havent updated the accounts balance yet because its more complicated and require careful handling of concurrent transactions to avoid deadlock.

So in this lecture were gonna implement this feature to learn more about database locking and how to debug a deadlock situation.

Here's:

• Link to the full series playlist on Youtube
• And its Github repository

Test Driven Development

Today Im gonna use a different implementation approach, which is test driven development (or TDD). The idea is: we write tests first to make our current code breaks. Then we gradually improve the code until the tests pass.

OK, this is the test that we were working on in the previous video:

func TestTransferTx(t *testing.T) {    store := NewStore(testDB)    account1 := createRandomAccount(t)    account2 := createRandomAccount(t)    // run n concurrent transfer transactions    n := 5    amount := int64(10)    errs := make(chan error)    results := make(chan TransferTxResult)    for i := 0; i < n; i++ {        go func() {            result, err := store.TransferTx(context.Background(), TransferTxParams{                FromAccountID: account1.ID,                ToAccountID:   account2.ID,                Amount:        amount,            })            errs <- err            results <- result        }()    }    // check results    for i := 0; i < n; i++ {        err := <-errs        require.NoError(t, err)        result := <-results        require.NotEmpty(t, result)        // check transfer        transfer := result.Transfer        require.NotEmpty(t, transfer)        require.Equal(t, account1.ID, transfer.FromAccountID)        require.Equal(t, account2.ID, transfer.ToAccountID)        require.Equal(t, amount, transfer.Amount)        require.NotZero(t, transfer.ID)        require.NotZero(t, transfer.CreatedAt)        _, err = store.GetTransfer(context.Background(), transfer.ID)        require.NoError(t, err)        // check entries        fromEntry := result.FromEntry        require.NotEmpty(t, fromEntry)        require.Equal(t, account1.ID, fromEntry.AccountID)        require.Equal(t, -amount, fromEntry.Amount)        require.NotZero(t, fromEntry.ID)        require.NotZero(t, fromEntry.CreatedAt)        _, err = store.GetEntry(context.Background(), fromEntry.ID)        require.NoError(t, err)        toEntry := result.ToEntry        require.NotEmpty(t, toEntry)        require.Equal(t, account2.ID, toEntry.AccountID)        require.Equal(t, amount, toEntry.Amount)        require.NotZero(t, toEntry.ID)        require.NotZero(t, toEntry.CreatedAt)        _, err = store.GetEntry(context.Background(), toEntry.ID)        require.NoError(t, err)        // TODO: check accounts' balance    }}

It creates 5 go routines to execute 5 concurrent transfer transactions, where each of them will transfer the same amount of money from account 1 to account 2. Then it iterates through the list of results to check the created transfer and entry objects.

Now to finish this test, we need to check the output accounts and their balances.

Lets start with the accounts. First the fromAccount, where money is going out. We check it should not be empty. And its ID should equal to account1.ID.

Similar for the toAccount, where money is going in. The account object should not be empty. And its ID should equal to account2.ID.

func TestTransferTx(t *testing.T) {    ...    // check results    for i := 0; i < n; i++ {        ...        // check accounts        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)        // TODO: check accounts' balance    }}

Next we will check the accounts balance. We calculate the difference diff1 between the input account1.Balance and the output fromAccount.Balance. This diff1 is the amount of money thats going out of account1.

Similarly, we calculate the difference diff2 between the output toAccount.Balance and the input account2.Balance. This diff2 is the amount of money thats going in to account2.

func TestTransferTx(t *testing.T) {    ...    // check results    for i := 0; i < n; i++ {        ...        // check accounts' balance        diff1 := account1.Balance - fromAccount.Balance        diff2 := toAccount.Balance - account2.Balance        require.Equal(t, diff1, diff2)        require.True(t, diff1 > 0)        require.True(t, diff1%amount == 0) // 1 * amount, 2 * amount, 3 * amount, ..., n * amount    }}

If the transaction works correctly then diff1 and diff2 should be the same, and they should be a positive number.

Also, this difference should be divisible by the amount of money that moves in each transaction. The reason is, the balance of account 1 will be decreased by 1 times amount after the 1st transaction, then 2 times amount after the 2nd transaction, 3 times amount after the 3rd transaction, and so on and so forth.

Because of this, If we compute k = diff1 / amount, then k must be an integer between 1 and n, where n is the number of executed transactions.

func TestTransferTx(t *testing.T) {    ...    // check results    existed := make(map[int]bool)    for i := 0; i < n; i++ {        ...        // check accounts' balance        ...        k := int(diff1 / amount)        require.True(t, k >= 1 && k <= n)        require.NotContains(t, existed, k)        existed[k] = true    }}

Moreover, k must be unique for each transaction, which means k should be 1 for the 1st transaction, 2 for the second, 3 for the 3rd and so on until k equals to n.

In order to check this, we need to declare a new variable called existed of type map[int]bool. Then in the loop, check that the existed map should not contain k. Then we set existed[k] to true after that.

Eventually, after the for loop, we should check the final updated balances of the 2 accounts.

First we get the updated account 1 from the database by calling store.GetAccount() with a background context and the ID of account 1. This query should not return an error. We get the updated account 2 from the database in the same manner.

func TestTransferTx(t *testing.T) {    ...    // check results    existed := make(map[int]bool)    for i := 0; i < n; i++ {        ...    }    // 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)    require.Equal(t, account1.Balance-int64(n)*amount, updatedAccount1.Balance)    require.Equal(t, account2.Balance+int64(n)*amount, updatedAccount2.Balance)}

Now after n transactions, the balance of account 1 must decrease by n * amount. So we require the updatedAccount1.Balance to equal to that value. amount is of type int64, so we need to convert n to int64 before doing the multiplication.

We do the same for the updatedAccount2.Balance, except that its value should be increasing by n * amount istead of decreasing.

And thats it! Were done with the test. But before running it, Im gonna write some logs to see the results more clearly.

First, lets print out the balance of the accounts before the transaction. Then print out their updated balances after all the transactions are executed. I also want to see the result balances after each transaction so lets add a log in the for loop as well.

OK, this is our final test:

func TestTransferTx(t *testing.T) {    store := NewStore(testDB)    account1 := createRandomAccount(t)    account2 := createRandomAccount(t)    fmt.Println(">> before:", account1.Balance, account2.Balance)    n := 5    amount := int64(10)    errs := make(chan error)    results := make(chan TransferTxResult)    // run n concurrent transfer transaction    for i := 0; i < n; i++ {        go func() {            result, err := store.TransferTx(context.Background(), TransferTxParams{                FromAccountID: account1.ID,                ToAccountID:   account2.ID,                Amount:        amount,            })            errs <- err            results <- result        }()    }    // check results    existed := make(map[int]bool)    for i := 0; i < n; i++ {        err := <-errs        require.NoError(t, err)        result := <-results        require.NotEmpty(t, result)        // check transfer        transfer := result.Transfer        require.NotEmpty(t, transfer)        require.Equal(t, account1.ID, transfer.FromAccountID)        require.Equal(t, account2.ID, transfer.ToAccountID)        require.Equal(t, amount, transfer.Amount)        require.NotZero(t, transfer.ID)        require.NotZero(t, transfer.CreatedAt)        _, err = store.GetTransfer(context.Background(), transfer.ID)        require.NoError(t, err)        // check entries        fromEntry := result.FromEntry        require.NotEmpty(t, fromEntry)        require.Equal(t, account1.ID, fromEntry.AccountID)        require.Equal(t, -amount, fromEntry.Amount)        require.NotZero(t, fromEntry.ID)        require.NotZero(t, fromEntry.CreatedAt)        _, err = store.GetEntry(context.Background(), fromEntry.ID)        require.NoError(t, err)        toEntry := result.ToEntry        require.NotEmpty(t, toEntry)        require.Equal(t, account2.ID, toEntry.AccountID)        require.Equal(t, amount, toEntry.Amount)        require.NotZero(t, toEntry.ID)        require.NotZero(t, toEntry.CreatedAt)        _, err = store.GetEntry(context.Background(), toEntry.ID)        require.NoError(t, err)        // check accounts        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 balances        fmt.Println(">> tx:", fromAccount.Balance, toAccount.Balance)        diff1 := account1.Balance - fromAccount.Balance        diff2 := toAccount.Balance - account2.Balance        require.Equal(t, diff1, diff2)        require.True(t, diff1 > 0)        require.True(t, diff1%amount == 0) // 1 * amount, 2 * amount, 3 * amount, ..., n * amount        k := int(diff1 / amount)        require.True(t, k >= 1 && k <= n)        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)}

Let's run it!

It fails at line 83, where we expect the fromAccount to be not empty. But of course it is empty at the moment, because we havent implemented the feature yet.

So lets go back to the store.go file to implement it!

Update account balances [the wrong way]

One easy and intuitive way to change an accounts balance is to first get that account from the database, then add or subtract some amount of money from its balance, and update it back to the database.

However, this is often done incorrectly without a proper locking mechanism. I'm gonna show you how!

First we call q.GetAccount() to get the fromAccount record and assign it to account1 variable. If err is not nil, we return it.

func (store *Store) TransferTx(ctx context.Context, arg TransferTxParams) (TransferTxResult, error) {    var result TransferTxResult    err := store.execTx(ctx, func(q *Queries) error {        ...        // 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        }    }    return result, err}

Else, we call q.UpdateAccount() to update this accounts balance. The ID should be arg.FromAccountID, and the balance will be changed to account1.Balance - arg.Amount because money is going out of this acount.

The updated account record will be saved to result.FromAccount. And if we get an error, just return it.

After this, we have moved money out of the fromAccount. Now we can do similar thing to move those money into the toAccount.

func (store *Store) TransferTx(ctx context.Context, arg TransferTxParams) (TransferTxResult, error) {    var result TransferTxResult    err := store.execTx(ctx, func(q *Queries) error {        ...        // move money out of account1        ...        // 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 result, err}

Here, the account ID should be arg.ToAccountID. The result will be stored in result.ToAccount. And the new balance should be account2.Balance + arg.Amount because money is going into this account.

OK so the implementation is done. However, Im telling you its incorrect. Lets rerun our test to see how it goes!

The test still fails. But this time the error is on line 94, where we compare the amount of money that goes out of account 1 with those that goes into account 2.

In the log, we can see that the first transaction is correct. The balance of account 1 decreases by 10, from 380 to 370. And the balance of account 2 increases by the same amount, from 390 to 400.

But it doesnt work correctly in the second transaction. The balance of account 2 increases by 10 more, to 410. While the balance of account 1 stays the same, at 370.

To understand why, lets look at the GetAccount query:

-- name: GetAccount :oneSELECT * FROM accountsWHERE id = $1 LIMIT 1;

Its just a normal SELECT, so it doesnt block other transactions from reading the same Account record.

Therefore, 2 concurrent transactions can get the same value of the account 1, with original balance of 380. Thus it explains why both of them have the updated balance of 370 after execution.

Query without lock

To demonstrate this scenario, lets start the psql console in 2 different terminal tabs and run 2 parallel transactions.

In the 1st transaction, lets run a normal SELECT query to get the account record with ID = 1.

SELECT * FROM accounts WHERE id = 1;

This account has balance of 748 USD.

Now Im gonna run this query in the other transaction.

As you can see, the same account record is returned immediately without being blocked. This is not what we want. So lets rollback both transactions and learn how to fix it.

Query with lock

I will start 2 new transactions. But this time, we will add FOR UPDATE clause at the end of the SELECT statement.

SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

Now the first transaction still gets the record immediately. But when we run this query on the second transaction:

It is blocked and has to wait for the first transaction to COMMIT or ROLLBACK.

Lets go back to that transaction and update the account balance to 500:

UPDATE accounts SET balance = 500 WHERE id = 1;

After this update, the second transaction is still blocked. However, as soon as we COMMIT the first transaction:

We can see that the second transaction is unblocked right away, and it gets the newly updated account with balance of 500 EUR. Thats exactly what we want to achieve!

Update account balance with lock

Lets go back to the account.sql file, and add a new query to get account for update:

-- name: GetAccountForUpdate :oneSELECT * FROM accountsWHERE id = $1 LIMIT 1FOR UPDATE;

Then we open the terminal and run make sqlc to regenerate the code. Now in the account.sql.go file, a new GetAccountForUpdate() function is generated.

const getAccountForUpdate = `-- name: GetAccountForUpdate :oneSELECT id, owner, balance, currency, created_at FROM accountsWHERE id = $1 LIMIT 1FOR UPDATE`func (q *Queries) GetAccountForUpdate(ctx context.Context, id int64) (Account, error) {    row := q.db.QueryRowContext(ctx, getAccountForUpdate, id)    var i Account    err := row.Scan(        &i.ID,        &i.Owner,        &i.Balance,        &i.Currency,        &i.CreatedAt,    )    return i, err}

We can use it in our money transfer transaction. Here, to get the first account, we call q.GetAccountForUpdate() instead of q.GetAccount(). We do the same thing to get the second account.

func (store *Store) TransferTx(ctx context.Context, arg TransferTxParams) (TransferTxResult, error) {    var result TransferTxResult    err := store.execTx(ctx, func(q *Queries) error {        ...        // move money out of account1        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,        })        if err != nil {            return err        }        // move money into account2        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,        })        if err != nil {            return err        }    }    return result, err}

Alright, now we expect this to work. Lets rerun our test.

Unfortunately, it still fails. This time the error is deadlock detected. So what can we do?

Dont worry! Im gonna show you how to debug this deadlock situation.

Debug a deadlock

In order to figure out why deadlock occured, we need to print out some logs to see which transaction is calling which query and in which order.

For that, we have to assign a name for each transaction and pass it into the TransferTx() function via the context argument.

Now inside this for loop of the test, Im gonna create a txName variable to store the name of the transaction. We use the fmt.Sprintf() function and the counter i to create different names: tx 1, tx 2, tx 3, and so on.

Then inside the go routine, instead of passing in the background context, we will pass in a new context with the transaction name.

func TestTransferTx(t *testing.T) {    ...    // run n concurrent transfer transaction    for i := 0; i < n; i++ {        txName := fmt.Sprintf("tx %d", i+1)        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        }()    }    // check results    ...}

To add the transaction name to the context, we call context.WithValue(), pass in the background context as its parent, and a pair of key value, where value is the transaction name.

In the documentation, it says the context key should not be of type string or any built-in type to avoid collisions between packages. Normally we should define a variable of type struct{} for the context key.

So Im gonna add a new txKey variable in the store.go file, because later we will have to use this key to get the transaction name from the input context of the TransferTx() function.

var txKey = struct{}{}func (store *Store) TransferTx(ctx context.Context, arg TransferTxParams) (TransferTxResult, error) {    ...}...

Here, the 2nd bracket in struct{}{} means that were creating a new empty object of type struct{}.

Now in the TransferTx() function, the context will hold the transaction name. We can get it back by calling ctx.Value() to get the value of the txKey from the context.

Now we have the transaction name, we can write some logs with it. Lets print out this transaction name and the first operation: create transfer. Then do the same for the rest of the operations:

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        txName := ctx.Value(txKey)        fmt.Println(txName, "create transfer")        result.Transfer, err = q.CreateTransfer(ctx, CreateTransferParams{            FromAccountID: arg.FromAccountID,            ToAccountID:   arg.ToAccountID,            Amount:        arg.Amount,        })        if err != nil {            return err        }        fmt.Println(txName, "create entry 1")        result.FromEntry, err = q.CreateEntry(ctx, CreateEntryParams{            AccountID: arg.FromAccountID,            Amount:    -arg.Amount,        })        if err != nil {            return err        }        fmt.Println(txName, "create entry 2")        result.ToEntry, err = q.CreateEntry(ctx, CreateEntryParams{            AccountID: arg.ToAccountID,            Amount:    arg.Amount,        })        if err != nil {            return err        }        // move money out of account1        fmt.Println(txName, "get account 1")        account1, err := q.GetAccountForUpdate(ctx, arg.FromAccountID)        if err != nil {            return err        }        fmt.Println(txName, "update account 1")        result.FromAccount, err = q.UpdateAccount(ctx, UpdateAccountParams{            ID:      arg.FromAccountID,            Balance: account1.Balance - arg.Amount,        })        if err != nil {            return err        }        // move money into account2        fmt.Println(txName, "get account 2")        account2, err := q.GetAccountForUpdate(ctx, arg.ToAccountID)        if err != nil {            return err        }        fmt.Println(txName, "update account 2")        result.ToAccount, err = q.UpdateAccount(ctx, UpdateAccountParams{            ID:      arg.ToAccountID,            Balance: account2.Balance + arg.Amount,        })        if err != nil {            return err        }    })    return result, err}

Alright, now the logs are added, we can rerun the test to see how it goes.

But to make it easier to debug, we should not run too many concurrent transactions. So Im gonna change this n to 2 instead of 5.

func TestTransferTx(t *testing.T) {    ...    n := 2    amount := int64(10)    errs := make(chan error)    results := make(chan TransferTxResult)    // run n concurrent transfer transaction    ...}

Then lets run the test!

And voila, we still got the deadlock. But this time, we have a detailed logs of what happened.

As you can see here:

• Transaction 2 ran its first 2 operations: create transfer and create entry 1.
• Then transaction 1 jumped in to run its create transfer operation.
• Transaction 2 came back and continued running its next 2 operations: create entry 2 and 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.

So now we know exactly what happened. What we have to do is to find out the reason why it happened.

Replicate deadlock in psql console

Here I have opened the simple_bank database in TablePlus. At the moment, it has 2 accounts with the same original balance of 100 USD.

I also prepared the money transfer transaction with the list of SQL queries that should be run exactly as we implemented in our Golang code:

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;

• The transaction starts with the BEGIN statement.
• First we INSERT a new transfer record from account 1 to account 2 with amount of 10.
• Then we INSERT a new entry record for account 1 with amount of -10.
• And INSERT another entry record for account 2 with amount of +10.
• Next we SELECT account 1 for update.
• And we UPDATE its balance to 100-10, which is 90 USD.
• Similarly, we SELECT account 2 for update.
• And we UPDATE its balance to 100+10, which equals to 110 USD.
• Finally we do a ROLLBACK when a deadlock occurs.

Now just like what we did before, Im gonna open the terminal and run 2 psql console in order to execute 2 transactions in parallel.

Lets start the first transaction with BEGIN. Then open another tab and access the psql console. Start the second transaction with BEGIN.

Now, we should follow the steps in the logs. First, transaction 2 should run its 2 first queries to create the transfer and entry 1 records:

Inserted successfully! Now we have to move to transaction 1 and run the 1st query to create transfer record.

Now back to transaction 2 and run its 3rd query to create entry 2 and the 4th query to get account 1 for update.

Now we see that this query is blocked. It is waiting for the transaction 2 to commit or rollback before continue.

It sounds strange because transaction 2 only creates a record in transfers table while were getting a record from accounts table. Why a INSERT into 1 table can block a SELECT from other table?

To confirm this, lets open this Postgres Wiki page about lock monitoring.

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_processFROM  pg_catalog.pg_locks         blocked_locksJOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pidJOIN 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.pidJOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pidWHERE NOT blocked_locks.granted;

This long and complex query allows us to look for blocked queries and what is blocking them. So lets copy and run it in TablePlus.

As you can see, the blocked statement is SELECT FROM accounts FOR UPDATE. And the one thats blocking it is INSERT INTO transfers. So its true that queries on these 2 different tables can block each other.

Lets dig deeper to understand why the SELECT query has to wait for the INSERT query.

If we go back to the Postgres Wiki and scroll down a bit, we will see another query that will allow us to list all the locks in our database.

Im gonna modify this query a bit because I want to see more information:

SELECT    a.datname,    a.application_name,    l.relation::regclass,    l.transactionid,    l.mode,    l.locktype,    l.GRANTED,    a.usename,    a.query,    a.pidFROM    pg_stat_activity a    JOIN pg_locks l ON    l.pid = a.pidORDER BY    a.pid;

• The a.datname field will show us the database name.
• Lets add a.application_name to see which application the lock comes from.
• The l.relation regclass is actually the name of the table,
• L.transactionid is the ID of the transaction that the lock come from.
• L.mod is the mod of the lock.
• Lets also add l.lock_type to see the type of the lock.
• L.granted tells us whether the lock is granted or not.
• a.usename is the username who run the query.
• a.query is the query thats holding or trying to acquire the lock.
• The time when that query started a.query_start or its age are not very important, so Im gonna remove them.
• The last field is a.pid, which is the process ID that's running the transaction.

As you can see, were selecting from the pg_state_activity table, alias as a, and join with the pg_locks table, alias as l, on the process ID column.

Its ordering by query start time, but actually I think order by process ID is better because we have 2 different processes that are running 2 psql consoles with 2 parallel transactions. So it will be easier to see which lock belong to which transaction.

Alright, lets run it!

Here we can see some locks from TablePlus application, which are not relevant. What we care about is only the locks that came from psql consoles.

So Im gonna add a WHERE clause to get only the locks with application name equals psql.

The database name is also not important because its always simple_bank in our case. So I will remove a.datname as well.

OK lets run this query again:

SELECT    a.application_name,    l.relation::regclass,    l.transactionid,    l.mode,    l.locktype,    l.GRANTED,    a.usename,    a.query,    a.pidFROM    pg_stat_activity a    JOIN pg_locks l ON    l.pid = a.pidWHERE    a.application_name = 'psql'ORDER BY    a.pid;

Now we can see, there is only 1 lock that hasnt been granted yet. It comes from the SELECT FROM accounts query of the process ID 3053.

The reason its not granted is because it is trying to acquire a ShareLock of type transactionid, where the transaction ID is 2442. While this transaction ID lock is being held exclusively by the other process ID 3047 with the INSERT INTO transfers query.

But why a SELECT FROM accounts table needs to get a lock from other transaction that runs INSERT INTO transfers table?

Well, if we look at the database schema, we can see that the only connection between accounts and transfers table is the foreign key constraint:

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");

The from_account_id and to_account_id columns of transfers table are referencing the id column of accounts table. So any UPDATE on the account ID will affect this foreign key constraint.

Thats why when we select an account for update, it needs to acquire a lock to prevent conflicts and ensure the consistency of the data.

Having said that, now if we continue running the rest of the queries on transaction 1 to create entry 1, create entry 2, and select account 1 for update:

We will get a deadlock because this query also has to wait for a lock from transaction 2, while transaction 2 is also waiting for a lock from this transaction 1.

And that clearly explains how the deadlock happens. But how to fix it?

Fix deadlock [the bad way]

As we know, the deadlock is caused by foreign key constraints, so one simple way to avoid it is to remove those constraints.

Lets try comment out these statements in the init_schema.up.sql file:

-- 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");

Then run make migratedown in the terminal to delete the database schema. And run make migrateup to recreate the new db schema without foreign key constraints.

Alright, now if we run the test again, it will pass because the constraints are gone, so no lock is required when select accounts for update. And no lock means no deadlock.

However, this is not the best solution, because we dont want to loose our nice constraints that keep our data consistent.

So lets revert these changes, run make migratedown, then make migrateup again to have those constraints back. Now the test will fail because of deadlock again.

Lets learn a better way to fix this issue.

Fix dead lock [the better way]

As we already know, the transaction lock is only required because Postgres worries that transaction 1 will update the account ID, which would affect the foreign key constraints of transfers table.

However, if we look at the UpdateAccount query, we can see that it only change the account balance.

-- name: UpdateAccount :oneUPDATE accountsSET balance = $2WHERE id = $1RETURNING *;

The account ID will never be changed because its the primary key of accounts table.

So if we can tell Postgres that Im selecting this account for update, but its primary key wont be touched, then Postgres will not need to acquire the transaction lock, and thus no deadlock.

Fortunately, its super easy to do so. In the GetAccountForUpdate query, instead of just SELECT FOR UPDATE, we just need to say more clearly: SELECT FOR NO KEY UPDATE

-- name: GetAccountForUpdate :oneSELECT * FROM accountsWHERE id = $1 LIMIT 1FOR NO KEY UPDATE;

This will tell Postgres that we dont update the key, or ID column of accounts table.

Now lets run make sqlc in the terminal to regenerate golang code for this query.

const getAccountForUpdate = `-- name: GetAccountForUpdate :oneSELECT id, owner, balance, currency, created_at FROM accountsWHERE id = $1 LIMIT 1FOR NO KEY UPDATE`func (q *Queries) GetAccountForUpdate(ctx context.Context, id int64) (Account, error) {    row := q.db.QueryRowContext(ctx, getAccountForUpdate, id)    var i Account    err := row.Scan(        &i.ID,        &i.Owner,        &i.Balance,        &i.Currency,        &i.CreatedAt,    )    return i, err}

OK the code is updated. Lets run our test again!

It passed! Excellent! So our debugging and fixing is done.

Update account balance [the better way]

Now before we finish, Im gonna show you a much better way to implement this update account balance operation.

Currently, we have to perform 2 queries to get the account and update its balance:

func (store *Store) TransferTx(ctx context.Context, arg TransferTxParams) (TransferTxResult, error) {    var result TransferTxResult    err := store.execTx(ctx, func(q *Queries) error {        ...        // move money out of account1        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,        })        if err != nil {            return err        }        // move money into account2        ...    })    return result, err}

We can improve this by using only 1 single query to add some amount of money to the account balance directly.

For that, Im gonna add a new SQL query called AddAccountBalance to the query/account.sql file.

-- name: AddAccountBalance :oneUPDATE accountsSET balance = balance + $1WHERE id = $2RETURNING *;

Its similar to the UpdateAccount query, except that, here we set balance = balance + $2.

Lets run make sqlc to generate the code. A new function is successfully added to the Queries struct:

const addAccountBalance = `-- name: AddAccountBalance :oneUPDATE accountsSET balance = balance + $1WHERE id = $2RETURNING id, owner, balance, currency, created_at`type AddAccountBalanceParams struct {    Balance int64 `json:"balance"`    ID     int64 `json:"id"`}func (q *Queries) AddAccountBalance(ctx context.Context, arg AddAccountBalanceParams) (Account, error) {    row := q.db.QueryRowContext(ctx, addAccountBalance, arg.Balance, arg.ID)    var i Account    err := row.Scan(        &i.ID,        &i.Owner,        &i.Balance,        &i.Currency,        &i.CreatedAt,    )    return i, err}

However the balance parameter inside AddAccountBalanceParams struct looks a bit confusing, because were just adding some amount of money to the balance, not changing the account balance to this value.

So this parameters name should be Amount instead. Can we tell sqlc to do that for us?

Yes, we can! In the SQL query, instead of $2, we can say sqlc.arg(amount), and instead of $1, we should say sqlc.arg(id)

-- name: AddAccountBalance :oneUPDATE accountsSET balance = balance + sqlc.arg(amount)WHERE id = sqlc.arg(id)RETURNING *;

This amount and id will be the name of the generated parameters. Lets run make sqlc in the terminal to regenerate the code.

const addAccountBalance = `-- name: AddAccountBalance :oneUPDATE accountsSET balance = balance + $1WHERE id = $2RETURNING id, owner, balance, currency, created_at`type AddAccountBalanceParams struct {    Amount int64 `json:"amount"`    ID     int64 `json:"id"`}func (q *Queries) AddAccountBalance(ctx context.Context, arg AddAccountBalanceParams) (Account, error) {    row := q.db.QueryRowContext(ctx, addAccountBalance, arg.Amount, arg.ID)    var i Account    err := row.Scan(        &i.ID,        &i.Owner,        &i.Balance,        &i.Currency,        &i.CreatedAt,    )    return i, err}

This time, we can see the parameters name have changed to what we want. Cool!

Now come back to the store.go file, Im gonna remove the GetAccountForUpdate call, and change UpdateAccount() to AddAccountBalance():

func (store *Store) TransferTx(ctx context.Context, arg TransferTxParams) (TransferTxResult, error) {    var result TransferTxResult    err := store.execTx(ctx, func(q *Queries) error {        ...        // move money out of account1        result.FromAccount, err = q.AddAccountBalance(ctx, AddAccountBalanceParams{            ID:     arg.FromAccountID,            Amount: -arg.Amount,        })        if err != nil {            return err        }        // move money into account2        result.ToAccount, err = q.AddAccountBalance(ctx, AddAccountBalanceParams{            ID:     arg.ToAccountID,            Amount: arg.Amount,        })        if err != nil {            return err        }        return nil    })    return result, err    }

Note that the Amount to add to account1 should be -amount because money is moving out.

And were done! Lets rerun the test.

Yee! It passed! Lets run the whole package test.

All passed!

And thats it for todays lecture about locking in db transaction and how to debug a deadlock. I hope you enjoy it.

Stay tuned for the next lecture, because Im telling you the deadlock issue is not completely resolved yet. There are much more to learn about it.

In the mean time, happy coding and Ill see you very soon!