select for update

CREATE TABLE `account` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `balance` decimal(10,0) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

spring.application.name=MyApp
server.port=9090
# MySQL
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/mcc?useUnicode=true&characterEncoding=utf8
spring.datasource.username=root
spring.datasource.password=

import jakarta.persistence.*;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.math.BigDecimal;

@Entity
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "account")
public class Account {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private BigDecimal balance;
}

import org.boot.entity.Account;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

public interface AccountDao extends JpaRepository<Account, Long> {

    @Query(value = "select c.* from account c where c.id = %?1 FOR UPDATE", nativeQuery = true)
    Account selectForUpdate(Long id);
}



import java.math.BigDecimal;

public interface AccountService {
    void updatePayment(Long accountId, BigDecimal amount);

    void updatePaymentWithDbLock(Long accountId, BigDecimal amount);
}



import jakarta.annotation.Resource;
import org.boot.dao.AccountDao;
import org.boot.entity.Account;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.io.PrintStream;
import java.math.BigDecimal;

@Service
public class AccountServiceImpl implements AccountService {

    @Resource
    private AccountDao accountDao;

    @Override
    public void updatePayment(Long accountId, BigDecimal amount) {
        Account account = accountDao.getReferenceById(accountId);
        BigDecimal newBalance = account.getBalance().add(amount);
        account.setBalance(newBalance);
        accountDao.save(account);
    }

    @Override
    @Transactional(rollbackFor = Throwable.class)
    public void updatePaymentWithDbLock(Long accountId, BigDecimal amount) {
        Account account = accountDao.selectForUpdate(accountId);
        BigDecimal newBalance = account.getBalance().add(amount);
        account.setBalance(newBalance);
        accountDao.save(account);
    }
}


import jakarta.annotation.Resource;
import org.boot.service.AccountService;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.math.BigDecimal;

@RestController
@RequestMapping("/account")
public class AccountController {

    @Resource
    AccountService accountService;

    // http://localhost:9090/account/pay
    @RequestMapping("/pay")
    String pay() {
        accountService.updatePayment(1L, BigDecimal.valueOf(100.0));
        return "1";
    }

    @RequestMapping("/pay2")
    String pay2() {
        accountService.updatePaymentWithDbLock(2L, BigDecimal.valueOf(100.0));
        return "2";
    }
}

# pip install requests

import threading
import requests


def fetch_url(url):
    response = requests.get(url)
    print(f"Request to {url} status code: {response.status_code}")


if __name__ == "__main__":
    urls = ['http://localhost:9090/account/pay' for _ in range(100)]
    threads = []
    for url in urls:
        thread = threading.Thread(target=fetch_url, args=(url,))
        thread.start()
        threads.append(thread)
    for thread in threads:
        thread.join()