Jeremy, Thanks for the reply. It gave us some ideas that we hadn't tried before (mainly CMP2.x). However, the "deadlock" issue remains.
One point that I want to make that I didn't make earlier is that execution never returns to the blocking spid (spid 53 in my previous post), which would allow it to release its lock. The transaction is rolled back because it times out. I have tried using the locking hints UPDLOCK and XLOCK as you suggested with ejb-2.0/cmp2.x along with <track-connection-by-tx> set to true in xa-ds.xml without any success. The test I was using that "deadlocks" with this configuration is slightly different from the one described in my original post. I'm able to get it to lock up more regularly with this test, but I believe the underlying problem is the same. I realize that setting the isolation level to something less restrictive than serializable may also help. However, I think it would be avoiding the real problem, which might show itself again unexpectedly. Has anybody else had unexpected lockups while using sqlserver 2000? As much trouble as it has caused us, I would expect it to affect others as well (unless I have done something completely wrong). However, I haven't been able to find much info on this topic in the forums and other places. Any additional insight about this problem would be welcome. Thanks, Greg -----Original Message----- From: Jeremy Boynes [mailto:[EMAIL PROTECTED] Sent: Saturday, June 07, 2003 10:26 PM To: [EMAIL PROTECTED] Subject: RE: [JBoss-user] cmp and cmt connection sharing deadlock? You haven't actually deadlocked here. spid 58 is blocked waiting for spid 53 to release its lock, but spid 53 is not blocked. If its transaction ends, spid 58 will unblock. However, if spid 53 attempts to get a X lock, then deadlock will occur and one of them will be rolled back at the database level (deadlock victim exception). The solution for this at the database level is to use an update or exclusive lock when reading the row. With SQL 2000 you do this with syntax such as SELECT ... FROM my_table WITH(UPDLOCK) ... WHERE ... or SELECT ... FROM my_table WITH(XLOCK) ... WHERE ... This can be configured fairly easily for CMP2.x by turning on row-locking in jbosscmp-jdbc.xml. However, from the SQL it looks like you are using JAWS which does not have this capability. Turning it on requires reconfiguring your container to use the CMP2.x engine in CMP1.x mode, or changing your EJB to CMP 2.x Regards Jeremy /************************* * Jeremy Boynes * Partner * Core Developers Network *************************/ > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of Suzor, > Greg > Sent: Monday, June 02, 2003 8:55 AM > To: '[EMAIL PROTECTED]' > Subject: [JBoss-user] cmp and cmt connection sharing deadlock? > > > (Short version) > Can deadlocks occur because of improper connection sharing between > transactions? > > (Long version) > I have been working on a large project with several coworkers and have > encountered a deadlock issue that needs to be resolved. While > investigating this problem, I have come up with a fairly simple test > that is capable of producing deadlock. This scenario may or may not be > the same as what occurs > in our large project, but I think they are related. > > For my test environment, I'm using JBoss-3.2.1, j2sdk 1.4.1_02-b06, > JDBC 3.0 > - JSQLConnect 3.30 driver, and SQLServer 2000. The test uses CMP and > CMT. The 'transaction-isolation' in xa-ds.xml has been set to > 'TRANSACTION_SERIALIZABLE'. Also, all 'trans-attribute' tags in > ejb-jar.xml have been set to 'Required'. > > My test consists of a standalone java application (APP) that calls a > stateless session bean (SLSB), which in turn makes 2 calls to an > entity bean (EB). Besides using jndi to look up the various components > at the appropriate times, the logic is as follows: (1) APP makes a > single call to the SLSB; (2) SLSB calls EBHome.findByPrimaryKey(id); > (3) SLSB calls EBRemote.setValue(id, value). Even though this isn't > very complex, it can cause deadlock. > > On one run where my test deadlocked, SQLServer Profiler revealed the > following: > - All spids (if my understanding is correct, they represent a physical > connection to SQLServer) for JBoss are created with isolation level > serializable. > - Spid 53 is created and a transaction is started on it. > - 'SELECT COUNT(*) FROM my_table WHERE id=1' is issued on spid53. This > creates a shared (S) lock on key PK_my_table. According to MS > documentation, since the isolation level is serializable, the S lock > will be held until the current transaction has finished. > - Spids 54, 55, 56, and 57 are created, but not used. > - Spid 58 is created. > - 'SELECT my_table.id,my_table.value FROM my_table WHERE id = 1' is > issued on spid 58. This creates an S lock on key PK_my_table for > spid 58. No problems yet, since the S lock on spid 53 and the S lock > on spid 58 don't conflict. S locks allow reads to occur on other > spids. > - 'UPDATE my_table SET value=100 WHERE id=1' is issued on spid 58. This > is where things go wrong. Spid 58 now needs an exclusive (X) lock on > key PK_my_table to carry out the update. However, this can't happen > because spid 53 still has its S lock on that key. This behavior > between > S locks and X locks on different spids is documented by MS. Since > spid 58 can't get its X lock because spid 53 won't release its S lock > (it isn't being used anymore), deadlock occurs. > > This test application does not always deadlock. If all of the work is > done within one spid, then the shared (S) lock is converted into an > exclusive (X) lock successfully, and the update is allowed. > > I have seen similar deadlock behavior in our internal semi-production > project. However, the deadlock might not occur for several hours. It > does seem though that the problem is usually related to transactions > and connections (spids) getting mixed up. > > I have tried other jdbc drivers, SapDB, and set TrackConnectionByTx to > true in the datasource configuration with similar tests as the one > described above, but haven't had any luck with them either. They all > deadlocked at some point. Does anybody know if TrackConnectionByTx is > intended to handle the previously described deadlock scenario? > > Any comments are welcome. > Thanks, > Greg > > > ------------------------------------------------------- > This SF.net email is sponsored by: eBay > Get office equipment for less on eBay! > http://adfarm.mediaplex.com/ad/ck/711-11697-6916-5 > _______________________________________________ > JBoss-user mailing list > [EMAIL PROTECTED] > https://lists.sourceforge.net/lists/listinfo/jboss-user ------------------------------------------------------- This SF.net email is sponsored by: Etnus, makers of TotalView, The best thread debugger on the planet. Designed with thread debugging features you've never dreamed of, try TotalView 6 free at www.etnus.com. _______________________________________________ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user ------------------------------------------------------- This SF.Net email is sponsored by: INetU Attention Web Developers & Consultants: Become An INetU Hosting Partner. Refer Dedicated Servers. We Manage Them. You Get 10% Monthly Commission! INetU Dedicated Managed Hosting http://www.inetu.net/partner/index.php _______________________________________________ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user
