Обсуждение: update table with row locking
Hi, Is it possible to lock row(s) when updating a table, so another call for update( from different session) will be rejected and to be on hold until lock get released ? Thanks, Mark. __________________________________ Do you Yahoo!? The all-new My Yahoo! - What will yours do? http://my.yahoo.com
On Thu, 2004-12-23 at 13:56, Mark wrote: > Hi, > Is it possible to lock row(s) when updating a table, so another call > for update( from different session) will be rejected and to be on > hold until lock get released ? Generally the update itself will lock the table from other updates until your transaction either commits or rolls back. OR are you looking for something like select for update to lock a row to be used to update another row?
On Thu, Dec 23, 2004 at 11:56:26AM -0800, Mark wrote: > Is it possible to lock row(s) when updating a table, so another call > for update( from different session) will be rejected and to be on > hold until lock get released ? The UPDATE statement automatically acquires locks, but if you need more control then you could use SELECT FOR UPDATE or LOCK. For more information see the "Concurrency Control" chapter in the documentation. What problem are you trying to solve? Depending on what you're doing, you might not need to use explicit locking. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
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. Will 'SELECT FOR UPDATE' lock whole table? As per 'LOCK' will lock released if connection get closed abnormally? ( example: hard crash on the client side, network interruptions, etc.) Can LOCK be used in JDBC or it's SQL92 standard? Thanks a lot. Mark --- Michael Fuhr <mike@fuhr.org> wrote: > On Thu, Dec 23, 2004 at 11:56:26AM -0800, Mark wrote: > > > Is it possible to lock row(s) when updating a table, so another > call > > for update( from different session) will be rejected and to be on > > hold until lock get released ? > > The UPDATE statement automatically acquires locks, but if you need > more control then you could use SELECT FOR UPDATE or LOCK. For > more information see the "Concurrency Control" chapter in the > documentation. > > What problem are you trying to solve? Depending on what you're > doing, you might not need to use explicit locking. > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > __________________________________ Do you Yahoo!? Read only the mail you want - Yahoo! Mail SpamGuard. http://promotions.yahoo.com/new_mail
On Wed, Dec 29, 2004 at 07:02:51 -0800, Mark <sendmailtomark@yahoo.com> 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. You probably want to use LOCK. You can read the documention for that command to help decide which lock you should take. > > Will 'SELECT FOR UPDATE' lock whole table? No. It will only lock records visible to the current transaction. In particular this won't keep other transactions from doing inserts into the table. > As per 'LOCK' will lock released if connection get closed abnormally? I believe the locks get released when the backend process terminates. Depending on how the client dies, there may be a significant delay before the backend process terminates. > ( example: hard crash on the client side, network interruptions, > etc.) Can LOCK be used in JDBC or it's SQL92 standard? I don't know, but I would be surprised if you couldn't.
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/
Bruno Wolff III <bruno@wolff.to> writes:
> I believe the locks get released when the backend process terminates.
> Depending on how the client dies, there may be a significant delay before
> the backend process terminates.
I'd expect a client-application crash to be reported to the backend
promptly. The cases where there might be significant delay include loss
of network connectivity and hard crash of the whole client machine (so
that its kernel forgets there is an open connection). In either case
the backend probably won't be told the connection is dead until a TCP
keepalive timeout elapses ... which is on the order of an hour or more
given an RFC-compliant TCP stack.
(If the crash happens while actively transferring data then the relevant
timeouts are much shorter. The slow case is when the backend is just
sitting idle-in-transaction, and awaiting a new client command that
never comes.)
regards, tom lane