Re: update table with row locking

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/