# isolation-level

Published articles for isolation-level.

This is one page of public article previews, not the complete archive. Follow Next page to continue. Summaries are not the original full articles.

## Optimistic Locking Overview

DevFeed: [Optimistic Locking Overview](<https://devfeed.tech/articles/optimistic-locking-overview-26514.md>)

Original publisher: [Read original article](<https://medium.com/engineering-housing/optimistic-locking-overview-6b30315e759b?source=rss----3a69e32e2594---4>)

Author: somesh sharma

Published: 2025-06-19T09:02:40Z

Content type: article

Language: en

Sources: [Housing.com](<https://devfeed.tech/sources/housing-com.md>)

Topics: [locking](<https://devfeed.tech/topics/locking.md>), [Transactions](<https://devfeed.tech/topics/transactions.md>), [retry](<https://devfeed.tech/topics/retry.md>), [database-consistency](<https://devfeed.tech/topics/database-consistency.md>)

Tags: [concurrency-control](<https://devfeed.tech/tags/concurrency-control.md>), [data-integrity](<https://devfeed.tech/tags/data-integrity.md>), [database](<https://devfeed.tech/tags/database.md>), [databases](<https://devfeed.tech/tags/databases.md>), [isolation-level](<https://devfeed.tech/tags/isolation-level.md>), [locking](<https://devfeed.tech/tags/locking.md>), [long-running](<https://devfeed.tech/tags/long-running.md>), [optimistic-locking](<https://devfeed.tech/tags/optimistic-locking.md>), [ratio](<https://devfeed.tech/tags/ratio.md>), [retry](<https://devfeed.tech/tags/retry.md>), [sql](<https://devfeed.tech/tags/sql.md>), [stateless](<https://devfeed.tech/tags/stateless.md>), [transaction-management](<https://devfeed.tech/tags/transaction-management.md>), [transactions](<https://devfeed.tech/tags/transactions.md>), [version](<https://devfeed.tech/tags/version.md>)

### AI overview

Optimistic locking lets transactions proceed without upfront locks, then checks for conflicts when a record is updated. A common database approach stores a version number or timestamp on each row: an update succeeds if the stored version still matches the one originally read, and fails if another transaction changed it; the operation can then be retried. The excerpt says it can suit low-contention, read-heavy workloads, retryable updates, stateless operations, and long-running transactions where holding locks is impractical. It also notes that additional optimistic locking may be unnecessary when database isolation already provides sufficient protection, citing Serializable isolation. The excerpt ends mid-explanation, so it does not establish the full SQL example or discussion.

### Source excerpt

Optimistic locking is a concurrency control mechanism where we assume that multiple transactions can safely access data without conflict, allowing them to proceed without locking the data upfront. Unlike pessimistic locking, where resources are locked to avoid conflicts, optimistic locking allows transactions to proceed without locks and checks for conflicts only when updating the data. If a conflict is detected (e.g., another transaction has already modified the data), the operation fails, and you can retry it. When Should You Use Optimistic Locking? Optimistic locking is ideal for use in scenarios where: Low contention exists: If it's unlikely that multiple users or processes will try to update the same data at the same time, optimistic locking is a good fit. This is especially true in systems where most operations involve reading data rather than writing it. High read-to-write ratio: If your application is mostly about reading data and writing happens less frequently, optimistic locking helps avoid the overhead of locking rows during reads. Non-critical updates: In cases where it's okay to retry a failed update without much impact, optimistic locking is a good choice. When conflicts arise, either the user or the system can simply retry. Stateless operations: Optimistic locking is suitable in stateless environments where holding onto locks across multiple requests or sessions isn't feasible. Long-running transactions: If your transactions take a long time to complete, holding locks during the entire process isn't feasible. Optimistic locking provides flexibility while still maintaining data integrity. How Optimistic Locking Works in Databases In databases, optimistic locking is usually implemented with a version column. Each row in the database has a version field (like a number or timestamp) that gets updated whenever the row is modified. When updating a record, the database checks if the version in the database matches the version the transaction originally read

## Pat's Big Deal, and Transaction Coordination

DevFeed: [Pat's Big Deal, and Transaction Coordination](<https://devfeed.tech/articles/pat-s-big-deal-and-transaction-coordination-12550.md>)

Original publisher: [Read original article](<http://brooker.co.za/blog/2024/01/23/big-deal.html>)

Author: Marc Brooker

Published: 2024-01-23T00:00:00Z

Content type: article

Language: en

Sources: [Marc Brooker's](<https://devfeed.tech/sources/marc-brooker-s-blog.md>)

Topics: [Transactions](<https://devfeed.tech/topics/transactions.md>), [synchronization](<https://devfeed.tech/topics/synchronization.md>), [database-consistency](<https://devfeed.tech/topics/database-consistency.md>), [Database](<https://devfeed.tech/topics/database.md>)

Tags: [application](<https://devfeed.tech/tags/application.md>), [concurrency](<https://devfeed.tech/tags/concurrency.md>), [concurrent](<https://devfeed.tech/tags/concurrent.md>), [concurrently](<https://devfeed.tech/tags/concurrently.md>), [database](<https://devfeed.tech/tags/database.md>), [deal](<https://devfeed.tech/tags/deal.md>), [developer](<https://devfeed.tech/tags/developer.md>), [don-t](<https://devfeed.tech/tags/don-t.md>), [isolation](<https://devfeed.tech/tags/isolation.md>), [isolation-level](<https://devfeed.tech/tags/isolation-level.md>), [key](<https://devfeed.tech/tags/key.md>), [pat](<https://devfeed.tech/tags/pat.md>), [serializable](<https://devfeed.tech/tags/serializable.md>), [snapshot](<https://devfeed.tech/tags/snapshot.md>), [snapshot-isolation](<https://devfeed.tech/tags/snapshot-isolation.md>), [transaction](<https://devfeed.tech/tags/transaction.md>), [transactions](<https://devfeed.tech/tags/transactions.md>), [updating](<https://devfeed.tech/tags/updating.md>)

### AI overview

The BIG DEAL pairs apps that avoid concurrent updates to the same key with databases that avoid coordinating transactions on disjoint keys, but under snapshot isolation this permits write skew. The excerpt illustrates how two transactions can both commit despite producing an outcome that cannot be ordered serially, potentially allowing an application to oversell warehouse stock. Serializable isolation prevents this anomaly by checking whether keys read by a transaction were changed before commit. That can require tracking broad read and predicate sets and coordinating across many machines; in the chair example, snapshot isolation coordinates around the chosen chair, while serializability may involve every chair. The proposed "serializable New Big Deal" asks apps to avoid updating keys frequently read by concurrent processes and to limit reads of frequently written keys. It simplifies correctness reasoning but can make scalability and performance harder for application programmers. The excerpt frames snapshot isolation's scalability advantage as dependent on applications' read and write patterns.

### Source excerpt

Pat's Big Deal, and Transaction Coordination Working together towards a common goal. I have a lot of opinions about Pat Helland's CIDR'24 paper Scalable OLTP in the Cloud: What's the BIG DEAL?1. Most importantly, I like the BIG DEAL that he proposes: Scalable apps don't concurrently update the same key. Scalable DBs don't coordinate across disjoint TXs updating different keys. In exchange for fulfilling their sides of this big deal2 the application gets a database that can scale6, and the database gets a task to do that allows it to scale. The cost of this scalability, for the application developer, is having to deal with a weird concurrency phenomenon called write skew. In this post, we'll look at write skew, why not preventing it helps databases scale, and how we can alter Pat's big deal to prevent write skew and get serializability. In particular, we'll try answer two big questions: Is Pat's big deal the best deal available? Would a serializable big deal be better for application programmers? Snapshot Isolation The big deal experiences write skew because of the database isolation level that Pat chose: snapshot isolation. Other than this particular weird behavior, the application can pretend that concurrent transactions run in a serial total order, which makes application developer's lives easy. Nobody likes reasoning about concurrency, and reasoning about concurrency while correctly implementing business logic is pretty hard, so that's comforting. There are a lot of ways to talk about these concurrency anomalies in the database literature. Tens, at least. The one I think is most accessible to developers is Martin Kleppmann's Hermitage, a set of minimal tests that illustrate each of the weird things that databases users can experience. The test setup is super simple: create table test (id int primary key, value int); insert into test (id, value) values (1, 10), (2, 20); Next, we make two connections to the database we'll call A and B, and run a transaction through

## What write skew looks like

DevFeed: [What write skew looks like](<https://devfeed.tech/articles/what-write-skew-looks-like-23825.md>)

Original publisher: [Read original article](<https://cockroachlabs.com/blog/what-write-skew-looks-like>)

Author: Justin Jaffray

Published: 2022-03-31T00:00:00Z

Content type: article

Language: en

Sources: [Cockroach Labs](<https://devfeed.tech/sources/cockroach-labs.md>)

Topics: [Transactions](<https://devfeed.tech/topics/transactions.md>), [acid](<https://devfeed.tech/topics/acid.md>), [consistency](<https://devfeed.tech/topics/consistency.md>)

Tags: [concurrency](<https://devfeed.tech/tags/concurrency.md>), [cycles](<https://devfeed.tech/tags/cycles.md>), [database](<https://devfeed.tech/tags/database.md>), [isolation](<https://devfeed.tech/tags/isolation.md>), [isolation-level](<https://devfeed.tech/tags/isolation-level.md>), [snapshot-isolation](<https://devfeed.tech/tags/snapshot-isolation.md>), [transaction](<https://devfeed.tech/tags/transaction.md>), [write-skew](<https://devfeed.tech/tags/write-skew.md>)

### AI overview

Snapshot Isolation gives each transaction a consistent read snapshot and prevents concurrent transactions from writing overlapping sets, yet it can still allow nonserializable write skew. In the example, one transaction copies x to y while another copies y to x; each reads before the other commits and writes a different location, producing a swap that no serial ordering permits. The article explains how to identify such anomalies with a serialization graph: dependencies form edges, and a cycle means the history is not serializable. Under Snapshot Isolation, any cycle contains consecutive concurrent read-write anti-dependencies. Serializable Snapshot Isolation uses this pattern to track transactions involved in anti-dependencies on both sides and abort a transaction or a neighbor, preventing cycles while sometimes aborting transactions that were not themselves part of a cycle. The article also argues that serializability simplifies invariant checking: each transaction can be checked on its own, whereas weaker isolation requires reasoning about interactions among transactions.

### Source excerpt

Syndication from What Does Write Skew Look Like by Justin Jaffray This post is about gaining intuition for Write Skew, and, by extension, Snapshot Isolation. Snapshot Isolation is billed as a transaction isolation level that offers a good mix between performance and correctness, but the precise meaning of "correctness" here is often vague. In this post I want to break down and capture exactly when the thing called "write skew" can happen.

## PostgreSQL Concurrency: an Article Series

DevFeed: [PostgreSQL Concurrency: an Article Series](<https://devfeed.tech/articles/postgresql-concurrency-an-article-series-34607.md>)

Original publisher: [Read original article](<https://tapoueh.org/blog/2018/08/postgresql-concurrency-an-article-series/>)

Author: Dimitri Fontaine PostgreSQL Major Contributor; Author

Published: 2018-08-14T08:49:02Z

Content type: tutorial

Language: en

Sources: [Dimitri Fontaine](<https://devfeed.tech/sources/dimitri-fontaine.md>)

Topics: [Concurrency](<https://devfeed.tech/topics/concurrency.md>), [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [Database](<https://devfeed.tech/topics/database.md>), [consistency](<https://devfeed.tech/topics/consistency.md>), [Open Source](<https://devfeed.tech/topics/open-source.md>)

Tags: [acid](<https://devfeed.tech/tags/acid.md>), [article](<https://devfeed.tech/tags/article.md>), [cache-invalidation](<https://devfeed.tech/tags/cache-invalidation.md>), [caching](<https://devfeed.tech/tags/caching.md>), [concurrency](<https://devfeed.tech/tags/concurrency.md>), [consistency](<https://devfeed.tech/tags/consistency.md>), [database](<https://devfeed.tech/tags/database.md>), [isolation-level](<https://devfeed.tech/tags/isolation-level.md>), [locking](<https://devfeed.tech/tags/locking.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [series](<https://devfeed.tech/tags/series.md>), [sql](<https://devfeed.tech/tags/sql.md>), [transactions](<https://devfeed.tech/tags/transactions.md>), [triggers](<https://devfeed.tech/tags/triggers.md>)

### AI overview

This article series explains how PostgreSQL handles concurrent access while maintaining data consistency. It covers data modification, isolation levels, locking, ACID transactions, concurrency-oriented data modeling, event processing, materialized views for caching, and triggers for transactionally correct cache maintenance.

### Source excerpt

PostgreSQL is a relational database management system. It's even the world's most advanced open source one of them. As such, as its core, Postgres solves concurrent access to a set of data and maintains consistency while allowing concurrent operations. In the PostgreSQL Concurrency series of articles here we did see several aspects of how to handle concurrent use cases of your application design with PostgreSQL. The main thing to remember is that a Database Management System first task is to handle concurrency access to the data for you.