Forem: Haris Tallat

ACID Properties in Databases: A Key to Data Integrity and Reliability

Haris Tallat — Sat, 16 Nov 2024 14:20:43 +0000

ACID properties are crucial in ensuring that databases remain reliable and consistent, even in the face of system failures or crashes during transactions. These properties Atomicity, Consistency, Isolation, and Durability help maintain data integrity in environments like banking systems, e-commerce applications, and online transactions.

In this article, we’ll dive into each ACID property and explore their importance, real-life examples, and use cases.

What Are ACID Properties?

1. Atomicity
The term atomicity comes from the concept of an atom, which is the smallest indivisible unit of matter. In the context of databases, atomicity refers to transactions being indivisible—either they fully succeed or fully fail.

If a transaction involves multiple steps, all of them must be completed successfully for the transaction to be considered successful. If any part of the transaction fails, the entire transaction is rolled back to maintain data integrity. This ensures that data is never left in an inconsistent or incomplete state.

Real-Life Example: Switch Flipping
Imagine flipping a light switch in your house. The light can either be fully ON or OFF, but there’s no state in between. If the switch fails halfway, the light doesn’t flicker or partially turn on—it remains in its original state.

Similarly, in a database transaction, if one part fails, the entire transaction fails, reverting any changes made.

Bank Transfer Example:

User A, Account Balance: $100
User B, Account Balance: $50

If User A initiates a transfer of $50 to User B, and an error occurs during the transaction, atomicity ensures that no changes are made to the database—both users’ balances remain unchanged. Abort will trigger.

— Abort : If a transaction aborts, changes made to the database are not visible.

If the transfer is successful, both User A and User B’s balances are updated, and the transaction is committed to the database. Commit will trigger.

— Commit : If a transaction commits, changes made are visible.

2. Consistency
Consistency ensures that the database remains in a valid state before and after the transaction. It guarantees that the database adheres to all defined rules, such as constraints, triggers, and relationships, sufficient balance after the transaction completes.

Real-Life Example: Balance Validation

User A, Account Balance: $100
User B, Account Balance: $50

If User A tries to transfer $200 to User B, the consistency property ensures that the transaction fails because User A does not have sufficient funds. The database will not allow the transaction to complete if it violates any defined rules, such as ensuring account balances do not go negative.

3. Isolation
Isolation ensures that transactions are processed independently, even if they occur simultaneously. This means that the operations in one transaction are isolated from others and are not affected by concurrent transactions, preventing inconsistencies.

Locking Mechanisms: Shared and Exclusive Locks
To maintain isolation, databases use locking mechanisms. Locks are applied to data to prevent other transactions from accessing or modifying it while the current transaction is in progress. There are two primary types of locks:

Shared Lock (S-lock): Allows a transaction to read the data but not modify it. Other transactions can also acquire a shared lock on the same data, allowing multiple transactions to read the data simultaneously but preventing modifications.
Exclusive Lock (X-lock): Prevents other transactions from reading or modifying the data. This is applied when a transaction needs to update data to ensure that no other transaction interferes with the process.
Real-Life Example: Bank Transfer with Concurrent Transactions
Let’s consider two transactions happening simultaneously:
Transaction 1: User A is transferring $100 from Account A to Account B.
Transaction 2: User B is transferring $50 from Account B to Account C.
Without isolation, these transactions could interfere with each other, leading to inconsistent balances.

How Isolation Works:
When Transaction 1 starts, it reads Account A’s balance (say, $500). At the same time, Transaction 2 reads Account B’s balance (say, $200). If there’s no isolation, Transaction 1 might update Account A’s balance to $400, while Transaction 2 could simultaneously update Account B’s balance to $150, leading to incorrect balances.

With isolation, these two transactions operate independently, so:

Transaction 1 locks Account A (using an exclusive lock) to prevent any other transaction from modifying it until the transaction is complete.
Transaction 2 waits until Transaction 1 commits, at which point it reads the updated balance of Account B and proceeds.

This prevents transactions from reading or modifying the same data simultaneously, avoiding inconsistencies.

Handling Concurrent Transactions with Locking:

If both Transaction 1 and Transaction 2 try to access the same record at the same time, the database will use locking to ensure that only one transaction can proceed at a time, preventing data corruption.
If both transactions attempt to lock the same resource, a deadlock may occur, where both transactions are waiting for each other to release the lock. The database can resolve deadlocks by rolling back one of the transactions, ensuring the system doesn’t hang.

4. Durability
Durability ensures that once a transaction has been committed, its changes are permanent. Even in the event of system crashes, power outages, or failures, the changes made by the transaction will not be lost.

Real-Life Example: Power Outage after Bank Transfer
Let’s say User A transfers $500 to User B. After the transaction is committed, the system crashes due to a power outage. However, because the transaction was committed, the changes to both User A’s and User B’s balances are written to durable storage (such as a disk). When the system restarts, the committed transaction is recovered, and both users’ balances are updated as expected.

Thank you for reading my article. Stay tuned for more in-depth articles 😊

Database and Their Relationships

Haris Tallat — Sun, 03 Nov 2024 08:33:44 +0000

1. What is a Database?
A database is a structured collection of data stored electronically, designed to facilitate easy access, management, and updating of that data. You can think of it as a digital filing system where information is organized into tables, making it efficient to retrieve the data you need.
Diagram

Key Components of a Database:

