Postgresql - Persistence operations not committed when using @Scheduled annotation

SalvatoreMontagna
June 21, 2024
201 views
0 votes
2 Answers

This Spring service, EmailsOutgoingService, is responsible for processing outgoing emails and it is scheduled to run every 60 seconds using the @Scheduled(fixedRate = 60000) annotation.
Basically it checks the status of the email in the queue and performs operations on the existing record or creates another one.
The problem is that the "repository.save" transaction is not committed to the database.

@Service
public class EmailsOutgoingService {

    @Autowired
    private JavaMailSender emailSender;

    @Autowired
    private EmailsOutgoingRepository repository;

    @Scheduled(fixedRate = 60000)
    @Transactional
    public void processEmails() {
        System.out.println("Processing emails...");
        List<EmailsOutgoing> pendingEmails = repository.findByDeliveryStatus(EmailsOutgoing.STATUS_PENDING);
        List<EmailsOutgoing> failedEmails = repository.findByDeliveryStatus(EmailsOutgoing.STATUS_FAILED);

        for (EmailsOutgoing pendingEmail : pendingEmails) {
            System.out.println("Processing pending email with ID: " + pendingEmail.getId());
            sendEmail(pendingEmail);
        }

        for (EmailsOutgoing failedEmail : failedEmails) {
            System.out.println("Processing failed email with ID: " + failedEmail.getId());
            retryFailedEmail(failedEmail);
        }
    }

    public void sendEmail(EmailsOutgoing email) {
        try {
            System.out.println("Sending email...");
            SimpleMailMessage message = new SimpleMailMessage();
            message.setTo(email.getEmailReceivers().split(","));
            message.setSubject(email.getEmailSubject());
            message.setText(email.getEmailBody());
            emailSender.send(message);
            email.setDeliveryStatus(EmailsOutgoing.STATUS_DELIVERED);
            repository.save(email);
            System.out.println("Email sent successfully, status updated to DELIVERED with ID: " + email.getId());
        } catch (Exception e) {
            System.out.println("Failed to send email: " + e.getMessage());
            email.setDeliveryStatus(EmailsOutgoing.STATUS_FAILED);
            email.setEmailMessage(e.getMessage());
            repository.save(email);
            System.out.println("Email failed, status updated to FAILED with ID: " + email.getId());

            EmailsOutgoing newEmail = new EmailsOutgoing();
            newEmail.setEmailSubject(email.getEmailSubject());
            newEmail.setEmailBody(email.getEmailBody());
            newEmail.setDeliveryStatus(EmailsOutgoing.STATUS_PENDING);
            newEmail.setEmailEntity(email.getEmailEntity());
            newEmail.setClientCode(email.getClientCode());
            newEmail.setEmailReceivers(email.getEmailReceivers());
            repository.save(newEmail);

            System.out.println("Created new pending email with ID: " + newEmail.getId());
        }
    }

    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void retryFailedEmail(EmailsOutgoing failedEmail) {
        System.out.println("Retrying failed email...");
        EmailsOutgoing newEmail = new EmailsOutgoing();
        newEmail.setEmailSubject(failedEmail.getEmailSubject());
        newEmail.setEmailBody(failedEmail.getEmailBody());
        newEmail.setDeliveryStatus(EmailsOutgoing.STATUS_PENDING);
        newEmail.setEmailEntity(failedEmail.getEmailEntity());
        newEmail.setClientCode(failedEmail.getClientCode());
        newEmail.setEmailReceivers(failedEmail.getEmailReceivers());

        repository.save(newEmail);
        System.out.println("Created new pending email with ID: " + newEmail.getId());

        sendEmail(newEmail);
    }
}

If I try to call the method from postman everything works normally. I concluded that @Scheduled is not committing the transaction. How can the problem be solved? I also tried creating a separate class with @Scheduled that calls the service class that does the persistence operations but it’s not working:

Calling the method with @Scheduler from another class:

EmailsOutgoingScheduler:

@Component
public class EmailsOutgoingScheduler {

    @Autowired
    private EmailsOutgoingService emailsOutgoingService;

    @Scheduled(fixedRate = 60000) 
    public void checkForNewNotifications() {
        System.out.println("Checking for new notifications...");
        emailsOutgoingService.processEmails();
    }
}

