Jasper SQL deadlock error

[saky]

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.

I got the above error in my jasper report mail. The query that is used in the report is quite complicated. 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

 

Since I am using SQL query as datasource, I added "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ " before every Select statement in the report XML. But it throws exception on the query and considers it not valid.
 

Has it happened to anyone in the Jasper reports? Does anyone know if there is a solution exist to the problem?

Post Edited by saky at 05/06/2010 11:03

[katherine.vierra]

PLEASE SOMEONE ANSWER THIS QUESTION!!!

[katherine.vierra]

I know this is probably far too late to help the OP, but thought I'd post the solution I am using in case anyone else has this issue.

Jasper will not accept the SET TRANSACTION ISOLATION LEVEL REPEATABLE READ in the query window.  (we use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED).  In either case, Jasper will not accept that.  Any statement must return a dataset.

To get around this we created a SQL stored procedure which sets the transaction isolation level before accepting the parameters from Jasper and returning a dataset.

In the report jrxml/query window you would have a statement like:  EXEC storedProcedureName $P{param1}, $P{param2}

You will have to change a config file to allow the execution of a stored procedure as it is not by allowed by default in Jasper.  You can do that in WEB-INF/classes/esapi/validation.properties

Look for the line: Validator.ValidSQL=

Mine now looks like the following:

Validator.ValidSQL=(?is)^\s*(select|call|exec(ute)?)\s+[^;]+;?\s*$