@Transactional and @Entity and DynamicTable (DeadLock and LockwaitTimeout) - Yash-777/MyWorld GitHub Wiki

When you annotate a method with @Transactional in a Spring application, it means that the method will be executed within a transactional context. Transactions in the context of a database typically involve operations like opening a transaction, committing changes to the database on success, and rolling back changes on failure.

In summary, the @Transactional annotation in Spring helps manage database transactions. It ensures that changes are either committed or rolled back consistently, and it provides mechanisms to handle concurrency issues, such as deadlocks, in multi-transaction scenarios. The first-level cache plays a crucial role in maintaining the persistent state of entities during these transactions.

Let’s consider an example with two tables: UserTable (a standard entity) and UserDynamic (created dynamically). Assume both tables are involved in multiple transactions.

@Entity // ORM - first-level cache
public class UserTable {
    // Fields, getters, setters, etc.
}

// Assume UserDynamic is created dynamically (not a standard entity) UserDynamic_YYYYMM, UserDynamic_202301, UserDynamic_202302 ...
public class UserDynamic {  // Non-ORM so no cache
    // Fields, getters, setters, etc.
}

In a typical JPA (Java Persistence API) and Hibernate setup, the first-level cache is managed by the EntityManager, and it is used to track the state of entities that are annotated with @Entity. The first-level cache is not directly tied to the use of native SQL queries or the JDBC template.

When you perform DB operations using native SQL and the JDBC template, you are bypassing the Hibernate EntityManager, and Hibernate is not directly aware of the changes made through these operations. The first-level cache is usually associated with the EntityManager's management of entities and the JPA repository methods.

In the context of Hibernate, which is commonly used with Spring for ORM (Object-Relational Mapping), entities marked with @Entity are managed by the Hibernate Session and can participate in the first-level cache.

However, in the case of UserDynamic, which is not annotated with @Entity, it won't be automatically managed by Hibernate.

@Entity UserTable : If you are using native SQL queries or the JDBC template to interact directly with the database, you won't automatically benefit from the first-level cache. In this scenario, you are responsible for managing the state of the entities yourself, and you won't get the automatic caching and consistency features provided by the EntityManager's first-level cache.

Open Transaction:

• A transaction is started when the annotated method begins execution.

Successful Operation:

• If all database operations within the transaction are successful, changes are committed to the database.
• The changes made to entities (like @Entity classes, e.g., UserTable) are stored in the first-level cache.
• The first-level cache is flushed to the database on successful commit.

Failed Operation:

• If any operation within the transaction fails, a rollback is triggered.
• The first-level cache, which holds the persistent state of entities, is reverted to its previous state before the transaction started.

DeadLock and LockwaitTimeout

-- These will normally show you the full sql for the query that is locking.
show full processlist;

-- Check your database transaction isolation level in MySQL: ( REPEATABLE READ is the InnoDB default )
SELECT @@GLOBAL.transaction_isolation, @@session.transaction_isolation, @@transaction_isolation;

SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; -- 134217728

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'; --  By default, it’s set to 50 seconds.
SELECT @@GLOBAL.innodb_lock_wait_timeout, @@session.innodb_lock_wait_timeout, @@innodb_lock_wait_timeout;
-- You can modify the innodb_lock_wait_timeout value globally or per session.
SET GLOBAL  innodb_lock_wait_timeout = 50; -- 300 seconds (5 minutes)
SET SESSION innodb_lock_wait_timeout = 50;
SET         innodb_lock_wait_timeout = 50;

In Java, the @Transactional annotation is typically used to mark a method, class, or interface as transactional, indicating that the method (or methods within the class or interface) should be executed within a transaction context.

Deadlocks can occur when multiple transactions compete for the same resources (e.g., database rows) and end up blocking each other. To mitigate deadlocks, consider the following approaches:

1. Optimistic Locking:

   • Use optimistic locking mechanisms (e.g., version-based locking or timestamp) to prevent concurrent updates from causing deadlocks.
   • In Spring Data JPA, you can achieve this by adding a @Version field to your entity class. This field is automatically incremented during updates.
   • When two transactions try to update the same entity concurrently, the one with the older version will fail, avoiding deadlocks.

2. Transaction Isolation Levels:

   • Configure appropriate transaction isolation levels to control how transactions interact with each other.
   • Adjust Isolation Levels: Change the isolation level of transactions to reduce contention. Common isolation levels include READ_COMMITTED, REPEATABLE_READ, and SERIALIZABLE.
   • Choose the isolation level based on your application’s requirements and the likelihood of concurrent access.

3. Avoid Long Transactions:

   • Keep transactions as short as possible to minimize the chances of deadlocks.
   • Long-running transactions increase the risk of conflicts with other transactions. Transaction Timeout: Set a timeout for transactions to prevent them from holding locks indefinitely.

4. Retry Mechanisms:

   • Implement retry logic for failed transactions.
   • If a transaction encounters a deadlock, retry it after a short delay.
   • Be cautious not to create an infinite loop of retries.

import org.springframework.dao.DeadlockLoserDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;

/*
 * DataSourceConfig file add this to create RetryJdbcTemplate object into spring context.
@Service
public class YourService {
	@Autowired JdbcTemplate      jdbcTemplate;
	@Autowired RetryJdbcTemplate jdbcTemplate;
}
 */
public class RetryJdbcTemplate extends JdbcTemplate {
    private static final int MAX_RETRIES = 3;
    private static final long WAIT_TIME_MS = 100; // Adjust as needed
    
    public RetryJdbcTemplate() {
        super();
    }

    public RetryJdbcTemplate(javax.sql.DataSource dataSource) {
        super(dataSource);
    }

    @Override
    public int update(String sql) {
        return doWithRetry(() -> super.update(sql));
    }

    @Override
    public int update(String sql, Object... args) {
        return doWithRetry(() -> super.update(sql, args));
    }

    private int doWithRetry(Operation operation) {
        int retries = 0;
        while (true) {
            try {
                return operation.execute();
            } catch (DeadlockLoserDataAccessException e) {
            	System.err.println("Retry :"+ e.getLocalizedMessage());
            	//e.printStackTrace();
                if (retries < MAX_RETRIES) {
                    retries++;
                    try {
                        Thread.sleep(WAIT_TIME_MS);
                    } catch (InterruptedException ignored) {
                        Thread.currentThread().interrupt();
                    }
                } else {
                    throw e; // If retries exceeded, rethrow the exception
                }
            }
        }
    }
    private interface Operation {
        int execute();
    }
}