Increase wait time out period for locked tables

Moderator: NorbertKrupa

Post Reply
User avatar
usli06
Intermediate
Intermediate
Posts: 93
Joined: Wed Jan 25, 2012 4:53 am

Increase wait time out period for locked tables

Post by usli06 » Thu Jan 24, 2013 8:20 pm

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!

scutter
Master
Master
Posts: 301
Joined: Tue Aug 07, 2012 2:15 am

Re: Increase wait time out period for locked tables

Post by scutter » Fri Jan 25, 2013 12:59 am

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
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

User avatar
usli06
Intermediate
Intermediate
Posts: 93
Joined: Wed Jan 25, 2012 4:53 am

Re: Increase wait time out period for locked tables

Post by usli06 » Fri Jan 25, 2013 1:31 pm

Thank you, scutter! Turns out I just need to increase the wait time a little and your suggestion worked perfectly!

prudhvi
Newbie
Newbie
Posts: 1
Joined: Fri Nov 17, 2017 10:21 am

Re: Increase wait time out period for locked tables

Post by prudhvi » Fri Nov 17, 2017 10:23 am

Please specify steps how to increase the lock timeout

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1778
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Increase wait time out period for locked tables

Post by JimKnicely » Fri Nov 17, 2017 5:18 pm

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
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “New to Vertica Database Development”