Pessimistic versus Optimistic Locking

Relational database systems (RDBMS) employ various locking strategies to enforce transaction ACID properties when modifying (e.g., UPDATING or DELETING) table records. On occasion, deadlock may occur when two concurrent transactions cannot make progress because each one is waiting for the other to release the lock. In Part 1 of this series, we we established what Object Locking is in Relational Databases, the different types of locks, and deadlocking. In today's follow-up, we'll be comparing the pros and cons of Pessimistic and Optimistic locking.

[Read More]

Part 1: Overview, Lock Granularity, and Deadlocks

Recently, we've had a few blogs about database transactions and they enforce the the four ACID (Atomicity Consistency Isolation Durability) properties. In today's blog, we'll be taking a look at another mechanism employed by relational databases (RDBMS) to enforce ACID properties, namely, Object Locking. Specifically, we'll learn what it is, what role(s) it plays in RDBMS transactions, and some of the side effects locking may cause. While Database Object Locking can be a fairly technical and complicated subject, we're going to break it down into layman's terms here and keep things as simple as possible.

[Read More]

In my early days as an IT consultant, I relied on a variety of open source tools to accomplish my tasks. My rational was that I was saving money by costs associated with commercial products. It would be a few years later that I would come to realize that commercial products can actually save time and money by streamlining and automating many of the common tasks that we tend to perform on a regular basis.

[Read More]

Back in August of 2020, The Many Flavors of the SQL Count() Function provided an overview of COUNT's many input parameter variations. Another way to use the COUNT() function is to combine it with the GROUP BY clause. Using the COUNT() function in conjunction with GROUP BY is useful for breaking down counts according to various groupings. In today's blog, we'll learn how to group counts by different criteria by querying the Sakila Sample Database using Navicat Premium as our database client.

[Read More]