How To Dev

Introduction When applying Pessimistic Locking, it means that the system is always defensive and assumes that "There will certainly be other people accessing to modify this row at the same time as me, so it is best to set up a barrier to lock this row right from the reading phase ( SELECT ) to reserve the spot." All types of Row-level Locks such as FOR SHARE, FOR KEY SHARE, FOR NO KEY UPDATE, FOR UPDATE in PostgreSQL are pure tools of the Pessimistic Locking mindset. Detail In the previous article, we explored Row-level locks and their characteristics. Now, I will guide you through its specific use cases when applied in a system like E-commerce. First, let us create the tables and seed data as follows. This is a simple schema to illustrate how to apply Pessimistic locking to solve problems, rather than being as comprehensive as a real-world production system. CREATE TABLE customers ( id INT PRIMARY KEY , name VARCHAR ( 100 ), phone VARCHAR ( 20 ), loyalty_p...

Introduction In the database world, a lock is a mechanism that helps the system manage simultaneous access and modification of data by multiple users or processes at a single point in time. This is the exact solution to ensure privacy and safety, because allowing unrestricted, uncontrolled access and modification of data will cause everything to become chaotic. Locks are not created to slow down the system, but rather a mandatory blocking mechanism to keep data accurate and consistent when thousands of users access it simultaneously. Why Do We Need Locks Without locks, the system will fall into a data conflict state leading to data corruption, which can cause three classic problems: Lost Update: Two users modify the same row of data at the same time and the user who saves later will overwrite and completely lose the data of the previous user. Dirty Read: You read data that is being modified by someone else, but they have not committed yet. If they perform a rollback afterwards, the dat...