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