Re: JDBC Transactions

Hi Everyone,

I'm trying to create a server for a database system which will be used
by multiple clients. Of course, table locking is very important. Reading
the Postgresql docs, locking occurs on a transaction-by-transaction basis.

In our java code, we are doing this:

//Start Code Block

Connection con = "..."
con.setAutoComitt(false);

//Insert SQL here to lock table

String qry1 = "..."
pst1 = con.prepareStatement(qry1)
//Insert code here to add values to prepared statement pst1
pst1.executequery();

String qry2 = "..."
pst2 = con.prepareStatement(qry2)
//Insert code here to add values to prepared statement pst2
pst2.executequery();

con.comitt();

//End Code Block

My question is, would the above block of code be classed as a single
transaction, and would the locking work correctly?

Thanks

Jonny

On 11/1/2010 12:37 PM, Jonathan Tripathy wrote:
> Hi Everyone,
>
> I'm trying to create a server for a database system which will be used
> by multiple clients. Of course, table locking is very important. Reading
> the Postgresql docs, locking occurs on a transaction-by-transaction basis.
>
> In our java code, we are doing this:
>
> //Start Code Block
>
> Connection con = "..."
> con.setAutoComitt(false);
>
> //Insert SQL here to lock table
>
> String qry1 = "..."
> pst1 = con.prepareStatement(qry1)
> //Insert code here to add values to prepared statement pst1
> pst1.executequery();
>
> String qry2 = "..."
> pst2 = con.prepareStatement(qry2)
> //Insert code here to add values to prepared statement pst2
> pst2.executequery();
>
> con.comitt();
>
> //End Code Block
>
> My question is, would the above block of code be classed as a single
> transaction, and would the locking work correctly?
>
> Thanks
>
> Jonny
>
>

Table locking is very bad for concurrent access. When a table is
locked, its one user at a time.

PG usually does not need any locks at all. As long as you use
transactions as they were meant to be used (as an atomic operation),
things usually work really well, with no locking at all. You could read
up on MVCC is you were interested.

Without knowing what sql you are running, I can _totally guarantee_
it'll work perfectly with NO table locking. :-)

-Andy

On 01/11/10 18:08, Andy Colson wrote:
> On 11/1/2010 12:37 PM, Jonathan Tripathy wrote:
>> Hi Everyone,
>>
>> I'm trying to create a server for a database system which will be used
>> by multiple clients. Of course, table locking is very important. Reading
>> the Postgresql docs, locking occurs on a transaction-by-transaction
>> basis.
>>
>> In our java code, we are doing this:
>>
>> //Start Code Block
>>
>> Connection con = "..."
>> con.setAutoComitt(false);
>>
>> //Insert SQL here to lock table
>>
>> String qry1 = "..."
>> pst1 = con.prepareStatement(qry1)
>> //Insert code here to add values to prepared statement pst1
>> pst1.executequery();
>>
>> String qry2 = "..."
>> pst2 = con.prepareStatement(qry2)
>> //Insert code here to add values to prepared statement pst2
>> pst2.executequery();
>>
>> con.comitt();
>>
>> //End Code Block
>>
>> My question is, would the above block of code be classed as a single
>> transaction, and would the locking work correctly?
>>
>> Thanks
>>
>> Jonny
>>
>>
>
> Table locking is very bad for concurrent access. When a table is
> locked, its one user at a time.
>
> PG usually does not need any locks at all. As long as you use
> transactions as they were meant to be used (as an atomic operation),
> things usually work really well, with no locking at all. You could
> read up on MVCC is you were interested.
>
> Without knowing what sql you are running, I can _totally guarantee_
> it'll work perfectly with NO table locking. :-)
>
> -Andy

Hi Andy,

Thanks for your reply. Would the above code be classed as a single
transaction then? And if so, I could just simple leave out the line
which says "//Insert SQL here to lock table"?

Thanks

