What is SELECT FOR UPDATE in SQL? | Database Essentials

Description

What is SELECT FOR UPDATE in SQL? And what is the importance of serializability? In this Database Essentials video by Cockroach Labs Technical Evangelist Rob Reid, you will learn about both of these topics through a live demo where he compares read committed databases to databases that have serializable isolation built-in. By the end of this video, you will understand why serialized isolation is the way to go if you need to guarantee what you're reading for your database and writing to your database is always going to be consistent. Key terminology: ✔️ SELECT FOR UPDATE: A SQL command that’s useful in the context of transactional workloads. It allows you to “lock” the rows returned by a SELECT query until the entire transaction that query is part of has been committed. Other transactions attempting to access those rows are placed into a time-based queue to wait, and are executed chronologically after the first transaction is completed. ✔️ Serializable Isolation: The strongest of the four transaction isolation levels defined by the SQL standard and is stronger than the SNAPSHOT isolation level developed later. SERIALIZABLE isolation guarantees that even though transactions may execute in parallel, the result is the same as if they had executed one at a time, without any concurrency. This ensures data correctness by preventing all "anomalies" allowed by weaker isolation levels. CockroachDB always uses SERIALIZABLE isolation. Further reading: ✔️ [Blog] What is SELECT FOR UPDATE in SQL (With examples): https://cockroa.ch/3tbsHlb ✔️ [Docs] Serializable Transactions in CockroachDB: https://cockroa.ch/4a8Xwrj ✔️ [Paper] ACIDRain: Concurrency-Related Attacks on Database-Backed Web Applications: http://www.bailis.org/papers/acidrain-sigmod2017.pdf ✔️ [More info] CockroachDB: A cloud-native, distributed SQL database designed for high availability, effortless scale, and control over data placement. https://cockroa.ch/46mRnFm What is SELECT FOR UPDATE in SQL? | Database Essentials 00:00 Welcome & Introduction 00:25 Understanding serializability through a real-world example 00:54 How we will compare CockroachDB vs PostgreSQL 01:51 Looking at the code behind our comparison 03:57 Setting up a connection to PostgreSQL 04:20 Running an application with one concurrent writer 04:53 Running an application with 10 concurrent workers 05:34 20 workers with a pool size of 20 05:46 Updating PostgreSQL to be serializable 06:57 How CockroachDB and PostgreSQL handle serializability 07:33 Running a single concurrent worker in CockroachDB 07:45 Increasing the concurrency level to 10 in CockroachDB 08:12 Running 20 concurrent workers with a pool size of 20 in CockroachDB 08:29 Ignoring isolation levels in databases is no joke Postgres isolation level read committed