Java Community | Help. Code. Learn.•2w ago

left fetch join creates race condition with optimistic locking

if i use query to left join authors books the test suddenly doesnt pass made a demo for simplicity

@Service
@RequiredArgsConstructor
public class BookService {
    private final BookRepository repository;
    private final AuthorRepository authorRepository;
    private final BookMapper mapper;

    @Transactional
    public void addBook(long id, CreateBookCommand command) {
        Author author = authorRepository.findWithLockingById(id)
                .orElseThrow(() -> new EntityNotFoundException(MessageFormat
                        .format("Author with id={0} not found", id)));
        if (author.getBooks().isEmpty()) {
            Book book = mapper.mapFromCommand(command);
            book.setAuthor(author);
            repository.save(book);
        } else {
            throw new RuntimeException("Author cannot have more than 1 book");
        }
    }
}

@Service
@RequiredArgsConstructor
public class BookService {
    private final BookRepository repository;
    private final AuthorRepository authorRepository;
    private final BookMapper mapper;

    @Transactional
    public void addBook(long id, CreateBookCommand command) {
        Author author = authorRepository.findWithLockingById(id)
                .orElseThrow(() -> new EntityNotFoundException(MessageFormat
                        .format("Author with id={0} not found", id)));
        if (author.getBooks().isEmpty()) {
            Book book = mapper.mapFromCommand(command);
            book.setAuthor(author);
            repository.save(book);
        } else {
            throw new RuntimeException("Author cannot have more than 1 book");
        }
    }
}

@Entity
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class Author {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    private String firstName;

    private String lastName;

    @OneToMany(mappedBy = "author", fetch = FetchType.EAGER)
    private Set<Book> books;
}

@Entity
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class Author {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    private String firstName;

    private String lastName;

    @OneToMany(mappedBy = "author", fetch = FetchType.EAGER)
    private Set<Book> books;
}

@Entity
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class Book {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    private String title;

    private String description;

    private long price;

    @ManyToOne
    private Author author;
}

@Entity
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class Book {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    private String title;

    private String description;

    private long price;

    @ManyToOne
    private Author author;
}

74 Replies

JavaBot•2w ago

⌛ This post has been reserved for your question.

Hey @ayylmao123xdd! Please use /close or the Close Post button above when your problem is solved. Please remember to follow the help guidelines. This post will be automatically marked as dormant after 300 minutes of inactivity.

TIP: Narrow down your issue to simple and precise questions to maximize the chance that others will reply in here.

ayylmao123xddOP•2w ago

test

message.txt

ayylmao123xddOP•2w ago

public interface AuthorRepository extends JpaRepository<Author, Long> {
    @Query("SELECT a FROM Author a LEFT JOIN FETCH a.books WHERE a.id = :id")
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    Optional<Author> findWithLockingById(long id);
}

public interface AuthorRepository extends JpaRepository<Author, Long> {
    @Query("SELECT a FROM Author a LEFT JOIN FETCH a.books WHERE a.id = :id")
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    Optional<Author> findWithLockingById(long id);
}

and the repo somehow the left join makes two books be added to the same author even though theres a check for it to not happen

dan1st•2w ago

that's the code with pessemistic locking How are you checking the version with optimistic locking? What are you actually modifying? How are you making the DB request?

ayylmao123xddOP•2w ago

oh typo in the thread name should be pessimistic locking

dan1st•2w ago

So you have a race condition with pessimistic locking?

ayylmao123xddOP•2w ago

yea after adding the query with left join fetch theres 2 books for the same author even though theres a check in repository to only allow one its like left join fetch disables pessimistic locking

dan1st•2w ago

That statement just locks the Author AFAIK Spring is doing exactly what you told it to do

ayylmao123xddOP•2w ago

yea but in that case it shouldnt let two threads add a book at once if pessimistic write prevents two threads from reading data at a time

dan1st•2w ago

@Lock is only for the query you are executing AFAIK

ayylmao123xddOP•2w ago

shouldnt it lock for the whole transaction tho

dan1st•2w ago

Where does it say that for @lock?

ayylmao123xddOP•2w ago

ok nvm

dan1st•2w ago

