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 <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] <mailto:[email protected]> >
Reply-To: "[email protected] <mailto:[email protected]> "
<[email protected] <mailto:[email protected]> >
Date: Friday, October 23, 2015 at 1:18 AM
To: "[email protected] <mailto:[email protected]> "
<[email protected] <mailto:[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 <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]]
Sent: 23 October 2015 09:08
To: [email protected] <mailto:[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]
<mailto:[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 <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.
