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