database deadlocks

[Vince Clark]

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]
[phone] office
[phone] cell

[BJ Freeman]

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

nd 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]
> [phone] office
> [phone] 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.

[Scott Gray-2]

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]

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.

[Scott Gray-2]

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]

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.

[Vince Clark]

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]
[phone] office
[phone] 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.

[BJ Freeman]

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]
> [phone] office
> [phone] 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.

[David E. Jones-2]

... 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]
>> [phone] office
>> [phone] 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.
>

[David E. Jones-2]

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]
> [phone] office
> [phone] 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.
>