[Backend #7] DB transaction lock & How to handle deadlock in Golang

[@ricassiocosta]

We usually just see the ready-to-go implementation with no errors out there, and these troubleshooting sessions is sooo valuable! Thanks for this content!

[@nuttaphonr.6498]

Thanks for sharing this great content ! 9:08 select normally will not block other select 9:41 add FOR UPDATE will block 10:05 update account balance 10:15 2nd tx is unblock! 11:24 deadlock detected 12:05 add print log to debug 13:06 passing struct as key of Context 15:38 sql for transfer, entries, updateBalance 16:20 run 2 tx parallel 17:32 insert one table block other table 17:58 run sql to see which lock 20:20 run sql to see relation of lock 21:09 foreign key constraint cause lock (its acquire lock to prevent unconsistent) 22:27 remove foreign key will no lock acquried (not best solution) 23:37 only change accountBalance, id key would not be touch (tell sqlc no need to be locked)

[@TECHSCHOOLGURU]

Thank you!

[@lacascadaobregon]

this needs more upvotes. i

[@cybermindable]

I've been working as software engineer for some 6 years already and I learn a lot from this series. Thank you sir for good work!

[@TECHSCHOOLGURU]

Cool, thanks!

[@stevenyke3836]

Can you bring me like helper on work?

[@tambui459]

This is a gold mine, I'm new to Go and learning a tons watching this playlist, hands down the best tutorial I've watched ever, and it's FREE? Can't imagine how much efforts you put into creating something like this, thank you so much. You deserved tons more subs.

[@consoledoterror971]

Thanks!

[@engdoretto]

You are an excellent teacher! I’m learning a lot with this playlist.... thank you so much!!!

[@TECHSCHOOLGURU]

Thanks, Carlos! Happy to hear that!

[@arijitroy5425]

Never learnt more about anything from a less than 30 mins video. Superb content. Thanks so much !! I have gone ahead and bought ur Udemy course. My way of saying thanks. Thanks once again !!

[@overstandnigeria]

This content is of the very highest quality. You teach very well and I love that you explain all that you are doing . Please keep it up .

[@TECHSCHOOLGURU]

I appreciate that!

[@pss_crs]

This playlist is the best contact I've incountered since I was on youtube, I watched a lot and I repeat that I always benefit from new knowledge not just programming but Computer science as general thanks again brother never tire of, ten professionals who benefit from your lessons are more important to you one million connected but not valued for your efforts

[@TECHSCHOOLGURU]

Thanks a lot! Really appreciate it!

[@lacascadaobregon]

@@TECHSCHOOLGURU yes you hit on a lot of good topics: deadlock, rollback, concurrency in golang, goroutines, context, testing, debugging exact PSQL line with that SQL query you placed in there. thank you

[@pss_crs]

​@@TECHSCHOOLGURUAs SAAS becomes more popular among startups, do you plan to share your knowledge about multi-tenant architecture in DBMSs and Go language? thanks again BTW

[@karamanabdullah]

WOOOOOWWW!! Perfect video,This is most perfectly video when I've ever seen. Not only coding, you explain mentality and remedies.

[@TECHSCHOOLGURU]

Wow, thank you!

[@ArcenstoneAa]

Damn dude this is so so great and important content with troubleshooting and explaining all the way it goes, big respect for this content

[@thirislifelogs]

The content is just gold. Thank you!

[@TECHSCHOOLGURU]

Thank you!

[@dangquyit]

Thank you for sharing such deep and extensive knowledge, you're truly amazing.

[@TECHSCHOOLGURU]

I appreciate that!

[@mamommaaa]

This playlist open my mind, I think bank system is high class of programing, I will applied this technical for my work.

[@TECHSCHOOLGURU]

Cool! Thank you!

[@oduber.vasquez]

Thank you very much! We look forward to more videos, to keep learning! Regards

[@TECHSCHOOLGURU]

Thanks for your support, Oduber! More to come!

[@aminroslan]

this man is good. great at teaching and making the audience understand the core issues and solutions!

[@TECHSCHOOLGURU]

Thanks, Amin!

[@mohitahlawat4944]

So much information... pure gold, thanks for such quality content man!!

[@TECHSCHOOLGURU]

My pleasure!

[@dnncntt]

Very good content with in deep knowledge about tech! Thank you guys very much!

[@TECHSCHOOLGURU]

Thanks a lot, Danh! More to come!

[@english51]

Previously learned in c program about philosophers eating, left hand and right hand, processes dead lock. Now with the example of database, I understand better.

[@TECHSCHOOLGURU]

Awesome!

[@vitaliibaryshev7269]

Amazing!!!! a lot of useful, high quality information. Thank you! Please continue making such great content.

[@TECHSCHOOLGURU]

Thank you! Will do!

[@PriyanshuSingh-sz8gn]

I am student and it was fun and kind of mind blowing to implement the stuff I have read in theory only. I am learning a lot. Thank you!!

[@TECHSCHOOLGURU]

Wonderful!

[@danpetre5414]

This is outstanding! I am learning a lot :) Thank you for your work and dedication

