Re: WO 5.3.3 / SQL-Server and primary key generation
Re: WO 5.3.3 / SQL-Server and primary key generation
- Subject: Re: WO 5.3.3 / SQL-Server and primary key generation
- From: Michael DeMan <email@hidden>
- Date: Wed, 11 Jan 2012 18:18:21 -0800
Hey thanks, this helps - another idea anyway.
I was wondering about deadlocks too the way the it runs now. We are on SQL-Server 2005, not sure if that matters.
I will look at XLOCK tomorrow. I'm not sure where the plugin we have came from, but luckily all the source code for it is in with the project itself so tweaking that might be an option.
- Mike
On Jan 11, 2012, at 5:48 PM, Peter Vandoros wrote:
> Hi Mike,
>
> I had a similar problem a long time ago in a configuration like yours. The difference was that I was getting dead locking transactions being detected by SQL server.
>
> I resolved it by changing the SQL to use an XLOCK instead - via my custom ms SQL JDBC hint plugin.
>
> Hope that helps!
>
> Regards,
>
> Peter
>
> On 12/01/2012, at 7:04 AM, Michael DeMan <email@hidden> wrote:
>
>> Hi All,
>>
>> I have a legacy 5.3.3 application that we need to scale up to multiple instances. It is a pretty big, old and fragile code base - wonderizing it does not seem to be an option. I reviewed the code and the use cases are separate enough that there is no need for full inter-app EO change notifications - except for primary key generation. On the primary key generation, I noticed that the SQL being generated is like the below and was thinking that with that UPDLOCK and ROWLOCK on the EO_PK_TABLE, that it might 'just work'. However, in testing, I was able to get cases where different application instances were colliding on trying to use the same primary key value.
>>
>>
>> [2012-01-07 12:29:35 EST] <WorkerThread44> evaluateExpression: <com.webobjects.jdbcadaptor.MicrosoftPlugIn$MicrosoftExpression: "SELECT PK FROM EO_PK_TABLE WITH (UPDLOCK,ROWLOCK) WHERE NAME = 'PROVIDER_USER'">
>> [2012-01-07 12:29:35 EST] <WorkerThread44> evaluateExpression: <com.webobjects.jdbcadaptor.MicrosoftPlugIn$MicrosoftExpression: "UPDATE EO_PK_TABLE SET PK = 2532643 WHERE NAME = 'PROVIDER_USER' AND PK = 2532642">
>>
>>
>>
>> I am unsure how to proceed and was hoping somebody might be able to give me some pointers.
>>
>> #1. Should that SQL above be okay and doing the right thing? Maybe we just have something set wrong on SQL-Server and it is ignoring the locks?
>>
>> #2. Otherwise - I am thinking my best bet is to override the hook in EOF for primary key generation.
>>
>>
>> Thanks,
>>
>> - Mike DeMan
>>
>> _______________________________________________
>> Do not post admin requests to the list. They will be ignored.
>> Webobjects-dev mailing list (email@hidden)
>> Help/Unsubscribe/Update your Subscription:
>>
>> This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden