Re: JDBC Transactions - Mailing list pgsql-general

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