All Postgres Locks Explained | A Deep Dive
Vložit
- čas přidán 19. 05. 2024
- Fundamentals of Database Engineering udemy course (link redirects to udemy with coupon)
database.husseinnasser.com
In this video I do a deep dive in all locks obtained by postgres, I learned a lot while making this video and hope you enjoy it.
0:00 Intro
2:30 What are Locks?
5:30 Overview of Postgres Locks
9:10 Table-Level Locks
11:40 ACCESS EXCLUSIVE
17:40 ACCESS SHARE
19:00 ROW SHARE20:15 ROW EXCLUSIVE
21:15 SHARE UPDATE EXCLUSIVE
23:30 SHARE
24:50 SHARE ROW EXCLUSIVE
25:18 EXCLUSIVE
25:30 Table Lock Conflict Matrix
28:30 Row-Level Locks
30:00 FOR UPDATE
33:00 FOR NO KEY UPDATE
34:00 FOR SHARE
34:40 FOR KEY SHARE35:10 Row Lock Conflict Matrix
39:25 Page-Level Locks
42:00 Deadlocks
46:00 Advisory Locks
47:20 Summary
Doc www.postgresql.org/docs/curre...
Fundamentals of Backend Engineering Design patterns udemy course (link redirects to udemy with coupon)
backend.husseinnasser.com
Fundamentals of Networking for Effective Backends udemy course (link redirects to udemy with coupon)
network.husseinnasser.com
Follow me on Medium
/ membership
Introduction to NGINX (link redirects to udemy with coupon)
nginx.husseinnasser.com
Python on the Backend (link redirects to udemy with coupon)
python.husseinnasser.com
Become a Member on CZcams
/ @hnasr
Buy me a coffee if you liked this
www.buymeacoffee.com/hnasr
Arabic Software Engineering Channel
/ @husseinnasser
🔥 Members Only Content
• Members-only videos
🏭 Backend Engineering Videos in Order
backend.husseinnasser.com
💾 Database Engineering Videos
• Database Engineering
🎙️Listen to the Backend Engineering Podcast
husseinnasser.com/podcast
Gears and tools used on the Channel (affiliates)
🖼️ Slides and Thumbnail Design
Canva
partner.canva.com/c/2766475/6...
Stay Awesome,
Hussein - Věda a technologie
Wrote a tool to show conflicting table and row locks in postgres. postgres-locks.husseinnasser.com
Pick a pg DDL or DML command and it tells you what is allowed to run concurrently with it, and what does it conflict with (blocks).
Tried to list all relevant commands. might missed few.
Brilliant stuff as always. Watching you explaining the docs and listening to your valuable thoughts is a productive time for me :). Thanks for making it!
PS: I love postgres docs
Love this! Nice job Hussein. I find the format of hearing you read the docs and providing your thoughts to be engaging and even entertaining. :) While I can read the docs myself, it's nice to get your perspectives and thoughts as if we're reading and discussing it together. You provide a lot of little valuable insights along the way.
Hats off to this human being who is serving other human beings.
This man is just awesome. Thanks for so much valuable content Hussein.
Thank you for this great video Mr Hussein 🙌
Will come back later. Just found out I'm not good enough to understand it after 15 mins. Good job man
A reindex needs an ACCESS EXCLUSIVE lock to prevent any reads that may make use of the index that is being reindexed.
A create index does not need this because any reads will have no chance to use it, since it does not exist yet.
Both will block writes though, unless you can use the CONCURRENTLY clause.
You are great, it's obvious that you feel software engineering ;) Greetings from Poland
It would be great if someone had a nice demo to show all these conflicts and what operations you are or are not allowed to carry out, maybe something like how you used two docker containers to show MVCC in your database fundamentals course.
You might have to find a way to 'pause' a transaction, e.g. if you SELECT one row, the lock won't be there for very long, so you would probably miss it happen.
begin; select ....; select pg_sleep(10); commit;
Great effort and awesome video 😊 thanks
Hussein, why is Postgres your choice among others? I wonder whether I will focus on SQL server or Postgres to get hired as a DBA?
Great videos ❤
At 42:01 Hussein says that he has a whole video about deadlocks. I can't find that one. Does anyone know which video he was referring to?
The timeline says "Advistory Locks". It's "Advisory Locks".
thanks fixed
You are just reading the postgres documentation. Don't do that, we can read that. We need explanation using code. Don't waste our time. I would give -(minus)5/5.
You would be surprised how many developers read any docs at all...
No, just appreciate some one efforts. You can do it yourself. I find this way of presenting information very useful, becz next time you read the doc yourself, it will feel familiar. See a lot of effort is needed to make videos along with full time job.
No one asked you to make videos ok!. Appreciation should be given to someone who deserves
@@AshisRaj and no one asked you to comment here. He made videos by his own choice. It's his platform. Throw your-vity somewhere else.
@@InvincibleMan99 no one asked you to be his lawyer. I would certainly do what I feel is correct
Request to do it in mysql