Re: JDBC Transactions

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