1. Transactions in SQL: In SQL, a transaction is a sequence of one or more SQL statements that are treated as a single unit of work. Transactions are used to group together a set of related operations that need to be executed together, ensuring that either all of them succeed or none of them have any effect on the database. Transactions are initiated using the following statements:

• BEGIN TRANSACTION or simply BEGIN: This statement marks the beginning of a transaction.
• COMMIT: This statement is used to commit the changes made during the transaction to the database, making them permanent.
• ROLLBACK: This statement is used to undo the changes made during the transaction and revert the database to its state before the transaction started.

2. ACID Properties in SQL:

• Atomicity: In SQL, the atomicity property is maintained by ensuring that all the statements within a transaction are treated as a single unit. If any part of the transaction fails, the changes made by the transaction are rolled back using the ROLLBACK statement.
• Consistency: SQL databases enforce data integrity constraints, such as unique key constraints, referential integrity constraints, and check constraints. These constraints ensure that data remains consistent before and after a transaction. If a transaction violates any of these constraints, it will be rolled back automatically, maintaining the consistency of the data.
• Isolation: SQL databases provide different levels of isolation to control how transactions interact with each other. Common isolation levels include “Read Uncommitted,” “Read Committed,” “Repeatable Read,” and “Serializable.” These isolation levels control the visibility of uncommitted changes and help prevent issues like dirty reads, non-repeatable reads, and phantom reads.
• Durability: SQL databases ensure durability by ensuring that once a transaction is committed using the COMMIT statement, the changes become permanent and are stored in a way that they will survive system failures. This ensures that the changes will be available and consistent even after the database system restarts.

In SQL, these ACID properties collectively provide the necessary guarantees to maintain data integrity, consistency, and reliability even in the face of system failures, concurrent access, or errors. Developers and database administrators can configure transactions and isolation levels based on the requirements of their application to balance performance and data integrity

EXAMPLE:

In this example, we’ll work with a simple database that contains two tables: Customers and Orders. We’ll focus on a scenario where a customer places an order, and we want to ensure that the ACID properties are maintained throughout the process.

Assume we have the following tables:

1. Customers Table:
   • Columns: CustomerID, FirstName, LastName, Email
2. Orders Table:
   • Columns: OrderID, CustomerID (foreign key), OrderDate, TotalAmount

Here’s how the process might work, ensuring ACID properties:

1. Atomicity and Consistency: Let’s say a new customer wants to place an order. We want to ensure that both the customer’s information and the order information are saved together, or not at all. If any part of the transaction fails, no changes should be made.

BEGIN TRANSACTION;

-- Insert customer information
INSERT INTO Customers (FirstName, LastName, Email)
VALUES ('John', 'Doe', '[email protected]');

-- Insert order information (assuming the CustomerID is 1)
INSERT INTO Orders (CustomerID, OrderDate, TotalAmount)
VALUES (1, '2023-08-15', 100.00);

-- Commit the transaction if both inserts are successful
COMMIT;

If either of the INSERT statements fails (due to a constraint violation, for example), the entire transaction will be rolled back, and no changes will be made to the database.

2. Isolation: Suppose another transaction is happening simultaneously, where a different customer is placing an order. We want to ensure that the two transactions do not interfere with each other.

— Transaction 1: Place an order for Customer 1
BEGIN TRANSACTION;

-- Insert order information (assuming the CustomerID is 1)
INSERT INTO Orders (CustomerID, OrderDate, TotalAmount)
VALUES (1, '2023-08-16', 150.00);

COMMIT;

-- Transaction 2: Place an order for Customer 2
BEGIN TRANSACTION;

-- Insert order information (assuming the CustomerID is 2)
INSERT INTO Orders (CustomerID, OrderDate, TotalAmount)
VALUES (2, '2023-08-16', 200.00);

COMMIT;

Even though these two transactions are happening concurrently, the database’s isolation mechanisms ensure that they do not interfere with each other. Each transaction sees its own consistent view of the data, preventing issues like dirty reads or uncommitted changes being visible to other transactions.

3. Durability: Once transactions are committed, the changes made to the database should survive system failures. If a system crash occurs after a commit, the committed changes should not be lost.

— Place an order for Customer 3
BEGIN TRANSACTION;

-- Insert order information (assuming the CustomerID is 3)
INSERT INTO Orders (CustomerID, OrderDate, TotalAmount)
VALUES (3, '2023-08-17', 75.00);

— Commit the transaction
COMMIT;

Example query from todo project:

START TRANSACTION;
INSERT INTO users (name,username,email,password) VALUES ('kelly', 'Doe', '[email protected]','pwd');
SET @user_id = LAST_INSERT_ID();
INSERT INTO todos (title,description,user_id) VALUES ('trasnsaction test', 'Doe', @user_id);
COMMIT

After the transaction is committed, even if the database server crashes, the changes made by this transaction will be saved and persisted, ensuring durability.

In summary, the example demonstrates how SQL transactions and ACID properties work together to ensure data integrity, consistency, isolation, and durability in a database system.