I am batch loading a few hundred orders at a time from a legacy system. I am using a custom simple method to read in the order data, create the order using the storeOrder service, and then quickShipEntireOrder.
Creating the order is working great, but when I added quickShip into the code I started getting database deadlocks. The job eventually finishes but is quite a performance issue. Environment: Sun JDK 1.6 CentOS with 2.6.18 kernel SQL Server 2005 (on another server obviously) JTDS JDBC driver I'm checking with the DBA to find out how SQL Server is configured. I know it has plenty of hardware but not sure how they have it configured for number of connections and that sort of thing. OFBiz is configured for 250 max db connections. Should be sufficient since my process is the only thing hitting it right now. Any advice on where to look from here would be helpful. Here is the error: Failure in findOne operation for entity [Shipment]: org.ofbiz.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT SHIPMENT_TYPE_ID, STATUS_ID, PRIMARY_ORDER_ID, PRIMARY_RETURN_ID, PRIMARY_SHIP_GROUP_SEQ_ID, PICKLIST_BIN_ID, ESTIMATED_READY_DATE, ESTIMATED_SHIP_DATE, ESTIMATED_SHIP_WORK_EFF_ID, ESTIMATED_ARRIVAL_DATE, ESTIMATED_ARRIVAL_WORK_EFF_ID, LATEST_CANCEL_DATE, ESTIMATED_SHIP_COST, CURRENCY_UOM_ID, HANDLING_INSTRUCTIONS, ORIGIN_FACILITY_ID, DESTINATION_FACILITY_ID, ORIGIN_CONTACT_MECH_ID, ORIGIN_TELECOM_NUMBER_ID, DESTINATION_CONTACT_MECH_ID, DESTINATION_TELECOM_NUMBER_ID, PARTY_ID_TO, PARTY_ID_FROM, ADDITIONAL_SHIPPING_CHARGE, ADDTL_SHIPPING_CHARGE_DESC, CREATED_DATE, CREATED_BY_USER_LOGIN, LAST_MODIFIED_DATE, LAST_MODIFIED_BY_USER_LOGIN, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP FROM dbo.SHIPMENT WHERE SHIPMENT_ID=? (Transaction (Process ID 72) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.). Rolling back transaction. Exception: org.ofbiz.entity.GenericDataSourceException Vince Clark www.globalera.com [hidden email] (303) 493-6723 office (303) 523-4843 cell |
first I would watch the SQL on the DB server to see what it is getting
and what it is doing with it. since ofbiz creates and send a Stored procedure see if it creating more than one for the same operation. Vince Clark sent the following on 8/3/2009 7:54 AM: > I am batch loading a few hundred orders at a time from a legacy system. I am using a custom simple method to read in the order data, create the order using the storeOrder service, and then quickShipEntireOrder. > > Creating the order is working great, but when I added quickShip into the code I started getting database deadlocks. The job eventually finishes but is quite a performance issue. > > Environment: > Sun JDK 1.6 > CentOS with 2.6.18 kernel > SQL Server 2005 (on another server obviously) > JTDS JDBC driver > > I'm checking with the DBA to find out how SQL Server is configured. I know it has plenty of hardware but not sure how they have it configured for number of connections and that sort of thing. OFBiz is configured for 250 max db connections. Should be sufficient since my process is the only thing hitting it right now. > > Any advice on where to look from here would be helpful. > > Here is the error: > > Failure in findOne operation for entity [Shipment]: org.ofbiz.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT SHIPMENT_TYPE_ID, STATUS_ID, PRIMARY_ORDER_ID, PRIMARY_RETURN_ID, PRIMARY_SHIP_GROUP_SEQ_ID, PICKLIST_BIN_ID, ESTIMATED_READY_DATE, ESTIMATED_SHIP_DATE, ESTIMATED_SHIP_WORK_EFF_ID, ESTIMATED_ARRIVAL_DATE, ESTIMATED_ARRIVAL_WORK_EFF_ID, LATEST_CANCEL_DATE, ESTIMATED_SHIP_COST, CURRENCY_UOM_ID, HANDLING_INSTRUCTIONS, ORIGIN_FACILITY_ID, DESTINATION_FACILITY_ID, ORIGIN_CONTACT_MECH_ID, ORIGIN_TELECOM_NUMBER_ID, DESTINATION_CONTACT_MECH_ID, DESTINATION_TELECOM_NUMBER_ID, PARTY_ID_TO, PARTY_ID_FROM, ADDITIONAL_SHIPPING_CHARGE, ADDTL_SHIPPING_CHARGE_DESC, CREATED_DATE, CREATED_BY_USER_LOGIN, LAST_MODIFIED_DATE, LAST_MODIFIED_BY_USER_LOGIN, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP FROM dbo.SHIPMENT WHERE SHIPMENT_ID=? (Transaction (Process ID 72) was deadlocked on lock resources with another process a > Exception: org.ofbiz.entity.GenericDataSourceException > > > > > Vince Clark > www.globalera.com > [hidden email] > (303) 493-6723 office > (303) 523-4843 cell > > -- BJ Freeman http://www.businessesnetwork.com/automation http://bjfreeman.elance.com http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro Systems Integrator. |
I think you meant prepared statements rather than stored procedures
there. Regards Scott On 4/08/2009, at 10:59 AM, BJ Freeman wrote: > since ofbiz creates and send a Stored procedure see if it creating > more > than one for the same operation. smime.p7s (3K) Download Attachment |
In reply to this post by BJ Freeman
I meant storeproceedure that are temporary from MS side anyway.
you may mean the prepared statements that create stored proceedures for MS. they are Functions for postgresql. Scott Gray sent the following on 8/3/2009 4:18 PM: > I think you meant prepared statements rather than stored procedures there. > > Regards > Scott > > On 4/08/2009, at 10:59 AM, BJ Freeman wrote: > >> since ofbiz creates and send a Stored procedure see if it creating more >> than one for the same operation. > -- BJ Freeman http://www.businessesnetwork.com/automation http://bjfreeman.elance.com http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro Systems Integrator. |
They aren't temporary stored procedures on MS unless you're talking 10
years ago: http://msdn.microsoft.com/en-us/library/aa197533(SQL.80).aspx And Postgresql makes no mention of functions: http://www.postgresql.org/docs/8.1/interactive/sql-prepare.html In both cases they just compile and cache the statement which is what people call prepared statements. Regards Scott On 4/08/2009, at 2:03 PM, BJ Freeman wrote: > I meant storeproceedure that are temporary from MS side anyway. > you may mean the prepared statements that create stored proceedures > for MS. > they are Functions for postgresql. > > Scott Gray sent the following on 8/3/2009 4:18 PM: >> I think you meant prepared statements rather than stored procedures >> there. >> >> Regards >> Scott >> >> On 4/08/2009, at 10:59 AM, BJ Freeman wrote: >> >>> since ofbiz creates and send a Stored procedure see if it creating >>> more >>> than one for the same operation. >> > > -- > BJ Freeman > http://www.businessesnetwork.com/automation > http://bjfreeman.elance.com > http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro > Systems Integrator. > smime.p7s (3K) Download Attachment |
In reply to this post by BJ Freeman
goes to show how far behind I am.
thanks for the update Scott Gray sent the following on 8/3/2009 7:25 PM: > They aren't temporary stored procedures on MS unless you're talking 10 > years ago: http://msdn.microsoft.com/en-us/library/aa197533(SQL.80).aspx > And Postgresql makes no mention of functions: > http://www.postgresql.org/docs/8.1/interactive/sql-prepare.html > In both cases they just compile and cache the statement which is what > people call prepared statements. > > Regards > Scott > > On 4/08/2009, at 2:03 PM, BJ Freeman wrote: > >> I meant storeproceedure that are temporary from MS side anyway. >> you may mean the prepared statements that create stored proceedures >> for MS. >> they are Functions for postgresql. >> >> Scott Gray sent the following on 8/3/2009 4:18 PM: >>> I think you meant prepared statements rather than stored procedures >>> there. >>> >>> Regards >>> Scott >>> >>> On 4/08/2009, at 10:59 AM, BJ Freeman wrote: >>> >>>> since ofbiz creates and send a Stored procedure see if it creating more >>>> than one for the same operation. >>> >> >> -- >> BJ Freeman >> http://www.businessesnetwork.com/automation >> http://bjfreeman.elance.com >> http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro >> >> Systems Integrator. >> > -- BJ Freeman http://www.businessesnetwork.com/automation http://bjfreeman.elance.com http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro Systems Integrator. |
Was able to track down the query causing the deadlock. Apparently has nothing to do with quickShipEntireOrder:
SELECT CURRENT_PASSWORD, PASSWORD_HINT, IS_SYSTEM, ENABLED, HAS_LOGGED_OUT, REQUIRE_PASSWORD_CHANGE, LAST_CURRENCY_UOM, LAST_LOCALE, LAST_TIME_ZONE, DISABLED_DATE_TIME, SUCCESSIVE_FAILED_LOGINS, EXTERNAL_AUTH_ID, USER_LDAP_DN, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP, PARTY_ID FROM dbo.USER_LOGIN WHERE USER_LOGIN_ID= When I schedule the job I am logged in as myself. Also, I am not doing anything in my simple method to specify a logged in user. I've seen other instances in the code where system user is explicitly set as the "logged in user". Should I be doing this? Vince Clark www.globalera.com [hidden email] (303) 493-6723 office (303) 523-4843 cell ----- Original Message ----- From: "BJ Freeman" <[hidden email]> To: [hidden email] Sent: Monday, August 3, 2009 8:35:44 PM GMT -07:00 US/Canada Mountain Subject: Re: database deadlocks goes to show how far behind I am. thanks for the update Scott Gray sent the following on 8/3/2009 7:25 PM: > They aren't temporary stored procedures on MS unless you're talking 10 > years ago: http://msdn.microsoft.com/en-us/library/aa197533(SQL.80).aspx > And Postgresql makes no mention of functions: > http://www.postgresql.org/docs/8.1/interactive/sql-prepare.html > In both cases they just compile and cache the statement which is what > people call prepared statements. > > Regards > Scott > > On 4/08/2009, at 2:03 PM, BJ Freeman wrote: > >> I meant storeproceedure that are temporary from MS side anyway. >> you may mean the prepared statements that create stored proceedures >> for MS. >> they are Functions for postgresql. >> >> Scott Gray sent the following on 8/3/2009 4:18 PM: >>> I think you meant prepared statements rather than stored procedures >>> there. >>> >>> Regards >>> Scott >>> >>> On 4/08/2009, at 10:59 AM, BJ Freeman wrote: >>> >>>> since ofbiz creates and send a Stored procedure see if it creating more >>>> than one for the same operation. >>> >> >> -- >> BJ Freeman >> http://www.businessesnetwork.com/automation >> http://bjfreeman.elance.com >> http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro >> >> Systems Integrator. >> > -- BJ Freeman http://www.businessesnetwork.com/automation http://bjfreeman.elance.com http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro Systems Integrator. |
In reply to this post by Vince Clark
system is used for scheduled jobs or services that are not initiated by
a user. Vince Clark sent the following on 8/4/2009 7:59 AM: > Was able to track down the query causing the deadlock. Apparently has nothing to do with quickShipEntireOrder: > > SELECT CURRENT_PASSWORD, PASSWORD_HINT, IS_SYSTEM, ENABLED, HAS_LOGGED_OUT, REQUIRE_PASSWORD_CHANGE, LAST_CURRENCY_UOM, LAST_LOCALE, LAST_TIME_ZONE, DISABLED_DATE_TIME, SUCCESSIVE_FAILED_LOGINS, EXTERNAL_AUTH_ID, USER_LDAP_DN, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP, PARTY_ID FROM dbo.USER_LOGIN WHERE USER_LOGIN_ID= > > When I schedule the job I am logged in as myself. Also, I am not doing anything in my simple method to specify a logged in user. I've seen other instances in the code where system user is explicitly set as the "logged in user". Should I be doing this? > > > > Vince Clark > www.globalera.com > [hidden email] > (303) 493-6723 office > (303) 523-4843 cell > > > ----- Original Message ----- > From: "BJ Freeman" <[hidden email]> > To: [hidden email] > Sent: Monday, August 3, 2009 8:35:44 PM GMT -07:00 US/Canada Mountain > Subject: Re: database deadlocks > > goes to show how far behind I am. > thanks for the update > > > Scott Gray sent the following on 8/3/2009 7:25 PM: >> They aren't temporary stored procedures on MS unless you're talking 10 >> years ago: http://msdn.microsoft.com/en-us/library/aa197533(SQL.80).aspx >> And Postgresql makes no mention of functions: >> http://www.postgresql.org/docs/8.1/interactive/sql-prepare.html >> In both cases they just compile and cache the statement which is what >> people call prepared statements. >> >> Regards >> Scott >> >> On 4/08/2009, at 2:03 PM, BJ Freeman wrote: >> >>> I meant storeproceedure that are temporary from MS side anyway. >>> you may mean the prepared statements that create stored proceedures >>> for MS. >>> they are Functions for postgresql. >>> >>> Scott Gray sent the following on 8/3/2009 4:18 PM: >>>> I think you meant prepared statements rather than stored procedures >>>> there. >>>> >>>> Regards >>>> Scott >>>> >>>> On 4/08/2009, at 10:59 AM, BJ Freeman wrote: >>>> >>>>> since ofbiz creates and send a Stored procedure see if it creating more >>>>> than one for the same operation. >>> -- >>> BJ Freeman >>> http://www.businessesnetwork.com/automation >>> http://bjfreeman.elance.com >>> http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro >>> >>> Systems Integrator. >>> > -- BJ Freeman http://www.businessesnetwork.com/automation http://bjfreeman.elance.com http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro Systems Integrator. |
... or for any operation that the system does automatically based on a user's actions or some other trigger, especially when you would not want to allow the user to do that operation manually or have control over that operation (ie have permissions related to it). -David On Aug 4, 2009, at 11:16 AM, BJ Freeman wrote: > system is used for scheduled jobs or services that are not initiated > by > a user. > > > Vince Clark sent the following on 8/4/2009 7:59 AM: >> Was able to track down the query causing the deadlock. Apparently >> has nothing to do with quickShipEntireOrder: >> >> SELECT CURRENT_PASSWORD, PASSWORD_HINT, IS_SYSTEM, ENABLED, >> HAS_LOGGED_OUT, REQUIRE_PASSWORD_CHANGE, LAST_CURRENCY_UOM, >> LAST_LOCALE, LAST_TIME_ZONE, DISABLED_DATE_TIME, >> SUCCESSIVE_FAILED_LOGINS, EXTERNAL_AUTH_ID, USER_LDAP_DN, >> LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, >> CREATED_TX_STAMP, PARTY_ID FROM dbo.USER_LOGIN WHERE USER_LOGIN_ID= >> >> When I schedule the job I am logged in as myself. Also, I am not >> doing anything in my simple method to specify a logged in user. >> I've seen other instances in the code where system user is >> explicitly set as the "logged in user". Should I be doing this? >> >> >> >> Vince Clark >> www.globalera.com >> [hidden email] >> (303) 493-6723 office >> (303) 523-4843 cell >> >> >> ----- Original Message ----- >> From: "BJ Freeman" <[hidden email]> >> To: [hidden email] >> Sent: Monday, August 3, 2009 8:35:44 PM GMT -07:00 US/Canada Mountain >> Subject: Re: database deadlocks >> >> goes to show how far behind I am. >> thanks for the update >> >> >> Scott Gray sent the following on 8/3/2009 7:25 PM: >>> They aren't temporary stored procedures on MS unless you're >>> talking 10 >>> years ago: http://msdn.microsoft.com/en-us/library/aa197533(SQL.80).aspx >>> And Postgresql makes no mention of functions: >>> http://www.postgresql.org/docs/8.1/interactive/sql-prepare.html >>> In both cases they just compile and cache the statement which is >>> what >>> people call prepared statements. >>> >>> Regards >>> Scott >>> >>> On 4/08/2009, at 2:03 PM, BJ Freeman wrote: >>> >>>> I meant storeproceedure that are temporary from MS side anyway. >>>> you may mean the prepared statements that create stored proceedures >>>> for MS. >>>> they are Functions for postgresql. >>>> >>>> Scott Gray sent the following on 8/3/2009 4:18 PM: >>>>> I think you meant prepared statements rather than stored >>>>> procedures >>>>> there. >>>>> >>>>> Regards >>>>> Scott >>>>> >>>>> On 4/08/2009, at 10:59 AM, BJ Freeman wrote: >>>>> >>>>>> since ofbiz creates and send a Stored procedure see if it >>>>>> creating more >>>>>> than one for the same operation. >>>> -- >>>> BJ Freeman >>>> http://www.businessesnetwork.com/automation >>>> http://bjfreeman.elance.com >>>> http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro >>>> >>>> Systems Integrator. >>>> >> > > -- > BJ Freeman > http://www.businessesnetwork.com/automation > http://bjfreeman.elance.com > http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro > Systems Integrator. > |
In reply to this post by Vince Clark
To paraphrase Yoda: A single query does not a deadlock make. ;) In order to really trace this down you'd need to find the 2 (or more) transactions that have locks on things that the other is trying to use. A deadlock is always characterized by one transaction holding resource A and waiting for resource B while another transaction is holding B and waiting for A (with more than 2 transactions in the "loop" this pattern will vary). It is possible to write code that does this on purpose. For an example see the automated tests in the framework/service component which are used to test the Service Engine's deadlock recovery/retry feature. Once you find the full problem (if it is caused by bad code) it usually isn't too hard to make adjustments to fix it. If it is a random runtime problem, that's different and often there is no way to "fix" the code and the things that the service engine does to retry the "victim" transaction is the best way to go, and then higher level code (and users) don't have to worry about it. -David On Aug 4, 2009, at 8:59 AM, Vince Clark wrote: > Was able to track down the query causing the deadlock. Apparently > has nothing to do with quickShipEntireOrder: > > SELECT CURRENT_PASSWORD, PASSWORD_HINT, IS_SYSTEM, ENABLED, > HAS_LOGGED_OUT, REQUIRE_PASSWORD_CHANGE, LAST_CURRENCY_UOM, > LAST_LOCALE, LAST_TIME_ZONE, DISABLED_DATE_TIME, > SUCCESSIVE_FAILED_LOGINS, EXTERNAL_AUTH_ID, USER_LDAP_DN, > LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, > CREATED_TX_STAMP, PARTY_ID FROM dbo.USER_LOGIN WHERE USER_LOGIN_ID= > > When I schedule the job I am logged in as myself. Also, I am not > doing anything in my simple method to specify a logged in user. I've > seen other instances in the code where system user is explicitly set > as the "logged in user". Should I be doing this? > > > > Vince Clark > www.globalera.com > [hidden email] > (303) 493-6723 office > (303) 523-4843 cell > > > ----- Original Message ----- > From: "BJ Freeman" <[hidden email]> > To: [hidden email] > Sent: Monday, August 3, 2009 8:35:44 PM GMT -07:00 US/Canada Mountain > Subject: Re: database deadlocks > > goes to show how far behind I am. > thanks for the update > > > Scott Gray sent the following on 8/3/2009 7:25 PM: >> They aren't temporary stored procedures on MS unless you're talking >> 10 >> years ago: http://msdn.microsoft.com/en-us/library/aa197533(SQL.80).aspx >> And Postgresql makes no mention of functions: >> http://www.postgresql.org/docs/8.1/interactive/sql-prepare.html >> In both cases they just compile and cache the statement which is what >> people call prepared statements. >> >> Regards >> Scott >> >> On 4/08/2009, at 2:03 PM, BJ Freeman wrote: >> >>> I meant storeproceedure that are temporary from MS side anyway. >>> you may mean the prepared statements that create stored proceedures >>> for MS. >>> they are Functions for postgresql. >>> >>> Scott Gray sent the following on 8/3/2009 4:18 PM: >>>> I think you meant prepared statements rather than stored procedures >>>> there. >>>> >>>> Regards >>>> Scott >>>> >>>> On 4/08/2009, at 10:59 AM, BJ Freeman wrote: >>>> >>>>> since ofbiz creates and send a Stored procedure see if it >>>>> creating more >>>>> than one for the same operation. >>>> >>> >>> -- >>> BJ Freeman >>> http://www.businessesnetwork.com/automation >>> http://bjfreeman.elance.com >>> http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro >>> >>> Systems Integrator. >>> >> > > -- > BJ Freeman > http://www.businessesnetwork.com/automation > http://bjfreeman.elance.com > http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro > Systems Integrator. > |
Free forum by Nabble | Edit this page |