I have determined that the problem is more general than I have stated.
Simply put: there is an SQL Server request that is not completing and it is blocking any requests to the same table made after it. I see a page level lock. I can see a statement that is blocking/sleeping and then the one that hangs/is blocked by the first. They are both requesting a lcok on the same page (should be okay). The first statement is an insert generated by the EJB3 framework. Why would it be in a state of sleeping/awaiting command? I assume there is some incompatibility between EJB3 subsystem and SQL server or I have something miss-configured.
From the SQL server management studio:
THIS ONE IS status=sleeping command = awaiting command
(@P0 int,@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 int,@P4 int)insert into eventfactory (srcEvtCode, factoryClass, parms, resultEvent, userModel) values ( @P0 , @P1 , @P2 , @P3 , @P4 ) SELECT SCOPE_IDENTITY() AS ID
THIS ONE is waiting on above one to complete: status=suspended command=select
()select eventfacto0_.ik as ik0_, eventfacto0_.srcEvtCode as srcEvtCode0_, eventfacto0_.factoryClass as factoryC2_0_, eventfacto0_.parms as parms0_, eventfacto0_.resultEvent as resultEv6_0_, eventfacto0_.userModel as userModel0_ from eventfactory eventfacto0_ order by eventfacto0_.srcEvtCode asc
Makes no sense. There is nothing else happening in the SQL server.
I am not an expert at this, but i think this is what is happening:
- SLSB calls some method with transaction T1. In this T1 transaction, the entity is inserted/updated (but not yet commited because the transaction is not complete). During this flow, the @PostUpdate lifecycle method is called in the same transaction context T1.
- Now this @PostUpdate lifecycle method calls a SLSB method probably which has the @RequiresNew transaction attribute. As a result a new transaction T2 is created and in this T2 transaction context the SLSB issues a select statement on the same entity.
Since these 2 queries are being fired from 2 different transaction contexts (remember that the transaction T1 is not yet committed), this probably is resulting in this issue.
You could try changing the transaction attribute of the second SLSB method to @Required and see if it fixes the problem.
Adding this to the datasource .xml file fixed the problem:
(XML tags removed)
This is okay for my app. Not for all apps.