Re: JDBC Transactions - Mailing list pgsql-general

On Mon, 01 Nov 2010 20:02:30 +0000, Jonathan Tripathy <jonnyt@abpni.co.uk>
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?

I didn't followed thread carefully, but you should first use FK's. If you
don't want to use it do as follows:
On delete or update or any change not thread change you should think
about:

1. select customer where id = :customner_id_from_membership for update -
this will lock this record. If someone other deleted record, after commit
you will get empty set.
2. now you can try to delete, ask about memberships etc.

Assuming that there will be unexpected rollbacks two things can happen:
1. user 1st won lock - membership #42 and customer will be deleted, user 2
- will not insert membership, because he will see no customer - broadcast
error to client.
2. user 2nd won lock - membership #100 will be inserted, then user 1st
deletes membership #42, but keeps customer; he see membership #100 on list

The above solution requires you to find all not thread safe places in your
code. It is something like synchronized(o) {...}.
--
----------
Radosław Smogura
http://www.softperience.eu