13 Replies Latest reply on Mar 31, 2004 12:43 AM by Stephane Nicoll

    DB Update after a select

    Ed Ross Newbie

      I may be a bit naive, but I do a find on the database, select some fields from the cmp, then select so other stuff. The first items I selected update the database (according to the logs). As no fields were changed, I don't understand why the container updated the tables. This seems like it could have some terrible performance implications - is there any whay around this?

      Thanks

      ed

        • 1. Re: DB Update after a select
          Erik Reuter Newbie

          Experienced the same experimenting with a simple CMR one-to-many relationshop using JBoss 3.2.1, where I never called any of the mutator methods on the entities.

          If I changed the <commit-option> in standardjboss.xml for "Standard CMP 2.x EntityBean" from B to A (JBoss owns the database), performance increased, in my case, over 1000% pct.

          Even though option A tells JBoss that it has exclusive access to the DB, it shouldn't do a commit to the DB unless an entity has actually changed with option B. It seems like they do some pretty aggressive caching with option A, but are too conservative with option B.

          Anyone any thoughts?

          • 2. Re: DB Update after a select
            Ed Ross Newbie

            WOW! I didn't even know that option exited. Thanks See an incredible change (From 69 transactions/Minute 245/Second) Using apache JMeter

            • 3. Re: DB Update after a select
              samart Newbie

              I'm wondering about this also. does anyone know how to disable the update after select when using cmr? i'm using commit option a...

              Thanks,
              trevor

              • 4. Re: DB Update after a select
                samart Newbie

                this is bug #699938 which was closed on march 19. however, its still there in 3.2.1.

                is there a way i can patch 3.2.1 to fix this now? its a major performance bottleneck.

                • 5. Re: DB Update after a select
                  tedlong Newbie

                  This is also causing us big problems. I found it because our datasource (SQLServer) is taking increasingly more time to do an update by primary key as the tables are growing... this is a separate issue.
                  If there is a work-around I'd like to know what it is. In the meantime I think I'll try out 3.2.2RC2.

                  • 6. Re: DB Update after a select
                    nekro Newbie

                    Hello TedLong,

                    Please, tell me, what workaround do you have for the problem you mentioned : "more time to do an update by primary key as the tables are growing"??

                    This is somehting that I am also encountering, but with MySQL...Is there anything to be done? I know for sure it is not related to commit options or transaction settings, it is just related to the table size....when the table is almost empty, all runs fine, but performance goes down as the tables increase.

                    • 7. Re: DB Update after a select
                      Rudolf Frank Newbie

                      Hi,

                      has anyone found a workaround for this problem so far?
                      I'm using JBoss 3.2.3 and Oracle 9i and there are happening UPDATES in finder methods after the SELECT.
                      This causes huge deadlock problems in the database (ORA-00060).
                      I switched to the "1 instance per transaction" configuration to avoid the JBoss deadlocks and now I'm getting deadlocks in the database.
                      Is it possible to switch off the UPDATES somehow?

                      Any help would be appreciated.

                      • 8. Re: DB Update after a select
                        Alexey Loubyansky Master

                        What is your code doing? What fields are updated? What are types the types of those fields?

                        • 9. Re: DB Update after a select
                          sago Newbie

                          I have the same problem using jboss 3.2.3 and oracle 9i. CMR field UPDATES are executed after SELECTS.

                          • 10. Re: DB Update after a select
                            Rudolf Frank Newbie

                            All java.util.Date fields are updated. In Oracle9i they are TIMESTAMP(3).

                            I've found a workaround for this problem in this forum:

                            In jbosscmp-jdbc.xml, set <check-dirty-after-get> to false for your non-primitive CMP fields.

                            <cmp-field>
                            <field-name>shipDate</field-name>
                            <column-name>ship_date</column-name>
                            <not-null/>
                            <jdbc-type>DATE</jdbc-type>
                            <sql-type>DATE</sql-type>
                            <check-dirty-after-get>false</check-dirty-after-get>
                            </cmp-field>

                            It works great and I didn't have a deadlock since I set <check-dirty-after-get> to false for the Date fields. The problem with it is that I have to do it for every single field and that I can't do it with XDoclet. So I have to patch jbosscmp-jdbc.xml after every run of XDoclet.

                            Does anybody know, why JBoss does these updates? If it's not a bug a switch to turn it off generally would be very helpful.

                            • 11. Re: DB Update after a select
                              snicoll@kiala.com Newbie

                              Guys,

                              Sounds we have the same problem with postgresql (especially the instance per transaction -> deadlock on the database).

                              Regarding the Xdoclet problem I will submit a patch to Xdoclet that will add this tag.

                              I will keep you posted.

                              Regards,

                              Stephane

                              • 12. Re: DB Update after a select
                                Micah Spears Newbie

                                actually they fixed the incorrect update for Date fields in 3.2.4RC1 which eliminates the need for check-dirty-after-get. see the notes at http://sourceforge.net/project/shownotes.php?release_id=223319

                                use JDBCTypeFactory.EQUALS state factory for date types. fix for [ 864871 ] CMP evals java.sql.Date and java.sql.Timestamp as dirty


                                as for XDoclet, I submitted a patch over 3 weeks ago which was never merged. oy vey...