The best way to use SQL NOWAIT
Introduction In this article, we are going to see what is the best way to use the amazing SQL NOWAIT feature that allows us to avoid blocking when acquiring a row-level lock. Since all the top major database support this functionality, Hibernate offers a NOWAIT option that allows you to render the proper database-specific syntax associated with this feature without risking database portability.
Spring Data JPA entity locking
Introduction In this article, we are going to see what options Spring Data JPA offers for entity locking. We will see how we can apply a shared or exclusive row-level lock upon fetching one or multiple entities.
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.
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 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.
