Search Postgresql Archives

Re: JDBC Transactions

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux