Re: JDBC Transactions - Mailing list pgsql-general

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