One of the hardest thing when working with
database transaction is locking and handling deadlock. From my experience, the best way to deal with
deadlock is to avoid it. By that I mean we should fine-tune our queries
in the transaction so that deadlock won’t have a chance to
occur, or at least minimize its chance of occurence. And that’s exactly what I’m gonna show
you in this video. Alright, let’s dive in! This is the money transfer transaction code
that we’ve implemented in the previous lecture. Basically we’ve fixed the deadlock issue
caused by the foreign key constraints. However, if we look at the code carefully, we can see a potential deadlock scenario. Here, in this part of the transaction, we’re updating the balance of the from-account
and the to-account. And we know that they both require an exclusive
lock to perform the operation. So if there are 2 concurrent transactions
involving the same pair of accounts, there might be a potential deadlock. But we already have a test that runs 5 concurrent
transfer transactions with the same pair of accounts, but deadlock doesn’t occur, right? That’s correct! However, the transactions in our existing
test all do the same thing: transfer money from account 1 to account 2. What if some of them transfer money from account
2 to account 1? To illustrate how deadlock might occur in
this scenario, I have prepared 2 transactions in TablePlus: The 1st transaction will transfer 10 dollars
from account 1 to account 2 By first subtracting 10 from the the balance
of account 1 And then adding 10 to the balance of account
2. The 2nd transaction will do the reverse work: transfer 10 dollars from account 2 to account 1. First it subtracts 10 from the balance of
account 2. Then it adds 10 to the balance of account 1. Now let’s open the terminal to run these
transactions in 2 parallel psql console. First, I will start the first psql console And BEGIN the 1st transaction. I’m gonna run its 1st query to subtract
10 from account 1’s balance. As you can see, the account is updated instantly. OK, now let’s open another tab Start a new psql console. And BEGIN the 2nd transaction. Now let’s run its 1st query to subtract
10 from account 2’s balance. This query also returns immediately. Now back to the 1st transaction and run its
2nd query to update account 2’s balance. This time, the query is blocked because the 2nd transaction is also updating
this same account, If we go back to TablePlus and run this query
to list all the locks, we can see that this update account 2 query of transaction 1 is trying to acquire a ShareLock on transaction
ID 911, but it is not granted yet because transaction 2 is already holding an
ExclusiveLock on the same transaction ID. Therefore, transaction 1 must wait for transaction
2 to finish before continue. Now if we continue running the 2nd query of
transaction 2 to update account 1’s balance, We will get a deadlock, because this account 1 is being updated by
transaction 1, thus transaction 2 also needs to wait for
transaction 1 to finish before getting the result of this query. Deadlock occurs because these 2 concurrent
transactions both need to wait for the other. OK now let’s rollback these 2 transactions Then go back to our simple bank project to
replicate this scenario in a test. It’s gonna be very similar to the test that
we’ve written in the last lecture, So I will just duplicate this TestTransferTx
function, Change its name to TestTransferTxDeadlock Then here, let’s say we’re gonna run 10
concurrent transactions. The idea is to have 5 transactions that send
money from account 1 to account 2, and another 5 transactions that send money
in reverse direction, from account 2 to account 1. In this scenario, we only need to check for
deadlock error, We don’t need to care about the result because it has already been checked in the
other test. So I will remove this results channel. Now inside this for loop, Let’s define 2 new variables: fromAccountID will be account1.ID and toAccountID will be account2.ID But since we want half of the transaction
to send money from account 2 to account 1, I will check if the counter i is an odd number,
or i modulo 2 equals to 1, then fromAccountID should be account2.ID and toAccountID should be account1.ID instead. Alright, now inside the go routine, we should change this field to fromAccountID and this field to toAccountID. Then remove this statement because we don’t
care about the result anymore. This result variable is no longer needed,
so let’s replace it with a blank identifier. OK, now the check errors part Let’s delete this existed map. and everything inside the for loop, except the error checking statements. We also want to check the final updated balance
of the 2 accounts. In this case, there are 10 transactions that move the same amount of money between
account 1 and account 2. But because of this condition, 5 of them will move money from account 1 to
account 2, and the other 5 will move money
from account 2 back to account 1. Therefore, we expect that in the end, the balance of both account 1 and account 2 should be the same as they were before the
transactions. So here, updatedAccount1.Balance should equal
to account1.Balance. And similar for account 2. OK let’s run this test! We’ve got a deadlock error as expected. Now let’s learn how to fix it! As you’ve already seen in the example that
we ran in psql console, the reason deadlock occurs is because of the
different order in which 2 concurrent transactions update
the accounts’ balance. where transaction 1
update account 1 before account 2, while the other transaction
update account 2 before account 1. So this gives us an idea of how deadlock can
be avoided by making both transactions update the accounts
balance in the same order. Let’s say in this transaction 2, we just move this update account 1 query up, and keep everything else the same. So now both transaction 1 and transaction 2
will always update account 1 before account 2. Let’s try to run them in the psql console
to see what will happen. First, begin transaction 1. Run its 1st query to update account 1. Then switch to the other console and begin
transaction 2. Also run its 1st query to update account 1. Now unlike before, this time the query is
blocked right away, because transaction 1 is already holding an
exclusive lock to update the same account 1. So let’s go back to transaction 1 and run
its 2nd query to update account 2. The result is returned immediately. And transaction 2 is still blocked. Now let’s COMMIT this transaction 1 to release
the lock. Then go to transaction 2. We can see that it is unblocked instantly, and the balance has been updated to the new value. We can go ahead and run the 2nd query to update
account 2. then COMMIT transaction 2. All successful, and no deadlock occurs. Alright, so now we understand that the best defense against deadlocks is to avoid
them by making sure that our application always acquire locks in a
consistent order. For example, in our case, we can easily change our code so that it always
updates the account with smaller ID first. So here we check if arg.FromAccountID is less
than arg.ToAccountID then the from-account should be updated before
the to-account. Else, the to-account should be updated before
the from-account. Let’s copy this block of code that
updates to-account, and paste it here. Then copy this block of code that updates
from-account and paste it below the previous one. OK, now after this change, we expect that the deadlock should be gone. Let’s rerun our test! It passed! In the logs, we can see the balances are the
same before and after the transactions. Perfect! Before we finish,
let’s refactor the code a bit, because now it looks quite long
and somewhat duplicated. To do this, I’m gonna define a new addMoney()
function to add money to 2 accounts. It will takes several inputs: The context, The queries object, The ID of the 1st account, The amount of money that should be added to
that 1st account. The ID of the 2nd account, And the amount of money that should be added
to that 2nd account. This function will return 3 values: The 1st account object, and the 2nd account object after updated, and a potential error. Inside this function, we first call q.AddAcountBalance() to add
amount 1 to account1’s balance. So the ID should be accountID1, And amount should be amount1. We save the results to the output account1
and err variables. Then we check if error is not nil,
simply return. Here because we’re using named return variables, So this return with no parameters
is basically the same as if we write
return account1, account2, err This is a cool syntax feature of Go that makes
the code more concise. OK, next we do similar thing to add money
to account 2. This ID should be accountID2, This Amount should be amount2. And at the end, we just need to return without
any parameters. Now with this addMoney function in hand, we can go up here to refactor our transfer
transaction. If fromAccountID is less than toAccountID, we want to update fromAccount before toAccount, So here, we call addMoney(), Pass in the context, the query, arg.FromAccountID, -arg.Amount because money is moving out, then arg.ToAccountID, and finally arg.Amount
because money is moving in. The output of this function call should be
assigned to result.FromAccount, result.ToAccount, and err. Else, in case toAccountID is smaller, we want to make sure that toAccount is updated
before fromAccount. So let’s copy this command, But change this to result.ToAccount, and result.FromAccount. The first account ID that we pass in the addMoney
function, in this case, should be arg.ToAccount. And its amount should be arg.Amount, since
money is going in. Then the 2nd account ID should be changed
to arg.FromAccountID, and its corresponding amount must be -arg.Amount,
since money is going out. And that’s it! The refactoring is done. Let’s rerun the TestTransferTxDeadlock! It passed! Excellent! Let’s also run this normal TestTransferTx Also passed. And finally rerun the whole package test. All passed! So everything is now working properly. Deadlock is no longer a threat to our application. And that’s the end of today’s lecture. I hope it’s useful for you. Thanks a lot for watching. Happy coding and see you in the next video!
