Hi Eugene, as you asked, I created a JIRA for the (other) deadlock issue :
https://issues.apache.org/jira/browse/HIVE-12258 Regards, Furcy On Sat, Oct 24, 2015 at 12:20 AM, Divakar Reddy <[email protected]> wrote: > 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 <[email protected]> > 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:[email protected]] >> *Sent:* 23 October 2015 17:13 >> >> *To:* [email protected] >> *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 <[email protected]> >> *Reply-To: *"[email protected]" <[email protected]> >> *Date: *Friday, October 23, 2015 at 1:18 AM >> *To: *"[email protected]" <[email protected]> >> *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:[email protected] <[email protected]>] >> >> *Sent:* 23 October 2015 09:08 >> *To:* [email protected] >> *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 <[email protected]> >> 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. >> >> >> >> >> > >
