All Postgres Locks Explained | A Deep Dive

[@hnasr]

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.

[@InvincibleMan99]

Hats off to this human being who is serving other human beings.

[@irfanfauzi8704]

Will come back later. Just found out I'm not good enough to understand it after 15 mins. Good job man

[@NostraDavid2]

Had the same reaction a year ago. I now understand - I've read the original papers by the creator of the Relational Model (E. F. Codd) and am now half-way the Postgres Manual (only 3000 pages! 😂).

[@professortrog7742]

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.

[@andatki]

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.

[@sdmagic]

Thank you very much. That was very clear and useful. Your video gave me quite a few points to ponder.

[@lakhveerchahal]

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!

[@lakhveerchahal]

PS: I love postgres docs

[@rishavagarwal6531]

This man is just awesome. Thanks for so much valuable content Hussein.

[@thecloudterminal]

Thank you for this great video Mr Hussein 🙌

[@XiaomiPad6-z8p]

You are great, it's obvious that you feel software engineering ;) Greetings from Poland

[@roshan8853]

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.

[@jonathanfrias1]

begin; select ....; select pg_sleep(10); commit;

[@IbraheemOsama]

Great effort and awesome video 😊 thanks

[@fishinawaterbottle]

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?

[@franky1116]

Great videos ❤

[@krishnabirla16]

The timeline says "Advistory Locks". It's "Advisory Locks".

[@hnasr]

thanks fixed

[@i_youtube_]

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?

[@AshisRaj]

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.

[@mattgrave]

You would be surprised how many developers read any docs at all...

[@InvincibleMan99]

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.

[@AshisRaj]

No one asked you to make videos ok!. Appreciation should be given to someone who deserves

[@InvincibleMan99]

@@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.

[@AshisRaj]

@@InvincibleMan99 no one asked you to be his lawyer. I would certainly do what I feel is correct

[@jed271]

Request to do it in mysql