Page 1 of 1

Increase wait time out period for locked tables

Posted: Thu Jan 24, 2013 8:20 pm
by usli06
Hi,

Can I increase the amount of time a session will wait for a locked table to become unlocked before it times out?

After a few minutes I get an error on an INSERTY statement against a table locked by another session:

Code: Select all

dbadmin=> insert into test01 values (1);
ERROR 5156:  Unavailable: initiator locks for query - Locking failure: Timed out I locking Table:vdb001.test1. X held by [user dbadmin (select * from test01 for update;)]. Your current transaction isolation level is READ COMMITTED
I'd like the session to wait indefinatly if possible.

Thanks!

Re: Increase wait time out period for locked tables

Posted: Fri Jan 25, 2013 12:59 am
by scutter
See the LockTimeout configuration parameter:

select * from configuration_parameters where parameter_name = 'LockTimeout';

Note that it says to change it only under the guidance of support :-) I don't recall if there's a way to say wait forever, but I also wouldn't recommend doing that. Sometimes you really do want it to timeout, especially when you do something silly or just don't expect what's happening to be happening.

--Sharon

Re: Increase wait time out period for locked tables

Posted: Fri Jan 25, 2013 1:31 pm
by usli06
Thank you, scutter! Turns out I just need to increase the wait time a little and your suggestion worked perfectly!

Re: Increase wait time out period for locked tables

Posted: Fri Nov 17, 2017 10:23 am
by prudhvi
Please specify steps how to increase the lock timeout

Re: Increase wait time out period for locked tables

Posted: Fri Nov 17, 2017 5:18 pm
by JimKnicely
You can change the Lock Timeout at the SESSION, NODE and DATABASE levels.

Examples:

dbadmin=> select parameter_name, current_value, default_value from configuration_parameters where parameter_name = 'LockTimeout';
parameter_name | current_value | default_value
----------------+---------------+---------------
LockTimeout | 300 | 300
(1 row)

dbadmin=> alter database sfdc set LockTimeout = 600; -- At the DB level (DB in my case = SFDC)
ALTER DATABASE

dbadmin=> select parameter_name, current_value, default_value from configuration_parameters where parameter_name = 'LockTimeout';
parameter_name | current_value | default_value
----------------+---------------+---------------
LockTimeout | 600 | 300
(1 row)

dbadmin=> alter session set LockTimeout = 600; -- At the SESSION level
ALTER SESSION