I think you should be using a sequence on Oracle and a generator on Firebird. I think you can write stored procedures on each to provide an identical interface.
If you wish to keep using a table, in firebird you will need to set up a separate connection pool with serializable isolation to defeat the record versioning, or keep trying until you get a successful update. david jencks On 2002.10.18 15:14:33 -0400 Victor Batista wrote: > Hello! > I am using JBoss 3.0.2 with bundled tomcat 4.0.4. I am also > using the Firebird database with > Firebird-jca-jdbc-driver RC1 (I also tried RC1a). > I am having the following exception when I have two > concurrent Transactions: > > 19:13:47,351 ERROR [GlobalTxEntityMap] Store failed on entity: [.xpto.] > javax.ejb.EJBException: Store failed; CausedByException is: > GDS exception: org.firebirdsql.gds.GDSException: deadlock > update conflicts with concurrent update > > I have a session bean which handles sequence values through > an Entity Bean. The session bean starts one transaction each time the > sequence must be incremented (this is done in one method of the session > bean): > > sessionMethod > start transaction > query sequence value > update sequence value with previous > value + 1 > end transaction > > > If I have two concurrent transactions to increment the same > sequence, Firebird crashes with the exception above. I have tested this > with Oracle and it works fine. I have accomplished my test inserting a > sleep of several seconds (30) between the query and the update. > The second transaction blocks before the read, and only > continues after the first one finishes. With Oracle, the second > transaction reads the value stored by the previously transaction with > firebird, it reads the same value as the previous transaction (Although > it waits for the first to finish). > For instance, if the value of one sequence is 10 and I start > 2 concurrent transactions to get the new value, I get the following: > > Oracle: > Tx1 - gets 10, stores 11 and the session method returns 11 > Tx2 - waits for the first Tx to finish, gets 11, stores 12 > and the session method returns 12. > > Firebird: > Tx1 - gets 10, stores 11 and the session method returns 11 > Tx2 - waits for the first Tx to finish, gets 10, tries to > store 11, but crashes with the exception above. > > Can any one help me, please? Any help would be welcome. > > Thanks in advance, > Victor Batista > > <html xmlns:o="urn:schemas-microsoft-com:office:office" > xmlns:w="urn:schemas-microsoft-com:office:word" > xmlns:st1="urn:schemas-microsoft-com:office:smarttags" > xmlns="http://www.w3.org/TR/REC-html40"> > > <head> > <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii"> > > > <meta name=ProgId content=Word.Document> > <meta name=Generator content="Microsoft Word 10"> > <meta name=Originator content="Microsoft Word 10"> > <link rel=File-List href="cid:filelist.xml@;01C276E2.F8AF7510"> > <o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags" > name="time"/> > <!--[if gte mso 9]><xml> > <o:OfficeDocumentSettings> > <o:DoNotRelyOnCSS/> > </o:OfficeDocumentSettings> > </xml><![endif]--><!--[if gte mso 9]><xml> > <w:WordDocument> > <w:SpellingState>Clean</w:SpellingState> > <w:GrammarState>Clean</w:GrammarState> > <w:DocumentKind>DocumentEmail</w:DocumentKind> > <w:EnvelopeVis/> > <w:Compatibility> > <w:BreakWrappedTables/> > <w:SnapToGridInCell/> > <w:WrapTextWithPunct/> > <w:UseAsianBreakRules/> > </w:Compatibility> > <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> > </w:WordDocument> > </xml><![endif]--><!--[if !mso]> > <style> > st1\:*{behavior:url(#default#ieooui) } > </style> > <![endif]--> > <style> > <!-- > /* Style Definitions */ > p.MsoNormal, li.MsoNormal, div.MsoNormal > {mso-style-parent:""; > margin:0in; > margin-bottom:.0001pt; > mso-pagination:widow-orphan; > font-size:12.0pt; > font-family:"Times New Roman"; > mso-fareast-font-family:"Times New Roman"; > mso-ansi-language:PT;} > a:link, span.MsoHyperlink > {color:blue; > text-decoration:underline; > text-underline:single;} > a:visited, span.MsoHyperlinkFollowed > {color:purple; > text-decoration:underline; > text-underline:single;} > span.EmailStyle17 > {mso-style-type:personal-compose; > mso-style-noshow:yes; > mso-ansi-font-size:10.0pt; > mso-bidi-font-size:10.0pt; > font-family:Arial; > mso-ascii-font-family:Arial; > mso-hansi-font-family:Arial; > mso-bidi-font-family:Arial; > color:windowtext;} > span.SpellE > {mso-style-name:""; > mso-spl-e:yes;} > span.GramE > {mso-style-name:""; > mso-gram-e:yes;} > @page Section1 > {size:8.5in 11.0in; > margin:1.0in 1.25in 1.0in 1.25in; > mso-header-margin:.5in; > mso-footer-margin:.5in; > mso-paper-source:0;} > div.Section1 > {page:Section1;} > --> > </style> > <!--[if gte mso 10]> > <style> > /* Style Definitions */ > table.MsoNormalTable > {mso-style-name:"Table Normal"; > mso-tstyle-rowband-size:0; > mso-tstyle-colband-size:0; > mso-style-noshow:yes; > mso-style-parent:""; > mso-padding-alt:0in 5.4pt 0in 5.4pt; > mso-para-margin:0in; > mso-para-margin-bottom:.0001pt; > mso-pagination:widow-orphan; > font-size:10.0pt; > font-family:"Times New Roman";} > </style> > <![endif]--> > </head> > > <body lang=EN-US link=blue vlink=purple style='tab-interval:.5in'> > > <div class=Section1> > > <p class=MsoNormal><a name="OLE_LINK1"></a><span class=SpellE><span > style='mso-bookmark:OLE_LINK1'><font size=2 face=Arial><span lang=PT > style='font-size:10.0pt;font-family:Arial'>Hello</span></font></span></span><span > style='mso-bookmark:OLE_LINK1'><font size=2 face=Arial><span lang=PT > style='font-size:10.0pt;font-family:Arial'>!<o:p></o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span lang=PT style='font-size:10.0pt;font-family:Arial'><span > >style='mso-tab-count:1'> > </span></span></font></span><span > style='mso-bookmark:OLE_LINK1'><font size=2 face=Arial><span > style='font-size: > 10.0pt;font-family:Arial;mso-ansi-language:EN-US'>I am using <span > class=SpellE>JBoss</span> 3.0.2 with bundled tomcat 4.0.4. <span > style='mso-spacerun:yes'> </span>I am also using the Firebird > database > with<o:p></o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-ansi-language: > EN-US'>Firebird-<span class=SpellE>jca-jdbc-driver</span> RC1 (I also > tried > RC1a).<o:p></o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-ansi-language: > EN-US'><span >style='mso-tab-count:1'> > </span>I > am having the following exception when I have two concurrent > Transactions:<o:p></o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-ansi-language: > EN-US'><o:p> </o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'></span><st1:time > Hour="19" Minute="13"><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-ansi-language: > EN-US'>19:13</span></font></span></st1:time><span > style='mso-bookmark:OLE_LINK1'><font > size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-ansi-language: > EN-US'>:47,351 ERROR [<span class=SpellE>GlobalTxEntityMap</span>] Store > failed > on entity: [.<span class=SpellE>xpto</span>.]<o:p></o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><span > class=SpellE><font > size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-ansi-language: > EN-US'>javax.ejb.EJBException</span></font></span></span><span > style='mso-bookmark:OLE_LINK1'><font size=2 face=Arial><span > style='font-size: > 10.0pt;font-family:Arial;mso-ansi-language:EN-US'>: Store failed; <span > class=SpellE>CausedByException</span> > is:<o:p></o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-ansi-language: > EN-US'><span > style='mso-spacerun:yes'> > </span>GDS > exception: <span class=SpellE>org.firebirdsql.gds.GDSException</span>: > deadlock<o:p></o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><span > class=SpellE><font > size=2 face=Arial><span lang=PT >style='font-size:10.0pt;font-family:Arial'>update</span></font></span></span><span > style='mso-bookmark:OLE_LINK1'><font size=2 face=Arial><span lang=PT > style='font-size:10.0pt;font-family:Arial'> <span > class=SpellE>conflicts</span> > <span class=SpellE>with</span> <span class=SpellE>concurrent</span> <span > class=SpellE>update</span><o:p></o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span lang=PT >style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span lang=PT style='font-size:10.0pt;font-family:Arial'><span > >style='mso-tab-count:1'> > </span></span></font></span><span > style='mso-bookmark:OLE_LINK1'><font size=2 face=Arial><span > style='font-size: > 10.0pt;font-family:Arial;mso-ansi-language:EN-US'>I have a session bean > which > handles sequence values through an Entity Bean. The session bean starts > one > transaction each time the sequence must be incremented (this is done in > one > method of the session bean):<o:p></o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-ansi-language: > EN-US'><o:p> </o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-ansi-language: > EN-US'><span >style='mso-tab-count:1'> > </span><span > class=SpellE><span >class=GramE>sessionMethod</span></span><o:p></o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-ansi-language: > EN-US'><span >style='mso-tab-count:2'> > </span><span > class=GramE>start</span> transaction<o:p></o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-ansi-language: > EN-US'><span >style='mso-tab-count:1'> > </span><span > >style='mso-tab-count:2'> > </span><span > class=GramE>query</span> sequence value<o:p></o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-ansi-language: > EN-US'><span >style='mso-tab-count:1'> > </span><span > >style='mso-tab-count:1'> > </span><span > >style='mso-tab-count:1'> > </span><span > class=GramE>update</span> sequence value with previous value + > 1<o:p></o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-ansi-language: > EN-US'><span >style='mso-tab-count:2'> > </span><span > class=GramE>end</span> transaction<o:p></o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-ansi-language: > EN-US'><o:p> </o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-ansi-language: > EN-US'><o:p> </o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-ansi-language: > EN-US'><span >style='mso-tab-count:1'> > </span>If > I have two concurrent transactions to increment the same sequence, > Firebird > crashes with the exception above. I have tested this with Oracle and it > works > fine. I have accomplished my test inserting a sleep of several seconds > (30) > between the query and the update. <o:p></o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-ansi-language: > EN-US'><span >style='mso-tab-count:1'> > </span>The > second transaction blocks before the read, and only continues after the > first > one finishes. With Oracle, the second transaction reads the value stored > by the > previously transaction with firebird, it reads the same value as the > previous > transaction (Although it waits for the first to > finish).<o:p></o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-ansi-language: > EN-US'><span >style='mso-tab-count:1'> > </span>For > instance, if the value of one sequence is 10 and I start 2 concurrent > transactions to get the new value, I get the > following:<o:p></o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-ansi-language: > EN-US'><o:p> </o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-ansi-language: > EN-US'><span >style='mso-tab-count:1'> > </span>Oracle:<o:p></o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-ansi-language: > EN-US'><span >style='mso-tab-count:1'> > </span>Tx1 > – gets 10, stores 11 and the session method returns > 11<o:p></o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-ansi-language: > EN-US'><span >style='mso-tab-count:1'> > </span>Tx2 > – waits for the first <span class=SpellE><span > class=GramE>Tx</span></span> > to finish, gets 11, stores 12 and the session method returns > 12.<o:p></o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-ansi-language: > EN-US'><o:p> </o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-ansi-language: > EN-US'><span >style='mso-tab-count:1'> > </span>Firebird:<o:p></o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-ansi-language: > EN-US'><span >style='mso-tab-count:1'> > </span>Tx1 > – gets 10, stores 11 and the session method returns > 11<o:p></o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-ansi-language: > EN-US'><span >style='mso-tab-count:1'> > </span>Tx2 > – waits for the first <span class=SpellE><span > class=GramE>Tx</span></span> > to finish, gets 10, tries to store 11, but crashes with the exception > above.<o:p></o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-ansi-language: > EN-US'><o:p> </o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-ansi-language: > EN-US'><span >style='mso-tab-count:1'> > </span>Can > any one help me, please? Any help would be > welcome.<o:p></o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-ansi-language: > EN-US'><o:p> </o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-ansi-language: > EN-US'><span >style='mso-tab-count:1'> > </span>Thanks > in advance,<o:p></o:p></span></font></span></p> > > <p class=MsoNormal><span style='mso-bookmark:OLE_LINK1'><font size=2 > face=Arial><span style='font-size:10.0pt;font-family:Arial;mso-ansi-language: > EN-US'><span >style='mso-tab-count:2'> > </span>Victor > Batista</span></font></span><font size=2 face=Arial><span > style='font-size: > 10.0pt;font-family:Arial;mso-ansi-language:EN-US'><o:p></o:p></span></font></p> > > </div> > > </body> > > </html> > ------------------------------------------------------- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf _______________________________________________ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user
