YugabyteDB column-level locking
Introduction In this article, we are going to see how YugabyteDB allows you to scale writes by employing column-level locking instead of the traditional row-level locking used by Oracle, MySQL, PostgreSQL, or SQL Server. If you’re new to YugabyteDB, then you can start with this article first, as it explains what YugabyteDB is and why you should definitely consider using it.
Strong Consistency with YugabyteDB
Introduction In this article, we are going to see why the default strong consistency guarantees offered by YugabyteDB allow you to design applications that are more resilient than when using traditional relational database systems. If you’re new to YugabyteDB, check out this article first, in which I explain what YugabyteDB is and why you should consider using it.
The race condition that led to Flexcoin bankruptcy
Introduction It’s hard to imagine that a race condition bug could lead to the bankruptcy of a given online service, isn’t it? In this article, I’m going to show you how a race condition led to the bankruptcy of Flexcoin in 2014.
A beginner’s guide to Serializability
Introduction In this article, we are going to see what Serializability means and what guarantees does it offer. Relational database systems provide a Serializable isolation level that’s supposed to provide transaction Serializability. However, as you will soon see, some databases even provide Strict Serializability, which is a combination of Serializability and Linearizability.
SQL Server Foreign Key Locking
Introduction In this article, we are going to see how SQL Server Foreign Key constraints are locking the parent record when executing a child record UPDATE. This situation is specific to SQL Server and happens even when using the Read Committed Snapshot Isolation level.
How does the 2PL (Two-Phase Locking) algorithm work
Introduction The 2PL (Two-Phase Locking) algorithm is one of the oldest concurrency control mechanisms used by relational database systems to guarantee data integrity. In this article, I’m going to explain how the 2PL algorithm works and how you can implement it in any programming language.
How to implement a database job queue using SKIP LOCKED
Introduction In this article, we are going to see how we can implement a database job queue using SKIP LOCKED. I decided to write this article while answering this Stack Overflow question asked by Rafael Winterhalter. Since SKIP LOCKED is a lesser-known SQL feature, it’s a good opportunity to show you how to use it and why you should employ it, especially when implementing a job queue task.
How do UPSERT and MERGE work in Oracle, SQL Server, PostgreSQL and MySQL
Introduction Last week, Burkhard Graves asked me to answer the following StackOverflow question: And, since he wasn’t convinced about my answer: I decided to turn it into a dedicated article and explain how UPSERT and MERGE work in the top 4 most common relational database systems: Oracle, SQL Server, PostgreSQL, and MySQL.
How do PostgreSQL advisory locks work
Introduction PostgreSQL, like many modern RDBMS, offers both MVCC (Multi-Version Concurrency Control) and explicit pessimistic locking for various use cases when you want a custom concurrency control mechanism. However, PostgreSQL also offers advisory locks which are very convenient to implement application-level concurrency control patterns. In this article, we are going to explain how PostgreSQL advisory locks work and how you should use them.
How does MVCC (Multi-Version Concurrency Control) work
Introduction In this article, I’m going to explain how the MVCC (Multi-Version Concurrency Control) mechanism works using PostgreSQL as a reference implementation. In Concurrency Control theory, there are two ways you can deal with conflicts: You can avoid them, by employing a pessimistic locking mechanism (e.g. Read/Write locks, Two-Phase Locking) You can allow conflicts to occur, but you need to detect them using an optimistic locking mechanism (e.g. logical clock, MVCC) Because MVCC (Multi-Version Concurrency Control) is such a prevalent Concurrency Control technique (not only in relational database systems, in this article,… Read More
