The Backend Engineering Show with Hussein Nasser

All Postgres Locks Explained | A Deep Dive

Mar 19, 2023

49:11

Creator website

Highlights

AI Chapters

Episode notes

How to Achieve Table Level Locks in Postgres?

02:44

PostgreSQL Application Advisorial Lock

02:39

PostgreSQL Advisory Locks - What Are They?

02:13

Introduction

2min

The Back-in Engineering Show - Spotify for Podcasters

3min

Can You Still Acquire Access Shared Locks and Exclusive Locks?

2min

PostgreSQL Advisory Lock

3min

How to Achieve Table Level Locks in Postgres?

3min

The Most Aggressive One, Access Exclusive

3min

Vacuumful Removes Physically Removes All These Space

3min

Access Share, Access Exclusive, and Access Share Locks

2min

What Happens When You Delete a Row, Share Update Exclusive?

3min

10.

Create Index Concurrently Acquires Share Lock on the Table

3min

11.

The Most Important Box Is Share Update Exclusive

2min

12.

Row Level Locks

3min

13.

Select for Update, Is It a Delete, or Delete?

2min

14.

The Concurrent Locking for No Key Update

3min

15.

How to Acquire a Key Share in PostgreSQL?

3min

16.

How Do I Simulate a Delete and Update in Postgres?

4min

17.

The Importance of Deadlocks in SQL Server, Right?

2min

18.

PostgreSQL Application Advisorial Lock

3min

19.

PostgreSQL Advisory Locks - What Are They?

2min

Get my database engineering course https://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 SHARE
20: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 SHARE
35:10 Row Lock Conflict Matrix

39:25 Page-Level Locks

42:00 Deadlocks

46:00 Advistory Locks

47:20 Summary

https://www.postgresql.org/docs/current/explicit-locking.html

Remember Everything You Learn from Podcasts

Save insights instantly, chat with episodes, and build lasting knowledge - all powered by AI.