hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mich Talebzadeh" <m...@peridale.co.uk>
Subject Hive support for concurrency with Oracle Metastore
Date Wed, 08 Apr 2015 21:45:54 GMT
Hi,

 

Thanks for all the useful info.  I managed to set up hive concurrency using
Oracle Metastore for Hive. Had to modify hive-txn-schema-0.14.0.oracle.sql
script in order to drop the existing tables as I had created the metastore
with hive-schema-0.14.0.oracle.sql initially.

 

All the transactions seem to behave as expected (meaning conforming to
behaviour expected from an ACID compliant RDBMS). Except a question that
comes to mind with locking when deleting all rows from the table (as opposed
to truncating the table) 

 

I created a test table 

 

create table txtest (col1 int, col2 varchar(30)) clustered by (col1) into 10
buckets  STORED AS orc TBLPROPERTIES('transactional'='true');

 

Inserted 10 rows and updatedstats

 

insert into table txtest values
(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'),(6,'f'),(7,'f'),(8,'g'),(9,'h'),(10,
'i');

analyse table txtest compute statistics;

 

Updated three columns followed by a single column

 

update txtest set col2 = col2 where col1 in (1,3,5);

 

update txtest set col2 = 'row1' where col1 = 1;

 

>From another session looked at locks

 

show locks;

+----------+---------------+---------+------------+-------------+-----------
----+-----------------+-----------------+----------------+---------+--------
---+--+

|  lockid  |   database    |  table  | partition  | lock_state  |
lock_type   | transaction_id  | last_heartbeat  |  acquired_at   |  user   |
hostname  |

+----------+---------------+---------+------------+-------------+-----------
----+-----------------+-----------------+----------------+---------+--------
---+--+

| Lock ID  | Database      | Table   | Partition  | State       | Type
| Transaction ID  | Last Hearbeat   | Acquired At    | User    | Hostname  |

| 21       | oraclehadoop  | txtest  | NULL       | ACQUIRED    |
SHARED_WRITE  | 6               | 1428525777008   | 1428525774206  | hduser
| rhes564   |

+----------+---------------+---------+------------+-------------+-----------
----+-----------------+-----------------+----------------+---------+--------
---+--+

 

Now see what happens if we try to delete two different rows from two
different  sessions concurrently (well almost)

 

Session 1

hive> delete from txtest where col1 = 10;

 

Session 2

delete from txtest where col1 = 4;

 

The locks are shown below

 

show locks;

+----------+---------------+---------+------------+-------------+-----------
----+-----------------+-----------------+----------------+---------+--------
---+--+

|  lockid  |   database    |  table  | partition  | lock_state  |
lock_type   | transaction_id  | last_heartbeat  |  acquired_at   |  user   |
hostname  |

+----------+---------------+---------+------------+-------------+-----------
----+-----------------+-----------------+----------------+---------+--------
---+--+

| Lock ID  | Database      | Table   | Partition  | State       | Type
| Transaction ID  | Last Hearbeat   | Acquired At    | User    | Hostname  |

| 23       | oraclehadoop  | txtest  | NULL       | ACQUIRED    |
SHARED_WRITE  | 7               | 1428526836905   | 1428526833132  | hduser
| rhes564   |

| 24       | oraclehadoop  | txtest  | NULL       | WAITING     |
SHARED_WRITE  | 8               | 1428526865246   | NULL           | hduser
| rhes564   |

+----------+---------------+---------+------------+-------------+-----------
----+-----------------+-----------------+----------------+---------+--------
---+--+

 

Now delete all rows from the table

 

hive> delete from txtest;

 

0: jdbc:hive2://rhes564:10010/default> show locks;

+----------+---------------+---------+------------+-------------+-----------
----+-----------------+-----------------+----------------+---------+--------
---+--+

|  lockid  |   database    |  table  | partition  | lock_state  |
lock_type   | transaction_id  | last_heartbeat  |  acquired_at   |  user   |
hostname  |

+----------+---------------+---------+------------+-------------+-----------
----+-----------------+-----------------+----------------+---------+--------
---+--+

| Lock ID  | Database      | Table   | Partition  | State       | Type
| Transaction ID  | Last Hearbeat   | Acquired At    | User    | Hostname  |

| 25       | oraclehadoop  | txtest  | NULL       | ACQUIRED    |
SHARED_WRITE  | 9               | 1428527740870   | 1428527737658  | hduser
| rhes564   |

+----------+---------------+---------+------------+-------------+-----------
----+-----------------+-----------------+----------------+---------+--------
---+--+

 

The question I have is if we delete from the whole table it seems that
"only" one lock is applied to whole table. Does that mean a full table lock
rather that locks for every row deleted?

 

 

Thanks,

 

Mich Talebzadeh

 

http://talebzadehmich.wordpress.com

 

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:

Creating in-memory Data Grid for Trading Systems with Oracle TimesTen and
Coherence Cache

Oracle and Sybase, Concepts and Contrasts, ISBN: 978-0-9563693-1-4, volume
one out shortly

 

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 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.

 


Mime
View raw message