How To Dev

Introduction This is a concept created by Postgres as a set of behavior rules to decide whether other people can see or edit data when someone is modifying it. The isolation level only makes sense and only works when accompanied by a Transaction. This is the factor that ensures Isolation, helping to decide how parallel transactions handle operations independently of each other. When you execute a single statement like UPDATE products SET price = 100, Postgres activates Autocommit mode to automatically wrap the statement with BEGIN...COMMIT. It automatically creates a Transaction for extremely fast execution. Levels There are currently 4 levels ranging from the most relaxed to the strictest. The stricter the level, the more accurate the data, but the system runs slightly slower. Read Uncommitted This is the most relaxed level, allowing you to see what others are drafting even if they have not yet committed. Because of that characteristic, using it is very dangerous as it causes Dirty Re...

Introduction In contrast to Pessimistic Locking which I explained in the previous article, Optimistic Locking is suitable for use in cases where two people modifying the same data row rarely occurs Therefore, it allows everyone to read and modify data freely and the check is only performed when executing an UPDATE If this row has not been modified by anyone, the UPDATE will proceed normally If someone has already modified this row beforehand, it will report an error and request a user retry To implement Optimistic Locking, you do not use any row-level lock commands, but will instead implement it yourself in code via a version column (or an updated_at column) in the table When you SELECT row data, you first retrieve the value of the version column After performing the required business logic processing When you UPDATE that row, you check whether the version matches the previously retrieved value If no one has UPDATE d it yet, the version remains unchanged, you will get that row to UP...

Introduction Unlike Level Locks created by the database at the physical layer, developers can freely create Advisory Locks based on custom logic at the application level The database does not know the meaning of this lock and it only acts as an intermediary to hold the lock (usually a bigint number) The process that arrives first is granted the lock for processing Meanwhile, subsequent processes must line up and wait to acquire the lock When a process finishes processing, it returns the lock to the next process Advantages When using other types of locks (such as Table level lock, Row level lock or Page level lock), you must rely on an actual existing data row in the table to lock, whereas Advisory Lock does not require any available data row, you can use any random number to lock It is extremely lightweight because it only exists on the Database RAM, does not write to the hard drive and does not generate redundant data (dead tuples) Functions To create an advisory lock, Postgres provid...

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...