Re: JDBC Transactions

On 11/1/2010 2:01 PM, Jonathan Tripathy wrote:
>
>>> I'll give you the exact case where I'm worried:
>>>
>>> We have a table of customers, and each customer can have multiple
>>> memberships (which are stored in the memberships table). We want our
>>> deleteMembership(int membershipID) method to remove the membership, then
>>> check to see if there are no more memberships left for the corresponding
>>> customer, and if there are none, delete the corresponding customer as
>>> well.
>>>
>>
>> Hum.. yeah, I can see a race condition there. but even with table
>> locking I can see it. Not sure how your stuff works, but I'm thinking
>> website:
>>
>> user1 goes to customer page, clicks on "add membership" and starts
>> filling out info.
>>
>> user2 goes to customer page, clicks on "delete membership" of the last
>> member ship, which blows away the membership, then the customer.
>>
>> user1 clicks save.
>>
>> Wouldnt matter for user2 if you locked the table or not, right?
>>
>> -Andy
>
> In the case described above, our code would throw an exception saying
> "Customer no longer exists", prompting the user to create a fresh
> customer - So I'm not worried about this (Although it may be
> inconvenient for the user, I don't think much can be done in this case).
> Please let me know if I've missed something here.
>
> I'm more worried about the following situation (Where a bad interleaving
> sequence happens):
>
> user1 goes to customer page, clicks on "delete membership" of the last
> member ship, which blows away the membership,
> user2 goes to customer page, clicks on "add membership" and starts
> filling out info.
> user1 then blows away the customer.
>
> However I guess that if the relations are set up properly in the
> database, an exception could be thrown to say that there are
> corresponding memberships still exist...
>

yep, that sequence could be a problem too. It'll be a problem whenever
more than one person gets to the customer page. Another user could
cause that customer to go away at any time. with or without table locks:

user1 and 2 go to customer page.
user1 deletes last membership, and customer
user2 does anything... cuz customer has gone away.

Do you really need to delete the customer? Is leaving it around a problem?

-Andy