Jasper error: Caused by SQLServerException: Transaction (Process ID 58) was deadlocked on thread | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Question

I got the above error in my jasper report mail. The query that is used in the report is quite complicated (for me). Reading different posts I conclude that to solve this the I have to change the query to

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
BEGIN TRANSACTION
... my query ...
COMMIT TRANSACTION

? I wonder if this is the correct way to solve the error and that if it has any side effects? Has it happened to anyone in the Jasper reports? Does anyone know if there is a better solution exist to the problem?

(Although that I have not yet tested the above solution, if anyone can give any insight on this will be helpful.)

Answer 1

When defining the connection to use with JasperReports. I usually set the transaction isolation like following.

//get the connection object (or create it, however you do it)
Connection conn = getConnectionToDatabase();

//set Transaction Isolation
conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITED);

//Also, set Holdability to HOLD (holds the ResultSet when connection is committed.
conn.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);

Note: check variables/methods names, I wrote this without an IDE.

Answer 2

edit: Use SNAPSHOT isolation with SQL Server.

Saky, use READ UNCOMMITTED, not REPEATABLE READ. If you have a database that supports multiversion row concurrency, you might be able to use READ COMMITTED, or as in MS SQL, SNAPSHOT isolation, which is stronger than READ COMMITTED because it makes the query return results that were committed at the time the query is started, so any partial changes to some interrelated rows will not have inconsistencies, but it will not block other any other queries or changes to records.

REPEATABLE READ is not going to work well for concurrency on some RDBMS platforms, so you can expect to get deadlocks. What is your RDBMS?