Transaction

Introduction

• It is a sequence of multiple operations performed on a database, and all served as a single logical unit of work — taking place wholly or not at all.

• For example, User A sell books to User B, there are several operations, update the book records, update the user A and user B's pocket money and quantity of book. These operations are in a transaction

Lifecycle

• Active states: It is the first state during the execution of a transaction. A transaction is active as long as its instructions (read or write operations) are performed.

• Partially committed: A change has been executed in this state, but the database has not yet committed the change on disk. In this state, data is stored in the memory buffer, and the buffer is not yet written to disk.

• Committed: In this state, all the transaction updates are permanently stored in the database. Therefore, it is not possible to rollback the transaction after this point.

• Failed: If a transaction fails or has been aborted in the active state or partially committed state, it enters into a failed state.

• Terminated state: This is the last and final transaction state after a committed or aborted state. This marks the end of the database transaction life cycle.

ACID

Atomicity(A)

• Atomicity in terms of a transaction means all or nothing. When a transaction is committed, the database either completes the transaction successfully or rolls it back so that the database returns to its original state.

Consistency(C)

• One of the key advantages of using a transaction is maintaining data integrity, regardless of whether it succeeds or fails. Transactions can only alter affected data in a way that is authorized by the database engine, ensuring that a consistent view of the data is maintained at all times.

Isolation(I)

• With multiple concurrent transactions running at the same time, each transaction should be kept independent without affecting other transactions executing simultaneously

Durability(D)

• Durability means that a successful transaction commit will survive permanently.

Implementation (With Sequalize)

const t = await sequelize.transaction();

try {
  // Then, we do some calls passing this transaction as an option:
  const user = await User.create({
    firstName: 'Bart',
    lastName: 'Simpson'
  }, { transaction: t });

  await user.addSibling({
    firstName: 'Lisa',
    lastName: 'Simpson'
  }, { transaction: t });

  // If the execution reaches this line, no errors were thrown.
  // We commit the transaction.
  await t.commit();

} catch (error) {

  // If the execution reaches this line, an error was thrown.
  // We rollback the transaction.
  await t.rollback();
}

Isolation

Introduction

• Isolation describes how changes applied by concurrent transactions are visible to each other.

• Each isolation level prevents zero or more concurrency side effects on a transaction:

  • Dirty read: A dirty read occurs when a transaction reads data that has not yet been committed. For example, suppose transaction 1 updates a row. Transaction 2 reads the updated row before transaction 1 commits the update. If transaction 1 rolls back the change, transaction 2 will have read data that is considered never to have existed.

  • Non-repeatable read: A non-repeatable read occurs when a transaction reads the same row twice but gets different data each time. For example, suppose transaction 1 reads a row. Transaction 2 updates or deletes that row and commits the update or delete. If transaction 1 rereads the row, it retrieves different row values or discovers that the row has been deleted.

  • Phantom read: A phantom is a row that matches the search criteria but is not initially seen. For example, suppose transaction 1 reads a set of rows that satisfy some search criteria. Transaction 2 generates a new row (through either an update or an insert) that matches the search criteria for transaction 1. If transaction 1 re-executes the statement that reads the rows, it gets a different set of rows.

Read Un-commit

• Transactions are not isolated from each other.

Read Committed

• Data modification can only be read after the transaction is committed.

• The transaction waits until rows write-locked by other transactions are unlocked; this prevents it from reading any "dirty" data

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Start transaction 1
BEGIN TRANSACTION;

-- Read data
SELECT * FROM Products;

-- Wait for a few seconds (simulate concurrent transaction)

-- Start transaction 2
BEGIN TRANSACTION;

-- Modify data
UPDATE Products SET ProductName = 'New Name' WHERE ProductID = 1;

-- Commit transaction 2
COMMIT;

-- Read data again within transaction 1
SELECT * FROM Products;

-- Commit or rollback transaction 1
COMMIT;

• Transaction 1 reads the data from the "Products" table, and while it is still active, transaction 2 modifies a record. After transaction 2 commit, The READ COMMITTED isolation level allows transaction 1 to see the changes made by transaction 2 when it reads the data again.

Repeatable Read

• Data read during the transaction stays the same as the transaction starts.

• Prevents dirty, and non-repeatable reads. So we are not affected by uncommitted changes in concurrent transactions

• Also, when we re-query for a row, we don't get a different result. However, in the re-execution of range-queries, we may get newly added or removed rows.

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Start transaction 1
BEGIN TRANSACTION;

-- Read data
SELECT * FROM Products;

-- Wait for a few seconds (simulate concurrent transaction)

-- Start transaction 2
BEGIN TRANSACTION;

-- Modify data
UPDATE Products SET ProductName = 'New Name' WHERE ProductID = 1;

-- Commit transaction 2
COMMIT;

-- Read data again within transaction 1
SELECT * FROM Products;

-- Commit or rollback transaction 1
COMMIT;

• transaction 1 reads the data from the "Products" table, and while it is still active, transaction 2 modifies a record. The REPEATABLE READ isolation level ensures that transaction 1 sees the same data , even if transaction 2 made changes. The second read within transaction 1 will still show the original data

Serializable

• It prevents all mentioned concurrency side effects, but can lead to the lowest concurrent access rate because it executes concurrent calls sequentially.

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Start transaction 1
BEGIN TRANSACTION;

-- Read data
SELECT * FROM Products;

-- Wait for a few seconds (simulate concurrent transaction)

-- Start transaction 2
BEGIN TRANSACTION;

-- Modify data
INSERT INTO Products (id, "ProductName")  Values (3, 'Product3');

-- Commit transaction 2
COMMIT;

-- Read data again within transaction 1
SELECT * FROM Products;

-- Commit or rollback transaction 1
COMMIT;

• transaction 1 reads the data from the "Products" table, and while it is still active, transaction 2 insert s a record. The SERIALIZABLE isolation level ensures that transaction 1 sees the same snapshot of the data it read initially, even if transaction 2 made changes. The second read within transaction 1 will still show the original data.

References

Transactions | Sequelize

What is a database transaction?Fauna