I use message-driven bean to asynchronously send newsletters. To avoid restarting process from the beginning in case of any error, I save the recipients in database table, and remove row by row after email has been sent. To not mark my mail server as spammer I send it in chunks of 90 per minute. My recipients list is quite big, about 30k addresses. The code works perfect, but only few minutes... Then transaction is timed out, deleting of already sent recipients is not commited and the process begins again! That of course results in delivering the same message multiple times to the same recipients.
I tried setting autocommit to false and forcing commit before thread going sleep, but I kept getting error "cannot commit managed transaction".
As you have a CMT bean, you must not use the commit, setAutoCommit or rollback methods.
It looks as if it is taking too long for the transaction to run. Typically, an EJB container will have a default of, say, 30 seconds for a transaction to last. If the transaction is still running after this period of time, the container will rollback the transaction.
You can increase the transaction period, but it is best to find out what is taking the time. (I don't think you should normally increase the transaction period by much, if at all.) For instance, how long does each PreparedStatement take to execute? You may eventually find that you can only send it in chunks of 30 per minute.
I've a few comments on your code.
You are swallowing exceptions. Never do this as you will lose valuable information if something goes wrong. At the least, log the getMessage().
You are not closing each PreparedStatement. This means that the PreparedStatement will most likely be closed on garbage collection, which could result in cursor leaks and/or a thread causing table locking.
You are getting a Connection three times. You only need to get one Connection from the pool, which will be returned to the pool when closed.
Incidentally, is your transaction attribute set to Required?
SCJP 1.4, SCWCD 1.3, SCBCD 1.3
Joined: Mar 14, 2006
Thanks for the reply and the comments.
As I written before I put thread sleep for 60 seconds after sending 90 emails. Probably even if I would send all of them at once, about 30 000 emails transaction would expire.
Maybe my solution is wrong for this kind of action?
Joined: Sep 29, 2002
Note that threading is forbidden by the EJB specification.
Also, won't the delay of 60 seconds guarantee that the transaction will timeout?
You need to send each batch of emails in a separate transaction. What is important is that every batch of emails must be processed within the transaction timeout period, which might be 30 seconds.