Re: JDBC Transactions - Mailing list pgsql-general

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?