![]() We can run the 2nd query to update account 2, then COMMIT transaction 2 successfully with no deadlocks.Īlright, 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.įor example, in our case, we can easily change our code so that it always updates the account with smaller ID first. We can see that it is unblocked instantly, and the balance has been updated to the new value. So we just COMMIT this transaction 1 to release the lock. The result is returned immediately, and transaction 2 is still blocked. So let’s go back to transaction 1 and run its 2nd query to update account 2. 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. Also run its 1st query to update account 1. Then switch to the other console and begin transaction 2. Let’s try to run them in the psql console to see what will happen!įirst, begin transaction 1 and run its 1st query to update account 1. So now both transaction 1 and transaction 2 will always update account 1 before account 2. 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. Here, let’s say we’re gonna run n = 10 concurrent transactions. It’s gonna be very similar to the test that we’ve written in the last lecture, so I will just duplicate that TestTransferTx function, and change its name to TestTransferTxDeadlock. OK now let’s rollback these 2 transactions then go back to our simple bank project to replicate this scenario in a test. Deadlock occurs because these 2 concurrent transactions both need to wait for the other. 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. Now if we continue running the 2nd query of transaction 2 to update account 1’s balance: Therefore, transaction 1 must wait for transaction 2 to finish before continue. That's because transaction 2 is already holding an ExclusiveLock on the same transaction ID. 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 If we go back to TablePlus and run this query to list all the locks: ![]() This time, the query is blocked because the 2nd transaction is also updating the same account 2. Now back to the 1st transaction and run its 2nd query to update account 2’s balance. Then let’s run its 1st query to subtract 10 from account 2’s balance. Now let’s open another tab, start a new psql console, and BEGIN the 2nd transaction. Then I’m gonna run its 1st query to subtract 10 from account 1’s balance. Now let’s open the terminal to run these transactions in 2 parallel psql console.įirst, I will start the first psql console and BEGIN the 1st transaction. Then it adds 10 to the balance of account 1. First it subtracts 10 from the balance of account 2. The 2nd transaction will do the reverse work: transfer 10 dollars from account 2 to account 1. 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. Context, arg TransferTxParams ) ( TransferTxResult, error ) Įnter fullscreen mode Exit fullscreen mode 3 How to write & run database migration in Golang 4 Generate CRUD Golang code from SQL | Compare db/sql, gorm, sqlx, sqlc 5 Write Go unit tests for db CRUD with random data 6 A clean way to implement database transaction in Golang 7 DB transaction lock & How to handle deadlock 8 How to avoid deadlock in DB transaction? Queries order matter! 9 Deeply understand Isolation levels and Read phenomena in MySQL & PostgreSQL 10 How to setup Github Actions for Go + Postgres to run automated tests 11 Implement RESTful HTTP API in Go using Gin 12 Load config from file & environment variables in Golang with Viper 13 Mock DB for testing HTTP API in Go and achieve 100% coverage 14 Implement transfer money API with a custom params validator in Go 15 Add users table with unique & foreign key constraints in PostgreSQL 16 How to handle DB errors in Golang correctly 17 How to securely store passwords? 18 How to write stronger unit tests with a custom go-mock matcher 19 Why PASETO is better than JWT for token-based authentication? 20 How to create and verify JWT & PASETO token in Golang 21 Implement login user API that returns PASETO or JWT access token in Goįunc ( store * Store ) TransferTx ( ctx context. 1 Design DB schema and generate SQL code with dbdiagram.io 2 Install & use Docker + Postgres + TablePlus to create DB schema.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |