ACID, Isolation level, @Transactional
1. In general, Spring does not create proxy classes by default for stereotype-annotated classes (such as those annotated with @Component, @Service, @Repository, etc.). Spring only creates a proxy for a class when it needs to apply some kind of aspect-oriented programming (AOP), typically for features like transaction management, security, or custom-defined interceptors.
Here are the conditions under which Spring creates a proxy:
AOP Requirements: When there is a specific aspect (e.g.,
@Transactional,@Cacheable, or@Async), Spring uses a proxy to implement that aspect without modifying the class itself.Interfaces: If a class implements an interface and Spring needs to create a proxy (e.g., for
@Transactional), Spring will use JDK dynamic proxies by default, which only proxy interfaces. For classes without an interface, Spring uses CGLIB to create a subclass proxy.
In the example you provided, B is annotated with @Component, but it doesn’t have any aspect requirements. So Spring will treat it as a regular bean, and no proxy will be created unless you add annotations or configurations that require one.
package com.appsdeveloperblog.ws.demo_transactional;
import org.springframework.stereotype.Component;
@Component
public class B {
public void m2() {
System.out.println("m2 in B class");
}
}
package com.appsdeveloperblog.ws.demo_transactional;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
@Component
@RequiredArgsConstructor
public class A {
private final B b;
@Transactional(propagation = Propagation.REQUIRES_NEW)
public void m1() {
System.out.println(b.getClass().getName());
System.out.println(this.getClass());
}
}
package com.appsdeveloperblog.ws.demo_transactional;
import lombok.RequiredArgsConstructor;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.transaction.annotation.Transactional;
@SpringBootApplication
@RequiredArgsConstructor
public class DemoTransactionalApplication implements CommandLineRunner {
private final A a;
public static void main(String[] args) {
SpringApplication.run(DemoTransactionalApplication.class, args);
}
@Override
public void run(String... args) throws Exception {
a.m1();
}
}
Now if put @Transaction:
package com.appsdeveloperblog.ws.demo_transactional;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
@Component
public class B {
@Transactional
public void m2() {
System.out.println("m2 in B class");
}
}
com.appsdeveloperblog.ws.demo_transactional.B$$SpringCGLIB$$0
class com.appsdeveloperblog.ws.demo_transactional.A
This is how @Transactional annotation works under the hood:
package com.appsdeveloperblog.ws.demo_transactional.service;
import com.appsdeveloperblog.ws.demo_transactional.entity.Account;
import com.appsdeveloperblog.ws.demo_transactional.repo.AccountRepository;
import jakarta.persistence.EntityManager;
import jakarta.persistence.EntityManagerFactory;
import jakarta.persistence.EntityTransaction;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@Service
@RequiredArgsConstructor
public class AccountService {
private final AccountRepository repository;
private final EntityManagerFactory entityManagerFactory;
public void transferProxy(Long fromId, Long toId, Integer amount) {
EntityManager em = entityManagerFactory.createEntityManager();
EntityTransaction transaction = em.getTransaction();
try {
transaction.begin();
Account from = em.find(Account.class, 1L);
Account to = em.find(Account.class, 2L);
from.setBalance(from.getBalance() - amount);
if(true) throw new RuntimeException("Error");
to.setBalance(to.getBalance() + amount);
} catch (RuntimeException e) {
transaction.rollback();
} finally {
transaction.commit();
em.close();
}
}
@Transactional
public void transfer(Account from, Account to, Integer amount) {
if (from.getBalance() < amount) {
throw new RuntimeException("Amount is not sufficient");
}
from.setBalance(from.getBalance() - amount);
to.setBalance(to.getBalance() + amount);
if (true) {
throw new RuntimeException("Error");
}
}
}
*** In RuntimeExceptions and Error it rolls back, but in Exception it does not.
--- *** Isolation Levels
MySQL ucun default isolation level Repeatable Read sayilir.
Bu zaman eger hansisa bir row ucun data update edirikse hemin row-nu lock-layir.
Dirty Read
Ferz edekki, Transaction1 report cixarmaq isteyir ve satilan mallarin umumi odeniwini cicarir, bu zaman Transaction2 table-da update edir ve tutalim ki transaction kecmir veya xeta baw verir veya imtina edir, bu zaman Transaction1-in hesabati yalniw olacaq.
Lakin bu o demek deyilki Dirty Read pis bir weydi. Mes: sosial webekede like sayi hazirki an ucun o qederde vacib bir wey deyil.
Non-repeatable Read
Bu o demekdir ki, eyni query ferqli netice gostere biler.
Ferz edekki Transaction1 hesabah aparmaq ucun query atir ve x bir deyer alir, bu zaman Transaction2 hemin table-da update edir, bu zaman Transaction1 yene eyni query generate edir ve ferqli bir netice alir.
Phantom Read
Ferz edek ki, Transaction1 hesabat cixarmaq isteyir ve x bir edek alir, bu zaman Transaction2 hemin table-a insert edir, bir nece saniye sonra yeniden hesabah cixarmaq isteyende ferqli bir netice alir.
Phantom reads and non-repeatable reads are both types of isolation-level issues that can occur in transactional databases. They affect the consistency of data when transactions are running concurrently. Here’s a breakdown of each:
### 1. **Non-Repeatable Read**
- **Definition**: A non-repeatable read occurs when a transaction reads the same row twice but gets different values because another transaction modified that row in between reads.
- **Example**: Suppose Transaction A reads the value of a row, then Transaction B updates that row, and when Transaction A reads it again, the value has changed. This inconsistency can lead to unexpected results in Transaction A.
- **Isolation Level**: This is prevented by the **REPEATABLE READ** isolation level, which ensures that once a transaction reads a row, that row cannot be modified by other transactions until the transaction completes.
### 2. **Phantom Read**
- **Definition**: A phantom read happens when a transaction reads a set of rows that satisfy a condition, but another transaction inserts, updates, or deletes rows that would also satisfy that condition, causing the initial transaction to get a different result set if it re-runs the query.
- **Example**: Transaction A reads all rows where `status = 'active'`. Meanwhile, Transaction B inserts a new row with `status = 'active'`. If Transaction A re-reads rows with `status = 'active'`, it will see an additional "phantom" row that wasn’t there in the first read.
- **Isolation Level**: This is prevented by the **SERIALIZABLE** isolation level, which ensures that no other transactions can modify or insert rows that would affect the initial transaction's result set.
### **Key Difference**
- **Scope**: Non-repeatable reads affect individual rows, while phantom reads affect the entire set of rows returned by a query.
- **Cause**: Non-repeatable reads occur due to **updates** or **deletes** on existing rows, while phantom reads are due to **inserts** or **deletes** that affect the result set of a query.
### Isolation Level Summary
- **Read Committed**: Prevents dirty reads but allows non-repeatable and phantom reads.
- **Repeatable Read**: Prevents dirty and non-repeatable reads but allows phantom reads.
- **Serializable**: Prevents dirty, non-repeatable, and phantom reads by locking the whole data set.
******** Extremely important part
The behavior you're observing stems from how **transaction management and SQL execution timing** differ between the MySQL CLI and your Spring Boot application using JPA/Hibernate. Here's why your second CLI session waits in the CLI scenario but doesn't when interacting with the Spring Boot application.
---
### **Scenario Breakdown**
#### **1. Two MySQL CLI Sessions**
**Session 1:**
```sql
START TRANSACTION;
UPDATE account SET balance = 0 WHERE id = 1;
-- Session holds an exclusive lock on the row with id = 1
```
**Session 2:**
```sql
START TRANSACTION;
UPDATE account SET balance = 800 WHERE id = 1;
-- This session waits because Session 1 holds a lock on the same row
```
#### **2. Spring Boot Application and MySQL CLI Session**
**Spring Boot Application:**
```java
@Transactional
public void transfer() throws Exception {
Account parvin = repository.findById(1L).get();
Account rovshan = repository.findById(2L).get();
parvin.setBalance(parvin.getBalance() - 50);
Thread.sleep(30000); // Pauses for 30 seconds
rovshan.setBalance(rovshan.getBalance() + 50);
}
```
**MySQL CLI Session:**
```sql
START TRANSACTION;
UPDATE account SET balance = 0 WHERE id = 1;
-- Session holds an exclusive lock on the row with id = 1
```
---
### **Why Does the Second CLI Session Wait?**
- **Immediate Execution and Locking:**
- In the MySQL CLI, when you execute `UPDATE account SET balance = ... WHERE id = 1;`, the statement is sent immediately to the database.
- The database acquires an **exclusive lock** on the row with `id = 1` for the duration of the transaction.
- **Conflict Detection:**
- The second session attempts to update the same row, but it **cannot acquire the lock** because the first session already holds it.
- Therefore, it **waits** until the first session commits or rolls back.
---
### **Why Doesn't the Spring Boot Transaction Cause the CLI Session to Wait?**
- **Deferred Execution in Hibernate/JPA:**
- When you modify an entity in a Spring Boot application using JPA/Hibernate, the change is made **in memory** within the persistence context (first-level cache).
- **SQL statements are not sent to the database immediately**; they are deferred until the persistence context is flushed.
- **No Immediate Lock Acquisition:**
- Since the `UPDATE` statement hasn't been sent to the database yet, **no lock is acquired** on the row with `id = 1`.
- During the `Thread.sleep(30000);`, the database is unaware of any pending changes, so the CLI session can proceed to acquire the lock.
- **Resulting Behavior:**
- The Spring Boot transaction doesn't prevent other transactions from modifying the same data during its sleep period.
- When it finally flushes and commits, it may overwrite changes made by other transactions or encounter conflicts.
---
### **How to Align Spring Boot Behavior with CLI Sessions**
To make your Spring Boot transaction behave like the CLI session (i.e., to acquire locks immediately), you need to **ensure that SQL statements are sent to the database promptly**. Here's how:
#### **1. Manually Flush the Persistence Context**
```java
@Autowired
private EntityManager entityManager;
@Transactional
public void transfer() throws Exception {
Account parvin = repository.findById(1L).get();
parvin.setBalance(parvin.getBalance() - 50);
// Force the SQL UPDATE to be executed now
entityManager.flush();
Thread.sleep(30000);
Account rovshan = repository.findById(2L).get();
rovshan.setBalance(rovshan.getBalance() + 50);
entityManager.flush();
}
```
- **Explanation:**
- `entityManager.flush();` sends all pending SQL statements to the database.
- The database then acquires the necessary locks.
- Other transactions attempting to modify the same data will now wait.
#### **2. Use Pessimistic Locking**
Modify your repository to lock the entities when fetching them:
```java
public interface AccountRepository extends JpaRepository<Account, Long> {
@Lock(LockModeType.PESSIMISTIC_WRITE)
@Query("SELECT a FROM Account a WHERE a.id = :id")
Account findByIdForUpdate(@Param("id") Long id);
}
```
Update your service method:
```java
@Transactional
public void transfer() throws Exception {
Account parvin = repository.findByIdForUpdate(1L);
Account rovshan = repository.findByIdForUpdate(2L);
parvin.setBalance(parvin.getBalance() - 50);
rovshan.setBalance(rovshan.getBalance() + 50);
// Locks are held until the transaction commits
}
```
- **Explanation:**
- `@Lock(LockModeType.PESSIMISTIC_WRITE)` acquires a database lock when the entity is read.
- Other transactions attempting to read or write to the same row will wait.
- Ensures data consistency and prevents concurrent modifications.
#### **3. Use `saveAndFlush` Method**
Force immediate write to the database:
```java
@Transactional
public void transfer() throws Exception {
Account parvin = repository.findById(1L).get();
parvin.setBalance(parvin.getBalance() - 50);
repository.saveAndFlush(parvin); // Flushes immediately
Thread.sleep(30000);
Account rovshan = repository.findById(2L).get();
rovshan.setBalance(rovshan.getBalance() + 50);
repository.saveAndFlush(rovshan);
}
```
---
### **Understanding the Difference in Locking Mechanisms**
- **CLI Sessions:**
- Statements are executed immediately.
- Locks are acquired as soon as the statement runs.
- Transactions are straightforward; there's no intermediate caching.
- **Spring Boot with JPA/Hibernate:**
- Uses a persistence context that caches changes.
- SQL statements are deferred until flush or commit time.
- No locks are acquired until the SQL statements are sent to the database.
---
### **Additional Points**
- **Transaction Isolation Levels:**
- **Repeatable Read** isolation level ensures consistent reads within a transaction but doesn't prevent other transactions from modifying the data.
- **Serializable** isolation level can prevent concurrent writes but may lead to decreased performance due to increased locking.
- **Optimistic vs. Pessimistic Locking:**
- **Optimistic Locking** assumes conflicts are rare and checks for conflicts at commit time.
- **Pessimistic Locking** immediately locks the data to prevent conflicts.
- **Flush Modes:**
- **AUTO (default):** Persistence context is flushed before query execution and at transaction commit.
- **COMMIT:** Flush occurs only at transaction commit.
---
### **Summary**
- **CLI Sessions Acquire Locks Immediately:**
- Direct execution of SQL statements leads to immediate lock acquisition.
- Concurrent transactions wait if they try to access the same data.
- **Spring Boot Defers SQL Execution:**
- Changes are made in-memory and flushed at commit time.
- No locks are held until the flush occurs.
- Other transactions can modify the data in the meantime.
- **To Synchronize Behavior:**
- **Force Flushing:** Use `entityManager.flush()` or `saveAndFlush()` to send SQL statements to the database immediately.
- **Use Locking:** Apply pessimistic locking to prevent other transactions from accessing the data.
---
### **Let Me Know If You Need Further Assistance**
- **Implementing the Solutions:** If you need help modifying your Spring Boot application to implement these changes, feel free to ask.
- **Understanding Transaction Management:** I can provide more details on how JPA/Hibernate handles transactions and flushing.
- **Exploring Locking Strategies:** If you're interested in the pros and cons of optimistic vs. pessimistic locking, I can elaborate.
By adjusting your Spring Boot application to flush changes immediately or to use locking mechanisms, you can ensure that it behaves similarly to your CLI sessions, preventing concurrent modifications and ensuring data consistency.
*** But when we do in this way:
private final EntityManager entityManager;
// @Transactional(isolation = Isolation.SERIALIZABLE)
@Transactional
public void transfer() throws Exception {
Account parvin = repository.findById(1L).get();
parvin.setBalance(parvin.getBalance() - 50);
// Force the SQL UPDATE to be executed now
entityManager.flush();
Thread.sleep(30000);
Account rovshan = repository.findById(2L).get();
rovshan.setBalance(rovshan.getBalance() + 50);
entityManager.flush();
// Account parvin = repository.findById(1L).get();
// Account rovshan = repository.findById(2L).get();
// parvin.setBalance(parvin.getBalance() - 50);
//// Thread.sleep(30000);
// rovshan.setBalance(rovshan.getBalance() + 50);
}
**********
Certainly! Let's work through how to simulate a **phantom read** in MySQL using two CLI sessions. I'll explain why you might not be seeing it and provide detailed steps to reproduce it.
---
### **Understanding Phantom Reads**
A **phantom read** occurs when:
- **Transaction T1** reads a set of rows matching a condition.
- **Transaction T2** inserts (or deletes) new rows that match the same condition.
- **Transaction T1** re-reads the data and finds new (or missing) rows—these are the "phantoms."
**Phantom reads** are possible under the **Read Committed** and **Repeatable Read** isolation levels, depending on the database system. However, MySQL's default **Repeatable Read** isolation level uses **next-key locking** and **MVCC** (Multi-Version Concurrency Control) to prevent phantom reads.
---
### **Why You're Not Seeing Phantom Reads in MySQL**
- **MySQL's Default Behavior**: By default, MySQL's **Repeatable Read** isolation level prevents phantom reads using **gap locks** and **next-key locks**.
- **Need to Use Read Committed Isolation Level**: To observe phantom reads in MySQL, you need to set the isolation level to **Read Committed**.
- **Table Type Matters**: Make sure you're using the **InnoDB** storage engine, as MyISAM doesn't support transactions.
---
### **Steps to Simulate Phantom Reads in MySQL**
#### **Preparation**
1. **Ensure InnoDB Engine is Used**
```sql
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
balance INT,
name VARCHAR(100)
) ENGINE=InnoDB;
INSERT INTO account (balance, name) VALUES (1000, 'Parvin'), (1000, 'Rovshan');
```
2. **Open Two MySQL CLI Sessions**
- **Session 1**: We'll call this **Transaction T1**.
- **Session 2**: We'll call this **Transaction T2**.
#### **Simulating Phantom Read**
##### **Session 1 (Transaction T1)**
1. **Set Isolation Level to Read Committed**
```sql
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
```
2. **Start Transaction**
```sql
START TRANSACTION;
```
3. **Execute Initial Query**
```sql
SELECT * FROM account WHERE balance >= 1000;
```
- **Expected Result**:
```
+----+---------+---------+
| id | balance | name |
+----+---------+---------+
| 1 | 1000 | Parvin |
| 2 | 1000 | Rovshan |
+----+---------+---------+
```
##### **Session 2 (Transaction T2)**
1. **Set Isolation Level to Read Committed**
```sql
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
```
2. **Start Transaction**
```sql
START TRANSACTION;
```
3. **Insert a New Row Matching the Condition**
```sql
INSERT INTO account (balance, name) VALUES (1500, 'Aydin');
```
4. **Commit Transaction**
```sql
COMMIT;
```
##### **Back to Session 1 (Transaction T1)**
1. **Re-Execute the Query**
```sql
SELECT * FROM account WHERE balance >= 1000;
```
- **Expected Result**:
```
+----+---------+---------+
| id | balance | name |
+----+---------+---------+
| 1 | 1000 | Parvin |
| 2 | 1000 | Rovshan |
| 3 | 1500 | Aydin |
+----+---------+---------+
```
- **Observation**: The new row ('Aydin') appears in the result set, which wasn't there in the first query—this is the **phantom read**.
2. **Commit or Rollback Transaction**
```sql
COMMIT;
```
---
### **Explanation**
- **Transaction T1** starts and reads the set of rows where `balance >= 1000`.
- **Transaction T2** inserts a new row with `balance = 1500`, which satisfies the condition.
- **Transaction T2** commits, making the new row visible to other transactions.
- When **Transaction T1** re-executes the query, it sees the new row, demonstrating a phantom read.
---
### **Why It Didn't Work Before**
- If you had both sessions using the **Repeatable Read** isolation level (MySQL's default), **Transaction T1** would not see the new row inserted by **Transaction T2** until **Transaction T1** ends. This is because **Repeatable Read** in MySQL prevents phantom reads using gap locks.
---
### **Key Points**
- **Isolation Level Matters**: To observe phantom reads in MySQL, you need to set the isolation level to **Read Committed**.
- **Repeatable Read Prevents Phantom Reads in MySQL**: Due to MySQL's implementation of **Repeatable Read**, phantom reads are prevented using next-key locking.
- **Ensure Transactions Are Open**: Both sessions must have active transactions to observe the effects.
---
### **Additional Example: Phantom Reads with Updates**
You can also simulate phantom reads using `UPDATE` statements.
#### **Session 1 (Transaction T1)**
```sql
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT COUNT(*) FROM account WHERE balance >= 1000;
```
- **Initial Count**: 2
#### **Session 2 (Transaction T2)**
```sql
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
INSERT INTO account (balance, name) VALUES (1200, 'Leyla');
COMMIT;
```
#### **Back to Session 1 (Transaction T1)**
```sql
SELECT COUNT(*) FROM account WHERE balance >= 1000;
```
- **New Count**: 3 (Phantom row 'Leyla' appears)
---
### **Preventing Phantom Reads**
If you want to prevent phantom reads, you can:
- **Use Repeatable Read Isolation Level**
```sql
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
```
- **Use Serializable Isolation Level**
```sql
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
```
- This level is the strictest and can impact performance due to increased locking.
---
### **Final Notes**
- **Consistency is Key**: Always ensure both sessions are using the intended isolation level.
- **Testing Different Scenarios**: Try inserting, updating, and deleting rows in **Transaction T2** to see how it affects **Transaction T1** under different isolation levels.
- **Understanding MySQL's Behavior**: MySQL's handling of isolation levels may differ from other databases due to its use of MVCC and locking strategies.
---
### **Let Me Know If You Need Further Assistance**
Feel free to ask if you have any questions or need clarification on any of the steps. I'm here to help you understand and observe phantom reads in your MySQL environment.
****
in application.yaml file when we include this:
spring:
aop:
proxy-target-class: false
it switches to jdk proxy
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
package com.appsdeveloperblog.ws.demotransaction2.service;
import com.appsdeveloperblog.ws.demotransaction2.entity.Account;
import com.appsdeveloperblog.ws.demotransaction2.repository.AccountRepository;
import lombok.RequiredArgsConstructor;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@Slf4j
@Service
@RequiredArgsConstructor
public class AccountService {
private final AccountRepository repository;
@SneakyThrows
public void transfer() {
log.info("Starting transfer on thread {}", Thread.currentThread().getId());
log.info("Getting account on thread {} with id 1 START", Thread.currentThread().getId());
Account from = repository.findById(1L).get();
log.info("Getting account on thread {} with id 1 DONE. {}", Thread.currentThread().getId(), from);
Thread.sleep(10000);
log.info("Getting account on thread {} with id 2 START", Thread.currentThread().getId());
Account to = repository.findById(2L).get();
log.info("Getting account on thread {} with id 2 DONE. {}", Thread.currentThread().getId(), to);
log.info("Balances on accounts on thread {} update START", Thread.currentThread().getId());
from.setBalance(from.getBalance() - 30);
to.setBalance(to.getBalance() + 30);
log.info("Balances on accounts on thread {} update END", Thread.currentThread().getId());
log.info("Trying to save account on thread {} with id 1 START", Thread.currentThread().getId());
Account saveFrom = repository.save(from);
log.info("Account on thread {} with id 1 DONE {}", Thread.currentThread().getId(), saveFrom);
log.info("Trying to save account on thread {} with id 2 START", Thread.currentThread().getId());
Account saveTo = repository.save(to);
log.info("Account on thread {} with id 2 DONE {}", Thread.currentThread().getId(), saveTo);
}
}
2024-11-10T15:44:42.033+04:00 INFO 81270 --- [nio-8080-exec-2] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring DispatcherServlet 'dispatcherServlet'
2024-11-10T15:44:42.033+04:00 INFO 81270 --- [nio-8080-exec-2] o.s.web.servlet.DispatcherServlet : Initializing Servlet 'dispatcherServlet'
2024-11-10T15:44:42.034+04:00 INFO 81270 --- [nio-8080-exec-2] o.s.web.servlet.DispatcherServlet : Completed initialization in 1 ms
2024-11-10T15:44:42.046+04:00 INFO 81270 --- [nio-8080-exec-2] c.a.w.d.service.AccountService : Starting transfer on thread 47
2024-11-10T15:44:42.046+04:00 INFO 81270 --- [nio-8080-exec-2] c.a.w.d.service.AccountService : Getting account on thread 47 with id 1 START
2024-11-10T15:44:42.068+04:00 DEBUG 81270 --- [nio-8080-exec-2] org.hibernate.SQL : select a1_0.id,a1_0.balance,a1_0.name from account a1_0 where a1_0.id=?
Hibernate: select a1_0.id,a1_0.balance,a1_0.name from account a1_0 where a1_0.id=?
2024-11-10T15:44:42.076+04:00 INFO 81270 --- [nio-8080-exec-2] c.a.w.d.service.AccountService : Getting account on thread 47 with id 1 DONE. Account(id=1, name=A, balance=100)
2024-11-10T15:44:44.238+04:00 INFO 81270 --- [nio-8080-exec-4] c.a.w.d.service.AccountService : Starting transfer on thread 49
2024-11-10T15:44:44.238+04:00 INFO 81270 --- [nio-8080-exec-4] c.a.w.d.service.AccountService : Getting account on thread 49 with id 1 START
2024-11-10T15:44:44.241+04:00 DEBUG 81270 --- [nio-8080-exec-4] org.hibernate.SQL : select a1_0.id,a1_0.balance,a1_0.name from account a1_0 where a1_0.id=?
Hibernate: select a1_0.id,a1_0.balance,a1_0.name from account a1_0 where a1_0.id=?
2024-11-10T15:44:44.246+04:00 INFO 81270 --- [nio-8080-exec-4] c.a.w.d.service.AccountService : Getting account on thread 49 with id 1 DONE. Account(id=1, name=A, balance=100)
2024-11-10T15:44:52.078+04:00 INFO 81270 --- [nio-8080-exec-2] c.a.w.d.service.AccountService : Getting account on thread 47 with id 2 START
2024-11-10T15:44:52.085+04:00 DEBUG 81270 --- [nio-8080-exec-2] org.hibernate.SQL : select a1_0.id,a1_0.balance,a1_0.name from account a1_0 where a1_0.id=?
Hibernate: select a1_0.id,a1_0.balance,a1_0.name from account a1_0 where a1_0.id=?
2024-11-10T15:44:52.091+04:00 INFO 81270 --- [nio-8080-exec-2] c.a.w.d.service.AccountService : Getting account on thread 47 with id 2 DONE. Account(id=2, name=B, balance=100)
2024-11-10T15:44:52.092+04:00 INFO 81270 --- [nio-8080-exec-2] c.a.w.d.service.AccountService : Balances on accounts on thread 47 update START
2024-11-10T15:44:52.092+04:00 INFO 81270 --- [nio-8080-exec-2] c.a.w.d.service.AccountService : Balances on accounts on thread 47 update END
2024-11-10T15:44:52.093+04:00 INFO 81270 --- [nio-8080-exec-2] c.a.w.d.service.AccountService : Trying to save account on thread 47 with id 1 START
2024-11-10T15:44:52.100+04:00 DEBUG 81270 --- [nio-8080-exec-2] org.hibernate.SQL : select a1_0.id,a1_0.balance,a1_0.name from account a1_0 where a1_0.id=?
Hibernate: select a1_0.id,a1_0.balance,a1_0.name from account a1_0 where a1_0.id=?
2024-11-10T15:44:52.115+04:00 DEBUG 81270 --- [nio-8080-exec-2] org.hibernate.SQL : update account set balance=?,name=? where id=?
Hibernate: update account set balance=?,name=? where id=?
2024-11-10T15:44:52.129+04:00 INFO 81270 --- [nio-8080-exec-2] c.a.w.d.service.AccountService : Account on thread 47 with id 1 DONE Account(id=1, name=A, balance=70)
2024-11-10T15:44:52.129+04:00 INFO 81270 --- [nio-8080-exec-2] c.a.w.d.service.AccountService : Trying to save account on thread 47 with id 2 START
2024-11-10T15:44:52.130+04:00 DEBUG 81270 --- [nio-8080-exec-2] org.hibernate.SQL : select a1_0.id,a1_0.balance,a1_0.name from account a1_0 where a1_0.id=?
Hibernate: select a1_0.id,a1_0.balance,a1_0.name from account a1_0 where a1_0.id=?
2024-11-10T15:44:52.131+04:00 DEBUG 81270 --- [nio-8080-exec-2] org.hibernate.SQL : update account set balance=?,name=? where id=?
Hibernate: update account set balance=?,name=? where id=?
2024-11-10T15:44:52.137+04:00 INFO 81270 --- [nio-8080-exec-2] c.a.w.d.service.AccountService : Account on thread 47 with id 2 DONE Account(id=2, name=B, balance=130)
2024-11-10T15:44:54.249+04:00 INFO 81270 --- [nio-8080-exec-4] c.a.w.d.service.AccountService : Getting account on thread 49 with id 2 START
2024-11-10T15:44:54.256+04:00 DEBUG 81270 --- [nio-8080-exec-4] org.hibernate.SQL : select a1_0.id,a1_0.balance,a1_0.name from account a1_0 where a1_0.id=?
Hibernate: select a1_0.id,a1_0.balance,a1_0.name from account a1_0 where a1_0.id=?
2024-11-10T15:44:54.263+04:00 INFO 81270 --- [nio-8080-exec-4] c.a.w.d.service.AccountService : Getting account on thread 49 with id 2 DONE. Account(id=2, name=B, balance=130)
2024-11-10T15:44:54.263+04:00 INFO 81270 --- [nio-8080-exec-4] c.a.w.d.service.AccountService : Balances on accounts on thread 49 update START
2024-11-10T15:44:54.264+04:00 INFO 81270 --- [nio-8080-exec-4] c.a.w.d.service.AccountService : Balances on accounts on thread 49 update END
2024-11-10T15:44:54.264+04:00 INFO 81270 --- [nio-8080-exec-4] c.a.w.d.service.AccountService : Trying to save account on thread 49 with id 1 START
2024-11-10T15:44:54.267+04:00 DEBUG 81270 --- [nio-8080-exec-4] org.hibernate.SQL : select a1_0.id,a1_0.balance,a1_0.name from account a1_0 where a1_0.id=?
Hibernate: select a1_0.id,a1_0.balance,a1_0.name from account a1_0 where a1_0.id=?
2024-11-10T15:44:54.271+04:00 INFO 81270 --- [nio-8080-exec-4] c.a.w.d.service.AccountService : Account on thread 49 with id 1 DONE Account(id=1, name=A, balance=70)
2024-11-10T15:44:54.271+04:00 INFO 81270 --- [nio-8080-exec-4] c.a.w.d.service.AccountService : Trying to save account on thread 49 with id 2 START
2024-11-10T15:44:54.271+04:00 DEBUG 81270 --- [nio-8080-exec-4] org.hibernate.SQL : select a1_0.id,a1_0.balance,a1_0.name from account a1_0 where a1_0.id=?
Hibernate: select a1_0.id,a1_0.balance,a1_0.name from account a1_0 where a1_0.id=?
2024-11-10T15:44:54.274+04:00 DEBUG 81270 --- [nio-8080-exec-4] org.hibernate.SQL : update account set balance=?,name=? where id=?
Hibernate: update account set balance=?,name=? where id=?
2024-11-10T15:44:54.280+04:00 INFO 81270 --- [nio-8080-exec-4] c.a.w.d.service.AccountService : Account on thread 49 with id 2 DONE Account(id=2, name=B, balance=160)
However:
@SneakyThrows
@Transactional
public void transfer() {
log.info("Starting transfer on thread {}", Thread.currentThread().getId());
log.info("Getting account on thread {} with id 1 START", Thread.currentThread().getId());
Account from = repository.findById(1L).get();
log.info("Getting account on thread {} with id 1 DONE. {}", Thread.currentThread().getId(), from);
Thread.sleep(10000);
log.info("Getting account on thread {} with id 2 START", Thread.currentThread().getId());
Account to = repository.findById(2L).get();
log.info("Getting account on thread {} with id 2 DONE. {}", Thread.currentThread().getId(), to);
log.info("Balances on accounts on thread {} update START", Thread.currentThread().getId());
from.setBalance(from.getBalance() - 30);
to.setBalance(to.getBalance() + 30);
log.info("Balances on accounts on thread {} update END", Thread.currentThread().getId());
log.info("Trying to save account on thread {} with id 1 START", Thread.currentThread().getId());
Account saveFrom = repository.save(from);
log.info("Account on thread {} with id 1 DONE {}", Thread.currentThread().getId(), saveFrom);
log.info("Trying to save account on thread {} with id 2 START", Thread.currentThread().getId());
Account saveTo = repository.save(to);
log.info("Account on thread {} with id 2 DONE {}", Thread.currentThread().getId(), saveTo);
}
2024-11-10T15:45:43.243+04:00 INFO 81800 --- [nio-8080-exec-2] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring DispatcherServlet 'dispatcherServlet'
2024-11-10T15:45:43.243+04:00 INFO 81800 --- [nio-8080-exec-2] o.s.web.servlet.DispatcherServlet : Initializing Servlet 'dispatcherServlet'
2024-11-10T15:45:43.244+04:00 INFO 81800 --- [nio-8080-exec-2] o.s.web.servlet.DispatcherServlet : Completed initialization in 1 ms
2024-11-10T15:45:43.264+04:00 INFO 81800 --- [nio-8080-exec-2] c.a.w.d.service.AccountService : Starting transfer on thread 47
2024-11-10T15:45:43.264+04:00 INFO 81800 --- [nio-8080-exec-2] c.a.w.d.service.AccountService : Getting account on thread 47 with id 1 START
2024-11-10T15:45:43.282+04:00 DEBUG 81800 --- [nio-8080-exec-2] org.hibernate.SQL : select a1_0.id,a1_0.balance,a1_0.name from account a1_0 where a1_0.id=?
Hibernate: select a1_0.id,a1_0.balance,a1_0.name from account a1_0 where a1_0.id=?
2024-11-10T15:45:43.289+04:00 INFO 81800 --- [nio-8080-exec-2] c.a.w.d.service.AccountService : Getting account on thread 47 with id 1 DONE. Account(id=1, name=A, balance=100)
2024-11-10T15:45:45.847+04:00 INFO 81800 --- [nio-8080-exec-4] c.a.w.d.service.AccountService : Starting transfer on thread 49
2024-11-10T15:45:45.847+04:00 INFO 81800 --- [nio-8080-exec-4] c.a.w.d.service.AccountService : Getting account on thread 49 with id 1 START
2024-11-10T15:45:45.848+04:00 DEBUG 81800 --- [nio-8080-exec-4] org.hibernate.SQL : select a1_0.id,a1_0.balance,a1_0.name from account a1_0 where a1_0.id=?
Hibernate: select a1_0.id,a1_0.balance,a1_0.name from account a1_0 where a1_0.id=?
2024-11-10T15:45:45.850+04:00 INFO 81800 --- [nio-8080-exec-4] c.a.w.d.service.AccountService : Getting account on thread 49 with id 1 DONE. Account(id=1, name=A, balance=100)
2024-11-10T15:45:53.293+04:00 INFO 81800 --- [nio-8080-exec-2] c.a.w.d.service.AccountService : Getting account on thread 47 with id 2 START
2024-11-10T15:45:53.296+04:00 DEBUG 81800 --- [nio-8080-exec-2] org.hibernate.SQL : select a1_0.id,a1_0.balance,a1_0.name from account a1_0 where a1_0.id=?
Hibernate: select a1_0.id,a1_0.balance,a1_0.name from account a1_0 where a1_0.id=?
2024-11-10T15:45:53.303+04:00 INFO 81800 --- [nio-8080-exec-2] c.a.w.d.service.AccountService : Getting account on thread 47 with id 2 DONE. Account(id=2, name=B, balance=100)
2024-11-10T15:45:53.303+04:00 INFO 81800 --- [nio-8080-exec-2] c.a.w.d.service.AccountService : Balances on accounts on thread 47 update START
2024-11-10T15:45:53.303+04:00 INFO 81800 --- [nio-8080-exec-2] c.a.w.d.service.AccountService : Balances on accounts on thread 47 update END
2024-11-10T15:45:53.303+04:00 INFO 81800 --- [nio-8080-exec-2] c.a.w.d.service.AccountService : Trying to save account on thread 47 with id 1 START
2024-11-10T15:45:53.311+04:00 INFO 81800 --- [nio-8080-exec-2] c.a.w.d.service.AccountService : Account on thread 47 with id 1 DONE Account(id=1, name=A, balance=70)
2024-11-10T15:45:53.311+04:00 INFO 81800 --- [nio-8080-exec-2] c.a.w.d.service.AccountService : Trying to save account on thread 47 with id 2 START
2024-11-10T15:45:53.311+04:00 INFO 81800 --- [nio-8080-exec-2] c.a.w.d.service.AccountService : Account on thread 47 with id 2 DONE Account(id=2, name=B, balance=130)
2024-11-10T15:45:53.322+04:00 DEBUG 81800 --- [nio-8080-exec-2] org.hibernate.SQL : update account set balance=?,name=? where id=?
Hibernate: update account set balance=?,name=? where id=?
2024-11-10T15:45:53.325+04:00 DEBUG 81800 --- [nio-8080-exec-2] org.hibernate.SQL : update account set balance=?,name=? where id=?
Hibernate: update account set balance=?,name=? where id=?
2024-11-10T15:45:55.855+04:00 INFO 81800 --- [nio-8080-exec-4] c.a.w.d.service.AccountService : Getting account on thread 49 with id 2 START
2024-11-10T15:45:55.857+04:00 DEBUG 81800 --- [nio-8080-exec-4] org.hibernate.SQL : select a1_0.id,a1_0.balance,a1_0.name from account a1_0 where a1_0.id=?
Hibernate: select a1_0.id,a1_0.balance,a1_0.name from account a1_0 where a1_0.id=?
2024-11-10T15:45:55.862+04:00 INFO 81800 --- [nio-8080-exec-4] c.a.w.d.service.AccountService : Getting account on thread 49 with id 2 DONE. Account(id=2, name=B, balance=100)
2024-11-10T15:45:55.862+04:00 INFO 81800 --- [nio-8080-exec-4] c.a.w.d.service.AccountService : Balances on accounts on thread 49 update START
2024-11-10T15:45:55.862+04:00 INFO 81800 --- [nio-8080-exec-4] c.a.w.d.service.AccountService : Balances on accounts on thread 49 update END
2024-11-10T15:45:55.862+04:00 INFO 81800 --- [nio-8080-exec-4] c.a.w.d.service.AccountService : Trying to save account on thread 49 with id 1 START
2024-11-10T15:45:55.863+04:00 INFO 81800 --- [nio-8080-exec-4] c.a.w.d.service.AccountService : Account on thread 49 with id 1 DONE Account(id=1, name=A, balance=70)
2024-11-10T15:45:55.863+04:00 INFO 81800 --- [nio-8080-exec-4] c.a.w.d.service.AccountService : Trying to save account on thread 49 with id 2 START
2024-11-10T15:45:55.863+04:00 INFO 81800 --- [nio-8080-exec-4] c.a.w.d.service.AccountService : Account on thread 49 with id 2 DONE Account(id=2, name=B, balance=130)
2024-11-10T15:45:55.864+04:00 DEBUG 81800 --- [nio-8080-exec-4] org.hibernate.SQL : update account set balance=?,name=? where id=?
Hibernate: update account set balance=?,name=? where id=?
2024-11-10T15:45:55.866+04:00 DEBUG 81800 --- [nio-8080-exec-4] org.hibernate.SQL : update account set balance=?,name=? where id=?
Hibernate: update account set balance=?,name=? where id=?
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
package com.appsdeveloperblog.ws.demotransaction2.repository;
import com.appsdeveloperblog.ws.demotransaction2.entity.Account;
import jakarta.persistence.LockModeType;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Lock;
import java.util.Optional;
public interface AccountRepository extends JpaRepository<Account, Long> {
@Override
@Lock(LockModeType.PESSIMISTIC_READ)
Optional<Account> findById(Long id);
}
package com.appsdeveloperblog.ws.demotransaction2.service;
import com.appsdeveloperblog.ws.demotransaction2.entity.Account;
import com.appsdeveloperblog.ws.demotransaction2.repository.AccountRepository;
import lombok.RequiredArgsConstructor;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@Slf4j
@Service
@RequiredArgsConstructor
public class AccountService {
private final AccountRepository repository;
@SneakyThrows
@Transactional
public void transfer() {
log.info("Starting transfer on thread {}", Thread.currentThread().getId());
log.info("Getting account on thread {} with id 1 START", Thread.currentThread().getId());
Account from = repository.findById(1L).get();
log.info("Getting account on thread {} with id 1 DONE. {}", Thread.currentThread().getId(), from);
Thread.sleep(10000);
log.info("Getting account on thread {} with id 2 START", Thread.currentThread().getId());
Account to = repository.findById(2L).get();
log.info("Getting account on thread {} with id 2 DONE. {}", Thread.currentThread().getId(), to);
log.info("Balances on accounts on thread {} update START", Thread.currentThread().getId());
from.setBalance(from.getBalance() - 30);
to.setBalance(to.getBalance() + 30);
log.info("Balances on accounts on thread {} update END", Thread.currentThread().getId());
log.info("Trying to save account on thread {} with id 1 START", Thread.currentThread().getId());
Account saveFrom = repository.save(from);
log.info("Account on thread {} with id 1 DONE {}", Thread.currentThread().getId(), saveFrom);
log.info("Trying to save account on thread {} with id 2 START", Thread.currentThread().getId());
Account saveTo = repository.save(to);
log.info("Account on thread {} with id 2 DONE {}", Thread.currentThread().getId(), saveTo);
}
@SneakyThrows
@Transactional
public Account update() {
log.info("Trying to get account");
Account account = repository.findById(1l).get();
log.info("Trying to update balance");
account.setBalance(account.getBalance() + 40);
log.info("Trying to save account");
Thread.sleep(10000);
repository.save(account);
log.info("Account saved successfully {}", account);
return account;
}
@SneakyThrows
@Transactional
public Account update2() {
log.info("Trying to get account #2");
Account account = repository.findById(1l).get();
log.info("Trying to update balance #2");
account.setBalance(account.getBalance() + 40);
log.info("Trying to save account #2");
repository.save(account);
log.info("Account saved successfully {} #2", account);
return account;
}
public Account read() {
log.info("Trying to read account");
Account account = repository.findById(1L).get();
log.info("Account read success {}", account);
return account;
}
}
When you call the `update()` method first and then, after 2 seconds, call the `update2()` method, the following sequence of events occurs:
1. **First Transaction (`update()` method):**
- **Time t=0s:** The `update()` method begins execution.
- **Acquire Pessimistic Read Lock:** It calls `repository.findById(1L).get()`, which acquires a **PESSIMISTIC_READ** lock on the account with `id=1`. This lock allows other transactions to read the data but prevents them from acquiring a lock that would allow writing.
- **Read Balance:** It reads the account balance, which is initially **100**.
- **Update Balance:** It increments the balance by **40**, setting it to **140**.
- **Sleep:** The thread sleeps for **10 seconds** (`Thread.sleep(10000);`) before saving.
2. **Second Transaction (`update2()` method):**
- **Time t=2s:** After a 2-second delay, the `update2()` method begins execution.
- **Attempt to Acquire Pessimistic Read Lock:** It calls `repository.findById(1L).get()`, attempting to acquire a **PESSIMISTIC_READ** lock on the same account.
- **Lock Acquisition Succeeds:** Because a **PESSIMISTIC_READ** lock allows multiple transactions to hold a read lock simultaneously (shared lock), the second transaction successfully acquires the lock.
- **Read Balance:** It reads the account balance, which is still **100** because the first transaction hasn't committed its changes yet.
- **Update Balance:** It increments the balance by **40**, setting it to **140**.
- **Save Account:** It calls `repository.save(account);`, saving the updated balance to the database.
- **Release Lock:** The second transaction completes and releases its locks.
3. **First Transaction Resumes:**
- **Time t=10s:** The `update()` method wakes up from sleep.
- **Save Account:** It calls `repository.save(account);`, attempting to save its version of the account with a balance of **140**.
- **Overwrite Occurs:** Since the second transaction has already updated the balance to **140**, the first transaction's save operation doesn't raise any conflicts and overwrites the existing balance.
- **Release Lock:** The first transaction completes and releases its locks.
**Final Outcome:**
- The account balance remains **140** instead of the expected **180**.
- This situation is known as a **"lost update"** problem, where the update from the first transaction overwrites the update from the second transaction without any conflict detection.
**Why Did This Happen?**
- **PESSIMISTIC_READ Lock Limitations:** A **PESSIMISTIC_READ** lock doesn't prevent other transactions from reading or even writing to the data. It only prevents other transactions from acquiring a lock that would allow them to write exclusively (like a **PESSIMISTIC_WRITE** lock).
- **Lack of Write Lock:** Since both transactions only hold a **PESSIMISTIC_READ** lock, they can read and even proceed to write without blocking each other.
- **No Conflict Detection:** There is no mechanism (like version checks in optimistic locking) to detect that another transaction has modified the data between the read and write operations.
**How to Prevent This:**
- **Use PESSIMISTIC_WRITE Lock:**
```java
@Lock(LockModeType.PESSIMISTIC_WRITE)
```
This lock mode will prevent other transactions from acquiring any lock on the data, effectively serializing access.
- **Optimistic Locking with Versioning:**
- Add a version field to the `Account` entity.
- Use `@Version` annotation to enable optimistic locking.
- This will cause a conflict if another transaction modifies the data between read and write operations, throwing an `OptimisticLockException`.
**Summary:**
By using a **PESSIMISTIC_READ** lock, both transactions are allowed to read and write the data concurrently, leading to a lost update where the second transaction's changes are overwritten by the first. To ensure data integrity, you should use a **PESSIMISTIC_WRITE** lock or implement optimistic locking with versioning.
**************************************************************************************************************************************************************************
package com.appsdeveloperblog.ws.demotransaction2.repository;
import com.appsdeveloperblog.ws.demotransaction2.entity.Account;
import jakarta.persistence.LockModeType;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Lock;
import java.util.Optional;
public interface AccountRepository extends JpaRepository<Account, Long> {
@Override
@Lock(LockModeType.PESSIMISTIC_READ)
Optional<Account> findById(Long id);
}
package com.appsdeveloperblog.ws.demotransaction2.service;
import com.appsdeveloperblog.ws.demotransaction2.entity.Account;
import com.appsdeveloperblog.ws.demotransaction2.repository.AccountRepository;
import lombok.RequiredArgsConstructor;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@Slf4j
@Service
@RequiredArgsConstructor
public class AccountService {
private final AccountRepository repository;
@SneakyThrows
@Transactional
public void transfer() {
log.info("Starting transfer on thread {}", Thread.currentThread().getId());
log.info("Getting account on thread {} with id 1 START", Thread.currentThread().getId());
Account from = repository.findById(1L).get();
log.info("Getting account on thread {} with id 1 DONE. {}", Thread.currentThread().getId(), from);
Thread.sleep(10000);
log.info("Getting account on thread {} with id 2 START", Thread.currentThread().getId());
Account to = repository.findById(2L).get();
log.info("Getting account on thread {} with id 2 DONE. {}", Thread.currentThread().getId(), to);
log.info("Balances on accounts on thread {} update START", Thread.currentThread().getId());
from.setBalance(from.getBalance() - 30);
to.setBalance(to.getBalance() + 30);
log.info("Balances on accounts on thread {} update END", Thread.currentThread().getId());
log.info("Trying to save account on thread {} with id 1 START", Thread.currentThread().getId());
Account saveFrom = repository.save(from);
log.info("Account on thread {} with id 1 DONE {}", Thread.currentThread().getId(), saveFrom);
log.info("Trying to save account on thread {} with id 2 START", Thread.currentThread().getId());
Account saveTo = repository.save(to);
log.info("Account on thread {} with id 2 DONE {}", Thread.currentThread().getId(), saveTo);
}
@SneakyThrows
// @Transactional
public void update1() {
log.info("{} GET 1L start", Thread.currentThread().getId());
Account from = repository.findById(1l).get();
log.info("{} GET 1L {} end", Thread.currentThread().getId(), from);
log.info("{} GET 2L start", Thread.currentThread().getId());
Account to = repository.findById(2L).get();
log.info("{} GET 2L {} end", Thread.currentThread().getId(), to);
log.info("{} UPDATE balance start 1L", Thread.currentThread().getId());
from.setBalance(from.getBalance() - 10);
log.info("{} UPDATE balance end 1L {}", Thread.currentThread().getId(), from);
log.info("{} UPDATE balance start 2L", Thread.currentThread().getId());
to.setBalance(to.getBalance() + 10);
log.info("{} UPDATE balance end 2L {}", Thread.currentThread().getId(), to);
log.info("{} SLEEPING for 10 seconds", Thread.currentThread().getId());
Thread.sleep(10000);
log.info("{} SAVING 1L start", Thread.currentThread().getId());
Account savedFrom = repository.save(from);
log.info("{} SAVING 1L end {}", Thread.currentThread().getId(), savedFrom);
log.info("{} SAVING 2L start", Thread.currentThread().getId());
Account savedTo = repository.save(to);
log.info("{} SAVING 2L end {}", Thread.currentThread().getId(), savedTo);
}
@SneakyThrows
// @Transactional
public void update2() {
log.info("{} GET 1L start", Thread.currentThread().getId());
Account from = repository.findById(1l).get();
log.info("{} GET 1L {} end", Thread.currentThread().getId(), from);
log.info("{} GET 2L start", Thread.currentThread().getId());
Account to = repository.findById(2L).get();
log.info("{} GET 2L {} end", Thread.currentThread().getId(), to);
log.info("{} UPDATE balance start 1L", Thread.currentThread().getId());
from.setBalance(from.getBalance() - 50);
log.info("{} UPDATE balance end 1L {}", Thread.currentThread().getId(), from);
log.info("{} UPDATE balance start 2L", Thread.currentThread().getId());
to.setBalance(to.getBalance() + 50);
log.info("{} UPDATE balance end 2L {}", Thread.currentThread().getId(), to);
log.info("{} SAVING 1L start", Thread.currentThread().getId());
Account savedFrom = repository.save(from);
log.info("{} SAVING 1L end {}", Thread.currentThread().getId(), savedFrom);
log.info("{} SAVING 2L start", Thread.currentThread().getId());
Account savedTo = repository.save(to);
log.info("{} SAVING 2L end {}", Thread.currentThread().getId(), savedTo);
}
public Account read() {
log.info("Trying to read account");
Account account = repository.findById(1L).get();
log.info("Account read success {}", account);
return account;
}
}
Certainly! Let's walk through the scenario step by step to understand what's happening in your code and why the final database state ends up with Account 1 having a balance of **90** and Account 2 having a balance of **110**.
### **Initial Conditions**
- **Database State:**
- Account 1: balance = **100**
- Account 2: balance = **100**
- **Methods:**
- `update1()`: Decreases Account 1 by **10**, increases Account 2 by **10**, sleeps for **10 seconds** before saving.
- `update2()`: Decreases Account 1 by **50**, increases Account 2 by **50**, saves immediately.
- **Annotations:**
- Both methods are **not transactional** (the `@Transactional` annotation is commented out).
- **Repository Method:**
- `findById()` is annotated with `@Lock(LockModeType.PESSIMISTIC_READ)`, which translates to a `FOR SHARE` lock in SQL.
### **Step-by-Step Execution**
#### **Thread 47 (Executing `update1()`)**
**Time: 17:17:29.903**
1. **Fetch Account 1:**
- Logs: `47 GET 1L start`
- Executes SQL: `SELECT ... FROM account WHERE id=1 FOR SHARE`
- Account 1 is fetched with balance **100**.
- Logs: `47 GET 1L Account(id=1, name=A, balance=100) end`
2. **Fetch Account 2:**
- Logs: `47 GET 2L start`
- Executes SQL: `SELECT ... FROM account WHERE id=2 FOR SHARE`
- Account 2 is fetched with balance **100**.
- Logs: `47 GET 2L Account(id=2, name=B, balance=100) end`
3. **Update Balances:**
- Account 1 balance is decreased by **10**: **100 - 10 = 90**
- Logs: `47 UPDATE balance start 1L`
- Logs: `47 UPDATE balance end 1L Account(id=1, name=A, balance=90)`
- Account 2 balance is increased by **10**: **100 + 10 = 110**
- Logs: `47 UPDATE balance start 2L`
- Logs: `47 UPDATE balance end 2L Account(id=2, name=B, balance=110)`
4. **Sleep:**
- Logs: `47 SLEEPING for 10 seconds`
- Thread sleeps for **10 seconds**.
#### **Thread 48 (Executing `update2()`)**
**Time: 17:17:32.246** (approximately 2.3 seconds after Thread 47 starts)
1. **Fetch Account 1:**
- Logs: `48 GET 1L start`
- Executes SQL: `SELECT ... FROM account WHERE id=1 FOR SHARE`
- Account 1 is fetched with balance **100** (changes made by Thread 47 are not yet saved).
- Logs: `48 GET 1L Account(id=1, name=A, balance=100) end`
2. **Fetch Account 2:**
- Logs: `48 GET 2L start`
- Executes SQL: `SELECT ... FROM account WHERE id=2 FOR SHARE`
- Account 2 is fetched with balance **100**.
- Logs: `48 GET 2L Account(id=2, name=B, balance=100) end`
3. **Update Balances:**
- Account 1 balance is decreased by **50**: **100 - 50 = 50**
- Logs: `48 UPDATE balance start 1L`
- Logs: `48 UPDATE balance end 1L Account(id=1, name=A, balance=50)`
- Account 2 balance is increased by **50**: **100 + 50 = 150**
- Logs: `48 UPDATE balance start 2L`
- Logs: `48 UPDATE balance end 2L Account(id=2, name=B, balance=150)`
4. **Save Changes Immediately:**
- **Save Account 1:**
- Logs: `48 SAVING 1L start`
- Executes SQL:
- `SELECT ... FROM account WHERE id=1` (to check if the entity is managed)
- `UPDATE account SET balance=50 WHERE id=1`
- Account 1 balance is updated to **50** in the database.
- Logs: `48 SAVING 1L end Account(id=1, name=A, balance=50)`
- **Save Account 2:**
- Logs: `48 SAVING 2L start`
- Executes SQL:
- `SELECT ... FROM account WHERE id=2`
- `UPDATE account SET balance=150 WHERE id=2`
- Account 2 balance is updated to **150** in the database.
- Logs: `48 SAVING 2L end Account(id=2, name=B, balance=150)`
- **Database State after Thread 48:**
- Account 1: balance = **50**
- Account 2: balance = **150**
#### **Thread 47 Resumes**
**Time: 17:17:39.953** (after 10-second sleep)
1. **Save Account 1:**
- Logs: `47 SAVING 1L start`
- Executes SQL:
- `SELECT ... FROM account WHERE id=1` (to check if the entity is managed)
- `UPDATE account SET balance=90 WHERE id=1`
- Account 1 balance is updated to **90** in the database.
- Logs: `47 SAVING 1L end Account(id=1, name=A, balance=90)`
2. **Save Account 2:**
- Logs: `47 SAVING 2L start`
- Executes SQL:
- `SELECT ... FROM account WHERE id=2`
- `UPDATE account SET balance=110 WHERE id=2`
- Account 2 balance is updated to **110** in the database.
- Logs: `47 SAVING 2L end Account(id=2, name=B, balance=110)`
- **Final Database State:**
- Account 1: balance = **90**
- Account 2: balance = **110**
### **Explanation of the Behavior**
#### **1. Lack of Transactional Boundaries**
- **Non-Transactional Methods:**
- Both `update1()` and `update2()` are not annotated with `@Transactional`.
- Each database operation (`findById()`, `save()`) runs in its own transaction.
- There is no overarching transaction that spans the entire method.
#### **2. Locking Mechanism**
- **PESSIMISTIC_READ Lock (`FOR SHARE`):**
- The `findById()` method is annotated with `@Lock(LockModeType.PESSIMISTIC_READ)`.
- In MySQL, this translates to a `SELECT ... FOR SHARE` query.
- **Important:** Since there is no transaction wrapping the method, the lock acquired by `FOR SHARE` is released immediately after the `findById()` method completes.
- **Effect:** The locks do not persist across multiple operations within the method.
#### **3. Concurrent Access Without Effective Locking**
- **Thread 47's Locks Released:**
- After fetching Account 1 and Account 2, the locks are released due to the lack of a transaction.
- Thread 47 holds no locks during its 10-second sleep.
- **Thread 48 Proceeds Unhindered:**
- Thread 48 is able to fetch and update the same accounts without any blocking.
- It reads the initial balances (since Thread 47 hasn't saved changes) and performs its own updates.
#### **4. Lost Updates and Overwriting Data**
- **Thread 48's Updates:**
- Updates Account 1 to balance **50** and Account 2 to balance **150**.
- Saves these changes to the database.
- **Thread 47's Updates Overwrite Thread 48's Changes:**
- After waking up, Thread 47 saves its earlier changes, unaware that the data has changed in the meantime.
- Updates Account 1 to balance **90** and Account 2 to balance **110**, overwriting Thread 48's changes.
- **Resulting Lost Updates:**
- The changes made by Thread 48 are effectively lost.
- The final balances reflect only Thread 47's updates.
### **Why Did This Happen?**
#### **A. Absence of Transaction Scope**
- Without `@Transactional`, each database operation is a separate transaction.
- Locks acquired during `findById()` are released immediately after the method returns.
#### **B. Ineffective Locking Due to Lack of Transaction**
- **PESSIMISTIC_READ** locks are only effective within a transaction.
- Locks are meant to be held until the transaction commits or rolls back.
- Without a transaction, locks are not held across multiple operations, leading to race conditions.
#### **C. No Conflict Detection Mechanism**
- There is no optimistic locking (versioning) to detect that the data has changed between read and write.
- The application overwrites data without awareness of concurrent modifications.
### **How to Prevent This Issue**
#### **1. Use Transactions**
- **Add `@Transactional` Annotation:**
- Annotate both methods with `@Transactional` to ensure all operations are executed within a single transaction.
- This ensures that locks are held throughout the entire method execution.
#### **2. Use Appropriate Locking**
- **Change Lock Mode to `PESSIMISTIC_WRITE`:**
- Modify `findById()` to use `@Lock(LockModeType.PESSIMISTIC_WRITE)`:
```java
@Override
@Lock(LockModeType.PESSIMISTIC_WRITE)
Optional<Account> findById(Long id);
```
- This acquires an exclusive lock (`FOR UPDATE`), preventing other transactions from reading or writing until the lock is released.
- **Effect of Using Transactions and PESSIMISTIC_WRITE Locks:**
- **Thread 47:**
- Acquires exclusive locks on Account 1 and Account 2.
- Holds the locks during the 10-second sleep.
- **Thread 48:**
- Attempts to acquire exclusive locks but blocks at `findById()` until Thread 47's transaction commits.
- Proceeds after Thread 47 finishes, ensuring sequential updates and data consistency.
#### **3. Implement Optimistic Locking**
- **Add a Version Field to the Entity:**
```java
@Entity
public class Account {
@Id
private Long id;
private BigDecimal balance;
@Version
private Long version;
// getters and setters
}
```
- **Enable Optimistic Locking:**
- Hibernate will automatically check the version field upon updates.
- If the version has changed since the entity was read, an `OptimisticLockException` is thrown.
- **Handle Conflicts:**
- Catch the `OptimisticLockException` and decide whether to retry the operation or inform the user.
#### **4. Adjust Transaction Isolation Level (if necessary)**
- **Set Isolation Level to SERIALIZABLE:**
```java
@Transactional(isolation = Isolation.SERIALIZABLE)
```
- **Note:**
- This provides the highest level of isolation but may impact performance due to increased locking and decreased concurrency.
### **Updated Code with Transactions and PESSIMISTIC_WRITE Locks**
```java
@SneakyThrows
@Transactional
public void update1() {
// same code as before
}
@SneakyThrows
@Transactional
public void update2() {
// same code as before
}
public interface AccountRepository extends JpaRepository<Account, Long> {
@Override
@Lock(LockModeType.PESSIMISTIC_WRITE)
Optional<Account> findById(Long id);
}
```
### **Expected Behavior After Changes**
- **Thread 47:**
- Starts transaction and acquires exclusive locks on Account 1 and Account 2.
- Performs updates and sleeps for 10 seconds.
- Holds locks throughout, preventing other transactions from accessing the accounts.
- **Thread 48:**
- Attempts to start transaction and acquire locks.
- Blocks at `findById()` until Thread 47's transaction commits.
- After Thread 47 commits, proceeds to read updated balances (Account 1: 90, Account 2: 110).
- Performs updates accordingly:
- Account 1: **90 - 50 = 40**
- Account 2: **110 + 50 = 160**
- Saves changes, resulting in final balances:
- Account 1: **40**
- Account 2: **160**
- **Outcome:**
- Both updates are applied sequentially.
- No lost updates or overwriting occurs.
- Data consistency is maintained.
### **Conclusion**
- **Root Cause:**
- The absence of transactional boundaries and ineffective locking led to concurrent modifications without conflict detection.
- This resulted in lost updates and data inconsistency.
- **Solution:**
- Implement transactions to ensure operations are atomic and locks are held appropriately.
- Use `PESSIMISTIC_WRITE` locks to prevent concurrent writes.
- Consider optimistic locking for conflict detection in low-contention scenarios.
### **Key Takeaways**
- **Transactions Are Essential:**
- They define the scope of atomic operations and lock durations.
- Without transactions, locks may not behave as expected.
- **Locking Must Be Used Correctly:**
- Locks need to be held for the duration of the transaction to be effective.
- Choose the appropriate lock mode based on concurrency requirements.
- **Conflict Detection Mechanisms:**
- Optimistic locking with versioning can prevent lost updates.
- Proper exception handling ensures that conflicts are resolved gracefully.
- **Testing and Validation:**
- Always test concurrent scenarios to validate the correctness of your transaction and locking strategies.
By understanding how transactions and locks work together, you can design your application to handle concurrency correctly, ensuring data integrity and consistency.
--------------------------------------------------------------------------------------------------------------------------------
Question1: in mysql we know that default isolation level is repeatable read. so question: if i do some operations (in one transaction) does it create snapshot of db?
Aswer1:Yes, in MySQL's default isolation level of **Repeatable Read**, when you perform operations within a single transaction, the InnoDB storage engine effectively creates a **snapshot** of the database at the point when the transaction begins its first read operation. This snapshot ensures that all reads within the same transaction are consistent and see the same data, regardless of other concurrent transactions that may be modifying the data.
### How It Works:
- **Multiversion Concurrency Control (MVCC):** InnoDB uses MVCC to handle transaction isolation. Instead of locking rows for reading, it maintains multiple versions of data using undo logs. This allows transactions to read a consistent snapshot without interfering with write operations from other transactions.
- **Consistent Read View:** When a transaction performs its first read, InnoDB establishes a read view (snapshot) of the database. All subsequent reads within that transaction refer to this snapshot, ensuring repeatable reads.
- **Isolation Level Behavior:**
- **Repeatable Read:** Ensures that if you read the same record multiple times within the same transaction, you get the same data each time, even if other transactions modify the data in between.
- **Serializable:** A stricter level where transactions are completely isolated from each other, behaving as if they were executed sequentially.
- **Read Committed:** Each read sees only data that has been committed before that read; non-repeatable reads can occur.
- **Read Uncommitted:** Transactions may read uncommitted changes from other transactions; dirty reads can occur.
### Implications:
- **Consistency:** The snapshot provides a consistent view of the data, enhancing data integrity within the transaction.
- **Performance:** MVCC allows for high concurrency without extensive locking, improving performance in multi-user environments.
- **Visibility of Changes:** Writes (INSERT, UPDATE, DELETE) within your own transaction are visible to subsequent reads in the same transaction, even if they are not yet committed.
### Example Scenario:
1. **Transaction A** starts and reads data from `table1`.
2. **Transaction B** updates some rows in `table1` and commits.
3. **Transaction A** reads from `table1` again.
- Under **Repeatable Read**, Transaction A will see the same data from its initial read, unaffected by Transaction B's changes.
- The snapshot ensures that Transaction A's view remains consistent throughout its execution.
### Conclusion:
So, when you perform operations within a transaction at the default **Repeatable Read** isolation level, MySQL's InnoDB storage engine provides a snapshot of the database to ensure consistent and repeatable reads throughout the transaction.
**References:**
- [MySQL 8.0 Reference Manual - InnoDB Transaction Model and Locking](https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-model.html)
- [MySQL 8.0 Reference Manual - Consistent Nonlocking Reads](https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html)
**Yes, under the default Repeatable Read isolation level, MySQL uses MVCC to create a snapshot of the database for your transaction, ensuring consistent reads throughout its operations.**
Question2:ok. thaks for answer. how about Read Uncommitted and Read committed, are they also provide snapshot?
Answer2:Certainly! Let's explore how the **Read Uncommitted** and **Read Committed** isolation levels handle snapshots in MySQL, specifically with the InnoDB storage engine.
---
## **Read Uncommitted Isolation Level**
### **Snapshot Behavior**
- **No Consistent Snapshot:** At the **Read Uncommitted** isolation level, InnoDB does **not** create a consistent snapshot for the transaction. Transactions can read **uncommitted changes** made by other transactions, leading to potential inconsistencies.
### **Implications**
- **Dirty Reads:** Transactions may see data that has been modified but not yet committed by other transactions. This can lead to **dirty reads**, where you read data that might be rolled back later.
- **No MVCC Snapshot:** Since InnoDB does not establish a snapshot for the transaction, each read operation might see the most recent data, regardless of its commit status.
### **Use Cases**
- **Rarely Recommended:** Due to the risk of reading inconsistent or invalid data, **Read Uncommitted** is seldom used in practice. It might be considered in scenarios where performance is critical, and data accuracy is less of a concern.
---
## **Read Committed Isolation Level**
### **Snapshot Behavior**
- **Snapshot per Read Operation:** In **Read Committed**, InnoDB creates a **new snapshot** at the start of each **consistent read** (e.g., each `SELECT` statement). This means:
- Each read sees only data that has been **committed** before the read begins.
- There is **no consistent snapshot maintained** throughout the entire transaction.
### **Implications**
- **Non-Repeatable Reads:**
- **Data Changes Between Reads:** If other transactions commit changes after your transaction begins but before a read operation, your transaction will see these new changes in subsequent reads.
- This can lead to **non-repeatable reads**, where the same query yields different results within the same transaction.
- **No Dirty Reads:** Unlike **Read Uncommitted**, you **will not** read uncommitted changes from other transactions.
- **MVCC Usage:** InnoDB uses **MVCC** (Multiversion Concurrency Control) to provide consistent reads without locking, but the snapshot is refreshed with each read operation.
### **Use Cases**
- **Common Default in Other Databases:** Many databases (e.g., Oracle, PostgreSQL) use **Read Committed** as the default isolation level.
- **Balance Between Consistency and Concurrency:** Offers better performance and concurrency than **Repeatable Read**, with acceptable consistency for many applications.
---
## **Comparison with Repeatable Read**
| **Isolation Level** | **Snapshot Creation** | **Data Visibility** | **Common Issues** |
|------------------------|---------------------------------------------------------------|----------------------------------------------------------|------------------------------|
| **Read Uncommitted** | No snapshot; reads latest data including uncommitted changes | Sees all changes, committed or not | Dirty reads |
| **Read Committed** | Snapshot per read operation (at start of each SELECT) | Sees changes committed before each read | Non-repeatable reads |
| **Repeatable Read** | Single snapshot at start of first read in the transaction | Sees data as of the start of the transaction (plus own changes) | Phantom reads (prevented by InnoDB's gap locking) |
---
## **Detailed Behavior**
### **Read Uncommitted**
- **No Isolation:** Transactions can read rows that are being modified by other transactions but are not yet committed.
- **Example Scenario:**
- Transaction A starts and reads a row that Transaction B has modified but not yet committed.
- If Transaction B rolls back, Transaction A has read invalid data.
### **Read Committed**
- **Latest Committed Data on Each Read:**
- Each `SELECT` statement sees the database as of the moment it starts.
- Changes committed by other transactions after your transaction starts but before your read begins are visible.
- **Own Changes Are Visible:**
- Modifications made within your transaction are visible to subsequent reads in the same transaction.
- **Non-Repeatable Reads:**
- If you query the same data multiple times, you might get different results if other transactions have committed changes in the meantime.
- **Example Scenario:**
1. Transaction A starts.
2. Transaction B updates a row and commits.
3. Transaction A reads the row and sees Transaction B's changes.
### **Repeatable Read** (For Comparison)
- **Consistent Snapshot Throughout Transaction:**
- The snapshot is taken at the time of the first read operation.
- Subsequent reads see the same data, ensuring repeatable reads.
- **Phantom Reads:**
- In standard implementations, new rows inserted by other transactions might appear in subsequent reads (**phantom reads**).
- InnoDB prevents phantom reads through gap locking and Next-Key Locking.
---
## **Conclusion**
- **Read Uncommitted:**
- **Does not provide a snapshot.**
- Transactions may read uncommitted changes from other transactions.
- High risk of data inconsistencies due to dirty reads.
- **Read Committed:**
- **Provides a snapshot per read operation.**
- Each read sees the latest committed data at the time the read begins.
- No consistent snapshot across the transaction; data can change between reads.
- Prevents dirty reads but allows non-repeatable reads.
---
## **Practical Recommendations**
- **Use Read Committed When:**
- You need to prevent dirty reads but can tolerate non-repeatable reads.
- Your application logic can handle data changing between reads.
- Higher concurrency and performance are desired over strict consistency.
- **Avoid Read Uncommitted Unless:**
- You have a specific use case where dirty reads are acceptable.
- Performance is critical, and data accuracy is not a priority.
---
## **Additional Notes**
- **MVCC in InnoDB:**
- In all isolation levels except **Read Uncommitted**, InnoDB uses MVCC to handle consistent reads.
- MVCC allows readers to access a snapshot of the data without blocking writers.
- **Changing Isolation Levels:**
- You can change the isolation level for a session or globally using:
```sql
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
```
- Be cautious when changing isolation levels, as it affects transaction behavior.
---
## **References**
- [MySQL 8.0 Reference Manual - Transaction Isolation Levels](https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html)
- [MySQL 8.0 Reference Manual - Consistent Nonlocking Reads](https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html)
- [MySQL 8.0 Reference Manual - InnoDB Multi-Versioning](https://dev.mysql.com/doc/refman/8.0/en/innodb-multi-versioning.html)
---
**In summary, under the **Read Uncommitted** isolation level, MySQL does not provide a snapshot of the database for transactions. Under the **Read Committed** isolation level, MySQL provides a snapshot at the start of each read operation, but not a consistent snapshot across the entire transaction. This means that while **Read Committed** prevents dirty reads, it does not prevent non-repeatable reads, as data can change between reads within the same transaction.**
Question3:now lets imagine mysql isolation level in read uncommitted level. So i begin transaction and update some data but not commit yet. So does it do on read database or on snapshot?
Answer3:Certainly! Let's delve into how **MySQL's InnoDB storage engine** handles transactions and data modifications under the **Read Uncommitted** isolation level, focusing on whether updates are performed on the actual database or a snapshot.
---
## **Understanding Data Modifications in Transactions**
### **General Behavior Across Isolation Levels**
- **Uncommitted Changes:**
- When you perform `UPDATE`, `INSERT`, or `DELETE` operations within a transaction, these changes are **immediately visible to your own transaction**, regardless of the isolation level.
- These modifications are **not yet committed** to the database; they are pending changes that can be either committed or rolled back.
- **Visibility to Other Transactions:**
- The visibility of your uncommitted changes to **other transactions** depends on their isolation level.
- **Read Uncommitted:** Other transactions can see your uncommitted changes.
- **Read Committed, Repeatable Read, Serializable:** Other transactions cannot see your uncommitted changes.
---
## **Behavior Under Read Uncommitted Isolation Level**
### **Updates and Their Effect on the Database**
- **Direct Modifications:**
- When you begin a transaction and perform updates under **Read Uncommitted**, you are **modifying the actual database records**, not a snapshot.
- These uncommitted changes are written to the database pages in memory (buffer pool) and are associated with your transaction.
- **Visibility to Other Transactions:**
- Other transactions operating under **Read Uncommitted** can **see your uncommitted changes** because there is no snapshot isolation in effect.
- This can lead to **dirty reads**, where other transactions read data that might later be rolled back.
### **No Snapshot Isolation**
- **No MVCC Snapshot for Reads:**
- Under **Read Uncommitted**, InnoDB does **not create a snapshot** for read operations.
- Reads access the latest version of the data, including uncommitted changes made by other transactions.
- **Implications:**
- There is **no consistent view** of the database during your transaction.
- Both your transaction and others may see data in an inconsistent state.
### **Internal Mechanisms**
- **Undo Logs and Redo Logs:**
- InnoDB uses **undo logs** to maintain previous versions of data for MVCC and to roll back transactions if necessary.
- **Redo logs** are used for crash recovery to replay committed changes.
- Your uncommitted changes are recorded in these logs but are also present in the actual data pages in memory.
- **Buffer Pool:**
- Modifications occur in the **InnoDB buffer pool** (memory), and the changes are marked as uncommitted.
- When a transaction is committed, the changes are eventually flushed to disk.
---
## **Example Scenario**
Let's walk through a practical example to illustrate this behavior.
### **Setup**
- **Transaction A (Your Transaction):**
- Begins a transaction under **Read Uncommitted** isolation level.
- Updates a row in `table1` but does **not commit** yet.
- **Transaction B (Another Transaction):**
- Begins a transaction under **Read Uncommitted** isolation level.
- Reads from `table1`.
### **Step-by-Step Process**
1. **Transaction A Updates Data:**
- Executes:
```sql
START TRANSACTION;
UPDATE table1 SET column1 = 'new_value' WHERE id = 1;
```
- The update modifies the actual data in the database's buffer pool.
- The change is uncommitted and associated with Transaction A.
2. **Transaction B Reads Data:**
- Executes:
```sql
START TRANSACTION;
SELECT * FROM table1 WHERE id = 1;
```
- Since both transactions are at **Read Uncommitted**, Transaction B **sees the uncommitted change** made by Transaction A.
- This is because there is no snapshot isolation, and reads access the latest version of the data.
3. **Transaction A Rolls Back:**
- Executes:
```sql
ROLLBACK;
```
- The change is undone using the undo logs.
- The data in `table1` reverts to its previous state.
4. **Transaction B Reads Data Again:**
- Executes:
```sql
SELECT * FROM table1 WHERE id = 1;
```
- Now, Transaction B sees the original data before Transaction A's update.
- This illustrates the **dirty read problem**, where Transaction B read data that was later rolled back.
---
## **Key Points**
### **Modifications Affect Actual Data**
- **Direct Impact:**
- Your updates are made directly to the data pages, not to a separate snapshot.
- The changes are pending and can be committed or rolled back.
### **No Snapshot for Reads or Writes**
- **Read Operations:**
- Under **Read Uncommitted**, reads do not use a snapshot; they access the latest data, including uncommitted changes.
- This applies to both your own uncommitted changes and those made by other transactions.
- **Write Operations:**
- Writes are performed on the actual data.
- Uncommitted changes are maintained in the buffer pool and managed with undo logs for potential rollback.
### **Transaction Isolation**
- **Isolation Level Determines Visibility:**
- The isolation level controls how transactions are isolated from each other in terms of reading data.
- **Read Uncommitted** provides the least isolation, allowing transactions to see uncommitted changes from others.
- **Dirty Reads:**
- The lack of isolation can lead to dirty reads, where transactions read data that might not persist if rolled back.
---
## **Conclusion**
- **Your Uncommitted Updates:**
- Are made directly on the actual database records in memory (buffer pool).
- Are visible to other transactions at the **Read Uncommitted** isolation level.
- **No Snapshot Mechanism:**
- **Read Uncommitted** does not employ snapshots for read consistency.
- Reads access the most recent data, including any uncommitted modifications.
- **Risk of Inconsistencies:**
- Using **Read Uncommitted** can lead to data inconsistencies due to dirty reads.
- It's important to use this isolation level cautiously and only when such inconsistencies are acceptable.
---
## **Recommendations**
- **Avoid Read Uncommitted for Critical Data:**
- Due to the risks of dirty reads and data inconsistencies, it's generally not recommended for transactions involving critical data.
- **Consider Higher Isolation Levels:**
- **Read Committed** or **Repeatable Read** provide better isolation by preventing dirty reads and offering snapshot consistency.
- **Understand Your Application Needs:**
- Choose the appropriate isolation level based on your application's requirements for data consistency and performance.
---
## **Additional Information**
- **Transaction Commit/Rollback:**
- Committing a transaction makes all changes permanent and visible to other transactions at higher isolation levels.
- Rolling back undoes all changes made during the transaction.
- **MVCC and Undo Logs:**
- InnoDB uses **Multi-Version Concurrency Control (MVCC)** to manage concurrent access.
- **Undo logs** allow InnoDB to reconstruct previous versions of data for consistent reads and to roll back transactions.
- **Physical Storage:**
- Uncommitted changes are stored in memory and, if necessary, in temporary storage on disk.
- They become part of the durable database state only upon commit.
---
## **References**
- [MySQL 8.0 Reference Manual - Transaction Isolation Levels](https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html)
- [MySQL 8.0 Reference Manual - Consistent Nonlocking Reads](https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html)
- [MySQL 8.0 Reference Manual - InnoDB Multi-Versioning](https://dev.mysql.com/doc/refman/8.0/en/innodb-multi-versioning.html)
- [MySQL 8.0 Reference Manual - InnoDB Transaction Model and Locking](https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-model.html)
---
**In summary, under the **Read Uncommitted** isolation level, when you begin a transaction and update data without committing, your changes are made directly on the actual database records (in memory), not on a snapshot. These uncommitted changes are immediately visible to other transactions operating under **Read Uncommitted**, leading to potential dirty reads. There is no snapshot isolation in effect, so reads and writes occur directly on the current state of the database, including any uncommitted modifications.**
Question4:now lets imagine mysql isolation level in read uncommitted level. So i begin transaction and update some data but not commit yet. So does it do on read database or on snapshot?
Answer4:Certainly! Let's delve deeper into how MySQL's InnoDB storage engine handles data modifications within a transaction, particularly focusing on where the changes are stored (buffer pool vs. disk) and how other transactions can access these changes, especially under the **Read Uncommitted** isolation level.
---
## **Understanding Data Modification and Storage in InnoDB**
### **1. The Buffer Pool and Disk Storage**
- **Buffer Pool (In-Memory Storage):**
- The **buffer pool** is InnoDB's in-memory cache where data pages are stored for quick access.
- When you modify data (e.g., through `INSERT`, `UPDATE`, or `DELETE`), the changes are first applied to the data pages in the buffer pool.
- These modified pages are marked as **dirty pages**, indicating that they have been changed in memory but not yet flushed to disk.
- **Disk Storage (Persistent Storage):**
- The data files on disk represent the persistent state of the database.
- Dirty pages in the buffer pool are periodically flushed to disk, ensuring that changes are made durable.
- A transaction's changes are only guaranteed to be flushed to disk upon commit, although they might be written earlier due to checkpointing or other internal processes.
### **2. Uncommitted Changes and Their Storage**
- **Uncommitted Changes:**
- Changes made within a transaction that has not yet been committed are stored in the buffer pool.
- These changes are associated with the transaction ID and are marked as uncommitted.
- **Undo logs** are maintained to allow for rollback if the transaction does not commit.
- **Visibility of Uncommitted Changes:**
- Other transactions can access these uncommitted changes depending on their isolation level.
- Under **Read Uncommitted**, other transactions are allowed to read uncommitted changes from the buffer pool.
- The buffer pool is shared among all transactions, so uncommitted changes are accessible to others when isolation levels permit.
---
## **How Other Transactions Read Uncommitted Data**
### **1. Shared Access to the Buffer Pool**
- The buffer pool is a shared resource among all transactions and threads in the MySQL server.
- When a transaction reads data, it accesses the data pages in the buffer pool.
- If the data page is not in the buffer pool, it is read from disk into the buffer pool.
- Since uncommitted changes are applied to the data pages in the buffer pool, other transactions can read these changes if their isolation level allows.
### **2. Isolation Levels and Data Visibility**
- **Read Uncommitted Isolation Level:**
- Transactions operating under this isolation level do not employ snapshot isolation.
- They read the most recent version of the data, including uncommitted changes present in the buffer pool.
- This is why dirty reads are possible: transactions can see data that another transaction has modified but not yet committed.
- **Higher Isolation Levels:**
- Under **Read Committed**, **Repeatable Read**, and **Serializable**, transactions use MVCC and consistent snapshots to avoid seeing uncommitted changes from other transactions.
- They read from versions of data that were committed before their transaction or snapshot started.
---
## **Detailed Process of Data Modification and Reading**
### **1. Transaction Modifying Data**
Let's consider **Transaction A** modifying data:
- **Transaction A Starts:**
```sql
START TRANSACTION;
```
- **Transaction A Updates Data:**
```sql
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
```
- **Data Modification Process:**
- The data page containing the `accounts` row is loaded into the buffer pool if not already present.
- The update is applied directly to the data page in the buffer pool.
- The page is marked as a **dirty page**.
- An **undo log** entry is created to record the previous state, allowing for rollback if necessary.
- The change is associated with Transaction A's ID and marked as uncommitted.
### **2. Another Transaction Reading Data**
Now, **Transaction B** wants to read the data:
- **Transaction B Starts Under Read Uncommitted:**
```sql
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
```
- **Transaction B Reads Data:**
```sql
SELECT balance FROM accounts WHERE account_id = 1;
```
- **Data Reading Process:**
- Transaction B accesses the data page in the buffer pool.
- Since Transaction B is operating under **Read Uncommitted**, it does not use a consistent snapshot or MVCC.
- It reads the most recent version of the data, which includes uncommitted changes from Transaction A.
- Therefore, Transaction B sees the updated balance, even though Transaction A has not committed yet.
### **3. Implications of Buffer Pool Access**
- **Shared Buffer Pool:**
- Both Transaction A and Transaction B are accessing the same buffer pool.
- Uncommitted changes in the buffer pool are visible to transactions that allow dirty reads.
- **No Disk Interaction Required:**
- Since the data is in the buffer pool, there's no need to read from or write to disk at this point.
- The buffer pool acts as the intermediary for data access and modification.
---
## **What Happens Upon Commit or Rollback**
### **1. Commit Operation**
- **Transaction A Commits:**
```sql
COMMIT;
```
- **Post-Commit Actions:**
- The transaction's changes are marked as committed.
- The dirty pages in the buffer pool still exist but are now considered part of the committed state.
- The changes will be flushed to disk based on InnoDB's flushing mechanism (e.g., during a checkpoint).
- Other transactions, even at higher isolation levels, will now see these changes if they start after the commit.
### **2. Rollback Operation**
- **Transaction A Rolls Back:**
```sql
ROLLBACK;
```
- **Post-Rollback Actions:**
- The undo logs are used to revert the changes in the buffer pool.
- The data pages are restored to their previous state.
- The dirty pages are either discarded or updated to reflect the rollback.
- Other transactions that read the uncommitted changes may have read data that no longer exists, leading to inconsistencies.
---
## **Addressing Your Questions Directly**
### **1. Does It Really Write Data to the Database?**
- **Initial Modification:**
- Yes, it writes data to the **buffer pool**, which is part of the database's in-memory representation.
- The changes are not immediately written to the disk-based data files.
- **Temporary Nature:**
- Until the transaction commits, these changes are considered temporary and can be rolled back.
- The changes exist in memory and are managed by InnoDB's transaction system.
### **2. How Can Other Transactions Read Uncommitted Changes from the Buffer Pool?**
- **Shared Access:**
- The buffer pool is shared among all transactions and threads.
- When a transaction reads data, it accesses the data pages in the buffer pool.
- **Isolation Level Permitting:**
- Under **Read Uncommitted**, transactions do not isolate themselves from uncommitted changes made by others.
- Therefore, they can read the latest version of the data in the buffer pool, including uncommitted modifications.
- **No Disk Interaction Needed:**
- Since the data is in the buffer pool, other transactions can read it without accessing the disk.
- The buffer pool ensures efficient data access and modification.
---
## **Visual Representation**
```
+---------------------------+
| Buffer Pool | <-- In-Memory Storage
| |
| [Data Page: accounts] |
| - account_id = 1 |
| - balance = 900 (uncommitted change by Transaction A)
| |
+---------------------------+
↑ ↑
| |
Transaction A Transaction B
(Modified Data) (Reads Data)
```
- Both transactions interact with the buffer pool.
- Transaction B reads the uncommitted change made by Transaction A because of the **Read Uncommitted** isolation level.
---
## **Key Takeaways**
- **Uncommitted Changes are Stored in the Buffer Pool:**
- Changes made within a transaction are applied to the data pages in the buffer pool.
- These changes are not immediately written to disk but are marked as uncommitted.
- **Buffer Pool is Shared Among Transactions:**
- All transactions access data through the buffer pool.
- Under **Read Uncommitted**, transactions can read uncommitted changes present in the buffer pool.
- **Disk Writes Occur Upon Commit:**
- Committing a transaction makes the changes durable.
- Dirty pages are eventually flushed to disk, ensuring persistence.
- **Undo Logs for Rollback:**
- If a transaction rolls back, the undo logs are used to revert changes in the buffer pool.
- This ensures that the database returns to a consistent state.
---
## **Additional Insights**
### **1. Dirty Reads and Data Integrity**
- **Risk of Inconsistencies:**
- Reading uncommitted data can lead to inconsistencies if the transaction making the changes rolls back.
- Applications using **Read Uncommitted** must handle potential anomalies due to dirty reads.
### **2. Why Use Read Uncommitted?**
- **Performance Considerations:**
- In theory, **Read Uncommitted** could offer performance benefits due to reduced locking and overhead.
- In practice, the risks often outweigh the benefits, and the performance gain is minimal with modern databases.
- **Use Cases:**
- It is rarely recommended to use **Read Uncommitted** unless you have a specific scenario where data consistency is not critical.
---
## **Practical Recommendations**
- **Avoid Read Uncommitted When Possible:**
- To maintain data integrity, it's advisable to use at least the **Read Committed** isolation level.
- **Understanding Transaction Behavior:**
- Be aware of how isolation levels affect data visibility and consistency.
- Design your application's transaction logic accordingly.
- **Monitor and Test:**
- If you must use **Read Uncommitted**, thoroughly test your application to ensure it can handle potential inconsistencies.
---
## **References for Further Reading**
- **MySQL Documentation:**
- [InnoDB Buffer Pool](https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool.html)
- [InnoDB Transaction Model and Locking](https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-model.html)
- [Transaction Isolation Levels](https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html)
- **Understanding Isolation Levels:**
- [A Beginner's Guide to Transaction Isolation Levels](https://www.percona.com/blog/2018/10/08/beginners-guide-to-transaction-isolation-levels/)
- [InnoDB Multi-Versioning and MVCC](https://dev.mysql.com/doc/refman/8.0/en/innodb-multi-versioning.html)
---
## **Conclusion**
- **Your Question Answered:**
- When you modify data within a transaction that hasn't been committed, the changes are applied to the **buffer pool** (in-memory storage), not immediately to the disk-based data files.
- These uncommitted changes are stored temporarily in the buffer pool until you commit or roll back the transaction.
- **How Other Transactions Access Uncommitted Data:**
- Because the buffer pool is shared, other transactions can access these uncommitted changes directly from the buffer pool if their isolation level allows it.
- Under **Read Uncommitted**, transactions do not isolate themselves from uncommitted changes, so they can read the data you've modified but not yet committed.
- **Implications:**
- This mechanism allows for high performance and concurrency but requires careful consideration of isolation levels to maintain data integrity.
- Understanding how the buffer pool and isolation levels interact is crucial for designing reliable database applications.
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
===========================================================================
Problem 1:
package com.appsdeveloperblog.ws.demotransaction2.service;
import com.appsdeveloperblog.ws.demotransaction2.entity.Account;
import com.appsdeveloperblog.ws.demotransaction2.repository.AccountRepository;
import lombok.RequiredArgsConstructor;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@Slf4j
@Service
@RequiredArgsConstructor
public class AccountService {
private final AccountRepository repository;
@SneakyThrows
@Transactional
public void transfer() {
log.info("Starting transfer on thread {}", Thread.currentThread().getId());
log.info("Getting account on thread {} with id 1 START", Thread.currentThread().getId());
Account from = repository.findById(1L).get();
log.info("Getting account on thread {} with id 1 DONE. {}", Thread.currentThread().getId(), from);
Thread.sleep(10000);
log.info("Getting account on thread {} with id 2 START", Thread.currentThread().getId());
Account to = repository.findById(2L).get();
log.info("Getting account on thread {} with id 2 DONE. {}", Thread.currentThread().getId(), to);
log.info("Balances on accounts on thread {} update START", Thread.currentThread().getId());
from.setBalance(from.getBalance() - 30);
to.setBalance(to.getBalance() + 30);
log.info("Balances on accounts on thread {} update END", Thread.currentThread().getId());
log.info("Trying to save account on thread {} with id 1 START", Thread.currentThread().getId());
Account saveFrom = repository.save(from);
log.info("Account on thread {} with id 1 DONE {}", Thread.currentThread().getId(), saveFrom);
log.info("Trying to save account on thread {} with id 2 START", Thread.currentThread().getId());
Account saveTo = repository.save(to);
log.info("Account on thread {} with id 2 DONE {}", Thread.currentThread().getId(), saveTo);
}
@SneakyThrows
@Transactional
public void update1() {
log.info("{} GET 1L start", Thread.currentThread().getId());
Account from = repository.findById(1L).get();
log.info("{} GET 1L {} end", Thread.currentThread().getId(), from);
log.info("{} GET 2L start", Thread.currentThread().getId());
Account to = repository.findById(2L).get();
log.info("{} GET 2L {} end", Thread.currentThread().getId(), to);
log.info("{} UPDATE balance start 1L", Thread.currentThread().getId());
from.setBalance(from.getBalance() - 10);
log.info("{} UPDATE balance end 1L {}", Thread.currentThread().getId(), from);
log.info("{} UPDATE balance start 2L", Thread.currentThread().getId());
to.setBalance(to.getBalance() + 10);
log.info("{} UPDATE balance end 2L {}", Thread.currentThread().getId(), to);
log.info("{} SLEEPING for 10 seconds", Thread.currentThread().getId());
Thread.sleep(10000);
log.info("{} SAVING 1L start", Thread.currentThread().getId());
Account savedFrom = repository.save(from);
log.info("{} SAVING 1L end {}", Thread.currentThread().getId(), savedFrom);
log.info("{} SAVING 2L start", Thread.currentThread().getId());
Account savedTo = repository.save(to);
log.info("{} SAVING 2L end {}", Thread.currentThread().getId(), savedTo);
log.info("{} EVERYTHING COMPLETED", Thread.currentThread().getId());
}
@SneakyThrows
@Transactional
public void update2() {
log.info("{} GET 1L start", Thread.currentThread().getId());
Account from = repository.findById(1L).get();
log.info("{} GET 1L {} end", Thread.currentThread().getId(), from);
log.info("{} GET 3L start", Thread.currentThread().getId());
Account to = repository.findById(3L).get();
log.info("{} GET 3L {} end", Thread.currentThread().getId(), to);
log.info("{} UPDATE balance start 1L", Thread.currentThread().getId());
from.setBalance(from.getBalance() - 50);
log.info("{} UPDATE balance end 1L {}", Thread.currentThread().getId(), from);
log.info("{} UPDATE balance start 3L", Thread.currentThread().getId());
to.setBalance(to.getBalance() + 50);
log.info("{} UPDATE balance end 3L {}", Thread.currentThread().getId(), to);
// log.info("{} SLEEPING for 10 seconds", Thread.currentThread().getId());
// Thread.sleep(10000);
log.info("{} SAVING 1L start", Thread.currentThread().getId());
Account savedFrom = repository.save(from);
log.info("{} SAVING 1L end {}", Thread.currentThread().getId(), savedFrom);
log.info("{} SAVING 3L start", Thread.currentThread().getId());
Account savedTo = repository.save(to);
log.info("{} SAVING 3L end {}", Thread.currentThread().getId(), savedTo);
log.info("{} EVERYTHING COMPLETED", Thread.currentThread().getId());
}
@Transactional
public Account read() {
log.info("Trying to read account");
Account account = repository.findById(1L).get();
// account.setBalance(account.getBalance() + 20);
// repository.save(account);
log.info("Account read success {}", account);
return account;
}
}
Problem2:
package com.appsdeveloperblog.ws.demotransaction2.service;
import com.appsdeveloperblog.ws.demotransaction2.entity.Account;
import com.appsdeveloperblog.ws.demotransaction2.repository.AccountRepository;
import lombok.RequiredArgsConstructor;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@Slf4j
@Service
@RequiredArgsConstructor
public class AccountService {
private final AccountRepository repository;
@SneakyThrows
@Transactional
public void transfer() {
log.info("Starting transfer on thread {}", Thread.currentThread().getId());
log.info("Getting account on thread {} with id 1 START", Thread.currentThread().getId());
Account from = repository.findById(1L).get();
log.info("Getting account on thread {} with id 1 DONE. {}", Thread.currentThread().getId(), from);
Thread.sleep(10000);
log.info("Getting account on thread {} with id 2 START", Thread.currentThread().getId());
Account to = repository.findById(2L).get();
log.info("Getting account on thread {} with id 2 DONE. {}", Thread.currentThread().getId(), to);
log.info("Balances on accounts on thread {} update START", Thread.currentThread().getId());
from.setBalance(from.getBalance() - 30);
to.setBalance(to.getBalance() + 30);
log.info("Balances on accounts on thread {} update END", Thread.currentThread().getId());
log.info("Trying to save account on thread {} with id 1 START", Thread.currentThread().getId());
Account saveFrom = repository.save(from);
log.info("Account on thread {} with id 1 DONE {}", Thread.currentThread().getId(), saveFrom);
log.info("Trying to save account on thread {} with id 2 START", Thread.currentThread().getId());
Account saveTo = repository.save(to);
log.info("Account on thread {} with id 2 DONE {}", Thread.currentThread().getId(), saveTo);
}
@SneakyThrows
@Transactional
public void update1(Long fromId, Long toId, Integer amount) {
log.info("{} GET 1L start", Thread.currentThread().getId());
Account from = repository.findById(fromId).get();
log.info("{} GET 1L {} end", Thread.currentThread().getId(), from);
log.info("{} GET 2L start", Thread.currentThread().getId());
Account to = repository.findById(toId).get();
log.info("{} GET 2L {} end", Thread.currentThread().getId(), to);
log.info("{} UPDATE balance start 1L", Thread.currentThread().getId());
from.setBalance(from.getBalance() - amount);
log.info("{} UPDATE balance end 1L {}", Thread.currentThread().getId(), from);
log.info("{} UPDATE balance start 2L", Thread.currentThread().getId());
to.setBalance(to.getBalance() + amount);
log.info("{} UPDATE balance end 2L {}", Thread.currentThread().getId(), to);
log.info("{} SLEEPING for 10 seconds", Thread.currentThread().getId());
Thread.sleep(10000);
log.info("{} SAVING 1L start", Thread.currentThread().getId());
Account savedFrom = repository.save(from);
log.info("{} SAVING 1L end {}", Thread.currentThread().getId(), savedFrom);
log.info("{} SAVING 2L start", Thread.currentThread().getId());
Account savedTo = repository.save(to);
log.info("{} SAVING 2L end {}", Thread.currentThread().getId(), savedTo);
log.info("{} EVERYTHING COMPLETED", Thread.currentThread().getId());
}
@SneakyThrows
@Transactional
public void update2() {
log.info("{} GET 1L start", Thread.currentThread().getId());
Account from = repository.findById(1L).get();
log.info("{} GET 1L {} end", Thread.currentThread().getId(), from);
log.info("{} GET 3L start", Thread.currentThread().getId());
Account to = repository.findById(3L).get();
log.info("{} GET 3L {} end", Thread.currentThread().getId(), to);
log.info("{} UPDATE balance start 1L", Thread.currentThread().getId());
from.setBalance(from.getBalance() - 50);
log.info("{} UPDATE balance end 1L {}", Thread.currentThread().getId(), from);
log.info("{} UPDATE balance start 3L", Thread.currentThread().getId());
to.setBalance(to.getBalance() + 50);
log.info("{} UPDATE balance end 3L {}", Thread.currentThread().getId(), to);
// log.info("{} SLEEPING for 10 seconds", Thread.currentThread().getId());
// Thread.sleep(10000);
log.info("{} SAVING 1L start", Thread.currentThread().getId());
Account savedFrom = repository.save(from);
log.info("{} SAVING 1L end {}", Thread.currentThread().getId(), savedFrom);
log.info("{} SAVING 3L start", Thread.currentThread().getId());
Account savedTo = repository.save(to);
log.info("{} SAVING 3L end {}", Thread.currentThread().getId(), savedTo);
log.info("{} EVERYTHING COMPLETED", Thread.currentThread().getId());
}
@Transactional
public Account read() {
log.info("Trying to read account");
Account account = repository.findById(1L).get();
// account.setBalance(account.getBalance() + 20);
// repository.save(account);
log.info("Account read success {}", account);
return account;
}
}
======================================================================================================================================================
Update etdiyimiz zaman X lock (write lock) iwe duwur. Read etdiyimiz zaman ise S lock (read lock) iwe duwur.
===========================================================================
public void transferProxy(Long fromId, Long toId, Double amount) throws Exception {
EntityManager em = entityManagerFactory.createEntityManager();
boolean success = false; // Track whether the transaction should be committed
em.getTransaction().begin();
try {
Account from = em.find(Account.class, fromId);
Account to = em.find(Account.class, toId);
transfer(from, to, amount); // Perform the transfer logic
em.flush(); // Synchronize changes with the database
success = true; // Mark transaction as successful
} catch (RuntimeException e) {
em.getTransaction().rollback(); // Rollback in case of exception
throw e; // Re-throw exception after rollback
} finally {
if (success) {
em.getTransaction().commit(); // Commit only if no rollback occurred
}
em.close(); // Ensure EntityManager is closed
}
}
Комментарии
Отправить комментарий