BMP and trasaction deadlock
I am trying to make BMP work for JSQLConnect + MSSqlServer2000 + JBoss2.4.4 but always have a deadlock problem. Everything worked fine when there's only one client, but with 3+ clients creating beans, there are always exception in JBoss console saying a process was deadlocked with another process and was chosen as victim. The result is, no matter how many beans I try to create, only the first one or two are created, all others were deadlocked and couldn't create a BMP. It seems that the process which get first chance to create a record in DB didn't release the lock and all other processes can't create records in DB.
To try to solve this problem, I closed the connection and PrepareStatment after every sql executing statement, and call con.commite() after every one of them. But I still have this problem. The weird thing is, I use the same datasource for my CMP, and it never had a problem for up to 12 concurrent client requests.
I tried to modify the commit option in jboss.xml to A or B but it doesn't seem to make a difference. I am thinking there must be a way to commit the transaction or release the lock after each sql execution, but don't know how, could someone offer me some help? Thank you very much.
The stack trace of exception is:
[INFO,Default] ps is closed.
[INFO,Default] connection is closed.
This is my print out and it shows the connection is closed as expected.
[ERROR,Default] com.jnetdirect.jsql.u: Transaction Process ID 57) was deadlocke
d on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
[ERROR,Default] at com.jnetdirect.jsql.as.a Unknown Source)
[ERROR,Default] at com.jnetdirect.jsql.ae.f
[ERROR,Default] at com.jnetdirect.jsql.ae.int[ERROR,Default] at com.jnetdirect.jsql.ae.new[ERROR,Default] at com.jnetdirect.jsql.am.executeUpdate(Unknown Source)
[ERROR,Default] at org.jboss.pool.jdbc.PreparedStatementInPool.executeUp
date(Unknown Source)
[ERROR,Default] at ejbserverperformance.Sun_BMPEntity.ejbStore(Sun_BMPEn
tity.java:112)
[ERROR,Default] at java.lang.reflect.Method.invoke(Native Method)
[ERROR,Default] at org.jboss.ejb.plugins.BMPPersistenceManager.storeEntity(BMPPersistenceManager.java:361)
[ERROR,Default] at org.jboss.ejb.EntityContainer.storeEntity(EntityContainer.java:253)