EmailsOutgoingService:

@Service
public class EmailsOutgoingService {

    @Autowired
    private JavaMailSender emailSender;

    @Autowired
    private EmailsOutgoingRepository repository;

    public void processEmails() {
        System.out.println("Processing emails...");
        List<EmailsOutgoing> pendingEmails = repository.findByDeliveryStatus(EmailsOutgoing.STATUS_PENDING);
        List<EmailsOutgoing> failedEmails = repository.findByDeliveryStatus(EmailsOutgoing.STATUS_FAILED);

        for (EmailsOutgoing pendingEmail : pendingEmails) {
            System.out.println("Processing pending email with ID: " + pendingEmail.getId());
            sendEmail(pendingEmail);
        }

        for (EmailsOutgoing failedEmail : failedEmails) {
            System.out.println("Processing failed email with ID: " + failedEmail.getId());
            retryFailedEmail(failedEmail);
        }
    }

    @Transactional
    public void sendEmail(EmailsOutgoing email) {
        try {
            System.out.println("Sending email...");
            SimpleMailMessage message = new SimpleMailMessage();
            message.setTo(email.getEmailReceivers().split(","));
            message.setSubject(email.getEmailSubject());
            message.setText(email.getEmailBody());
            emailSender.send(message);
            email.setDeliveryStatus(EmailsOutgoing.STATUS_DELIVERED);
            repository.save(email);  // Update status to delivered
            System.out.println("Email sent successfully, status updated to DELIVERED with ID: " + email.getId());
        } catch (Exception e) {
            System.out.println("Failed to send email: " + e.getMessage());
            email.setDeliveryStatus(EmailsOutgoing.STATUS_FAILED);
            email.setEmailMessage(e.getMessage());
            repository.save(email);  // Update status to failed
            System.out.println("Email failed, status updated to FAILED with ID: " + email.getId());

            // Create a new pending email record to retry
            EmailsOutgoing newEmail = new EmailsOutgoing();
            newEmail.setEmailSubject(email.getEmailSubject());
            newEmail.setEmailBody(email.getEmailBody());
            newEmail.setDeliveryStatus(EmailsOutgoing.STATUS_PENDING);
            newEmail.setEmailEntity(email.getEmailEntity());
            newEmail.setClientCode(email.getClientCode());
            newEmail.setEmailReceivers(email.getEmailReceivers());
            repository.save(newEmail);

            System.out.println("Created new pending email with ID: " + newEmail.getId());
        }
    }

    @Transactional
    public void retryFailedEmail(EmailsOutgoing failedEmail) {
        System.out.println("Retrying failed email...");
        // The retry logic here is to create a new pending email record and then try sending it
        EmailsOutgoing newEmail = new EmailsOutgoing();
        newEmail.setEmailSubject(failedEmail.getEmailSubject());
        newEmail.setEmailBody(failedEmail.getEmailBody());
        newEmail.setDeliveryStatus(EmailsOutgoing.STATUS_PENDING);
        newEmail.setEmailEntity(failedEmail.getEmailEntity());
        newEmail.setClientCode(failedEmail.getClientCode());
        newEmail.setEmailReceivers(failedEmail.getEmailReceivers());

        repository.save(newEmail);
        System.out.println("Created new pending email with ID: " + newEmail.getId());

        sendEmail(newEmail);
    }
}

Hibernate log:

