Re: JDBC Transactions

On 11/1/2010 3:02 PM, Jonathan Tripathy wrote:
>
> On 01/11/10 19:56, Andy Colson wrote:
>> On 11/1/2010 2:29 PM, Jonathan Tripathy wrote:
>>>
>>> On 01/11/10 19:12, Andy Colson wrote:
>>>> 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
>>>>
>>> Yeah, unfortunately leaving the customer round is a problem due to Data
>>> Protection Policies in the EU.
>>>
>>> However, I'm not worried about the above situation, as if the user tries
>>> to do anything with a customer that doesn't exist, an exception is
>>> thrown which is, I believe, handled properly (i.e. the program doesn't
>>> crash, but will simply tell the user to start again and create a new
>>> customer).
>>>
>>> Do you think table relations are enough to solve the situation that I
>>> gave above? I.e:
>>>
>>> user1 goes to customer page, clicks on "delete membership" of the last
>>> membership, 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.
>>>
>>> Would my above problem be solved if the database refused to remove a
>>> customer if it had remaining memberships?
>>>
>>> Another potential solution could be to leave the customer behind, but
>>> run a script on a Saturday night or something to delete all customers
>>> with no memberships...
>>>
>>> What do you think would be best?
>>>
>>> Thanks
>>>
>>
>> I think we might be splitting hairs... What are the chances two people
>> are editing the same customer at the exact same time? Plus the chances
>> there is only one membership (which one user is deleting), plus the
>> chances they are clicking the save button at the exact same time.
>>
>> In the PG world, I think it might go like:
>>
>> user1 clicks delete last membership:
>> start transaction
>> delete from memberships where id = 42;
>>
>> user2 has filled out new membership and clicks save
>> start transaction
>> insert into memebership where id = 100;
>>
>> user1
>> pg's default transaction level is read commited (which I learned in
>> "[GENERAL] Can Postgres Not Do This Safely ?!?" thread)
>> At this point both have a transaction open, neither commited. If user1
>> checked right now to see if customer had any more memberships, it
>> would not see any and delete the customer which would be bad... but
>> lets wait
>>
>> user2
>> commit
>>
>> user1
>> now user1 would see the new membership, and not delete the customer,
>> which would be ok.
>>
>>
>> So yes, there is a problem. I'm not 100% sure how to solve.
>>
>> -Andy
>>
>
> Sorry, Andy, where is the problem?
>

At this point I'm hoping someone will jump in... hint hint.  I have no
idea if I'm even close to correct.


user1 clicks delete last membership:
 >> start transaction
 >> delete from memberships where id = 42;

user2 has filled out new membership and clicks save
 >> start transaction
 >> insert into memebership where id = 100;

user1
    check to see if any memberships, nope, so blow away the customer
    commit

user2
    commit

now now we have a membership record (100), but no customer record.

-Andy