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
" 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