Did you set a PessimisticLockScope?

ayylmao123xddOP•2w ago

no i was reading about that but didnt check where to set it

dan1st•2w ago

actually no I think that's something slightly different that would lock both Author and Book

ayylmao123xddOP•2w ago

yea and i need the lock to work for the whole transaction ig its quite weird considering it seems to lock for the whole transaction when i dont use the query even with eager fetching the test passes i got open in view disabled btw

dan1st•2w ago

How is addBook called?

ayylmao123xddOP•2w ago

@RestController
@RequestMapping("/api/v1/authors")
@RequiredArgsConstructor
public class AuthorController {
    private final BookService service;

    @ResponseStatus(HttpStatus.CREATED)
    @PostMapping("/{id}/books")
    public void addBook(@PathVariable long id, @RequestBody CreateBookCommand command) {
        service.addBook(id, command);
    }
}

@RestController
@RequestMapping("/api/v1/authors")
@RequiredArgsConstructor
public class AuthorController {
    private final BookService service;

    @ResponseStatus(HttpStatus.CREATED)
    @PostMapping("/{id}/books")
    public void addBook(@PathVariable long id, @RequestBody CreateBookCommand command) {
        service.addBook(id, command);
    }
}

dont mind the names

dan1st•2w ago

How did you configure the test such that both threads can do stuff at the same time? oh nvm it's just mockmvc

ayylmao123xddOP•2w ago

ye so kinda weird

dan1st•2w ago

Can you log the text "start" before findWithLockingById() and "end" after save()? And then show what exactly is logged in what order

ayylmao123xddOP•2w ago

ye doin that now

Hibernate: insert into author (first_name,last_name,id) values (?,?,default)
start
start
Hibernate: select a1_0.id,b1_0.author_id,b1_0.id,b1_0.description,b1_0.price,b1_0.title,a1_0.first_name,a1_0.last_name from author a1_0 left join book b1_0 on a1_0.id=b1_0.author_id where a1_0.id=? for update
Hibernate: select a1_0.id,b1_0.author_id,b1_0.id,b1_0.description,b1_0.price,b1_0.title,a1_0.first_name,a1_0.last_name from author a1_0 left join book b1_0 on a1_0.id=b1_0.author_id where a1_0.id=? for update
Hibernate: insert into book (author_id,description,price,title,id) values (?,?,?,?,default)
end
Hibernate: insert into book (author_id,description,price,title,id) values (?,?,?,?,default)
end

Hibernate: insert into author (first_name,last_name,id) values (?,?,default)
start
start
Hibernate: select a1_0.id,b1_0.author_id,b1_0.id,b1_0.description,b1_0.price,b1_0.title,a1_0.first_name,a1_0.last_name from author a1_0 left join book b1_0 on a1_0.id=b1_0.author_id where a1_0.id=? for update
Hibernate: select a1_0.id,b1_0.author_id,b1_0.id,b1_0.description,b1_0.price,b1_0.title,a1_0.first_name,a1_0.last_name from author a1_0 left join book b1_0 on a1_0.id=b1_0.author_id where a1_0.id=? for update
Hibernate: insert into book (author_id,description,price,title,id) values (?,?,?,?,default)
end
Hibernate: insert into book (author_id,description,price,title,id) values (?,?,?,?,default)
end

seems like 2 read at the same time even tho its pessimistic write lmao pessimistic read doesnt help just checked just made a shocking discovery

public interface AuthorRepository extends JpaRepository<Author, Long> {
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @Query("SELECT a FROM Author a WHERE a.id = :id")
    Optional<Author> findWithLockingById(long id);
}

public interface AuthorRepository extends JpaRepository<Author, Long> {
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @Query("SELECT a FROM Author a WHERE a.id = :id")
    Optional<Author> findWithLockingById(long id);
}

if i do this the test still doesnt pass so ig the query annotation itself is the problem and use eager fetch same behavior when using entity graph

dan1st•2w ago

I tried to reproduce the issue and I get 0 books inserted because of the test DB setup I think

ayylmao123xddOP•2w ago

h2 db

dan1st•2w ago

yes I'm using H2 but the thing is that normally, each thread for testing is isolated from the others

