Introduction to Database transaction, Read phenomenom and isolation level using Sequelize ORM

Let start with transaction,

Transaction

Transaction in a database basically is a collection of queries you want run as a unit of work, meaning if its going to succeed, everything succeed together and since each one of your queries is a part of one single work and if at all one fail, the others should also fail and rollback(Reverse the effect of the query), as a database transaction must follow the ACID properties (Atomic, Consistency, Isolation, and Durability).

In this article, we will mostly use Sequelize Orm in our examples.

Now, let's move on to the business. You have been asked to create a peer-to-peer transfer feature in an app, and you need to record a debit transaction on the sender's side and a credit transaction on the receiver's end. It won't make sense if, for some unforeseen reason, we are able to record a debit transaction but not equally able to record a credit transaction, or vice versa. To prevent this scenario, we put both recordings in one transaction.

For example, the normal flow would be as follows, in terms of queries:
Assuming every other validation has been done:

1. Update sender balance
2. Create debit transaction record
3. Update receiver balance
4. Create credit transaction record for receiver

All of these queries make sense to be in a transaction because if any of the above steps fail when a customer attempts to make a transfer, it will affect the integrity of such a payment record. We need them to either fail all together or succeed together.

Sequelize provides us with two types of transactions:

1. Managed Transaction

In a managed transaction, you don't need to worry about handling the rolling back process. Sequelize will automatically do that if an error occurs in your transaction, as shown in the example below:

try {

  const result = await sequelize.transaction(async (t) => {
     // update sender balance
     sender.balance  -= amount;
     await sender.save({transaction: t});
     receiver.balance  += amount;
     await receiver.save({transaction: t});

     await Transaction.create({
      amount: amount,
      type: 'debit'
      userId: sender.id
    }, { transaction: t });

     await Transaction.create({
      amount: amount,
      type: 'credit'
      userId: receiver.id
    }, { transaction: t });

    return { sender, receiver };

  });

  // success block

} catch (error) {

  // error occured in the transaction and all queries effect has been rollback meaning not committed

}

2. UnManaged Transaction

In an unmanaged transaction as it is below, you will be the handling how and when you want to commit/rollback.

const t = await sequelize.transaction();

try {

     // update sender balance
     sender.balance  -= amount;
     await sender.save({transaction: t});
     receiver.balance  += amount;
     await receiver.save({transaction: t});

     await Transaction.create({
      amount: amount,
      type: 'debit'
      userId: sender.id
    }, { transaction: t });

     await Transaction.create({
      amount: amount,
      type: 'credit'
      userId: receiver.id
    }, { transaction: t });

    await t.commit();

    return { sender, receiver };

} catch (error) {

  // error is thrown in you transaction
  // manually rollback.
  await t.rollback();

}

These approaches are common among other ORMs such as Rails' Active Record and Laravel's Query Builder, which have similar syntax and flow.

Even without an ORM and using only SQL(since ORMs implemented the flow using SQL under the hood), the above-mentioned ORM implementations can be followed with similar flow. This involves using keywords like BEGIN/START (depending on the database type), ROLLBACK, and SAVEPOINT. With the SAVEPOINT keyword, you can create a step and rollback to a specific step when necessary, as demonstrated in the example below:

BEGIN TRANSACTION NameOfTransaction;//or
START TRANSACTION; // in case of Mysql
//run you balance update queries
SAVEPOINT balanceUpdate
//create your transactions
SAVEPOINT createTransaction
COMMIT

/*to rollback to the point of balance update*/
ROLLBACK TO balanceUpdate

/*to rollback*/
ROLLBACK

RELEASE to Relese a savepoint
RELEASE SAVEPOINT savepointname

ISOLATION LEVEL & READ PHENOMENOM

The ISOLATION property in the ACID properties of a database transaction means that multiple in-flight transactions can run separately in isolation. The READ PHENOMENON is a side effect of this property, which can cause concerns.

TYPES OF ISOLATION LEVEL

• REPEATABLE READ
• READ COMMITTED
• READ UNCOMMITTED
• SERIALIZABLE

