might be your issue related to https://issues.apache.org/jira/browse/HIVE-10500
Can you please add this in the custom hive-site.xml and try? Name: datanucleus.connectionPoolingType Value: dbcp On Fri, Oct 23, 2015 at 2:37 PM, Mich Talebzadeh <m...@peridale.co.uk> wrote: > Hi Eugene, > > > > The code drops the table if exists and that is the exclusive lock. Once > created it is populated from another > > > > use asehadoop; > > drop table if exists t; > > create table t ( > > owner varchar(30) > > ,object_name varchar(30) > > ,subobject_name varchar(30) > > ,object_id bigint > > ,data_object_id bigint > > ,object_type varchar(19) > > ,created timestamp > > ,last_ddl_time timestamp > > ,timestamp varchar(19) > > ,status varchar(7) > > ,temporary2 varchar(1) > > ,generated varchar(1) > > ,secondary varchar(1) > > ,namespace bigint > > ,edition_name varchar(30) > > ,padding1 varchar(4000) > > ,padding2 varchar(3500) > > ,attribute varchar(32) > > ,op_type int > > ,op_time timestamp > > ) > > clustered by (object_id) into 256 buckets > > ; > > INSERT INTO TABLE t > > SELECT > > owner > > , object_name > > , subobject_name > > , object_id > > , data_object_id > > , object_type > > , cast(created AS timestamp) > > , cast(last_ddl_time AS timestamp) > > , timestamp > > , status > > , temporary2 > > , generated > > , secondary > > , namespace > > , edition_name > > , padding1 > > , padding2 > > , attribute > > , 1 > > , cast(from_unixtime(unix_timestamp()) AS timestamp) > > FROM t_staging > > ; > > > > I killed the program using Ctrl C and then used mapred job as below to > kill > > > > mapred job -kill job_1445590859106_0002 > > Killed job job_1445590859106_0002 > > > > > > Still locks were held > > > > HTH > > > > Mich Talebzadeh > > > > *Sybase ASE 15 Gold Medal Award 2008* > > A Winning Strategy: Running the most Critical Financial Data on ASE 15 > > > http://login.sybase.com/files/Product_Overviews/ASE-Winning-Strategy-091908.pdf > > Author of the books* "A Practitioner’s Guide to Upgrading to Sybase ASE > 15", ISBN 978-0-9563693-0-7*. > > co-author *"Sybase Transact SQL Guidelines Best Practices", ISBN > 978-0-9759693-0-4* > > *Publications due shortly:* > > *Complex Event Processing in Heterogeneous Environments*, ISBN: > 978-0-9563693-3-8 > > *Oracle and Sybase, Concepts and Contrasts*, ISBN: 978-0-9563693-1-4, volume > one out shortly > > > > http://talebzadehmich.wordpress.com > > > > NOTE: The information in this email is proprietary and confidential. This > message is for the designated recipient only, if you are not the intended > recipient, you should destroy it immediately. Any information in this > message shall not be understood as given or endorsed by Peridale Technology > Ltd, its subsidiaries or their employees, unless expressly so stated. It is > the responsibility of the recipient to ensure that this email is virus > free, therefore neither Peridale Ltd, its subsidiaries nor their employees > accept any responsibility. > > > > *From:* Eugene Koifman [mailto:ekoif...@hortonworks.com] > *Sent:* 23 October 2015 17:13 > > *To:* user@hive.apache.org > *Subject:* Re: locks are held on tables even when no job running > > > > Mich, > > how were you running/killing the job? was it ^C of CLI or something else? > > (The only time you’d get Exclusive lock is to drop an object. (With > DbTxnManager which looks like what you are using)) > > The locks will timeout but > https://issues.apache.org/jira/browse/HIVE-11317 may be relevant. > > > > Furcy, > > could you file a Jira with a repro for the deadlock you are describing? > > > > Thanks, > > Eugene > > > > > > > > *From: *Mich Talebzadeh <m...@peridale.co.uk> > *Reply-To: *"user@hive.apache.org" <user@hive.apache.org> > *Date: *Friday, October 23, 2015 at 1:18 AM > *To: *"user@hive.apache.org" <user@hive.apache.org> > *Subject: *RE: locks are held on tables even when no job running > > > > Hi Furcy, > > > > Thanks for the info. > > > > I ran the same job twice, killing it first time and starting again. > Actually your point about 5 min duration seems to be correct. my process > basically creates a new hive table with two additional columns and populate > it from an existing table hence the locks > > > > Even if the job is killed “WAITING EXCLUSIVE” locks are still held on the > new table (see below) and that is the cause of the issue > > > > Lock ID Database Table Partition State Type > Transaction ID Last Hearbeat Acquired At User Hostname > > 14031 asehadoop t NULL WAITING EXCLUSIVE NULL > 1445586539232 NULL hduser rhes564 > > 14029 asehadoop t_staging NULL ACQUIRED > SHARED_READ NULL 1445586247044 1445585940653 hduser rhes564 > > 14029 asehadoop t NULL ACQUIRED SHARED_READ > NULL 1445586247044 1445585940654 hduser rhes564 > > 14030 asehadoop t NULL WAITING EXCLUSIVE NULL > 1445586471827 NULL hduser rhes564 > > > > I am not sure this is behaving like classic RDBMS like Sybase or MSSQL > where the rollback is happening after KILL command and the locks are held > until rollback is complete. Killing a process itself will not release the > locks! > > > > Regards, > > > > > > Mich Talebzadeh > > > > *Sybase ASE 15 Gold Medal Award 2008* > > A Winning Strategy: Running the most Critical Financial Data on ASE 15 > > > http://login.sybase.com/files/Product_Overviews/ASE-Winning-Strategy-091908.pdf > > Author of the books* "A Practitioner’s Guide to Upgrading to Sybase ASE > 15", ISBN 978-0-9563693-0-7*. > > co-author *"Sybase Transact SQL Guidelines Best Practices", ISBN > 978-0-9759693-0-4* > > *Publications due shortly:* > > *Complex Event Processing in Heterogeneous Environments*, ISBN: > 978-0-9563693-3-8 > > *Oracle and Sybase, Concepts and Contrasts*, ISBN: 978-0-9563693-1-4, > volume one out shortly > > > > http://talebzadehmich.wordpress.com > > > > NOTE: The information in this email is proprietary and confidential. This > message is for the designated recipient only, if you are not the intended > recipient, you should destroy it immediately. Any information in this > message shall not be understood as given or endorsed by Peridale Technology > Ltd, its subsidiaries or their employees, unless expressly so stated. It is > the responsibility of the recipient to ensure that this email is virus > free, therefore neither Peridale Ltd, its subsidiaries nor their employees > accept any responsibility. > > > > *From:* Furcy Pin [mailto:furcy....@flaminem.com <furcy....@flaminem.com>] > > *Sent:* 23 October 2015 09:08 > *To:* user@hive.apache.org > *Subject:* Re: locks are held on tables even when no job running > > > > Hi Mich, > > > > I believe the duration of locks is defined by hive.txn.timeout, which is 5 > min by default. > > https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties > > > > Retry your SHOW LOCKS command and check that the Last HeartBeat is not > changing. > > If it is, it means your query is still active somehow. If it isn't, the > lock should disappear by itself after the timeout. > > > > Also, I don't know if this is a known issue in Hive's Jira, but we noticed > that trying to read and write data > > into the same table within a single query creates a deadlock. > > > > If that is what you were trying to do, you should rather write your data > in a temporary file and then move it back into the table. > > > > > > > > > > > > > > > > > > On Fri, Oct 23, 2015 at 9:56 AM, Mich Talebzadeh <m...@peridale.co.uk> > wrote: > > Hi, > > > > > > What is the duration of locks held in Hive? > > > > I have got the following locks in Hive, although I have already killed the > jobs! > > > > > > Lock ID Database Table Partition State Type > Transaction ID Last Hearbeat Acquired At User Hostname > > 14031 asehadoop t NULL WAITING EXCLUSIVE NULL > 1445586539232 NULL hduser rhes564 > > 14029 asehadoop t_staging NULL ACQUIRED > SHARED_READ NULL 1445586247044 1445585940653 hduser rhes564 > > 14029 asehadoop t NULL ACQUIRED SHARED_READ > NULL 1445586247044 1445585940654 hduser rhes564 > > 14030 asehadoop t NULL WAITING EXCLUSIVE NULL > 1445586471827 NULL hduser rhes564 > > > > > > mapred job -list > > Total jobs:0 > > JobId State StartTime > UserName Queue Priority UsedContainers > RsvdContainers UsedMem RsvdMem NeededMem AM info > > > > No locks are held in metastore (Oracle in my case) as well. > > > > Thanks > > > > > > Mich Talebzadeh > > > > *Sybase ASE 15 Gold Medal Award 2008* > > A Winning Strategy: Running the most Critical Financial Data on ASE 15 > > > http://login.sybase.com/files/Product_Overviews/ASE-Winning-Strategy-091908.pdf > > Author of the books* "A Practitioner’s Guide to Upgrading to Sybase ASE > 15", ISBN 978-0-9563693-0-7*. > > co-author *"Sybase Transact SQL Guidelines Best Practices", ISBN > 978-0-9759693-0-4* > > *Publications due shortly:* > > *Complex Event Processing in Heterogeneous Environments*, ISBN: > 978-0-9563693-3-8 > > *Oracle and Sybase, Concepts and Contrasts*, ISBN: 978-0-9563693-1-4, > volume one out shortly > > > > http://talebzadehmich.wordpress.com > > > > NOTE: The information in this email is proprietary and confidential. This > message is for the designated recipient only, if you are not the intended > recipient, you should destroy it immediately. Any information in this > message shall not be understood as given or endorsed by Peridale Technology > Ltd, its subsidiaries or their employees, unless expressly so stated. It is > the responsibility of the recipient to ensure that this email is virus > free, therefore neither Peridale Ltd, its subsidiaries nor their employees > accept any responsibility. > > > > >