ayylmao123xddOP•2w ago

ye i was thinking it might be something with the cache before but idk what it can be now

dan1st•2w ago

ah so the test passes for me

ayylmao123xddOP•2w ago

😱 what did you change

dan1st•2w ago

I only made parts simpler ah rerunning made it fail

ayylmao123xddOP•2w ago

disaster tbh

dan1st•2w ago

nah

ayylmao123xddOP•2w ago

hmmmmmmmmmmmmmmmm

dan1st•2w ago

ok so the locking part doesn't seem to be the issue for me

ayylmao123xddOP•2w ago

 @Query("SELECT a FROM Author a LEFT JOIN a.books WHERE a.id = :id")

 @Query("SELECT a FROM Author a LEFT JOIN a.books WHERE a.id = :id")

i deleted the fetch from the query and it seems to work ig can you check if it helped for you

dan1st•2w ago

Where did you put the START log?

ayylmao123xddOP•2w ago

 @Transactional
    public void addBook(long id, CreateBookCommand command) {
        System.out.println("start");
        Author author = authorRepository.findWithLockingById(id)
                .orElseThrow(() -> new EntityNotFoundException(MessageFormat
                        .format("Author with id={0} not found", id)));
        if (author.getBooks().isEmpty()) {
            Book book = mapper.mapFromCommand(command);
            book.setAuthor(author);
            repository.save(book);
            System.out.println("end");
        } else {
            throw new RuntimeException("Author cannot have more than 1 book");
        }
    }

 @Transactional
    public void addBook(long id, CreateBookCommand command) {
        System.out.println("start");
        Author author = authorRepository.findWithLockingById(id)
                .orElseThrow(() -> new EntityNotFoundException(MessageFormat
                        .format("Author with id={0} not found", id)));
        if (author.getBooks().isEmpty()) {
            Book book = mapper.mapFromCommand(command);
            book.setAuthor(author);
            repository.save(book);
            System.out.println("end");
        } else {
            throw new RuntimeException("Author cannot have more than 1 book");
        }
    }

dan1st•2w ago

put the start log after the findWithLockingById

ayylmao123xddOP•2w ago

Hibernate: insert into author (first_name,last_name,id) values (?,?,default)
Hibernate: select a1_0.id,a1_0.first_name,a1_0.last_name from author a1_0 left join book b1_0 on a1_0.id=b1_0.author_id where a1_0.id=? for update
Hibernate: select a1_0.id,a1_0.first_name,a1_0.last_name from author a1_0 left join book b1_0 on a1_0.id=b1_0.author_id where a1_0.id=? for update
start
Hibernate: select b1_0.author_id,b1_0.id,b1_0.description,b1_0.price,b1_0.title from book b1_0 where b1_0.author_id=?
Hibernate: insert into book (author_id,description,price,title,id) values (?,?,?,?,default)
end
start
Hibernate: select b1_0.author_id,b1_0.id,b1_0.description,b1_0.price,b1_0.title from book b1_0 where b1_0.author_id=?

Hibernate: insert into author (first_name,last_name,id) values (?,?,default)
Hibernate: select a1_0.id,a1_0.first_name,a1_0.last_name from author a1_0 left join book b1_0 on a1_0.id=b1_0.author_id where a1_0.id=? for update
Hibernate: select a1_0.id,a1_0.first_name,a1_0.last_name from author a1_0 left join book b1_0 on a1_0.id=b1_0.author_id where a1_0.id=? for update
start
Hibernate: select b1_0.author_id,b1_0.id,b1_0.description,b1_0.price,b1_0.title from book b1_0 where b1_0.author_id=?
Hibernate: insert into book (author_id,description,price,title,id) values (?,?,?,?,default)
end
start
Hibernate: select b1_0.author_id,b1_0.id,b1_0.description,b1_0.price,b1_0.title from book b1_0 where b1_0.author_id=?

dan1st•2w ago

see, it does pessimistic locking it's just the isolation level probably

ayylmao123xddOP•2w ago

i removed the fetch and now the tests pass somehow well thats interesting nvm it just stopped working

dan1st•2w ago

Which fetch?

ayylmao123xddOP•2w ago