Data: This refers to raw information, such as names, addresses, or transaction details.
Tables: A database consists of tables that resemble spreadsheets, organized into rows and columns. Each row represents a record (an individual entry), while each column represents a field (a specific piece of information about that entry).
DBMS:A Database Management System (like MySQL, PostgreSQL, or Oracle) provides the tools to create, read, update, and delete data within the database.

2. What is a Relationship?
In general terms, a relationship refers to a connection or association between two or more entities. In the context of databases, relationships define how data in one table relates to data in another. These relationships are essential for organizing and structuring data across multiple tables, helping to avoid redundancy (duplicated data) and enhancing data integrity.

Example of Notations:

3. Types of Database Relationships
A database relationship is a defined connection between two tables, specifying how records in one table relate to records in another. There are three primary types of database relationships:

3.1 One-to-One Relationship
In a one-to-one relationship, each record in Table A corresponds to a single record in Table B, and vice versa. This type of relationship is often used when two tables contain different types of information about the same entity.

Example: Each person has only one passport, and each passport is assigned to only one person.

Schema Diagram:

Notable Points:

Foreign Key Placement: The PersonID is included in the Passport table instead of the Passport ID in the Person table because the passport is dependent on the person. If a person exists, the passport exists; a profile doesn’t make sense without a user. The table that has the dependency contains the foreign key.
Direction of the Relationship: The arrow in database relationship diagrams indicates which table contains the foreign key that references the other. When reading a one-to-one relationship in a database schema, starting from the foreign key (FK) side often provides clearer context.
Table Representation

Golang Struct Details

3.2 One-to-Many Relationship

A one-to-many relationship occurs when a single record in one table (the "one" side) can be associated with multiple records in another table (the "many" side). However, each record in the "many" table is linked back to only one record in the "one" table.

Example: One user can create multiple posts. Each post will reference a single user, establishing a one-to-many relationship between the Users and Posts tables.
Schema Diagram

Characteristics:

Single Entity: The "one" side represents a single entity.
Multiple Associations: The "many" side consists of multiple entities associated with that single entity.
Foreign Key: The "many" table contains a foreign key referencing the primary key of the "one" table. Table Representation GoLang Struct 3.3 Many-to-Many Relationship

In a many-to-many relationship, multiple records in one table can be associated with multiple records in another table. This relationship is typically implemented using a junction (or join) table that holds foreign keys referencing the primary keys of both tables.
Schema Diagram

Example Without a Junction Table:

Drawbacks of Not Using a Junction Table

Data Redundancy You may need to duplicate data across multiple rows, leading to inconsistencies and increased storage requirements.
Limited Flexibility: Not using a junction table restricts your ability to store additional attributes about the relationship itself (e.g., timestamps or status), complicating queries and reducing the richness of your data model.

Benefits of Using a Junction Table:

Reduced Redundancy: The Users and Roles tables are free from redundancy. Each user and each role is stored only once.
Clear and Manageable Relationships: The junction table clearly defines the many-to-many relationships without confusion. It simplifies the process of adding or removing roles for users.

GoLang Struct Representation

When and Why Summary Table

Understanding Databases and Relationships

Haris Tallat — Sun, 03 Nov 2024 07:44:03 +0000

Key Components of a Database:
Data: This refers to raw information, such as names, addresses, or transaction details.
Tables: A database consists of tables that resemble spreadsheets, organized into rows and columns. Each row represents a record (an individual entry), while each column represents a field (a specific piece of information about that entry).
DBMS: A Database Management System (like MySQL, PostgreSQL, or Oracle) provides the tools to create, read, update, and delete data within the database.
2. What is a Relationship?
In general terms, a relationship refers to a connection or association between two or more entities. In the context of databases, relationships define how data in one table relates to data in another. These relationships are essential for organizing and structuring data across multiple tables, helping to avoid redundancy (duplicated data) and enhancing data integrity.
Examples of notations:

Example: Each person has only one passport, and each passport is assigned to only one person.

Schema Diagram:

Notable Points:
Foreign Key Placement: The PersonID is included in the Passport table instead of the Passport ID in the Person table because the passport is dependent on the person. If a person exists, the passport exists; a profile doesn’t make sense without a user. The table that has the dependency contains the foreign key.

Direction of the Relationship: The arrow in database relationship diagrams indicates which table contains the foreign key that references the other. When reading a one-to-one relationship in a database schema, starting from the foreign key (FK) side often provides clearer context.

Here is the sample table explanation

Go lang strcut details

3.2 One-to-Many Relationship

Example: One user can create multiple posts. Each post will reference a single user, establishing a one-to-many relationship between the Users and Posts tables.

Characteristics:
Single Entity: The "one" side represents a single entity.
Multiple Associations: The "many" side consists of multiple entities associated with that single entity.
Foreign Key: The "many" table contains a foreign key referencing the primary key of the "one" table.
Table:

Golang struct:

3.3 Many-to-Many Relationship

Example Without a Junction Table

When not using a junction table, you may encounter several drawbacks:

Data Redundancy: You may need to duplicate data across multiple rows, leading to inconsistencies and increased storage requirements.

Limited Flexibility: Not using a junction table restricts your ability to store additional attributes about the relationship itself (e.g., timestamps or status), complicating queries and reducing the richness of your data model.

........

Using Junction Table

Benefits of Using a Junction Table
Reduced Redundancy:

The Users and Roles tables are free from redundancy. Each user and each role is stored only once.
Clear and Manageable Relationships:

The junction table clearly defines the many-to-many relationships without confusion. It simplifies the process of adding or removing roles for users.