So lets take a look at each of these, one after the other.

REPEATABLE READ: When we have more than one running transaction at the same time, we are guarranteed that only data that has been committed will be read and the value won't change even if we read the same data again in same transaction but other concurrent transaction may add a new row to the data but can't delete/update.

for example

BEGIN TRANSACTION;
SELECT * FROM Accounts;
WAITFOR DELAY '00:03:00'
SELECT * FROM Accounts ;
COMMIT;

in the above, the data returned by the first select will be same as data from second select and if another transaction have added a new row while our current transaction was waiting, the new row will be returned in the second select statement. The data is locked(rows level) for update/delete until the transaction is done.

READ COMMITTED: When we have more than one running transaction at the same time, as it name suggested, every new changes will reflect in the current transaction as long as the other transaction as committed it. Whether the operation is UPDATE, DELETE or CREATE every changes will be seen as long as they are committed.

READ UNCOMMITTED: When we have more than one running transaction at the same time, as it name suggested, every new changes will reflect in the current transaction even if not committed. This bring about phenomenum called DIRTY READ because uncommitted data in out result will leads to having dirty data.

SERIALIZABLE: When we have more than one running transaction at the same time, just like repeatable read but new row cannot be added because as long as the first transaction is running, other transaction won't be able modify or create new rows.
This is the highest level of isolation and also could result to performance issues as transaction may have to wait for each other.

Setting Isolation levels in sequelize is as follows:

const { Transaction } = require('sequelize');

// The following are valid isolation levels:
Transaction.ISOLATION_LEVELS.READ_UNCOMMITTED // "READ UNCOMMITTED"
Transaction.ISOLATION_LEVELS.READ_COMMITTED // "READ COMMITTED"
Transaction.ISOLATION_LEVELS.REPEATABLE_READ  // "REPEATABLE READ"
Transaction.ISOLATION_LEVELS.SERIALIZABLE // "SERIALIZABLE"

and this can be set using the sequelize transaction method first parameter object key isolationLevel as it is below:

const { Transaction } = require('sequelize');

await sequelize.transaction({
  isolationLevel: Transaction.ISOLATION_LEVELS.SERIALIZABLE
}, async (t) => {
  // Your queries
});

These isolation levels comes different side effect called READ PHENOMENUM for the next few minutes, we will be discussion each of the read phenomenum types:

• DIRTY READ
• NON REPEATABLE READ
• PHANTOM READ
• LOST UPDATES

DIRTY READ: This occur when an uncommitted data of a transaction is read by another transaction. The data is dirty because in case where the new changes depend on existing values, we may be getting a redundant data or even already modified or deleted data.
for example, A first transaction is saving sales data to db and another transaction is getting it and rendering it for the administrator, it possible the first transaction later make change to a data already read by second transaction thereby resulting to second transaction sending wrong sales data to the administrator.

NON REPEATABLE READ: This is the opposite of repeatable read because in a transaction, the gotten result by same query may be different there by leading to inconsistent result.

PHANTOM READ: Similar to Non Repeatable read but instead of row data values, number of rows return maybe be different.
This occur when the other transaction as perform CREATE or DELETE operation.

LOST UPDATES: This occur when more than one transaction is trying to update a row at same time. The last transaction to commit will override the first one/ones thereby resulting to situation where previous transaction' updates are lost.

It easy to mix-up or confuse isolation level with read phenomenum so here is a tip that can help.
Think of isolation levels as a possible different approach you could have taken if you are to be the core Database Engineer who is to implement how transaction behave or handle isolation, and think of read phenomenum as the side effect of each decision/approach you later take.

and last check the table below for graphical illustration of isolation levels and their side effect.

In my next couple of articles, i will be writing about locking, scaling techniques, concurrency and concurrency control. Please stay tuned.

References
Sequelize Docs:
https://sequelize.org/docs/v6/other-topics/transactions
Microsoft Sql Server
https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms190805(v=sql.105)?redirectedfrom=MSDN
Nasser Hussein Fundamental of Database Engineering
https://www.udemy.com/course/database-engines-crash-course