Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

DML lock timeout

xxsawerMay 16 2014 — edited May 16 2014

Hi all,

I would like to ask if there is some timeout after which Oracle releases lock based on some DML statement. e.g. if in session A one particular record is locked and session B tries to modify that record, would session B wait forever if session A would not do a commit or rollback?

Thanks for help,
Dan

Comments

Solomon Yakobson

No. The only DML timeout Oracle has is distributed_lock_timeout.

SY.

1 person found this helpful
Hoek

http://docs.oracle.com/cd/E11882_01/server.112/e10839/app_odbc.htm#UNXAR352

1 person found this helpful
Zlatko Sirotic

You can use:

LOCK TABLE ... NOWAIT / WAIT n (seconds)

and

SELECT ... FOR UPDATE NOWAIT / WAIT n (seconds)

You can call SELECT ... FOR UPDATE NOWAIT before you call UPDATE / DELETE.

Calling SELECT ... FOR UPDATE NOWAIT before INSERT hasn’t effect, because SELECT in the second database session can't see non-committed row.

But, see this trick, using parameter DISTRIBUTED_LOCK_TIMEOUT:

Simulating LOCK TIMEOUT with INSERT / UPDATE / DELETE Statements

https://community.oracle.com/message/12277016#12277016

Regards

1 person found this helpful
xxsawer

Thank you for replies and interesting points.

1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 13 2014
Added on May 16 2014
4 comments
6,963 views