2010/11/1 Jonathan Tripathy <jonnyt(at)abpni(dot)co(dot)uk>:
>
> On 01/11/10 18:08, Andy Colson wrote:
>>
>> On 11/1/2010 12:37 PM, Jonathan Tripathy wrote:
>>>
>>> Hi Everyone,
>>>
>>> I'm trying to create a server for a database system which will be used
>>> by multiple clients. Of course, table locking is very important. Reading
>>> the Postgresql docs, locking occurs on a transaction-by-transaction
>>> basis.
>>>
>>> In our java code, we are doing this:
>>>
>>> //Start Code Block
>>>
>>> Connection con = "..."
>>> con.setAutoComitt(false);
>>>
>>> //Insert SQL here to lock table
>>>
>>> String qry1 = "..."
>>> pst1 = con.prepareStatement(qry1)
>>> //Insert code here to add values to prepared statement pst1
>>> pst1.executequery();
>>>
>>> String qry2 = "..."
>>> pst2 = con.prepareStatement(qry2)
>>> //Insert code here to add values to prepared statement pst2
>>> pst2.executequery();
>>>
>>> con.comitt();
>>>
>>> //End Code Block
>>>
>>> My question is, would the above block of code be classed as a single
>>> transaction, and would the locking work correctly?
>>>
>>> Thanks
>>>
>>> Jonny
>>>
>>>
>>
>> Table locking is very bad for concurrent access.  When a table is locked,
>> its one user at a time.
>>
>> PG usually does not need any locks at all.  As long as you use
>> transactions as they were meant to be used (as an atomic operation), things
>> usually work really well, with no locking at all.  You could read up on MVCC
>> is you were interested.
>>
>> Without knowing what sql you are running, I can _totally guarantee_ it'll
>> work perfectly with NO table locking.  :-)
>>
>> -Andy
>
> Hi Andy,
>
> Thanks for your reply. Would the above code be classed as a single
> transaction then?

Yes, assuming there's no explicit transaction control
(COMMIT/ROLLBACK/END) in your queries.

> And if so, I could just simple leave out the line which
> says "//Insert SQL here to lock table"?

In PostgreSQL, locking is done automatically depending on actual
isolation level and SQL queries.
You can use explicit locking but most of the time it's not needed.

--
Filip Rembiałkowski
JID,mailto:filip(dot)rembialkowski(at)gmail(dot)com
http://filip.rembialkowski.net/

>> Hi Andy,
>>
>> Thanks for your reply. Would the above code be classed as a single
>> transaction then?
> Yes, assuming there's no explicit transaction control
> (COMMIT/ROLLBACK/END) in your queries.
Actually, we do have maybe one or 2 queries that use ROLLBACK, however
ROLLBACK happens at the end of a "code block" so the question is
probably moot.
>> And if so, I could just simple leave out the line which
>> says "//Insert SQL here to lock table"?
> In PostgreSQL, locking is done automatically depending on actual
> isolation level and SQL queries.
> You can use explicit locking but most of the time it's not needed.
>
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.

On 01/11/10 18:38, Jonathan Tripathy wrote:
>
>>> Hi Andy,
>>>
>>> Thanks for your reply. Would the above code be classed as a single
>>> transaction then?
>> Yes, assuming there's no explicit transaction control
>> (COMMIT/ROLLBACK/END) in your queries.
> Actually, we do have maybe one or 2 queries that use ROLLBACK, however
> ROLLBACK happens at the end of a "code block" so the question is
> probably moot.
Please ignore this above comment from me. We are using JDBC's rollback()
method, instead of comitt() (in a catch block), so all seems fine.
>>> And if so, I could just simple leave out the line which
>>> says "//Insert SQL here to lock table"?
>> In PostgreSQL, locking is done automatically depending on actual
>> isolation level and SQL queries.
>> You can use explicit locking but most of the time it's not needed.
>>
> 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.
>

On 11/1/2010 1:38 PM, Jonathan Tripathy wrote:
>
>>> Hi Andy,
>>>
>>> Thanks for your reply. Would the above code be classed as a single
>>> transaction then?
>> Yes, assuming there's no explicit transaction control
>> (COMMIT/ROLLBACK/END) in your queries.
> Actually, we do have maybe one or 2 queries that use ROLLBACK, however
> ROLLBACK happens at the end of a "code block" so the question is
> probably moot.
>>> And if so, I could just simple leave out the line which
>>> says "//Insert SQL here to lock table"?
>> In PostgreSQL, locking is done automatically depending on actual
>> isolation level and SQL queries.
>> You can use explicit locking but most of the time it's not needed.
>>
> 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

>> 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...

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

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

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?

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

Andy Colson <andy(at)squeakycode(dot)net> writes:
> now now we have a membership record (100), but no customer record.

I haven't really been following this thread, but: isn't the answer
to that to establish a foreign-key constraint? If there's an FK
then the database will provide sufficient row locking to prevent
you from deleting a row that someone else is in the midst of creating
a reference to.

regards, tom lane

On Mon, 01 Nov 2010 20:02:30 +0000, Jonathan Tripathy <jonnyt(at)abpni(dot)co(dot)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

On 11/02/2010 03:01 AM, Jonathan Tripathy wrote:

> 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. However, most webapps use short transactions and optimistic locking
using a row change timestamp / counter. This sort of approach will
detect conflicting writes but will NOT lock rows to prevent someone else
deleting them. There are still races, you just find out if you lose
rather than having data clobbered silently. It doesn't sound like you're
using this kind of strategy; it's mostly popular with ORM systems and
"highly scalable" webapps with high user counts. Beware if you are,
though, as you have to design things differently, as you pretty much
have to live with user 2 getting an error from your app saying that "the
customer seems to have been deleted by somebody else".

If you're holding database connections open with transactions open
during user "think time", which I think you are, then you can use
row-level locking in the database to handle the issue. Just obtain a
row-level read lock on the customer row of interest before doing any
addition/deletion/alteration of memberships. If your transaction will
alter the customer record its self, obtain a write lock (FOR UPDATE)
instead, because trying to get a SHARE lock then upgrading to an UPDATE
lock is, like any other lock promotion, prone to deadlock.

SELECT id FROM customer WHERE id = 'thecustomerid' FOR SHARE;
INSERT INTO membership(...)

You can do this with a BEFORE trigger on the table containing
memberships, but doing it that way may make you more prone to deadlocks
caused by lock ordering problems.

If you do this, you have to be aware that other SELECT .. FOR UPDATE
queries will block if a row is already locked by another transaction.
You can use NOWAIT to prevent this, but have to be prepared to handle
errors caused by another transaction having the row locked.

See:
http://www.postgresql.org/docs/current/static/sql-select.html#SQL-FOR-UPDATE-SHARE

--
Craig Ringer

On 02/11/10 09:53, Craig Ringer wrote:
> On 11/02/2010 03:01 AM, Jonathan Tripathy wrote:
>
>> 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. However, most webapps use short transactions and optimistic
> locking using a row change timestamp / counter. This sort of approach
> will detect conflicting writes but will NOT lock rows to prevent
> someone else deleting them. There are still races, you just find out
> if you lose rather than having data clobbered silently. It doesn't
> sound like you're using this kind of strategy; it's mostly popular
> with ORM systems and "highly scalable" webapps with high user counts.
> Beware if you are, though, as you have to design things differently,
> as you pretty much have to live with user 2 getting an error from your
> app saying that "the customer seems to have been deleted by somebody
> else".
>
> If you're holding database connections open with transactions open
> during user "think time", which I think you are, then you can use
> row-level locking in the database to handle the issue. Just obtain a
> row-level read lock on the customer row of interest before doing any
> addition/deletion/alteration of memberships. If your transaction will
> alter the customer record its self, obtain a write lock (FOR UPDATE)
> instead, because trying to get a SHARE lock then upgrading to an
> UPDATE lock is, like any other lock promotion, prone to deadlock.
>
> SELECT id FROM customer WHERE id = 'thecustomerid' FOR SHARE;
> INSERT INTO membership(...)
>
> You can do this with a BEFORE trigger on the table containing
> memberships, but doing it that way may make you more prone to
> deadlocks caused by lock ordering problems.
>
> If you do this, you have to be aware that other SELECT .. FOR UPDATE
> queries will block if a row is already locked by another transaction.
> You can use NOWAIT to prevent this, but have to be prepared to handle
> errors caused by another transaction having the row locked.
>
> See:
> http://www.postgresql.org/docs/current/static/sql-select.html#SQL-FOR-UPDATE-SHARE
>
> --
> Craig Ringer

Hi Craig,

Thanks for the excellent reply. I don't have time to read it at the
minute, but I'll read it later on today and get back to you.

Just as a quick response, I'm not keeping any transactions open during
user "think time" so row level locks aren't possible. However I'm happy
enough with the user getting a message saying that "The customer has
been deleted by somebody else". I don't really mind what happens, as
long as the user is made aware of what has happen, and there aren’t any
memberships with no corresponding customers.

Thanks

Jonny

On 02/11/10 18:29, Jonathan Tripathy wrote:

> I don't really mind what happens, as
> long as the user is made aware of what has happen, and there aren’t any
> memberships with no corresponding customers.

Well, that's taken care of by a referential integrity constraint. You
don't need anything else.

It sounded earlier like you also needed to ensure that there were no
customers without corresponding memberships.

--
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.com/

On 02/11/10 23:11, Craig Ringer wrote:
> On 02/11/10 18:29, Jonathan Tripathy wrote:
>
>
>> I don't really mind what happens, as
>> long as the user is made aware of what has happen, and there aren’t any
>> memberships with no corresponding customers.
> Well, that's taken care of by a referential integrity constraint. You
> don't need anything else.
>
> It sounded earlier like you also needed to ensure that there were no
> customers without corresponding memberships.
That would be bad as well, however at least it wouldn’t crash the
system. What interleaving sequence would cause that?