Checking for new notifications...
[restartedMain] INFO org.springframework.boot.devtools.autoconfigure.ConditionEvaluationDeltaLoggingListener - Condition evaluation unchanged
Processing emails...
[2024-06-19 17:13:32.011] - 23980 DEBUG [scheduling-1] --- org.hibernate.SQL: select emailsoutg0_.pk_email_outgoing as pk_email1_11_, emailsoutg0_.client_code as client_c2_11_, emailsoutg0_.email_outgoing_status as email_ou3_11_, emailsoutg0_.email_outgoing_body as email_ou4_11_, emailsoutg0_.email_outgoing_date as email_ou5_11_, emailsoutg0_.email_outgoing_entity as email_ou6_11_, emailsoutg0_.email_outgoing_message as email_ou7_11_, emailsoutg0_.email_outgoing_receivers as email_ou8_11_, emailsoutg0_.email_outgoing_subject as email_ou9_11_ from emails_outgoing emailsoutg0_ where emailsoutg0_.email_outgoing_status=?
[2024-06-19 17:13:32.034] - 23980 TRACE [scheduling-1] --- org.hibernate.type.descriptor.sql.BasicBinder: binding parameter [1] as [VARCHAR] - [Pending]
[2024-06-19 17:13:32.037] - 23980 DEBUG [scheduling-1] --- org.hibernate.SQL: select emailsoutg0_.pk_email_outgoing as pk_email1_11_, emailsoutg0_.client_code as client_c2_11_, emailsoutg0_.email_outgoing_status as email_ou3_11_, emailsoutg0_.email_outgoing_body as email_ou4_11_, emailsoutg0_.email_outgoing_date as email_ou5_11_, emailsoutg0_.email_outgoing_entity as email_ou6_11_, emailsoutg0_.email_outgoing_message as email_ou7_11_, emailsoutg0_.email_outgoing_receivers as email_ou8_11_, emailsoutg0_.email_outgoing_subject as email_ou9_11_ from emails_outgoing emailsoutg0_ where emailsoutg0_.email_outgoing_status=?
[2024-06-19 17:13:32.038] - 23980 TRACE [scheduling-1] --- org.hibernate.type.descriptor.sql.BasicBinder: binding parameter [1] as [VARCHAR] - [Failed]
Processing pending email with ID: 1
Sending email...
Email failed, status updated to FAILED with ID: 1
[2024-06-19 17:13:32.122] - 23980 DEBUG [scheduling-1] --- org.hibernate.SQL: select nextval ('emails_outgoing_sequence')
Created new pending email with ID: 866

Answers

Chosen as BEST ANSWER

Solution

For those who got trapped using @Scheduled to update data on the database without solution:

I solved bypassing JPA using two PostgreSQL function to create and update records. Nothing changed on the service, I'm just calling functions from the repository using @Procedure annotation.

    @Transactional
    @Procedure(procedureName = "iot.updateOutgoingEmails")
    int updateOutgoingEmails(@Param("p_id") Integer id, 
                             @Param("p_deliveryStatus") String deliveryStatus, 
                             @Param("p_emailMessage") String emailMessage);
    
    @Transactional
    @Procedure(procedureName = "iot.createOutgoingEmails")
    int createOutgoingEmails(@Param("p_emailSubject") String emailSubject,
                             @Param("p_emailBody") String emailBody,
                             @Param("p_emailEntity") String emailEntity,
                             @Param("p_clientCode") String clientCode,
                             @Param("p_emailReceivers") String emailReceivers,
                             @Param("p_deliveryStatus") String deliveryStatus, 
                             @Param("p_emailMessage") String emailMessage);

The update method should be void, but I went through errors when using @Modifying annotation, so I decided to keep a return value to implement this once for all.

(Edit)

I fell into the same trap once.

The transaction is not propagated through Spring proxy and no transaction management happens. You need to call a public method annotated with @Transactional from a different class.

@Autowired
private EmailProcessor email processor;

@Scheduled(fixedRate = 60000)
public void processEmails() {
    System.out.println("Processing emails...");
    emailProcessor.processEmails();
}

@Service
public class EmailProcessor {

    // ...

    @Transactional
    public void processEmails() {
        List<EmailsOutgoing> pendingEmails = repository.findByDeliveryStatus(EmailsOutgoing.STATUS_PENDING);
        List<EmailsOutgoing> failedEmails = repository.findByDeliveryStatus(EmailsOutgoing.STATUS_FAILED);

        for (EmailsOutgoing pendingEmail : pendingEmails) {
            System.out.println("Processing pending email with ID: " + pendingEmail.getId());
            sendEmail(pendingEmail);
        }

        for (EmailsOutgoing failedEmail : failedEmails) {
            System.out.println("Processing failed email with ID: " + failedEmail.getId());
            retryFailedEmail(failedEmail);
        }
    }

    public void sendEmail(EmailsOutgoing email) {
        // code
    }

    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void retryFailedEmail(EmailsOutgoing failedEmail) {
        // code
    }
}

Please signup or login to give your own answer.

Click here to cancel reply.

Postgresql – Persistence operations not committed when using @Scheduled annotation

Answers