Skip site navigation (1) Skip section navigation (2)

Re: update table with row locking

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Mark <sendmailtomark(at)yahoo(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: update table with row locking
Date: 2004-12-29 18:20:34
Message-ID: 20041229182034.GA36573@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-general
On Wed, Dec 29, 2004 at 07:02:51AM -0800, Mark wrote:

> In general I need to lock whole  table so only one session can
> perform update. I'm looking for solution that will work in both C++
> and Java.

As I asked in my previous message, what problem are you trying to
solve?  What's the rationale behind locking the entire table?  What
situation are you trying to prevent that automatically-acquired
locks would otherwise allow?  You may indeed need to lock the entire
table, but so far we haven't seen the reasons to justify doing so.
Locking an entire table can hurt concurrent performance, so avoid
it if possible.

> Will 'SELECT FOR UPDATE' lock whole table?

SELECT FOR UPDATE will lock the selected rows -- see "Row-Level
Locks" in the "Concurrency Control" chapter of the documentation.
SELECT FOR UPDATE is useful if you need to lock part of a table,
but if you need to lock the entire table then I suspect LOCK would
be more efficient.

> As per 'LOCK' will lock released if connection get closed abnormally?
> ( example: hard crash on the client side, network interruptions,
> etc.)

Locks should be released when a transaction completes, either
normally (COMMIT or ROLLBACK) or abnormally, as long as the backend
recognizes what's happened (e.g., if the connection suddenly closes).

> Can LOCK be used in JDBC or it's SQL92 standard?

See the "SQL Commands" part of the documentation for each command's
standards-compliance -- each statement should have a "Compatibility"
section near the bottom of the page.

Does JDBC enforce a specific standard, or does it merely provide
the communications channel?

Depending on your needs, a SERIALIZABLE transaction isolation level
might also work.  See the "Transaction Isolation" section in the
"Concurrency Control" chapter of the documentation.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

In response to

pgsql-general by date

Next:From: Tom LaneDate: 2004-12-29 18:30:43
Subject: Re: update table with row locking
Previous:From: Bruno Wolff IIIDate: 2004-12-29 18:19:24
Subject: Re: update table with row locking

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group