All locks in Hive are on database, table, or partition level. There are
no row level locks. When using DbTxnManager the locking is chosen to be
as fine grained as possible (ie only partitions are locked when we can a
priori know the partitions that will be used in the query).
Alan.
Mich Talebzadeh <mailto:m...@peridale.co.uk>
April 8, 2015 at 14:45
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.
