[Backend #8] How to avoid deadlock in DB transaction? Queries order matters!

Aug 15, 2023

[Backend #8] How to avoid deadlock in DB transaction? Queries order matters!

One of the hardest things when working with database transactions is locking and handling deadlock. The best way to deal with deadlock is to avoid it, which means 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 occurrence. That’s exactly what I’m gonna show you in this video.
- Join us on Discord: https://bit.ly/techschooldc
- Get the course on Udemy: https://bit.ly/backendudemy
- Buy us a coffee: https://www.buymeacoffee.com/techschool
- Full series playlist: https://bit.ly/backendmaster
- Github repository: https://github.com/techschool/simplebank
- Read it on DEV: Read it on DEV: https://dev.to/techschoolguru/how-to-…

---

In this backend master class, we’re going to learn everything about how to design, develop, and deploy a complete backend system from scratch using PostgreSQL, Golang and Docker.

TECH SCHOOL - From noob to pro
/ techschoolguru
At Tech School, we believe that everyone deserves a good and free education. We create high-quality courses and tutorials in Information Technology. If you like the videos, please feel free to share and subscribe to support the channel or buy us a coffee: https://www.buymeacoffee.com/techschool

Content

0 -> One of the hardest thing when working with database transaction

3.3 -> is locking and handling deadlock.

5.96 -> From my experience, the best way to deal with deadlock is to avoid it.

10.82 -> By that I mean we should fine-tune our queries in the transaction

14.829 -> so that deadlock won’t have a chance to occur,

17.269 -> or at least minimize its chance of occurence.

20.44 -> And that’s exactly what I’m gonna show you in this video.

23.9 -> Alright, let’s dive in!

25.82 -> This is the money transfer transaction code that we’ve implemented in the previous lecture.

30.84 -> Basically we’ve fixed the deadlock issue caused by the foreign key constraints.

36.06 -> However, if we look at the code carefully,

38.9 -> we can see a potential deadlock scenario.

41.5 -> Here, in this part of the transaction,

43.66 -> we’re updating the balance of the from-account and the to-account.

47.76 -> And we know that they both require an exclusive lock to perform the operation.

53.44 -> So if there are 2 concurrent transactions involving the same pair of accounts,

58.1 -> there might be a potential deadlock.

60.6 -> But we already have a test that runs 5 concurrent transfer transactions

65.48 -> with the same pair of accounts,

67.6 -> but deadlock doesn’t occur, right?

70.1 -> That’s correct!

71.26 -> However, the transactions in our existing test all do the same thing:

75.96 -> transfer money from account 1 to account 2.

79.14 -> What if some of them transfer money from account 2 to account 1?

83.44 -> To illustrate how deadlock might occur in this scenario,

87.02 -> I have prepared 2 transactions in TablePlus:

90.799 -> The 1st transaction will transfer 10 dollars from account 1 to account 2

95.2 -> By first subtracting 10 from the the balance of account 1

98.88 -> And then adding 10 to the balance of account 2.

102.38 -> The 2nd transaction will do the reverse work:

105.3 -> transfer 10 dollars from account 2 to account 1.

108.66 -> First it subtracts 10 from the balance of account 2.

111.88 -> Then it adds 10 to the balance of account 1.

114.92 -> Now let’s open the terminal to run these transactions in 2 parallel psql console.

120.98 -> First, I will start the first psql console

123.96 -> And BEGIN the 1st transaction.

126.14 -> I’m gonna run its 1st query to subtract 10 from account 1’s balance.

130.74 -> As you can see, the account is updated instantly.

134.16 -> OK, now let’s open another tab

136.6 -> Start a new psql console.

138.88 -> And BEGIN the 2nd transaction.

141.12 -> Now let’s run its 1st query to subtract 10 from account 2’s balance.

145.52 -> This query also returns immediately.

148.84 -> Now back to the 1st transaction and run its 2nd query to update account 2’s balance.

154.38 -> This time, the query is blocked

156.8 -> because the 2nd transaction is also updating this same account,

162.36 -> If we go back to TablePlus and run this query to list all the locks,

167.9 -> we can see that

169.18 -> this update account 2 query of transaction 1

172.17 -> is trying to acquire a ShareLock on transaction ID 911,

176.64 -> but it is not granted yet

178.66 -> because transaction 2 is already holding an ExclusiveLock

182.57 -> on the same transaction ID.

184.56 -> Therefore, transaction 1 must wait for transaction 2 to finish before continue.

190.32 -> Now if we continue running the 2nd query of transaction 2

193.9 -> to update account 1’s balance,

196.86 -> We will get a deadlock,

198.44 -> because this account 1 is being updated by transaction 1,

202.22 -> thus transaction 2 also needs to wait for transaction 1 to finish

205.84 -> before getting the result of this query.

208.52 -> Deadlock occurs because these 2 concurrent transactions both need to wait for the other.

214.28 -> OK now let’s rollback these 2 transactions

219.14 -> Then go back to our simple bank project to replicate this scenario in a test.

225.2 -> It’s gonna be very similar to the test that we’ve written in the last lecture,

230.14 -> So I will just duplicate this TestTransferTx function,

235.38 -> Change its name to TestTransferTxDeadlock

241.38 -> Then here, let’s say we’re gonna run 10 concurrent transactions.

245.32 -> The idea is to have 5 transactions that send money from account 1 to account 2,

250.44 -> and another 5 transactions that send money in reverse direction,

254.42 -> from account 2 to account 1.

256.62 -> In this scenario, we only need to check for deadlock error,

260.1 -> We don’t need to care about the result

262.26 -> because it has already been checked in the other test.

265.82 -> So I will remove this results channel.

268.76 -> Now inside this for loop,

270.5 -> Let’s define 2 new variables:

273.12 -> fromAccountID will be account1.ID

276.52 -> and toAccountID will be account2.ID

280.14 -> But since we want half of the transaction to send money from account 2 to account 1,

285.26 -> I will check if the counter i is an odd number, or i modulo 2 equals to 1,

291.76 -> then fromAccountID should be account2.ID

295.58 -> and toAccountID should be account1.ID instead.

299.6 -> Alright, now inside the go routine,

302.5 -> we should change this field to fromAccountID

306.54 -> and this field to toAccountID.

310.28 -> Then remove this statement because we don’t care about the result anymore.

314.96 -> This result variable is no longer needed, so let’s replace it with a blank identifier.

321.52 -> OK, now the check errors part

324.5 -> Let’s delete this existed map.

327.86 -> and everything inside the for loop,

329.88 -> except the error checking statements.

333.24 -> We also want to check the final updated balance of the 2 accounts.

337.52 -> In this case, there are 10 transactions

339.98 -> that move the same amount of money between account 1 and account 2.

344.58 -> But because of this condition,

346.66 -> 5 of them will move money from account 1 to account 2,

350.28 -> and the other 5 will move money from account 2 back to account 1.

354.96 -> Therefore, we expect that in the end,

357.73 -> the balance of both account 1 and account 2

360.56 -> should be the same as they were before the transactions.

363.98 -> So here, updatedAccount1.Balance should equal to account1.Balance.

369.9 -> And similar for account 2.

373.76 -> OK let’s run this test!

376.6 -> We’ve got a deadlock error as expected.

379.56 -> Now let’s learn how to fix it!

381.9 -> As you’ve already seen in the example that we ran in psql console,

386.88 -> the reason deadlock occurs is because of the different order

390.86 -> in which 2 concurrent transactions update the accounts’ balance.

394.84 -> where transaction 1 update account 1 before account 2,

398.93 -> while the other transaction update account 2 before account 1.

403.36 -> So this gives us an idea of how deadlock can be avoided

407.14 -> by making both transactions update the accounts balance in the same order.

411.32 -> Let’s say in this transaction 2,

413.44 -> we just move this update account 1 query up,

416.46 -> and keep everything else the same.

418.8 -> So now both transaction 1 and transaction 2 will always update account 1 before account 2.

425.26 -> Let’s try to run them in the psql console to see what will happen.

429.66 -> First, begin transaction 1.

432.16 -> Run its 1st query to update account 1.

435.34 -> Then switch to the other console and begin transaction 2.

439.8 -> Also run its 1st query to update account 1.

443.18 -> Now unlike before,

444.9 -> this time the query is blocked right away,

447.84 -> because transaction 1 is already holding an exclusive lock to update the same account 1.

453.9 -> So let’s go back to transaction 1 and run its 2nd query to update account 2.

459.24 -> The result is returned immediately.

461.82 -> And transaction 2 is still blocked.

464.86 -> Now let’s COMMIT this transaction 1 to release the lock.

468.44 -> Then go to transaction 2.

470.32 -> We can see that it is unblocked instantly,

473.12 -> and the balance has been updated to the new value.

476.1 -> We can go ahead and run the 2nd query to update account 2.

480.06 -> then COMMIT transaction 2.

481.86 -> All successful, and no deadlock occurs.

485.04 -> Alright, so now we understand that

487.57 -> the best defense against deadlocks is to avoid them by making sure that

491.86 -> our application always acquire locks in a consistent order.

496.16 -> For example, in our case,

498.06 -> we can easily change our code so that it always updates the account with smaller ID first.

505.16 -> So here we check if arg.FromAccountID is less than arg.ToAccountID

512.08 -> then the from-account should be updated before the to-account.

516.18 -> Else, the to-account should be updated before the from-account.

520.16 -> Let’s copy this block of code that updates to-account,

523.72 -> and paste it here.

525.12 -> Then copy this block of code that updates from-account

528.48 -> and paste it below the previous one.

531.22 -> OK, now after this change,

533.72 -> we expect that the deadlock should be gone.

536.78 -> Let’s rerun our test!

540.58 -> It passed!

542.46 -> In the logs, we can see the balances are the same before and after the transactions.

548.08 -> Perfect!

550.06 -> Before we finish, let’s refactor the code a bit,

553.64 -> because now it looks quite long and somewhat duplicated.

559.92 -> To do this, I’m gonna define a new addMoney() function to add money to 2 accounts.

565.76 -> It will takes several inputs:

567.82 -> The context,

569.36 -> The queries object,

571.52 -> The ID of the 1st account,

574.3 -> The amount of money that should be added to that 1st account.

578.48 -> The ID of the 2nd account,

581.16 -> And the amount of money that should be added to that 2nd account.

585.38 -> This function will return 3 values:

588.37 -> The 1st account object,

590.5 -> and the 2nd account object after updated,

594.1 -> and a potential error.

597.64 -> Inside this function,

599.18 -> we first call q.AddAcountBalance() to add amount 1 to account1’s balance.

605.58 -> So the ID should be accountID1,

609.02 -> And amount should be amount1.

612.26 -> We save the results to the output account1 and err variables.

617.8 -> Then we check if error is not nil, simply return.

623.82 -> Here because we’re using named return variables,

627.38 -> So this return with no parameters is basically the same

631.42 -> as if we write return account1, account2, err

636.06 -> This is a cool syntax feature of Go that makes the code more concise.

643.08 -> OK, next we do similar thing to add money to account 2.

648 -> This ID should be accountID2,

650.68 -> This Amount should be amount2.

654.94 -> And at the end, we just need to return without any parameters.

660.68 -> Now with this addMoney function in hand,

663.8 -> we can go up here to refactor our transfer transaction.

667.62 -> If fromAccountID is less than toAccountID,

670.82 -> we want to update fromAccount before toAccount,

674.72 -> So here, we call addMoney(),

676.68 -> Pass in the context,

678.24 -> the query,

679.86 -> arg.FromAccountID,

682.8 -> -arg.Amount because money is moving out,

687.8 -> then arg.ToAccountID,

690.68 -> and finally arg.Amount because money is moving in.

695.73 -> The output of this function call should be assigned to

699.07 -> result.FromAccount,

701.52 -> result.ToAccount,

703.86 -> and err.

705.14 -> Else, in case toAccountID is smaller,

708.8 -> we want to make sure that toAccount is updated before fromAccount.

713.42 -> So let’s copy this command,

715.9 -> But change this to result.ToAccount,

719.26 -> and result.FromAccount.

721.56 -> The first account ID that we pass in the addMoney function,

724.96 -> in this case, should be arg.ToAccount.

728.52 -> And its amount should be arg.Amount, since money is going in.

734.16 -> Then the 2nd account ID should be changed to arg.FromAccountID,

739.42 -> and its corresponding amount must be -arg.Amount, since money is going out.

746.18 -> And that’s it!

747.44 -> The refactoring is done.

749.34 -> Let’s rerun the TestTransferTxDeadlock!

752.88 -> It passed!

754.12 -> Excellent!

756.44 -> Let’s also run this normal TestTransferTx

760.66 -> Also passed.

762.22 -> And finally rerun the whole package test.

766.38 -> All passed!

767.8 -> So everything is now working properly.

771.06 -> Deadlock is no longer a threat to our application.

774.84 -> And that’s the end of today’s lecture.

777 -> I hope it’s useful for you.

779.34 -> Thanks a lot for watching.

781.02 -> Happy coding and see you in the next video!

Source: https://www.youtube.com/watch?v=qn3-5wdOfoA