Database Isolation Level with PostgreSQL and Spring Boot

In database management systems, isolation refers to the degree to which transactions are isolated from the effects of other concurrently running transactions.

Depending on the needs of your application, you can query a PostgreSQL or MySQL database using different isolation levels in a Spring Boot application.

You may accomplish this by combining the @Transactional annotation with the Isolation enum, which lets us specify the desired isolation level for your transaction.

Isolation Level in Database (PostgreSQL)

In this blog, we will see what all types of isolation we have and how we can achieve that using PostgreSQL query and Spring Boot @Transactional annotation.

Database Isolation Levels

• Read Uncommitted: A transaction can access uncommitted data from another transaction at this isolation level, which may result in dirty reads. Consider a transaction that alters a database record but fails to commit or roll back the transaction. Another transaction reading the same record may retrieve uncommitted data, resulting in inconsistency.
• Read Committed: A transaction at this isolation level can only read committed data from other transactions. Consider a transaction that modifies a database record and then commits the transaction. A subsequent transaction reading the same record can only retrieve the committed data.
• Repeatable Read: A transaction in this isolation level can only read committed data from other transactions and will not see any modifications made by other transactions until it is committed. Consider a transaction that reads a collection of records from a database. Even if another transaction modifies or deletes the records, the first transaction will continue to see the original data until it commits.
• Serializable: Transactions are executed at this isolation level in such a way that they appear to be executed serially, one after the other. Consider two concurrent transactions in which one transaction modifies a record and the other transaction deletes the same record. The transactions are processed serially at this isolation level, and one transaction is blocked until the next transaction is completed.

Higher levels of isolation provide more consistency and reliability, but they may also produce more conflicts between transactions, resulting in decreased concurrency and performance. The isolation level selected is determined by the application’s requirements and the trade-off between consistency and performance.

Note: The default isolation level in PostgreSQL is READ COMMITTED .

Configure the Isolation level in PostgreSQL

In PostgreSQL, you can configure the isolation level using the SET TRANSACTION command, which allows you to set the isolation level for the current transaction. The steps are as follows:

Use a client tool like pgAdmin to connect to your PostgreSQL database.

Begin a transaction by typing BEGIN or START TRANSACTION.

To provide the appropriate isolation level, use the SET TRANSACTION command. To change the isolation level to read committed, for example, execute the following command:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

READ COMMITTED can be replaced with any other isolation level, such as READ UNCOMMITTED, REPEATABLE READ, or SERIALIZABLE.

Within the transaction, carry out your database activities.

When you’re finished with your activities, use the COMMIT or ROLLBACK commands to commit or roll back the transaction.

Note that the isolation level can also be adjusted globally for the entire Postgresql server using postgresql.conf file. However, setting the isolation level at the transaction level provides more fine-grained control over the level of consistency and concurrency in your application.
To change the isolation level globally open postgresql.conf file and edit the parameter transaction_isolation. This parameter can have the following values [serializable, repeatable read, read committed, read uncommitted] . For more check this link.

Configure the isolation level in Spring Boot

The steps are as follows:

Use the @Transactional annotation to annotate the method that executes the database query.

Set the isolation attribute of the @Transactional annotation to the desired amount of isolation. To set the isolation level to READ COMMITTED, for example, use the following code:

@Transactional(isolation = Isolation.READ_COMMITTED)
public void performDatabaseQuery() {
    // Perform your database query here
}

READ_COMMITTED can be replaced with any other isolation level, such as READ_UNCOMMITTED, REPEATABLE_READ, or SERIALIZABLE.

Set the default transaction isolation level in your Spring Boot application’s DataSource bean. You can accomplish this by including the following code in your application’s configuration file:

@Bean
public DataSource dataSource() {
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName("org.postgresql.Driver");
    dataSource.setUrl("jdbc:postgresql://localhost:5432/mydatabase");
    dataSource.setUsername("myusername");
    dataSource.setPassword("mypassword");
    dataSource.setDefaultTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); // Set default isolation level
    return dataSource;
}

Again, you can replace TRANSACTION_READ_COMMITTED with any other isolation level.

If you enjoyed reading this, don’t forget the applause. 👏

I hope I was able to explain what is Isolation level in Database and how to query with different types of isolation.

Thank you.