[jboss-user] [Persistence, JBoss/CMP, Hibernate, Database] - Re: [JBoss-4.0.3][SQLServer] LockAcquisitionException blocks

[ceciiil]

2 reasons : 

1) SQL Server sends "transaction started" and "transaction rolled back/committed" messages to the driver "in band" with a query's execution results (update counts, result sets, errors).  The driver can't "see" the messages until the results that precede them have been processed.  So once a transaction has been started, if a statement's execution causes a commit or rollback, the driver will think the transaction is still active until the statement's results have been processed. More here http://blogs.msdn.com/jdbcteam/archive/2009/02/24/the-server-failed-to-resume-the-transaction-why.aspx. So the solution is to configure your datasource with a request to always check the connection : 

  | <check-valid-connection-sql>SELECT 1 </check-valid-connection-sql>
  | 
Believe it or not, the above select works with SQLServer2005. 


2) When setting snapshot mode on SQLServer, if you use Hibernate (as we do) as ORM you need to set the hibernate.connection.isolation to snapshot mode value (4096) :

  | hibernate.connection.isolation = 4096
  | 
More here http://www.hibernate.org/382.html

View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=4221611#4221611

Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=4221611