[@TECHSCHOOLGURU]

My pleasure!

[@temurmannonov2473]

Thanks a lot! Your tutorials are helping a lot.

[@TECHSCHOOLGURU]

Glad to hear that!

[@HelloWorld-tn1tl]

Great stuff man !!!

[@TECHSCHOOLGURU]

Appreciate it!

[@SamuelVolder]

Big thanks for you Sir Very greatcontent

[@riadafridishibly327]

Man!!! You're amazing. Thanks You.

[@TECHSCHOOLGURU]

Thanks!

[@mc0vz]

Great lesson thanks!

[@TECHSCHOOLGURU]

My pleasure!

[@andresorrego6778]

What a genius!

[@TECHSCHOOLGURU]

Thank you!

[@eureka4740]

thanks a lot ! i learned so much from your video ! and i want to learn more ! looking forward to the new video!

[@TECHSCHOOLGURU]

Happy to hear that!

[@TheWowashka]

AWESOME!!

[@TECHSCHOOLGURU]

Thanks!!

[@inyuns5260]

thanks! its cool!

[@mass13982]

Amazing content.

[@TECHSCHOOLGURU]

Much appreciated!

[@shaxzooda_cookings]

Thanks very much

[@shalsteven]

21:14 I still don't understand why Get Account 1 has to wait for the Create transfer?

[@muntadher8087]

Overwhelmingly new and diffcult to understand, would need to learn some basics first about deadlock then come back again

[@jarriabidi1438]

Amazing content

[@TECHSCHOOLGURU]

Thanks, Jarri!

[@thanhkhuu4167]

Good content! Thanks so much. Can you create video about structure project has more than hundred tables?

[@ankitsagar255]

I have one query, what if we need to check the balance first if it's not less than the amount to be deducted, then in that case we again have to read from the table first right, putting the same lock situation again, instead of leveraging the direct update query [25:30]. What would be the best approach in that case? PS: Wonderful series (y)

[@TECHSCHOOLGURU]

Hi Ankit, We don't have to query the record to check the amount. We can just include the check in a "WHERE balance >= amount" clause. We can also add a DB constraint to make sure the balance is always non-negative. Hope that answered your question. Feel free to join Tech School's discord server if you want to discuss more with me and other students: bit.ly/techschooldc

[@ankitsagar255]

@@TECHSCHOOLGURU Great, thanks 👍🏻

[@mahmoudgeiziry3100]

excellent

[@TECHSCHOOLGURU]

Thank you! Cheers!

[@stuckinforloop]

In future video, if possible, can you teach logging with zap

[@TECHSCHOOLGURU]

Thanks, Neel! Great suggestion!

[@stuckinforloop]

@@TECHSCHOOLGURU man you are my only hope at mastering golang

[@stuckinforloop]

just keep producing the content.

[@NhatNguyen-yf4qi]

Hi, thank you for great lecture. I have a question that at the end of the video you use AddAccountBalance instead of GetAccountForUpdate and UpdateAccount, why AddAccountBalance always get the right amount of account and not cause deadlock? Is it because UPDATE mean SELECT with FOR NO KEY UPDATE ? Again thanks for sharing this great content !

[@techdevmt7816]

thanks a lot!!

[@TECHSCHOOLGURU]

You're welcome!

[@recepdemir302]

thanks a lot !

[@TECHSCHOOLGURU]

Welcome!

[@quocattran772]

Hello, thanks for sharing excellent content! appreciate that. But at the end of the video, you shared a way to optimize our TransferTx function. You remove 2 queries that are getting 2 accounts and "lock" those 2 rows in the Database. So is it good and deserves to do?

[@TECHSCHOOLGURU]

Hey Đạt, Removing those 2 queries means that we're reducing the time needed to communicate with the database. So naturally, the performance of the app would be better. I think it's definitely worth the effort.

[@minhnghia107]

The key point to fix `deadlock` here is `postgresdb` worries we update key => tell it we not gonna update key.

[@rishabhagrawalzra]

Hi, TECH SCHOOL, Why are we processing two transactions concurrently? Can't we just process them in a queue, or if it will decrease the efficiency of our application, then can't we just make those transactions in the queue working on the same account?