the LEFT JOIN FETCH

dan1st•2w ago

ah that

ayylmao123xddOP•2w ago

but now it shows the tests as failed again it was working for a few runs and now died maybe its the test thats badly written nevermind i had fetch back to test if it works

dan1st•2w ago

I enabled debug logging for my reproducer which gives me some additional information

ayylmao123xddOP•2w ago

so try to run it with fetch and without fetch the results should be different

dan1st•2w ago

To me, it seems like the issue is that findWithLockingById() is just getting an old value even though the other thread already completed And I think this may be related to test transactions

ayylmao123xddOP•2w ago

i think it was because of the FETCH at least for me when i do it like this

@Query("SELECT a FROM Author a LEFT JOIN a.books WHERE a.id = :id")

@Query("SELECT a FROM Author a LEFT JOIN a.books WHERE a.id = :id")

it seems to pass with this

    @Query("SELECT a FROM Author a LEFT JOIN FETCH a.books WHERE a.id = :id")

    @Query("SELECT a FROM Author a LEFT JOIN FETCH a.books WHERE a.id = :id")

2 books in repo

dan1st•2w ago

it also happens without FETCH for me but it might happen more often with FETCH

ayylmao123xddOP•2w ago

ill rerun it a few times without fetch ran it 10 times all passed

dan1st•2w ago

it seems to work perfectly with hsqldb try that instead of H2

ayylmao123xddOP•2w ago

ok lemme swap lol for me its the opposite hsql has 2 books with h2 1

dan1st•2w ago

Can you try with a proper DB and testcontainers?

ayylmao123xddOP•2w ago

after swapping back to h2 works properly btw yea ill check with mysql

dan1st•2w ago

btw I'm using LEFT JOIN FETCH For me, this works with both HSQLDB and MySQL (testcontainers)

ayylmao123xddOP•2w ago

i ran it with mysql and both versions work

dan1st•2w ago

But with all DBs, it's running sequentially

ayylmao123xddOP•2w ago

with fetch and without

dan1st•2w ago

the difference is that it gets the old value with H2 btw I'm using this for logging

logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE
logging.level.org.springframework.orm.jpa=DEBUG
logging.level.org.springframework.transaction=DEBUG

logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE
logging.level.org.springframework.orm.jpa=DEBUG
logging.level.org.springframework.transaction=DEBUG

ayylmao123xddOP•2w ago

ill run again in hsqldb but its weird that in mysql both cases work and in h2 and hsql only 1 case and its the opposite one to them

dan1st•2w ago

testing with H2 is weird in general What DB are you using in production?

ayylmao123xddOP•2w ago

mysql lmao

dan1st•2w ago

Can you try H2 with a file DB?

ayylmao123xddOP•2w ago

now in hsql both scenarios dont work ye ill grab one without fetch passes with fetch doesnt pass

dan1st•2w ago

yeah also fails for me without FETCH yeah H2 do be weird

ayylmao123xddOP•2w ago

lets just blame the db

dan1st•2w ago

https://stackoverflow.com/a/74492684/10871900

Stack Overflow

Entity fetched with pessimistic locking has outdated value

im testing some kind of small banking-ish service with mysql database. im using 2nd level cache hibernate (possible source of problem?). the data structure basically consists of 2 tables, a tab...

dan1st•2w ago

or https://shouldiblamecaching.com/

dan1st•2w ago

https://stackoverflow.com/a/42530490/10871900

Stack Overflow

Spring data PESSIMISTIC_WRITE returns old DB value

I have this method in my interface which extends CrudRepository: @Lock(LockModeType.PESSIMISTIC_WRITE) Voucher findTop1ByUsedFalseAndProductOrderByIdAsc(Product product); And this component: @

dan1st•2w ago

With H2, it seems like it first loads the value and then locks the DB

ayylmao123xddOP•2w ago

yea i was reading these ok gonna mark as solved

ayylmao123xddOP•2w ago

https://tenor.com/view/cat-gif-7898695231799564452

Tenor

JavaBot•2w ago

Post Closed

This post has been closed by <@452882919325827074>.

Gaming

Programming

left fetch join creates race condition with optimistic locking

Did you find this page helpful?