[@TECHSCHOOLGURU]

imagine you're building a bank for millions of customers. there will definitely a lot of concurrent transactions. we can't just let them wait for each other, right? and a transaction often involves multiple accounts (usually 2), so in which account's queue do you put the transaction?

[@Daniel-xi2eb]

hi teacher , i from tw, can i ask question? in this class, for update in postgres use for no key update but if in mysql how to fix this question I've been looking for a long time but can't find the answer

[@robert5763]

Great content! Can you tell me the name of the vscode theme you are using in this video?

[@TECHSCHOOLGURU]

Thanks, Robert! The theme is Shades of purple :D

[@taylordurden594]

Great resouce! Please zoom in the code, it's hard to watch!

[@dangbey4822]

why addAccountBalance blocking other transactions ? I mean why we are not writing for update there also

[@dangbey4822]

because UPDATE is already blocking by not writing NO KEY there?

[@TECHSCHOOLGURU]

yeah, UPDATE is a write operation, so it will block.

[@郑建兵-m7k]

PostgreSQL has "select .... for no key update", but MySQL hasn't "no key". How to do in MySQL ?

[@tuanlyngoc2013]

Today i learn: Select for No key update!

[@TECHSCHOOLGURU]

Cheers! :D

[@郑建兵-m7k]

@@TECHSCHOOLGURU How to do in MySQL ?

[@dangbey4822]

Why when we start to use getaccountforupdate, when we printed the results of get account problem: 380 390 370 400 370 410 why there is a 410, not 400, because if they are getting balance from the first (380 and 390)

[@ikehakinyemi]

I scrolled down to find this😭. Thought I was going insane thinking about this at first, until I took my time to execute the queries 'concurrently' (swapping executions on two instances of Postgres). And yeah, if do take your time and run through the queries on the two instances, you get the '370 410' balances, as one instance blocks execution until the other 'commit'. Queries: ``` BEGIN; SELECT * FROM accounts WHERE id = 5; UPDATE accounts SET balance = x - 10 WHERE id = 5 RETURNING *; SELECT * FROM accounts WHERE id = 6; UPDATE accounts SET balance = x + 10 WHERE id = 6 RETURNING *; COMMIT; ```

[@bikramgyawali7292]

Thanks a lot for this series . I have come to this error and stuck for almost 2 3 days cannot solve by myself . panic: runtime error: invalid memory address or nil pointer dereference [signal SIGSEGV: segmentation violation code=0x1 addr=0x20 pc=0x53ce73] goroutine 11 [running]: database/sql.(*DB).conn(0x0, {0x7865d8, 0xc00007be00}, 0x1) /usr/local/go/src/database/sql/sql.go:1282 +0x53 database/sql.(*DB).begin(0xc000059920?, {0x7865d8, 0xc00007be00}, 0x60?, 0x58?) /usr/local/go/src/database/sql/sql.go:1853 +0x33 database/sql.(*DB).BeginTx.func1(0x60?) /usr/local/go/src/database/sql/sql.go:1836 +0x45 database/sql.(*DB).retry(0xc0000599b8?, 0xc000059970) /usr/local/go/src/database/sql/sql.go:1538 +0x47 database/sql.(*DB).BeginTx(0xc000059af8?, {0x7865d8?, 0xc00007be00?}, 0x2?) /usr/local/go/src/database/sql/sql.go:1835 +0x8a what is this issue Can anyone help me :

[@TECHSCHOOLGURU]

It's hard to tell without seeing your codes, but I guess you're facing the same issues as some other students. Please check this line: github.com/techschool/simplebank/blob/master/db/sqlc/main_test.go#L22 And make sure that you're not using := instead of = here testDB, err = sql.Open(dbDriver, dbSource) When you use :=, it creates a new local variable, so the global testDB is not initialized, and remain nil and you'll get a nil pointer error Feel free to join Tech School's discord server to chat directly with me and other students if you need any help: discord.com/invite/BksFFXu

[@angnat05]

Got an error on make migratedown (migrate -path db/migration -database "postgresql://root:password.1@localhost:5432/simple_bank?sslmode=disable" -verbose down) error: Dirty database version -1. Fix and force version. does anyone have the solotion? Thanks

[@TECHSCHOOLGURU]

You can connect to the the DB (using Table Plus for example), then correct the current version and set dirty field to FALSE in the schema_migrations

[@angnat05]

@@TECHSCHOOLGURU thank you for the response 👍🙏

[@arijitroy5425]

Never learnt more about anything from a less than 30 mins video. Superb content. Thanks so much !! I have gone ahead and bought ur Udemy course. My way of saying thanks. Thanks once again !!